Formuła ogólna
=FILTER(data,(header="a")+(header="b"))
Podsumowanie
Aby filtrować kolumny, podaj poziomą tablicę dla argumentu dołączania. W pokazanym przykładzie formuła w I5 to:
=FILTER(B5:G12,(B4:G4="a")+(B4:G4="c")+(B4:G4="e"))
Wynikiem jest przefiltrowany zestaw danych, który zawiera tylko kolumny A, C i E z danych źródłowych.
Wyjaśnienie
Chociaż FILTER jest częściej używany do filtrowania wierszy, można również filtrować kolumny, sztuczka polega na dostarczeniu tablicy z taką samą liczbą kolumn jak dane źródłowe. W tym przykładzie tworzymy potrzebną tablicę z logiką boolowską, zwaną również algebrą Boole'a.
W algebrze Boole'a mnożenie odpowiada logice AND, a dodawanie odpowiada logice OR. W przedstawionym przykładzie używamy algebry Boole'a z logiką OR (dodawanie), aby kierować tylko kolumny A, C i E w następujący sposób:
(B4:G4="a")+(B4:G4="c")+(B4:G4="e")
Po obliczeniu każdego wyrażenia mamy trzy tablice wartości PRAWDA / FAŁSZ:
(TRUE,FALSE,FALSE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,TRUE,FALSE,FALSE,FALSE)+ (FALSE,FALSE,FALSE,FALSE,TRUE,FALSE)
Operacja matematyczna (dodawanie) konwertuje wartości PRAWDA i FAŁSZ na 1 i 0, więc możesz myśleć o operacji w ten sposób:
(1,0,0,0,0,0)+ (0,0,1,0,0,0)+ (0,0,0,0,1,0)
Na koniec mamy pojedynczą poziomą tablicę jedynek i zer:
(1,0,1,0,1,0)
który jest dostarczany bezpośrednio do funkcji FILTER jako argument include:
=FILTER(B5:G12,(1,0,1,0,1,0))
Zauważ, że istnieje 6 kolumn w danych źródłowych i 6 wartości w tablicy, wszystkie 1 lub 0. FILTR używa tej tablicy jako filtru, aby uwzględnić tylko kolumny 1, 3 i 5 z danych źródłowych. Kolumny 2, 4 i 6 zostały usunięte. Innymi słowy, jedyne kolumny, które przetrwały, są powiązane z 1.
Z funkcją MATCH
Zastosowanie logiki OR z dodawaniem, jak pokazano powyżej, działa dobrze, ale nie jest dobrze skalowane i uniemożliwia użycie zakresu wartości z arkusza jako kryteriów. Alternatywnie możesz użyć funkcji MATCH razem z funkcją ISNUMBER w ten sposób, aby wydajniej skonstruować argument include:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,("a","c","e"),0)))
Funkcja MATCH jest skonfigurowana do wyszukiwania wszystkich nagłówków kolumn w stałej tablicy („a”, „c”, „e”), jak pokazano. Robimy to w ten sposób, aby wynik z PODAJ.POZYCJI miał wymiary zgodne z danymi źródłowymi, które zawierają 6 kolumn. Zauważ również, że trzeci argument funkcji PODAJ.POZYCJĘ jest ustawiony na zero, aby wymusić dokładne dopasowanie.
Po uruchomieniu MATCH zwraca tablicę taką:
(1,#N/A,2,#N/A,3,#N/A)
Ta tablica trafia bezpośrednio do ISNUMBER, który zwraca inną tablicę:
(TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
Jak wyżej, ta tablica jest pozioma i zawiera 6 wartości oddzielonych przecinkami. FILTER używa tablicy do usuwania kolumn 2, 4 i 6.
Z zakresem
Ponieważ nagłówki kolumn znajdują się już w arkuszu w zakresie I4: K4, powyższy wzór można łatwo dostosować do bezpośredniego użycia zakresu w następujący sposób:
=FILTER(B5:G12,ISNUMBER(MATCH(B4:G4,I4:K4,0)))
Zakres I4: K4 jest oceniany jako („a”, „c”, „e”) i zachowuje się tak samo jak stała tablicowa w powyższym wzorze.
