Filtrowanie z użyciem słów kluczowych i pól wyboru

Za pomocą formuły QUERY możemy przefiltrować tabelę używając słów kluczowych wpisywanych w komórki. Możemy również użyć pól wielokrotnego wyboru.

W tym wpisie posłużę się bazą cen samochodów używanych przygotowaną przez Aleksandra Glotova.

Baza zawiera prawie 120 000 ofert zebranych z Otomoto pod koniec stycznia 2022.

Opisane poniżej przykłady i całą bazę można znaleźć w pliku roboczym.

Ma on postać tabeli z 11 kolumnami:

Filtrowanie za pomocą wszystkich słów kluczowych

Słowa kluczowe wewnątrz formuły

Zacznijmy od prostego filtra z użyciem słów kluczowych zapisanych w formule. Załóżmy, że chcemy wyciągnąć z listy oferty dotyczące Volkswagena Golfa.

Tworzę nową zakładkę, gdzie zamierzam wyświetlić wyniki.

Moja formuła będzie wyglądać tak:

=query({dane!A1:K};"select * where Col2='volkswagen' and Col3='golf'";1)

Użycie { } wokół zakresu, pozwala mi opisywać poszczególne kolumny jako Col1, Col2, etc. zamiast liter A , B, etc. Przywykłem do tego sposobu, bo jest on konieczny, gdy używamy bardziej skomplikowanych formuł.

Treść zapytania wewnątrz cudzysłowu to język QUERY – bardzo wygodny jeśli się go trochę pozna.

select * oznacza, że chcę wyświetlić wszystkie kolumny z zakresu

Where Col2='volkswagen' and Col3='golf' oznacza, że wyświetlone mają być tylko te wiersze, gdzie kolumna 2 to ‘volkswagen’ a kolumna 3 to ‘golf’

1 na końcu oznacza, że w filtrowanym zakresie jest jeden wiersz nagłówka.

Słowa kluczowe w oddzielnej komórce

Często chcemy móc szybko zmieniać słowa kluczowe i warto mieć ich podgląd i szybki dostęp. W tym wypadku możliwość wyboru marki i modelu jest w komórkach B4 i C4. Formuła wygląda tak:

=query({dane!A1:K};"select * where Col2='"&B4&"' and Col3='"&C4&"'";1)

Skąd te wszystkie ‘”& &”‘ ?

‘ to jest potrzebne by formuła query wiedziała, że podsuwamy jej tekst

” oznacza, że na moment wychodzimy z wnętrza opisu (potem wracamy drugim “)

& pozwala sklejać kilka komend ze sobą

Rozwiązanie problemu wielkości liter

Jak widzicie, w tabeli wszystkie nazwy modeli są pisane z małych liter i jeśli w wyszukiwanym polu użyjemy dużych liter – wyniki się nie wyświetlą. Dlatego użyjemy formuły LOWER(), żeby do QUERY zostały podstawione słowa kluczowe bez dużych liter.

=query({dane!A1:K};"select * where Col2='"&lower(B4)&"' and Col3='"&lower(C4)&"'";1)

Filtrowanie z zostawianiem niektórych słów kluczowych pustych

Może się zdarzyć, że nie chcemy wypełniać niektórych pól np. po to by wyświetlić wszystkie auta Volkswagena, bez względu na model (pojawi się ponad 10 000 pozycji). W takiej sytuacji musimy zastąpić ‘=’ słowem ‘contains’. To pozwoli zarówno na wpisanie niepełnej nazwy modelu (np. Gol zamiast Golf), a zostawienie pola pustego sprawi, że nie będzie ono brane pod uwagę.

=query({dane!A1:K};"select * where Col2 contains '"&LOWER(B4)&"' and Col3 contains '"&lower(C4)&"'";1)

Filtrowanie z pomocą pól wielokrotnego wyboru ,tzw. ptaszków ☑

Ten fragment zacznę od krótkiego przygotowania wyrażenia do wstawienia w QUERY. Potrzebujemy spisu wszystkich wybranych opcji w odpowiednim formacie.

Chcemy, żeby filtr uwzględniał tylko auta z napędem hybrydowym lub elektrycznym.

Pierwsze zadanie to stworzenie filtra wybranych wyrażeń:

=join("|";filter(G2:G6;F2:F6))

Formuła filter(G2:G6;F2:F6) wyświetla te pozycje z kolumny G, którym odpowiadają ptaszki. Ponieważ ptaszek jest dla arkusza widoczny jako wartość logiczna PRAWDA, to nie muszę piszać filter(G2:G6;F2:F6=true) , arkusz się domyśli 🙂

Następnie przy pomocy JOIN łączę wybrane wartości w jedną komórkę, używając jako separatora znaku “|”. W wyrażeniach regularnych jest on jednoznaczny ze znakiem “lub”.

Następnie przerabiam zapytanie QUERY tak by akceptowało wyrażenia regularne. Służy do tego słówko “matches”. Ponieważ informacja o rodzaju paliwa jest w kolumnie 8, to formuła wygląda teraz tak:

=query({dane!A1:K};"select * where Col2 contains '"&LOWER(B4)&"' and Col3 contains '"&lower(C4)&"' and Col8 matches '"&join("|";filter(G2:G6;F2:F6))&"'";1)