Like

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ę).

Przykład zastosowania fukcji FILTER – zawężenie listy do filmów z 2000 roku i nowszych, mających też ponad 500 000 ocen.

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.

=filter(B4:D203;D4:D203>=2000;E4:E203>=500000) wyświetla tylko 3 kolumny. Jedna z kolumn której użyliśmy jako kryterium pozostaje niewidoczna.

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:

Kryterium do funkcji FILTER jest pobierane w formie liczby (2000), ale też jako odwołanie do komórki O3 gdzie jest wartość 300 000. Dzięki temu możemy szybko zmieniać kryterium.

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:

=filter(B5:D204;D5:D204>=S3;D5:D204<=S2) – pozwala założyć dwa warunki na jedną kolumnę.

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

=sort( filter(B5:D204;D5:D204>=S3;D5:D204<=S2) ;3;0;1;1) najpierw filtrujemy tabelę, ale zanim wynik zostanie wyświetlony. zostaje ona posortowana.

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:

Na pierwszym etapie =filter(C5:C204;D5:D204=2010) uzyskujemy tabelę liczb.

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:

Przy pomocy dwóch zagnieżdżonych funkcji możemy wyciągnąć grupę danych i zrobić na nich nową operację.

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.

Zgodnie z wczęśniej narysowanym schematem, najpierw wyświetlamy dwukolumnową tabelę z tytułami i Rokiem produkcji, a pod nią ilość tytułów i średnią ocenę.

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!

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.