В 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.
Надеюсь, эта статья оказалась полезна для вас!