ИНФОРМАТИКА · 9 КЛАСС
Электронные таблицы · Интерактивный конспект

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

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

Попробовать на модели ↓
Разбираемся в теме

Основные пункты параграфа

1

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

Упорядочение строк по значениям столбца.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Сортировка записей

Выберите ключ сортировки и порядок. Строки переставляются целиком: имя, класс и результат одной записи остаются вместе.

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

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

В примере 16.1 таблица «Соревнования легкоатлетов» отсортирована по убыванию результатов прыжков в длину:

ФамилияПрыжок в длину, мБег на 60 м, с
Павлов А.2,1810,3
Лемешко К.2,129,1
Власов С.2,19,9
Сергеев А.2,0310,2
Комаров Ю.1,838,2
Усов С.1,818,5
Корнев Н.1,810,8
Жук Р.1,788,3
Ткаченко Т.1,768,6
Бажков С.1,748,9

Вместе с результатом прыжка переместились фамилия и результат бега: строка таблицы — одна запись, и она переставляется целиком. При сортировке фамилий по алфавиту первым оказывается Бажков С., последним — Усов С.

Главная мысль: Сортировка переставляет строки таблицы так, чтобы значения выбранного столбца шли по возрастанию или по убыванию.
2

Инструменты и уровни сортировки

Кнопки сортировки и окно Сортировка.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Сортировка по разным ключам

Сравните сортировку по результату, по имени и по классу. При ключе «Класс, затем результат» записи одного класса дополнительно упорядочены по результату — это второй уровень сортировки.

Инструменты сортировки располагаются на вкладке Главная (кнопка Сортировка и фильтр) и на вкладке Данные (раздел Сортировка и фильтр). Они дублируют друг друга. Для сортировки устанавливают курсор в одну из ячеек столбца и выбирают Сортировка от А до Я (по возрастанию) или Сортировка от Я до А (по убыванию). Строки таблицы будут переупорядочены согласно выбранному критерию.

Параметры сортировки выделенного диапазона задают командой Настраиваемая сортировка…. В открывшемся окне Сортировка можно задавать уровни сортировки (кнопки Добавить уровень, Удалить уровень, Копировать уровень) и для каждого уровня определять порядок. Флажок «Мои данные содержат заголовки» не даёт строке заголовков участвовать в сортировке.

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

УровеньСтолбецПорядок
Сортировать поОбластьОт А до Я
Затем поРайонОт А до Я
Затем поГлубина, мПо убыванию

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

Главная мысль: Уровни сортировки нужны, когда в столбце есть одинаковые данные: следующий уровень упорядочивает их по другому столбцу.
3

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

Показать только строки, отвечающие условию.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Фильтр по классу и результату

Выберите класс и минимальный результат. Неподходящие строки скрываются, но не удаляются: показатель «Скрыто» говорит, сколько записей временно не видно.

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

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

Числовые фильтрыТекстовые фильтры
равно…, не равно…равно…, не равно…
больше…, больше или равно…, меньше…, меньше или равно…, между…начинается с…, заканчивается на…
Первые 10…, Выше среднего, Ниже среднегосодержит…, не содержит…
Настраиваемый фильтр…Настраиваемый фильтр…

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

Фильтр «Первые 10…» выбирает из списка заданное количество строк с наибольшими или наименьшими числовыми значениями. Пять стран Европы с наименьшей площадью: Числовые фильтры → Первые 10… → показать наименьших 5 элементов списка. Результат: Дания, Литва, Молдова, Нидерланды, Швейцария.

Главная мысль: Фильтр временно скрывает строки, не отвечающие условию, и не меняет порядок записей.
4

Составные условия и восстановление данных

Пользовательский автофильтр, И, ИЛИ, несколько столбцов.

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Два условия через И

Модель соединяет условие по классу и условие по результату операцией И: видны только записи, которые удовлетворяют обоим условиям. Верните «Все» и минимум 0, чтобы снова увидеть всю таблицу.

Более сложные критерии отбора задают в окне Пользовательский автофильтр. Оно открывается при выборе текстовых фильтров начинается с…, заканчивается на…, содержит…, не содержит…, а также большинства числовых фильтров, кроме Первые 10… и Выше (Ниже) среднего. В левых полях выбирают условия отбора (равно, не равно, больше, больше или равно, меньше…), в правых — значение для сравнения: его выбирают из списка значений столбца или вводят с клавиатуры. Знак «?» обозначает один любой знак, знак «*» — последовательность любых знаков.

Составные условия образуются с помощью логических операций И и ИЛИ — их выбирают переключателями в окне. Страны площадью от 100 до 350 тыс. кв. км (числовой фильтр между…: «больше или равно 100» И «меньше или равно 350»): Беларусь, Болгария, Великобритания, Греция, Италия, Польша. Страны, названия которых заканчиваются на «ия» ИЛИ на «ь» (текстовый фильтр заканчивается на…): Австрия, Беларусь, Германия, Испания, Франция, Швеция и др.

Фильтры можно устанавливать для нескольких столбцов; применяются они последовательно, в том порядке, в котором установлены. Страны, названия которых заканчиваются на «ия» и площадь которых выше средней: Великобритания, Германия, Испания, Италия, Норвегия, Франция, Швеция.

Главная мысль: Сложные условия задают в окне Пользовательский автофильтр, объединяя их операциями И и ИЛИ.
Интерактивная практика

Исследуйте и примените

Проверьте на модели, что сортировка меняет порядок записей, а фильтр — только их видимость. Затем выполните задание по таблице «Соревнования легкоатлетов».

НАБЛЮДАЙТЕ И ПРОБУЙТЕ

Лаборатория: сортировка и фильтр вместе

Задайте условие фильтра и ключ сортировки. Определите, сколько записей видно и в каком порядке они стоят.

Соберём главное

Шесть выводов

01

Сортировка переставляет строки таблицы так, чтобы значения выбранного столбца шли по возрастанию или по убыванию.

02

Уровни сортировки нужны, когда в столбце есть одинаковые данные: следующий уровень упорядочивает их по другому столбцу.

03

Фильтр временно скрывает строки, не отвечающие условию, и не меняет порядок записей.

04

Сложные условия задают в окне Пользовательский автофильтр, объединяя их операциями И и ИЛИ.

05

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

06

Чтобы вернуть все строки, выбирают (Выделить все) в параметрах столбца или команду Очистить.

Самопроверка

Тест: 10 вопросов

Один верный ответ. За ответ — 1 балл. Откройте и проверьте себя.

1. Что такое сортировка?

2. В каком порядке можно сортировать текстовые данные?

3. В каких случаях задают уровни сортировки?

4. Что такое фильтр?

5. Как установить фильтр?

6. Что происходит со строками, которые не прошли фильтр?

7. Какое условие есть среди числовых фильтров, но нет среди текстовых?

8. Какой фильтр выбрать, чтобы найти 5 стран с наименьшей площадью?

9. Что обозначает знак «*» в окне Пользовательский автофильтр?

10. Какая операция нужна для отбора стран, названия которых заканчиваются на «ия» или на «ь»?

ВыводыТестВ началоВсе параграфы
↑

Загрузка прогресса…

↑