Фильтрация с применением сложных условий

Если требуется отфильтровать данные с помощью сложных условий (например, Тип = "Фрукты" ИЛИ Продавец = "Белова"), можно использовать диалоговое окно Расширенный фильтр.

Чтобы открыть диалоговое окно Расширенный фильтр, выберите пункты Данные > Дополнительно.

Группа ''Сортировка и фильтр'' на вкладке ''Данные''

Расширенный фильтр

Пример

Общие сведения

Несколько условий для одного столбца, где хотя бы одно из условий должно быть истинным

Продавец = "Белова" ИЛИ Продавец = "Кротов"

Несколько условий для нескольких столбцов, где все условия должны быть истинными

Тип = "Фрукты" И Продажи > 1000

Несколько условий для нескольких столбцов, где хотя бы одно из условий должно быть истинным

Тип = "Фрукты" ИЛИ Продавец = "Кротов"

Несколько наборов условий, один столбец во всех наборах

(Продажи > 6000 И Продажи < 6500) ИЛИ (Продажи < 500)

Несколько наборов условий, несколько столбцов в каждом наборе

(Продавец = "Белова" И Продажи >3000) ИЛИ
(Продавец = "Кротов" И Продажи > 1500)

Условия с подстановочными знаками

Продавец = имя со второй буквой "г"

Общие сведения

Между командой Дополнительно и командой Фильтр есть несколько важных отличий.

  • Вместо меню "Автофильтр" отображается диалоговое окно Расширенный фильтр.

  • Расширенные условия вводятся в отдельный диапазон условий на листе над диапазоном ячеек или таблицей, которые требуется отфильтровать. В Microsoft Office Excel в качестве источника расширенных условий используется отдельный диапазон условий в диалоговом окне Расширенный фильтр.

Образец данных

Приведенный ниже образец данных используется во всех процедурах, описанных в этой статье.

Эти данные включают четыре пустые строки над диапазоном списка, которые будут использоваться как диапазон условий (A1:C4), и диапазон списка (A6:C10). Диапазон условий содержит названия столбцов и по крайней мере одну пустую строку между значениями условий и диапазоном списка.

Для работы с этими данными, выделите их в указанной ниже таблице, скопируйте, а затем вставьте в ячейку A1 на новом листе Excel.

Тип

Продавец

Продажи

Тип

Продавец

Продажи

Напитки

Ермолаева

5 122 р.

Мясо

Белова

450 р.

фрукты

Кротов

6 328 р.

Фрукты

Белова

6 544 р.

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

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

Оператор сравнения

Значение

Пример

= (знак равенства)

Равно

A1=B1

> (знак больше)

Больше

A1>B1

< (знак меньше)

Меньше

A1<B1

>= (знак больше или равно)

Больше или равно

A1>=B1

<= (знак меньше или равно)

Меньше или равно

A1<=B1

<> (знак не равно)

Не равно

A1<>B1

Использование знака равенства для ввода текста и значений

Поскольку при вводе текста или значения в ячейку знак равенства (=) используется для обозначения формулы, Excel вычисляет введенную формулу; однако это может привести к неожиданным результатам фильтрации. Чтобы указать оператор сравнения для текста или значения, введите условие в виде строкового выражения в соответствующую ячейку диапазона условий:

=''= запись ''

Здесь запись — это текст или значение, которое требуется найти. Например:

Введенный текст

Excel оценивает и отображает как

="=Белова"

=Белова

="=3000"

=3000

Учет регистра

При фильтрации текстовых данных в Excel не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.

Использование предварительно заданных имен

Можно присвоить диапазону имя Условия, и ссылка на диапазон будет автоматически появляться в поле Диапазон условий. Можно также определить имя База_данных для фильтруемого диапазона данных и имя Извлечение для области, в которую нужно вставить строки, и ссылки на эти диапазоны появятся автоматически в полях Исходный диапазон и Поместить результат в диапазон соответственно.

Создание условия с помощью формулы

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

  • Формула должна возвращать результат ИСТИНА или ЛОЖЬ.

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

    =''= запись ''

  • Не используйте подпись столбца в качестве подписи условия. Либо оставьте подпись условия пустой, либо используйте подпись, которая не является подписью столбцов диапазона (в примерах ниже — «Среднее арифметическое» и «Точное соответствие»).

    Если в формуле вместо относительной ссылки на ячейку или имени диапазона используется название столбца, в ячейке с условием будет выведено значение ошибки #ИМЯ? или #ЗНАЧ! Эту ошибку можно не исправлять, поскольку она не повлияет на результаты фильтрации.

  • В формуле, которая применяется в качестве условия, для ссылки на соответствующую ячейку в первой строке необходимо использовать относительную ссылку.

  • Все другие ссылки в формуле должны быть абсолютными.

Несколько условий для одного столбца, где любое условие может быть истинным

Логическое выражение:    (Продавец = "Егоров" ИЛИ Продавец = "Орехов")

  1. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

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

    Тип

    Продавец

    Продажи

    ="=Егоров"

    ="=Грачев"

  3. Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне A6:C10.

  4. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  5. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3.

    Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  7. Используя пример, получаем следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    Мясо

    Белова

    450 р.

    фрукты

    Кротов

    6 328 р.

    Фрукты

    Белова

    6 544 р.

Несколько условий для нескольких столбцов, где все условия должны быть истинными

Логическое выражение:    (Тип = "Фрукты" И Продажи > 1 000)

  1. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

  2. Чтобы найти строки, отвечающие нескольким условиям в нескольких столбцах, введите все условия в одной строке диапазона условий. В этом примере следует ввести:

    Тип

    Продавец

    Продажи

    ="=Фрукты"

    >1000

  3. Щелкните ячейку в диапазоне. В приведенном примере следует щелкнуть любую ячейку в диапазоне A6:C10.

  4. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  5. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$2.

    Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  7. Используя пример, получаем следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    фрукты

    Кротов

    6 328 р.

    Фрукты

    Белова

    6 544 р.

Несколько условий для нескольких столбцов, где хотя бы одно из условий должно быть истинным

Логическое выражение:    (Тип = "Фрукты" ИЛИ Продавец = "Кротов")

  1. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

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

    Тип

    Продавец

    Продажи

    ="=Фрукты"

    ="=Грачев"

  3. Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

  4. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  5. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  6. Введите в поле Диапазон условий ссылку на диапазон условий, включающий подписи столбцов условий. В данном примере следует указать диапазон $A$1:$B$3.

    Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  7. Используя пример, получаем следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    фрукты

    Кротов

    6 328 р.

    Фрукты

    Белова

    6 544 р.

Несколько наборов условий, один столбец во всех наборах

Логическое выражение:    ( (Продажи > 6000 И Продажи < 6500 ) ИЛИ (Продажи < 500) )

  1. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

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

    Тип

    Продавец

    Продажи

    Продажи

    >6000

    <6500

    <500

  3. Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

  4. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  5. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  6. Введите в поле Диапазон условий ссылку на диапазон условий, включающий подписи столбцов условий. В данном примере следует указать диапазон $A$1:$D$3.

    Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  7. Используя пример, получаем следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    Мясо

    Белова

    450 р.

    фрукты

    Кротов

    6 328 р.

Несколько наборов условий, несколько столбцов в каждом наборе

Логическое выражение:    ((Продавец = "Егоров" И Продажи >3000) ИЛИ(Продавец = "Орехов" И Продажи > 1500))

  1. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

  2. Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для нескольких столбцов, введите каждый набор условий в отдельном столбце и строке. В данном примере следует ввести:

    Тип

    Продавец

    Продажи

    ="=Белова"

    >3000

    ="=Кротов"

    >1500

  3. Щелкните ячейку в диапазоне. В приведенном примере следует щелкнуть любую ячейку в диапазоне A6:C10.

  4. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  5. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  6. В поле Диапазон условий введите ссылку на диапазон условий, включая названия условий. Используя пример, введите $A$1:$C$3. Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  7. Используя пример, получим следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    фрукты

    Кротов

    6 328 р.

    Фрукты

    Белова

    6 544 р.

Условия с подстановочными знаками

Логическое выражение:    Продавец = имя со второй буквой "р"

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

    • Для поиска строк, в которых текстовое значение в столбце начинается с определенной последовательности знаков, введите эти знаки, не используя знак равенства (=). Например, если ввести в качестве условия Его, Excel найдет строки Егоров, Егорова и Егоренко.

    • Используйте подстановочные знаки.

      Используйте

      Чтобы найти

      ? (вопросительный знак)

      Любой символ (один)
      Пример: условию "бар?н" соответствуют результаты "барин" и "барон"

      * (звездочка)

      Любое количество символов
      Пример: условию "*-восток" соответствуют результаты "северо-восток" и "юго-восток"

      ~ (тильда), за которой следует ?, * или ~

      Вопросительный знак, звездочка или тильда
      Например, условию "фг91~?" соответствует результат "фг91?".

  2. Вставьте не менее трех пустых строк над исходным диапазоном, чтобы использовать их для диапазона условий. Диапазон условий должен включать в себя названия столбцов. Убедитесь в том, что между значениями условий и исходным диапазоном имеется по крайней мере одна пустая строка.

  3. В строках под названиями столбцов введите условия, которым должен соответствовать результат. Используя пример, введите:

    Тип

    Продавец

    Продажи

    ="=Мя*"

    ="=?г*"

  4. Щелкните ячейку в диапазоне списка. Используя пример, щелкните любую ячейку в диапазоне списка A6:C10.

  5. На вкладке Данные в группе Сортировка и фильтр нажмите Дополнительно.

    Группа ''Сортировка и фильтр'' на вкладке ''Данные''

  6. Выполните одно из следующих действий.

    • Чтобы показать результат фильтрации, скрыв ненужные строки, установите переключатель фильтровать список на месте.

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

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

  7. Введите в поле Диапазон условий ссылку на диапазон условий, включающий подписи столбцов условий. В данном примере следует указать диапазон $A$1:$B$3.

    Чтобы временно убрать диалоговое окно Расширенный фильтр, пока вы выбираете диапазон условий, нажмите кнопку Свернуть диалоговое окно Изображение кнопки .

  8. Используя пример, получаем следующий отфильтрованный результат для диапазона списка:

    Тип

    Продавец

    Продажи

    Напитки

    Ермолаева

    5 122 р.

    Мясо

    Белова

    450 р.

    фрукты

    Кротов

    6 328 р.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×