Фильтрация данных в Excel с использованием расширенного фильтра
Расширенный фильтр в Excel дает вам больше контроля над данными электронной таблицы. Его сложнее настроить, но гораздо эффективнее в эксплуатации.
С помощью стандартного фильтра пользователь Microsoft Excel абсолютно не может решить все задачи. Нет отображения примененных условий фильтрации. Невозможно применить более двух критериев отбора. Вы не можете фильтровать повторяющиеся значения, чтобы сохранить только уникальные записи. А сами критерии схематичны и просты. Функционал расширенного фильтра намного богаче. Давайте подробнее рассмотрим его возможности.
Как сделать расширенный фильтр в Excel?
Расширенный фильтр позволяет фильтровать данные на основе неограниченного набора условий. Используя инструмент, пользователь может:
- установить более двух критериев отбора;
- скопировать результат фильтрации на другой лист;
- задать условие любой сложности по формулам;
- извлекать уникальные значения.
Алгоритм применения расширенного фильтра прост:
- Составляем таблицу с исходными данными или открываем существующую. Например, вот так:
- Создадим таблицу условий. Особенности: строка заголовка полностью совпадает с «заголовком» отфильтрованной таблицы. Чтобы избежать ошибок, скопируйте строку заголовка в исходную таблицу и вставьте ее на тот же лист (сбоку, сверху, снизу) или на другой лист. Вставляем критерии выбора в таблицу условий.
- Переходим во вкладку «Данные» — «Сортировка и фильтр» — «Дополнительно». Если отфильтрованная информация должна отображаться на другом листе (НЕ там, где находится исходная таблица), вам необходимо выполнить расширенный фильтр с другого листа.
- В открывшемся окне «Расширенный фильтр» выберите способ обработки информации (на том же или другом листе), установите начальный диапазон (таблица 1, пример) и диапазон условий (таблица 2, условия). Строки заголовка должны быть включены в диапазоны.
- Чтобы закрыть окно «Расширенный фильтр», нажмите ОК. Посмотрим на результат.
Таблица выше — результат фильтра. Нижняя табличка с условиями приведена рядом для ясности.
Как пользоваться расширенным фильтром в Excel?
Чтобы отменить действие расширенного фильтра, поместите курсор в любое место таблицы и нажмите комбинацию клавиш Ctrl + Shift + L или «Данные» — «Сортировка и фильтр» — «Удалить».
С помощью инструмента «Расширенный фильтр» находим информацию о значениях, содержащих слово «Установить».
Добавим критерии в таблицу условий. Например, такие:
В этом случае программа будет искать всю информацию о товарах, в названии которых присутствует слово «Set».
Вы можете использовать знак «=», чтобы найти точное значение. Добавим в таблицу условий следующие критерии:
Excel интерпретирует знак «=» как сигнал: теперь пользователь устанавливает формулу. Для правильной работы программы строка формул должна содержать запись модуля: = «= Установить область 6 cl.»
После использования «Расширенного фильтра»:
Теперь давайте отфильтруем исходную таблицу на основе условия «ИЛИ» для разных столбцов. Оператор OR также доступен в инструменте Auto Filter. Но там его можно использовать внутри колонны.
В таблице условий введите критерии выбора: = «= Набор регион 6 кл.» (в столбце «Имя») e = «» «; MAX ($ A $ 1: A1) +1) ‘class =’ formula’>
Рядом с раскрывающимся списком ячеек введите следующую формулу: MAX ($ B $ 1: $ O $ 1); «»; HLO (COLUMN (A: A), $ B $ 1: $ O $ 33; ПОИСК ($ A $ 12; $ A $ 4: $ A $ 9;) + 3)) ‘class =’ formula ‘> Его задача — выбрать те значения из таблицы, которые соответствуют конкретному продукту
Поэтому с помощью инструмента «Раскрывающийся список» и встроенных функций Excel выбирает данные в строках на основе определенного критерия.
Расширенный фильтр в Excel
Для фильтрации данных в Excel большинство пользователей используют хорошо известный и чрезвычайно доступный инструмент AutoFilter. Если вы не слышали об этом, вы можете узнать здесь и здесь .
Но что, если вам нужно фильтровать данные по нескольким критериям одновременно? Или вы пользуетесь формулой при фильтрации? Или выбрать значения в двух или более столбцах одновременно? В такой ситуации на помощь может прийти расширенный фильтр) .
Процесс работы с этим фильтром существенно отличается от привычного всем и состоит из нескольких этапов:
- Подготовка диапазона для условий фильтрации
- Формирование условий фильтрации
- Работа с мастером расширенных фильтров
Чтобы вы сразу поняли, что поставлено на карту, на гифке ниже показан небольшой пример того, как работает фильтр:
Рассмотрим подробнее каждый из этапов его применения.
1. Подготовка диапазона для условий фильтрации
В автоматическом фильтре условия фильтрации данных задаются в меню, которое появляется после нажатия кнопки в заголовке таблицы. В расширенном необходимо указать условия фильтрации в отдельном диапазоне. Этот диапазон может находиться в той же таблице, на другом листе или даже в другой книге. Главное, чтобы он был отделен от исходных данных хотя бы одним пустым столбцом или строкой (чтобы Excel не воспринимал их как диапазон).
также важно помнить, что когда вы указываете на шаге 3 (работа в мастере «Расширенный фильтр») диапазон условий, вам нужно выбрать заголовок и строки, в которых есть некоторые критерии. Выбирать пустые строки необязательно, иначе фильтр интерпретирует их как сигнал «Показать все строки».
Диапазон задания условий фильтрации — это копия заголовка основной таблицы (или хотя бы тех полей, по которым нужно выбирать данные) и достаточное количество пустых строк под этим заголовком.
2. Формирование условий фильтрации
Этот этап является фундаментальным и раскрывает всю мощь инструмента. Для начала нужно научиться правильно выставлять критерии отбора.
Они бывают 3-х видов:
Если в качестве критерия текста ввести слово в поле, например «Москва», будут выбраны ВСЕ строки, в которых запись в указанном столбце начинается со слова «Москва»
Если вам нужно искать слово или часть слова не с начала строки, а в целом, вам нужно использовать подстановочные знаки. Если вы их не знаете, читайте здесь и здесь. В следующем примере вы найдете все строки, в которых столбец «Город» содержит слово «Петербург»
Если вам нужно найти точное вхождение слова или фразы, критерий должен быть установлен с помощью несколько необычной формулы. Например, чтобы найти строки, содержащие «Петербург» и не отображающие строки «Санкт-Петербург», вам нужно ввести формулу: = «= Петербург» (правильно, с двумя знаками «=») .
— числовые критерии и даты
В качестве критерия можно ввести число (и тогда будут выбраны строки, в которых значения столбцов равны этому числу)
вы также можете вводить выражения с помощью логических операторов (>, =,). Например, вы можете найти строки с суммой больше 500 000, введя критерий> 500 000
с особой осторожностью нужно быть при вводе критериев в виде даты. Даты должны вводиться через косую черту. Например, чтобы выбрать все транзакции после 4 января 2017 года, необходимо ввести критерий для поля «Дата» -> 01.04.2017 (в некоторых версиях Excel необходимо вставить в поле MM / DD / ГГГГ, т.е нужно предварительно указать месяц в уме при работе).
Лучшее, что может сделать расширенный фильтр, — это использовать формулы в качестве критерия. Чтобы это работало, указанная формула должна возвращать ИСТИНА (и, таким образом, строка будет отображаться) или ЛОЖЬ (строка будет скрыта). Чрезвычайно важно, чтобы заголовок столбца формулы отличался от любой записи в заголовке таблицы (вы можете оставить его полностью пустым). При написании формул не забывайте правильно размещать абсолютные и относительные ссылки.
Например, если вы хотите отобразить первые 5 строк в поле суммы, вам нужно будет ввести следующую формулу:
где F10 — это ячейка первой строки в столбце «Сумма» (она не заблокирована, поскольку формула будет перебирать строки одна за другой), $ F $ 10: $ F $ 37 — ссылка на диапазон, который столбец «Сумма» занимает »(ссылка заблокирована, так как столбец не меняется).
В результате формула пройдет по всем строкам (с 10-й по 37-ю) и скроет все, кроме тех, у которых значение больше шестого по величине (то есть останется 5 первых).
Конечно, все описанные критерии и примеры можно реализовать с помощью обычного автоматического фильтра (за исключением возможности использования формул). Однако весь потенциал расширенного фильтра раскрывается, когда вы знаете, как правильно комбинировать множество критериев друг с другом.
Итак, вот основные концепции, которые вам необходимо понять, чтобы успешно применить расширенный фильтр:
— заголовок столбца, в который мы записываем критерий выбора, должен быть точно таким же, как заголовок столбца, к которому мы применяем этот критерий. То есть, если мы выбираем строки, в которых значение в столбце «Сумма» больше 500, условие> 500 записывается под заголовком «Сумма»;
— условия, записанные подряд, воспринимаются фильтром как связанные оператором I .
— условия, написанные разными строками, воспринимаются фильтром как связанные оператором ИЛИ. Условия могут применяться к столбцу или к разным столбцам. Например, на изображении ниже показано условие ИЛИ город Москва, ИЛИ менеджер Иванов. Следовательно, каждая строка представляет собой единый набор условий.
— если вам нужно установить условие И, но при этом использовать тот же столбец (например, И сумма больше 500 000 И сумма меньше 600 000), заголовок этого столбца должен быть продублирован дважды. Пример:
Теперь вы знаете, какие критерии можно задать и как их правильно комбинировать. Этого достаточно для построения сложных запросов, недоступных обычному автофильтру. Например, если вам нужно показать все предложения Москвы на 2017 год на сумму больше 500000 и при этом также отобразить все предложения из Иванова на 2016 год, которые находятся в ТОП5, критерии будут выглядеть так:
3. Работа с мастером «Расширенного фильтра»
Самое сложное позади: вы научились формировать критерии отбора. Один шаг влево. Выберите диапазон с исходными данными (или любую из его ячеек — Excel определит и выберет требуемый массив) и найдите команду «Дополнительно» на ленте под вкладкой «Данные» в группе «Сортировка и фильтр». Перед вами откроется окно «Расширенный фильтр»
1) установить начальный диапазон (он будет выбран автоматически при запуске фильтра),
2) определите диапазон условия (ваша мини-таблица с критериями, Excel может автоматически определять диапазон неточно, лучше каждый раз переназначать его), на этом шаге напоминаем, что мы выбираем только заголовки и строки с критерием, пустые строки не могут быть включены в диапазон ,
3) выберите метод обработки (отфильтруйте данные в исходной таблице или скопируйте отфильтрованные строки в другое место),
4) укажите, стоит ли показывать все записи или просматривать только уникальные.
После настройки нажмите «ОК» и получите желаемый результат.
Чтобы очистить фильтр, наведите курсор на заголовок таблицы с отфильтрованными данными и используйте команду «Очистить» на ленте или примените автофильтр (Ctrl + Shift + L).
Недостатки Расширенного фильтра
Главный недостаток в том, что этот инструмент не интерактивен и не динамичен. Нельзя просто ввести новый критерий в диапазоне условий и увидеть результат. Вам придется каждый раз вызывать мастер «Расширенный фильтр» и повторять некоторые операции. Конечно, этот недостаток можно обойти, но это уже тема для другой статьи.
Бонус. Полезный трюк с Расширенным фильтром
Если вам нужно быстро извлечь уникальные записи из диапазона, сделайте следующее:
1) Выделите диапазон
2) Запустите расширенный фильтр
3) Укажите следующие параметры:
В результате вы извлечете все уникальные записи из диапазона в любое удобное место. В этом случае сам ассортимент останется нетронутым.
Свои вопросы по статье вы можете задать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
Как в Excel сделать фильтр по столбцам
Фильтрация данных по различным критериям помогает манипулировать информацией, выбирать и анализировать только то, что необходимо. Сегодня мы узнаем, как вставить фильтр в электронную таблицу Excel и как его использовать.
Простой фильтр в Экселе
Давайте посмотрим, как настроить в Excel фильтр по столбцам:
- Щелкните любую ячейку в таблице (вернее, выберите всю таблицу с заголовком)
- Щелкните ленту Данные — Сортировка и фильтр — Фильтр
- Кнопки со стрелками появляются в первой строке таблицы, указывая на то, что вы можете установить фильтр для столбца:
Как фильтровать сейчас? Например, в таблице-примере нужно выделить всех алуштинцев:
- В столбце «Город» щелкните значок фильтра
- Откроется список, в котором галками отмечены все города, а также форма поиска
- В поиске введите «Алушта» и нажмите ОК
Теперь у нас на экране только те строки, где находится город Алушта. Остальные строки не удаляются, а просто скрываются.
В столбце, где на кнопку установлен фильтр, вместо стрелки появится воронка.
Чтобы повторно применить установленный фильтр, когда информация изменилась, нажмите Данные — Сортировка и фильтр — Сброс или комбинацию клавиш Ctrl + Alt + L .
А когда вам нужно сбросить все примененные фильтры, нажмите Данные — Сортировка и фильтр — Очистить .
Вы можете фильтровать по нескольким столбцам одновременно, даже по всем!
Текстовый фильтр в Эксель
Давайте посмотрим, как фильтровать ячейки с определенным текстом в Excel. Самый простой способ — по аналогии с предыдущим примером ввести в поиск необходимый текст (или его часть.
Однако вы можете настроить выбор более гибко. Если вы нажмете «Текстовые фильтры» в окне фильтра, в контекстном меню появится выбор метода сопоставления: равно, не равно, начинается с, заканчивается, содержит, не содержит.
Например, вам нужно выбрать людей, которых зовут не Богдан. Выбираем вариант «не содержит» и пишем для него критерий «Богдана». Ставим пробелы до и после имени. В противном случае, например, Богданов Егор Егорович тоже попадет под фильтр, хотя имя его не Богдан:
Настраиваемый тестовый фильтр
Я расскажу, как в Excel вставить фильтр для двух условий в одну ячейку. Для этого щелкните Текстовые фильтры — Пользовательский фильтр .
Допустим, нам нужно выбрать людей с именем Богдан или Никита. Пишем логику как на картинке
И вот результат:
Как определить, какой оператор сравнения выбрать: «И» или «ИЛИ»? Логика такая:
- И — когда необходимо, чтобы оба условия выполнялись одновременно
- ИЛИ — когда достаточно выполнить хотя бы одно из двух условий
Подробнее о логических операторах вы можете прочитать в этой статье.
Кроме того, в условии могут использоваться следующие операторы:
- ? Это одиночный персонаж
- * — любое количество любого символа
Например, чтобы выбрать полное имя, содержащее строку «ctor», напишите условие следующим образом: * ctor*.
Как поставить фильтр в Экселе на столбец с числами
Числовые фильтры тоже можно гибко настраивать, есть такие способы выбора:
- Равные или не равные
- Больше, больше или равно, меньше, меньше или равно
- Между (в диапазоне)
- Топ 10
- Выше среднего, ниже среднего
Как видите, есть интересные варианты. Попробуем выделить людей с оборотом 200 000 — 500 000. Выберем пункт МЕЖДУ. Нравится:
для числовых данных, как и для текста, есть собственный фильтр.
Фильтрация дат
Когда ячейки в столбце отформатированы как Дата, фильтрация этих данных становится намного проще.
Для начала поле значения содержит даты, сгруппированные по году, месяцу, дню. Вы можете выбрать желаемые годы или месяцы с помощью галочки.
Группа команд «Фильтр по дате» содержит большой список популярных запросов:
Чтобы установить собственный интервал, выберите МЕЖДУ .
Фильтр по цвету в Excel
Если для некоторых ячеек задан цвет фона или текста, вы можете выполнить фильтрацию по ним. При этом программа сама сформирует список цветов по форматам, указанным в столбце:
Выберите свой вариант из списка.
Расширенный фильтр в Excel
Расширенный фильтр предоставляет быстрый способ фильтрации. Он заключается в том, что вы создаете предварительную таблицу данных, которую хотите выбрать, а затем — основную. Поля заголовка должны совпадать. Вот как это будет, если отбирать людей только из городов Агрыз и Азов:
Теперь нужно сделать:
- Нажмите Данные — Сортировка и фильтр — Дополнительно
- В открывшемся окне выберите:
- Исходный диапазон — таблица с данными
- Диапазон условий — диапазон значений для фильтрации
- Только уникальные записи: установите этот флажок, если хотите, чтобы программа скрывала дубликаты
- Обработка: выберите «скопировать результат в другое место», если вы хотите, чтобы отфильтрованные данные находились в другом месте того же листа
- Нажмите ОК
Посмотрите на фото, у нас список только по Агрызу и Азову:
вы можете поставить дополнительное условие на контент. Для этого прямо в строке, по которой будет производиться выбор, нужно написать формулу с условием.
Завершим рассмотренный пример. Теперь нужно выбрать те же города, но записи, в которых продажи больше 500 тысяч. Условия будут такими:
Давайте снова применим расширенный фильтр, но добавим новые столбцы в таблицу фильтров. Результат на фото выше.
Может быть, это все, о чем я хотел поговорить с вами сегодня. Мы объяснили, как настроить фильтр в Excel, чтобы получить желаемый образец. Жду ваших вопросов в комментариях. Увидимся!
Фильтрация данных в Excel
В Excel есть три типа фильтров:
- Автоматический фильтр: для фильтрации записей на основе значения ячейки, формата или простого критерия выбора.
- Срезы — это интерактивные инструменты для фильтрации данных в таблицах.
- Расширенный фильтр — для фильтрации данных по сложному критерию отбора.
Автофильтр
- Выберите ячейку из диапазона данных.
- На вкладке «Данные» найдите группу «Сортировка и [фильтр].
- Щелкните кнопку [Фильтр] .
- Рядом с каждым столбцом в верхнем ряду диапазона появились кнопки со стрелками. В столбце, содержащем ячейку для фильтрации, нажмите кнопку со стрелкой. Откроется список возможных вариантов фильтрации.
- Выберите условие фильтра.
Варианты фильтрации данных
- Фильтр по значению: установите флажок для значений, необходимых для столбца данных, которые выделены в нижней части диалогового окна.
- Фильтр по цвету: выбор по форматированию ячейки: по цвету ячейки, цвету шрифта или значку ячейки (если задано условное форматирование).
- Вы можете использовать панель быстрого поиска
- Чтобы выбрать числовой фильтр, текстовый фильтр или фильтр даты (в зависимости от типа данных), выберите соответствующую строку. Появится контекстное меню с более подробными параметрами фильтра:
- При выборе параметра числовых фильтров появятся следующие параметры фильтра: равно, больше, меньше, 10 первых… [10 первых…] и т.д.
- Когда вы выбираете опцию Текстовые фильтры в контекстном меню, вы можете отметить опцию фильтра, которую он содержит, начинается с .. и т.д.
- Когда вы выбираете опцию Фильтр по дате, варианты фильтрации: завтра, следующая неделя, последний месяц и т.д.
- Во всех предыдущих случаях контекстное меню содержит пункт Пользовательский фильтр… [Пользовательский…], который может использоваться для одновременной установки двух условий выбора, связанных соотношением И [И] — одновременное выполнение 2 условий, ИЛИ [Или] — выполнение хотя бы одного условия.
Если данные были изменены после фильтрации, фильтрация не работает автоматически, поэтому вам необходимо перезапустить процедуру, нажав кнопку «Повторно применить» в группе «Сортировка и фильтр» на вкладке «Данные.
Отмена фильтрации
Чтобы отменить фильтрацию диапазона данных, просто нажмите кнопку «Фильтр» еще раз.
Чтобы удалить фильтр из одного столбца, просто нажмите кнопку со стрелкой в первой строке и выберите следующую строку в контекстном меню: Удалить фильтр из столбца. 
Чтобы быстро удалить фильтр со всех столбцов, нужно выполнить команду Очистить на вкладке Данные 
Слайсеры представляют собой те же фильтры, но размещены в отдельной области и имеют удобное графическое представление. Срезы — это не часть листа ячеек, а отдельный объект, набор кнопок, расположенный на листе Excel. Использование слайсеров не заменяет автофильтр, но благодаря удобному виду облегчает фильтрацию: все примененные критерии видны одновременно. Срезы добавляются в Excel с 2010 года.
Создание срезов
В Excel 2010 вы можете использовать слайсер для сводных таблиц, а в 2013 вы можете создать слайсер для любой таблицы.
Для этого выполните следующие действия:
- Выберите ячейку в таблице и перейдите на вкладку [«Дизайн].
- В диалоговом окне отметьте поля, которые хотите включить в раздел, и нажмите OK.
Форматирование срезов
- Выберите срез.
- На ленте вкладки «Параметры» выберите группу «Стили слайсера», которая содержит 14 стандартных стилей и возможность создания собственного пользовательского стиля.
- Выберите кнопку с подходящим стилем форматирования.
Чтобы удалить часть, выберите ее и нажмите клавишу Delete.
Расширенный фильтр
Расширенный фильтр предоставляет дополнительные параметры. Он позволяет комбинировать несколько условий, размещать результат в другой части листа или на другом листе и т.д.
Задание условий фильтрации
- В диалоговом окне «Расширенный фильтр» выберите вариант записи результатов: отфильтровать список на месте [Отфильтровать список на месте] или скопировать результат в другое место [Копировать в другое место].
- Укажите диапазон исходного списка, выделив исходную таблицу вместе с заголовками столбцов.
- Укажите диапазон критериев, щелкнув диапазон критериев, включая ячейки с заголовками столбцов.
- При необходимости укажите место с результатами в поле Поместить результат в диапазон [Копировать в], выделив ячейку диапазона курсором для размещения результатов фильтрации.
- Если вы хотите исключить повторяющиеся записи, установите флажок в строке Только уникальные записи.
