Як відфільтрувати дані в 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)) Ось варіант переписаного тексту з збереженням значення:

Наприклад, ось як ви можете отримати спискок трьох найкращих продавців:

=ФІЛЬТР($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);”Нічого не знайдено”)

Виконуємо фільтрацію за різними параметрами (АБО)

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

Припустимо, у нас є ось така таблиця:

Ми прагнемо залучити продавців з Канади або США.

У цьому випадку формула виглядає наступним чином:

=ФІЛЬТР($A$2:$C$11;($B$2:$B$11=”США”)+($B$2:$B$11=”Канада”))

У цьому випадку ми застосували «+», оскільки нас задовольняє виконання будь-якої з умов.

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

Ось варіант:

=ФІЛЬТР($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, що свідчить про те, що сортування відбуватиметься за третім стовпцем.

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

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