Резюме
Для фільтрування та вилучення даних на основі декількох складних критеріїв можна використовувати функцію ФІЛЬТР із ланцюжком виразів, що використовують логічну логіку. У наведеному прикладі формула в G5 має вигляд:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4))
Ця формула повертає дані, де:
рахунок починається з "х" І регіон "схід", а місяць НЕ Квітень.
Пояснення
У цьому прикладі нам потрібно побудувати логіку, яка фільтрує дані, щоб включати:
рахунок починається з "х" І регіон "схід", а місяць НЕ Квітень.
Логіка фільтрації цієї формули (аргумент "включення") створюється шляхом об'єднання трьох виразів, які використовують логічну логіку для масивів у даних. Перший вираз використовує функцію LEFT, щоб перевірити, чи починається Рахунок з "x":
LEFT(B5:B16)="x" // account begins with "x"
Результатом є масив TRUE FALSE значень, як це:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Другий вираз перевіряє, чи є регіон "східним" з оператором (=):
C5:C16="east" // region is east
Результат - інший масив:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Третій вираз використовує функцію МІСЯЦЬ із функцією NOT, щоб перевірити, чи не місяць є квітнем:
NOT(MONTH(D5:D16)=4) // month is not april
що дає:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Зверніть увагу, що функція NOT повертає результат з виразу MONTH.
Всі три масиви множаться разом. Операція математики примушує значення TRUE і FALSE до 1s і 0s, тому на цьому етапі ми можемо візуалізувати аргумент включення таким чином:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
Булеве множення відповідає логічній функції І, тому кінцевим результатом є одиничний масив, такий:
(0;0;0;1;1;0;0;0;0;1;0;1)
Функція FILTER використовує цей масив для фільтрації даних і повертає чотири рядки, що відповідають одиницям масиву.
Критерії розширення
Вирази, що використовуються для створення аргументу включення у фільтр, можуть бути розширені за необхідності для обробки ще більш складних фільтрів. Наприклад, щоб додатково фільтрувати дані, щоб включати лише рядки, де сума> 10000, ви можете використовувати формулу, подібну до цієї:
=FILTER(B5:E16,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4)*(E5:E16>10000))
