Funkcja filtrowania Excel - wyjaśniona z przykładami + wideo

Obejrzyj wideo - Excel FILTER Przykłady funkcji

Office 365 oferuje kilka niesamowitych funkcji, takich jak XLOOKUP, SORT i FILTER.

Jeśli chodzi o filtrowanie danych w Excelu, w świecie sprzed Office 365 byliśmy głównie zależni od wbudowanego filtra Excela lub maksymalnie filtra Zaawansowanego lub złożonych formuł SUMPRODUCT. W przypadku, gdy trzeba było filtrować część zbioru danych, było to zwykle złożone obejście (coś, co omówiłem tutaj).

Ale dzięki nowej funkcji FILTER teraz naprawdę łatwo jest szybko filtrować część zestawu danych na podstawie warunku.

W tym samouczku pokażę Ci, jak niesamowita jest nowa funkcja FILTR i kilka przydatnych rzeczy, które możesz z nią zrobić.

Ale zanim przejdę do przykładów, szybko poznajmy składnię funkcji FILTER.

Jeśli chcesz uzyskać te nowe funkcje w programie Excel, możesz uaktualnij do Office 365 (dołącz do programu Insider, aby uzyskać dostęp do wszystkich funkcji/formuł)

Funkcja filtrowania Excela - składnia

Poniżej znajduje się składnia funkcji FILTER:

=FILTR(tablica;uwzględnij;[jeśli_pusty])

• szyk - jest to zakres komórek, w których masz dane i chcesz z nich odfiltrować niektóre dane
• zawierać - jest to warunek, który mówi funkcji, które rekordy filtrować
• [jeśli_pusty] - jest to opcjonalny argument, w którym można określić, co ma zostać zwrócone w przypadku braku wyników funkcji FILTER. Domyślnie (jeśli nie określono), zwraca #CALC! błąd

Teraz spójrzmy na kilka niesamowitych przykładów funkcji filtrowania i rzeczy, które może zrobić, a które były dość skomplikowane pod jego nieobecność.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 1: Filtrowanie danych na podstawie jednego kryterium (region)

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz filtrować wszystkie rekordy tylko dla Stanów Zjednoczonych.

Poniżej znajduje się formuła FILTER, która to zrobi:

=FILTR($A$2:$C$11;$B$2:$B$11=US")

Powyższa formuła używa zestawu danych jako tablicy, a warunek to $B$2:$B$11=”US”

Ten warunek spowodowałby, że funkcja FILTER sprawdzi każdą komórkę w kolumnie B (taką, która zawiera region) i tylko te rekordy, które spełniają to kryterium, zostaną przefiltrowane.

Ponadto w tym przykładzie oryginalne dane i przefiltrowane dane znajdują się w tym samym arkuszu, ale można je również umieścić w osobnych arkuszach, a nawet skoroszytach.

Funkcja Filter zwraca wynik będący tablicą dynamiczną (co oznacza, że ​​zamiast zwracać jedną wartość, zwraca tablicę, która rozlewa się do innych komórek).

Aby to zadziałało, musisz mieć obszar, w którym wynik byłby pusty. W dowolnej komórce w tym obszarze (w tym przykładzie E2:G5) jest już coś w sobie, funkcja zwróci błąd #SPILL.

Ponadto, ponieważ jest to tablica dynamiczna, nie można zmienić części wyniku. Możesz usunąć cały zakres zawierający wynik lub komórkę E2 (w której wprowadzono formułę). Obie te czynności usunęłyby całą wynikową tablicę. Ale nie możesz zmienić żadnej pojedynczej komórki (lub jej usunąć).

W powyższej formule na stałe zakodowałem wartość regionu, ale możesz ją również umieścić w komórce, a następnie odwołać się do tej komórki, która ma wartość regionu.

Na przykład w poniższym przykładzie mam wartość regionu w komórce I2, a następnie jest do niej przywołana formuła:

=FILTR($A$2:$C$11,$B$2:$B$11=I1)

Dzięki temu formuła jest jeszcze bardziej użyteczna i teraz możesz po prostu zmienić wartość regionu w komórce I2, a filtr zmieni się automatycznie.

Możesz także mieć listę rozwijaną w komórce I2, gdzie możesz po prostu dokonać wyboru i natychmiast zaktualizuje przefiltrowane dane.

Przykład 2: Filtrowanie danych na podstawie jednego kryterium (więcej niż lub mniej niż)

Możesz również użyć operatorów porównawczych w funkcji filtru i wyodrębnić wszystkie rekordy, które są mniej więcej niż określona wartość.

Załóżmy na przykład, że masz zestaw danych pokazany poniżej i chcesz filtrować wszystkie rekordy, w których wartość sprzedaży przekracza 10000.

Poniższa formuła może to zrobić:

=FILTR($A$2:$C$11,($C$2:$C$11>10000))

Argument tablicowy odnosi się do całego zestawu danych, a warunkiem w tym przypadku jest ($C$2:$C$11>10000).

Formuła sprawdza każdy rekord pod kątem wartości w kolumnie C. Jeśli wartość przekracza 10000, jest filtrowana, w przeciwnym razie jest ignorowana.

Jeśli chcesz uzyskać wszystkie rekordy mniejsze niż 10000, możesz użyć poniższej formuły:

=FILTR($A$2:$C$11;($C$2:$C$11<10000))

Możesz również zwiększyć swoją kreatywność dzięki formule FILTER. Na przykład, jeśli chcesz przefiltrować trzy pierwsze rekordy na podstawie wartości sprzedaży, możesz użyć poniższej formuły:

=FILTR($A$2:$C$11;($C$2:$C$11>=DUŻY(C2:C11,3)))

Powyższa formuła używa funkcji LARGE, aby uzyskać trzecią co do wielkości wartość w zestawie danych. Ta wartość jest następnie używana w kryteriach funkcji FILTER, aby uzyskać wszystkie rekordy, w których wartość sprzedaży jest większa lub równa trzeciej największej wartości.

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 3: Filtrowanie danych za pomocą wielu kryteriów (ORAZ)

Załóżmy, że masz poniższy zestaw danych i chcesz przefiltrować wszystkie rekordy dla Stanów Zjednoczonych, w których wartość sprzedaży przekracza 10000.

Jest to warunek AND, w którym należy sprawdzić dwie rzeczy - region potrzebuje USA i sprzedaż musi być większa niż 10000. Jeśli spełniony jest tylko jeden warunek, wyniki nie powinny być filtrowane.

Poniżej znajduje się formuła FILTER, która filtruje rekordy z USA jako regionem i sprzedażą powyżej 10000:

=FILTR($A$2:$C$11;($B$2:$B$11="US")*($C$2:$C$11>10000))

Zauważ, że kryterium (nazywane argumentem dołączania) to ($B$2:$B$11=”US”)*($C$2:$C$11>10000)

Ponieważ używam dwóch warunków i oba muszą być prawdziwe, użyłem operatora mnożenia, aby połączyć te dwa kryteria. Zwraca tablicę zer i jedynek, gdzie 1 jest zwracane tylko wtedy, gdy oba warunki są spełnione.

W przypadku braku rekordów spełniających kryteria funkcja zwróci #CALC! błąd.

A jeśli chcesz zwrócić coś znaczącego (zamiast błędu), możesz użyć wzoru, jak pokazano poniżej:

=FILTR($A$2:$C$11;($B$2:$B$11="USA")*($C$2:$C$11>10000);"Nic nie znaleziono")

Tutaj użyłem „Nie znaleziono” jako trzeciego argumentu, który jest używany, gdy nie znaleziono rekordów spełniających kryteria.

Przykład 4: Filtrowanie danych za pomocą wielu kryteriów (LUB)

Możesz także zmodyfikować argument „include” w funkcji FILTER, aby sprawdzić kryterium LUB (gdzie dowolny z podanych warunków może być spełniony).

Załóżmy na przykład, że masz zestaw danych, jak pokazano poniżej, i chcesz filtrować rekordy, w których krajem są Stany Zjednoczone lub Kanada.

Poniżej znajduje się formuła, która to zrobi:

=FILTR($A$2:$C$11;($B$2:$B$11=USA")+($B$2:$B$11=Kanada"))

Zauważ, że w powyższym wzorze po prostu dodałem dwa warunki za pomocą operatora dodawania. Ponieważ każdy z tych warunków zwraca tablicę TRUE i FALSE, mogę dodać, aby uzyskać połączoną tablicę, w której jest TRUE, jeśli którykolwiek z warunków jest spełniony.

Innym przykładem może być filtrowanie wszystkich rekordów, w których albo krajem są Stany Zjednoczone, albo wartość sprzedaży przekracza 10000.

Zrobi to poniższa formuła:

=FILTR($A$2:$C$11;($B$2:$B$11=US")+(C2:C11>10000))

Uwaga: W przypadku używania kryteriów AND w funkcji FILTER należy użyć operatora mnożenia (*), a w przypadku kryteriów OR - operatora dodawania (+).

Przykład 5: Filtrowanie danych w celu uzyskania rekordów powyżej/poniżej średniej

Możesz użyć formuł w funkcji FILTER, aby filtrować i wyodrębniać rekordy, w których wartość jest powyżej lub poniżej średniej.

Załóżmy na przykład, że masz zestaw danych pokazany poniżej i chcesz filtrować wszystkie rekordy, w których wartość sprzedaży jest powyżej średniej.

Możesz to zrobić za pomocą następującego wzoru:

=FILTR($A$2:$C$11;C2:C11>ŚREDNIA(C2:C11))

Podobnie dla wartości poniżej średniej możesz użyć poniższego wzoru:

=FILTR($A$2:$C$11;C2:C11<>

Kliknij tutaj, aby pobrać przykładowy plik i podążaj dalej

Przykład 6: Filtrowanie tylko rekordów o numerach parzystych (lub rekordach o numerach nieparzystych)

Jeśli potrzebujesz szybko przefiltrować i wyodrębnić wszystkie rekordy z parzystych lub nieparzystych wierszy, możesz to zrobić za pomocą funkcji FILTER.

Aby to zrobić, musisz sprawdzić numer wiersza w funkcji FILTER i filtrować tylko numery wierszy, które spełniają kryteria numeru wiersza.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcę tylko wyodrębnić rekordy o numerach parzystych z tego zestawu danych.

Poniżej znajduje się formuła, która to zrobi:

=FILTR($A$2:$C$11;MOD(WIERSZ(A2:A11)-1,2)=0)

Powyższa formuła wykorzystuje funkcję MOD do sprawdzenia numeru wiersza każdego rekordu (który jest podawany przez funkcję WIERSZ).

Formuła MOD(WIERSZ(A2:A11)-1,2)=0 zwraca PRAWDA, gdy numer wiersza jest parzysty, a FAŁSZ, gdy jest nieparzysty. Zauważ, że odjąłem 1 od części WIERSZ(A2:A11), ponieważ pierwszy rekord znajduje się w drugim wierszu, a to dostosowuje numer wiersza tak, aby drugi wiersz był traktowany jako pierwszy rekord.

Podobnie możesz filtrować wszystkie nieparzyste rekordy za pomocą poniższej formuły:

=FILTR($A$2:$C$11;MOD(WIERSZ(A2:A11)-1,2)=1)

Przykład 7: Sortuj przefiltrowane dane za pomocą formuły

Połączenie funkcji FILTER z innymi funkcjami pozwala nam zrobić dużo więcej.

Na przykład, jeśli filtrujesz zestaw danych za pomocą funkcji FILTER, możesz użyć funkcji SORTOWANIE, aby uzyskać wynik, który jest już posortowany.

Załóżmy, że masz zestaw danych, jak pokazano poniżej, i chcesz przefiltrować wszystkie rekordy, w których wartość sprzedaży przekracza 10000. Możesz użyć funkcji SORTOWANIE z funkcją, aby upewnić się, że dane wynikowe są sortowane na podstawie wartości sprzedaży.

Zrobi to poniższa formuła:

=SORTUJ(FILTR($A$2:$C$11;($C$2:$C$11>10000)),3;-1)

Powyższa funkcja używa funkcji FILTER w celu uzyskania danych, w których wartość sprzedaży w kolumnie C jest większa niż 10000. Ta tablica zwrócona przez funkcję FILTER jest następnie używana w funkcji SORTOWANIE do sortowania tych danych na podstawie wartości sprzedaży.

Drugim argumentem w funkcji SORTOWANIE jest 3, co oznacza sortowanie na podstawie trzeciej kolumny. A czwartym argumentem jest -1, który ma posortować te dane w kolejności malejącej.

Kliknij tutaj, aby pobrać przykładowy plik

Oto 7 przykładów użycia funkcji FILTER w programie Excel.

Mam nadzieję, że ten samouczek okazał się przydatny!

Możesz również polubić następujące samouczki programu Excel:

1. Jak filtrować komórki za pomocą pogrubionego formatowania czcionki w programie Excel
2. Dynamiczne pole wyszukiwania filtra Excel
3. Jak filtrować dane w tabeli przestawnej w programie Excel