Расширенные параметры сортировки

Практическая работа №2

Использование списков Excel. Сортировка и фильтрация списков.

Связанные таблицы

Создание списков

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

Для того чтобы электронная таблица воспринималась системой как список, необходимо правильно его сформировать:

· список должен состоять из столбцов (полей) и строк (записей).

· каждый столбец должен иметь заголовок (имя поля) и содержать данные только одного типа (например, только числа или только текстовые значения и т.д.).

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

Excel обладает мощными средствами для работы со списками:

· пополнение списка;

· фильтрация списка;

· сортировка списка;

· подведение промежуточных итогов;

· создание итоговой сводной таблицы на основе данных списка.

ЗАДАНИЕ 1. Сортировка списков

Упорядочение списков не только позволяет специальным образом разместить данные, но и значительно упрощает поиск информации. После сортировки записи отображаются в порядке, определенном значениями столбцов. Например, можно упорядочить список по алфавиту или по возрастанию цены и т.д.

1. Откройте из папки Задания по Excelфайл Страны.xlsx.

2. Вычислите сумму в столбце Площадь, используя кнопку Сумма, для вызова функции Автосуммирования.

3. Вычислите для каждой страны:

· плотность населения, чел./км²;

· долю (в %) от всего населения Земли (при вычислении используйте абсолютный адрес ячейки с числом, обозначающим количество населения Земли— 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 чел./км²;

• на листе Выборка4 выберите страны, население которых составляет более 2 % от всего населения Земли.

27. На листе Выборка4восстановитеисходный вариант таблицы и отменитережим фильтрации (Сортировка и фильтры - Фильтр).

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

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

Расширенный фильтр позволяет осуществлять более сложную выборку данных электронной таблицы с заданием одновременно нескольких условий.

При работе Расширенный фильтр опирается на три области:

· Область данных(база данных);

· Область критериев поиска. Она формируется из строки заголовков полей, которые будут ключевыми при отборе записей, и строки (или строк) критериев.

Если критерии находятся в одной строке, они работают по принципу И;

Если в разных - по принципу ИЛИ.

В критериях могут применяться шаблоны с ? и *.

Критерии могут быть вычисляемыми;

· Целевая область. Ее задание необязательно, так как существует параметр "оставить результаты отбора на месте".

Области могут быть расположены на одном листе, на разных листах и даже в разных файлах.

Порядок действий при использовании Расширенного фильтра:

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

2). Заполнить строки критериев. Причем критерии, соединенные по И - в одной строке, соединенные по ИЛИ - в разных строках.

3). Скопировать заголовки интересующих полей в свободное место на листе (если отобранные записи будут находиться в отдельном месте).

4). Выделить любую ячейку диапазона и выбрать команду ДАННЫЕ - Дополнительно.

5). Выполнить настройки в диалоговом окне Расширенный фильтр:

Обработка- переключатель, указывающий, куда поместить результат поиска по критерию:

· фильтровать список на месте - оставить там же;

· скопировать результат в другое место -поместить в сформированную целевую область;

Исходный диапазон- диапазон ячеек для поиска.

Диапазон условий- содержит сформированные в пунктах 1 и 2 критерии отбора.