Як фільтрувати дані в Excel

У 365 версії програми, для фільтрації даних, ми можемо використовувати ПРОГЛЯД, СОРТ І ФІЛЬТР.

У звичайних версіях, щоб фільтрувати дані ми використовували комбінації різних функцій. Найчастіше вони були дуже великими та незручними. Цю проблему вирішили у Office 365.

Завдяки функції ФІЛЬТР ми можемо фільтрувати будь-які дані дуже швидко, а найголовніше, без громіздких конструкцій.

У цій статті ми розглянемо, як використовувати цю функцію в різних ситуаціях.

Функція ФІЛЬТР

Викликається вона так:

=ФІЛЬТР(діапазон_даних,критерії,[якщо_збігів_не_знайдено])

  • діапазон даних – тут все зрозуміло, це той діапазон, який ми фільтруватимемо;
  • критерії – критерії, за якими ми фільтруватимемо;
  • якщо_збігу_не_знайдено — не обов'язково вказувати цей параметр, він говорить функції, що нам потрібно повернути якщо вона не знайде будь-яких збігів.

Отже, перейдемо до прикладів.

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

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

Як фільтрувати дані в Excel

Наше завдання – відфільтрувати дані таким чином, щоб залишилися лише люди з Америки.

Для цього формула набуває такого вигляду:

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

Як фільтрувати дані в Excel

От і все!

Оскільки ми вказали критерій «US», функція вивела нам лише рядки, що збігаються.

Функцію можна викликати не тільки в тому аркуші, де знаходиться діапазон, що фільтрується, але і в інших.

Результат буде динамічним, тобто якщо щось зміниться в початкових даних, це зміниться і в кінцевих даних.

Оскільки результат виконання функції є масив даних, редагувати окремі рядки, цього результату, у нас не вийде.

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

Ось приклад:

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

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

Це, власне кажучи, порівняння чисел. Якщо більше або менше те.

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

Як фільтрувати дані в Excel

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

Для цього формула набуває такого вигляду:

=ФІЛЬТР($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.

Грубо кажучи, тепер нам потрібно використовувати два критерії, а не один.

Для цього формула набуває такого вигляду:

=ФІЛЬТР($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

Тут ми використовували «+», тому що нас влаштує виконання будь-якої з вимог.

Так само, ми можемо використовувати функцію ФІЛЬТР для фільтрації даних по країні або продажу.

Ось приклад:

=ФІЛЬТР($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

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

Для цього формула набуває такого вигляду:

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

Як фільтрувати дані в Excel

Спочатку функція ФІЛЬТР обробляє дані за критеріями, повертає масив, а після цього, функція СОРТ сортує отриманий масив даних за спаданням.

У функції СОРТ, в передостанньому аргументі ми використали 3, це означає, що сортування буде по 3 стовпчику.

От і все! Ми розглянули, як фільтрувати дані за допомогою функції ФІЛЬТР у Microsoft Office 365.

Сподіваюся, ця стаття виявилася корисною для вас!

nBook
Додати коментар