Formuła ogólna
=FILTER(data,logic)
Podsumowanie
Aby filtrować dane ułożone poziomo w kolumnach, możesz użyć funkcji FILTER. W pokazanym przykładzie formuła w C9 to:
=TRANSPOSE(FILTER(data,group="fox"))
gdzie dane (C4: L6) i grupa (C5: L5) to nazwane zakresy.
Wyjaśnienie
Uwaga: FILTER to nowa funkcja tablicy dynamicznej w programie Excel 365. W innych wersjach programu Excel istnieją alternatywy, ale są one bardziej złożone.
W zakresie C4: L6 znajduje się dziesięć kolumn danych. Celem jest odfiltrowanie tych danych poziomych i wyodrębnienie tylko kolumn (rekordów), w których grupa jest „lis”. Dla wygody i czytelności arkusz zawiera trzy nazwane zakresy: dane (C4: L6) i grupa (C5: L5) oraz wiek (C6: L6).
Funkcja FILTER może służyć do wyodrębniania danych ułożonych pionowo (w wierszach) lub poziomo (w kolumnach). FILTER zwróci pasujące dane w tej samej orientacji. Nie jest wymagana żadna specjalna konfiguracja. W pokazanym przykładzie formuła w C9 to:
=FILTER(data,group="fox")
Działając od wewnątrz, argument include dla FILTER jest wyrażeniem logicznym:
group="fox" // test for "fox"
Kiedy wyrażenie logiczne jest obliczane, zwraca tablicę 10 wartości TRUE i FALSE:
(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE)
Uwaga: przecinki (,) w tej tablicy oznaczają kolumny. Średniki (;) wskazywałyby wiersze.
Tablica zawiera jedną wartość na kolumnę danych, a każda wartość TRUE odpowiada kolumnie, w której grupa to „lis”. Ta tablica jest zwracana bezpośrednio do FILTER jako argument dołączania i wykonuje faktyczne filtrowanie:
FILTER(data,(TRUE,FALSE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE,TRUE,FALSE))
Tylko dane odpowiadające wartościom PRAWDA przechodzą przez filtr, więc FILTER zwraca 6 kolumn, w których grupa to „lis”. FILTER zwraca te dane w oryginalnej strukturze poziomej. Ponieważ FILTER jest dynamiczną funkcją tablicową, wyniki rozlewają się do zakresu C9: H11.
Jest to rozwiązanie dynamiczne - jeśli jakiekolwiek dane źródłowe w C4: L6 ulegną zmianie, wyniki z FILTRA są automatycznie aktualizowane.
Transponuj do formatu pionowego
Aby przetransponować wyniki z filtru do formatu pionowego (wierszy), możesz zawinąć funkcję TRANSPOSE wokół funkcji FILTER w następujący sposób:
=TRANSPOSE(FILTER(data,group="fox"))
Wynik wygląda następująco:
Ta formuła jest wyjaśniona bardziej szczegółowo tutaj.
Filtruj według wieku
Ta sama podstawowa formuła może służyć do filtrowania danych na różne sposoby. Na przykład, aby filtrować dane w celu wyświetlenia tylko kolumn, w których wiek jest mniejszy niż 22 lata, możesz użyć następującej formuły:
=FILTER(data,age<22)
FILTER zwraca cztery zgodne kolumny danych:
