Wykonywanie zapytań na danych za pomocą QUERY

Google Sheets ma jedną funkcję, która przypomina mały język baz danych: QUERY. Pozwala ona wybierać, filtrować i podsumowywać dane za pomocą składni bardzo zbliżonej do SQL.

Zamiast łączyć funkcje FILTER, SORT i SUMIF, można napisać jedną czytelną instrukcję. Na przykład polecenie pokaż tylko wiersze regionu East, posortowane według sprzedaży staje się pojedynczą formułą.

QUERY jest dostępna tylko w Google Sheets. Excel jej nie obsługuje, dlatego cała ta lekcja dotyczy wyłącznie Sheets.

QUERY składa się z trzech części:

• data zakres do odczytania, na przykład A1:D100
• query tekstowa instrukcja w cudzysłowie
• headers opcjonalna liczba określająca, ile wierszy nagłówków zawiera zakres

To właśnie w ciągu zapytania kryje się cała magia. Zawsze należy umieszczać go w podwójnych cudzysłowach.

Klauzula SELECT wybiera kolumny, które mają zostać zwrócone. W QUERY kolumny wskazuje się za pomocą liter arkusza (A, B, C), a nie nazw nagłówków.

Użyj SELECT *, aby zwrócić wszystkie kolumny, albo wypisz tylko te, których potrzebujesz. Kolejność ich wypisania określa kolejność w wyniku.

Wyobraź sobie arkusz sprzedaży w zakresie A1:D7 z nagłówkami Region, Rep, Product, Sales:

• A: Region (East, West)
• B: Rep (Ann, Bo, Cy)
• C: Product (Pens, Paper)
• D: Sales (liczby)

Tego zbioru danych będziemy używać w dalszej części lekcji. Wyobrażaj go sobie za każdym razem, gdy poniżej zobaczysz formułę QUERY.

Klauzula WHERE zachowuje tylko te wiersze, które spełniają określony warunek. Kolumnę można porównać z wartością za pomocą operatorów takich jak =, >, < i >=.

Wartości tekstowe zapisuje się w pojedynczych cudzysłowach wewnątrz ciągu zapytania, ponieważ całe zapytanie jest już ujęte w podwójne cudzysłowy.

Filtry liczbowe nie wymagają cudzysłowów. Aby wyświetlić tylko sprzedaż o dużej wartości, porównaj kolumnę D bezpośrednio z liczbą.

Spowoduje to zwrócenie tylko tych wierszy, w których wartość Sales jest większa niż 500, dzięki czemu raport skupi się na najważniejszych transakcjach.

Użyj AND i OR, aby łączyć warunki, podobnie jak w SQL.

• AND wymaga, aby oba warunki były spełnione
• OR wymaga spełnienia co najmniej jednego warunku

Ten przykład zachowuje wiersze regionu East, w których sprzedaż przekroczyła również 300.

Dodaj LIMIT, aby ograniczyć liczbę zwracanych wierszy. Przydaje się to do tworzenia podglądów typu top-N lub zachowania porządku na pulpicie nawigacyjnym.

W tym przypadku pobierane są tylko 3 pierwsze pasujące wiersze. Później można połączyć tę klauzulę z sortowaniem, aby utworzyć rzeczywistą listę top 3.

Na początku sprawia to trudność niemal każdemu. Całe zapytanie jest jednym ciągiem tekstowym Sheets, dlatego używa podwójnych cudzysłowów. Każdy porównywany tekst musi więc używać znajdujących się wewnątrz niego pojedynczych cudzysłowów.

Zapisanie WHERE A = "East" psuje formułę, ponieważ wewnętrzny podwójny cudzysłów zbyt wcześnie kończy ciąg. Zawsze używaj WHERE A = 'East'.

Można sprawić, że zapytanie będzie interaktywne, pobierając wartość z komórki. Ponieważ wartość komórki znajduje się poza cudzysłowami, należy ją połączyć z zapytaniem za pomocą operatora &.

Jeśli komórka F1 zawiera nazwę regionu, ta formuła filtruje dane według wartości wpisanej przez użytkownika.

Trzeci argument informuje QUERY, ile wierszy nagłówków zawierają dane. Przekaż 1, gdy pierwszy wiersz zawiera etykiety takie jak Region i Sales.

Jeśli go pominiesz, QUERY spróbuje odgadnąć tę wartość, a czasami zrobi to nieprawidłowo: może połączyć nagłówek z wynikami albo potraktować liczby jako tekst. Jawne podanie wartości 1 zapewnia przewidywalne wyniki.

Sprawdź swoją znajomość podstaw QUERY.

Poznałeś najważniejsze informacje o funkcji QUERY w Google Sheets:

• QUERY(data, query, headers) to jej struktura
• SELECT wybiera kolumny według liter, a * oznacza wszystkie
• WHERE filtruje wiersze i można łączyć go z AND oraz OR
• Tekst zapisuje się w pojedynczych cudzysłowach, a liczby bez cudzysłowów
• & wstawia wartości komórek, umożliwiając tworzenie interaktywnych filtrów

W następnej części posortujesz i pogrupujesz te wyniki, aby utworzyć podsumowania.