Функція FILTER дає змогу відфільтрувати діапазон даних на основі визначених умов.
У наведеному нижче прикладі ми використали формулу =FILTER(A5:D20;C5:C20=H2;""), щоб повернути всі записи для Apple, як вибрано у клітинці H2, а якщо яблук немає, поверніть пустий рядок ("").
Синтаксис
Функція FILTER фільтрує значення масиву на основі масиву логічних (істина або хибність) значень.
=FILTER(масив;включити;[якщо_пустий])
| Аргумент | Опис |
|---|---|
|
масив Обов’язковий |
Масив або діапазон, значення якого потрібно відфільтрувати |
|
включити Обов’язковий |
Масив логічних значень, висота або ширина якого відповідає масиву, який потрібно відфільтрувати |
|
[якщо_пустий] Необов’язковий |
Значення, що повертається, якщо всі значення включеного масиву пусті (фільтр нічого не повертає) |
Примітка.
- Масив можна розглядати як рядок або стовпець значень чи поєднання рядків і стовпців значень. У наведеному вище прикладі діапазон A5:D20 – це вихідний масив формули FILTER.
- Функція FILTER повертає масив, який розгорнеться, якщо це остаточний результат формули. Це означає, що якщо натиснути клавішу ENTER, Excel динамічно створить діапазон масиву відповідних розмірів. Якщо допоміжні дані розташовано в таблиці Excel і ви використовуєте структуровані посилання, розмір масиву автоматично змінюватиметься після додавання даних до його діапазону або їх вилучення. Докладні відомості див. у цій статті про поведінку розгорнутого масиву.
- Якщо існує ймовірність того, що за вашим набором даних функція поверне пусте значення, додайте третій аргумент ([якщо_пустий]). В іншому разі станеться помилка #CALC! , оскільки excel наразі не підтримує пусті масиви.
- Якщо будь-яке значення аргументу include є помилкою (#N/A, #VALUE тощо) або не може бути перетворене на логічне значення, функція FILTER поверне помилку.
- Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, усі зв'язані формули динамічного масиву повертатимуть помилку #REF! , коли їх буде оновлено.
Приклади
Використання функції FILTER для повернення множинних умов
У цьому випадку ми використовуємо оператор множення (*), щоб повернути всі значення в діапазоні масиву (A5:D20), які мають apples AND , розташовані в східному регіоні: =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);"").
Використання функції FILTER для повернення множинних умов сортування
У цьому випадку ми використовуємо попередню функцію FILTER із функцією SORT, щоб повернути всі значення в діапазоні масиву (A5:D20), які мають значення Apples AND , розташовані в східній області, а потім відсортуйте значення "Одиниці" за спаданням: =SORT(FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);""),4;-1)
У цьому випадку ми використовуємо функцію FILTER із оператором додавання (+), щоб повернути всі значення в діапазоні масиву (A5:D20), які мають apples OR , розташовані в східній області, а потім відсортуйте одиниці за спаданням: =SORT(FILTER(A5:D20;(C5:C20=H1)+(A5:A20=H2);""),4;-1).
Зверніть увагу: усі функції можна використовувати без абсолютних посилань, оскільки вони зберігаються лише в одній клітинці та розгортають свої результати в суміжних клітинках.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті tech Excel або отримати підтримку в спільнотах.