Jeżeli potrzebujesz zarządzać danymi w Arkuszach Google, funkcja QUERY będzie niezwykle pomocna! Umożliwia ona zaawansowane przeszukiwanie danych w stylu baz danych w arkuszu kalkulacyjnym, co pozwala na skuteczne wyszukiwanie oraz filtrowanie informacji w różnych formatach. Poniżej przedstawimy, jak z niej korzystać.
Jak korzystać z funkcji QUERY
Opanowanie funkcji QUERY nie jest trudne, zwłaszcza jeśli masz doświadczenie z bazami danych i SQL. Struktura typowej formuły QUERY jest podobna do zapytań SQL, co przenosi funkcjonalności baz danych do Arkuszy Google.
Ogólny format formuły QUERY to = QUERY (zakres danych, zapytanie, nagłówki). W miejscu „zakres danych” możesz wpisać zakres komórek, na przykład „A2: D12” lub „A: D”, a w „zapytaniu” umieszczasz swoje zapytanie.
Opcjonalny parametr „nagłówki” pozwala ustawić liczbę wierszy nagłówka, które mają być uwzględnione na górze zakresu danych. Jeśli masz nagłówek rozciągający się na dwa wiersze, np. „Imię” w A1 i „Nazwisko” w A2, QUERY uwzględni te dwa wiersze jako nagłówki.
W poniższym przykładzie arkusz (nazywany „Lista pracowników”) w Arkuszach Google zawiera dane dotyczące pracowników, w tym ich imiona, nazwiska, numery identyfikacyjne, daty urodzenia oraz informacje o uczestnictwie w obowiązkowych szkoleniach.
Na kolejnym arkuszu możesz zastosować formułę QUERY, aby uzyskać listę wszystkich pracowników, którzy nie uczestniczyli w obowiązkowym szkoleniu. Lista ta będzie zawierać numery identyfikacyjne, imiona, nazwiska oraz informację o udziale w szkoleniu.
Aby to zrobić, użyj danych przedstawionych powyżej i wpisz formułę: = QUERY (’Lista pracowników’! A2: E12, „WYBIERZ A, B, C, E GDZIE E = 'Nie’”). Taki zapis zapyta o dane w zakresie od A2 do E12 w arkuszu „Lista pracowników”.
Podobnie jak w typowym zapytaniu SQL, funkcja QUERY określa kolumny do wyświetlenia (SELECT) i definiuje parametry wyszukiwania (WHERE). Zwraca kolumny A, B, C oraz E, przedstawiając listę wszystkich pasujących wierszy, gdzie wartość w kolumnie E („Szkolenie z udziałem”) jest równa „Nie”.
Jak pokazano powyżej, czterech pracowników z początkowej listy nie ukończyło szkolenia. Funkcja QUERY dostarczyła te informacje oraz odpowiednie kolumny, aby wyświetlić ich nazwiska i numery identyfikacyjne na oddzielnej liście.
W tym przykładzie użyto konkretnego zakresu danych. Możesz jednak zmodyfikować go, aby przeszukiwać cały zakres od A do E, co umożliwia dodawanie nowych pracowników do listy bez zmiany formuły. Użyta formuła QUERY automatycznie zaktualizuje się przy każdym dodaniu nowego pracownika lub gdy ktoś weźmie udział w sesji szkoleniowej.
Prawidłowy zapis formuły to = QUERY („Lista pracowników”! A2: E, „WYBIERZ A, B, C, E GDZIE E =„ Nie ””). Ta formuła pomija nagłówek „Pracownicy” w komórce A1.
Jeśli dodasz jedenastego pracownika, który nie brał udziału w szkoleniu, do początkowej listy (na przykład Christine Smith), formuła QUERY również się zaktualizuje, aby pokazać nowego pracownika.
Zaawansowane formuły QUERY
Funkcja QUERY jest niezwykle wszechstronna. Pozwala na stosowanie różnych operacji logicznych (takich jak AND i OR) oraz funkcji Google (takich jak COUNT) w ramach zapytań. Możesz również używać operatorów porównania (większe niż, mniejsze niż itd.), aby wyszukiwać wartości w określonym zakresie.
Użycie operatorów porównania z QUERY
Możesz wykorzystać QUERY z operatorami porównania (takimi jak mniejsze niż, większe niż lub równe), aby precyzyjnie przefiltrować dane. W tym celu dodamy nową kolumnę (F) do arkusza „Lista pracowników” z liczbą nagród zdobytych przez pracowników.
Za pomocą QUERY możemy znaleźć wszystkich pracowników, którzy zdobyli przynajmniej jedną nagrodę. Format tej formuły to = QUERY („Lista pracowników”! A2: F12, „WYBIERZ A, B, C, D, E, F GDZIE F > 0”).
Używając operatora porównania większe niż (>), wyszukujemy wartości przekraczające zero w kolumnie F.
W powyższym przykładzie funkcja QUERY zwróciła listę ośmiu pracowników, którzy zdobyli co najmniej jedną nagrodę. Spośród 11 pracowników trzech nigdy nie zdobyło żadnej nagrody.
Użycie AND i OR z QUERY
Zagnieżdżone funkcje operatorów logicznych, takich jak AND i OR, dobrze sprawdzają się w ramach bardziej złożonych formuł QUERY, pozwalając na dodanie wielu kryteriów wyszukiwania.
Przykładem użycia AND może być wyszukiwanie pracowników urodzonych w latach 1980-1989. W tym celu możemy wykorzystać naszą listę pracowników.
W tym przypadku użyjemy operatorów porównania, takich jak większe lub równe (> =) oraz mniejsze lub równe (<=). Format tej formuły to = QUERY („Lista pracowników”! A2: E12, „WYBIERZ A, B, C, D, E GDZIE D >= DATA '1980-01-01′ i D <= DATA '1989-12-31’”).
Jak pokazano powyżej, trzech pracowników urodzonych w latach 1980, 1986 i 1983 spełnia te wymagania.
Możesz również wykorzystać OR, aby uzyskać podobne wyniki. Używając tych samych danych, ale zmieniając daty oraz stosując OR, możemy wykluczyć wszystkich pracowników urodzonych w latach 80.
Format tej formuły to = QUERY (’Lista pracowników’! A2: E12, „WYBIERZ A, B, C, D, E GDZIE D <= DATA '1989-12-31′ lub D >= DATA '1990-01-01’”).
Z początkowych 10 pracowników trzech urodziło się w latach 80. Powyższy przykład przedstawia pozostałych siedmiu, którzy urodzili się przed lub po wykluczonych przez nas datach.
Użycie COUNT z QUERY
Nie tylko możesz wyszukiwać i zwracać dane, ale także łączyć QUERY z innymi funkcjami, takimi jak COUNT, aby manipulować danymi. Załóżmy, że chcemy usunąć niektórych pracowników z naszej listy, na podstawie ich uczestnictwa w szkoleniach.
Aby to zrobić, połącz QUERY z COUNT w ten sposób: = QUERY („Lista pracowników”! A2: E12, „WYBIERZ E, COUNT(E) GRUPUJ WEDŁUG E”).
Funkcja QUERY zlicza, ile razy każda wartość (np. „Tak” lub „Nie”) występuje w kolumnie E („Szkolenie z udziałem”). Na naszej liście sześciu pracowników ukończyło szkolenie, a czterech nie. Możesz modyfikować tę formułę, by używać jej z innymi funkcjami Google, takimi jak SUMA.
newsblog.pl