Обща формула
=FILTER(data,(header="a")+(header="b"))
Обобщение
За да филтрирате колони, предоставете хоризонтален масив за аргумента за включване. В показания пример формулата в I5 е:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Резултатът е филтриран набор от данни, който съдържа само колони A, C и E от изходните данни.
Обяснение
Въпреки че FILTER е по-често използван за филтриране на редове, можете също да филтрирате колони, трикът е да предоставите масив със същия брой колони като изходните данни. В този пример ние конструираме масива, от който се нуждаем, с булева логика, наричана още булева алгебра.
В булевата алгебра умножението съответства на логиката И, а добавянето съответства на логиката ИЛИ. В показания пример използваме булева алгебра с логика OR (добавяне), за да насочваме само колоните 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 като аргумент за включване:
=FILTER(B5:G12,(1,0,1,0,1,0))
Забележете, че в изходните данни има 6 колони и 6 стойности в масива, всички или 1 или 0. FILTER използва този масив като филтър, за да включва само колони 1, 3 и 5 от данните източник. Колони 2, 4 и 6 се премахват. С други думи, единствените оцелели колони са свързани с 1s.
С функцията MATCH
Прилагането на ИЛИ логика с добавяне, както е показано по-горе, работи добре, но не се мащабира добре и прави невъзможно използването на набор от стойности от работен лист като критерии. Като алтернатива можете да използвате функцията MATCH заедно с функцията ISNUMBER като тази, за да изградите по-ефективно аргумента за включване:
=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") и се държи точно като константата на масива във формулата по-горе.
