2.15. Работа с электронной таблицей как с базой данных. Сортировка и фильтрация данных

Цель занятия – ознакомление с использованием электронной таблицы как базы данных; обучение осуществлению поиска информации в базе данных по различным критериям, произведению сортировки информации.

Произвольно заполните таблицу размером A1:F12 (с названием в объединенных ячейках A1:A12), содержащую информацию о планетах солнечной системы:

Единицы измерения, используемые в таблице:

• период обращения по орбите – в земных годах;

• среднее расстояние от Солнца – в млн. км;

• экваториальный диаметр – в тыс. км;

• масса – в 10²⁴ кг.

Основные понятия баз данных. Область таблицы A2:F12 можно рассматривать как базу данных. Столбцы A, B, С, D, Е, F этой таблицы называются полями, а строки с 3 по 12 – записями. Область A2:F2 содержит имена полей. Существуют ограничения, накладываемые на структуру базы данных:

• первый ряд базы данных должен содержать неповторяющиеся имена полей;

• остальные ряды базы данных должны содержать записи, которые не являются пустыми рядами;

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

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

Фильтрация данных. Команды «Данные / Фильтр» позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр – Автофильтр, так и через ручной Расширенный.

Автофильтр. При использовании Автофильтра необходимо переместить курсор в область, содержащую базу данных, или выделить ее. Затем нужно выполнить команды «Данные / Фильтр / Автофильтр». На именах полей появятся кнопки с изображением стрелок вниз. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт «Все» отключает фильтрацию, а пункт «Настройка» вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.

С использованием Автофильтра осуществить поиск планет, начинающихся на букву «С» или букву «Ю» с массой менее 600*10²⁴ кг.

1. Выполните команды «Данные / Фильтр / Автофильтр».

2. Нажмите на кнопку в поле «Планета». Выберите пункт «Условие».

3. В диалоговом окне задайте критерий отбора:

равно с* или равно ю*

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

4. Нажмите на кнопку на поле «Масса». Выберите пункт «Условие».

5. В диалоговом окне задайте критерий:

меньше 600

Должен остаться только Сатурн.

6. Выполните команды меню «Данные / Фильтр / Отобразить все».

С использованием Автофильтра:

• осуществите поиск планет, имеющих экваториальный диаметр менее 50 тыс. км и массу менее 50*10²⁴ кг (ответ: Меркурий, Венера, Земля, Марс, Плутон);

• осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*10²⁴ кг до 600*10²⁴ кг, а также не более 2 спутников (ответ: Венера, Земля, Нептун).

Расширенный фильтр. При использовании Расширенного фильтра необходимо сначала определить (создать) три области:

• интервал списка – область базы данных (А2:F12);

• интервал критериев – область, где задаются критерии фильтрации (A14:F15);

• интервал извлечения – область, в которой будут появляться результаты фильтрации (A17:F17).

Имена полей во всех интервалах должны точно совпадать. Для выполнения действий по фильтрации необходимо воспользоваться командами меню «Данные / Фильтр / Расширенный фильтр…». В диалоговом окне надо указать координаты всех запрошенных интервалов.

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

С использованием расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее двух.

1. Создайте интервал критериев и интервал извлечения.

2. Запишите критерии поиска в интервал критериев.

3. Поместите курсор в область базы данных.

4. Выполните команды «Данные / Фильтр / Расширенный фильтр…».

5. Установите «Поместить результат в диапазон».

6. Укажите исходный диапазон, диапазон условий и диапазон результата. Нажмите кнопку ОК.

В результате должны быть найдены планеты Юпитер, Сатурн. Уран, Нептун.

С использованием расширенного фильтра сделать самостоятельно:

• найдите планеты, имеющие период обращения более 2 световых лет и экваториальный диаметр менее 50 тыс. км (ответ: Уран и Плутон);

• осуществите поиск планет, находящихся от Солнца на расстоянии более 100 млн. км., а также имеющих более 1 спутника (ответ: Марс, Юпитер, Сатурн, Уран и Нептун).

Сортировка данных. Команды «Данные / Сортировка…» позволяют упорядочивать (сортировать) базу данных.

Для выполнения сортировки необходимо выделить область базы данных или поместить в нее курсор, а затем выполнить команды «Данные / Сортировка…». В появившемся диалоговом окне выбрать названия полей, по которым нужно производить сортировку, указать метод сортировки: по возрастанию или по убыванию и установить параметр «Идентифицировать поля по подписям».

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

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

Сохраните созданный Вами документ под названием «Практическое занятие № 15».