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

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

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

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

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

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

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

=ФИЛЬТР(диапазон_данных,критерии,[если_совпадений_не_найдено])
  • диапазон данных — тут все понятно, это тот диапазон, который мы будем фильтровать;
  • критерии —  критерии, по которым мы будем фильтровать;
  • если_совпадений_не_найдено —  не обязательно указывать этот параметр, он говорит функции, что нам нужно вернуть если она не найдет каких-либо совпадений.

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;$B$2:$B$11="US")
Как фильтровать данные в Excel

Вот и все!

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

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

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

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

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

Вот пример:

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

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

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

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

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

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

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

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

=ФИЛЬТР($A$2:$C$11;($C$2:$C$11>10000))
Как фильтровать данные в Excel

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;($C$2:$C$11>=НАИБОЛЬШИЙ(C2:C11;3)))
Как фильтровать данные в Excel

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

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="US")*($C$2:$C$11>10000))
Как фильтровать данные в Excel

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

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

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

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

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

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;($B$2:$B$11="US")+($B$2:$B$11="Canada"))
Как фильтровать данные в Excel

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

Вот пример:

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

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

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;C2:C11>СРЗНАЧ(C2:C11))
Как фильтровать данные в Excel

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

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

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

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

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

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

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

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

=ФИЛЬТР($A$2:$C$11;ОСТАТ(СТРОКА(A2:A11)-1;2)=0)
Как фильтровать данные в Excel

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

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

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

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

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

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

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

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

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

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

=СОРТ(ФИЛЬТР($A$2:$C$11;($C$2:$C$11>10000));3;-1)
Как фильтровать данные в Excel

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

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

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

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Оцените статью, пожалуйста
Добавить комментарий