Všeobecný vzorec
=FILTER(data,(header="a")+(header="b"))
Zhrnutie
Ak chcete filtrovať stĺpce, zadajte horizontálne pole pre argument zahrnutia. V zobrazenom príklade je vzorec v I5:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Výsledkom je filtrovaná množina údajov, ktorá obsahuje iba stĺpce A, C a E zo zdrojových údajov.
Vysvetlenie
Aj keď sa FILTER na filtrovanie riadkov používa bežnejšie, môžete filtrovať aj stĺpce, ale trikom je dodať poli rovnaký počet stĺpcov ako zdrojové údaje. V tomto príklade skonštruujeme pole, ktoré potrebujeme, s logickou logikou, ktorá sa tiež nazýva logická algebra.
V boolovskej algebre násobenie zodpovedá logike AND a pridanie logike OR. V zobrazenom príklade používame logickú algebru s logikou OR (dodatok) na zacielenie iba na stĺpce A, C a E takto:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Po vyhodnotení každého výrazu máme tri polia hodnôt TRUE / FALSE:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Matematická operácia (sčítanie) prevádza hodnoty TRUE a FALSE na 1 s a 0 s, takže na túto operáciu môžete myslieť takto:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Nakoniec máme jediné horizontálne pole 1 s a 0 s:
(1,0,1,0,1,0)
ktorý sa dodáva priamo do funkcie FILTER ako argument include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Všimnite si, že v zdrojových dátach je 6 stĺpcov a v poli 6 hodnôt, všetky sú buď 1 alebo 0. FILTER používa toto pole ako filter na zahrnutie iba stĺpcov 1, 3 a 5 zo zdrojových údajov. Stĺpce 2, 4 a 6 sú odstránené. Inými slovami, jediné stĺpce, ktoré prežijú, sú spojené s 1 s.
S funkciou MATCH
Aplikácia logiky ALEBO s prídavkom, ako je uvedené vyššie, funguje dobre, ale nemá dostatočné mierky a znemožňuje použitie rozsahu hodnôt z hárka ako kritérií. Ako alternatívu môžete použiť funkciu MATCH spolu s funkciou ISNUMBER na efektívnejšiu konštrukciu argumentu include:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Funkcia MATCH je nakonfigurovaná tak, aby hľadala všetky hlavičky stĺpcov v konštante poľa („a“, „c“, „e“), ako je to znázornené. Robíme to tak, aby výsledok z MATCH mal dimenzie kompatibilné so zdrojovými údajmi, ktoré obsahujú 6 stĺpcov. Všimnite si tiež, že tretí argument v MATCH je nastavený na nulu, aby vynútil presnú zhodu.
Po spustení MATCH vráti pole takto:
(1,#N/A,2,#N/A,3,#N/A)
Toto pole prechádza priamo na ISNUMBER, ktoré vracia ďalšie pole:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Ako je uvedené vyššie, toto pole je horizontálne a obsahuje 6 hodnôt oddelených čiarkami. FILTER používa pole na odstránenie stĺpcov 2, 4 a 6.
S rozsahom
Pretože hlavičky stĺpcov sú už na pracovnom hárku v rozsahu I4: K4, je možné vyššie uvedený vzorec ľahko prispôsobiť tak, aby sa rozsah používal priamo takto:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Rozsah I4: K4 je vyhodnotený ako („a“, „c“, „e“) a správa sa rovnako ako konštanta poľa vo vyššie uvedenom vzorci.
