Arkusze Google / Excel
QUERY – narzędzie do analizy danych w Arkuszach Google
–
31 lipca, 2024
Czy wiesz, że można połączyć funkcjonalność filtrów i tabel przestawnych w arkuszach kalkulacyjnych?
Jeśli chcesz łatwo operować danymi za pomocą przycisków, bez konieczności zagłębiania się w ustawienia tabeli przestawnej, to czas poznać funkcję QUERY!
Czym jest QUERY?
QUERY to zaawansowana funkcja dostępna w Arkuszach Google, która pozwala na kompleksową analizę i manipulację danymi. Łączy w sobie możliwości filtrowania, sortowania, grupowania i agregacji danych, a wszystko to za pomocą prostej składni przypominającej język SQL.
Podstawowa składnia QUERY
Formuła składa się z trzech głównych elementów:
=QUERY(dane ; zapytanie ; [nagłówki])
zakres danych - tabela z danymi wejściowymi
zapytanie - sformułowanie sposobu filtrowania danych
nagłówki - stosowanie / pomijanie nagłówków Składnia podobna jest do funkcji FILTER, ale w zapytaniu nie rozdzielam poszczególnych filtrów średnikami, tylko opisuje je słownie.
Do prezentacji możliwości funkcji stworzyłem prostą tabelę z 40 wierszami danych.
Tutaj możesz skopiować plik Google Sheets, na którym pracowałem: KLIK
Przypadek 1: Filtrowanie całej tabeli przez jeden warunek
Z bazy danych chce wyfiltrować rekordy, w których kolumna C “zawód” zawiera słowo “emeryt”.
Używam następującej składni:
=QUERY(Baza!A1:D40; "select * where C = 'emeryt'"; 0)
zakres danych - tabela z danymi wejściowymi
zapytanie - ciąg tekstu
nagłówki - 0 W zapytaniu, dzięki zapisowi select, wybieram kolumny, które chcę zaprezentować w wyniku działania funkcji. Wybieram wszystkie kolumny z zakresu, co mogę zapisać jako select lub select A,B,C,D. Jeżeli chcę, aby wynik funkcji zawierał tylko kolumny B i D zmieniam zapis na select B,D.
Zaraz po części select mogę zastosować filtrowanie where. Filtruje zatem kolumnę C po rekordach, które zawierają słowo ‘emeryt’ przy użyciu zapisu where C = ‘emeryt’.
Wartość 0 w nagłówkach oznacza, że przefiltrowany zakres nie będzie pokazywać danych zaciągniętych z pierwszego wiersza tabeli z danymi wejściowymi. Jeżeli wpiszę tu wartość 1, to w pierwszym wierszu pojawią się dane z zakresu Baza!A1:D1.
Uwaga na błędy!
W funkcji może pojawić się problem, jeżeli wstawię nową kolumnę przed kolumną A. Wtedy zakres danych zmieni się na Baza!B1:E40. Składnia będzie filtrować rekordy po słowie ‘emeryt’ w kolumnie C, gdzie po dodaniu kolumny znajdują się aktualnie wartości odnoszące się do wieku. Otrzymam komunikat #N/A w komórce z formułą.
Mogę temu zapobiec, stosując pewien trik.
Używam następującej składni:
=QUERY({Baza!A1:D40} ; "select * where Col3 = 'emeryt'" ; 0) Zakres danych zamykam w wąsy { }. Teraz mogę odnieść się w zapytaniu nie do oznaczenia kolumny where C, tylko do jej pozycji where Col3. W ten sposób nawet jeśli przesunę zakres, będę się zawsze odnosić do trzeciej kolumny w zakresie danych.
W kolejnych przypadkach będę stosować oba zapisy.
Mogę też filtrować całą tabelę na podstawie pustych komórek.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where not C is null" ; 0)
=QUERY({Baza!A1:D40} ; "select * where not Col3 is null" ; 0) Stosując zapis where not X is null, pozbawiam wynik funkcji rekordów, które w kolumnie C są puste.
Natomiast stosując zapis where X is null, wynik funkcji wskaże wyłącznie rekordy, które w kolumnie C są puste.
Przypadek 2: Filtrowanie całej tabeli przez dwa warunki
Z bazy danych chcę wyfiltrować rekordy, w których w kolumnie C znajduje się wartość ‘emeryt’, a w kolumnie B wartość wieku jest wyższa niż 60.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where C = 'emeryt' and B > 60" ; 0)
=QUERY({Baza!A1:D40} ; "select * where Col3 = 'emeryt' and Col2 > 60" ; 0) W zapytaniu dodałem spójnik and, który określa konieczność spełnienia obu warunków przez rekordy.
Zastąpienie and spójnikiem or poskutkowałoby wynikiem formuły, w którym rekordy spełniają co najmniej jeden z tych warunków.
Przypadek 3: Filtrowanie po warunku1 ORAZ warunku2 LUB warunku3 ORAZ warunku4
Z bazy danych chcę wyfiltrować rekordy, w których kolumnie C znajduje się wartość ‘emeryt’ ORAZ w kolumnie B wartość wieku jest wyższa niż 60 LUB w kolumnie C znajduje się wartość ‘student’ ORAZ w kolumnie B wartość wieku jest niższa niż 30.
Używam następującej składni:
=QUERY( Baza!A1:D40 ; "select * where C = 'emeryt' and B > 60 or C = 'student' and B < 30" ; 0)
=QUERY({Baza!A1:D40} ; "select * where Col3 = 'emeryt' and Col2 > 60 or Col3 = 'student' and Col2 < 30" ; 0) Nie muszę pamiętać o dawaniu nawiasów. Jedyna zasada jest taka, że or jest spójnikiem parametrów połączonych ze sobą za pomocą and.
Przypadek 4: Łączenie QUERY z innymi funkcjami
Tutaj sytuacja jest trudniejsza niż w przypadku funkcji FILTER. Z bazy chcę wyfiltrować rekordy, w których w kolumnie B wartość wieku jest niższa, niż średnia wieku w bazie. Średnią liczymy bezpośrednio poprzez funkcję ŚREDNIA.
Używam następującej składni:
=QUERY( Baza!A2:D40 ; "select * where B < "& ZAOKR( ŚREDNIA(Baza!B2:B40) ;0) ;0)
=QUERY({Baza!A2:D40} ; "select * where Col2 < "& ZAOKR( ŚREDNIA(Baza!B2:B40) ;0) ;0) Komplikacje pojawiają się w momencie łączenia formuły. Zapytanie jest w postaci tekstowej, więc muszę “dołączyć” dynamiczne części (ZAOKR(ŚREDNIA(Baza!B2:B40);0)) po znaku &. Muszę zwrócić również uwagę na to, aby po znaku < zamknąć fragment tekstu cudzysłowem (select * where B < lub select * where Col2 <).
Nieco inaczej wygląda składnia, jeżeli chciałbym odnieść się do zmiennej w postaci tekstowej, np. „programista” z komórki F2.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where C = '"& F2 &"'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col3 = '"&F2&"'" ; 0) W przeciwieństwie do poprzedniej formuły muszę ująć F2 w apostrofach (‘ ‘) tak jak w Przypadku 1. Aby to zrobić, muszę użyć znaku & dwukrotnie, tworząc składnię ‘ “ & F2 & “ ‘ “.
Przypadek 5: Kolumna zawiera dany tekst / cyfry / pasuje do wzoru.
Zawiera “f” – CONTAINS
Z bazy chcę wyfiltrować rekordy, w których w kolumnie C znajdują się wartości zawierające literę f.
W bazie są zawody takie jak: grafik i farmaceuta, które zawierają literę f.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where C contains 'f'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col3 contains 'f'" ; 0) W zapytaniu po części where C lub where Col3 zamiast = umieszczam zwrot contains ‘f’.
Taki sam zabieg mogę zastosować dla innych wartości np. gra, 123.
Zaczyna się od “M” – STARTS WITH
Z bazy chcę wyfiltrować rekordy, w których w kolumnie A znajdują się imiona zaczynające się na literę M.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where A starts with 'M'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col1 starts with 'M'" ; 0) W zapytaniu po części where A lub where Col1 zamiast = umieszczam zwrot starts with ‘M’.
Kończy się na “a” – ENDS WITH
Z bazy chcę wyfiltrować rekordy, w których w kolumnie A znajdują się imiona kończące się na literę a.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where A ends with 'a'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col1 ends with 'a'" ; 0) W zapytaniu po części where A lub where Col1 zamiast = umieszczam zwrot ends with ‘a’.
Pasuje do wzoru – pierwsza litera “p”, ostatnia “a” – MATCHES
Z bazy chcę wyfiltrować rekordy, w których w kolumnie C znajdują się zawody zaczynające się na literę p i kończące na literę a.
Zawody w bazie zaczynające się na literę p to: prawnik, programista, pielęgniarka.
Jedyny niepasujący do wzoru zawód to “prawnik”, bo nie kończy się na literę a
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where C matches '^p.*?a'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col3 matches '^p.*?a'" ; 0) W zapytaniu po części where C lub Col3 zamiast = umieszczam zwrot matches '^p.*?a’.
Zwrot zawiera wyrażenia regularne:
^p – zaczyna się na literę p,
.* – zawiera więcej niż zero znaków dowolnych
?a – kończy się na literę a.
Mówi się, że jeśli masz problem i użyjesz wyrażeń regularnych do jego rozwiązania go, to… masz dwa problemy. Sformułowanie dobrego wyrażenia regularnego może być na początku trudne, ale praktyka czyni mistrza! Ostatecznie ich używanie pozwala na znacznie więcej elastyczności w pracy na bazach danych.
Dla ułatwienia sobie pracy z wyrażeniami regularnymi można wspomóc się biblioteką https://regex101.com/
Więcej informacji znajdziesz w artykule:
Pasuje do wzoru – pierwsza litera “p”, ostatnia “a” – LIKE
Z bazy chcę wyfiltrować rekordy, w których w kolumnie C znajdują się zawody zaczynające się na literę p i kończące na literę a. Tym razem wykorzystam inną metodę.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where C like 'p%a'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col3 like 'p%a'" ; 0) W tej metodzie umieszczam zapis like ‘p%a’.
% – oznacza dowolną ilość znaków.
W tej metodzie umieszczam zapis Jeżeli chciałbym wskazać określoną ilość znaków np.: wzór pasujący do wartości pwda, pdwa, pfra mogę użyć sformułowania p__a, gdzie dwa znaki _ oznaczają dwa dowolne pojedyncze znaki.
Przypadek 6: Sortowanie tabeli
Tym razem oprócz filtrowania, chcę posortować bazę za pomocą QUERY. Mogę to zrobić na dwa sposoby – dodając zewnętrzną funkcję SORT albo dodając na końcu zapytania order by.
Używam następującej składni:
=QUERY(Baza!A2:D40 ; "select * where C like 'p%a'" ; 0)
=QUERY({Baza!A2:D40} ; "select * where Col3 like 'p%a'" ; 0) W ramach order by ustawiam dwa parametry: kolumnę sortowania oraz kolejność sortowania.
Kolejność sortowania oznaczona jest poprzez:
asc – sortowanie A→Z
desc – sortowanie Z→A
Jeżeli potrzebuję dokładniejszego sortowania, to mogę dodać kolejne parametry.
Muszę pamiętać, aby oddzielić te parametry przecinkiem.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where C = 'emeryt' order by D asc, B desc";0)
=QUERY({Baza!A1:D40} ; "select * where Col3 = 'emeryt' order by Col4 asc, Col2 desc";0) 
Przypadek 7: Pierwsze X wyników – LIMIT
Z bazy chcę wyfiltrować wybraną liczbę rekordów z góry. W przypadku funkcji FILTER musiałbym wrzucić filter w jakimś miejscu, a następnie pobrać zakres do docelowego miejsca. W przypadku QUERY mogę zastosować parametr limit, którego wynikiem będzie wybrana liczba rekordów z góry zakresu bazy.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where B > 20 order by B asc limit 10" ; 0)
=QUERY({Baza!A1:D40} ; "select * where Col2 > 20 order by Col2 asc limit 10" ; 0) 
Funkcja z całej bazy pobiera rekordy, dla których wartość w kolumnie B jest większa niż 20. Są automatycznie posortowane rosnąco według wieku. Limit 10 sprawia, iż pobierane są tylko rekordy o 10 najniższych wartościach wieku w kolumnie B. Jeżeli chciałbym, żeby wynik funkcji pokazał 10 najwyższych wartości wieku w kolumnie B, wystarczy, że zamienię asc na desc.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where B > 20 order by B desc limit 10" ; 0)
=QUERY({Baza!A1:D40} ; "select * where Col2 > 20 order by Col2 desc limit 10" ; 0) 
Przypadek 8: Pomijanie wyników – OFFSET
Chcę pominąć z bazy wybraną liczbę rekordów z góry. Funkcja offset pozwoli mi na usunięcie ich z wyniku.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where B > 20 order by B asc offset 10" ; 0)
=QUERY({Baza!A1:D40} ; "select * where Col2 > 20 order by Col2 asc offset 10" ; 0) 
Funkcja z całej bazy pobiera rekordy, dla których wartość w kolumnie B jest większa niż 20. Są automatycznie posortowane rosnąco według wieku. Offset 10 sprawia, iż odrzucane z wyniku są rekordy o 10 najniższych wartościach wieku w kolumnie B.
BONUS: Połączenie LIMIT i OFFSET
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select * where B > 20 order by B asc limit 10 offset 5" ; 0)
=QUERY({Baza!A1:D40} ; "select * where B > 20 order by B asc limit 10 offset 5" ; 0) Funkcja z całej bazy pobiera rekordy, dla których wartość w kolumnie B jest większa niż 20. Są automatycznie posortowane rosnąco według wieku. Offset 5 powoduje, iż wynik formuły nie będzie zawierał pierwszych 5 rekordów. Limit 10 uszczupli dodatkowo wynik, pokazując 10 rekordów zawierających najniższe wartości wieku w kolumnie B.
Przypadek 9: Grupowanie według parametru – GROUP BY
Bazę danych chcę pogrupować według wybranych parametrów. Na przykład wyznaczyć średni wiek dla danego zawodu.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C, avg(B) group by C" ; 0)
=QUERY({Baza!A1:D40} ; "select Col3, avg(Col2) group by Col3" ; 0) W zapytaniu umieściłem parametr avg(B), którego wynikiem będzie średnia wieku, natomiast zapis group by C, pozwoli na pogrupowanie rekordów według wartości w kolumnie C.
W wyniku formuły otrzymuję średni wiek dla wszystkich zawodów (B3) oraz średni wiek dla poszczególnych zawodów z listy (B5:17).
Grupować (agregować) dane można także według innych parametrów:
Mogę grupować także po większej liczbie parametrów. Na przykład zawód i miejscowość.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C,D, avg(B) group by C,D" ; 0)
=QUERY({Baza!A1:D40} ; "select Col3,Col4, avg(Col2) group by Col3,Col4" ; 0) Muszę pamiętać, aby kolumny, które mają być parametrami grupowania (zawód i miejscowość), były podane razem na końcu komendy.
W powyższym przykładzie pojawia się wygenerowany dodatkowy nagłówek, a wyliczona średnia ma nieograniczoną liczbę miejsc po przecinku.
W poniższych bonusach pokazuję jak to zmienić.
BONUS I: Nazwy nagłówka – LABEL
Aby usunąć dodatkowo wygenerowany przez funkcję nagłówek, dodam parametr label i zmienię wartość nagłówków.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C, avg(B) group by C label avg(B) '', C ''" ; 2 )
=QUERY({Baza!A1:D40} ; "select Col3, avg(Col2) group by Col3 label avg(Col2) '', Col3 ''"; 2) W zapytaniu po zapisie group by C umieszczam parametr label avg(B) ”, C ” – w wyniku nagłówek wygenerowany przez funkcję zostanie zamieniony na pusty. W nagłówkach podałem wartość 2 – formuła rozpoczynać się będzie od 2 wiersza wyniku.
Jeśli nie mam przygotowanych wcześniej nagłówków bazy, mogę nazwać kolumny według własnego upodobania. W tym celu wpisuję w apostrofy (‘ ‘) swój tekst ‘średni wiek’ i ‘zawód’. Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C, avg(B) group by C label avg(B) 'średni wiek', C 'zawód'" ; 2 )
=QUERY({Baza!A1:D40} ; "select Col3, avg(Col2) group by Col3 label avg(Col2) 'średni wiek', Col3 'zawód'"; 2) 
BONUS II: Formatowanie danych
Dane, które można formatować za pomocą QUERY to: liczby, data, czas, data i czas oraz boolean.
Dane, pokazywane w tabeli wynikowej możemy formatować według zasad zawartych w artykule.
Aby zmienić ilość miejsc po przecinku w średniej wyliczanej w Przykładzie 9, wykorzystam parametr format.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C, avg(B) group by C label avg(B) '', C '' format avg(B) '0.00'" ; 2 )
=QUERY({Baza!A1:D40} ; "select Col3, avg(Col2) group by Col3 label avg(Col2) '', Col3 '' format avg(Col2) '0.00'"; 2) W zapytaniu po zapisie group by C lub w moim przypadku po parametrze label, umieszczam zapis format avg(B) ‘ 0.00 ’ “. Formatuje on średnie z kolumny B (wiek), tak aby końcowy zapis zawierał dwa miejsca po przecinku. Formuła wstawia 0 w puste miejsca zarówno dla jedności, jak i miejsc po przecinku.
Przy zastosowaniu zapisu #.## formuła pozostawi miejsca bez liczb puste np. dla wartości 41; 45,56 i 0,5 sformatuje wynik w następujący sposób: 41, ; 45,56 ; ,5. Na tej samej zasadzie formatowane są wartości dat i czasu.
Wartości boolean to inaczej PRAWDA (TRUE) i FAŁSZ (FALSE). Nie są one jednak wartościami tekstowymi. Wzorzec to string w formacie „wartość_jeśli_prawda:wartość_jeśli_fałsz”.
Co dziwne, czasami formatowanie boolean nie działa, to jak powinno i bardzo dużo zapytań o to, dlaczego to nadal nie jest naprawione przez Google pozostaje bez odpowiedzi 😥
Przypadek 10: Tabela przestawna – PIVOT
W Przypadku 9 otrzymałem pionową listę wybranych danych. Chcę ją zmienić w tabelę przestawną, więc wykorzystam parametr pivot.
Używam następującej składni:
=QUERY(Baza!A1:D40 ; "select C, avg(B) group by C pivot D" ; -1)
=QUERY({Baza!A1:D40} ; "select Col3, avg(Col2) group by Col3 pivot Col4" ; -1) W wyniku formuły przyporządkowuje wartości do odpowiednich kolumn (miejscowości) i wierszy (zawody).
Przekształcanie danych: Funkcje skalarne
Funkcje skalarne odpowiadają za przekształcanie danych zawartych w bazie. Przykład: dla kolumny z pełnymi datami, mogę wyciągnąć kolumnę zawierającą wyłącznie rok.
Funkcje skalarne, jakie można zastosować, wykorzystując QUERY to:
Modyfikacja kolumn: Operatory arytmetyczne
Operatory arytmetyczne służą do modyfikacji kolumn za pomocą:
=QUERY({A2:C17};"select Col1*Col2/Col3")
=QUERY(A2:C17;"select A*B/C") 
Podsumowanie
Funkcja QUERY w Arkuszach Google to potężne narzędzie łączące w sobie zalety filtrów i tabel przestawnych. Oferuje zaawansowane możliwości analizy danych, pozwalając na łatwe filtrowanie, sortowanie, grupowanie i agregację informacji. Dzięki składni przypominającej SQL, QUERY umożliwi Ci tworzenie złożonych zapytań bez konieczności zagłębiania się w skomplikowane ustawienia. To idealne rozwiązanie do zwiększenia efektywności zarządzania danymi w arkuszach kalkulacyjnych, oferujące elastyczność i prostotę obsługi za pomocą intuicyjnych przycisków. Opanowanie funkcji QUERY otworzy przed Tobą nowe możliwości w analizie danych, będąc niezbędnym narzędziem do zwiększenia produktywności w pracy z Arkuszami Google
Tag
Share
Related Posts
