Formuła QUERY w Arkuszach Google. Co to i dlaczego jest mocarne?

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

=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.