§16 Сортировка и фильтрация данных

16.1. Сортировка данных

Электронные таблицы позволяют производить сортировку и фильтрацию данных. Для этого данные организуют специальным образом:

  • в одном столбце размещают данные только одного типа (текст, число, дата и т. д.);
  • столбцы имеют заголовки;
  • каждая строка представляет собой запись о каком-либо одном объекте.
Сортировка — упорядочение данных по возрастанию или по убыванию их значений.

Сортировка позволяет переставить строки в таблице так, чтобы в определенном столбце они располагались по порядку. Числовые данные могут располагаться в порядке возрастания или убывания, а текстовые — в алфавитном порядке или в порядке, обратном алфавитному (пример 16.1). По возрастанию или убыванию можно располагать данные формата дат и времени.

Инструменты для выполнения сортировки располагаются на вкладках Главная и Данные (пример 16.2). Они дублируют друг друга.

Для сортировки нужно установить курсор в одну из ячеек столбца и выбрать (Сортировка по возрастанию) или (Сортировка по убыванию). Строки таблицы будут переупорядочены согласно выбранному критерию сортировки. Параметры сортировки выделенного диапазона задают с помощью пиктограммы — настраиваемая сортировка. В открывшемся окне (пример 16.3) можно задавать уровни сортировки и для каждого уровня определять порядок.

Уровни сортировки необходимы, когда в каком-либо столбце имеются одинаковые данные. Тогда новый уровень позволит распределить эти данные по другому критерию.

16.2. Фильтрация данных

Фильтр — средство отображения только тех строк таблицы, которые соответствуют заданным условиям.

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

Для установки Фильтра нужно выделить заголовки столбцов и выбрать команду Фильтр на вкладке Главная или Данные (команда располагается вместе с командой Сортировка). Справа от названий столбцов в фильтруемом списке появляются кнопки со стрелками (пример 16.4).
В отличие от сортировки фильтр не меняет порядок записей в списке. При фильтрации (применении фильтра) временно скрываются строки, которые не требуется отображать. Строки, отобранные при фильтрации в Excel, можно редактировать, форматировать, выводить на печать, не изменяя порядок строк и не перемещая их.
Для установки параметров фильтра нужно нажать на кнопку рядом с тем столбцом, по которому фильтруются значения. В выпадающем списке выбрать нужные значения или задать условие (пример 16.5). Для числовых и текстовых данных применяются разные условия фильтрации (пример 16.6).
Рассмотрим подробнее фильтрацию данных по различным критериям.

Фильтр по конкретному значению

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

  1. В столбце Факультатив нажать на кнопку .
  2. В открывшемся списке снять все выделения — снять флажок со строки (Выделить все).
  3. Выбрать информатика.

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

Фильтр «Первые10…»

Условие отбора «Первые 10…» позволяет выбрать из общего списка заданное количество строк, содержащих наибольшие или наименьшие числовые значения.

Пример 16.8. В таблице «Страны Европы» выбрать 5 стран с наименьшей площадью.

  1. В столбце Площадь (тыс. кв. км) нажать на кнопку .
  2. В открывшемся списке выбрать Числовые фильтры.
  3. Выбрать первые 10.
  4. Установить параметры в окне.


Наложение условия по списку

Более сложные критерии отбора устанавливаются в окне Пользовательский автофильтр (пример 16.9). Оно открывается при выборе текстовых фильтров начинается с…, заканчивается на…, содержит…, не содержит…, а также большинства числовых фильтров, кроме первые 10 и выше (ниже) среднего.

Поля под номерами 1 и 2 предназначены для задания условий отбора из выпадающего списка, показанного в примере 16.10.

Поля под номером 4 и 5 позволяют задавать значение, с которым производится сравнение. Значение выбирается из выпадающего списка, содержащего все возможные значения выбранного поля, или вводится с клавиатуры (пример 16.11). Составные условия отбора образуются с использованием логических
операций «И» и «ИЛИ». Выбор операции производится с помощью соответствующих переключателей (номер 3 в примере 16.9).

Составные условия фильтрации

Пример 16.12. В таблице «Страны Европы» выбрать страны, площадь которых больше 100 и меньше 350 тыс. кв. км.

  1. В столбце Площадь (тыс. кв. км) нажать на кнопку .
  2. В открывшемся списке выбрать Числовые фильтры.
  3. Выбрать между… .
  4. Установить параметры в окне.

Пользовательский автофильтр

Пример 16.13. В таблице «Страны Европы» выбрать страны, названия которых заканчиваются на «ия» или на «ь».

  1. В столбце Название нажать на кнопку .
  2. В открывшемся списке выбрать Текстовые фильтры.
  3. Выбрать заканчивается на… .
  4. Установить параметры в окне.

Восстановление данных

Для восстановления данных после фильтрации существует несколько способов. Для сброса фильтра в одном столбце достаточно открыть параметры фильтра и выбрать строку (Выделить все). Чтобы отменить все фильтры, нужно выполнить команду . Ее можно выбрать в выпадающем списке команды Сортировка и фильтр на вкладке Главная или в разделе Сортировка и
фильтр на вкладке Данные.

Фильтрация по нескольким столбцам

Фильтры в таблице можно устанавливать для нескольких столбцов. Применяется фильтрация последовательно, в том порядке, в котором устанавливаются фильтры.

Пример 16.14. В таблице «Страны Европы» выбрать страны, названия которых заканчиваются на «ия» и площадь которых больше средней.

  1. В столбце Название нажать на кнопку .
  2. В открывшемся списке выбрать Текстовые фильтры.
  3. Выбрать заканчивается на… .
  4. Установить параметры в окне Пользовательский автофильтр.
  5. В столбце Площадь нажать на кнопку .
  6. В открывшемся списке выбрать Числовые фильтры.
  7. Выбрать выше среднего.
  8. Установить параметры в окне

Пользовательский автофильтр.

 Пример 16.1. Сортировка данных.
Сортировка фамилий по алфавиту:Сортировка по убыванию результатов прыжков в длину:Пример 16.2. Инструменты сортировки на вкладке Главная:Инструменты сортировки на вкладке Данные:Пример 16.3. Окно Сортировка:

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

Пример 16.4. Таблица, у которой установлен Фильтр.

Пример 16.5. Параметры фильтра
для столбца Столица.

Пример 16.6. Условия отбора числовых и текстовых фильтров:

Пример 16.7. Параметры отбора:

Результат:

Пример 16.8. Параметры отбора:

Результат:

Пример 16.9. Окно Пользовательский автофильтр.

Пример 16.10. Выпадающий список с условиями отбора.

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

Пример 16.12. Параметры отбора:

Результат:

Пример 16.13. Параметры отбора:

Результат:

Установленные фильтры сохраняются вместе с таблицей. Если навести указатель мыши на значок , то можно увидеть, какой фильтр установлен для столбца.

Параметры фильтра можно просмотреть, если выбрать команду Настраиваемый фильтр:

Пример 16.14. Установка параметров отбора.
По столбцу Название:

По столбцу Площадь:

Результат:

 

 
1.  Что такое сортировка?
2.  В каком порядке Excel позволяет сортировать числовые данные? Текстовые?
3.  В каких случаях задают уровни сортировки?
4.  Что такое фильтр?
5.  Как установить фильтр?
6.  Какие текстовые фильтры можно применять к данным?
7.   Какие числовые фильтры можно применять к данным?

Упражнения

1.   Откройте электронную таблицу «Озера.xlsx». Скопируйте исходную таблицу 6 раз
на разные листы книги и для каждой копии выполните одну сортировку.

  1. Отсортируйте таблицу по областям в порядке возрастания значений.
  2. Отсортируйте таблицу по названиям озер в порядке убывания значений.
  3. Отсортируйте таблицу по районам в порядке возрастания значений.
  4. Отсортируйте таблицу по площади в порядке убывания значений.
  5. Отсортируйте таблицу по глубине озер в порядке возрастания значений.
  6. Выполните многоуровневую сортировку. Отсортируйте исходную таблицу сначала по областям, затем по районам, затем по озерам в порядке возрастания значений.

2.   Откройте электронную таблицу «Страны.xlsx». Скопируйте таблицу 7 раз на разные листы. Найдите с помощью фильтра записи, удовлетворяющие перечисленным условиям.

  1. Страна, в которой протекает река длиной 600 км.
  2. Страны, названия которых начинаются на букву «И».
  3. Страны, названия столиц которых заканчиваются на букву «м».
  4. Страны, самая высокая точка которых >1000 и <300 м.
  5. Страны, население которых <10 или >50 млн чел.
  6. Найдите первые 5 самых высоких точек Европы.
  7. *  Страны с количеством населения >7 млн чел., названия которых заканчиваются на «ия», площадь которых меньше средней.

 

Проверь себя