ЛАБОРАТОРНАЯ РАБОТА №10

Работа с таблицей EXCEL как с базой данных: автофильтр,

расширенный фильтр, сортировка данных, создание свободных таблиц.

Цель работы. Изучить:

• Автофильтрация.

• Расширенный фильтр.

• Сортировка данных.

• Создание свободной таблицы.

Задания к работе:

К работе допущен:

Работу выполнил:

Работу защитил:

Теоретическая часть

База данных - это совокупность хранимых в памяти ЭВМ и специальным образом организованных взаимосвязанных данных, отображающих состояние предметной области. Примерами простейших баз данных могут служить телефонный справочник, расписание движения поездов и т. п.

В Microsoft Excel в качестве базы данных можно использовать список.

Список - таблица, данные в строках которой имеют однородную структуру, т.е. в каждом столбце списка содержатся данные одного типа (число, текст, дата и т.д.)

Рекомендации по созданию списка на листе книги

Размер и расположение списка

На листе не следует помещать более одного списка.

Между списком и другими данными листа необходимо оставить, по меньшей мере, одну пустую строку и один пустой столбец.

В самом списке не должно быть пустых строк и столбцов.

Важные данные, не относящиеся к списку, не следует помещать у левого или правого края списка; после применения фильтра они могут оказаться скрытыми.

Заголовки столбцов

Заголовки столбцов должны находиться в первой строке списка.

Шрифт, выравнивание, формат, шаблон, граница и формат прописных и строчных букв, присвоенные заголовкам столбцов списка, должны отличаться от формата, присвоенного строкам данных.

Для отделения заголовков от расположенных ниже данных следует использовать границы ячеек, а не пустые строки или прерывистые линии.

Содержание строк и столбцов

Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.

Перед данными в ячейке не следует вводить лишние пробелы, так как они влияют на сортировку.

Не следует помещать пустую строку между заголовками и первой строкой данных.

---

Основным назначением баз данных является быстрый поиск содержащейся в них информации.

Для работы с базами данных Excel содержит следующий набор специальных средств:

• Организация ввода, просмотра и поиска данных с помощью форм;

• Сортировка данных;

• Фильтрация данных;

• Подведение итогов и создание сводных таблиц.

• Организация ввода, просмотра и поиска данных с помощью форм

Форма — это наиболее удобный способ для просмотра, изменения, добавления и удаления записей списка, а также для поиска записей, удовлетворяющих заданным условиям. Перед работой с формой необходимо задать заголовки столбцов списка. Эти заголовки используются для создания полей формы.

Сортировка списков

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки.

По умолчанию списки сортируются в алфавитном порядке. Для сортировки месяцев и дней недели в соответствии с их логическим, а не алфавитным порядком следует использовать пользовательский порядок сортировки.

Фильтрация данных

Фильтрация позволяет выбирать данные, удовлетворяющие определенным критериям. Существует три способа фильтрации: поиск с помощью формы (мы его уже рассмотрели), автофильтр и расширенный фильтр.

Автофильтр

• Поместите курсор в область, содержащую базу данных или выделите её.

• Затем выполнить команды меню Данные/ Фильтр

• На именах полей появятся кнопки с изображением стрелок вниз, которыми можно задать критерии фильтрации.

• Пункт Условие позволяет применить отличные от равенства операторы сравнения .

• Для одного поля могут быть заданы два условия одновременно, связанные логическими и или или (рис. 27). Допускается использование специальных символов подстановки:

? - Любой символ в той же позиции, что и знак вопроса.

Например: д?м задает поиск "дым" и "дом"

* - Любую последовательность символов в той же позиции, что и звездочка.

Например: *ино задает поиск "Люблино" и "Выхино"

Строчные и прописные буквы при фильтрации данных не различаются.

---

• Если данные уже отфильтрованы по одному из столбцов, то при использовании фильтра для другого столбца будут предложены только те значения, которые видны в отфильтрованном списке

Создание диапазона условий

1. Скопируйте из списка заголовки фильтруемых столбцов в первую строку диапазона условий.

2. Введите в строки под заголовками условий требуемые критерии отбора.

3. Укажите ячейку в списке.

4. Выберите пункт меню Данные/ Фильтр/ Дополнительно.

5. В исходный диапазон автоматически ставятся координаты фильтруемого списка, если вы указали ячейку внутри списка в п.3.

6. Укажите координаты диапазона условий, содержащего строку заголовков и строки с критериями. В диапазон недопустимо включение лишних, пустых строк.

Примеры условий отбора расширенного фильтра

В условия отбора расширенного фильтра может входить несколько условий, накладываемых на один столбец, несколько условий, накладываемых одновременно на несколько столбцов, а также условия, накладываемые на возвращаемое формулой значение.

Условия, записанные в одной строке, автоматически соединяются в одно логическое выражение союзом И (And). Условия, записанные в смежных строках, соединяются союзом ИЛИ (Or).

• На ячейки одного столбца накладываются три или более условий отбора

Практическая часть

Задание 1

Выполнить задания, рассмотренные в примерах 1-10 в теоретической части:

1. Создаю таблицу аналогичную таблице (пример1) из теоретической части.

2. Подсчитываю стоимость материалов по каждой строке вставив формулу: стоимость=количество*цена

3. П олучаю таблицу, в которой данные отсортированы по фамилии получателя, внутри каждой фамилии отсортированы по материалу и каждый материал упорядочен по дате получения.

4.Получаю таблицу со строками, относящимися к октябрю 2000 года.

5.В ыбраю строки с одним наибольшим количеством кирпича.

---

6.Выбрать строки с условием:

(материал=доска) или (материал=гвозди) или (материал=цемент).

7.Выбрать строки с условием:

(получил=иванов) и (дата>10/10/2000) и (дата<1/11/2000).

8.Выбрать строки с условием:

( цена=максимальной цене в списке) или стоимость=максимальной стоимости

9.Выбрать строки с условием:

значение в столбце 'цена(у.е.)' превышает среднее значение в ячейках $D$3:$D$20.

10.П олучить таблицу итогов по фамилиям получателей с суммированием стоимости, а также с суммированием количества и стоимости каждого материала для каждого получателя.

11.Создать сводную таблицу.

Задание 2

1.Создаю таблицу 4.5.

2.Произвожу автоматическую фильтрацию данных созданной таблицы по спутникам.

3.П роизвожу автоматическую фильтрацию данных созданной таблицы по расстоянию и диаметру.

4.Выполнить расширенную фильтрацию планет по спутникам количество которых меньше 5 и по диаметрам с массой больше 1000.

5.Отсортировать данные в созданной таблице в порядке убывания массы.

Задание 3

1. Создать указанную таблицу 4.6.

2. Произвожу автоматическую фильтрацию данных созданной таблицы по к оличеству купленных компьютеров.

3. Произвести автоматическую фильтрацию данных созданной таблицы по магазинам и цене.

---

4. Выполнить расширенную фильтрацию данных по цене большей 25000 и по количеству купленных компьютеров больше 35.

5. Отсортировать данные в созданной таблице в порядке возрастания стоимости.

6. Создать сводную таблицу на новом листе для расчета средней цены различных типов компьютеров за рассматриваемый период.

Задание 4

1.Создать таблицу 4.7.

2.Произвести автоматическую фильтрацию данных созданной таблицы по с редней оценке за семестр.

3.Произвести автоматическую фильтрацию данных созданной таблицы по дисциплинам математика и сопромат.

4.Выполнить расширенную фильтрацию данных по оценке дисциплины математика большей 3 и по оценке дисциплины теоретическая механика больше

5.Отсортировать данные в созданной таблице в порядке убывания средней оценки.

Заключение:

Я изучил:

• Автофильтрация.

• Расширенный фильтр.

• Сортировка данных.

• Создание свободной таблицы.

---

Смотрите также файлы

• Лаб Работа № 1.doc

• Laboratornaya_rabota_5.doc

• Laboratornaya_rabota_3.doc

• Laboratornaya_rabota_14.doc

• Лаб Работа № 9.doc