Filtrowanie – czyli wydobywanie wierszy, które nas interesują. I nie “z palca na menu”, a automatycznie i bez niszczenia oryginalnej tabeli.

Zarówno Google Sheets jak i Excel dają całkiem niezłe opcje filtrowania. Jednak królem filtrowania jest ręczna funkcja FILTER, którą możemy wyświetlać przefiltrowane tabele w innym miejscu niż pierwotnie i nie naruszać oryginału. Ponadto funkcję możemy zagnieżdżać wraz z innymi funkcjami – np. z sumowaniem, wyciąganiem średniej, sortowaniem.

Kiedy potrzebujemy funkcji FILTER?

Za każdym razem gdy chcemy wyodrębnić jakąś część danych i coś z nimi zrobić dalej. Np. w przypadku wyników biegu – pokazać tylko wyniki kobiet. W przypadku zestawienia sprzedaży – np. jedynie transakcje z woj. Śląskiego. A w przypadku listy filmów np. tylko filmy wyprodukowane w roku 2000 i później.

W dobie współdzielonych dokumentów i automatycznych aktualizacji, ważne jest by umieć pracować nie naruszając oryginału. Bo:

• możemy nie mieć odpowiednich uprawnień
• nie chcemy komuś mieszać w dokumentach
• dane źródłowe będą się jeszcze zmieniać
• ułożony zbiór funkcji może być łatwo wykorzystany do obróbki innych danych.

Do ćwiczenia posłuży nam TOP200 filmów z rankingu Filmweb.pl (widzimy go w kolumnach A do E).

Wszystkie zamieszczone tu zrzuty ekranu pochodzą z tego arkusza Google. Żeby go edytować zrób sobie własną kopię (menu Plik –> Utwórz kopię).

Podstawowa składnia

=FILTER(zakres; warunek1; warunek2)

• zakres – to będziemy wyświetlać. Może to być jedna lub kilka kolumn (w pierwszym przykładzie używamy całego rankingu – bez nagłówków, czyli A4:E203)
• warunek 1 – kryterium filtrowania wraz z podaniem zakresu, którego ma dotyczyć. D4:D203>=2000 oznacza, że wyświetlone zostaną tylko te wiersze z kolumn A do E, które w kolumnie D mają wartość większą lub równą 2000.
• warunek 2 – drugie kryterium które pozwala zawęzić wybór (aby wiersz został wyświetlony, muszą być spełnione OBA warunki) – w tym wypadku E4:E203>500000 oznacza, że wyświetlone zostaną te wiersze, gdzie w kolumnie E jest liczba większa lub równa 500 000.

Warunków możemy ułożyć więcej. Należy pamiętać, że zakresy i warunki muszą mieć tą samą ilość wierszy.

Nie zawsze chcemy i nie musimy wyświetlać kryterium którego użyliśmy do filtrowania. W drugim przykładzie – poniżej, widać że zasady wyboru filmów są te same, ale wyświetlamy tylko Tytuł, Ocenę i rok produkcji.

To użyteczne rozwiązanie – jeśli np. chcemy wyświetlić transakcje z jednego regionu sprzedaży (np. Śląskie), to wrzucanie słowa woj. Śląskie w każdym wierszu nie służy niczemu więcej niż bałaganowi. A kryterium możemy wyświetlić np. powyżej tabeli. Zobaczcie na naszym przykładzie:

Kolejny przykład to dodanie dwóch warunków dotyczących tego samego kryterium. Załóżmy, że chcemy wyciągnąć z listy tylko filmy zrobione w latach 2015-2020 i szybko manewrować tymi datami. Oto rozwiązanie:

Użyteczne zagnieżdżanie

Filtrowanie plików zewnętrznych

Funkcję filter można zastosować do każdego zakresu. Możemy filtrować tablice z naszego pliku, ale możemy pobierać z obcego pliku tylko te wiersze które nas interesują. Wówczas jako zakresu używamy funkcji IMPORTRANGE.

Przypomnijmy na moment jeszcze składnię filter:

=FILTER(zakres; warunek1; warunek2)

W związku z tym korzystanie z zakresu w innym pliku i filtrowanie go będzie wyglądać tak:

=FILTER(IMPORTRANGE("url...";"A2:B100");IMPORTRANGE("url...";"A2:A100")<200)

W ten sposób funkcja FILTER pociągnie nam zakres A2:B100 z pliku pod adresem url… ale tylko wiersze które w kolumnie A mają wartość niższą niż 200.

Filtrowanie z sortowaniem

Na załączonym przykładzie najpierw definiujemy jak ma wyglądać tabela (za pomocą funkcji FILTER ustalamy, że ma mieć 3 wiersze), a następnie określamy, że funkcja SORT posortuje ją według roku publikacji (3 kolumna), malejąco (0), a w obrębie danego roku będzie sortowanie wg pierwszej kolumny (1), rosnąco (1).

Przypominam, że składnia wygląda tak: =SORT(zakres; numer kolumny sortującej; rosnąco (1 lub 0); numer drugorzędnej kolumny sortowania; rosnąco (1 lub 0).

W tym wypadku jako zakres traktujemy to co dostarcza nam funkcja FILTER.

Działania na przefiltrowanych danych.

Załóżmy, że chcemy zobaczyć jaka jest średnia ocen filmów z 2015 roku w naszej tabeli.

Jednym ze sposobów jest przefiltrowanie tej wartości i zagnieżdżenie w funkcji AVERAGE (ŚREDNIA).

Pierwszy etap to sam filtr:

Nic nie stoi na przeszkodzie, żeby teraz potraktować to funkcją AVERAGE – nie musimy wcale wyświetlać tych danych by zrobić na nich działanie.

Po zagnieżdżeniu wynik wygląda tak:

Tak samo możemy sumować (SUM) zakresy uzyskane przez fukncję FILTER, lub zliczać wiersze (COUNT lub COUNTA) wycinać duplikaty (UNIQUE), ograniczać wielkość tabeli wynikowej (ARRAY_CONSTRAIN) i wiele innych.

Pamiętaj: Jeśli za rok czy dwa wgramy nowe wyniki rankingu – wszystkie nasze filtrowania będą ciągle działać. Tego nie da się zrobić przy obróbce z funkcji menu!

Użycie funkcji FILTER z zapisem klamrowym

Jeśli chcemy wyświetlić kolumny, które nie są obok siebie, powinniśmy użyć zapisu klamrowego. Zatem jeśli chcemy wyświetlić tylko Tytuł i Rok produkcji, należy użyć zapisu {zakres1 \ zakres2} – oznacza to ułożenie zakresów jeden obok drugiego. W tym wypadku będzie to wyglądało tak:

{B5:B204 \ D5:D204}

(pamiętajcie – to nie jest dzielenie, to backslash)

Poniżej pokazuję modyfikację jednego z wcześniejszych przykładów – tym razem z użyciem 2 kolumn, które nie leżą obok siebie:

Przykład złożenia wszystkiego razem.

Teraz trochę zamieszamy by pokazać ile da się uzyskać przy pomocy kilku funkcji i zapisu klamrowego.

Zadanie:

Pokażmy wszystkie filmy z danego przedziału lat – tytuł i rok produkcji, ale pod zestawieniem podsumujmy ile filmów zostało wyświetlonych i określmy średnią ocenę.

Myk polega na tym, że nasze podsumowanie nie będzie miało stałego punktu położenia, tylko będzie jeździć razem ze zmieniającą się długością tabeli.

W tym celu musimy wszystko sprząc zapisem klamrowym.

Będzie to postać { Tytuł i rok ; Ilość tytułów \ śr. ocena}

; oznacza że następne zakresy będą poniżej,

\ oznacza, że następny zakres będzie po prawej.

Tytuł	Rok
Ilość tytułów ##	Średnia ocena ##

Funkcja do uzyskania tytułu i roku jest już uzyskana to:

=sort( filter({B5:B204\D5:D204};D5:D204>=W3;D5:D204<=W2) ;3;0;1;1)

Żeby uzyskać niezależnie ilość tytułów łączymy funkcję FILTER z funkcją COUNTA, która policzy nam zgromadzone tytuły:

=counta( filter(B5:B204 ; D5:D204>=W3 ; D5:D204<=W2) )

Funkcja FILTER prawie się nie zmieniła – tym razem bierzemy jedną kolumnę (B5:B204), żeby policzyć tytuły.

Średnią ocenę uzyskujemy robiąc filtr kolumny C (to ta z ocenami), a następnie wyciągając średnią:

=average( filter(C5:C204 ; D5:D204>=W3 ; D5:D204<=W2) )

Na koniec sklejamy to wszystko razem w jedną długą komendę, która pozwoli wyświetlić wszystko za jednym zamachem.

W ten sposób skleiliśmy aż 3 filtrowania w jednej formule. I jeszcze dla kosmetyki dodamy etykiety opisujące te ostatnie pozycje. Używamy do tego zapisu &, którym można łączyć tekst z formułą ="dowolny tekst "&formuła (zobacz wpis poświęcony łączeniu komórek i zakresów).

Finalna wersja formuły wygląda następująco:

={ sort( filter({B5:B204\D5:D204};D5:D204>=W3;D5:D204<=W2) ;2;0;1;1) ;
"Ilość tytułów: " & counta( filter(B5:B204;D5:D204>=W3;D5:D204<=W2) ) \
"śr. ocena: "&average(filter(C5:C204;D5:D204>=W3;D5:D204<=W2)) }

Wiem, że to dużo do łyknięcia, ale przypomnijcie sobie: kilka ekranów wcześniej jest zdefiniowana struktura, którą tworzymy i po kolei były pokazywane składniki:

{ Tytuł i rok ; Ilość tytułów \ śr. ocena }

Dla elegancji dodaliśmy formatowanie warunkowe, które zakolorowało nam komórki zawierające słowa kluczowe “śr. ocena” i “ilość tytułów”:

Jeśli udało Ci się dobrnąć w to miejsce – możesz być z siebie dumny! I nie zapomnij obejrzeć jakiegoś filmu z tego rankingu!