Hvis du trenger å manipulere data i Google Sheets, kan QUERY-funksjonen hjelpe! Det gir kraftig søk i databasestil til regnearket ditt, slik at du kan slå opp og filtrere dataene dine i hvilket som helst format du vil. Vi vil lede deg gjennom hvordan du bruker den.
Innholdsfortegnelse
Bruke QUERY-funksjonen
QUERY-funksjonen er ikke så vanskelig å mestre hvis du noen gang har samhandlet med en database ved hjelp av SQL. Formatet til en typisk QUERY-funksjon ligner på SQL og bringer kraften til databasesøk til Google Sheets.
Formatet til en formel som bruker QUERY-funksjonen er =QUERY(data, spørring, overskrifter). Du erstatter «data» med celleområdet ditt (for eksempel «A2:D12» eller «A:D») og «query» med søket ditt.
Det valgfrie «headers»-argumentet angir antallet overskriftsrader som skal inkluderes øverst i dataområdet. Hvis du har en overskrift som sprer seg over to celler, som «Først» i A1 og «Navn» i A2, vil dette spesifisere at QUERY bruker innholdet i de to første radene som den kombinerte overskriften.
I eksemplet nedenfor inneholder et ark (kalt «Staff List») i et Google Sheets-regneark en liste over ansatte. Den inkluderer navn, medarbeider-ID-nummer, fødselsdato og om de har deltatt på den obligatoriske opplæringsøkten for ansatte.
På et annet ark kan du bruke en QUERY-formel for å trekke en liste over alle ansatte som ikke har deltatt på den obligatoriske opplæringsøkten. Denne listen vil inkludere ansattes ID-numre, fornavn, etternavn og om de deltok på treningsøkten.
For å gjøre dette med dataene vist ovenfor, kan du skrive =QUERY(«Staff List»!A2:E12, «VELG A, B, C, E WHERE E = «Nei'»). Dette spør etter dataene fra område A2 til E12 på «Staff List»-arket.
Som en typisk SQL-spørring, velger QUERY-funksjonen kolonnene som skal vises (SELECT) og identifiserer parametrene for søket (WHERE). Den returnerer kolonnene A, B, C og E, og gir en liste over alle samsvarende rader der verdien i kolonne E («Delte opplæring») er en tekststreng som inneholder «Nei.»
Som vist ovenfor har fire ansatte fra den første listen ikke deltatt på en treningsøkt. QUERY-funksjonen ga denne informasjonen, samt matchende kolonner for å vise deres navn og medarbeider-ID-nummer i en egen liste.
Dette eksemplet bruker et veldig spesifikt dataområde. Du kan endre dette for å søke etter alle dataene i kolonnene A til E. Dette vil tillate deg å fortsette å legge til nye ansatte i listen. QUERY-formelen du brukte vil også oppdateres automatisk når du legger til nye ansatte eller når noen deltar på treningsøkten.
Den riktige formelen for dette er =QUERY(«Personalliste»!A2:E, «Velg A, B, C, E WHERE E = «Nei'»). Denne formelen ignorerer den første «Ansatte»-tittelen i celle A1.
Hvis du legger til en 11. ansatt som ikke har deltatt på opplæringen til den innledende listen, som vist nedenfor (Christine Smith), oppdateres QUERY-formelen også og viser den nye ansatte.
Avanserte QUERY-formler
QUERY-funksjonen er allsidig. Den lar deg bruke andre logiske operasjoner (som OG og ELLER) eller Google-funksjoner (som COUNT) som en del av søket ditt. Du kan også bruke sammenligningsoperatorer (større enn, mindre enn og så videre) for å finne verdier mellom to figurer.
Bruke sammenligningsoperatører med QUERY
Du kan bruke QUERY med sammenligningsoperatorer (som mindre enn, større enn eller lik) for å begrense og filtrere data. For å gjøre dette, legger vi til en ekstra kolonne (F) på arket «Staffliste» med antall priser hver ansatt har vunnet.
Ved å bruke QUERY kan vi søke etter alle ansatte som har vunnet minst én pris. Formatet for denne formelen er =QUERY(«Personalliste»!A2:F12, «VELG A, B, C, D, E, F WHERE F > 0»).
Dette bruker en større enn sammenligningsoperator (>) for å søke etter verdier over null i kolonne F.
Eksemplet ovenfor viser QUERY-funksjonen returnerte en liste over åtte ansatte som har vunnet en eller flere priser. Av totalt 11 ansatte har tre aldri vunnet en pris.
Bruk AND og OR med QUERY
Nestede logiske operatorfunksjoner som AND og OR fungerer godt innenfor en større QUERY-formel for å legge til flere søkekriterier i formelen din.
En god måte å teste OG er å søke etter data mellom to datoer. Hvis vi bruker vårt eksempel på ansattliste, kan vi liste opp alle ansatte født fra 1980 til 1989.
Dette drar også fordel av sammenligningsoperatorer, som større enn eller lik (>=) og mindre enn eller lik (
Formatet for denne formelen er =QUERY(«Personalliste»!A2:E12, «VELG A, B, C, D, E WHERE D >= DATO «1980-1-1» og D
Som vist ovenfor oppfyller tre ansatte som er født i 1980, 1986 og 1983 disse kravene.
Du kan også bruke OR for å produsere lignende resultater. Hvis vi bruker samme data, men bytter datoene og bruker OR, kan vi ekskludere alle ansatte som er født på 1980-tallet.
Formatet for denne formelen vil være =QUERY(«Personalliste»!A2:E12, «VELG A, B, C, D, E WHERE D >= DATO «1989-12-31» eller D
Av de opprinnelige 10 ansatte var tre født på 1980-tallet. Eksemplet ovenfor viser de resterende syv, som alle ble født før eller etter datoene vi ekskluderte.
Bruker COUNT med QUERY
I stedet for bare å søke etter og returnere data, kan du også blande QUERY med andre funksjoner, som COUNT, for å manipulere data. La oss si at vi ønsker å slette en rekke av alle ansatte på listen vår som har og ikke har deltatt på den obligatoriske treningsøkten.
For å gjøre dette, kan du kombinere QUERY med COUNT slik =QUERY(«Staff List»!A2:E12, «SELECT E, COUNT(E) group by E»).
Med fokus på kolonne E («Delte opplæring»), brukte QUERY-funksjonen COUNT for å telle antall ganger hver type verdi (en «Ja» eller en «Nei» tekststreng) ble funnet. Fra listen vår har seks ansatte fullført opplæringen, og fire har ikke.
Du kan enkelt endre denne formelen og bruke den med andre typer Google-funksjoner, som SUM.