Лабораторная работа 1: Работа с электронной таблицей как с базой данных.
Сортировка и фильтрация данных.
Цель работы:
· познакомиться с использованием электронной таблицы как базы данных;
· научиться осуществлять поиск информации в базе данных по различным критериям;
· научиться производить сортировку информации.
Задание 1. Заполните таблицу.
Основные понятия баз данных
БД предназначены для хранения больших объёмов структурированной информации.
БД в Excel – это таблица, организованная определённым образом. Область таблицы А2:Н17 можно рассматривать как базу данных. Столбцы А, В, С, D, E, F, G, H этой таблицы называются полями, а строки со 2 по 17 – записями. Область А2:Н2 содержит имена полей.
Существуют ограничения, накладываемые на структуру базы данных:
· первый ряд базы данных должен содержать неповторяющиеся имена полей;
· остальные ряды данных должны содержать записи, которые не являются пустыми рядами;
· информация по полям (столбцам) должна быть однородной, т. е. только цифры или только текст.
Весь инструмент работы с БД в Excel сосредоточен на вкладке Данные.
Фильтрация данных
Excel даёт возможность работать с данными, удовлетворяющими определённым условиям. Фильтрация таблицы оставляет на экране только те строки, которые отвечают заданным критериям.
При использовании фильтра необходимо переместить курсор в область, содержащую базу данных, или выделить её. Затем нужно перейти на вкладку Данные, щелкнуть по кнопке Фильтр.
. На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации.
Задание 1. Оставить в базе данных только трехкомнатные квартиры.
1. Установите курсор в области базы данных.
2. На вкладке Данные щелкните по кнопке Фильтр. На полях появились кнопки.
3. Нажмите на кнопку в поле «Кол-во комнат».
4. Снимите флажок «Выделить все».
5. Установите флажок «3».
6. Нажмите «ОК».
Проверьте: в базе данных остались 6 квартир (№ 2, 8, 9, 10, 12, 15).
7. Щелкните по кнопке «Фильтр», чтобы убрать кнопки с полей.
Задание 2. С использованием фильтра осуществить поиск двухкомнатных квартир Октябрьского или Центрального района, расположенных не выше 5 этажа с телефоном.
1. Установите курсор в области базы данных.
2. На вкладке Данные щелкните по кнопке Фильтр. На полях появились кнопки.
3. Нажмите на кнопку в поле «Район». Выберите «Текстовые фильтры» - «Настраиваемый фильтр». Появится окно «Пользовательский автофильтр».
4. В окне «Пользовательский автофильтр» задайте критерии согласно рис. 1. Примечание: Символ «*» заменяет любое количество любых символов.
Рис. 1
Н ажмите кнопку Ok.
Проверьте! В базе данных остались шесть квартир с № п/п 3, 5, 7, 10, 13, 14.
5. Нажмите на кнопку на поле «Кол-во комнат». Снимите флажок «Выделить все», установите флажок «2».
Проверьте! В базе данных остались три квартиры с № п/п 5, 7, 14.
6. Нажмите на кнопку на поле «Этажность квартиры». Выберите пункт «Числовые фильтры» - «меньше или равно».
7. В диалоговом окне задайте критерий (Рис. 2).
Рис. 2
Проверьте! В базе данных остались две квартиры с № п/п 7, 14.
8. Нажмите на кнопку на поле «Телефон». Снимите флажок «Выделить все», затем установите флажок «есть».
Проверьте! Осталась одна квартира № 7.
2.8. На вкладке Данные щелкните по кнопке Фильтр, чтобы убрать кнопки с полей.
Задание 3. С использованием фильтра самостоятельно:
· осуществите поиск трёхкомнатных квартир в кирпичных домах со стоимостью не выше 1 миллиона руб. (ответ: одна квартира с № 15)
· найдите однокомнатные квартиры в 5-этажных домах (ответ: № 3, 13).
Расширенный фильтр (на примере задания № 4, стр. 5)
При использовании Расширенного фильтра необходимо сначала определить (создать) три области (см. рис. 2):
· Интервал списка – область базы данных (А2:Н17), эта область у вас уже есть.
· Интервал критериев – область, где задаются критерии фильтрации (А19:Н21). Для ее создания скопируйте заголовки таблицы в строку 19, в строки 20 и 21 заносятся данные из задачи № 4.
· Интервал извлечения – область, в которой будут появляться результаты фильтрации (А23:Н28). Для ее создания скопируйте заголовки таблицы в строку 23. Имена полей во всех интервалах должны точно совпадать. В строки 24-26 будут занесены результаты фильтрации.
Рис. 2
Для выполнения действий по фильтрации переходим на вкладку Данные и щелкаем по кнопке Дополнительно 
. Появится окно Расширенный фильтр. В этом окне надо указать координаты интервалов (Рис. 3) и установить флажок «Скопировать результат в другое место».
Рис. 3
Нажимаем ОК.
Задание 4. С использованием Расширенного фильтра осуществите поиск трёхкомнатных квартир стоимостью не более 900 тыс. руб. и однокомнатных со стоимостью менее 600 тыс. руб.
4.1. Создайте интервал критериев и интервал извлечения.
4.2. Запишите критерии поиска в интервал критериев.
4.3. Поместите курсор в область базы данных.
4.4. Вкладка Данные – кнопка Дополнительно
4.5. Установите флажок Скопировать результат в другое место.
4.6. Проверьте правильность задания интервалов. Нажмите кнопку Ok.
Проверьте! Должны остаться квартиры с № 1, 9, 13.
Задание 5. С использованием Расширенного фильтра самостоятельно:
Найдите двухкомнатные квартиры Октябрьского района, расположенные не выше 5 этажа и однокомнатные квартиры Железнодорожного района (ответ: № 6, 14).
Найдите все однокомнатные квартиры в панельных домах стоимостью менее 600 тыс. руб. и двухкомнатные квартиры с телефоном в кирпичных домах стоимостью менее 700 тыс. руб
(ответ: №1, 7, 13).
Сортировка данных
Для выполнения сортировки необходимо выделить область базы данных или поместить в неё курсор, а затем на вкладке Данные щелкнуть по кнопке Сортировка 
При этом появится диалоговое окно, в котором выбрать название поля, по которому нужно производить сортировку и указать порядок сортировки (Рис. 4).
Для добавления строки «Затем по» была нажата кнопка «Добавить уровень».
Рис. 4
Задание 6.
· Отсортируйте квартиры по районам города, а в районе – по количеству комнат.
· Отсортировать квартиры по материалу, затем – по количеству комнат, в последнюю очередь – по стоимости.
