У 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.
Грубо кажучи, тепер нам потрібно використовувати два критерії, а не один.
Для цього формула набуває такого вигляду:
=ФІЛЬТР($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.
Сподіваюся, ця стаття виявилася корисною для вас!
