Google Sheets-Kochbuch: QUERY

Die QUERY-Funktion in Google Tabellen gehört mit zum mächtigsten Werkzeug, das Sheets zu bieten hat. Leider ist die Dokumentation - insbesondere die auf Deutsch - nicht sehr ausführlich, so dass die vielfältigen Vorzüge und Möglichkeiten etwas mühsam erschlossen werden müssen.

Wir werden als Beispiele auf dieser Seite mehrere kleine Tabellen verwenden. Sie können die Tabellen direkt aus der Seite herauskopieren und in Google Sheets einfügen, um die Aufrufe selbst nachvollziehen zu können. Unter Umständen müssen Sie dann noch die Formatierung ergänzen.

Beispieltabelle

Beispieltabelle - A1:E7

Nachname	Vorname	Alter	Geburtsort	Gehalt
Müller	Peter	34	München	2538,34
Sánchez Vicario	Cassandra	64	Wiesbaden	4389,42
Yilmaz	Joleen	18	Darmstadt	1138,89
Çelik	Martin	22	Berlin	1832,65
Rahman	Jasper	42	Leipzig	6896,26
Jones	Melisa	19	Dortmund	938,25

Folgende Aufgaben sollen erledigt werden:

• wir suchen alle Personen mit einem Alter von 34,
  • =QUERY(A1:E7;"select * where C = 34")
  • Gib alle (*) Spalten aus. Wähle die Zeilen, bei denen in Spalte C die Zahl 34 steht.
• alle Personen mit einem Gehalt unter 4.000,- €,
  • =QUERY(A1:E7;"select * where E < 4000")
  • Gib alle (*) Spalten aus. Wähle die Zeilen, bei denen in Spalte E eine Zahl kleiner 4000 steht.
• die Summe aller Gehälter über 3.000,- €,
  • =QUERY(A1:E7;"select sum(E) where E>3000 label sum(E) 'Summe der Gehälter über 3.000,- €'")
  • Gib die Summe der Spalte E aus, aber nur für die Zeilen, in denen in Spalte E eine Zahl größer 3000 steht. Benenne diese Spalte mit 'Summe der Gehälter über 3.000,- €'.
  • Strings im Query-String müssen ebenfalls in Hochkommata gesetzt werden.
• alle Personen, deren Vorname mit J anfängt.
  • =QUERY(A1:E7;"select A,B where lower(B) matches('j.*')")
  • gib die Spalten A und B aus, wenn der in Kleinbuchstaben umgewandelte Inhalt von B mit dem regulären Ausdruck 'j.*' übereinpassst.
  • Reguläre Ausdrücke sind als Strings zu übergeben, müssen also wieder in Hochkommata eingefasst werden.

Als weiteres Beispiel soll die Verwendung von QUERY statt VLOOKUP dienen, wenn es mehrere Ergebnisse gibt und diese alle in eine Zelle ausgegeben werden sollen:

• =JOIN(", ";TRANSPOSE(QUERY(Andere-Seite!A$4:G$41;"select A where G matches '"&A4&"' ")))
  • Wir suchen auf der Tabellenseite "Andere-Seite" im Bereich A$4:G$41 in der Spalte G nach dem Wert der Zelle A4.
  • Mit TRANSPOSE wird bei mehreren Ergebnissen aus dem Spaltenvektor ein Zeilenvektor.
  • JOIN fasst die Ergebnisse in einer Zelle zusammen.

Das war jetzt ziemlich viel auf einmal, mehr Optionen werden etwas systematischer im folgenden erklärt. Um ein besseres Gefühl für QUERY zu erhalten, werden wir zunächst die Ausgabe besprechen. Dabei wollen wir eine Auswahl von Spalten in anderer Reihenfolge und mit neuen Überschriften ausgeben.

Syntax

=QUERY(Daten; Abfrage; [Überschriften])

• in Daten gibt man den Datenbereich für die Abfrage an. Das kann ein Zellbereich auf der gleichen Seite, auf einer anderen Seite oder auch in einer anderen Tabelle sein. Auch mehrere Datenbereiche können gleichzeitig abgefragt werden.
• Die ganze Arbeit geschieht in der Abfrage. Die Abfragesprache (Google Visualization API) erinnert stark an SQL, ist daher für Menschen, die keine Erfahrung mit SQL haben, etwas unübersichtlich. Die Abfrage muss als String (Zeichenkette) angegeben, also in Hochkommata eingeschlossen werden.
• Als dritten Parameter kann man angeben, ob die Daten im Datenbereich eine oder mehrere Überschriftszeilen haben. Google versucht die Zahl der Überschriftszeilen automatisch zu erkennen, auch wenn Sie nichts angeben, wird in unserem Beispiel eine Überschriftszeile erkannt.

Die Ausgabe der Funktion kann sein:

• ein einzelner Wert (Zahl, Zeichenkette, Boolscher-Wert).
• ein Datenbereich, also mehrere Zeilen, Spalten, oder ein kombinierter Bereich mit mehreren Zeilen und Spalten.

Schlüsselworte und ihre Reihenfolge

Die Schlüsselworte (clauses) müssen in der Abfrage in einer bestimmten Reihenfolge angegeben werden. Alle Schlüsselworte sind optional, erfordern dann aber unter Umständen verpflichtend Parameter.

1. select wählt Spalten aus, man kann auch Funktionen für Spalten ausführen lassen und das Ergebnis der Funktion ausgeben
2. where liefert nur Zeilen zurück, für die bestimmte Bedingungen erfüllt sind
3. group by fasst Werte zusammen (z.B. alle Zeilen, in denen in einer Spalte ein bestimmter Wert steht)
4. pivot vertauscht bei der Ausgabe von manchen Abfragen Zeilen und Spalten, erfordert Gruppierung oder Zusammenfassung
5. order by sortiert die Ausgabe
6. limit beschränkt die Anzahl der zurückgegebenen Zeilen
7. offset überspringt die angegebene Anzahl von Zeilen
8. label Angabe von Spaltenüberschriten
9. format formatiert die Ausgabe
10. options zusätzliche Optionen, in der Tabellenkalkulation habe ich das noch nicht gebraucht

Datenausgabe

• =QUERY(A1:E7)
  • liefert 1 zu 1 die orginale Tabelle zurück. Wenn nicht genügend Platz für die Ausgabe der Tabelle ist, weil angrenzende Zellen bereits Werte enthalten, erhält man eine Fehlermeldung #REF! mit dem Hinweis: "Fehler Array-Ergebnis wurde nicht erweitert, da dadurch Daten in "Zelle" überschrieben würden."
• =QUERY(A1:E7;"select C")
  • selektiert die Spalte C und gibt diese aus
  • Die Ausgabe ist die Spalte C des Datenbereiches einschließlich der Überschrift. Man muss hier immer die tatsächliche Spaltenbezeichnung angeben, nicht die relative Position wie z.B. bei sverweis().
  • select * wählt alle Spalten aus.
• =QUERY(A1:E7;"select C,B,A")
  • wählt die Spalten C, B und A
  • gibt die Spalten in der angegebenen Reihenfolge - hier also C, dann B, dann A - aus.
• =QUERY(A1:E7;"select A,B,E-800 label (E-800) 'über Mindestlohn'";1)
  • Hier wird eine neue Spalte in der Ausgabe erzeugt, die den Wert der Spalte E minus der Zahl 800 berechnet.
  • Die neue Spalte (E-800) soll mit "über Mindestlohn" überschrieben werden (label).

Datenausgabe beschränken

• =QUERY(A1:E7;"select C,B,A limit 1")
  • Beschränkt die Ausgabe auf das erste gefundene Ergebnis, hier also die erste Zeile der Daten und die Überschrift der Daten.
  • Es werden also zwei Zeilen angezeigt - das mag erst einmal überraschen. Man muss die Ausgabe der Überschrift unterdrücken, oder die Auswahl ohne die Überschrift vornehmen (von A2:E7).
• =QUERY(A1:E7;"select C,B,A limit 1 label C '', B '', A ''")
  • Beschränkt die Ausgabe auf eine Zeile, und gibt das Ergebnis ohne Überschrift aus. Die Ausgabe besteht hier also aus nur einer Zeile.
  • Mit dem Schlüsselwort label können neue Spaltenüberschriften vergeben werden. Diese sind hier leer (bestehen aus einem leeren String). Da die doppelten Hochkommata bereits verwendet wurden, müssen Strings innerhalb von Strings in einfache Hochkommata eingeschlossen werden.

Neue Spaltenüberschriften vergeben

• =QUERY(A1:E7;"select B,A,C label A 'Zuname'")
  • Hier wird die Spaltenüberschrift der Spalte A durch die Zeichenkette 'Zuname' ersetzt.

Suchen bestimmter Einträge

Zahlen

• =QUERY(A1:E7;"select B,A,C where C = 34")
  • Findet alle Personen mit einem Alter von 34 Jahren. Wir haben im Beispiel zwar nur eine Person, es würden aber alle Personen gefunden und aufgelistet.
• =QUERY(A1:E7;"select B,A,C where C != 34")
  • Findet alle Personen mit einem Alter ungleich 34. Das != bedeutet 'nicht gleich'.
• =QUERY(A1:E7;"select B,A,C where E <4000")
  • Findet alle Personen mit einem Gehalt unter 4.000,- €. Genauso funktionieren <, <=, >=, <> (kleiner, kleiner gleich, größer gleich, ungleich).
  • Die Spalte in der gesucht wird, muss in der Ausgabe nicht eingeschlossen sein.

Zeichenketten

• =QUERY(A1:E7;"select B,A,C where B matches 'Melisa'")
  • Für Zeichenketten kann man matches verwenden, diese müssen dann in Hochkommata eingeschlossen sei. Hier werden alle 'Melisa' gefunden, keine 'melisa', auch keine Melisa, vor deren Namen ein Leerzeichen steht, auch keine 'Melisan'.
• =QUERY(A1:E7;"select B,A,C where B matches '"&G10&"'")
  • Statt einer Zeichenkette als Parameter können Zellbezüge angegeben werden, hier die Zelle G10. Das sieht nicht so schön aus, da hier eine Zeichenkette "zusammengebastelt" werden muss, daher die Abfolge ' " & ... & " '. Das ist aber toll, da ich mir dann ein Suchfeld in der Tabelle definieren kann, und Anwender nur das Suchfeld ändern müssen.
  • Hier suche ich nach einer Zeichenkette. Will ich nach einer Zahl suchen und einen Zellverweis verwenden, darf diese Zahl nicht als String gekennzeichnet werden.
• =QUERY(A1:E7;"select B,A,C where C matches "&G10&"")
  • Hier suche ich mit "matches" nach einer Zahl, die in Zelle "G10" steht.

matches erwartet einen regulären Ausdruck, sucht aber nicht global. Das bedeutet, dass der reguläre Ausdruck "Melisa" nur den Namen "Melisa" findet, nicht "Melisan" oder "Ich bin Melisa". Die REGEX-Ausdrücke dagegen suchen global.

Hier sollen nur einige wichtige Beispiele angegeben werden:

• =QUERY(A1:E7;"select B,A,C where B matches ('Melisa.*')")
  • findet "Melisa", "Melisan", und alle anderen Zeichenketten, die mit "Melisa" anfangen.
  • der Punkt steht für ein beliebiges Zeichen, der Stern für beliebig viele Vorkommen. Ein einzelner Punkt bedeutet also: hier muss ein Zeichen sein. Ein Punkt gefolgt von einem Stern bedeutet: hier kann kein, ein oder beliebig viele beliebige Zeichen sein.
  • Das entspricht dem REGEX-Ausdruck '^Melisa.*' Das Dach kennzeichnet den Text- oder Zeilenanfang.
• matches ('.*Melisa.*')
  • findet alle Zeichenketten, die irgendwo "Melisa" enthalten.
  • Das entspricht der globalen Suche mit den REGEX-Ausdrücken.
• matches ('.*"&A5&".*')
  • findet alle Zeichenketten, die irgendwo die Zeichenkette enthalten die in Zelle A5 steht.
• matches ('[m,M]elisa')
  • findet "Melisa" und "melisa", die eckigen Klammern stehen für eine Zeichenklasse.
• =QUERY(A1:E7;"select B,A,C where lower(B) matches 'melisa' ")

Neben matches gibt es einige weitere Suchoptionen:
contains, starts with, ends with und like. (Dokumentation zu where)

• contains('Melisa')
  • findet alle Zeichenketten, die irgendwo "Melisa" enthalten.
  • Das entspricht der globalen Suche mit den REGEX-Ausdrücken.
• like('%Melisa')
  • Alle Zeichenketten bei denen vor Melisa etwas stehen kann und hinter "Melisa" nichts mehr folgt.
• like('Melisa_')
  • Alle Zeichenketten bei denen hinter "Melisa" genau ein Zeichen folgt, auch ein Leerzeichen.

Suchen mit logischen Ausdrücken (und/oder/nicht)

Suchausdrücke können kombiniert werden.

• =QUERY(A1:E7;"select B,A,C where B matches 'Melisa' and A matches 'Jones' ")
  • and ist die und-Verknüpfung. Hier wird also nach allen "Melisa" gesucht, deren Nachname "Jones" ist.

Bei komplizierteren logischen Ausdrücken ist es sehr hilfreich mit Klammern zu arbeiten, damit man mit den Vorrangregeln nicht durcheinander kommt.

• =QUERY(A1:E7;"select B,A,C where (B matches 'Melisa' or B matches 'Peter') and A matches 'Jones' ")
  • Hier finden wir alle "Melisa" und alle "Peter" (entweder "Melisa" oder "Peter"), deren Nachname "Jones" ist.
• =QUERY(A1:E7;"select B,A,C where (B matches 'Peter' and D matches 'München') or E >4000")
  • Wir finden alle Personen die Peter heißen und in München geboren sind, oder die ein Gehalt über 4.000,- € haben. Dabei ist das "oder" nicht ausschließend, Peter aus München wird auch gefunden, wenn er ein Gehalt über 4.000,- € hat.

Suchen nach Kalenderdaten

Beispieltabelle Buchhaltung

Datum	Betrag	Kategorie	Startdatum	Enddatum
01.01.2024	2.435,00 €	Gehalt	01.05.2024	31.12.2024
15.02.2024	-87,37 €	Restaurantbesuche
13.05.2024	-56,48 €	Benzin
05.08.2024	-13,47 €	Restaurantbesuche

Das Suchen nach Kalenderdaten ist ein bisschen "tricky", da QUERY das Datum im Format 'YYYY-MM-DD' hinter dem Schlüsselwort date erwartet. Wir bauen eine Abfrage, die so aussieht wie die folgende:

• DatumsSpalte > date '2024-03-28'

Hier für alle Daten, die größer sind als der 28.03.2024. Idealerweise steht das Abfragedatum in einer anderen Zelle, hat aber dann vermutlich ein anderes Format. Das Umformatieren geht wie folgt:

• =QUERY(A$2:C;"select * where (A>date '"&TEXT(DATEVALUE(D2);"yyyy-mm-dd")&"') ")
  • Wir suchen im Bereich A2:C.
  • Ausgegeben werden alle Einträge (*) in denen in der Spalte A ein Datum steht, das größer als das in der Zelle D2 ist.
  • Das Datum in der Zelle D2 wird zunächst mit DATEVALUE() in eine Zahl umgewandelt und anschließend mit TEXT() in das passende Format gebracht.

Häufig suchen wir vermutlich nach größer gleich, und mit einem Startddatum und einem Enddatum.

• =QUERY(A$2:C;"select * where ((A>=date '"&TEXT(DATEVALUE(D2);"yyyy-mm-dd")&"') and (A<=date '"&TEXT(DATEVALUE(E2);"yyyy-mm-dd")&"')) ")
  • Das Startdatum steht hier in der Zelle D2, das Enddatum in der Zelle E2.

Ausgaben verändern

Gruppieren (Aggregieren)

Hier geht es um das Zusammenfassen von Zeilen. Das ist immer dann nützlich, wenn wir mehrere Zeilen mit dem gleichen Wert haben. Z.B.: wie oft kommt der Vorname "Peter" vor?

Wir schauen uns ein neues Beispiel an:

Gezahlte Provisionen - A1:C8

Nachname	Vorname	Provision
Müller	Peter	1000
Müller	Peter	1500
Sánchez Vicario	Cassandra	2100
Yilmaz	Joleen	600
Müller	Peter	1800
Rahman	Joleen	700
Müller	Hugo	1200

• =QUERY(A1:C8;"select B,count(B) group by B")
  • Wir erstellen eine Abfrage über Spalte B und die Anzahl der Vorkommen der Elemente. Aufgelistet werden dann die Vornamen und die Anzahl ihrer Vorkommen.

• =QUERY(A1:C8;"select A,B,sum(C) group by A,B order by sum(C) asc label sum(C) 'Provisionssumme'")
  • Hier werden der Nachname, der Vorname und die Summe der Provisionen ausgegeben. Dabei wird die Provisionssumme dort zusammengerechnet, wo Spalte A und Spalte B gleich sind. Die Ausgabe ist aufsteigend sortiert nach Summe der Provisionen, und die Spalte wird mit "Provisionssumme" überschrieben.
  • Anmerkung: im wirklichen Leben würde ich das nie so machen, man braucht einen eindeutigen Schlüssel. Die Kombination Vorname/Nachname ist nicht eindeutig.

Ausgabe: Gezahlte Provisionen gesamt

Nachname	Vorname	Provisionssumme
Yilmaz	Joleen	600,00 €
Rahman	Joleen	700,00 €
Müller	Hugo	1.200,00 €
Sánchez Vicario	Cassandra	2.100,00 €
Müller	Peter	4.300,00 €

• =QUERY(A1:C8;"select sum(C),avg(C) label sum(C) 'Summe der Provisionen', avg(C) 'Durchschnitt der Provisionen'")
  • Hier lassen wir uns die Summe und den Durchschnitt der gezahlten Provisionen berechnen.

Sortieren

• =QUERY(A1:C8;"select * order by A")
  • Gibt die Tabelle nach Spalte A aufsteigend sortiert aus.
• =QUERY(A1:C8;"select * order by A asc")
  • Macht genau das gleiche wie das erste Beispiel, da voreingestellt aufsteigend sortiert wird.
• =QUERY(A1:C8;"select * order by C desc")
  • Hier wird die Tabelle absteigend nach Spalte C sortiert.
• =QUERY(A1:C8;"select * order by B desc, C asc")
  • Tabellen können nach mehreren Kriterien sortiert werden, hier zuerst nach Spalte B absteigend, dann nach Spalte C aufsteigend.

Datenbereinigung / Abfangen von leeren Ausgaben

Nehmen wir an, wir hätten nicht so ordentliche Daten wie in den vorhergehenden Beispielen.

• =QUERY(A1:D14;"select * where (A is not null) and (B is not null)")
  • Hier nehmen wir nur die Zeilen in unsere Abfrage auf, in denen weder die Zeile A, noch die Zeile B leer (=not null) sind.

Arbeiten mit Datum und Uhrzeit

Datum und Uhrzeit müssen in bestimmter Form im QUERY-Aufruf verwendet werden.

• Das Datum erscheint als String im Format "YYYY-MM-DD", also Jahr, Monat, Tag. Mit dem Operator "date" wird der String in ein Kalenderdatum umgewandelt.
• Die Uhrzeit kann als "timeofday" im Format "HH:mm:ss[.SSS]" angegeben werden, also Stunde, Minuten, Sekunde, Millisekunde. Die zweite Möglichkeit ist "datetime", das ist eine Kombination von Datum und Uhrzeit im Format "YYYY-MM-DD HH:mm:ss[.sss]".

Arbeitszeiten - A1:E10

Datum	Checkin	Checkout	Name	Vorname
03.02.2022	08:15	16:25	Müller	Peter
03.02.2022	07:30	15:20	Sánchez Vicario	Cassandra
03.02.2022	07:30	18:10	Yilmaz	Joleen
04.02.2022	08:15	16:25	Müller	Peter
04.02.2022	07:30	16:10	Sánchez Vicario	Cassandra
04.02.2022	07:40	17:20	Yilmaz	Joleen
05.02.2022	08:00	16:00	Müller	Peter
05.02.2022	08:15	17:10	Sánchez Vicario	Cassandra
05.02.2022	07:45	16:00	Yilmaz	Joleen

• =QUERY(A1:E10;"select * where A > date '2022-02-03' ";1)
  • In Spalte A steht das Datum. Hier filtern wir nach allen Daten, die größer sind als der 03.02.2022 (also ab dem 04.02.2022).
• =QUERY(A1:E10;"select A,D,E where C > timeofday '16:00:00'";1)
  • Wer geht nach 16:00 Uhr nach Hause, und an welchen Tagen?
• =QUERY(A1:E10;"select D,E,count(E) where C > timeofday '16:00:00' group by D,E order by count(E) desc label count(E) 'Tage nach 16:00 Uhr'";1)
  • Wer ist wie häufig nach 16:00 Uhr nach Hause gegangen? Die Ausgabe hätten wir gerne absteigend sortiert.

Verschachtelte Querys

Wem die Möglichkeiten eines Querys noch nicht ausreichen, kann die Querys auch schachteln. Dabei entstehen aus dem ersten Query neue Ausgabespalten, diese sind allerdings virtuell, haben also keinen Spaltenbuchstaben. Stattdessen heißen die Spalten jetzt 'Col1', 'Col2' usw.

Wir nehmen folgenden Query:

• =QUERY(A1:C8;"select * where A matches 'Müller' and B matches 'Peter'")
  • Wir suchen aus der Provisionstabelle von oben alle Zeilen mit Peter Müller raus.

Jetzt wollen wir die Summe und den Durchschnitt der Provisionen von Peter Müller berechnen.

• =QUERY(QUERY(A1:C8;"select * where A matches 'Müller' and B matches 'Peter'");"select sum(Col3), avg(Col3)")
  • In der vom ersten Query ausgegebenen Tabelle stehen die Provisionen in Spalte drei, also 'Col3'. Von dieser berechnen wir die Summe und den Durchschnitt.

Todo

• Abfragen mehrerer Datenbereiche
• Weitere Beispiele für's Aggregieren
• Zeichenkettenmanipulationen
• Ersatz von sverweis und wverweis (hlookup und vlookup)
• Ersatz von xverweis
• Vergleichen von Tabellen

Die Beispiele auf dieser Seite wurden getestet: 02/2022