Лабораторная работа №3
Сортировка и фильтрация данных
Заполните таблицу, содержащую информацию о планетах солнечной системы.
Планеты солнечной системы.
| Планета | Период(в земных годах) | Расстояние от Солнца(в млн.км) | Диаметр(в тыс.км) | Масса(*1024 кг) | Спутники |
| Меркурий | 0,241 | 4,9 | 0,32 | ||
| Венера | 0,615 | 12,1 | 4,86 | ||
| Земля | 12,8 | ||||
| Марс | 1,881 | 6,8 | 0,61 | ||
| Юпитер | 11,86 | 142,6 | 1906,98 | ||
| Сатурн | 29,46 | 120,2 | 570,9 | ||
| Уран | 84,01 | 87,24 | |||
| Нептун | 164,8 | 50,2 | 103,38 | ||
| Плутон | 247,7 | 2,8 | 0,1 |
Фильтрация данных
Команды Данные/Фильтр позволяют выделять (фильтровать) нужные записи. Фильтрация возможна как через автоматический фильтр – Автофильтр, так и через ручной – Расширенный.
Автофильтр
При использовании Автофильтра необходимо переместить курсор в область заполненной таблицы или выделить ее. Затем нужно выполнить команды Данные/Фильтр/Автофильтр. На именах полей появятся кнопки с о стрелками. Нажимая на кнопки, можно задавать критерии фильтрации. В появляющемся подменю пункт Все отключает фильтрацию, а пункт Условие вызывает диалоговое окно, в котором можно установить параметры фильтрации. Для одного поля могут быть заданы два условия одновременно, связанные логическим И или ИЛИ.
Упражнение 1
С использованием Автофильтра осуществить поиск планет, начинающихся на букву С или букву Ю с массой менее 600*1024 кг.
· Выполните команды Данные/Фильтр/Автофильтр.
· Щелкните на кнопку со стрелкой в поле Планета. Выберите пункт Условие.
· В диалоговом окне задайте критерии и нажмите кнопку ОК.
Проверьте: Остались планеты Юпитер, Сатурн.
· Щелкните по кнопке со стрелкой в поле Масса. Выберите пункт Условие.
· В диалоговом окне задайте критерий по массе.
Проверьте: Остался Сатурн.
· Выполните команды меню Данные/Фильтр/Показать все.
Упражнение 2
С использованием Автофильтра самостоятельно:
· Осуществите поиск планет, имеющих диаметр менее 50 тыс км. И массу менее 4*1024 кг. (ответ: Меркурий, Марс, Плутон)
· Осуществите поиск планет, находящихся от Солнца на расстоянии не менее 100 млн. км, имеющих массу в диапазоне от 3*1024 кг до 500*1024 кг, а также имеющих не более 2 спутников (ответ: Венера, Земля, Нептун).
Отмените Автофильтр.
Расширенный фильтр
С помощью Расширенного фильтра можно фильтровать список так же, как и с помощью Автофильтра, но при этом не отображаются раскрывающиеся списки для столбцов. Вместо этого, над таблицей нужно вставить пустые строки с заголовками всех столбцов таблицы, куда вводятся условия отбора записей. Диапазон условий позволяет произвести фильтрацию при более сложных условиях отбора. Сложные условия, связанные союзом И, записываются в одной строке, а союзом ИЛИ – в разных
| Планета | Период(в земных годах) | Расстояние от Солнца(в млн.км) | Диаметр(в тыс.км) | Масса(*1024 кг) | Спутники |
| >5 | |||||
| <2 |
Представленное выше условие означает: планеты, имеющие период обращения более 5 лет ИЛИ менее двух спутников
Упражнение 3
С использованием расширенного фильтра осуществить поиск планет с периодом обращения более 10 земных лет и количеством спутников не менее 2.
· В первую строку скопируйте заголовки все столбцов таблицы.
· В ячейку В2 впишите «>10»
· В ячейку F2 впишите «>=2»
· Поставьте курсор в таблицу и выполните команду Данные/Фильтр/Расширенный фильтр.
· Выберите обработку: фильтровать список на месте.
· Исходный диапазон: вся исходная таблица.
· Диапазон условий: выделите те строки, в которых содержатся скопированные заголовки и условия (1 и 2 строки). Пустые строки не выделять.
· ОК.
Проверьте: остались планеты Юпитер, Сатурн, Уран, Нептун.
· Отмените фильтрацию.
Упражнение 4
С использованием расширенного фильтра самостоятельно:
· Найдите планеты, имеющие период обращения вокруг Солнца более 2 лет ИЛИ экваториальный диаметр не менее 50 тыс.км. (должно остаться 5 планет)
· Осуществите поиск планет, находящихся от Солнца на расстоянии более 1000 млн. км. И имеющих более 1 спутника, ИЛИ диаметром более 150 тыс. км. (должно остаться 3 планеты).
Сортировка данных
Команды Данные/Сортировка позволяют упорядочивать (сортировать) строки таблицы. Для выполнения сортировки необходимо выделить область таблицы или поместить в нее курсор, а затем выполнить команды Данные/Сортировка. При этом появится диалоговое окно. Необходимо выбрать название поля, по которому нужно производить сортировку.
Кроме того, необходимо указать метод сортировки: по возрастанию или по убыванию, и нажать кнопку ОК.
После указанных действий таблица будет упорядочена. Символьные поля сортируются в алфавитном порядке.
Упражнение 5
· Отсортируйте данные в таблице в порядке убывания количества спутников.
· Отсортируйте данные в таблице в алфавитном порядке названий планет.
· Отсортируйте данные в таблице в порядке увеличения расстояний от Солнца.
Задания для самостоятельного выполнения
Создайте книгу Задания.xls, в которой выполните описанные ниже задания.
Задание 1
Создайте лист Клиенты с таблицей, содержащей информацию о 20-ти клиентах фирмы:
| № | Фамилия | Имя | Отчество | Город | Улица | Дом | Квартира | Телефон |
| Задумайский | Артур | Владимирович | Киров | Труда | ||||
| Крупина | Екатерина | Владимировна | Москва | Ленина |
Примечание по заполнению таблицы. При вводе информации о клиентах предусмотрите, чтобы существовали клиенты с одинаковыми фамилиями, а также с одинаковыми фамилиями и именами.
· Отсортируйте записи в порядке возрастания одновременно по трем полям Фамилия, Имя, Отчество.
· Используя автофильтр, вывести информацию о проживающих в городе Кирове или Казани, с именем, начинающимся на букву «А», не имеющих телефона.
· Используя расширенный фильтр, отобразить информацию о клиентах женского пола, проживающих в Москве. Подсказка: для определение рола у клиента можно заметить, что у женщин отчество имеет окончание «на», следовательно условие будет – *на, где символ «*» обозначает любое количество символов.
Задание 2
Создайте лист Студенты с таблицей, содержащей информацию о 20 студентах:
| Фамилия | Имя | Отчество | Математика | Экономика | Английский | Культурология | Средний балл | Размер стипендии |
| Иванова | Лия | Павловна | ||||||
Примечание по заполнению таблицы. Средний балл вычислите, используя функцию. Стипендия начисляется в зависимости от среднего балла: если средний бал меньше 4, то стипендия 200 рублей, если больше или равен 4, то стипендия 500 рублей (используйте функцию ЕСЛИ).
· Отсортируйте данные в порядке убывания по полю Средний балл.
· Используя автофильтр, вывести студентов, чьи фамилии заканчиваются на –ова, -ева, и получивших по всем предметам оценки не ниже 4;
· Используя расширенный фильтр, выведите студентов, получивших, по крайней мере, одну двойку за сессию.