Met de QUERY-functie kunt u informatie uit een reeks of een volledig gegevensblad halen met behulp van flexibele query-opdrachten. Als u leert hoe u de QUERY-functie van Google Spreadsheets gebruikt, krijgt u toegang tot een krachtige opzoektool.
Als je ooit SQL-query's hebt geschreven om gegevens uit een database te halen, dan herken je de QUERY-functie. Als je geen database-ervaring hebt, is de QUERY-functie nog steeds heel gemakkelijk te leren.
Wat is de QUERY-functie?
De functie heeft drie hoofdparameters:
=QUERY(data, query, headers)
Deze parameters zijn vrij eenvoudig.
- Data: het cellenbereik dat de brongegevens bevat
- Query: Een zoekopdracht die beschrijft hoe u uit de brongegevens kunt halen wat u wilt
- Headers: een optioneel argument waarmee u meerdere koppen in het bronbereik kunt combineren tot één kop in het bestemmingsblad
De flexibiliteit en kracht van de QUERY-functie komt van het Query-argument, zoals je hieronder zult zien.
Een eenvoudige QUERY-formule maken
De QUERY-formule is vooral handig als u een zeer grote gegevensset hebt waaruit u gegevens moet extraheren en filteren.
In de volgende voorbeelden worden prestatiestatistieken van de Amerikaanse SAT middelbare school gebruikt. In dit eerste voorbeeld leert u hoe u een eenvoudige QUERY-formule schrijft die alle middelbare scholen en hun gegevens retourneert waarbij "New York" in de naam van de school staat.
-
Maak een nieuw blad voor het plaatsen van de queryresultaten. Typ in de cel linksboven =Query(. Wanneer u dit doet, ziet u een pop-upvenster met vereiste argumenten, een voorbeeld en nuttige informatie over de functie.
Image -
Vul vervolgens, ervan uitgaande dat u de brongegevens in Blad1 heeft, de functie als volgt in:
=Query(Blad1!A1:F460, "SELECT B, C, D, E, F WHERE B LIKE '%New York%'")
Deze formule bevat de volgende argumenten:
- Bereik van cellen: het gegevensbereik in A1 tot F460 in Blad1
- SELECT-instructie: Een SELECT-instructie die gegevens in kolommen B, C, D, E en F oproept, waarbij kolom B tekst bevat met het woord "New York " erin.
Image Het "%"-teken is een jokerteken dat u kunt gebruiken om te zoeken naar delen van tekenreeksen of getallen in een dataset. Als u "%" van de voorkant van de tekenreeks laat, wordt elke schoolnaam geretourneerd die begint met de tekst "New York".
-
Als je de naam van een exacte school uit de lijst wilt vinden, kun je de volgende vraag typen:
=Query(Blad1!A1:F460, "SELECTEER B, C, D, E, F WHERE B='New York Harbor High School'")
Het gebruik van de =operator vindt een exacte overeenkomst en kan worden gebruikt om overeenkomende tekst of getallen in elke kolom te vinden.
Image
Omdat de QUERY-functie van Google Spreadsheets heel gemakkelijk te begrijpen en te gebruiken is, kunt u alle gegevens uit elke grote gegevensset halen met behulp van eenvoudige query-instructies zoals die hierboven.
Gebruik de QUERY-functie met een vergelijkingsoperator
Met vergelijkingsoperators kunt u de QUERY-functie gebruiken om gegevens uit te filteren die niet aan een voorwaarde voldoen.
U hebt toegang tot alle volgende operators in een QUERY-functie:
- =: Waarden komen overeen met de zoekwaarde
- <: Waarden zijn kleiner dan de zoekwaarde
- >: Waarden zijn groter dan de zoekwaarde
- <=: Waarden zijn kleiner dan of gelijk aan de zoekwaarde
- >=: Waarden zijn groter dan of gelijk aan de zoekwaarde
- en !=: Zoekwaarde en bronwaarden zijn niet gelijk
Laten we, met dezelfde SAT-voorbeeldgegevensset hierboven, eens kijken hoe we kunnen zien welke scholen een gemiddeld wiskundegemiddelde van meer dan 500 punten hadden.
-
Vul in de cel linksboven op een leeg blad de QUERY-functie als volgt in:
=Query(Blad1!A1:F460, "SELECT B, C, D, E, F WHERE E > 500")
Deze formule roept alle gegevens op waarbij kolom E een waarde bevat die groter is dan 500.
Image -
Je kunt ook logische operatoren zoals AND en OR gebruiken om naar meerdere voorwaarden te zoeken. Als u bijvoorbeeld alleen scores wilt ophalen voor scholen met meer dan 600 testpersonen en een kritisch leesgemiddelde tussen 400 en 600, typt u de volgende QUERY-functie:
=Query(Blad1!A1:F460, "SELECTEER B, C, D, E, F WHERE C > 600 EN D > 400 EN D < 600")
Image - Vergelijking en logische operators bieden u veel verschillende manieren om gegevens uit een bronspreadsheet te halen. Ze laten je belangrijke stukjes informatie uit zelfs zeer grote datasets filteren.
Geavanceerd gebruik van de QUERY-functie
Er zijn een paar andere functies die u kunt toevoegen aan de QUERY-functie met enkele extra opdrachten. Met deze opdrachten kunt u waarden optellen, waarden tellen, gegevens bestellen en maximale waarden vinden.
-
Door GROUP in een QUERY-functie te gebruiken, kunt u waarden in meerdere rijen samenvoegen. U kunt bijvoorbeeld het gemiddelde maken van toetscijfers voor elke student met behulp van de GROEP-functie. Typ hiervoor:
=Query(Blad1!A1:B24, "SELECTEER A, AVG(B) GROEP DOOR A")
Image -
Als u AANTAL gebruikt in een QUERY-functie, kunt u het aantal scholen tellen met een gemiddelde schrijfscore van meer dan 500 met behulp van de volgende QUERY-functie:
=QUERY(Blad1!A2:F460, "SELECT B, COUNT (F) GROUP BY B")
Image -
Door ORDER BY in een QUERY-functie te gebruiken, kun je scholen vinden met maximale gemiddelde scores voor wiskunde en de lijst op die scores rangschikken.
=QUERY(Blad1!A2:F460, "SELECT B, MAX (E) GROEP OP B ORDER DOOR MAX(E)")
Image
