Als u gegevens in Google Spreadsheets moet manipuleren, kan de QUERY-functie helpen! Het biedt krachtige zoekfuncties in databasestijl in uw spreadsheet, zodat u uw gegevens kunt opzoeken en filteren in elk gewenst formaat. We laten u zien hoe u het kunt gebruiken.
De QUERY-functie gebruiken
De QUERY-functie is niet zo moeilijk om onder de knie te krijgen als je ooit met een database hebt gewerkt met behulp van SQL. Het formaat van een typische QUERY-functie is vergelijkbaar met SQL en brengt de kracht van databasezoekopdrachten naar Google Spreadsheets.
De indeling van een formule die de functie QUERY gebruikt, is =QUERY(data, query, headers). U vervangt “gegevens” door uw celbereik (bijvoorbeeld “A2: D12” of “A: D”) en “zoekopdracht” door uw zoekopdracht.
Het optionele argument ‘headers’ stelt het aantal koptekstrijen in dat bovenaan uw gegevensbereik moet worden opgenomen. Als u een koptekst heeft die zich over twee cellen verspreidt, zoals “Eerste” in A1 en “Naam” in A2, zou dit specificeren dat QUERY de inhoud van de eerste twee rijen als de gecombineerde koptekst gebruikt.
In het onderstaande voorbeeld bevat een blad (genaamd ‘Personeelslijst’) van een Google Spreadsheets-spreadsheet een lijst met werknemers. Het bevat hun namen, werknemers-ID-nummers, geboortedata en of ze de verplichte trainingssessie voor werknemers hebben bijgewoond.
Op een tweede blad kunt u een QUERY-formule gebruiken om een lijst op te vragen van alle werknemers die de verplichte training niet hebben bijgewoond. Deze lijst bevat ID-nummers van werknemers, voornamen, achternaam en of ze de trainingssessie hebben bijgewoond.
Om dit te doen met de bovenstaande gegevens, kunt u typen =QUERY('Staff List'!A2:E12, "SELECT A, B, C, E WHERE E = 'No'"). Dit vraagt de gegevens van het bereik A2 tot E12 op het blad “Personeelslijst”.
Net als een typische SQL-query, selecteert de functie QUERY de kolommen die moeten worden weergegeven (SELECT) en identificeert de parameters voor de zoekopdracht (WHERE). Het retourneert kolommen A, B, C en E, en geeft een lijst met alle overeenkomende rijen waarin de waarde in kolom E (“Bijgewoonde training”) een tekstreeks is die “Nee” bevat.
Zoals hierboven is aangetoond, hebben vier medewerkers van de oorspronkelijke lijst geen training bijgewoond. De QUERY-functie leverde deze informatie, evenals overeenkomende kolommen om hun namen en werknemers-ID-nummers in een aparte lijst weer te geven.
In dit voorbeeld wordt een zeer specifiek gegevensbereik gebruikt. U kunt dit wijzigen om alle gegevens in de kolommen A tot en met E op te vragen. Hierdoor kunt u doorgaan met het toevoegen van nieuwe werknemers aan de lijst. De QUERY-formule die je hebt gebruikt, wordt ook automatisch bijgewerkt wanneer je nieuwe medewerkers toevoegt of wanneer iemand de trainingssessie bijwoont.
De juiste formule hiervoor is =QUERY('Staff List'!A2:E, "Select A, B, C, E WHERE E = 'No'"). Deze formule negeert de oorspronkelijke titel “Werknemers” in cel A1.
Als u een 11e werknemer die de training niet heeft gevolgd aan de initiële lijst toevoegt, zoals hieronder weergegeven (Christine Smith), wordt de QUERY-formule ook bijgewerkt en wordt de nieuwe werknemer weergegeven.
Geavanceerde QUERY-formules
De QUERY-functie is veelzijdig. Hiermee kunt u andere logische bewerkingen (zoals EN en OF) of Google-functies (zoals COUNT) gebruiken als onderdeel van uw zoekopdracht. U kunt ook vergelijkingsoperatoren (groter dan, kleiner dan, enzovoort) gebruiken om waarden tussen twee cijfers te vinden.
Vergelijkingsoperatoren gebruiken met QUERY
U kunt QUERY gebruiken met vergelijkingsoperatoren (zoals kleiner dan, groter dan of gelijk aan) om gegevens te verfijnen en te filteren. Om dit te doen, voegen we een extra kolom (F) toe aan ons blad “Personeelslijst” met het aantal prijzen dat elke werknemer heeft gewonnen.
Met QUERY kunnen we zoeken naar alle medewerkers die minimaal één award hebben gewonnen. Het formaat voor deze formule is =QUERY('Staff List'!A2:F12, "SELECT A, B, C, D, E, F WHERE F > 0").
Dit gebruikt een groter dan-vergelijkingsoperator (>) om te zoeken naar waarden boven nul in kolom F.
Het bovenstaande voorbeeld laat zien dat de QUERY-functie een lijst heeft geretourneerd van acht werknemers die een of meer prijzen hebben gewonnen. Van de in totaal elf werknemers hebben er drie nog nooit een prijs gewonnen.
Gebruik AND en OR met QUERY
Geneste logische operatorfuncties zoals AND en OR werken goed binnen een grotere QUERY-formule om meerdere zoekcriteria aan uw formule toe te voegen.
VERWANT: Hoe u de EN- en OF-functies in Google Spreadsheets gebruikt
Een goede manier om AND te testen, is door te zoeken naar gegevens tussen twee datums. Als we ons voorbeeld van een werknemerslijst gebruiken, zouden we alle werknemers kunnen vermelden die zijn geboren tussen 1980 en 1989.
Dit maakt ook gebruik van vergelijkingsoperatoren, zoals groter dan of gelijk aan (> =) en kleiner dan of gelijk aan (<=).
Het formaat voor deze formule is =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1980-1-1' and D <= DATE '1989-12-31'"). Dit gebruikt ook een extra geneste DATE-functie om datum-tijdstempels correct te parseren, en zoekt naar alle verjaardagen tussen en gelijk aan 1 januari 1980 en 31 december 1989.
Zoals hierboven aangetoond, voldoen drie werknemers die zijn geboren in 1980, 1986 en 1983 aan deze vereisten.
U kunt OR ook gebruiken om vergelijkbare resultaten te verkrijgen. Als we dezelfde gegevens gebruiken, maar de datums wijzigen en OR gebruiken, kunnen we alle werknemers uitsluiten die in de jaren tachtig zijn geboren.
Het formaat voor deze formule zou zijn =QUERY('Staff List'!A2:E12, "SELECT A, B, C, D, E WHERE D >= DATE '1989-12-31' or D <= DATE '1980-1-1'").
Van de oorspronkelijke 10 medewerkers zijn er drie geboren in de jaren tachtig. Het bovenstaande voorbeeld toont de overige zeven, die allemaal zijn geboren voor of na de datums die we hebben uitgesloten.
COUNT gebruiken met QUERY
In plaats van simpelweg naar gegevens te zoeken en deze terug te sturen, kunt u QUERY ook combineren met andere functies, zoals COUNT, om gegevens te manipuleren. Laten we zeggen dat we een aantal van alle medewerkers op onze lijst willen verwijderen die de verplichte training wel en niet hebben bijgewoond.
Om dit te doen, kunt u QUERY op deze manier combineren met COUNT =QUERY('Staff List'!A2:E12, "SELECT E, COUNT(E) group by E").
Gericht op kolom E (“Bijgewoonde training”), gebruikte de QUERY-functie COUNT om het aantal keren te tellen dat elk type waarde (een “Ja” of een “Nee” tekstreeks) werd gevonden. Van onze lijst hebben zes medewerkers de training voltooid en vier niet.
U kunt deze formule eenvoudig wijzigen en gebruiken met andere soorten Google-functies, zoals SUM.