Like

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.

Tabela ma prawie 90 wierszy i kilkanaście kolumn opisanych w wierszu 3. Żeby sobie ułatwić pracę, nazwałem tą tabelę Lewandowski

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.

Użyliśmy polecenia query do stworzenia kopii tabeli Lewandowski ograniczonej do trzech kolumn. =query(Lewandowski;”select B,C,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.

=query(Lewandowski;”select C , sum ( F ) group by C order by sum ( F ) desc”)

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.

=query(Lewandowski;”Select A, count (C) group by A”)

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.

=query(Lewandowski;”select A, F where C = ‘”&W12&”‘ order by ( F ) desc limit 3″) daje nam zawartość kolumn A i F gdzie zawartość kolumny C odpowiada komórce W12. Sortujemy malejąco i ograniczamy wynik do pierwszych 3 wartości.

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.

W tym wypadku zapytanie można przetłumaczyć “na nasze” jako: Pokaż sezony (A) kiedy drużyną (B) był Bayern , a ligą (C) Bundesliga. Query wyszukiwanie robi w tabeli o nazwie Lewandowski obejmującej kolumny A do N.

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)
Przykład, gdy do funkcji query podstawiamy tabelę inaczej niż zwykłe wyświetlanie (w tym wypadku zanim użyto query, posortowano tabelę. Ale to wystarcza by arkusz wymagał identyfikatorów Col1, Col2, etc. Porównaj to z wcześniejszym obrazkiem.

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.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.