Jeśli potrzebujesz manipulować danymi w Arkuszach Google, funkcja QUERY może pomóc! Zapewnia zaawansowane wyszukiwanie w stylu bazy danych w arkuszu kalkulacyjnym, dzięki czemu możesz wyszukiwać i filtrować dane w dowolnym formacie. Pokażemy Ci, jak z niego korzystać.
Spis treści:
Korzystanie z funkcji QUERY
Funkcja QUERY nie jest zbyt trudna do opanowania, jeśli kiedykolwiek korzystałeś z bazy danych za pomocą SQL. Format typowej funkcji QUERY jest podobny do SQL i przenosi możliwości wyszukiwania bazy danych do Arkuszy Google.
Format formuły używającej funkcji QUERY to = QUERY (dane, zapytanie, nagłówki). Zastąpisz „dane” zakresem komórek (na przykład „A2: D12” lub „A: D”), a „zapytanie” swoim zapytaniem.
Opcjonalny argument „headers” ustawia liczbę wierszy nagłówka, które mają być uwzględnione u góry zakresu danych. Jeśli masz nagłówek, który rozciąga się na dwie komórki, na przykład „Pierwsza” w A1 i „Nazwa” w A2, oznaczałoby to, że QUERY użyje zawartości pierwszych dwóch wierszy jako połączonego nagłówka.
W poniższym przykładzie arkusz (zwany „Listą pracowników”) arkusza kalkulacyjnego Arkuszy Google zawiera listę pracowników. Zawiera ich imiona i nazwiska, numery identyfikacyjne pracowników, daty urodzenia i informacje, czy uczestniczyli w obowiązkowych szkoleniach pracowników.
Na drugim arkuszu możesz użyć wzoru QUERY, aby wyciągnąć listę wszystkich pracowników, którzy nie uczestniczyli w obowiązkowej sesji szkoleniowej. Ta lista będzie zawierała numery identyfikacyjne pracowników, imiona, nazwiska oraz informację, czy uczestniczyli w szkoleniu.
Aby to zrobić z danymi pokazanymi powyżej, możesz wpisać = QUERY (’Lista pracowników’! A2: E12, „WYBIERZ A, B, C, E GDZIE E = 'Nie’”). Spowoduje to zapytanie o dane z zakresu od A2 do E12 w arkuszu „Lista pracowników”.
Podobnie jak w przypadku typowego zapytania SQL, funkcja QUERY wybiera kolumny do wyświetlenia (SELECT) i identyfikuje parametry wyszukiwania (WHERE). Zwraca kolumny A, B, C i E, dostarczając listę wszystkich pasujących wierszy, w których wartość w kolumnie E („Szkolenie z udziałem”) jest ciągiem tekstowym zawierającym „Nie”.
Jak pokazano powyżej, czterech pracowników z początkowej listy nie uczestniczyło w szkoleniu. Funkcja QUERY dostarczyła te informacje, a także pasujące kolumny, aby wyświetlić ich nazwiska i numery identyfikacyjne pracowników na osobnej liście.
W tym przykładzie użyto bardzo określonego zakresu danych. Możesz to zmienić, aby przeszukiwać wszystkie dane w kolumnach od A do E. Umożliwiłoby to dalsze dodawanie nowych pracowników do listy. Użyta przez Ciebie formuła QUERY będzie również automatycznie aktualizowana za każdym razem, gdy dodasz nowych pracowników lub ktoś weźmie udział w sesji szkoleniowej.
Prawidłowa formuła to = QUERY („Lista pracowników”! A2: E, „Wybierz A, B, C, E GDZIE E =„ Nie ””). Ta formuła ignoruje początkowy tytuł „Pracownicy” w komórce A1.
Jeśli dodasz jedenastego pracownika, który nie brał udziału w szkoleniu, do listy początkowej, jak pokazano poniżej (Christine Smith), formuła QUERY również zostanie zaktualizowana i wyświetli nowego pracownika.
Zaawansowane formuły QUERY
Funkcja QUERY jest wszechstronna. Umożliwia korzystanie z innych operacji logicznych (takich jak AND i OR) lub funkcji Google (takich jak COUNT) w ramach wyszukiwania. Możesz także użyć operatorów porównania (większe niż, mniejsze niż itd.), Aby znaleźć wartości między dwiema cyframi.
Używanie operatorów porównania z QUERY
Możesz użyć QUERY z operatorami porównania (takimi jak mniejszy niż, większy niż lub równy), aby zawęzić i przefiltrować dane. W tym celu dodamy dodatkową kolumnę (F) do naszego arkusza „Lista pracowników” z liczbą nagród zdobytych przez każdego pracownika.
Za pomocą QUERY możemy wyszukać 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żywa operatora porównania większego niż (>) do wyszukiwania wartości powyżej zera w kolumnie F.
Powyższy przykład pokazuje, że funkcja QUERY zwróciła listę ośmiu pracowników, którzy zdobyli jedną lub więcej nagród. Spośród 11 pracowników trzech nigdy nie zdobyło żadnej nagrody.
Używanie AND i OR z QUERY
Zagnieżdżone funkcje operatorów logicznych, takie jak AND i OR, działają dobrze w ramach większej formuły QUERY, dodając wiele kryteriów wyszukiwania do formuły.
Dobrym sposobem sprawdzenia AND jest wyszukanie danych między dwiema datami. Jeśli użyjemy naszego przykładu listy pracowników, moglibyśmy wymienić wszystkich pracowników urodzonych w latach 1980-1989.
Wykorzystuje to również operatory porównania, takie jak większe lub równe (> =) i 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–1–1 ”i D
Jak pokazano powyżej, trzech pracowników urodzonych w latach 1980, 1986 i 1983 spełnia te wymagania.
Możesz również użyć OR, aby uzyskać podobne wyniki. Jeśli używamy tych samych danych, ale zmieniamy daty i używamy 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
Z pierwotnych 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żywanie COUNT z QUERY
Zamiast po prostu wyszukiwać i zwracać dane, możesz także łączyć QUERY z innymi funkcjami, takimi jak COUNT, aby manipulować danymi. Powiedzmy, że chcemy usunąć kilku pracowników z naszej listy, którzy uczestniczyli i nie uczestniczyli w obowiązkowych szkoleniach.
Aby to zrobić, możesz połączyć QUERY z COUNT w ten sposób = QUERY („Lista pracowników”! A2: E12, „WYBIERZ E, COUNT (E) grupa według E”).
Skupiając się na kolumnie E („Szkolenie z udziałem”), funkcja QUERY użyła funkcji COUNT do obliczenia, ile razy każdy typ wartości (ciąg tekstowy „Tak” lub „Nie”) został znaleziony. Z naszej listy sześciu pracowników ukończyło szkolenie, a czterech nie.
Możesz łatwo zmienić tę formułę i używać jej z innymi typami funkcji Google, takimi jak SUMA.