Сортировка и фильтрация данных в книге Excel пакета Office 365

Сортировка и фильтрация применяются во многих случаях, когда данные, имеющиеся на листе книги, нужно упорядочить по определенному критерию, либо найти данные, соответствующие заданным условиям. Мы рассмотрим использование сортировки и фильтрации на конкретном примере. Обратите внимание на таблицу (рис. 5.22).

Рис. 5.22. Пример таблицы

Рис. 5.22. Пример таблицы

В данной таблице вы видите список людей (пусть это будут сотрудники предприятия). В нашем примере список небольшой, но на самом деле он может содержать сотни и тысячи строк (и в этом случае инструменты сортировки и фильтрации просто необходимы). Мы будем работать со списком, содержащим сведения о нескольких сотрудниках. В таблице приведены порядковый номер строки, фамилия, имя, отчество и дата рождения каждого сотрудника. Обратите внимание, к ячейкам E2:E7 применен формат Дата. Это позволяет выводить дату, набранную в формате дд.мм.гггг в более удобном виде. Кроме того, это необходимо, если мы планируем выполнять сортировку и фильтрацию данных по дате.

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

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

Рассмотрим несколько примеров. В первом примере мы отсортируем фамилии в таблице по алфавиту.

Рис. 5.23. Диалоговое окно Обнаружены данные вне указанного диапазона

Рис. 5.23. Диалоговое окно Обнаружены данные вне указанного диапазона

  1. Выделите столбец B, то есть столбец, в котором указаны фамилии (также вы можете выделить только диапазон ячеек, в которых указаны фамилии).
  2. Перейдите на вкладку Данные на ленте.
  3. На вкладке Данные нажмите кнопку Сортировка от А до Я, расположенную в группе Сортировка и фильтр. На экране появится диалоговое окно Обнаруженные данные вне указанного диапазона (рис. 5.23). Диалоговое окно Обнаруженные данные вне указанного диапазона содержит переключатель, с помощью которого выбирается режим сортировки. Переключатель имеет два положения:
    • Автоматически расширить выделенный диапазон. Программа Excel обнаружила, что в таблице есть связанные данные. Каждая строка содержит данные, связанные с определенным сотрудником в списке. Этот режим отсортирует таблицу, не разрывая связи между данными, то есть все строки таблицы поменяются местами согласно условиям сортировки. И каждому сотруднику будут соответствовать его имя, отчество и дата рождения. Данный режим используется наиболее часто, поэтому он выбран по умолчанию.
    • Сортировать в пределах указанного выделения. При выборе этого режима будут отсортированы только те данные, которые находятся в выделенной области. В нашем примере — только фамилии, в то время как остальные данные (имя, отчество, номер и дата рождения) останутся на своих местах. Это приведет к нарушению связей в записях, в результате напротив фамилии сотрудника могут оказаться чужое имя, отчество и некорректная дата рождения.
  4. Убедитесь, что переключатель в диалоговом окне Обнаружены данные вне указанного диапазона находится в положении Автоматически расширить выделенный диапазон.
  5. Нажмите кнопку Сортировка. Диалоговое окно закроется, а данные в таблице будут отсортированы по полю Фамилия в алфавитном порядке (рис. 5.24). Обратите внимание, ячейка с заголовком Фамилия не подверглась сортировке, несмотря на то, что была выделена вместе с остальными ячейками столбца. Программа Excel в большинстве случаев корректно распознает ячейки, служащие заголовками столбцов таблицы, и не включает их в сортировку.
Рис. 5.24. Результат сортировки по фамилии

Рис. 5.24. Результат сортировки по фамилии

Точно также выполняется и сортировка в обратном порядке. При нажатии кнопки Сортировка от Я до А данные в таблице упорядочатся в обратном алфавитном порядке.

Сортировка выполняется одинаково для любых видов данных. Чтобы выполнить сортировку, следует выделить диапазон или столбец, по которому нужно упорядочить данные, и нажать кнопку Сортировка от А до Я или Сортировка от Я до А. Название кнопок не меняется вне зависимости от того, какие данные выделены на листе (текст, числа или даты). В качестве примера отсортируем данные по дате рождения.

  1. Выделите столбец Дата рождения.
  2. Нажмите кнопку Сортировка от Я до А на вкладке Данные.
  3. Убедитесь, что в появившемся диалоговом окне переключатель установлен в верхнее положение.
  4. Нажмите кнопку Сортировка (рис. 5.25).
Рис. 5.25. Результат сортировки по дате рождения

Рис. 5.25. Результат сортировки по дате рождения

Записи о сотрудниках в таблице были отсортированы по дате рождения от самого молодого до самого старшего. При нажатии кнопки Сортировка от А до Я сортировка будет выполнена в обратном порядке (от самого старшего до самого молодого). Сортировка всех остальных видов данных выполняется точно так же. Попробуйте самостоятельно отсортировать данные в таблице по имени и порядковому номеру.

В сложных таблицах или таблицах, где данные в одном и том же столбце могут повторяться, программа позволяет выполнять многоуровневую сортировку . Такая сортировка может охватывать несколько столбцов, упорядочивая повторяющиеся записи по данным, указанным в других столбцах. В нашей таблице есть два однофамильца. В результате выполнения обычной сортировки по фамилии запись Иванов Андрей оказалась под записью Иванов Валерий. Это не совсем правильно. Согласно принятым правилам сортировки, совпадающие данные должны быть отсортированы по следующему полю. Мы можем задать правила многоуровневой сортировки самостоятельно.

Рис. 5.26. Диалоговое окно Сортировка

Рис. 5.26. Диалоговое окно Сортировка

  1. Выделите столбец Фамилия.
  2. Нажмите кнопку Сортировка в группе Сортировка и фильтр на вкладке Данные.
  3. Убедитесь, что переключатель в появившемся диалоговом окне установлен в верхнее положение.
  4. Нажмите кнопку Сортировка. На экране появится диалоговое окно Сортировка (рис. 5.26). В диалоговом окне Сортировка задаются правила многоуровневой сортировки. Здесь выбираются последовательность сортировки столбцов и направление сортировки для каждого столбца.
  5. В левом раскрывающемся списке выберите пункт Фамилия.
  6. В среднем раскрывающемся списке выберите пункт Значения.
  7. В правом раскрывающемся списке выберите пункт От А до Я. Мы задали правило сортировки столбца Фамилия. Но нам нужно добавить в сортировку еще столбец Имя. Сортировка в данном столбце будет выполняться только для данных, которые дублируются в столбце Фамилия.
  8. В диалоговом окне Сортировка нажмите кнопку Добавить уровень. Появится вторая строка с раскрывающимися списками.
  9. В левом раскрывающемся списке выберите пункт Имя (диалоговое окно Сортировка берет конкретные имена полей прямо из строки заголовка таблицы, если установлен флажок Мои данные содержат заголовки).
  10. В среднем раскрывающемся списке выберите пункт Значения.
  11. В правом раскрывающемся списке выберите пункт От А до Я (рис. 5.27). Мы добавили второй уровень сортировки. Теперь данные будут сортироваться в таблице согласно правилу, заданному в верхней строке. Если столбец первого уровня сортировки содержит повторяющиеся данные (а в нашем примере есть две одинаковые фамилии), будет выполнена дополнительная сортировка по столбцу, указанному во втором правиле.
  12. Нажмите кнопку ОК в диалоговом окне Сортировка. Диалоговое окно закроется, а данные будут отсортированы согласно заданным правилам (рис. 5.28).
Рис. 5.27. Добавлен второй уровень сортировки

Рис. 5.27. Добавлен второй уровень сортировки

Рис. 5.28. Результат выполнения двухуровневой сортировки по фамилии и имени

Рис. 5.28. Результат выполнения двухуровневой сортировки по фамилии и имени

Теперь сортировка выполнена корректно. Записи в таблице расположены в алфавитном порядке по полю Фамилия, при этом запись Иванов Андрей находится над записью Иванов Валерий. В сложных таблицах можно применять любое количество уровней сортировки, но на практике двух-трех обычно хватает.

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

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

  1. Выделите ячейку Стаж.
  2. Нажмите кнопку Фильтр в группе Сортировка и фильтр на вкладке Данные. Кнопка зафиксируется в нажатом положении, а во всех ячейках с заголовками колонок появятся кнопки сортировки и фильтрации.
  3. Нажмите кнопку в ячейке Стаж. Появится меню.
  4. В появившемся меню выберите команду Числовые фильтры, а затем — вложенную команду Больше (рис. 5.29). Появится диалоговое окно Пользовательский автофильтр (рис. 5.30).
  5. Убедитесь, что в левом верхнем раскрывающемся списке выбран пункт Больше.
  6. В раскрывающемся списке, расположенном справа, введите 3.
  7. Нажмите кнопку ОК. Диалоговое окно закроется, и в таблице останутся только записи о сотрудниках, чей стаж превышает 3 года (рис. 5.31).
Рис. 5.29. Выбор фильтра

Рис. 5.29. Выбор фильтра

Рис. 5.30. Диалоговое окно Пользовательский автофильтр

Рис. 5.30. Диалоговое окно Пользовательский автофильтр

Рис. 5.31. Результат фильтрации по столбцу Стаж

Рис. 5.31. Результат фильтрации по столбцу Стаж

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

  1. Нажмите кнопку фильтрации в ячейке Стаж.
  2. В появившемся меню выберите команду Удалить фильтр из столбца «Стаж». Фильтр будет удален, в результате чего в таблице вновь отобразятся все строки.

Теперь применим другой фильтр. Предположим, нам нужно найти сотрудников, родившихся в период между 1980 и 1986 годами.

  1. Нажмите кнопку фильтрации в ячейке Дата рождения.
  2. В появившемся меню выберите команду Фильтр по дате, а затем — вложенную команду Между. Появится диалоговое окно Пользовательский автофильтр (рис. 5.32).
Рис. 5.32. Фильтр с двумя связанными условиями

Рис. 5.32. Фильтр с двумя связанными условиями

В данном случае в диалоговом окне необходимо задать два условия (впрочем, они уже заданы по умолчанию). В верхней строке выбрано условие После или равно. Здесь нужно задать начальную дату периода. В нижней строке выбрано условие До или равно. Здесь необходимо задать конечную дату периода. Оба условия связываются логическим оператором И. Данный оператор подразумевает выполнение обоих условий. То есть мы задаем фильтр, при котором в таблице должны остаться записи о сотрудниках, родившихся после указанной даты И до указанной даты. Если использовать оператор ИЛИ, фильтр будет применен к записям, отвечающим как первому, так и второму условию.

  1. Убедитесь, что в раскрывающемся списке в левой части первой строки выбрано условие После или равно.
  2. В поле, находящемся справа, введите дату 01.01.1980.
  3. Убедитесь, что переключатель выбора оператора установлен в положение И.
  4. Убедитесь, что в первом раскрывающемся списке второй строки выбрано условие До или равно.
  5. В поле, расположенном справа, введите дату 31.12.1986 (рис. 5.33).
  6. Нажмите кнопку ОК. Диалоговое окно закроется, а фильтр с заданными условиями будет применен к таблице (рис. 5.34).
Рис. 5.33. Условия фильтра заданы

Рис. 5.33. Условия фильтра заданы

Рис. 5.34. Результат фильтрации по дате рождения

Рис. 5.34. Результат фильтрации по дате рождения

Как видите, в таблице отображаются записи, отвечающие условиям фильтра, то есть, мы видим записи о сотрудниках, родившихся в период с 1 января 1980 года до 31 декабря 1986 года.

  1. Нажмите кнопку фильтрации в ячейке Дата рождения.
  2. В появившемся меню выберите команду Удалить фильтр из столбца «Дата рождения». Фильтр будет удален, в результате чего в таблице вновь отобразятся все строки.

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

    1. Нажмите кнопку фильтрации в ячейке Имя.
    2. В появившемся меню выберите команду Текстовые фильтры, а затем — вложенную команду Равно. Появится диалоговое окно Пользовательский автофильтр.
    3. Убедитесь, что в первом раскрывающемся списке верхней строки выбрано условие Равно.
    4. В поле, расположенном справа, введите Олег (рис. 5.35).
    5. Нажмите кнопку ОК. Диалоговое окно закроется, и фильтр будет применен к таблице (рис. 5.36). Удалите ранее примененный фильтр.
    Рис. 5.35. Условие текстового фильтра

    Рис. 5.35. Условие текстового фильтра

    Рис. 5.36. Результат фильтрации по имени

    Рис. 5.36. Результат фильтрации по имени

    Вы наверняка заметили, что в меню Числовые фильтры, Фильтры по дате и Текстовые фильтры содержится множество команд. Эти команды определяют условие фильтра. Почти во всех случаях при выборе условия появляется диалоговое окно Пользовательский автофильтр, в котором настраиваются параметры условия. Левые раскрывающиеся списки содержат дополнительные условия. Например, при выборе условия Больше для числового фильтра в диалоговом окне Пользовательский автофильтр можно изменить условие на Равно, Не равно, Больше или равно, Меньше, Меньше или равно и т. д. Для настройки сложных фильтров используйте логические операторы И и ИЛИ. При выборе оператора И оба заданных условия должны выполняться. Оператор ИЛИ допускает выполнение одного из заданных условий.

    В таблицах с большим количеством данных можно применять многоуровневые фильтры , настраивая условия для разных столбцов. Например, в нашей таблице фильтрация позволяет отобразить всех сотрудников, чей стаж превышает 2 года и родившихся после 1983 года. Для этого вы применяете соответствующий фильтр в столбце Стаж и, не удаляя его, применяете фильтр по дате рождения в столбце Дата рождения. То есть вы можете применять фильтр к любым колонкам таблицы, не сбрасывая фильтры в других столбцах. Новые фильтры будут искать данные в записях, оставшихся после предыдущих фильтраций. Чтобы отобразить все строки, удалите фильтры во всех столбцах таблицы.

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

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

Наверх