Arkusze Google / Excel
Jak działa funkcja FILTER?
29 stycznia, 2024
Zapewne korzystasz w swojej pracy na Arkuszach Google czy Excelu z opcji filtrowania. W łatwy sposób pozwala na prezentację danych w wybranym zakresie. Jednak istnieje lepszy sposób, aby pracować nad danymi. Umożliwia także zagnieżdżenie innych funkcji w tej samej formule.
Jest to Funkcja FILTER, dostępna zarówno w Arkuszach Google, jak i Excelu. Dzięki niej wygenerujesz nowy zestaw danych, zachowując oryginalne dane. FILTER pozwala zwrócić wiersze w zakresie, który spełnia założone przez Ciebie kryteria. Możesz również dodać wiele kryteriów w różnych kolumnach.
Może okazać się szczególnie przydatna kiedy dane wejściowe:
– są współdzielone i pracuje na nich więcej osób,
– są poddawane aktualizacjom,
– mają zablokowaną możliwość edycji.
Daje to możliwość wygodnego prowadzenia obliczeń lub wprowadzania dodatkowych modyfikacji w sposobie prezentowania wybranego zestawu danych.
Składnia formuły
Komórka, w której umieszczona zostanie formuła FILTER, będzie stanowiła lewy górny róg wygenerowanego przez funkcję zestawu danych.
Wiersz powyżej powinien być pusty, aby zostawić miejsce na nazwy kolumn wskazanych w funkcji.
=FILTER(zakres ; warunek1 ; [warunek2] ; ...)
zakres - zakres filtrowanej treści - jedna lub kilka kolumn
warunek 1 - pierwszy warunek, dla którego wartości z wynikiem PRAWDA zostaną odfiltrowane
warunek 2 - drugi warunek... (już opcjonalny) Do prezentacji możliwości funkcji FILTER stworzyłem prostą tabelę z 60 wierszami danych.
Tutaj możesz skopiować plik Google Sheets, na którym pracowałem: KLIK
Przypadek 1: Filtrowanie całej tabeli przez jeden warunek
Zależy mi na tym, aby w wygenerowanym przez FILTER zakresie danych znalazły się wiersze, w których w kolumnie „Zawód” występuje słowo „programista”.
Formuła, którą umieściłem w komórce A2 to:
=FILTER(Baza!A2:D60 ; Baza!C2:C60 = "programista") 
W powyższej funkcji warunkiem jest obecność słowa “programista” w zakresie Baza!C2:C60.
Pamiętaj że rozmiar zakresów musi być sobie równy – jeżeli pierwszy obejmuje wiersze od 2 do 60, to drugi również powinien mieć rozmiar od 2 do 60.
Funkcja automatycznie uzupełniana jest w dół kolumny A. Oznacza to, że jeżeli poniżej wyniku funkcji, napotka ona jakąś komórkę, która nie jest pusta, funkcja wyświetli błąd #ADR!.
Przypadek 2: Filtrowanie całej tabeli przez dwa warunki
Naszą funkcje możemy rozszerzyć o dodatkowe parametry.
Teraz chcę, aby funkcja wygenerowała dane osób, które w kolumnie „Zawód” oznaczone są jako “emeryt” i mają więcej niż 59 lat.
Stosuję zatem poniższą formułę:
=FILTER(Baza!A2:D60 ; Baza!C2:C60 = "emeryt" ; Baza!B2:B60 > 59) 
W tym przypadku, oprócz filtrowania bazy po słowie „emeryt”, po średniku dodałem kolejny warunek, który określa interesujący mnie wiek.
Średnik pozwala na oddzielenie warunków od siebie. Można zatem przetłumaczyć funkcję jako “filtruj zawód emeryt ORAZ wiek większy niż 59”.
Formatowanie kolumny „Wiek” ustawione jest jako liczba, w związku z tym zmienna w formule nie wymaga użycia cudzysłowu.
Znak większości “>” oznacza, że dane w podanym zakresie, muszą być większe niż 59.
Możemy tutaj użyć zarówno “>” jak i: “<”, “<=”, “>=” i “<>” (nie jest równe, np: <>3).
Przypadek 3: Filtrowanie po warunku1 LUB warunku2
W poprzednim przypadku przefiltrowałem dane, które muszą spełniać dwa warunki.
Teraz chce, aby funkcja wygenerowała zestaw danych osób, które są emerytami lub są starsze niż 59 lat.
Zmieniam zatem formułę poprzez wstawienie warunków w nawiasy, a znak który je oddziela zmieniam ze średnika (;) na plus (+).
=FILTER(Baza!A2:D60 ; (Baza!C2:C60 = "emeryt") + (Baza!B2:B60 > 59)) Chcę pokazać w moim zestawie danych również osoby z zawodem „student”, zatem mogę dodać kolejny warunek LUB w następujący sposób:
=FILTER(Baza!A2:D60 ; (Baza!C2:C60 = "emeryt") + (Baza!B2:B60 > 59) + (Baza!C2:C60 = "student")) 
W tym przypadku nasz zestaw danych opisuje funkcja „filtruj zawód to emeryt LUB zawód to student LUB wiek większy niż 59 lat”.
Przypadek 4: Filtrowanie po warunku1 LUB warunku2 ORAZ warunku3
W tym przypadku chcę wygenerować zestaw danych, które opisuje funkcja „filtruj wiek niższy niż 30 lat LUB wiek większy niż 59 ORAZ miejscowość to „Gdańsk”.
Wykorzystuję więc formułę:
=FILTER(Baza!A2:D60 ; (Baza!B2:B60 > 59) + (Baza!B2:B60 < 30) ; (Baza!D2:D60 = "Gdańsk")) Struktura formuły wygląda następująco: (warunek1) + (warunek2) ; (warunek3)
Ten sam wynik da również formuła:
=FILTER(Baza!A2:D60 ; ( (Baza!B2:B60 > 59) + (Baza!B2:B60 < 30) ) * (Baza!D2:D60 = "Gdańsk")) Warunek1 i warunek2 ująłem we wspólnym nawiasie, a średnik zastąpiłem znakiem mnożenia “*”. Oznacza to, że warunek3 odnosi się do wszystkich warunków ujętych we wspólnym nawiasie.
Przypadek 5: Łączenie z innymi funkcjami
FILTER daje możliwość zagnieżdżenia w formule kilkunastu różnych funkcji, lecz reguła jest taka sama – musi nastąpić zgodność z jakimś warunkiem.
Dla przykładu, określę wartość maksymalną w kolumnie „Wiek”.
Mogę użyć dwóch funkcji MAX oraz FILTER, wtedy wykorzystam formułę:
=MAX(Baza!B2:B60) Formuła zwróciła wynik 70.
Mogę zatem wygenerować zestaw danych, dla których w kolumnie „Wiek” wartości są większe lub równe 70 używając formuły:
=FILTER(Baza!A2:D60;Baza!B2:B60>=70) Natomiast ten sam efekt otrzymam poprzez połączenie funkcji FILTER z funkcją MAX w jednej formule:
=FILTER(Baza!A2:D60;Baza!B2:B60>=MAX(Baza!B2:B60)) 
Podobnie wyglądać będzie analiza kolumn, które zawierają np: liczby i litery.
jeżeli zależy nam na odfiltrowaniu wszystkich liczb to możemy utworzyć nową kolumnę, w której umieścimy funkcję CZY.LICZBA:
Szybszym i łatwiejszym rozwiązaniem będzie jednak zagnieździć funkcję CZY.LICZBA w funkcji FILTER:
=FILTER(A2:A8 ; CZY.LICZBA(A2:A8)) Ta formuła analizuje wszystkie pozycje w zakresie A2:A8, a następnie dla liczb ustawia wartość PRAWDA, a dla tekstu FAŁSZ.
Przypadek 6: Fragmentacja zakresów
Czasami nie wszystkie dane z bazy wykorzystywane są do dalszej obróbki, a ich widoczność wpływa na przejrzystość i naszą efektywność.
Możemy wykorzystać funkcję FILTER do wyodrębnienia interesujących nas kolumn.
Pojedyncza kolumna
Chciałabym poznać jedynie miejscowości, z których pochodzą programiści.
Dla pojedynczej kolumny mogę zastosować poniższą formułę:
=FILTER( Baza!D2:D60 ; Baza!C2:C60 = "programista") Jest to zmodyfikowana funkcja z przypadku 1.
Zamiast zakresu obejmującego całą bazę (Baza!A2:D60) umieściłem zakres (Baza!D2:D60), co umożliwi mi odfiltrowanie tylko ostatniej kolumny.
Wiele kolumn – Sposób I
Umieszczenie funkcji FILTER wewnątrz drugiej funkcji FILTER.
Teraz interesuje mnie dane z kolumn „Imię” i „Miejscowość”.
Ponieważ wiemy, że to programiści, ta kolumna zmniejsza przejrzystość naszej tabeli.
Mogę zastosować zatem poniższą formułę:
=FILTER( FILTER(Baza!A2:D60;Baza!C2:C60="programista") ; {1\1\0\1} ) Formułę, której użyłem w przypadku 1 “opakowałem” w drugą funkcję FILTER, której warunkiem będzie {1\1\0\1}.
Jest to oznaczenie, obrazujące ilość kolumn (4) oddzielonych znakiem „\”.
1 oznacza kolumny, które chcę pokazać (1, 2, 4). 0 kolumny wykluczone z wyodrębniania.
Taką samą funkcję możemy zastosować dla wierszy używając oznaczenia {1;1;0;0}. W takim wypadku należy spełnić warunek, w którym zakres funkcji FILTER wynosi 4 wiersze.
W łatwiejszy sposób można to zrobić za pomocą funkcji QUERY.
Wiele kolumn – Sposób II
Sposób I sprawdza się dla baz, które posiadają małą liczbę kolumn w zestawie danych.
W pozostałych wypadkach lepiej sprawdzi się zmiana zakresu.
W tym sposobie operuje na zakresie danych, które są przetwarzane przez funkcję FILTER, wykorzystując formułę:
=FILTER({ Baza!A2:B60 \ Baza!D2:D60 } ;Baza!C2:C60="programista") 
Zakres obejmujący Baza!A2:D60 przekształciłem do {Baza!A2:B60\Baza!D2:D60}.
Ograniczyłem tym samym zakres wyodrębnianych danych do kolumny A i B (A2:B60) oraz kolumny D (D2:D60).
Kolumna C w dalszym ciągu jest wykorzystywana jako warunek zastosowanej funkcji FILTER.
Potencjalne błędy
Oprócz błędu #ADR! opisanego w przypadku I, możemy napotkać także inne komunikaty.
Niezgodne rozmiary zakresu
Rozmiar zakresu w warunkach funkcji FILTER musi być spójny.
Jeżeli jednak oznaczymy różne zakresy, funkcja pokaże nam informację:
Jak widać jeden zakres filtruje po C2:C60, a drugi po B2:B30.
Nie znaleziono pasujących wyników
Jeśli żaden z warunków nie został spełniony, funkcja FILTER nie będzie mogła zwrócić wyniku.
Otrzymamy wtedy informację:
Oznacza to, że w bazie danych nie mam emerytów w wieku 80 lat lub starszych.
Tag
Share
Related Posts
