Как работать с фильтрами в Google Таблицах и Excel: пошагово разбираем 7 способов

Обычно таблицы с данными большие и содержат много информации. Но иногда нам нужны только конкретные данные.

Например, у нас есть большая сводная таблица с данными по продажам, но нам нужно изучить продажи одного менеджера. Или у нас таблица с результатами email-рассылок — а нам нужно проанализировать письма для одного сегмента получателей.

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

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

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

В Google Таблицах можно отфильтровать данные четырьмя способами.

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

Способ 1. Простой фильтр

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

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

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

Выберем для примера данные по рассылкам с Open Rate от 5 до 20%.

Нажимаем «ОК». Теперь в таблице остались только выбранные данные.

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

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

Способ 2. Режим фильтрации

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

Чтобы включить режим, откройте меню «Данные» и выберите строчку «+ Создать фильтр» (именно этот, с плюсиком).

Имена строк и столбцов окрасятся в светло-зеленый.

Как и в предыдущем способе, около заголовков столбцов появятся значки фильтрации — но эти фильтры увидите только вы. 

Впрочем, вы можете сохранить их для всех пользователей. 

Для этого нажмите зеленую кнопку в правой части экрана — «Сохранить фильтр». Появится диалоговое окно. Напишите собственное название фильтра или оставьте имя по умолчанию: «Фильтр 1».

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

Чтобы снять фильтрацию с таблицы, откройте меню «Данные», выберите «Изменить фильтр» и «Закрыть фильтр». 

Чтобы совсем удалить этот фильтр, откройте «Данные», выберите «Параметры просмотра» и «Удалить фильтр».

Способ 3. Срез

Срезы — удобный и наглядный инструмент для работы с фильтрацией. Они особенно хорошо подходят, когда в таблице много взаимосвязанных данных. Если мы выберем значение в одном блоке среза, в других блоках мы увидим только доступные для выборки данные.

Например, если мы выберем один сегмент получателей рассылки, в срезе «Дата» останутся только те даты, в которые этим получателям уходили письма. 

Срез добавляют так:

1. Выделите заголовок столбца, из которого будете делать выборку. 
2. Откройте меню «Данные». 
3. Выберите «Добавить срез». 
4. Появится серая плашка — разместите ее в удобном месте рядом с таблицей и выберите условие фильтрации.

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

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

Чтобы удалить срез, кликните на нем. Справа на плашке появится кнопка с тремя точками. Нажмите на нее, в выпадающем меню выберите «Удалить срез».

Способ 4. Функция FILTER

Функция FILTER — мощный инструмент, потому что ее можно комбинировать с другими функциями. Она может быть вложенной, а также сама содержать в себе другие функции.

Например, мы можем использовать функцию FILTER как аргумент в функции SPARKLINE, которая выводит небольшие графики в рамках одной ячейки. Или в функции UNIQUE — в результате мы сможем фильтровать данные, попутно убирая повторяющиеся значения. 

Синтаксис функции несложный:

=FILTER(диапазон; условия)

Давайте выведем ниже нашей таблицы названия рассылок с Open Rate больше 20% или отправленные Сегменту B. 

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

=FILTER(A1:A15;(D1:D15>20%)+(C1:C15=»Сегмент B»))

Из чего она состоит:

• A1:A15 — диапазон выводимых данных: в нашем случае — столбец с названиями рассылок;
• D1:D15>20% — первое условие: значения в столбце с Open Rate больше 20%;
• + — логический символ, обозначающий, что будет применено одно из двух условий;
• C1:C15=»Сегмент B» — второе условие: получатель «Сегмент B».

Однако удобнее выводить отфильтрованные данные на другой лист. 

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

=FILTER(‘Исходная’!A2:A15;(‘Исходная’!D2:D15>20%)+(‘Исходная’!C2:C15=»Сегмент B»))

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

В Microsoft Excel можно отфильтровать данные тремя способами. 

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

Способ 1. Автофильтр

Автофильтр Excel — самый простой инструмент из трех. Он похож на простой фильтр в Google Таблицах.

Чтобы добавить автофильтр, выделите любую ячейку с данными, на вкладке «Данные» и выберите «Фильтр».

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

Например, выберем данные по рассылкам, где Open Rate больше 30%.

Готово. К столбцу применилась фильтрация — возле треугольника в его заголовке появился значок воронки.

Если нужно отфильтровать таблицу по нескольким параметрам, можно последовательно применить несколько автофильтров. Например, мы хотим увидеть данные по рассылкам для конкретного сегмента получателей и с определенным Open Rate. Для этого можно задать сначала фильтры по сегменту, затем — по OR.

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

Способ 2. Расширенный фильтр

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

Чтобы применить расширенный фильтр, создайте отдельную таблицу с условиями над основной таблицей. 

Для этого: 

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

Строк с условиями может быть несколько, и применяться они будут все. 

Например, мы хотим посмотреть рассылки сегмента B с Open Rate меньше 20%.

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

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

Нажмите ОК.

Чтобы убрать фильтр, на вкладке «Данные» нажмите кнопку «Очистить».

Способ 3. Срезы

Срезы — еще один красивый и удобный способ отфильтровать данные. Как и срезы в Google Таблицах, они позволяют работать с большим объемом данных, связанных друг с другом. Когда мы выбираем параметр фильтрации в одном блоке среза, в других отобразятся только доступные параметры — те, которые остались после первой фильтрации.

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

Прежде чем добавлять срезы, нужно отформатировать данные в виде таблицы. Для этого выделите всё командой CTRL+A (или Cmd+A для MacOS). Далее нажмите CTRL+T (Cmd+T) или на вкладке «Главная» нажмите кнопку «Форматировать как таблицу».

Дальше переключитесь на вкладку «Вставка», нажмите кнопку «Добавить срез». 

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

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

Можно настроить внешний вид блоков: цвет, размер, расположение. Для этого кликните на него правой кнопкой мыши — откроется меню с настройками. 

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

Чтобы полностью удалить срез, кликните правой кнопкой мыши на блоке среза, в выпадающем меню выберите «Удалить [название столбца]».

Выводы

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