Mówiąc krótko – potężna machina do wyławiania i obrabiania danych z tabel. Umie filtrować tabele według różnych kryteriów, ograniczać ich wielkość, podliczać składniki tabeli, sumować, sortować. Składnia tej funkcji jest dość czytelna i opiera się na języku podobnym do SQL – Google Visualization API Query Language. Podstawowe zastosowania pokażę w tym wpisie, ale bądźcie świadomi, że Query jest jak kombajn i zanim poznacie wszystkie tajniki – minie sporo czasu. Ale proste polecenia ogarniecie w kwadrans.
Korzystając z tego wpisu warto odpalić plik roboczy, który wykorzystuję do pokazania przykładu. Pracuję na tabeli opisującej karierę naszego znakomitego piłkarza Roberta Lewandowskiego.
- Podstawowa składnia
- Wyświetlanie poszczególnych kolumn za pomocą Query
- Sumowanie przy pomocy Query
- Sortowanie przy pomocy Query
- Zliczanie wystąpień danej wartości przy pomocy Query
- Usuwanie pustych wierszy w Arkuszu Google przy pomocy Query
- Ograniczanie wielkości tabeli przy pomocy Query
- Używanie odniesień do zawartości pojedynczych komórek.
- Używanie razem z innymi funkcjami tworzącymi tabele i nawigacja w nich.
- Jak pozbyć się nagłówków w wyniku QUERY
- Trick pozwalający na spłaszczenie tabeli
- Coś jeszcze?
Podstawowa składnia
=QUERY(tabela; "zapytanie";ilość wierszy nagłówków)
tabela – to może być zakres (np. A1:N100), nazwana tabela, a może być funkcja generująca tabelę (np. =googlefinance(“CURRENCY:USDPLN”;”PRICE”;TODAY()-30;30;1 generuje tabelę kursów dolara względem złotówki z ostatnich 30 dni).
zapytanie – zapytanie przy użyciu SQL-podobnego języka
ilość wierszy nagłówków – parametr opcjonalny mówiący ile wierszy tabeli źródłowej ma być traktowanych jako nagłówki.
Wyświetlanie poszczególnych kolumn za pomocą Query
Podstawowe zapytanie to prośba o wyświetlenie określonych kolumn. W tym celu używamy polecenia select.
=query(Lewandowski;”select *”) wyświetli nam całą tabelę.
=query(Lewandowski;”select B,C,F”) wyświetli nam tylko zawartość kolumny B,C oraz F.
Sumowanie przy pomocy Query
Weźmy tabelę i spróbujmy zobaczyć w jakich rozgrywkach (C) Lewandowski strzelił ile bramek (F) (podsumujemy wszystkie sezony).
Służy do tego
=query(Lewandowski;"select C , sum ( F ) group by C")
Jak widać query ładnie sobie poradziło i nawet posortowało wyniki automatycznie rosnąco wedle pierwszej wyświetlanej kolumny.
Sortowanie przy pomocy Query
Teraz chcemy wziąć dane z poprzedniego przykładu i posortować jednak według ilości zdobytych bramek. Musimy zatem dodać na końcu informację order by sum (F), (koniecznie dalej niż opcja group by)
Nasza funkcja wygląda teraz tak:
=query(Lewandowski;"select C , sum ( F ) group by C order by sum ( F ) desc")
order by (kolumna) desc – oznacza sortowanie od największego do najmniejszego, natomiast:
order by (kolumna) asc – oznacza sortowanie rosnące.
Zliczanie wystąpień danej wartości przy pomocy Query
Załóżmy, że chcemy zobaczyć w rodzajach rozgrywek aktywny był Lewandowski w każdym sezonie. Żeby się tego dowiedzieć, musimy sprawdzić ile jest rozgrywek (Bundesliga, Puchar Niebiec, etc.) dla każdego sezonu.
W tym celu dodajemy count (C)
do naszej formuły, bo rozgrywki są w kolumnie C.
Usuwanie pustych wierszy w Arkuszu Google przy pomocy Query
To narzędzie przydatne bardzo często, gdy łączymy tabele pociągnięte do końca arkusza (np. opisane A:C). Jeśli złączymy dwie takie jedna nad drugą, to 1000 czy więcej pustych wierszy może nam przeszkadzać.
Oto przykład – bierzemy dwie tabele V4:V i W4:W, wciągamy je w nawias klamrowy i ustawiamy jedna na drugiej (łącząc średnikiem). Następnie przy pomocy funkcji Query prosimy by pokazana została cała zawartość (*) pod warunkiem, że kolumna nr 1 (czyli jednyna kolumna dostępna po połączeniu) nie jest pusta (is not null).
Ograniczanie wielkości tabeli przy pomocy Query
W arkuszach google mamy standardową funkcję ograniczającą wielkość tabeli – array_constrain
, ale wynikową tabelę możemy przyciąć w samej formule query przy pomocy parametru limit
.
Załóżmy, że chcemy zobaczyć tylko TOP 3 sezony (A) jeśli chodzi o ilość strzelonych bramek (F) w rozgrywkach bundesligi (C).
=query(Lewandowski;"select A, F where C = 'Bundesliga' order by ( F ) desc limit 3")
Używanie odniesień do zawartości pojedynczych komórek.
Zdarza się, że korzystając z query potrzebujemy się odnieść do jakiejś komórki (w której np. podane będzie kryterium filtrowania). Musimy wprowadzić do zapytania odniesienie do adresu komórki. Da się to zrobić.
Wróćmy do przykładu z TOP 3 strzelonych bramek w rozgrywkach z poprzedniego przykładu. Tym razem spróbujemy mieć wybór rozgrywek. W komórce W12 będziemy wpisywać nazwę ligi, a funkcja query ma nam podać 3 najlepsze sezony Lewandowskiego jeśli chodzi o liczbę strzelonych bramek.
W całym przykładzie chodzi o składnię:
‘ ‘ – służą do tego, że musimy podać ciąg znaków, a nie np. liczbę
” ” – kończą zapytanie, a potem do niego wracają
& & – pozwalają dopiąć dodatkową wartość (a nawet formułę).
Razem wygląda to: ‘ “&w12&” ‘.
Używanie razem z innymi funkcjami tworzącymi tabele i nawigacja w nich.
Jeśli funkcja query używa istniejącej tabeli, to do konkretnych kolumn odnosimy się przy pomocy ich oznaczeń literowych u źródła.
Natomiast jeśli funkcja query obsługuje coś co nie jest wyświetlone w bieżącym pliku (np. importuje tabelę przy pomocy importrange) lub query jest składana z innymi funkcjmi (np. query używa się do tabeli stworzonej przy pomocy funkcji, filter, arrayformula, googlefinance, etc.) wówczas do konkretnej klumny odnosimy się używając jej numeru i przedrostka Col. A więc Col1, Col2, Col3, etc.
Jeśli jest nam wygodniej pracować na zapisie Col1, Col2 Col3…, a używamy standardowego zakresu (np. A1:C), to powinniśmy wpisać go w query w nawiasie klamrowym – {A1:C} . Nie można tych notacji mieszać. zawsze jest dostępny albo zapis literowy albo Col1, Col2…
Marek Aureliusz (chyba)
Jak pozbyć się nagłówków w wyniku QUERY
Query ma wkurzającą cechę – jeśli używamy sum lub średnich, query tworzy wiersz z nagłówkiem, a wyniki zaczynają się od drugiego wiersza.
Żeby się pozbyć nagłówka, należy użyć pustej etykiety – label [id kolumny] ” (dwa pojedyncze cudzysłowy) np.:
=Query({A1:B};”select Col1, sum(Col2) group by Col1 order by sum(Col2) desc limit 5 label sum(Col2) ” “
Trick pozwalający na spłaszczenie tabeli
Chodzi o operację, która pozwala pozbawić każdą kolumnę pustych wierszy, niezależnie.
Ostatni parametr w Query służy do określenia ile wierszy w zakresie danych stanowią nagłówki. Na tej podstawie tworzone są nagłówki w wynikach Query. Ale jeśli nagłówki mają np. 2 wiersze, to ich zawartość zostanie włożona do jednej komórki. Skrajną sytuacją jest wstawienie “nieskończonej liczby” nagłówków. Wówczas cała kolumna ląduje w jednej komórce, ale znikają puste przestrzenie. 99^99 oznacza (99 podniesione do potęgi 99 – czyli więcej wierszy niż ktokolwiek widział)
Ten etap jeszcze nic nie wnosi (warto pamiętać, że jest taka możliwość). Ale możemy go przekształcać:
Żeby uzyskać zamierzony efekt, najpierw obrócimy wynik Query o 90 stopni (przy pomocy transpose), następnie zawartość każdej komórki podzielimy używając spacji jako separatora (split by dzielić i arrayformula by podzielić wszystkie). Na koniec obrócimy tabelę z powrotem (znów transpose).
Efektem jest tabela pozbawiona pustych komórek.
Coś jeszcze?
Zastosowań Query jest mnóstwo. Ja omówiłem tylko te, z których korzystam w mojej pracy. Bardzo ciekawy i dość wyczerpujący wpis na temat query zrobił na swoim blogu spec z Googla Ben Collins.