Как фильтровать данные в Excel

В 365 версии программы, для фильтрации данных, мы можем использовать ПРОСМОТРХ, СОРТ И ФИЛЬТР.

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

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

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

Функция ФИЛЬТР

Вызывается она так:

=ФИЛЬТР(диапазон_данных,критерии,[если_совпадений_не_найдено])

• диапазон данных — тут все понятно, это тот диапазон, который мы будем фильтровать;
• критерии —  критерии, по которым мы будем фильтровать;
• если_совпадений_не_найдено —  не обязательно указывать этот параметр, он говорит функции, что нам нужно вернуть если она не найдет каких-либо совпадений.

Итак, перейдем к примерам.

Стандартная фильтрация

Допустим, у нас есть такая табличка:

Наша задача — отфильтровать данные таким образом, чтобы остались только люди из Америки.

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;$B$2:$B$11="US")

Вот и все!

Так как мы указали критерий «US», функция вывела нам только совпадающие строки.

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

Результат будет динамичен, то есть, если что-то измениться в изначальных данных, это изменится и в конечных данных.

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

Также необязательно прописывать критерий руками, можно просто вставить ссылку на какую-либо ячейку.

Вот пример:

=ФИЛЬТР($A$2:$C$11;$B$2:$B$11=I1)

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

Это, грубо говоря, сравнение чисел. Если больше то или если меньше то.

Допустим, у нас есть такая табличка:

Наша задача вывести продавцов, продажи которых больше 10000.

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;($C$2:$C$11>10000))

Результат вы видите.

Значения сравниваются и функция выдает нам нужные строки.

Вот пример использования функции, если нужно найти не больше 10000, а меньше:

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

А вот пример использования, когда вам нужно вывести топ-3 продавцов:

=ФИЛЬТР($A$2:$C$11;($C$2:$C$11>=НАИБОЛЬШИЙ(C2:C11;3)))

Фильтруем по нескольким критериям (И)

Допустим, у нас есть такая табличка:

Наша задача теперь — вывести продавцов из америки, чьи продажи больше 10000.

Грубо говоря, теперь нам нужно использовать 2 критерия, а не 1.

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="US")*($C$2:$C$11>10000))

Вот и все! Результат вы видите.

Мы используем “*” между критериями, так как нам нужно чтобы выполнялись оба. Так как результатом выполнения критерия является число 1 или 0, то при выполнении обоих критериев будет 1*1, в таком случае мы получаем верный результат, а если какой-то не выполнится, будет 1*0 или 0*1, в таком случае конечный итог 0 и строка выведена не будет.

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

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="USA")*($C$2:$C$11>10000);"Ничего не найдено")

Фильтруем по разным критериям (ИЛИ)

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

Допустим, у нас есть такая табличка:

Наша задача — вывести продавцов из «Canada» или «US».

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="US")+($B$2:$B$11="Canada"))

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

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

Вот пример:

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="US")+(C2:C11>10000))

Фильтруем на основе средних значений

Даже такое, в Excel, можно сделать.

Допустим, у нас есть такая табличка:

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

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;C2:C11>СРЗНАЧ(C2:C11))

Точно так же и для вывода строк, в которых продажи меньше среднего:

=ФИЛЬТР($A$2:$C$11;C2:C11<СРЗНАЧ(C2:C11))

Вывод строк с четными/нечетными порядковыми номерами

А теперь давайте рассмотрим такой пример.

Допустим, у нас есть та же табличка:

Наша задача — вывести все строки с нечетными порядковыми номерами.

Для этого формула принимает такой вид:

=ФИЛЬТР($A$2:$C$11;ОСТАТ(СТРОКА(A2:A11)-1;2)=0)

А, вот так, в случае если вам нужны четные:

=ФИЛЬТР($A$2:$C$11;ОСТАТ(СТРОКА(A2:A11)-1;2)=1)

Как отсортировать полученные данные?

А теперь рассмотрим комбинацию функции ФИЛЬТР с функцией СОРТ.

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

Допустим, у нас есть такая табличка:

Наша задача — сначала вывести всех продавцов, продажи которых больше чем 10000, а потом отсортировать их по убыванию.

Для этого формула принимает такой вид:

=СОРТ(ФИЛЬТР($A$2:$C$11;($C$2:$C$11>10000));3;-1)

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

В функции СОРТ, в предпоследнем аргументе мы использовали 3, это значит что сортировка будет по 3 столбику.

Вот и все! Мы рассмотрели как фильтровать данные с помощью функции ФИЛЬТР в Microsoft Office 365.

Надеюсь, эта статья оказалась полезна для вас!