- Функція фільтра Excel - Синтаксис
- Приклад 1: Фільтрування даних на основі одного критерію (регіон)
- Приклад 2: Фільтрація даних на основі одного критерію (більше або менше)
- Приклад 3: Фільтрування даних за кількома критеріями (І)
- Приклад 4: Фільтрування даних за кількома критеріями (АБО)
- Приклад 5: Фільтрування даних для досягнення записів вище/нижче середнього
- Приклад 6: Фільтрування ТІЛЬКИ записів ЧИТИХ ЗАПИСІВ (АБО ЗАПИСІВ ЧИСЛИХ ЧИСЛІВ)
- Приклад 7: Сортуйте відфільтровані дані за формулою
Перегляд відео - приклади функцій фільтра Excel
Office 365 пропонує деякі чудові функції, такі як XLOOKUP, SORT та FILTER.
Що стосується фільтрації даних у Excel, у світі до Office 365 ми в основному залежали від вбудованого фільтра Excel або від розширеного фільтра або складних формул SUMPRODUCT. Якщо вам потрібно було відфільтрувати частину набору даних, це зазвичай було складним обхідним шляхом (те, що я тут висвітлював).
Але за допомогою нової функції FILTER тепер дуже легко швидко відфільтрувати частину набору даних на основі умов.
І в цьому уроці я покажу вам, наскільки чудова нова функція ФІЛЬТР, і деякі корисні речі, які ви можете з цим зробити.
Але перш ніж перейти до прикладів, давайте швидко дізнаємося про синтаксис функції FILTER.
Якщо ви хочете отримати ці нові функції в Excel, ви можете оновлення до Office 365 (приєднуйтесь до інсайдерської програми, щоб отримати доступ до всіх функцій/формул)Функція фільтра Excel - Синтаксис
Нижче наведено синтаксис функції ФІЛЬТР:
= ФІЛЬТР (масив, включити, [якщо_порожній])
- масив - це діапазон клітинок, у яких у вас є дані, і ви хочете відфільтрувати деякі дані з них
- включати - це умова, яка вказує функції, які записи слід фільтрувати
- [якщо_порожній] - це необов’язковий аргумент, де ви можете вказати, що повернути, якщо функція FILTER не знайде результатів. За замовчуванням (якщо не вказано), він повертає #CALC! помилка
Тепер давайте подивимося на деякі дивовижні приклади функцій фільтра та речі, які вона може зробити, що раніше було досить складним за його відсутності.
Натисніть тут, щоб завантажити файл прикладу та продовжити
Приклад 1: Фільтрування даних на основі одного критерію (регіон)
Припустимо, у вас є набір даних, як показано нижче, і ви хочете відфільтрувати всі записи лише для США.
Нижче наведено формулу ФІЛЬТР, яка зробить це:
= ФІЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = "США")
У наведеній вище формулі використовується набір даних як масив, а умовою є $ B $ 2: $ B $ 11 = "США"
Ця умова змусить функцію ФІЛЬТР перевірити кожну клітинку у стовпці В (таку, що має область), і будуть відфільтровані лише ті записи, які відповідають цьому критерію.
Крім того, у цьому прикладі я маю вихідні дані та відфільтровані дані на одному аркуші, але ви також можете мати ці дані в окремих аркушах або навіть робочих книгах.
Функція фільтра повертає результат, який є динамічним масивом (це означає, що замість того, щоб повертати одне значення, він повертає масив, який розливається в інші комірки).
Щоб це працювало, вам потрібно мати область, де результат буде порожнім. У будь -якій клітинці цієї області (E2: G5 у цьому прикладі) щось у ній є, функція видасть вам помилку #SPILL.
Крім того, оскільки це динамічний масив, ви не можете змінити частину результату. Ви можете видалити весь діапазон, який містить результат, або клітинку E2 (де була введена формула). Обидва вони видалять весь отриманий масив. Але ви не можете змінити жодну окрему клітинку (або видалити її).
У наведеній вище формулі я жорстко закодував значення регіону, але ви також можете мати його у комірці, а потім посилатися на цю клітинку, яка має значення регіону.
Наприклад, у наведеному нижче прикладі я маю значення регіону у клітинці I2, і це посилання у формулі:
= ФІЛЬТР ($ A $ 2: $ C $ 11, $ B $ 2: $ B $ 11 = I1)
Це робить формулу ще більш корисною, і тепер ви можете просто змінити значення регіону в комірці I2, і фільтр автоматично зміниться.
Ви також можете мати спадне меню у комірці I2, де ви можете просто зробити вибір, і він миттєво оновить відфільтровані дані.
Приклад 2: Фільтрація даних на основі одного критерію (більше або менше)
Ви також можете використовувати порівняльні оператори в межах функції фільтра та витягувати всі записи, які більше або менше певного значення.
Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете відфільтрувати всі записи, де вартість продажу перевищує 10000.
Нижченаведена формула може зробити це:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000))
Аргумент масиву відноситься до всього набору даних, і умова в цьому випадку така ($ C $ 2: $ C $ 11> 10000).
Формула перевіряє кожен запис на значення у стовпці C. Якщо значення більше 10000, воно фільтрується, інакше воно ігнорується.
Якщо ви хочете отримати всі записи менше 10000, ви можете скористатися формулою нижче:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11 <10000))
Ви також можете стати більш креативними за допомогою формули ФІЛЬТР. Наприклад, якщо ви хочете відфільтрувати три найкращі записи на основі вартості продажів, ви можете скористатися формулою нижче:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> = ВЕЛИКИЙ (C2: C11,3)))
У наведеній вище формулі використовується функція LARGE для отримання третього за величиною значення в наборі даних. Потім це значення використовується в критеріях функції ФІЛЬТР для отримання всіх записів, де вартість продажу більше або дорівнює третьому за величиною значенню.
Натисніть тут, щоб завантажити файл прикладу та продовжити
Приклад 3: Фільтрування даних за кількома критеріями (І)
Припустимо, у вас є набір даних нижче, і ви хочете відфільтрувати всі записи для США, де вартість продажу перевищує 10000.
Це умова І, коли вам потрібно перевірити дві речі - регіон повинен бути в США, а обсяг продажів повинен бути більше 10000. Якщо виконується лише одна умова, результати не слід фільтрувати.
Нижче наведена формула ФІЛЬТР, яка фільтруватиме записи з США як регіону та продажів понад 10000:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000))
Зауважте, що критерій (який називається аргументом включення) є ($ B $ 2: $ B $ 11 = "US")*($ C $ 2: $ C $ 11> 10000)
Оскільки я використовую дві умови, і мені потрібно, щоб обидві були істинними, я використав оператор множення, щоб об’єднати ці два критерії. Це повертає масив 0 та 1, де 1 повертається лише тоді, коли виконуються обидві умови.
Якщо немає записів, що відповідають критеріям, функція повертає #CALC! помилка.
І якщо ви хочете повернути щось значення (замість помилки), ви можете використовувати формулу, як показано нижче:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "США")*($ C $ 2: $ C $ 11> 10000), "Нічого не знайдено")
Тут я використав “Не знайдено” як третій аргумент, який використовується, коли не знайдено записів, що відповідають критеріям.
Приклад 4: Фільтрування даних за кількома критеріями (АБО)
Ви також можете змінити аргумент "включити" у функції ФІЛЬТР, щоб перевірити наявність критеріїв АБО (де будь -яка з наведених умов може бути істинною).
Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете відфільтрувати записи, де країною є США або Канада.
Нижче наведена формула, яка дозволить це зробити:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "США")+($ B $ 2: $ B $ 11 = "Канада"))
Зауважте, що у наведеній вище формулі я просто додав дві умови за допомогою оператора додавання. Оскільки кожна з цих умов повертає масив ІСТИН і ЛОЖЬ, я можу додати, щоб отримати комбінований масив, де це ІСТИНА, якщо виконана одна з умов.
Іншим прикладом може бути, коли ви хочете відфільтрувати всі записи, де або країна США, або вартість продажу більше 10000.
Нижченаведена формула зробить це:
= ФІЛЬТР ($ A $ 2: $ C $ 11, ($ B $ 2: $ B $ 11 = "US")+(C2: C11> 10000))
Примітка: При використанні критеріїв AND у функції FILTER використовуйте оператор множення (*), а при використанні критеріїв OR - оператор додавання (+).
Приклад 5: Фільтрування даних для досягнення записів вище/нижче середнього
Ви можете використовувати формули у функції FILTER для фільтрації та вилучення записів, де значення вище або нижче середнього.
Наприклад, припустимо, що у вас є набір даних, як показано нижче, і ви хочете відфільтрувати всі записи, де вартість продажу вище середнього.
Зробити це можна за такою формулою:
= ФІЛЬТР ($ A $ 2: $ C $ 11, C2: C11> СЕРЕДНЯ (C2: C11))
Так само для нижче середнього можна скористатися формулою нижче:
= ФІЛЬТР ($ A $ 2: $ C $ 11, C2: C11<>
Натисніть тут, щоб завантажити файл прикладу та продовжити
Приклад 6: Фільтрування ТІЛЬКИ записів ЧИТИХ ЗАПИСІВ (АБО ЗАПИСІВ ЧИСЛИХ ЧИСЛІВ)
Якщо вам потрібно швидко відфільтрувати та витягти всі записи з рядків з парним чи непарним числом, це можна зробити за допомогою функції ФІЛЬТР.
Для цього вам потрібно перевірити номер рядка у функції ФІЛЬТР та лише фільтрувати номери рядків, які відповідають критеріям рядків.
Припустимо, у вас є набір даних, як показано нижче, і я хочу витягти з цього набору лише парні записи.
Нижче наведена формула, яка дозволить це зробити:
= ФІЛЬТР ($ A $ 2: $ C $ 11, MOD (РЯД (A2: A11) -1,2) = 0)
Наведена вище формула використовує функцію MOD для перевірки номера рядка кожного запису (який задається функцією ROW).
Формула MOD (ROW (A2: A11) -1,2) = 0 повертає TRUE, якщо номер рядка парний, і FALSE, коли він непарний. Зауважте, що я вилучив 1 з частини ROW (A2: A11), оскільки перший запис знаходиться у другому рядку, і це коригує номер рядка, щоб вважати другий рядок першим.
Так само ви можете відфільтрувати всі непарні записи, використовуючи формулу нижче:
= ФІЛЬТР ($ A $ 2: $ C $ 11, MOD (РЯД (A2: A11) -1,2) = 1)
Приклад 7: Сортуйте відфільтровані дані за формулою
Використання функції ФІЛЬТР з іншими функціями дозволяє нам зробити набагато більше.
Наприклад, якщо ви фільтруєте набір даних за допомогою функції ФІЛЬТР, ви можете використовувати з нею функцію СОРТУВАННЯ, щоб отримати результат, який уже відсортований.
Припустимо, у вас є набір даних, як показано нижче, і ви хочете відфільтрувати всі записи, де вартість продажів перевищує 10000. Ви можете використовувати функцію СОРТУВАННЯ з функцією, щоб переконатися, що отримані дані сортуються на основі вартості продажів.
Нижченаведена формула зробить це:
= СОРТУВАТИ (ФІЛЬТР ($ A $ 2: $ C $ 11, ($ C $ 2: $ C $ 11> 10000)), 3, -1)
Вищевказана функція використовує функцію FILTER для отримання даних, де вартість продажу у стовпці C становить більше 10000. Цей масив, повернутий функцією FILTER, потім використовується у функції SORT для сортування цих даних на основі вартості продажів.
Другий аргумент у функції SORT - 3, який має сортуватися на основі третього стовпця. І четвертий аргумент -1 -це сортування цих даних у порядку спадання.
Натисніть тут, щоб завантажити файл прикладу
Отже, це 7 прикладів використання функції ФІЛЬТР в Excel.
Сподіваюся, вам цей підручник був корисним!
Вам також можуть сподобатися такі підручники Excel:
- Як фільтрувати клітинки за допомогою жирного форматування шрифту в Excel
- Вікно пошуку динамічного фільтра Excel
- Як фільтрувати дані у зведеній таблиці в Excel
