Перейти к основному контенту
Office

Использование расширенных условий фильтрации

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке).

Если данные, которые нужно отфильтровать, должны иметь сложные условия (например, тип = "фрукты" или продавец = "Белов"), можно использовать диалоговое окно " Расширенный фильтр ".

Чтобы открыть диалоговое окно Расширенный фильтр , выберите элемент данные _Гт_ на странице " Дополнительные параметры".

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

Пример

Overview

Несколько условий, один столбец, любое из условий истинно

Продавец = "Егоров" ИЛИ Продавец = "Грачев"

Несколько условий, несколько столбцов, все условия истинны

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

Несколько условий, несколько столбцов, любое из условий истинно

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

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

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

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

(Продавец = "Белова" и >3000 продаж) ИЛИ
(Продавец = "Батурин" и Sales _Гт_ 1500)

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

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

Обзор

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

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

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

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

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

Данные включают четыре пустых строки над диапазоном, который будет использоваться в качестве диапазона условий (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 не учитывается регистр букв. Однако для поиска с учетом регистра можно воспользоваться формулой. Пример см. в разделе Условия с подстановочными знаками.

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

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

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

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

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

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

    =''= ввод ''

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

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

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

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

Несколько условий, один столбец, любое из условий истинно

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

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

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

    Тип

    Продавец

    Продажи

    ="=Егоров"

    ="=Грачев"

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавец

    Продажи

    Мясо

    Егоров

    450 ₽

    Фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

Несколько условий, несколько столбцов, все условия истинны

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

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

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

    Тип

    Продавец

    Продажи

    ="=Фрукты"

    >1 000

  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. Чтобы найти строки, отвечающие нескольким наборам условий, каждый из которых содержит условия для одного столбца, используйте несколько столбцов с одинаковым заголовком. Используя пример, введите:

    Тип

    Продавец

    Продажи

    Продажи

    >6 000

    <6 500

    <500

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

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

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

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

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

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

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

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

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

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

    Тип

    Продавец

    Объем продаж

    Мясо

    Егоров

    450 ₽

    Фрукты

    Грачев

    6 328 ₽

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

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

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

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

    Тип

    Продавец

    Объем продаж

    ="=Егоров"

    >3 000

    ="=Грачев"

    >1 500

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

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

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

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

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

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

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

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

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

    Тип

    Продавец

    Продажи

    фрукты

    Грачев

    6 328 ₽

    Фрукты

    Егоров

    6 544 ₽

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

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

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

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

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

      Используйте

      Чтобы найти

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

      Любой одиночный
      символ Например, См_км_с находит слова "Смит" и "строфа"

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

      Любое количество символов
      Например, * East находит "Северо-Восток" и "Юго-Восток"

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

      Вопросительный знак, звездочка или тильда
      Например, Ан 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

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

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

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

×