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

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

Argument ‘Topic id’ is null or empty

Сейчас на форуме

© Николай Павлов, Planetaexcel, 2006-2023
info@planetaexcel.ru

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

ООО «Планета Эксел» ИНН 7735603520 ОГРН 1147746834949

ИП Павлов Николай Владимирович ИНН 633015842586 ОГРНИП 310633031600071

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

Самый простой способ отфильтровать несколько столбцов в Excel — использовать функцию расширенного фильтра .

В следующих примерах показано, как использовать эту функцию в двух разных сценариях:

• Отфильтровать строки, соответствующие нескольким условиям
• Отфильтровать строки, соответствующие одному из нескольких условий

Пример 1. Фильтрация строк, соответствующих нескольким условиям

Предположим, у нас есть следующий набор данных, который показывает общий объем продаж определенных продуктов в определенных регионах для компании:

Теперь предположим, что мы хотим отфильтровать строки, в которых «Регион» — « Восток », а «Продукт» — «А ».

Для этого мы можем определить следующий диапазон ячеек, который содержит наши критерии:

Затем мы можем щелкнуть вкладку « Данные », а затем нажать кнопку « Расширенный фильтр »:

Мы выберем A1: C17 в качестве диапазона списка и F1: G2 в качестве диапазона критериев :

Как только мы нажмем « ОК », набор данных будет отфильтрован, чтобы отображались только строки, в которых «Регион» — « Восток », а «Продукт» — «А »:

Пример 2. Фильтрация строк, соответствующих одному из нескольких условий

Предположим, у нас есть следующий набор данных, который показывает общий объем продаж определенных продуктов в определенных регионах для компании:

Теперь предположим, что мы хотим отфильтровать строки, в которых Регион — Восток или Продукт — А.

Для этого мы можем определить следующий диапазон ячеек, который содержит наши критерии:

Затем мы можем щелкнуть вкладку « Данные », а затем нажать кнопку « Расширенный фильтр ».

Мы выберем A1:C17 в качестве диапазона списка и F1:G3 в качестве диапазона критериев :

Как только мы нажмем « ОК », набор данных будет отфильтрован, чтобы отображались только строки, в которых «Регион» — « Восток » или «Продукт» — «А »:

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Excel: как отфильтровать столбец по нескольким значениям

Часто вам может понадобиться отфильтровать столбец по нескольким значениям в Excel.

К счастью, это легко сделать с помощью функции расширенного фильтра .

В следующем примере показано, как использовать эту функцию на практике.

Пример: фильтрация столбца по нескольким значениям в Excel

Предположим, у нас есть следующий набор данных, содержащий информацию о различных баскетболистах:

Теперь предположим, что мы хотим отфильтровать набор данных, чтобы отображались только те строки, в которых столбец «Команда» содержит «Хит» или «Селтикс».

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

Затем щелкните вкладку « Данные » на верхней ленте, а затем нажмите кнопку « Дополнительно » в группе « Сортировка и фильтр »:

В появившемся новом окне используйте A1:C16 в качестве диапазона списка и E1:E3 в качестве диапазона критериев:

Как только вы нажмете OK , данные будут автоматически отфильтрованы, чтобы отображались только те строки, в которых название команды равно Heat или Celtics:

В этом конкретном примере мы выбрали фильтрацию данных на месте.

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

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

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

EXCEL: как применить расширенный фильтр к таблице

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

«Расширенный фильтр Excel, как следует из названия, является расширенной версией обычного фильтра. Его лучше использовать в том случае, когда необходимо провести отбор данных по более сложным критериям: например, по нескольким столбцам на основе И/ИЛИ правил»

Шлычков Константин
Эксперт и преподаватель Excel

Расширенный фильтр в Excel и его особенности

Для начала работы с функцией расширенного фильтра в Excel необходимо ознакомиться с некоторыми характерными его особенностями:

1. Расширенный фильтр позволяет произвести фильтрацию по неограниченному количеству оснований;
2. Все критерии, по которым отфильтрована таблица, представлены на виду – отдельном диапазоне на том же листе, что и исходные данные;
3. С помощью расширенного фильтра можно показать в таблице только уникальные строки;
4. Фильтровать таблицу можно при помощи формул и подстановочных знаков;
5. Расширенный фильтр можно применить сразу к разным (но обязательно схожим) таблицам;
6. При изменении условий фильтрации расширенный фильтр требует ручной настройки: для этого нужно открывать настройки расширенного фильтра и указывать новый диапазон;
7. Фильтр не чувствителен к регистру символов.

Как настроить расширенный фильтр в Excel

Шаг 1. Настроить данные

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

• Первая строка таблицы обязательно должна содержать в себе заголовки столбцов. Повторяющиеся заголовки могут вызвать проблемы при запуске расширенного фильтра;
• Рекомендуется привести данные к формату умной таблицы Excel. Она автоматически предотвращает дублирование заголовков столбцов путем добавления номеров к названию. Создать умную таблицу можно при помощи горячих клавиш: выберите любую ячейку в массиве данных и зажмите клавиши «Ctrl» и английскую «T»;
• В таблице с исходными данными не должно быть пустых строк;
• Таблица должна быть отделена от других данных снизу пустой строкой, а с боку пустым столбцом.

Шаг 2. Создать диапазон критериев

Что это? Диапазон критериев для расширенного фильтра Excel представляет собой дополнительную таблицу, где Вы устанавливаете правила фильтрации данных.

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

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

Шаг 3. Указать критерии, по которым необходимо отфильтровать таблицу

Например, если хотим, чтобы в столбце «Страна» в нашей таблице отобразились только ячейки со значением «Россия», в дополнительной таблице – в диапазоне критериев пишем «Россия», без дополнительных символов. Таким образом мы указываем все необходимые критерии, по которым хотим отобрать наши данные.

Шаг 4. Включить функцию «Расширенный фильтр»

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

На экране появится диалоговое окно «Расширенный фильтр Excel». Настроим его вместе:

Раздел «Обработка». Выберите пункт в зависимости от того, хотите ли Вы фильтровать свои данные на месте или скопировать результаты в другое местоположение.

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

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

Поле «Исходный диапазон». Выберите диапазон данных, которые хотите отфильтровать, включая заголовки.

Поле «Диапазон критериев». Снова нажмите на стрелочку справа от этого поля и вручную выберите диапазон таблицы с критериями.

Нажмите «ОК». Вы великолепны!

Совет: Если выбрать любую ячейку в наборе данных перед настройкой расширенного фильтра в Excel, то программа автоматически заполнит поле «Исходный диапазон» во всплывающем окне. Однако, необходимо проверить правильность определения диапазона таблицы. Если диапазон определен неверный – Вы можете легко это исправить: щелкните значок стрелки справа от поля «Исходный диапазон» и выберите нужный диапазон с помощью мыши или при помощи сочетания горячих клавиш «Ctrl» и «A».

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

Как работает диапазон критериев расширенного фильтра Excel

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

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

Условия, указанные в этом примере, дают команду Excel показать строки, которые содержат информацию только о штукатурке из Китая.

Критерии, введенные в разные строки, работают по логике ИЛИ, то есть критерии не будут зависеть друг от друга.

В данном примере Excel выполняет отбор данных по принципу: сначала найди все строки, содержащие в столбце товар первым словом «Штукатурка», а далее найди все строки, начинающиеся со слова «Беларусь», остальные строки скрой.

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

Операторы сравнения чисел и дат

В критериях расширенного фильтра Вы можете сравнивать различные числовые значения, используя следующие операторы сравнения:

Знак;Значение;Пример
>;Больше;A1 > B1 <;Меньше;A1 < B1 =;Равно;A1 = B1 >=;Больше или равно;A1 >= B1 ;Не равно;A1 <> B1

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

Взглянем на таблицу, в которой отображено наличие товаров на складе по странам-производителям. Представим, что хотим отобразить в таблице только те товары, которые были закуплены из Китая в августе 2022 года с ценой больше или равной 10 000 тыс.

Для этого нам необходимо создать диапазон критериев со следующими значениями:

Страна: Китай
Дата: >=01.08.2022
Дата: Цена: >10 000

В диапазоне критериев необходимо создать два столбца «Дата», чтобы отобразить в них два значения: первое – дата начала периода, который мы хотим отобразить, а второе – конец периода.

Важно: между знаками условных операторов (>,

Операторы сравнения для текстовых данных

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

=»=текст»;Фильтровать ячейки, значения которых точно равны «тексту». Excel считывает эту формулу как «=текст» текст;Фильтровать ячейки, содержимое которых начинается с «текста» <>текст;Фильтровать ячейки, значения которых не точно равны «тексту» (ячейки, содержащие «текст» как часть их содержимого, будут включены в фильтр) <текст;Фильтровать ячейки, значения которых расположены в алфавитном порядке после «текста» >текст;Фильтровать ячейки, значения которых расположены в алфавитном порядке перед «текстом»

Пример №1. Текстовый фильтр для точного соответствия

Если Вы хотите отобразить в таблице только те строки, что содержат слово «Штукатурка» и ничего более, запишите в ячейку формулу =”=Штукатурка”. Excel отобразит критерии как =Штукатурка в ячейке.

Пример №2: Фильтр текстовых значений, начинающихся с определенного символа (или символов).

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

Пример 3. Текстовый фильтр для частичных совпадений при помощи символов подстановки

В случаях, когда Вам необходимо отобразить строки, содержащие в себе набор символов вне зависимости от места их расположения в строке, нужно использовать специальные подстановочные символы (*, ?, ~).

Для того чтобы отфильтровать таблицу по определенному сочетанию символов в артикле, необходимо вписать такой критерий: *СКЗИ*

В Excel есть три подстановочных символа :

* (звездочка) — обозначает, что искомое слово может содержать любое количество любых символов после этого значка. Например, экс* может означать эксель, экскурсия, эксперт и т. д.
? (вопросительный знак) — представляет собой один символ. Например, «ло?ка» может означать «ложка» или «лодка».
~ (тильда) — за которой следуют *, ? или ~, используется, чтобы отфильтровать ячейки, содержащие настоящий вопросительный знак, звездочку или тильду.

Как использовать формулы в расширенном фильтре

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

• Формула должна принимать значение ИСТИНА или ЛОЖЬ;
• Если результатом применения формулы будет ИСТИНА – строка отобразится в результате применения фильтра, если ЛОЖЬ – нет;

• Ячейка заголовка в таблице критериев должна иметь заголовок, отличный от любого из заголовков исходной таблицы;
• Для указания исходного диапазона таблицы в Excel следует использовать абсолютные ссылки, обозначаемые знаком $ (например, $A$1), в то время как для задания критерия отбора необходимо использовать относительные ссылки без знака $ (например, A1).

Например. Для фильтрации строк в Excel по такому критерию, как сравнение суммы закупок за август и июль, необходимо создать таблицу условий, содержащую заголовок (можно так и назвать «Критерий») и одну ячейку. В этой ячейке необходимо указать критерий фильтрации, используя относительные ссылки без знака $: формулу =E5>D5 для сравнения значений столбцов E и D.

Разберем еще один пример. Представим, что необходимо отобразить в таблице только заказы на сумму выше среднего от всех зарегистрированных заказов.

Создаем таблицу условий, заголовком которой можем быть «Стоимость выше среднего». Формула, которую нужно указать в качестве критерия, выглядит так: =С6>СРЗНАЧ($С$5:$С$20), где С6 — первая ячейка в столбце «Цена», а диапазон $С$5:$С$20 — сам этот столбец. Заострим внимание на том, что диапазон столбца зафиксирован при помощи знака $ — это абсолютная ссылка. Его можно ввести вручную, или же нажатием клавиши «F4».