Funkcja QUERY umożliwia pobieranie informacji z zakresu lub całego arkusza danych za pomocą elastycznych poleceń zapytań. Dowiedz się, jak korzystać z funkcji QUERY Arkuszy Google, aby uzyskać dostęp do zaawansowanego narzędzia do wyszukiwania.
Jeśli kiedykolwiek pisałeś zapytania SQL, aby pobrać dane z bazy danych, rozpoznasz funkcję QUERY. Jeśli nie masz doświadczenia w zakresie baz danych, funkcja QUERY jest nadal bardzo łatwa do nauczenia.
Co to jest funkcja QUERY?
Funkcja ma trzy główne parametry:
=ZAPYTANIE(dane, zapytanie, nagłówki)
Te parametry są dość proste.
- Dane: Zakres komórek zawierających dane źródłowe
- Query: Wyrażenie wyszukiwania opisujące, jak wydobyć to, czego chcesz z danych źródłowych
- Nagłówki: Opcjonalny argument, który pozwala połączyć wiele nagłówków z zakresu źródłowego w jeden nagłówek w arkuszu docelowym
Elastyczność i moc funkcji QUERY pochodzi z argumentu Query, jak zobaczysz poniżej.
Jak utworzyć prostą formułę ZAPYTANIA
Formuła QUERY jest szczególnie przydatna, gdy masz bardzo duży zestaw danych, z którego musisz wyodrębnić i przefiltrować dane.
W poniższych przykładach wykorzystano statystyki dotyczące wyników w liceum SAT w USA. W tym pierwszym przykładzie dowiesz się, jak napisać prostą formułę QUERY, która zwraca wszystkie szkoły średnie i ich dane, w których w nazwie szkoły znajduje się „Nowy Jork”.
-
Utwórz nowy arkusz do umieszczania wyników zapytania. W lewej górnej komórce wpisz =Zapytanie(. Gdy to zrobisz, zobaczysz wyskakujące okienko z wymaganymi argumentami, przykładem i pomocnymi informacjami o funkcji.
Image -
Następnie, zakładając, że masz dane źródłowe w Arkuszu1, wypełnij funkcję w następujący sposób:
=Query(Arkusz1!A1:F460, "WYBIERZ B, C, D, E, F GDZIE B LIKE '%New York%'")
Ta formuła zawiera następujące argumenty:
- Zakres komórek: Zakres danych od A1 do F460 w Arkuszu1
- Wyrażenie SELECT: Instrukcja SELECT, która odwołuje się do dowolnych danych w kolumnach B, C, D, E i F, gdzie kolumna B zawiera tekst zawierający słowo „Nowy Jork " w nim.
Image Znak "%" to symbol wieloznaczny, którego można użyć do wyszukiwania części ciągów lub liczb w dowolnym zestawie danych. Pozostawienie „%” na początku ciągu spowoduje zwrócenie dowolnej nazwy szkoły, która zaczyna się od tekstu „Nowy Jork”.
-
Jeśli chcesz znaleźć dokładną nazwę szkoły z listy, możesz wpisać zapytanie:
=Query(Arkusz1!A1:F460, "SELECT B, C, D, E, F WHERE B='New York Harbor High School'")
Użycie operatora =umożliwia znalezienie dokładnego dopasowania i może być użyte do znalezienia pasującego tekstu lub liczb w dowolnej kolumnie.
Image
Ponieważ funkcja QUERY w Arkuszach Google jest bardzo łatwa do zrozumienia i używania, możesz pobrać dowolne dane z dowolnego dużego zestawu danych, używając prostych zapytań, takich jak te powyżej.
Użyj funkcji QUERY z operatorem porównania
Operatory porównania umożliwiają użycie funkcji QUERY do odfiltrowania danych, które nie spełniają warunku.
Masz dostęp do wszystkich następujących operatorów w funkcji QUERY:
- =: Wartości pasują do szukanej wartości
- <: Wartości są mniejsze niż wartość wyszukiwania
- >: Wartości są większe niż wartość wyszukiwania
- <=: Wartości są mniejsze lub równe wartości wyszukiwania
- >=: Wartości są większe lub równe wartości wyszukiwania
- i !=: Wartość wyszukiwania i wartości źródłowe nie są równe
Używając tego samego przykładowego zestawu danych SAT powyżej, przyjrzyjmy się, jak sprawdzić, które szkoły mają średnią średnią matematyczną powyżej 500 punktów.
-
W lewej górnej komórce pustego arkusza wypełnij funkcję ZAPYTANIE w następujący sposób:
=Zapytanie(Arkusz1!A1:F460, "WYBIERZ B, C, D, E, F GDZIE E > 500")
Ta formuła wywołuje wszelkie dane, w których kolumna E zawiera wartość większą niż 500.
Image -
Możesz również użyć operatorów logicznych, takich jak AND i OR, aby wyszukać wiele warunków. Na przykład, aby uzyskać wyniki tylko dla szkół z ponad 600 osobami przystępującymi do testu i krytyczną średnią czytania między 400 a 600, należy wpisać następującą funkcję QUERY:
=Zapytanie(Arkusz1!A1:F460, "WYBIERZ B, C, D, E, F GDZIE C > 600 I D > 400 I D < 600")
Image - Operatory porównania i logiczne zapewniają wiele różnych sposobów pobierania danych ze źródłowego arkusza kalkulacyjnego. Pozwalają odfiltrować ważne informacje z nawet bardzo dużych zbiorów danych.
Zaawansowane zastosowania funkcji QUERY
Istnieje kilka innych funkcji, które możesz dodać do funkcji QUERY za pomocą dodatkowych poleceń. Te polecenia umożliwiają agregowanie wartości, liczenie wartości, porządkowanie danych i znajdowanie wartości maksymalnych.
-
Użycie GROUP w funkcji QUERY umożliwia agregowanie wartości w wielu wierszach. Na przykład możesz uśrednić oceny z testu dla każdego ucznia za pomocą funkcji GRUPA. Aby to zrobić, wpisz:
=Zapytanie(Arkusz1!A1:B24, "WYBIERZ A, ŚREDNIA(B) GRUPA WEDŁUG A")
Image -
Używając COUNT w funkcji QUERY, możesz policzyć liczbę szkół ze średnim wynikiem zapisu powyżej 500, używając następującej funkcji QUERY:
=ZAPYTANIE(Arkusz1!A2:F460, "WYBIERZ B, LICZBA (F) GRUPUJ WEDŁUG B")
Image -
Używając ORDER BY w funkcji QUERY, możesz znaleźć szkoły z maksymalnymi średnimi wynikami matematycznymi i uporządkować listę według tych wyników.
=QUERY(Arkusz1!A2:F460, "WYBIERZ B, MAX (E) GRUPUJ WEDŁUG B ZAMÓWIENIA WG MAX(E)")
Image
