Если для фильтрации данных требуются условия по нескольким полям, например фильтрация по нескольким условиям, которые должны быть истинными, или отображение строк, соответствующих любому из нескольких условий (например, Type = "Produce" or Salesperson = "Davolio"), можно использовать диалоговое окно Расширенный фильтр .
Чтобы открыть диалоговое окно Расширенный фильтр, щелкните Дополнительные данные>.
| Расширенный фильтр | Пример |
|---|---|
| Обзор расширенных условий фильтра | |
| Несколько условий, один столбец, любое из условий истинно | Продавец = "Егоров" ИЛИ Продавец = "Грачев" |
| Несколько условий, несколько столбцов, все условия истинны | Type = "Produce" AND Sales > 1000 |
| Несколько условий, несколько столбцов, любое из условий истинно | Тип = "Фрукты" ИЛИ Продавец = "Грачев" |
| Несколько наборов условий, один столбец во всех наборах | (Продажи > 6000 И Продажи < 6500 ) OR (Продажи < 500) |
| Несколько наборов условий, несколько столбцов в каждом наборе | (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) |
| Условия с подстановочными знаками | Продавец = имя со второй буквой "г" |
Обзор расширенных условий фильтра
Расширенный фильтр работает иначе, чем фильтр в нескольких важных направлениях.
- Она отображает диалоговое окно Расширенный фильтр, а не меню "Автофильтр".
- Вы создаете диапазон условий (отдельные ячейки над данными), в котором вводите условия фильтра, а затем указываете в диалоговом окне Расширенный фильтр использовать этот диапазон.
- Расширенный фильтр не обновляется автоматически при изменении значений условий
Примечание
Расширенный фильтр по-прежнему доступен для сложных сценариев фильтрации, хотя новые функции, такие как Copilot в Excel, теперь могут помочь пользователям с анализом данных и фильтрацией по запросам на естественном языке в качестве альтернативы для некоторых вариантов использования.
Общие сведения о логике AND и OR
| Тип логики | Настройка | Пример | Что он находит |
|---|---|---|---|
| Логика AND (все критерии должны быть истинными) | Размещение условий в одной строке | Type = "Produce" в столбце 1 Продажи > 1000 в столбце 2 (оба в одной строке) |
Только строки, в которых тип IS "Производить" и продажи is больше 1000 |
| Логика OR (любое условие может быть истинным) | Размещение условий в другой строке | Строка 1: Type = "Produce" Строка 2. Type = "Meat" (разные строки, один столбец) |
Строки, в которых тип 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 не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.
Использование заранее определенных имен
Вы можете назвать диапазон Условия, и ссылка на диапазон автоматически появится в поле Диапазон условий. Вы также можете указать имя База данных для диапазона списка, который будет фильтроваться, и имя Извлечение для области, в которой вы собираетесь вставлять строки. Эти диапазоны автоматически появятся в полях Исходный диапазон и Поместить результат в диапазон соответственно.
Создание условий с помощью формулы
В качестве условия можно использовать значение, вычисленное с помощью формулы. Обратите внимание на важные моменты, указанные ниже.
- Формула должна возвращать результат ИСТИНА или ЛОЖЬ.
- Поскольку используется формула, введенное строковое выражение должно иметь обычный вид, а не тот, который показан ниже:
=''=entry'' ' - Не используйте название столбца в качестве названия условия. Либо оставьте название условия пустым, либо используйте название, не являющееся названием столбца в диапазоне списка (в последующих примерах: "Среднее арифметическое" и "Точное совпадение").
Если вы используете метку столбца в формуле вместо ссылки на относительную ячейку или имя диапазона, Excel отображает значение ошибки, например #NAME? или #VALUE! #ЗНАЧ!. Эту ошибку можно проигнорировать, поскольку она не влияет на фильтрацию диапазона списка. - В формуле, которая используется для условий, необходимо использовать относительную ссылку для ссылки на соответствующую ячейку в первой строке данных.
- Все остальные ссылки в формуле должны быть абсолютными.
Несколько условий, один столбец, любое из условий истинно
Логическое выражение: (Продавец = "Егоров" ИЛИ Продавец = "Грачев")
Используйте его, если требуется отфильтровать строки, в которых один столбец соответствует ANY из нескольких значений. Будут показаны обе строки со строками Davolio AND с Бьюкененом.
Чтобы найти строки, отвечающие нескольким условиям для одного столбца, введите условия непосредственно одно под другим в разных строках диапазона условий. В качестве примера введите следующую строку в первые две строки диапазона условий:
Тип Продавец Продажи ="=Егоров" ="=Грачев" Щелкните ячейку в диапазоне списка.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько условий, несколько столбцов, все условия истинны
Логическая логика: (Type = "Produce" AND Sales > 1000)
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий. В качестве примера введите:
Тип Продавец Продажи ="=Фрукты" >1000 Щелкните ячейку в диапазоне списка.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько условий, несколько столбцов, любое из условий истинно
Логическая логика: (Type = "Produce" OR Salesperson = "Buchanan")
Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, где любое условие может быть истинным, введите эти условия в разных строках диапазона условий. В качестве примера введите:
Тип Продавец Продажи ="=Фрукты" ="=Грачев" Щелкните ячейку в диапазоне списка.
На вкладке Данные в группе Фильтр & сортировки нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Несколько наборов условий, один столбец во всех наборах
Логическая логика: ( (Продажи > 6000 И Продажи < 6500 ) OR (Продажи < 500) )
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком. В качестве примера введите:
Тип Продавец Продажи Продажи >6000 <6500 <500 Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
-
Совет.
При копировании отфильтрованных строк в другое место можно указать, какие столбцы следует включить в операцию копирования. Перед фильтрацией скопируйте нужные названия столбцов в первую строку области, в которую вы собираетесь вставить отфильтрованные строки. При применении фильтра введите ссылку на скопированные названия столбцов в поле Поместить результат в диапазон. Тогда скопированные строки будут включать только те столбцы, названия которых вы скопировали.
-
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$D$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽
Несколько наборов условий, несколько столбцов в каждом наборе
Логическая логика: ( (Salesperson = "Davolio" AND Sales >3000) OR (Salesperson = "Buchanan" AND Sales > 1500) )
Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельных столбцах или строках. В качестве примера введите:
Тип Продавец Продажи ="=Егоров" >3000 ="=Грачев" >1500 Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.
Используя пример, получим следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи фрукты Грачев 6 328 ₽ Фрукты Егоров 6 544 ₽
Условия с подстановочными знаками
Логическое выражение: Продавец = имя со второй буквой "г"
Чтобы найти текстовые значения с совпадающими знаками в некоторых из позиций, выполните одно или несколько действий, описанных ниже.
Введите один или несколько символов без знака равенства (=), чтобы найти строки с текстовым значением в столбце, который начинается с этих символов. Например, если ввести условие Бел, будут найдены строки с ячейками, содержащими слова "Белов", "Беляков" и "Белугин".
Воспользуйтесь подстановочными знаками.
Используйте Чтобы найти ? (вопросительный знак) Любой символ
Пример: условию "стро?а" соответствуют результаты "строфа" и "строка"Звездочка (*) Любое количество символов
Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"~ (тильда), за которой следует ?, * или ~ Вопросительный знак, звездочка или тильда
Например, fy91~? соответствует результат "ан91?"
Вставьте как минимум три пустые строки над диапазоном списка, которые можно использовать в качестве диапазона условий. Диапазон условий должен включать названия столбцов. Убедитесь, что есть по крайней мере одна пустая строка между значениями условий и диапазоном списка.
В строках под названиями столбцов введите условия, которым должен соответствовать результат. Используя пример, введите:
Тип Продавец Продажи ="=Мя*" ="=?г*" Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.
На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно.
Выберите Фильтровать список, на месте, скрывать строки, которые не соответствуют вашим условиям, или Копировать в другое расположение, копировать строки, соответствующие вашим условиям, в другую область листа.
В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$B$3.
Используя пример, получаем следующий отфильтрованный результат для диапазона списка:
Тип Продавец Продажи Напитки Шашков 5 122 ₽ Мясо Егоров 450 ₽ фрукты Грачев 6 328 ₽
Удаление или очистка расширенного фильтра
После применения расширенного фильтра может потребоваться удалить его, чтобы снова просмотреть все данные. Вот как это сделать.
- Щелкните любую ячейку в отфильтрованном диапазоне данных.
- Перейдите на вкладку Данные.
- В группе Фильтр & сортировки нажмите кнопку Очистить.
- Все строки будут отображаться снова.
Дополнительные сведения
Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществах.