Способы фильтрации в Excel, о которых знают немногие

Фильтры нужны, чтобы отобразить необходимые данные в отчёте, при печати или для анализа данных. В Excel есть несколько видов фильтрации данных в таблицах: автофильтры, расширенные фильтры и срезы. Подробнее рассмотрим каждый из них.

Автофильтры помогают отображать или скрывать данные, работают с числовыми и текстовыми значениями в одном или нескольких столбцах. Например, автофильтр поможет быстро посмотреть, сколько сделок от 50 000 рублей было совершено в январе 2024 года.

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

Excel-фильтр помогает сортировать информацию по столбцам

С помощью фильтра данных в Excel-ячейке можно сортировать по цвету, тексту, числу, поиску:

● Фильтр по цвету выбирает данные по цвету ячейки или шрифта.
● Текстовый фильтр выбирает диапазон данных по названиям или ключевым словам. Например, если нужно посмотреть, сколько ноутбуков продано за месяц, можно ввести слово ноутбук в текстовый фильтр «Начинается с». В таблице отобразятся ноутбуки всех производителей и моделей.
● Числовой фильтр выбирает диапазон данных по числам. Чтобы посмотреть, сколько в магазине было продано ноутбуков в диапазоне от 30 000 до 45 000 тысяч рублей, нужно ввести нужный диапазон в фильтр.
● Фильтр по дате. Если использовать фильтр по дате «После 01.01.2024», в таблице отобразятся все ноутбуки по цене от 30 000 до 45 000 тысяч рублей, проданные с начала января 2024 года.
● Фильтр по поиску. Например, чтобы найти ноутбуки конкретной модели, нужно убрать галочку рядом с «Выделить всё» и поставить её рядом с названиями нужных моделей. В таблице появятся все ноутубки этой модели, а в углу столбца будет стоять значок, который показывает, где применили фильтр.

На курсе «Excel для работы» можно попрактиковаться в настройке фильтров по определённым параметрам. Практика проходит на онлайн-платформе с интерактивными заданиями. Над программой курса работали специалисты из разных сфер, поэтому она охватывает все возможности Excel. Начать учится можно бесплатно, чтобы оценить, подходит ли курс.

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

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

Графические срезы вместе работают как автоматизированный фильтр по поиску

Как задать фильтр срезов в Excel:

1. Активировать таблицу, щёлкнув по ней, и перейти в раздел «Вставка» в панели управления.
2. Нажать на значок Таблица, а в появившемся табло поставить галочку напротив «Таблица с заголовками». Если всё сделано правильно, в верхней части панели управления появится вкладка «Работа с таблицей», а в самой таблице отобразятся значки фильтра.
3. В разделе «Вкладка» нажать значок Среза. Или значок «Вставить срез» в разделе «Конструктор».
4. Если нажать на срез, появится табло со списком заголовков из шапки таблицы. Ставим галочки напротив данных, которые нужно отобразить, например, имена заказчиков и статус сделки.

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

Срезы работают как эксель-фильтрация данных в таблице по поиску. Например, бухгалтерия хранит данные о поставщиках услуг в таблице Excel. Чтобы посмотреть контрагентов, у которых компания покупает электроэнергию, бухгалтер кликает по кнопке Электроэнергия в срезе — и фильтр отображает нужные данные. Если нужно посмотреть поставщиков и электроэнергии, и теплоэнергии — во время клика нужно зажать клавишу Ctrl.

Пользователь может настроить срезы по цвету и размеру, по-разному компоновать их на экране в разделе «Инструменты для срезов»

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

Попробуем настроить фильтр, чтобы посмотреть, по какому договору у поставщиков тепло- и электроэнергии отсрочка платежа 30 дней:

● В срезе «Услуги» выбираем тепло- и электроэнергию — остальные данные, которые не соответствуют условиям, будут прозрачными во всех срезах.
● В срезе «Отсрочка платежа» добавляем условие 30 дней — данные, частично удовлетворяющие условия фильтрации, станут приглушённого цвета.
● В таблице будет отображена только та информация, которая подходит по всем условиям.

На срезах видно, что только в договорах о поставках электроэнергии отсрочка платежа 30 дней, поэтому кнопка «Теплоэнергия» не активна

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

В отличие от срезов, в расширенной фильтрации эксель можно задать более широкий спектр условий. Например, использовать знаки «<» или «>», чтобы проверить, в какие месяцы работники закрывали личный план.

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

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

Далее нужно следовать алгоритму:

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

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

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

3. Щёлкнув на «Диапазон условий», нужно выделить дополнительную таблицу. В выделенную область должна попасть только шапка таблицы и строки, в которых заданы условия. Иначе расширенный фильтр будет работать некорректно.

4. Выбрать «Фильтровать список на месте» — расширенный фильтр в таблице сработает как автофильтр.

В таблице диапазона данных каждая строка — набор фильтров, отображающий в исходной таблице только данные, которые отвечают всем заданным условиям фильтра строк в экселе. Например, в таблице будут отражены два вида данных: по Ивановой в Castorama и Петрову в «Леруа Мерлен». Поэтому, если необходимо отобразить несколько результатов по типу набор условий № 1 ИЛИ набор условий № 2, следует прописать условия фильтрации в каждой строке.

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

Убрать фильтр в Excel можно несколькими способами:

1. Убрать фильтр с конкретного столбца — щёлкнуть правой кнопкой мыши по столбцу, в котором применён фильтр, и выбрать «Фильтр» — «Удалить фильтр» из столбца.

2. Если нужно очистить все применённые фильтры — перейти в панели инструментов в раздел «Данные» и нажать на значок «Очистить».

3. Чтобы очистить фильтр среза, нужно нажать на значок «Удалить фильтр» в верхнем правом углу среза.

Если нужно удалить сам срез, следует щёлкнуть по нему и нажать клавишу Delete

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