Загальна формула
=FILTER(data,(header="a")+(header="b"))
Резюме
Щоб фільтрувати стовпці, введіть горизонтальний масив для аргументу включення. У наведеному прикладі формула в I5 має вигляд:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Результат - відфільтрований набір даних, який містить лише стовпці A, C та E з вихідних даних.
Пояснення
Незважаючи на те, що ФІЛЬТР частіше використовується для фільтрування рядків, Ви також можете фільтрувати стовпці, хитрість полягає у наданні масиву з такою ж кількістю стовпців, що і вихідні дані. У цьому прикладі ми будуємо необхідний нам масив за допомогою логічної логіки, яка також називається булевою алгеброю.
У булевій алгебрі множення відповідає логіці І, а додавання відповідає АБО логіці. У наведеному прикладі ми використовуємо булеву алгебру з логікою АБО (додавання) для орієнтування лише на стовпці A, C та E так:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Після обчислення кожного виразу ми маємо три масиви значень TRUE / FALSE:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Математична операція (додавання) перетворює значення TRUE і FALSE на 1s і 0s, тому ви можете думати про цю операцію так:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Врешті-решт, ми маємо єдиний горизонтальний масив 1s і 0s:
(1,0,1,0,1,0)
який доставляється безпосередньо до функції FILTER як аргумент include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Зверніть увагу, що у вихідних даних є 6 стовпців, а в масиві - 6 значень, або 1, або 0. FILTER використовує цей масив як фільтр, щоб включити лише стовпці 1, 3 та 5 з вихідних даних. Стовпці 2, 4 та 6 видаляються. Іншими словами, єдині збережені стовпці пов’язані з 1s.
З функцією MATCH
Застосування логіки АБО з додаванням, як показано вище, справно працює, але воно погано масштабується і унеможливлює використання діапазону значень з робочого аркуша як критерії. Як альтернативу, ви можете використовувати функцію MATCH разом із функцією ISNUMBER, як це, для більш ефективного побудови аргументу include:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Функція MATCH налаштована на пошук усіх заголовків стовпців у константі масиву ("a", "c", "e"), як показано. Ми робимо це таким чином, щоб результат із MATCH мав розміри, сумісні з вихідними даними, що містить 6 стовпців. Також зверніть увагу, що третій аргумент у MATCH встановлюється рівним нулю, щоб змусити точно збігатися.
Після запуску MATCH він повертає такий масив:
(1,#N/A,2,#N/A,3,#N/A)
Цей масив потрапляє безпосередньо в ISNUMBER, який повертає інший масив:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Як і вище, цей масив є горизонтальним і містить 6 значень, розділених комами. FILTER використовує масив для видалення стовпців 2, 4 та 6.
З асортиментом
Оскільки заголовки стовпців вже знаходяться на аркуші в діапазоні I4: K4, формулу вище можна легко адаптувати для використання діапазону безпосередньо так:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Діапазон I4: K4 оцінюється як ("a", "c", "e") і поводиться так само, як константа масиву у наведеній вище формулі.
