EXCEL: как применить расширенный фильтр к таблице
Помимо стандартной фильтрации при помощи автофильтра, программа Excel позволяет применять к данным расширенный фильтр. В этой статье мы рассмотрим особенности данного функционала и на примерах разберем, как его можно использовать
«Расширенный фильтр Excel, как следует из названия, является расширенной версией обычного фильтра. Его лучше использовать в том случае, когда необходимо провести отбор данных по более сложным критериям: например, по нескольким столбцам на основе И/ИЛИ правил»
Шлычков Константин
Эксперт и преподаватель Excel
Расширенный фильтр в Excel и его особенности
Для начала работы с функцией расширенного фильтра в Excel необходимо ознакомиться с некоторыми характерными его особенностями:
- Расширенный фильтр позволяет произвести фильтрацию по неограниченному количеству оснований;
- Все критерии, по которым отфильтрована таблица, представлены на виду – отдельном диапазоне на том же листе, что и исходные данные;
- С помощью расширенного фильтра можно показать в таблице только уникальные строки;
- Фильтровать таблицу можно при помощи формул и подстановочных знаков;
- Расширенный фильтр можно применить сразу к разным (но обязательно схожим) таблицам;
- При изменении условий фильтрации расширенный фильтр требует ручной настройки: для этого нужно открывать настройки расширенного фильтра и указывать новый диапазон;
- Фильтр не чувствителен к регистру символов.
Как настроить расширенный фильтр в Excel
Шаг 1. Настроить данные
Для начала необходимо настроить Ваши данные в удобный для фильтрации вид. Следуйте этим рекомендациям, чтобы расширенный фильтр работал правильно:
Для начала необходимо настроить Ваши данные в удобный для фильтрации вид. Следуйте этим рекомендациям, чтобы расширенный фильтр работал правильно:
- Первая строка таблицы обязательно должна содержать в себе заголовки столбцов. Повторяющиеся заголовки могут вызвать проблемы при запуске расширенного фильтра;
- Рекомендуется привести данные к формату умной таблицы Excel. Она автоматически предотвращает дублирование заголовков столбцов путем добавления номеров к названию. Создать умную таблицу можно при помощи горячих клавиш: выберите любую ячейку в массиве данных и зажмите клавиши «Ctrl» и английскую «T»;
- В таблице с исходными данными не должно быть пустых строк;
- Таблица должна быть отделена от других данных снизу пустой строкой, а с боку пустым столбцом.
Шаг 2. Создать диапазон критериев
Что это? Диапазон критериев для расширенного фильтра Excel представляет собой дополнительную таблицу, где Вы устанавливаете правила фильтрации данных.
Для того чтобы создать диапазон критериев, копируем заголовки из таблицы, которую мы хотим отфильтровать, и вставляем в любое свободное место на листе. Теоретически диапазон критериев можно разместить где угодно, но на практике удобнее всего, когда он находится выше таблицы с нашими данными.
Что это? Диапазон критериев для расширенного фильтра Excel представляет собой дополнительную таблицу, где Вы устанавливаете правила фильтрации данных.
Для того чтобы создать диапазон критериев, копируем заголовки из таблицы, которую мы хотим отфильтровать, и вставляем в любое свободное место на листе. Теоретически диапазон критериев можно разместить где угодно, но на практике удобнее всего, когда он находится выше таблицы с нашими данными.
Теперь у нас две таблицы: одна для того, чтобы прописывать критерии, по которым будет происходить фильтрация, и вторая – исходная, она будет меняться в зависимости от указанных условий.
Шаг 3. Указать критерии, по которым необходимо отфильтровать таблицу
Например, если хотим, чтобы в столбце «Страна» в нашей таблице отобразились только ячейки со значением «Россия», в дополнительной таблице – в диапазоне критериев пишем «Россия», без дополнительных символов. Таким образом мы указываем все необходимые критерии, по которым хотим отобрать наши данные.
Например, если хотим, чтобы в столбце «Страна» в нашей таблице отобразились только ячейки со значением «Россия», в дополнительной таблице – в диапазоне критериев пишем «Россия», без дополнительных символов. Таким образом мы указываем все необходимые критерии, по которым хотим отобрать наши данные.
Шаг 4. Включить функцию «Расширенный фильтр»
Для фильтрации таблицы в соответствии с условиями, указанными в таблице критериев, необходимо включить функцию расширенного фильтра в Excel. Чтобы включить эту функцию, нужно перейти на вкладку «Данные», затем выбрать «Сортировка и фильтр» и выбрать «Дополнительно».
Для фильтрации таблицы в соответствии с условиями, указанными в таблице критериев, необходимо включить функцию расширенного фильтра в Excel. Чтобы включить эту функцию, нужно перейти на вкладку «Данные», затем выбрать «Сортировка и фильтр» и выбрать «Дополнительно».
На экране появится диалоговое окно «Расширенный фильтр Excel». Настроим его вместе:
Раздел «Обработка». Выберите пункт в зависимости от того, хотите ли Вы фильтровать свои данные на месте или скопировать результаты в другое местоположение.
При выборе «Фильтровать список на месте», строки в Вашей таблице, не соответствующие заданным критериям, будут скрыты.
Раздел «Обработка». Выберите пункт в зависимости от того, хотите ли Вы фильтровать свои данные на месте или скопировать результаты в другое местоположение.
При выборе «Фильтровать список на месте», строки в Вашей таблице, не соответствующие заданным критериям, будут скрыты.
Если нажать «Копировать результаты в другое место», необходимо выбрать, куда Вы хотите вставить отфильтрованные строки. Для этого определите свободное место на листе Excel и выберите ячейку – при переносе данных она станет крайней левой ячейкой появившейся таблицы с отфильтрованными данными.
Поле «Исходный диапазон». Выберите диапазон данных, которые хотите отфильтровать, включая заголовки.
Поле «Диапазон критериев». Снова нажмите на стрелочку справа от этого поля и вручную выберите диапазон таблицы с критериями.
Нажмите «ОК». Вы великолепны!
Поле «Диапазон критериев». Снова нажмите на стрелочку справа от этого поля и вручную выберите диапазон таблицы с критериями.
Нажмите «ОК». Вы великолепны!
Совет: Если выбрать любую ячейку в наборе данных перед настройкой расширенного фильтра в Excel, то программа автоматически заполнит поле «Исходный диапазон» во всплывающем окне. Однако, необходимо проверить правильность определения диапазона таблицы. Если диапазон определен неверный – Вы можете легко это исправить: щелкните значок стрелки справа от поля «Исходный диапазон» и выберите нужный диапазон с помощью мыши или при помощи сочетания горячих клавиш «Ctrl» и «A».
Фильтр применился, наши данные отобраны. Но добиться этого можно было и с помощью обычного автофильтра, зачем усложнять? Не спешите разочаровываться в расширенном фильтре, ведь на самом деле его функционал гораздо шире. Мы советуем Вам дочитать статью до конца, чтобы познакомиться со всеми возможностями такого способа фильтрации.
Как работает диапазон критериев расширенного фильтра Excel
После того как Вы убедились, что в применении расширенного фильтра нет ничего сложного, предлагаем Вам ознакомиться со всеми тонкостями заполнения диапазона критериев.
Критерии, перечисленные в одной строке, работают с логикой И, то есть будут выполняться одновременно.
Условия, указанные в этом примере, дают команду Excel показать строки, которые содержат информацию только о штукатурке из Китая.
Условия, указанные в этом примере, дают команду Excel показать строки, которые содержат информацию только о штукатурке из Китая.
Критерии, введенные в разные строки, работают по логике ИЛИ, то есть критерии не будут зависеть друг от друга.
В данном примере Excel выполняет отбор данных по принципу: сначала найди все строки, содержащие в столбце товар первым словом «Штукатурка», а далее найди все строки, начинающиеся со слова «Беларусь», остальные строки скрой.
В данном примере Excel выполняет отбор данных по принципу: сначала найди все строки, содержащие в столбце товар первым словом «Штукатурка», а далее найди все строки, начинающиеся со слова «Беларусь», остальные строки скрой.
Логику И и ИЛИ можно использовать одновременно. В данном примере таблица критериев говорит о том, что в фильтре должны отобразиться штукатурка из Китая и строки, содержащие информацию о краске вне зависимости от страны.
Операторы сравнения чисел и дат
В критериях расширенного фильтра Вы можете сравнивать различные числовые значения, используя следующие операторы сравнения:
Выглядит довольно очевидно, не так ли? Давайте на примере разберем, как с помощью хорошо понятных нам операторов сравнения можно настраивать сложные критерии для фильтрации таблицы.
Взглянем на таблицу, в которой отображено наличие товаров на складе по странам-производителям. Представим, что хотим отобразить в таблице только те товары, которые были закуплены из Китая в августе 2022 года с ценой больше или равной 10 000 тыс.
Взглянем на таблицу, в которой отображено наличие товаров на складе по странам-производителям. Представим, что хотим отобразить в таблице только те товары, которые были закуплены из Китая в августе 2022 года с ценой больше или равной 10 000 тыс.
Для этого нам необходимо создать диапазон критериев со следующими значениями:
Страна: Китай
Дата: >=01.08.2022
Дата: <=30.08.2022
Цена: >10 000
Страна: Китай
Дата: >=01.08.2022
Дата: <=30.08.2022
Цена: >10 000
В диапазоне критериев необходимо создать два столбца «Дата», чтобы отобразить в них два значения: первое – дата начала периода, который мы хотим отобразить, а второе – конец периода.
Важно: между знаками условных операторов (>, <= и др.) и самим значением не должно быть пробелов, в противном случае фильтр не сработает.
Операторы сравнения для текстовых данных
Символы для сравнения можно использовать и для ячеек, содержащих текстовые данные.
Пример №1. Текстовый фильтр для точного соответствия
Если Вы хотите отобразить в таблице только те строки, что содержат слово «Штукатурка» и ничего более, запишите в ячейку формулу =”=Штукатурка”. Excel отобразит критерии как =Штукатурка в ячейке.
Если Вы хотите отобразить в таблице только те строки, что содержат слово «Штукатурка» и ничего более, запишите в ячейку формулу =”=Штукатурка”. Excel отобразит критерии как =Штукатурка в ячейке.
Пример №2: Фильтр текстовых значений, начинающихся с определенного символа (или символов).
Чтобы отобразить все ячейки, содержимое которых начинается с указанного сочетания символов, просто введите их в диапазоне критериев без знака равенства или двойных кавычек.
Чтобы отобразить все ячейки, содержимое которых начинается с указанного сочетания символов, просто введите их в диапазоне критериев без знака равенства или двойных кавычек.
Пример 3. Текстовый фильтр для частичных совпадений при помощи символов подстановки
В случаях, когда Вам необходимо отобразить строки, содержащие в себе набор символов вне зависимости от места их расположения в строке, нужно использовать специальные подстановочные символы (*, ?, ~).
Для того чтобы отфильтровать таблицу по определенному сочетанию символов в артикле, необходимо вписать такой критерий: *СКЗИ*
В случаях, когда Вам необходимо отобразить строки, содержащие в себе набор символов вне зависимости от места их расположения в строке, нужно использовать специальные подстановочные символы (*, ?, ~).
Для того чтобы отфильтровать таблицу по определенному сочетанию символов в артикле, необходимо вписать такой критерий: *СКЗИ*
В Excel есть три подстановочных символа:
* (звездочка) — обозначает, что искомое слово может содержать любое количество любых символов после этого значка. Например, экс* может означать эксель, экскурсия, эксперт и т. д.
? (вопросительный знак) — представляет собой один символ. Например, «ло?ка» может означать «ложка» или «лодка».
~ (тильда) — за которой следуют *, ? или ~, используется, чтобы отфильтровать ячейки, содержащие настоящий вопросительный знак, звездочку или тильду.
* (звездочка) — обозначает, что искомое слово может содержать любое количество любых символов после этого значка. Например, экс* может означать эксель, экскурсия, эксперт и т. д.
? (вопросительный знак) — представляет собой один символ. Например, «ло?ка» может означать «ложка» или «лодка».
~ (тильда) — за которой следуют *, ? или ~, используется, чтобы отфильтровать ячейки, содержащие настоящий вопросительный знак, звездочку или тильду.
Как использовать формулы в расширенном фильтре
Для того чтобы расширенный фильтр выполнил условия с использованием формул, необходимо ознакомиться со следующими правилами:
- Формула должна принимать значение ИСТИНА или ЛОЖЬ;
- Если результатом применения формулы будет ИСТИНА – строка отобразится в результате применения фильтра, если ЛОЖЬ – нет;
- Ячейка заголовка в таблице критериев должна иметь заголовок, отличный от любого из заголовков исходной таблицы;
- Для указания исходного диапазона таблицы в Excel следует использовать абсолютные ссылки, обозначаемые знаком $ (например, $A$1), в то время как для задания критерия отбора необходимо использовать относительные ссылки без знака $ (например, A1).
Например. Для фильтрации строк в Excel по такому критерию, как сравнение суммы закупок за август и июль, необходимо создать таблицу условий, содержащую заголовок (можно так и назвать «Критерий») и одну ячейку. В этой ячейке необходимо указать критерий фильтрации, используя относительные ссылки без знака $: формулу =E5>D5 для сравнения значений столбцов E и D.
Разберем еще один пример. Представим, что необходимо отобразить в таблице только заказы на сумму выше среднего от всех зарегистрированных заказов.
Создаем таблицу условий, заголовком которой можем быть «Стоимость выше среднего». Формула, которую нужно указать в качестве критерия, выглядит так: =С6>СРЗНАЧ($С$5:$С$20), где С6 — первая ячейка в столбце «Цена», а диапазон $С$5:$С$20 — сам этот столбец. Заострим внимание на том, что диапазон столбца зафиксирован при помощи знака $ — это абсолютная ссылка. Его можно ввести вручную, или же нажатием клавиши «F4».
Создаем таблицу условий, заголовком которой можем быть «Стоимость выше среднего». Формула, которую нужно указать в качестве критерия, выглядит так: =С6>СРЗНАЧ($С$5:$С$20), где С6 — первая ячейка в столбце «Цена», а диапазон $С$5:$С$20 — сам этот столбец. Заострим внимание на том, что диапазон столбца зафиксирован при помощи знака $ — это абсолютная ссылка. Его можно ввести вручную, или же нажатием клавиши «F4».
Как извлечь уникальные данные из таблицы
Если нужно показать только уникальные строки в таблице, то при использовании расширенного фильтра необходимо выбрать опцию «Только уникальные записи» во всплывающем окне настроек.
Вы ознакомились со всеми особенностями использования расширенного фильтра и теперь уверенно можете отбирать данные в Вашем массиве при помощи самых разнообразных критериев, не боясь что-то потерять!
