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

Обновлено: 07.07.2024

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

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

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

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

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

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

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

(Продавец = "Егоров" И Продажи >3000) ИЛИ
(Salesperson = "Buchanan" AND Sales > 1500)

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

Обзор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=''= ввод ''

где ввод — искомый текст или значение. Например:

Вводится в ячейку

Вычисляется и отображается

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

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

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

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

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

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

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

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

=''= ввод ''

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Используйте

Чтобы найти

Любой символ
Например, если найти "кузнецов" и "смайл"

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

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

Вопроси метка, звездочка или тильда
Например, fy91

? соответствует результат "ан91?"

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

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

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

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

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

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

Функция ФИЛЬТР позволяет выполнять фильтрацию диапазона данных на основе условий, которые вы определяете.

Примечание: Эта функция в настоящее время доступна только Microsoft 365 подписчикам.

В следующем примере мы использовали формулу =ФИЛЬТР(A5:D20;C5:C20=H2;"") для возврата всех записей для Apple, как было выбрано в ячейке H2, и, если яблоки нет, возвращает пустую строку ("").

Функция ФИЛЬТР фильтрует массив с учетом массива логических значений (ИСТИНА/ЛОЖЬ).

=ФИЛЬТР(массив;включить;[если_пусто])

Массив или диапазон для фильтрации

Массив логических переменных с аналогичной высотой или шириной, что и массив.

Значение, возвращаемое, если все значения во включенном массиве пустые (фильтр не возвращает ничего)

Массивом может быть ряд значений, столбец со значениями или комбинация строк и столбцов значений. В приведенном выше примере массив для нашей формулы ФИЛЬТР представляет собой диапазон A5:D20.

Функция ФИЛЬТР возвращает массив, который будет переноситься на другие ячейки, если является конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете структурированные ссылки. Дополнительные сведения см. в статье о переносе массива.

Примеры

Функция ФИЛЬТР, используемая для возврата нескольких условий

В данном случае мы используем оператор умножения (*) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" И находящихся в восточном регионе: =ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);"").

Функция ФИЛЬТР, используемая для возврата нескольких условий и сортировки

В данном случае мы используем предыдущую функцию ФИЛЬТР с функцией СОРТ для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" И находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)*(A5:A20=H2);"");4;-1)

В данном случае мы используем функцию ФИЛЬТР с оператором сложения (+) для возврата всех значений в диапазоне массива (A5:D20), содержащих текст "Яблоко" ИЛИ находящихся в восточном регионе, а затем для сортировки единиц в порядке убывания: =СОРТ(ФИЛЬТР(A5:D20;(C5:C20=H1)+(A5:A20=H2);""),4;-1).

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter) :

Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).

Основа

Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):

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

Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:

Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:

Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.

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

Добавляем макрос

"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!

Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:

Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:

Так все гораздо лучше, правда? :)

Реализация сложных запросов

Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:

Критерий	Результат
гр* или гр	все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д.
=лук	все ячейки именно и только со словом Лук, т.е. точное совпадение
лив или *лив	ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д.
=п*в	слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д.
а*с	слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д.
=*с	слова оканчивающиеся на С
=.	все ячейки с текстом из 4 символов (букв или цифр, включая пробелы)
=м. н	все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д.
=*н??а	все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д.
>=э	все слова, начинающиеся с Э , Ю или Я
<>о	все слова, не содержащие букву О
<>*вич	все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству)
=	все пустые ячейки
<>	все непустые ячейки
>=5000	все ячейки со значением больше или равно 5000
5 или =5	все ячейки со значением 5
>=3/18/2013	все ячейки с датой позже 18 марта 2013 (включительно)

Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).

Логические связки И-ИЛИ

Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND) :

Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".

Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:

Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:

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

Вывести на экран информацию по одному / нескольким параметрам можно с помощью фильтрации данных в Excel.

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

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

Имеется простая таблица, не отформатированная и не объявленная списком. Включить автоматический фильтр можно через главное меню.

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

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

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

Сразу видим результат:

Особенности работы инструмента:

Автофильтр работает только в неразрывном диапазоне. Разные таблицы на одном листе не фильтруются. Даже если они имеют однотипные данные.
Инструмент воспринимает верхнюю строчку как заголовки столбцов – эти значения в фильтр не включаются.
Допустимо применять сразу несколько условий фильтрации. Но каждый предыдущий результат может скрывать необходимые для следующего фильтра записи.

У расширенного фильтра гораздо больше возможностей:

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

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

Готовый пример – как использовать расширенный фильтр в Excel:

Создадим таблицу с условиями отбора. Для этого копируем заголовки исходного списка и вставляем выше. В табличке с критериями для фильтрации оставляем достаточное количество строк плюс пустая строка, отделяющая от исходной таблицы.
Настроим параметры фильтрации для отбора строк со значением «Москва» (в соответствующий столбец таблички с условиями вносим = «=Москва»). Активизируем любую ячейку в исходной таблице. Переходим на вкладку «Данные» - «Сортировка и фильтр» - «Дополнительно».
Заполняем параметры фильтрации. Исходный диапазон – таблица с исходными данными. Ссылки появляются автоматически, т.к. была активна одна из ячеек. Диапазон условий – табличка с условием.
Выходим из меню расширенного фильтра, нажав кнопку ОК.

В исходной таблице остались только строки, содержащие значение «Москва». Чтобы отменить фильтрацию, нужно нажать кнопку «Очистить» в разделе «Сортировка и фильтр».

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

Рассмотрим применение расширенного фильтра в Excel с целью отбора строк, содержащих слова «Москва» или «Рязань». Условия для фильтрации должны находиться в одном столбце. В нашем примере – друг под другом.

Заполняем меню расширенного фильтра:

Получаем таблицу с отобранными по заданному критерию строками:

Выполним отбор строк, которые в столбце «Магазин» содержат значение «№1», а в столбце стоимость – «>1 000 000 р.». Критерии для фильтрации должны находиться в соответствующих столбцах таблички для условий. На одной строке.

Заполняем параметры фильтрации. Нажимаем ОК.

Оставим в таблице только те строки, которые в столбце «Регион» содержат слово «Рязань» или в столбце «Стоимость» - значение «>10 000 000 р.». Так как критерии отбора относятся к разным столбцам, размещаем их на разных строках под соответствующими заголовками.

Применим инструмент «Расширенный фильтр»:

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

Результат формулы – это критерий отбора.
Записанная формула возвращает результат ИСТИНА или ЛОЖЬ.
Исходный диапазон указывается посредством абсолютных ссылок, а критерий отбора (в виде формулы) – с помощью относительных.
Если возвращается значение ИСТИНА, то строка отобразится после применения фильтра. ЛОЖЬ – нет.

Отобразим строки, содержащие количество выше среднего. Для этого в стороне от таблички с критериями (в ячейку I1) введем название «Наибольшее количество». Ниже – формула. Используем функцию СРЗНАЧ.

Выделяем любую ячейку в исходном диапазоне и вызываем «Расширенный фильтр». В качестве критерия для отбора указываем I1:I2 (ссылки относительные!).

В таблице остались только те строки, где значения в столбце «Количество» выше среднего.

Чтобы оставить в таблице лишь неповторяющиеся строки, в окне «Расширенного фильтра» поставьте птичку напротив «Только уникальные записи».

Нажмите ОК. Повторяющиеся строки будут скрыты. На листе останутся только уникальные записи.

Читайте также: