Способы фильтрации списка данных

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

В Excel предусмотрено три типа фильтров:

1. Автофильтр – для отбора записей по значению ячейки, по формату или в соответствии с простым критерием отбора.
2. Срезы – интерактивные средства фильтрации данных в таблицах.
3. Расширенный фильтр – для фильтрации данных с помощью сложного критерия отбора.

Автофильтр

1. Выделить одну ячейку из диапазона данных.
2. На вкладке Данные [Data] найдите группу Сортировка и фильтр [Sort&Filter].
3. Щелкнуть по кнопке Фильтр [Filter] .

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

1. Выбрать условие фильтрации.

Варианты фильтрации данных

• Фильтр по значению – отметить флажком нужные значения из столбца данных, которые высвечиваются внизу диалогового окна.
• Фильтр по цвету – выбор по отформатированной ячейке: по цвету ячейки, по цвету шрифта или по значку ячейки (если установлено условное форматирование).
• Можно воспользоваться строкой быстрого поиска
• Для выбора числового фильтра, текстового фильтра или фильтра по дате (в зависимости от типа данных) выбрать соответствующую строку. Появится контекстное меню с более детальными возможностями фильтрации:

1. При выборе опции Числовые фильтры появятся следующие варианты фильтрации: равно, больше, меньше, Первые 10… [Top 10…] и др.
2. При выборе опции Текстовые фильтры в контекстном меню можно отметить вариант фильтрации содержит. , начинается с… и др.
3. При выборе опции Фильтры по дате варианты фильтрации – завтра, на следующей неделе, в прошлом месяце и др.
4. Во всех перечисленных выше случаях в контекстном меню содержится пункт Настраиваемый фильтр… [Custom…], используя который можно задать одновременно два условия отбора, связанные отношением И [And] – одновременное выполнение 2 условий, ИЛИ [Or] – выполнение хотя бы одного условия.

Если данные после фильтрации были изменены, фильтрация автоматически не срабатывает, поэтому необходимо запустить процедуру вновь, нажав на кнопку Повторить [Reapply] в группе Сортировка и фильтр на вкладке Данные.

Отмена фильтрации

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

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

Чтобы быстро снять фильтрацию со всех столбцов необходимо выполнить команду Очистить на вкладке Данные

Срезы

Срезы – это те же фильтры, но вынесенные в отдельную область и имеющие удобное графическое представление. Срезы являются не частью листа с ячейками, а отдельным объектом, набором кнопок, расположенным на листе Excel. Использование срезов не заменяет автофильтр, но, благодаря удобной визуализации, облегчает фильтрацию: все примененные критерии видны одновременно. Срезы были добавлены в Excel начиная с версии 2010.

Создание срезов

В Excel 2010 срезы можно использовать для сводных таблиц, а в версии 2013 существует возможность создать срез для любой таблицы.

Для этого нужно выполнить следующие шаги:

Выделить в таблице одну ячейку и выбрать вкладку Конструктор [Design].

1. В диалоговом окне отметить поля, которые хотите включить в срез и нажать OK.
   

Форматирование срезов

1. Выделить срез.
2. На ленте вкладки Параметры [Options] выбрать группу Стили срезов [Slicer Styles], содержащую 14 стандартных стилей и опцию создания собственного стиля пользователя.

1. Выбрать кнопку с подходящим стилем форматирования.

Чтобы удалить срез, нужно его выделить и нажать клавишу Delete.

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

Расширенный фильтр предоставляет дополнительные возможности. Он позволяет объединить несколько условий, расположить результат в другой части листа или на другом листе и др.

Задание условий фильтрации

1. В диалоговом окне Расширенный фильтр выбрать вариант записи результатов: фильтровать список на месте [Filter the list, in-place] или скопировать результат в другое место [Copy to another Location].

1. Указать Исходный диапазон [List range], выделяя исходную таблицу вместе с заголовками столбцов.
2. Указать Диапазон условий [Criteria range], отметив курсором диапазон условий, включая ячейки с заголовками столбцов.
3. Указать при необходимости место с результатами в поле Поместить результат в диапазон [Copy to], отметив курсором ячейку диапазона для размещения результатов фильтрации.
4. Если нужно исключить повторяющиеся записи, поставить флажок в строке Только уникальные записи [Unique records only].

Источник

Фильтрация данных в диапазоне или таблице

Проверьте, как это работает!

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

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

Выберите любую ячейку в диапазоне данных.

Выберите Данные > Фильтр.

Выберите стрелку в столбце .

Выберите пункт Текстовые фильтры или Числовые фильтры, а затем — сравнение, например пункт Между.

Введите условия фильтрации и нажмите кнопку ОК.

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

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

Выберите стрелку в столбца, который вы хотите отфильтровать.

Снимите флажок (Выделить все) и установите флажки для тех элементов, которые вы хотите отобразить.

Нажмите кнопку ОК.

Стрелка заглавного изменится на фильтра. Щелкните этот значок, чтобы изменить или очистить фильтр.

Источник

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

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

Обычная (простая) сортировка

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

Быстрая организация данных в таблице возможна благодаря набору инструментов «Сортировка и фильтр». Эта кнопка находится на вкладке «Главная», в правой ее части. Опции сортировки соответствуют формату выбранных ячеек.

1. Проверим работу инструментов в действии. Для этого откроем существующую таблицу с большим количеством записей, выберем ячейку в столбце текстового формата и откроем меню сортировки и фильтра. Появятся две опции. Представим, что нужно рассортировать список имен по алфавиту. Нужно кликнуть по кнопке «Сортировка от А до Я». Записи распределятся в указанном порядке.

1

1. Попробуем развернуть таблицу в обратную сторону – от конца к началу. Вновь необходимо открыть меню сортировки, теперь выбираем функцию «Сортировка от Я до А».

2

1. Рассортировать числа тоже возможно, но опции для этого появляются только после выбора ячейки числового формата. Кликнем по одной из таких ячеек и откроем «Сортировку и фильтр». В меню появятся новые функции – «По возрастанию» и наоборот. Для дат заготовлены опции сортировки «От старых к новым» и в обратную сторону.

Пользовательская сортировка данных

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

1. Открываем меню «Сортировка и фильтр» и выбираем пункт «Настраиваемая сортировка» или «Пользовательская сортировка» (название зависит от версии программы).

3

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

Дополнительная информация! В случае, если таблица начинается с шапки, нужно поставить галочку в графе, расположенной в правом верхнем углу экрана.

Обратите внимание на пометку «Затем по». Она показывает, что приоритетными для сортировки таблицы являются условия первой строки.

1. Если все поля заполнены правильно, нажимаем «ОК».

5

Настройки можно изменить, выбрав одну из ячеек рассортированного диапазона и открыв окно пользовательской/настраиваемой сортировки.

Как настроить фильтр в таблице

Фильтры в Excel позволяют временно скрыть часть информации с листа и оставить только самое необходимое. Информация не пропадает навсегда – изменение настроек вернет ее на лист. Разберемся, как фильтровать строки электронной таблицы.

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

6

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

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

7

1. Получившуюся таблицу можно дополнительно рассортировать по другим данным, например по дате. Открываем меню фильтрации в столбце с датами и убираем несколько галочек. Когда фильтр установлен, нажимаем «ОК». Количество строк в таблице снова уменьшится.

8

Как убрать фильтр в таблице

Не обязательно расставлять галочки обратно в меню, чтобы восстановить прежний вид таблицы. Воспользуемся инструментами Microsoft Excel, чтобы отменить результаты фильтрации – есть два способа сделать это.

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

9

1. Другой метод подразумевает использование панели инструментов. Открываем меню «Сортировка и фильтр» на главной вкладке и находим пункт «Очистить». Он активен, когда диапазон ячеек подвергся фильтрации. Кликаем по этому пункту, и все настройки фильтров стираются.

10

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

11

Как создать «умную таблицу»

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

1. Выделяем всю таблицу целиком с помощью курсора.
2. Выбираем пункт «Форматировать как таблицу» на главной вкладке.
3. На экране появится меню стилей. Нужно выбрать любой из них – выбор не повлияет на результат.

12

1. Откроется окно для выбора координат. Поскольку таблица была выделена изначально, можно пропустить этот шаг и нажать кнопку «ОК».

13

1. Стиль будет применен к диапазону ячеек, в шапке появятся кнопки со стрелками для настройки фильтров.

Существует еще один метод создания «умной» таблицы:

1. Переходим во вкладку «Вставка» после выделения диапазона ячеек и нажимаем кнопку «Таблица».

14

1. Проверяем правильность координат в появившемся окне и нажимаем «ОК».

15

1. К таблице будет применен стандартный стиль, кнопки для настройки фильтрации и сортировки появятся в заголовках столбцов.

Как сделать фильтр в Excel по столбцам

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

Сортировка по нескольким столбцам в Excel

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

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

16

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

17

1. Заполняем несколько форм для постановки условий и жмем кнопку «ОК». Столбцы таблицы построятся в указанном порядке.

18

Важно! Количество уровней для сортировки ограничено только количеством столбцов или строк в таблице.

Автофильтр

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

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

19

1. Выбираем один из фильтров, заполняем поля в окне «Пользовательский автофильтр» и жмем «ОК». Настройки будут применены к диапазону ячеек.

20

Рассмотрим опцию «Настраиваемый фильтр». С ее помощью пользователи могут самостоятельно установить нужные настройки фильтрации.

1. Снова открываем текстовые или числовые фильтры. В конце списка находится нужный пункт, по которому следует кликнуть.

21

1. Заполняем поля – можно выбрать любой тип фильтра, походящий по формату ячеек, из списка и значения, существующие в диапазоне ячеек. После заполнения нажимаем кнопку «ОК». Если заполнение было непротиворечивым, настройки будут применены автоматически.

Стоит обратить внимание на пункты И/ИЛИ в окне настройки автофильтра. От них зависит то, как будут применены настройки – вместе или частично.

Срезы

Программа Microsoft Excel позволяет прикрепить к таблицам интерактивные элементы для сортировки и фильтрации – срезы. После выхода версии 2013-о года появилась возможность подключать срезы к обычным таблицам, а не только к сводным отчетам. Разберемся, как создать и настроить эти опции.

Создание срезов

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

Обратите внимание! Если ваша версия Microsoft Excel старше 2013-го года, составить срез для обычной таблицы будет невозможно. Функция применима только к отчетам в формате сводных таблиц.

1. На вкладке следует найти кнопку «Вставить срез» — кликаем по ней и переходим к новому открывшемуся окну.

22

1. Отмечаем галочками все поля, которые следует добавить в срез, и кликаем по кнопке «ОК».

23

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

Форматирование срезов

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

1. Открываем вкладку «Параметры» и находим раздел «Стили срезов». В нем находятся темы для срезов разных цветов. Выбираем любую из них – цвет не повлияет на эффективность работы элемента. Сразу после клика по стилю срез приобретет указанные цвета.

24

1. Также возможно изменить положение срезов на экране. Воспользуемся кнопками «Переместить вперед» и «Переместить назад» в разделе «Упорядочить». Необходимо выбрать один из срезов и нажать кнопку на панели инструментов. Теперь при перемещении по экрану срез будет оказываться поверх всех срезов или попадет под них.
2. Удаление срезов – несложная операция. Выберите лишнее окно и нажмите клавишу «Delete» на клавиатуре. Срез исчезнет с экрана и перестанет влиять на фильтрацию данных в таблице.

Заключение

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

Источник