Функція FILTER

Функція FILTER дає змогу відфільтрувати діапазон даних на основі визначених умов.

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

У наведеному нижче прикладі ми використали формулу =FILTER(A5:D20;C5:C20=H2;""), щоб повернути всі записи для Apple, як вибрано у клітинці H2, а якщо яблук немає, поверніть пустий рядок ("").

Фільтрування регіону за товаром ("Яблука") за допомогою функції FILTER

Функція FILTER фільтрує значення масиву на основі масиву логічних (істина або хибність) значень.

=FILTER(масив;включити;[якщо_пустий])

Аргумент	Опис
масив Обов’язковий	Масив або діапазон, значення якого потрібно відфільтрувати
включити Обов’язковий	Масив логічних значень, висота або ширина якого відповідає масиву, який потрібно відфільтрувати
[якщо_пустий] Необов’язковий	Значення, що повертається, якщо всі значення включеного масиву пусті (фільтр нічого не повертає)

Масив можна розглядати як рядок або стовпець значень чи поєднання рядків і стовпців значень. У наведеному вище прикладі діапазон A5:D20 – це вихідний масив формули FILTER.
Функція FILTER повертає масив, який розгорнеться, якщо це остаточний результат формули. Це означає, що якщо натиснути клавішу ENTER, Excel динамічно створить діапазон масиву відповідних розмірів. Якщо допоміжні дані розташовано в таблиці Excel і ви використовуєте структуровані посилання, розмір масиву автоматично змінюватиметься після додавання даних до його діапазону або їх вилучення. Докладні відомості див. у цій статті про поведінку розгорнутого масиву.
Якщо існує ймовірність того, що за вашим набором даних функція поверне пусте значення, додайте третій аргумент ([якщо_пустий]). Інакше ви отримаєте помилку #CALC!, оскільки Excel поки не підтримує пусті масиви.
Якщо будь-яке значення аргументу include є помилкою (#N/A, #VALUE тощо) або не може бути перетворено на логічне значення, функція FILTER поверне помилку.
Excel має обмежену підтримку перенесення динамічних масивів між книгами, і цей сценарій підтримується, лише якщо відкрито обидві книги. Якщо закрити вихідну книгу, формули пов’язаного динамічного масиву повертатимуть помилку #REF! під час оновлення.

Приклади

Використання функції FILTER для повернення множинних умов

У цьому прикладі ми скористаємось оператором множення (*), щоб повернути всі значення в діапазоні масиву (A5:D20) для товару "Яблука" Й регіону "Східний": =FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);"").

Повернення всіх значень у діапазоні масиву (A5:D20) для товару "Яблука" І регіону "Східний" за допомогою функції FILTER з оператором множення (*)

Використання функції FILTER для повернення множинних умов сортування

У цьому прикладі ми скористаємось попередньою функцією FILTER з функцією SORT, щоб повернути всі значення в діапазоні масиву (A5:D20) для товару "Яблука" Й регіону "Східний", а потім відсортувати значення стовпця "Кількість" за спаданням: =SORT(FILTER(A5:D20;(C5:C20=H1)*(A5:A20=H2);"");4;-1)

Повернення всіх значень у діапазоні масиву (A5:D20) для товару "Яблука" І регіону "Східний" та їх сортування за спаданням значень стовпця "Кількість" за допомогою функцій FILTER і SORT

У цьому прикладі ми скористаємось функцією FILTER з додатковим оператором (+), щоб повернути всі значення в діапазоні масиву (A5:D20) для товару "Яблука" АБО регіону "Східний", а потім відсортувати значення стовпця "Кількість" за спаданням: =SORT(FILTER(A5:D20;(C5:C20=H1)+(A5:A20=H2);"");4;-1).

Фільтрування за товаром ("Яблука") АБО за регіоном ("Східний") за допомогою функцій FILTER і SORT

Зверніть увагу: усі функції можна використовувати без абсолютних посилань, оскільки вони зберігаються лише в одній клітинці та розгортають свої результати в суміжних клітинках.