QUERY - die Leistungsfähigste Funktion der Google Tabellen
BUSINESS ANALYTIC ENTWICKLUNGSPROJEKT
Die QUERY - Funktion ist ein leistungsfähiges Google Tabellen-Tool, das eine Vielzahl von Aufgaben lösen kann: von der Neuanordnung von Spalten bis hin zu einem vollständigen Programm, das sich je nach Benutzereinstellungen dynamisch ändert.
QUERY - die Leistungsfähigste Funktion der Google Tabellen
BUSINESS ANALYTIC ENTWICKLUNGSPROJEKT
Die QUERY-Funktion ist ein leistungsfähiges Google Tabellen-Tool, das eine Vielzahl von Aufgaben lösen kann: von der Neuanordnung von Spalten bis hin zu einem vollständigen Programm, das sich je nach Benutzereinstellungen dynamisch ändert.
Arbeitsbeginn
Kurz gesagt, nimmt die QUERY-Funktion die Daten an, verarbeitet sie und gibt sie in einen Kasten aus.
Die Syntax der Funktion sieht folgendermaßen aus:
QUERY(Daten; Abfrage; [Header])
ARGUMENT
BESCHREIBUNG
Daten
Ein oder mehrere Datenbereiche, mit denen sie arbeiten werden, zum Beispiel:
“Tabelle1”!A1:D oder {“Tabelle1”!A1:D;“Tabelle2”!A1:D;“Tabelle3”!A1:D}
“Tabelle1”!A1:D oder {“Tabelle1”!A1:D;“Tabelle2”!A1:D;“Tabelle3”!A1:D}
Anfrage
Direkt "Programmcode" ist ein Satz von Bedingungen, unter denen die Datenausgabe erzeugt wird. Zum Beispiel:
“SELECT*” oder
“SELECT A WHERE C>0”
“SELECT*” oder
“SELECT A WHERE C>0”
Titel
(Optional)
(Optional)
Die Anzahl der Kopfzeilen am oberen Rand des Datenabschnitts. Wenn der Parameter weggelassen wird oder "-1" lautet, wird sein Wert automatisch basierend auf dem Inhalt der Daten berechnet.
Abfragen können wie folgt aussehen:
ANFRAGE
BESCHREIBUNG
select
Aufzählung der Felder, die von der Abfrage herausgegeben werden
where
Listet die Bedingungen auf, mit denen das von der Abfrage verarbeitete Daten Array gefiltert wird;
group by
Enthält eine Liste der Felder, nach denen das Ergebnis gruppiert werden soll
pivot
Erstellt Kreuztabellen mit dem Wert einer einzelnen Spalte als Spaltennamen in der finalen Tabelle
order by
Ergebnisse sortieren
limit
Grenzwert für die Anzahl der von einer Abfrage zurückgegebenen Zeilen
offset
Die Anzahl der ersten Zeilen, die von der Abfrage nicht verarbeitet werden sollen
label
Verantwortlich für den Namen der von der Abfrage zurückgegebenen Felder
format
Verantwortlich für das Format der ausgegebenen Daten
options
erweiterte Ausgabeoptionen
Öffnen
Öffnen Sie zunächst die Datei mit allen Beispielen über den Link und erstellen Sie eine Kopie der Datei :
Ein einfaches Beispiel mit einer Bedingung
Beispiel 1 : =QUERY('Tabelle1'!B2:F;"SELECT B WHERE C='Kleidung'")
Diese Funktion gibt wieder:
Ware
Jacke
Laufschuhe
Kurze Hose
Schirmmütze
Mit dem Bereich 'Tabelle1'!B2:F gibt die QUERY-Funktion die Spalte B wieder, wenn in Spalte C "Kleidung" angegeben ist.
Wenn die Daten aus dem aktuellen Arbeitsblatt stammen, kann der Bereich als B2:F angegeben werden.
Mit WHERE können Sie auch komplexere Abfragen erstellen, z. B.:
=QUERY({B2:F};"SELECT Col3, Col1, Col 5 WHERE(Col 5 >= 10000 AND Col 5 <= 20000) AND (Col3 = 'Müller' OR Col3 = 'Krüger')"; 1)
Beispiel 2: =QUERY('Tabelle5'!B2:F;"SELECT B WHERE C='Kleidung'";0)
Die Funktion gibt wieder:
Jacke
Sportschuhe
Kurze Hose
Kappe
Wir haben die Anzahl der Header-Zeilen hinzugefügt, nämlich 0, und sie werden jetzt nicht mehr angezeigt.
Externe Elemente zum Anpassen von QUERY
Beispiel 3:
=QUERY('Tabelle5'!B2:F;"SELECT B WHERE C='"&H2&"' ORDER BY F DESC"; 0)
Dieses Beispiel ist völlig gleich zu =QUERY('Tabelle5'!B2:F;"SELECT B WHERE C='Inventar' ORDER BY F"; 0) außer dass der Wert der Spalte C jetzt mit einer äußeren Zelle geändert werden kann, z. B. mit einer Dropdown-Liste. Es wurde auch eine ORDER BY - Abfrage hinzugefügt, die das Ergebnis nach Spalte F.
DESC sortiert - ein optionaler Parameter, der die Reihenfolge in die entgegengesetzte ändert.
Geschweifte Klammern, mehrere Bereiche und das Aufrufen von Spalten nach Zahlen
Beispiel 4: =QUERY({'Tabelle5'!B2:F};"SELECT Col2, Col1, Col 5"; 1)
Auf diese Weise haben wir die Reihenfolge der Spalten geändert und einige von ihnen entfernt. Dies ist für VLOOKUP-Funktionen erforderlich, die eine Suche nach der ersten Spalte eines Bereichs durchführen. Beachten Sie auch die geschweiften Klammern um den Bereich - sie ermöglichen es Ihnen, nicht nur mehrere Bereiche zu einem zu kombinieren, sondern auch die Spaltenreferenz nach der Sequenznummer zu verwenden. In unserem Fall ist Col1 B die erste Spalte des Bereichs.
Rechnungen in QUERY
Beispiel 5: =QUERY('Tabelle5'!B2:F11;"SELECT D, AVG(F), count(B) GROUP BY D")
So erfahren wir den durchschnittlichen Kaufpreis sowie die Anzahl der vom Käufer gekauften Artikel oder den minimalen und maximalen Preis in den Abschnitten "Kleidung" und "Inventar".
GROUP BY gruppiert die Werte in der ausgewählten Spalte und darüber hinaus gibt es Aggregationsfunktionen für GROUP BY:
FUNKTION
BESCHREIBUNG
UNTERSTÜTZTER DATENTYP
WIEDERGEGEBENER DATENTYP
avg()
Gibt den Durchschnittswert einer Gruppe wieder
Numerisch
Numerisch
count()
Gibt die Anzahl der Werte in einer Gruppe wieder
Jeder
Numerisch
max()
Gibt den maximalen Wert für eine Gruppe wieder
Jeder
Ähnlich zu dem Feld, auf das angewendet wurde
min()
Gibt den minimalen Wert für eine Gruppe wieder
Jeder
Ähnlich zu dem Feld, auf das angewendet wurde
sum()
Gibt den minimalen Wert für eine Gruppe wieder
Numerisch
Numerisch
Beispiel 6: =QUERY('Blatt 5'!B2:F11;"SELECT D, AVG(F), count(B) GROUP BY D
LABEL D 'Käufer', AVG(F) 'durchschnittlicher Preis', count(B) 'Anzahl der Namenl'
FORMAT avg(F) '0.00'")
Alles ist das gleiche wie im vorherigen Beispiel, aber mit Verbesserungen. LABEL, um die Titel umzubenennen und das Ergebnis mit FORMAT zu runden. So würde Beispiel 5 besser aussehen.
Ein Beispiel für erhöhte Komplexität. Mit & aus dem dritten Beispiel und mehreren Funktionen können wir dynamisch von außen ändern, welche Spalten die QUERY zurückgibt. Hier wird uns die Darstellung der Spalten in Form von Com1 helfen, wo du die Zahl von außen nehmen wirst.
In der grünen Zelle P2 geben wir die Spaltennummern durch ein Komma getrennt an, SPLIT in Q2 teilt sie in eine Reihe von Ziffern auf, MATCH in P3 sucht nach einer leeren Zelle und gibt die Spaltennummer wieder, was uns gepaart mit ADDRESS den Bereich der Werte gibt, die SPLIT zurückgegeben hat. Als nächstes macht ein JOIN in P4 eine Zeichenfolge wie "Col3, Col4, Col1", die in der QUERY verwendet wird. Die Zwischenformeln in P4 und P5 werden in die Abfrage eingefügt und erhalten Beispiel 7.
Und wenn Sie sich daran erinnern, dass eine Checkbox beliebige Werte wiedgeben kann (eine Zahl statt TRUE und nichts statt FALSE), können Sie SPLIT loswerden und die gewünschten Spalten mit Checkboxen markieren.
Beispiel 7: =QUERY({'Blatt 5'!B2:F11};"SELECT "&"Com"&JOIN(",Com";INDIRECT(ADDRESS(2;MATCH(P2;$A2:$2;-1)+1;1)&":"&ADDRESS(2;MATCH(;$A2:$2;-1);1))); 1)
Beispiel 8: =QUERY({B2:F11};"SELECT Col3, sum(Col 5) GROUP BY Col3 PIVOT Col2")
Col3 gibt (Namen) wieder, Col 5 summiert(Preis), Col3 gruppiert nach (Namen) und erstellt eine Kreuztabelle basierend auf Col2 (Produkttyp). Am Ende sehen wir, was genau und für welchen Betrag jeder Käufer erworben hat.
Beispiel 9: =QUERY({'Blatt 5'!B2:F11};"SELECT Col3, sum(Col 5)-(SUM(Col 5)/100*SUM(Col 4)) GROUP BY Col3 PIVOT Col2 LABEL sum(Col 5)-(SUM(Col 5)/100*SUM(Col4)) ''")
Das gleiche, aber unter Berücksichtigung des Rabatts. Wie Sie sehen können, können Sie in QUERY Berechnungen durchführen und nicht nur vorhandene Daten zurückgeben.
Daten
Auch in QUERY gibt es eine Reihe von Funktionen für die Arbeit mit Daten:
FUNKTION
BESCHREIBUNG
year()
Gibt die Jahrnummer aus «Datum» oder «Datum und Uhrzeit» zurück. Beispiel: year(date '2009-02-05') gibt 2009 zurück. Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
month()
Gibt die Monatsnummer aus "Datum» oder "Datum und Uhrzeit" wieder. Aber in diesem Fall wird Januar 0, Februar 1 und so weiter wiedergeben. Der Beginn der Zählung für die Monatsnummer ist 0. Beispiel: month(date '2009-02-05') gibt 1 zurück. Damit die Funktion die Monatsnummer in ihrer gewohnten Form zu ihrem Ergebnis wiedergeben, fügen Sie 1 hinzu, month (date "2009-02-05") + 1 gibt 2 zurück. Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
day()
Gibt die Nummer des Tages im Monat aus «Datum» oder «Datum und Uhrzeit» wieder. Beispiel: day(date '2009-02-05') gibt 5 zurück. Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
hour()
Gibt die Nummer der Stunde am Tag aus «Datum und Uhrzeit» oder «Uhrzeit» wieder. Beispiel: hour(time of day '12:03:17') ergibt 12. Angeforderte Parameter: Ein Parameter mit dem Typ Uhrzeit oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
minute()
Gibt die Anzahl der Minuten in einer Stunde aus "Datum und Uhrzeit» oder "Uhrzeit" wieder. Beispiel: minute(time of day '12:03:17') gibt 3 zurück. Angeforderte Parameter: Ein Parameter mit dem Typ Uhrzeit oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
second()
Gibt die Anzahl der Sekunden in einer Minute aus "Datum und Uhrzeit» oder "Uhrzeit" wieder. Beispiel: second(time of day '12:03:17') gibt 17 zurück. Angeforderte Parameter: Ein Parameter mit dem Typ Uhrzeit oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
millisecond()
Gibt die Millisekunden pro Sekunde aus "Datum und Uhrzeit» oder "Uhrzeit" wieder. Beispiel: millisecond(timeofday '12:03:17.123') gibt 123 wieder.
Angeforderte Parameter: Ein Parameter mit dem Typ Uhrzeit oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Angeforderte Parameter: Ein Parameter mit dem Typ Uhrzeit oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
quarter()
Gibt die Quartalsnummer eines Jahres aus "Datum und Uhrzeit» oder "Uhrzeit" wieder. Der Basiswert oder der Beginn des Countdowns ist 1. Für das erste Quartal gibt die Funktion den Wert 1 wieder, für das zweite 2 und so weiter. Beispiel: quarter(date '2009-02-05') gibt 1 zurück.
Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
dayOfWeek()
Gibt die Nummer des Wochentags in der Woche aus «Datum» oder «Datum und Uhrzeit» wieder. Der Beginn der Woche ist Sonntag, für Sonntag gibt die Funktion den Wert 1 zurück, für Montag 2 und so weiter. Beispiel: DayOfWeek(date '2015-11-10') gibt 3 wieder, da der 10. November 2015 ein Dienstag ist. Angeforderte Parameter: Ein Parameter mit dem Typ Datum oder Datum und Uhrzeit.
Die Wiedergabeart ist eine Zahl.
Die Wiedergabeart ist eine Zahl.
now()
Gibt das aktuelle Datum und die aktuelle Uhrzeit in der MESZ-Zeitzone wieder. Angeforderte Parameter: Keine Parametereingabe erforderlich.
Die Wiedergabeart ist Datum und Uhrzeit.
Die Wiedergabeart ist Datum und Uhrzeit.
dateDiff()
Gibt die Differenz in Tagen zwischen zwei Daten wieder. Beispiel: dateDiff(date '2008-03-13' , ' date '2008-02-12') 29 zurück, da 10. November 2015 Dienstag.
Angeforderte Parameter: Zwei Parameter vom Typ «Datum» oder «Datum und Uhrzeit».
Die Wiedergabeart ist eine Zahl.
Angeforderte Parameter: Zwei Parameter vom Typ «Datum» oder «Datum und Uhrzeit».
Die Wiedergabeart ist eine Zahl.
toDate
Gibt den in ein Datum konvertierten Wert aus "Datum" oder »Datum und Uhrzeit« oder »Zahl" wieder. Ein Beispiel:
- toDate(date '2008-03-13') gibt einen ähnlichen Wert im Datumsformat '2008-03-13' wieder
- to Date(DateTime'2013-03-13 11:19:22') gibt das Datum '2013-03-13' wieder
- toDate(1234567890000) gibt das Datum '2009-02-13' wieder
Die angeforderten Parameter sind ein einzelner Parameter mit dem Typ Datum, Datum und Uhrzeit oder Zahl.
Wiedergabeart: Datum.
- toDate(date '2008-03-13') gibt einen ähnlichen Wert im Datumsformat '2008-03-13' wieder
- to Date(DateTime'2013-03-13 11:19:22') gibt das Datum '2013-03-13' wieder
- toDate(1234567890000) gibt das Datum '2009-02-13' wieder
Die angeforderten Parameter sind ein einzelner Parameter mit dem Typ Datum, Datum und Uhrzeit oder Zahl.
Wiedergabeart: Datum.
upper()
Konvertiert alle Werte in einer Zeichenfolge in Großbuchstaben. Beispiel: upper( 'foo') gibt den String 'FOO' zurück. Angeforderte Parameter: Ein einzelner Parameter mit einem Text Datentyp.
Die Wiedergabeart ist Text.
Die Wiedergabeart ist Text.
lower()
Konvertiert alle Werte in einer Zeichenfolge in Kleinbuchstaben. Beispiel: upper( 'Bar') gibt den String 'bar' zurück. Angeforderte Parameter: Ein einzelner Parameter mit einem Text Datentyp.
Die Wiedergabeart ist Text.
Die Wiedergabeart ist Text.
Beispiel 10: =QUERY({'Blatt 5'!B2:G11};"SELECT dayOfWeek(Col1), Col4, Co2, Co 6 WHERE Col 6>8000"; 1)
Fügen Sie eine Spalte mit dem Datum hinzu und sehen Sie, an welchen Tagen der Woche die Menschen die teuersten Waren kaufen. DayOfWeek gibt die Nummer des Wochentags in der Woche aus «Datum» oder «Datum und Uhrzeit» wieder. Der Beginn der Woche ist Sonntag, für Sonntag gibt die Funktion den Wert 1 zurück, für Montag 2 und so weiter.
Am Ende sehen wir, dass die teuersten Einkäufe am Freitag und Samstag stattfinden. Und mit & aus dem dritten Beispiel können Sie die Daten für die ausgewählten Daten mithilfe der Dropdown-Listen mit den Zahlen von Monaten und Jahren anzeigen:
=QUERY({'Blatt 5'!B2:G11};"SELECT Col1, Col4, Co2, Co 6 WHERE month(Col1)="&Y21&" LABEL Col1 'nur für "&Y21+1&" Monat' "; 1)
QUERY und IMPORTRANGE
Beispiel 11: =QUERY({IMPORTRANGE("https://docs .google.
com/spreadsheets/d/1ZnWDQnGRLq...OE4y9OtrAUU6uWlxC8iOLDw/edit#gid=0";
"'"Tabelle5'!B2:G5")};"SELECT Col1, Col2, Col3")
Sie können IMPORTRANGE als Bereich verwenden, indem Sie Daten aus
eine andere Datei. IMPORTRANGE akzeptiert entweder einen vollständigen Verweis oder eine Datei-ID: =QUERY({IMPORTRANGE("1ZnWDQnGRLqJ1pIzYRkJTOE4y9OtrAUU6uWlxC8iOLDw";"'
Blatt5'!«2:G5")};"SELECT Col1, Col2, Col3")
Wie Sie sich erinnern können, können Sie mit geschweiften Klammern mehrere Bereiche verwenden.
Das gleiche gilt für IMPORTRANGE. Sie können mehrere Dateien angeben, indem Sie
mehrere IMPORTRANGE
=QUERY({IMPORTRANGE("ID";"Bereich");IMPORTRANGE("ID";"Bereich");IMPORTRANGE
("ID";"Bereich")};"SELECT Col1, Col2, Col3")
Wie Sie sehen können, ist die Abfrage mehr als nur eine Funktion. Es ist ein ganzes Programm, das sowohl sehr klein und einfach als auch riesig sein kann. Verwenden Sie im Formelfenster Strg+Eingabetaste, um die Zeilen zu durchbrechen und die Funktion lesbarer zu machen.
Wenn Sie Fragen haben und oder Hilfe benötigen, dann hinterlassen Sie Ihre Kontaktdaten und wir werden uns mit Ihnen schnellstmöglich in Verbindung setzen.
Wenn Sie Fragen haben und oder Hilfe benötigen, dann hinterlassen Sie Ihre Kontaktdaten und wir werden uns mit Ihnen schnellstmöglich in Verbindung setzen.