Om du behöver manipulera data i Google Sheets kan QUERY-funktionen hjälpa dig! Det ger kraftfull sökning i databasstil till ditt kalkylblad, så att du kan slå upp och filtrera dina data i vilket format du vill. Vi går igenom hur du använder den.
Innehållsförteckning
Använda QUERY-funktionen
QUERY-funktionen är inte så svår att bemästra om du någonsin har interagerat med en databas med SQL. Formatet för en typisk QUERY-funktion liknar SQL och ger kraften i databassökningar till Google Sheets.
Formatet för en formel som använder QUERY-funktionen är =QUERY(data, fråga, rubriker). Du ersätter ”data” med ditt cellintervall (till exempel ”A2:D12” eller ”A:D”) och ”query” med din sökfråga.
Det valfria argumentet ”headers” anger antalet rubrikrader som ska inkluderas överst i ditt dataintervall. Om du har en rubrik som sprids över två celler, som ”Första” i A1 och ”Namn” i A2, skulle detta ange att QUERY använder innehållet i de två första raderna som den kombinerade rubriken.
I exemplet nedan innehåller ett arbetsblad (kallad ”personallista”) i ett kalkylark i Google Kalkylark en lista över anställda. Den inkluderar deras namn, anställdas ID-nummer, födelsedatum och om de har deltagit i sin obligatoriska utbildningssession för anställda.
På ett andra blad kan du använda en QUERY-formel för att ta fram en lista över alla anställda som inte har deltagit i den obligatoriska utbildningen. Denna lista kommer att innehålla anställdas ID-nummer, förnamn, efternamn och om de deltog i utbildningen.
För att göra detta med data som visas ovan kan du skriva =QUERY(’Personallista’!A2:E12, ”VÄLJ A, B, C, E WHERE E = ’Nej'”). Detta frågar efter data från intervall A2 till E12 på bladet ”Personallista”.
Liksom en typisk SQL-fråga väljer QUERY-funktionen de kolumner som ska visas (SELECT) och identifierar parametrarna för sökningen (WHERE). Den returnerar kolumnerna A, B, C och E, och ger en lista över alla matchande rader där värdet i kolumn E (”Deltad träning”) är en textsträng som innehåller ”Nej”.
Som framgår ovan har fyra anställda från den första listan inte deltagit i en utbildning. Funktionen QUERY gav denna information, samt matchande kolumner för att visa deras namn och medarbetar-ID-nummer i en separat lista.
Det här exemplet använder ett mycket specifikt dataintervall. Du kan ändra detta för att fråga efter alla data i kolumnerna A till E. Detta skulle tillåta dig att fortsätta lägga till nya medarbetare till listan. QUERY-formeln du använde uppdateras också automatiskt när du lägger till nya medarbetare eller när någon deltar i utbildningssessionen.
Den korrekta formeln för detta är =QUERY(’Personallista’!A2:E, ”Välj A, B, C, E WHERE E = ’Nej'”). Den här formeln ignorerar den ursprungliga titeln ”Anställda” i cell A1.
Om du lägger till en 11:e anställd som inte har deltagit i utbildningen till den första listan, som visas nedan (Christine Smith), uppdateras QUERY-formeln också och visar den nya medarbetaren.
Avancerade QUERY-formler
QUERY-funktionen är mångsidig. Det låter dig använda andra logiska operationer (som OCH och ELLER) eller Google-funktioner (som COUNT) som en del av din sökning. Du kan också använda jämförelseoperatorer (större än, mindre än och så vidare) för att hitta värden mellan två siffror.
Använda jämförelseoperatorer med QUERY
Du kan använda QUERY med jämförelseoperatorer (som mindre än, större än eller lika med) för att begränsa och filtrera data. För att göra detta lägger vi till ytterligare en kolumn (F) till vår ”Personallista” med antalet utmärkelser varje anställd har vunnit.
Med QUERY kan vi söka efter alla anställda som har vunnit minst en utmärkelse. Formatet för denna formel är =QUERY(’Personallista’!A2:F12, ”VÄLJ A, B, C, D, E, F WHERE F > 0”).
Detta använder en större än jämförelseoperator (>) för att söka efter värden över noll i kolumn F.
Exemplet ovan visar att QUERY-funktionen returnerade en lista med åtta anställda som har vunnit en eller flera utmärkelser. Av totalt 11 anställda har tre aldrig vunnit ett pris.
Använd OCH och ELLER med QUERY
Kapslade logiska operatorfunktioner som AND och OR fungerar bra i en större QUERY-formel för att lägga till flera sökkriterier till din formel.
Ett bra sätt att testa AND är att söka efter data mellan två datum. Om vi använder vårt exempel på personallistan kan vi lista alla anställda födda 1980 till 1989.
Detta drar också fördel av jämförelseoperatorer, som större än eller lika med (>=) och mindre än eller lika med (
Formatet för denna formel är =QUERY(’Personallista’!A2:E12, ”VÄLJ A, B, C, D, E WHERE D >= DATUM ’1980-1-1’ och D
Som framgår ovan uppfyller tre anställda som är födda 1980, 1986 och 1983 dessa krav.
Du kan också använda ELLER för att få liknande resultat. Om vi använder samma data, men byter datum och använder OR, kan vi utesluta alla anställda som är födda på 1980-talet.
Formatet för denna formel skulle vara =QUERY(’Personallista’!A2:E12, ”VÄLJ A, B, C, D, E WHERE D >= DATUM ’1989-12-31’ eller D
Av de ursprungliga tio anställda var tre födda på 1980-talet. Exemplet ovan visar de återstående sju, som alla är födda före eller efter datumen vi exkluderade.
Använder COUNT med QUERY
Istället för att bara söka efter och returnera data kan du också blanda QUERY med andra funktioner, som COUNT, för att manipulera data. Låt oss säga att vi vill rensa ett antal av alla anställda på vår lista som har och inte har deltagit i den obligatoriska utbildningen.
För att göra detta kan du kombinera QUERY med COUNT så här =QUERY(’Personallista’!A2:E12, ”SELECT E, COUNT(E) group by E”).
Med fokus på kolumn E (”Deltad träning”), använde QUERY-funktionen COUNT för att räkna antalet gånger varje typ av värde (en ”Ja” eller en ”Nej” textsträng) hittades. Från vår lista har sex anställda genomgått utbildningen och fyra har inte.
Du kan enkelt ändra denna formel och använda den med andra typer av Google-funktioner, som SUM.