Если для данных, которые вы хотите отфильтровать, требуются условия в нескольких полях, например для фильтрации по нескольким условиям, все из которых должны быть истинными, или для отображения строк, соответствующих любому из нескольких условий (например, Тип = "Фрукты" ИЛИ Продавец = "Егоров"), можно использовать диалоговое окно "Расширенный фильтр ".
Чтобы открыть диалоговое окно Расширенный фильтр, щелкнитеДополнительныеданные>.
| Расширенный фильтр | Пример |
|---|---|
| Обзор расширенных условий фильтра | |
| Несколько условий, один столбец, любое из условий истинно | Продавец = "Егоров" ИЛИ Продавец = "Грачев" |
| Несколько условий, несколько столбцов, все условия истинны | Тип = "Фрукты" И Продажи > 1000 |
| Несколько условий, несколько столбцов, любое из условий истинно | Тип = "Фрукты" ИЛИ Продавец = "Грачев" |
| Несколько наборов условий, один столбец во всех наборах | (Продажи > 6000 И Продажи < 6500) ИЛИ (Продажи < 500) |
| Несколько наборов условий, несколько столбцов в каждом наборе | (Продавец = "Егоров" И Продажи >3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) |
| Условия с подстановочными знаками | Продавец = имя со второй буквой "г" |
Обзор расширенных условий фильтра
Работа расширенного фильтра отличается от работы фильтра в нескольких важных аспектах.
- Она отображает диалоговое окно Расширенный фильтр, а не меню "Автофильтр".
- Вы создаете диапазон условий (отдельные ячейки над данными), в который вводятся условия фильтра, а затем указываете в диалоговом окне расширенного фильтра использовать этот диапазон.
- Расширенный фильтр НЕ обновляется автоматически при изменении значений условий
Примечание
Расширенный фильтр остается доступным для сложных сценариев фильтрации, хотя новые функции, такие как Copilot в Excel, теперь могут помочь пользователям с анализом данных и фильтрацией запросов на естественном языке в качестве альтернативного подхода для некоторых случаев использования.
Общие сведения о логике И и ИЛИ
| Тип логики | Настройка | Пример | Что он находит |
|---|---|---|---|
| И логика (все условия должны быть истинными) | Поместить условия в одну строку | Тип = "Фрукты" в колонке 1 Продажи > 1000 в столбце 2 (оба в одном ряду) |
Только строки с типом "Фрукты" И значением продаж больше 1000 |
| логика ИЛИ (любое условие может быть истинным) | Поместить условия в другую строку | Строка 1: Тип = "Фрукты" Строка 2: Тип = "Мясо" (разные строки, один столбец) |
Строки, в которых тип IS "Фрукты" ИЛИ тип IS "Мясо" (или и то, и другое) |
Образец данных
Этот пример данных используется для всех процедур, описанных в этой статье.
Эти данные включают три пустые строки над диапазоном списка, которые будут использоваться как диапазон условий (A1:C4) и диапазон списка (A6:C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.
Для работы с этими данными выделите их в следующей таблице, скопируйте, а затем вставьте в ячейку A1 на новом листе Excel.
| Тип | Продавец | Продажи |
|---|---|---|
| Напитки | Шашков | 5 122 ₽ |
| Мясо | Егоров | 450 ₽ |
| фрукты | Грачев | 6328 ₽ |
| Фрукты | Егоров | 6544 ₽ |
В этом примере результирующий лист будет выглядеть следующим образом, где диапазон условий фильтрации выделен синим цветом, а диапазон списка (данные, которые нужно отфильтровать) — красным.
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
| Оператор сравнения | Значение | Пример |
|---|---|---|
| = (знак равенства) | Равно | A1=B1 |
| > (знак "больше") | Больше | A1>B1 |
| < (знак "меньше") | Меньше | A1<B1 |
| >= (знак «больше или равно») | Больше или равно | A1>=B1 |
| <= (знак «меньше или равно») | Меньше или равно | A1<=B1 |
| <> (знак «не равно») | Не равно | A1<>B1 |
Использование знака равенства для ввода текста или значения
При вводе текста или значения в ячейке знак равенства (=) используется для обозначения формулы, поэтому Excel вычисляет то, что вы вводите. Однако это может привести к неожиданным результатам фильтрации. Чтобы указать оператор сравнения "равно" для текста или значения, введите условия в виде строкового выражения в соответствующей ячейке в диапазоне условий.
=''=ввод''
где ввод — это текст или значение, которое нужно найти. Например:
| Вводится в ячейку | Вычисляется и отображается |
|---|---|
| ="=Егоров" | =Егоров |
| ="=3000" | =3000 |
Учет регистра
При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.
Использование заранее определенных имен
Вы можете назвать диапазон Условия, и ссылка на диапазон автоматически появится в поле Диапазон условий. Вы также можете указать имя База данных для диапазона списка, который будет фильтроваться, и имя Извлечение для области, в которой вы собираетесь вставлять строки. Эти диапазоны автоматически появятся в полях Исходный диапазон и Поместить результат в диапазон соответственно.
Создание условий с помощью формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
- Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
- Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''=запись'' - Не используйте название столбца в качестве названия условия. Либо оставьте название условия пустым, либо используйте название, не являющееся названием столбца в диапазоне списка (в последующих примерах: "Среднее арифметическое" и "Точное совпадение").
Если вместо относительной ссылки на ячейку или имени диапазона использовать в формуле название столбца, Excel отобразит значение ошибки, например #NAME? или #VALUE! #ЗНАЧ!. Эту ошибку можно проигнорировать, поскольку она не влияет на фильтрацию диапазона списка. - В формуле, которая используется для условий, необходимо использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке данных.
- Все остальные ссылки в формуле должны быть абсолютными.
Несколько условий, один столбец, любое из условий истинно
Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")
Используйте эту функцию, если требуется отфильтровать строки, в которых один столбец соответствует ЛЮБОМУ из нескольких значений. Будут показаны обе строки с Даволио И строки с Бьюкененом.
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий. Например, в первые две строки диапазона условий введите следующее:
Тип Продавец Продажи ="=Егоров" ="=Грачев" Щелкните ячейку в диапазоне списка.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько условий, несколько столбцов, все условия истинны
Логическое выражение: (Тип = "Фрукты" И Продажи > 1000)
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий. Например, введите:
Тип Продавец Продажи ="=Фрукты" >1000 Щелкните ячейку в диапазоне списка.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько условий, несколько столбцов, любое из условий истинно
Логическое выражение: (Тип = "Фрукты" ИЛИ Продавец = "Грачев")
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий. Например, введите:
Тип Продавец Продажи ="=Фрукты" ="=Грачев" Щелкните ячейку в диапазоне списка.
На вкладке " Данные " в группе "Сортировка & фильтр " щелкните "Дополнительно".
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько наборов условий, один столбец во всех наборах
Логическое выражение: ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком. Например, введите:
Тип Продавец Продажи Продажи >6000 <6500 <500 Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
Совет.
При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$D$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽
Несколько наборов условий, несколько столбцов в каждом наборе
Логическое выражение: ( (Продавец = "Егоров" И Продажи >3000) ИЛИ (Продавец = "Грачев" И Продажи > 1500) )
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельных столбцах или строках. Например, введите:
Тип Продавец Продажи ="=Егоров" >3000 ="=Грачев" >1500 Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Используя пример, получим следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Условия с подстановочными знаками
Логическое выражение: Продавец = имя со второй буквой "г"
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
Чтобы найти строки, в которых текстовое значение в столбце начинается с него, введите один или несколько символов без знака равенства (=). Например, если ввести условие Бел, будут найдены строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
Воспользуйтесь подстановочными знаками.
Используйте Чтобы найти ? (вопросительный знак) Любой символ
Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"Звездочка (*) Любое количество символов
Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"~ (тильда), за которой следует ?, * или ~ Вопросительный знак, звездочку или тильду
Например, ан91~? соответствует результат "ан91?"
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
В строках под названиями столбцов введите условия, которым должен соответствовать результат. Используя пример, введите:
Тип Продавец Продажи ="=Мя*" ="=?г*" Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите один из следующих вариантов : "Фильтровать список", "На месте", "Скрытие строк, не отвечающих условиям", или "Копировать в другое место", копировать строки, соответствующие условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Напитки Шашков 5 122 ₽ Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽
Удаление или очистка расширенного фильтра
После применения расширенного фильтра вы можете удалить его, чтобы снова увидеть все свои данные. Вот как это сделать.
- Щелкните любую ячейку в отфильтрованном диапазоне данных.
- Перейдите на вкладку Данные.
- В группе Сортировка & фильтр нажмите кнопку Очистить.
- Все строки будут отображены снова.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.