Как использовать функцию ФИЛЬТР Excel

Office 365 предлагает несколько замечательных функций, таких как XLOOKUP, SORT и FILTER.

При фильтрации данных в Excel, в мире до Office 365, мы в основном полагались на встроенные фильтры Excel или на самые расширенные фильтры или сложные формулы СУММПРОИЗВ.Часто это сложный обходной путь, если вам нужно отфильтровать часть набора данных.

Но с новой функцией FILTER теперь очень легко быстро фильтровать части набора данных на основе условий.

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

Но прежде чем я перейду к примеру, давайте кратко рассмотрим синтаксис функции FILTER.

Если вы хотите получить эти новые функции в Excel, вы можетеОбновление до Office 365(Присоединитесь к внутренней программе, чтобы получить доступ ко всем функциям/формулам)

Функция фильтра Excel — синтаксис

Ниже приведен синтаксис функции ФИЛЬТР:

=ФИЛЬТР(массив,включить,[if_empty])

• массив - это диапазон ячеек, в которых у вас есть данные, и вы хотите отфильтровать некоторые данные из него.
• включают - Это условие, которое сообщает функции, какие записи следует фильтровать.
• [если_пусто] – Это необязательный параметр, в котором вы можете указать, что возвращать, если функция ФИЛЬТР не находит результатов.По умолчанию (если не указано) возвращается #CALC!ошибка

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

Пример 1. Фильтрация данных по одному условию (регион)

Предположим, у вас есть набор данных, подобный приведенному ниже, и вы хотите отфильтровать только все записи в США.

Вот формула FILTER, которая делает это:

=ФИЛЬТР($A$2:$C$11,$B$2:$B$11="США")

Приведенная выше формула принимает набор данных в виде массива, а условие $B$2:$B$11=”US”

Это условие заставит функцию ФИЛЬТР проверять каждую ячейку в столбце B (ячейки, имеющие диапазон) и фильтровать только те записи, которые соответствуют этому условию.

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

Функция фильтра возвращает динамический массив результатов (это означает, что вместо возврата значения она возвращает массив, который переполняет другие ячейки).

Для этого нужно иметь область, где результат пустой.В любой ячейке диапазона (в данном примере E2:G5) уже что-то есть, и функция выдаст ошибку #SPILL.

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

В приведенной выше формуле я жестко закодировал значение диапазона, но вы также можете поместить его в ячейку и сослаться на эту ячейку со значением диапазона.

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

=FILTER($A$2:$C$11,$B$2:$B$11=I1)

Это делает формулу еще более полезной, теперь вам просто нужно изменить значение диапазона в ячейке I2, и фильтр изменится автоматически.

У вас также может быть раскрывающийся список в ячейке I2, где вы можете просто сделать выбор, и он мгновенно обновит отфильтрованные данные.

Пример 2. Фильтрация данных по одному критерию (больше или меньше)

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

Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите отфильтровать все записи с продажами более 10000 XNUMX.

Это может сделать следующая формула:

=FILTER($A$2:$C$11,($C$2:$C$11>10000))

Аргумент массива относится ко всему набору данных, в данном случае к условию ($C$2:$C$11>10000).

Формула проверяет каждую запись на наличие значения в столбце C.Если значение больше 10000, оно фильтруется, в противном случае игнорируется.

Если вы хотите получить все записи меньше 10000, вы можете использовать следующую формулу:

=FILTER($A$2:$C$11,($C$2:$C$11<10000))

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

=FILTER($A$2:$C$11,($C$2:$C$11>=LARGE(C2:C11,3)))

В приведенной выше формуле функция НАИБОЛЬШИЙ используется для получения третьего по величине значения в наборе данных.Затем используйте это значение в условии функции FILTER, чтобы получить все записи с продажами, превышающими или равными третьему по величине значению.

Пример 3. Фильтрация данных с использованием нескольких условий (И)

Предположим, у вас есть следующий набор данных, и вы хотите отфильтровать все записи в Соединенных Штатах с объемом продаж больше 10000 XNUMX.

Это условие И, вам нужно проверить две вещи — регион должен быть в США и продажи должны быть более 10000.Если выполняется только одно условие, результаты не должны фильтроваться.

Вот формула фильтра, которая будет фильтровать записи с США в качестве региона и с более чем 10000 XNUMX продаж:

=FILTER($A$2:$C$11,($B$2:$B$11="US")*($C$2:$C$11>10000))

請注意，標準（稱為包含參數）是 ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Поскольку я использую два условия и мне нужно, чтобы оба они были истинными, я использовал оператор умножения, чтобы объединить два условия.Это возвращает массив из 0 и 1, где 1 возвращается, только если выполняются оба условия.

Если совпадающих записей нет, функция вернет #CALC!Ошибка.

Если вы хотите вернуть что-то значимое (не ошибку), вы можете использовать такую ​​формулу:

=FILTER($A$2:$C$11,($B$2:$B$11="USA")*($C$2:$C$11>10000),"Nothing Found")

Здесь я использовал «не найдено» в качестве третьего параметра, который используется, когда не найдена соответствующая запись.

Пример 4. Фильтрация данных по нескольким критериям (ИЛИ)

Вы также можете изменить параметр «содержит» в функции ФИЛЬТР, чтобы проверить условия ИЛИ (где любое заданное условие может быть истинным).

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

Вот формула для этого:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+($B$2:$B$11="Canada"))

Обратите внимание, что в приведенной выше формуле я просто добавляю два условия с помощью оператора сложения.Поскольку каждое из этих условий возвращает массив ИСТИНА и ЛОЖЬ, я могу добавить комбинированный массив, который будет ИСТИНА при выполнении любого из условий.

Другим примером может быть ситуация, когда вы хотите отфильтровать все записи, в которых страной является США или объем продаж превышает 10000 XNUMX.

Это сделает следующая формула:

=FILTER($A$2:$C$11,($B$2:$B$11="US")+(C2:C11>10000))

ПРИМЕЧАНИЕ. При использовании условия И в функции ФИЛЬТР используйте оператор умножения (*), а при использовании условия ИЛИ используйте оператор сложения (+).

Пример 5. Отфильтровать данные для записей выше/ниже среднего

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

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

Вы можете сделать это с помощью следующей формулы:

=FILTER($A$2:$C$11,C2:C11>AVERAGE(C2:C11))

Опять же, для ниже среднего вы можете использовать следующую формулу:

=FILTER($A$2:$C$11,C2:C11<AVERAGE(C2:C11))

Пример 6: фильтрация только четных записей (или нечетных записей)

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

Для этого нужно проверить номер строки в функции ФИЛЬТР и отфильтровать только те номера строк, которые соответствуют условию номера строки.

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

Вот формула для этого:

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=0)

В приведенной выше формуле используется функция MOD для проверки номера строки (задаваемого функцией ROW) каждой записи.

Формула ОСТАТ(СТРОКА(A2:A11)-1,2)=0 возвращает ИСТИНА, если номер строки четный, и ЛОЖЬ, если он нечетный.Обратите внимание, что я вычел 2 из части ROW(A11:A1), потому что первая запись находится во второй строке, что настраивает номера строк так, чтобы вторая строка рассматривалась как первая запись.

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

=FILTER($A$2:$C$11,MOD(ROW(A2:A11)-1,2)=1)

Пример 7. Сортировка отфильтрованных данных с помощью формулы

Использование функции FILTER с другими функциями позволяет нам делать больше.

Например, если вы используете функцию ФИЛЬТР для фильтрации набора данных, вы можете использовать функцию СОРТИРОВКА для получения отсортированных результатов.

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

Это сделает следующая формула:

=SORT(FILTER($A$2:$C$11,($C$2:$C$11>10000)),3,-1)

Вышеупомянутая функция использует функцию FILTER для получения данных в столбце C с продажами больше 10000.Затем используйте массив, возвращенный функцией ФИЛЬТР в функции СОРТИРОВКА, чтобы отсортировать данные по продажам.

Второй параметр функции SORT равен 3, что означает сортировку по третьему столбцу.Четвертый параметр равен -1, который сортирует данные в порядке убывания.

Итак, это 7 примеров использования функции ФИЛЬТР в Excel.

Надеюсь, вы нашли этот урок полезным.