Obecný vzorec
=FILTER(data,(header="a")+(header="b"))
souhrn
Chcete-li filtrovat sloupce, zadejte vodorovné pole pro argument zahrnutí. V zobrazeném příkladu je vzorec v I5:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Výsledkem je filtrovaná sada dat, která obsahuje pouze sloupce A, C a E ze zdrojových dat.
Vysvětlení
Ačkoli se FILTER běžněji používá k filtrování řádků, můžete filtrovat také sloupce, ale trikem je poskytnout pole se stejným počtem sloupců jako zdrojová data. V tomto příkladu konstruujeme pole, které potřebujeme, pomocí logické logiky, nazývané také booleovská algebra.
V booleovské algebře násobení odpovídá logice AND a přidání odpovídá logice OR. V ukázaném příkladu používáme booleovskou algebru s logikou OR (přidání) k cílení pouze na sloupce A, C a E takto:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Po vyhodnocení každého výrazu máme tři pole TRUE / FALSE hodnot:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Matematická operace (doplněk) převede hodnoty TRUE a FALSE na 1 s a 0 s, takže si můžete tuto operaci představit takto:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Nakonec máme jediné horizontální pole 1 s a 0 s:
(1,0,1,0,1,0)
který je dodáván přímo do funkce FILTER jako argument include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Všimněte si, že ve zdrojových datech je 6 sloupců a v poli 6 hodnot, všechny buď 1 nebo 0. FILTER používá toto pole jako filtr k zahrnutí pouze sloupců 1, 3 a 5 ze zdrojových dat. Sloupce 2, 4 a 6 jsou odstraněny. Jinými slovy, jediné sloupce, které přežijí, jsou spojeny s 1 s.
S funkcí MATCH
Použití logiky OR s přidáním, jak je uvedeno výše, funguje dobře, ale nemění se dobře a znemožňuje použít řadu hodnot z listu jako kritéria. Jako alternativu můžete použít funkci MATCH společně s funkcí ISNUMBER k efektivnější konstrukci argumentu include:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Funkce MATCH je nakonfigurována tak, aby hledala všechna záhlaví sloupců v konstantě pole ("a", "c", "e"), jak je znázorněno. Děláme to tak, aby výsledek z MATCH měl rozměry kompatibilní se zdrojovými daty, která obsahují 6 sloupců. Všimněte si také, že třetí argument v MATCH je nastaven jako nula k vynucení přesné shody.
Po spuštění MATCH vrátí pole takto:
(1,#N/A,2,#N/A,3,#N/A)
Toto pole jde přímo do ISNUMBER, které vrací další pole:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Jak je uvedeno výše, toto pole je vodorovné a obsahuje 6 hodnot oddělených čárkami. FILTER používá pole k odstranění sloupců 2, 4 a 6.
S řadou
Vzhledem k tomu, že záhlaví sloupců jsou již na listu v rozsahu I4: K4, lze výše uvedený vzorec snadno upravit tak, aby používal rozsah přímo takto:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Rozsah I4: K4 je vyhodnocen jako ("a", "c", "e") a chová se stejně jako konstanta pole ve výše uvedeném vzorci.
