Расширенные параметры сортировки
Практическая работа №2
Использование списков Excel. Сортировка и фильтрация списков.
Связанные таблицы
Создание списков
Электронные таблицы Excel создавались в первую очередь для вычислений. Среди других задач, которые умеет решать Excel - работа с большими массивами сведений - списками, благодаря наличию в программе средств упорядочения и отбора данных.
Для того чтобы электронная таблица воспринималась системой как список, необходимо правильно его сформировать:
· список должен состоять из столбцов (полей) и строк (записей).
· каждый столбец должен иметь заголовок (имя поля) и содержать данные только одного типа (например, только числа или только текстовые значения и т.д.).
· В списке не должно пустых строк или столбцов.
Excel обладает мощными средствами для работы со списками:
· пополнение списка;
· фильтрация списка;
· сортировка списка;
· подведение промежуточных итогов;
· создание итоговой сводной таблицы на основе данных списка.
ЗАДАНИЕ 1. Сортировка списков
Упорядочение списков не только позволяет специальным образом разместить данные, но и значительно упрощает поиск информации. После сортировки записи отображаются в порядке, определенном значениями столбцов. Например, можно упорядочить список по алфавиту или по возрастанию цены и т.д.
1. Откройте из папки Задания по Excelфайл Страны.xlsx.
2. Вычислите сумму в столбце Площадь, используя кнопку Сумма, для вызова функции Автосуммирования.
3. Вычислите для каждой страны:
· плотность населения, чел./км2;
· долю (в %) от всего населения Земли (при вычислении используйте абсолютный адрес ячейки с числом, обозначающим количество населения Земли— 6091000).
4. Вычислите среднюю плотность населения в ячейке Е13, используя кнопку 
Вставить функциюв строке формул для вызова функции СРЗНАЧ.
5. Вычислите, сколько % населения Земли составляет население всех стран, приведенных в таблице.
6. Установите точность вычислений - 1 десятичный знак после запятой с помощью кнопки Уменьшить разрядность 
.
7. Временно удалите из таблицы две последние строки (13 и 14). Для этого выделите их и выберите команду контекстного меню Скрыть.
8. Скопируйте таблицу на этот же лист ниже. Для этого выделите таблицу, укажите на границу (появится крестик со 4-мя стрелочками) и далее, удерживая клавишу Ctrl, отбуксируйте ее вниз.
9. Восстановите скрытые строкив исходном экземпляре таблицы. Для этого выделите две строки, находящиеся выше и ниже удаленных строк и с помощью команды контекстного меню Отобразить восстановите строки.
10. Отформатируйте исходный экземпляр таблицы, вызвав с помощью команды контекстного меню диалоговое окно - Формат ячеек(вкладки Цвет, Граница, Заливка).
11. Скопируйте в буфер обмена исходный экземпляр таблицы.
12. Перейдите на Лист2 и вставьте находящуюся в буфере обмена таблицу (в активную ячейку А1).
13. Переименуйте Лист2 в лист Сортировка с помощью контекстного меню (команда Переименовать).
14. Скройте две последние строкитаблицы налисте Сортировкас помощью контекстного меню.
15. Выделите таблицу и скопируйте ее ниже на этом же листееще 4 раза (буксировкой при нажатой клавише Ctrl).
· Выполните следующие сортировки в таблицах на листе Сортировка.
· Во 2-м экземпляретаблицы выполните сортировку по данным столбца Плотность населения по убыванию. Выделите первую ячейку с данными в этом столбце и выберите командуСортировка и фильтр - Сортировать от максимального к минимальному. Первым в столбце окажется Израиль.
· В 3-м экземпляре таблицы расположите страны по алфавиту. Выделите первую ячейку с данными в этом столбце и выполните команды. Первойв столбце окажется Бразилия.
· В 4-м экземпляре таблицы проведите сортировку по убыванию по данным последнего столбца (%).Первым в столбце окажется Китай.
· В 5-м экземпляре таблицы отсортируйте данные по первому столбцу (по номерам). Получился исходный вариант таблицы?
16. Проанализируйте полученные результаты.
Расширенные параметры сортировки
При выборе команды Сортировка и фильтр - Настраиваемая сортировка появляется возможность сложной сортировки, то есть упорядочения данных по нескольким полям.
17. Укажите мышкой в любую ячейку в 5-м экземпляре таблицы на листе и выберите команду Сортировка и фильтр - Настраиваемая сортировка.
18. В полеСортировать по укажите поле Населениеи параметры сортировки- По возрастанию.
19. Далее щелкните по кнопке Добавить уровень в окне Сортировка.Появится еще одна строка для ввода условий сортировки. щелкните по кнопке Добавить уровень.
20. В поле Затем по выберите Плотностьи параметры сортировки- По возрастанию.Страны расположились по первому уровню - по возрастанию населения, а по второму уровню - и по возрастанию плотности населения.
Работа с фильтрами
Фильтрация (выборка данных) позволяет отобразить в таблице только те строки, содержимое ячеек которых отвечает заданному условию (или нескольким условиям). Эта операция может выполняться с помощью Автофильтраили Расширенного фильтра.
ЗАДАНИЕ 2. Использование Автофильтра
21. Скопируйте в буфер обмена исходный экземпляр таблицы на Листе1.
22. Перейдите на Лист3 и вставьте находящуюся в буфере обмена таблицу (в активную ячейку А1).
23. Создайте Лист4 и Лист5, Лист6, щелкнув по значку 
Вставить лист около ярлычков листов.
24. Скопируйте Лист3на Лист4, Лист5и Лист6через контекстное меню.
25. Переименуйте листы соответственно: Выборка1, Выбрка2, Выбрка3, Выбрка4 -с помощью контекстного меню (команда Переименовать).
26. Произведите фильтрацию записейтаблицы на листах 3, 4, 5 и 6 согласно следующим критериям.
• на листе Выборка1 выберите страны с площадью более 5000 тыс. км;
Для применения автофильтра (обычного фильтра) выберите команду Сортировка и фильтры - Фильтр.Рядом с названиями столбцов появятся стрелочки, с помощью которых можно задавать сортировку диапазона ячеек (по возрастанию, по убыванию, по цвету).
• на листе - Выборка2 выберите страны с населением меньше 150 млн чел.;
• на листе Выборка3 выберите страны с плотностью населения от 100 до 300 чел./км2;
• на листе Выборка4 выберите страны, население которых составляет более 2 % от всего населения Земли.
27. На листе Выборка4восстановитеисходный вариант таблицы и отменитережим фильтрации (Сортировка и фильтры - Фильтр).
Примечание. Другой вариант простой фильтрации - отображение в таблице определенных значений. По умолчанию установлен флажок Выделить все. Чтобы показывать только ячейки с определенными значениями, нужно снять флажки для тех значений, которые должны быть скрыты.
Расширенный фильтр
Расширенный фильтр позволяет осуществлять более сложную выборку данных электронной таблицы с заданием одновременно нескольких условий.
При работе Расширенный фильтр опирается на три области:
· Область данных(база данных);
· Область критериев поиска. Она формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки (или строк) критериев.
Если критерии находятся в одной строке, они работают по принципу И;
Если в разных - по принципу ИЛИ.
В критериях могут применяться шаблоны с ? и *.
Критерии могут быть вычисляемыми;
· Целевая область. Ее задание необязательно, так как существует параметр "оставить результаты отбора на месте".
Области могут быть расположены на одном листе, на разных листах и даже в разных файлах.
Порядок действий при использовании Расширенного фильтра:
1). Скопировать заголовки критериев поиска в свободное место на листе (копирование производится только для того, чтобы не допустить неточности в названиях полей, например: вместо русской С не набрать латинскую С.)
2). Заполнить строки критериев. Причем критерии, соединенные по И - в одной строке, соединенные по ИЛИ - в разных строках.
3). Скопировать заголовки интересующих полей в свободное место на листе (если отобранные записи будут находиться в отдельном месте).
4). Выделить любую ячейку диапазона и выбрать команду ДАННЫЕ - Дополнительно.
5). Выполнить настройки в диалоговом окне Расширенный фильтр:
Обработка- переключатель, указывающий, куда поместить результат поиска по критерию:
· фильтровать список на месте - оставить там же;
· скопировать результат в другое место -поместить в сформированную целевую область;
Исходный диапазон- диапазон ячеек для поиска.
Диапазон условий- содержит сформированные в пунктах 1 и 2 критерии отбора.