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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
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ą:
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
