Sådan bruger du QUERY i Google Sheets trin for trin

¿Hvordan bruger man QUERY i Google Sheets? Hvis du arbejder med Excel Uanset om du arbejder med regneark dagligt eller ej, vil du før eller siden få brug for noget mere kraftfuldt end simple filtre og summer. Funktionen Google Sheets QUERY Det er den schweiziske lommekniv, der lader dig behandle dine data som en SQL-database, men uden at forlade regnearket eller konfigurere noget kompliceret. Det er et af de værktøjer, der, når du først mestrer det, fuldstændig ændrer den måde, du organiserer dine rapporter på.

I denne artikel vil du se i detaljer, hvordan du bruger QUERY i Google Sheets til at filtrere, sortere, gruppere, pivotere, formatere og kombinere Data på en meget fleksibel måde. Du vil se forholdet til SQL, præcis hvad hver klausul gør (SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, OFFSET, LABEL og FORMAT), hvordan den opfører sig med datoer, klokkeslæt og tekst, og også hvordan du bruger den med data spredt over flere ark eller endda med områder, der har et Google Sheets-tabelformat.

Hvad er QUERY i Google Sheets, og hvorfor ligner det SQL så meget?

QUERY-funktionen i Google Sheets er en formel, der udfører en slags SQL-lignende forespørgselssprog over et celleområde. Du kan tænke på dit ark som en databasetabel: hver kolonne er et felt, og hver række er en post. Med QUERY kan du bede Sheets om kun at returnere de rækker og kolonner, du er interesseret i, om at udføre aggregeringer, ændre formatering, omdøbe kolonner osv. uden at røre den oprindelige tabel.

Teknisk set er funktionen baseret på Google Visualization API-forespørgselssprog (Google Visualization API Query Language). Det minder meget om standard SQL, selvom det har sin egen syntaks og nogle unikke funktioner. For eksempel bruger det kolonneidentifikatorer som A, B, C eller Kol1, Kol2… i stedet for feltnavne, og det har specifikke funktioner til håndtering af datoer, klokkeslæt eller strenge.

En stor fordel er, at QUERY kombinerer det, du normalt ville gøre med flere funktioner, som f.eks. FILTER, SORTERING, SUMIFS, VLOOKUP og så videre. Dette gør dine regneark overskuelige og nemmere at vedligeholde, især når du opretter dashboards eller rapporter, der er afhængige af mange data.

Grundlæggende syntaks for QUERY-funktionen i Google Sheets

Funktionens generelle struktur på spansk er:

=FORSPRØVELSE(data; forespørgsel;)

Eller, hvis din brugerflade er på engelsk:

=FORESPØRGELSE(dataområde, forespørgsel, )

Hvor argumentet data (eller data_range) er det celleområde, som du vil køre forespørgslen på. Det kan være noget i retning af A2:E100, et område på et andet ark (Data!A1:F500) eller endda en kombination af områder ved hjælp af krøllede parenteser til at oprette arrays. Det vigtige er, at hver kolonne primært indeholder en enkelt type data. tekst, tal/valuta eller dato/klokkeslætfordi QUERY udleder typen i henhold til majoriteten og behandler minoriteten som nul.

Det andet argument, konsultationDette er en tekststreng, der indeholder forespørgslen i visualiserings-API-sproget. Den er omsluttet af dobbelte anførselstegn eller kan være en reference til en celle, der indeholder teksten. For eksempel:
=FORESPØRGELSE('Data'!A:L; «vælg *»)

Den tredje parameter, overskrifterDette er valgfrit og angiver, hvor mange af de øverste rækker i området der betragtes som overskrifter. Hvis du udelader det, vil Google Sheets forsøge at registrere dette automatisk. Hvis du indtaster 0, vil alt blive behandlet som data. Hvis du indtaster 1, vil det antage, at den første række i området er overskrifter, og så videre.

Typer af literaler som QUERY forstår (tekst, tal og datoer)

I forespørgselssektionen skal du bruge, når du filtrerer eller sammenligner værdier bogstaverDette er de direkte værdier, som du skriver i forespørgselsstrengen, og de kan være af forskellige typer: strenge, tal eller datoer/tidspunkter, hver med sin egen specifikke syntaks.

De tekststrenge De er omgivet af enkelte eller dobbelte anførselstegn, og sammenligningen skelner mellem store og små bogstaver. Eksempler: "første dag", "én person", "Hamburger". Når du filtrerer med WHERE eller bruger operatorer som starter med, slutter med, indeholder, kan lide eller matcherDu vil primært arbejde med denne type bogstavelig tekst.

De tal De er skrevet med normal decimalnotation: 1, 2.5, 7.15, -20.0, 0.8… Det er disse, du sender til numeriske operatorer eller aggregeringsfunktioner som f.eks. sum, gennemsnit, min, maksHusk, at hvis du blander tal med tekst i samme kolonne, vil QUERY forsøge at bestemme, hvilken type der dominerer; alt, der ikke matcher den valgte type, vil blive betragtet som en nullværdi.

De datoer og tidspunkter De har en lidt mere specifik syntaks. De er konstrueret med nøgleord som DATE, TIMEOFDAY, TIMESTAMP eller DATETIME efterfulgt af værdien i ISO-format: åååå-MM-dd for datoer, HH:mm:ss for klokkeslæt og åååå-MM-dd HH:mm:ss for dato- og tidsstempler. Dette er vigtigt, når man skriver datoliteraler direkte i forespørgslen.

QUERY-klausuler: den korrekte rækkefølge og hvad hver enkelt gør

Forespørgselssproget, der bruges af QUERY, har 9 hovedklausuler. Du behøver ikke at bruge dem alle, men hvis du kombinerer flere, skal de være i en bestemt rækkefølge. Rækkefølgen er: VÆLG, HVOR, GRUPPER EFTER, PIVOT, ORDER EFTER, GRÆNSE, OFFSET, LABEL og FORMATHver enkelt tilføjer et lag af logik oven på det foregående resultat.

Klausulen VÆLGE Definer hvilke kolonner du vil returnere, og i hvilken rækkefølge. Hvis du ikke angiver det, opfører QUERY sig, som om du havde brugt "select *", og returnerer alle kolonner fra dataområdet i deres oprindelige rækkefølge.

Klausulen HVOR Det bruges til at filtrere rækker baseret på betingelser. Du kan kombinere betingelser med AND, OR og NOT, bruge numeriske operatorer som <, <=, >, >=, =, !=, <> og tekstfunktioner som starter med, slutter med, indeholder, matcher eller synes godt om, samt kontrollere for NULL-værdier med IS NULL og IS NOT NULL.

Klausulen GRUPPER EFTER Den grupperer rækker, der deler de samme værdier, i en eller flere kolonner, ligesom GROUP BY i SQL. Hver gruppe reduceres til en enkelt række, der indeholder resultatet af aggregeringsfunktioner (sum, gennemsnit, antal, maks., min. osv.), der anvendes på de numeriske kolonner.

Med PIVOT Du kan konvertere værdier fra en kolonne til kolonneoverskrifter og dermed oprette pivottabeller ud fra dit oprindelige område. Dette bruges næsten altid i kombination med aggregeringsfunktioner og ofte med GROUP BY for at opnå mere omfattende krydstabuleringer.

BESTIL EFTER Sortér resultaterne efter en eller flere kolonner, i stigende (ASC) eller faldende (DESC) rækkefølge. Hvis du ikke angiver noget, er standardrækkefølgen stigende. Du kan sortere efter kolonneidentifikatorer eller efter aggregeringsresultater.

BEGRÆNSE reducerer antallet af returnerede rækker til et forudbestemt maksimum, mens FORSKYDNING springer de første N rækker af resultatet over, før det vises. Kombineret tillader de paginering eller udelukkelse af uønskede overskrifter i selve resultatet.

Klausulen MÆRKE Det giver dig mulighed for at omdøbe kolonneoverskrifterne i den resulterende tabel. Dette er meget nyttigt for at sikre, at den endelige rapport har læsbare navne i stedet for ID'er som "sum(I)" eller "G*H".

Endelig, FORMAT Den anvender specifikke formater på numeriske kolonner, dato-, klokkeslæts- eller dato- og klokkeslætskolonner. Du kan definere mønstre som "dd-mmm-åååå" for datoer eller "##.00" for tal, så outputtet er præsentabelt lige fra starten.

SELECT i QUERY: valg og kombination af kolonner

SELECT-klausulen er grundlaget for enhver forespørgsel. Den angiver, hvilke kolonner der returneres, og deres rækkefølge. Du kan bruge kolonnebogstaver (A, B, C…) eller generiske identifikatorer som f.eks. Kolonne1, Kolonne2, Kolonne3, som refererer til henholdsvis den første, anden og tredje kolonne i dataområdet.

Det enkleste tilfælde er en SELECT-sætning for alt:

=QUERY('data fra Airtable'!A:I; «vælg *»)

I dette eksempel er området A:I kildetabellen, og strengen "select *" angiver, at du ønsker alle kolonner. Hvis du ønsker, at outputtet skal ekskludere overskrifter, kan du indstille den tredje parameter til 0. =QUERY('data fra Airtable'!A:I; «vælg *»; 0)På denne måde får du kun data, hvilket er nyttigt, hvis du vil indlejre outputtet i en anden formel.

Du kan også vælge bestemte kolonner, for eksempel:

=QUERY('data fra Airtable'!A:I; «vælg C, E, I»)

Dette returnerer kun kolonnerne C, E og I fra dataområdet. Det er meget almindeligt at kombinere dette valg med efterfølgende WHERE-, GROUP BY- eller ORDER BY-klausuler for at opbygge rapporter med fokus på et par nøgleparametre.

Når dine data er spredt ud over flere faner, kan du forbinde dem lodret ved hjælp af krøllede parenteser og derefter forespørge på dem, som om de var én tabel. For eksempel:

=FORESPØRGELSE({'data fra Airtable'!A1:L; Ark1!A1:L; Ark2!A1:L}; «vælg * hvor Kol.1 ikke er null»)

Her oprettes et array med tre stablede områder (det ene bag det andet). Kol1 refererer til den første kolonne i det kombinerede område, og WHERE-klausulen, `Kol1 er ikke nul`, filtrerer tomme rækker fra. Hvis du har brug for data fra et andet regneark, kan du kombinere det først med BETYDNING og derefter anvende QUERY på det importerede område.

WHERE i QUERY: filtrer rækker med simple og avancerede betingelser

WHERE-klausulen er dit værktøj til kun at udtrække de rækker, der opfylder bestemte betingelser. Den fungerer med tal, tekst, datoer og nullværdier og understøtter både grundlæggende operatorer og mere avancerede sammenligninger med regulære udtryk.

Et grundlæggende eksempel, filtrering efter en minimums numerisk værdi:

=FORSPRØVELSE('data fra Airtable'!A:I; «vælg C, E, I hvor I >= 40»)

I dette tilfælde får du kolonne C, E og I, men kun fra de rækker, hvor kolonne I (f.eks. samlet pris) er større end eller lig med 40. Nøglen er at bruge operatorerne >=, <=, >, <, = eller != (eller <>), som alle understøttes af QUERY.

Hvis du vil filtrere rækker som tomme eller ikke-tomme, kan du ikke bruge direkte sammenligninger med null, som du ville gøre med andre værdier. I stedet skal du bruge er null o er ikke nullFor eksempel: hvor A ikke er nul, tvinger kolonne A til at have en vis værdi.

For sammensatte betingelser bruger du og, eller og ikkeFor eksempel:

=QUERY('data fra Airtable'!A:I; «vælg C, E, I hvor I >= 40 og ikke E = 'Denver sandwich'»)

Den forrige forespørgsel filtrerer efter to kriterier: at værdien af ​​I er større end eller lig med 40, og at kolonne E ikke indeholder præcis "Denver sandwich". NOT-klausulen gælder for sammenligningen E = 'Denver sandwich', så du udelader disse rækker.

For lidt mere fleksible tekstsøgninger har du operatorer som f.eks. starter med, slutter med, indeholder, synes godt om og matcherHvis du for eksempel ønsker produkter, hvis beskrivelse starter med C, og kundens navn også starter med K, kan du skrive:

=QUERY('data fra Airtable'!A:I; «vælg C, E, I hvor E starter med 'C' og C som 'K%'»)

Her kontrollerer `starts with 'C'` den nøjagtige begyndelse af teksten, og `ligesom 'K%'` udfører en jokertegnssammenligning, hvor `%` repræsenterer et hvilket som helst antal tegn. Hvis du har brug for noget endnu mere kraftfuldt, så kommer `` i spil. kampe, som tillader brugen af ​​regulære udtryk.

For eksempel, for kun at få rækker hvor kolonne E er præcis "Steak sandwich":

=QUERY('data fra Airtable'!A:L; «vælg C, E, I hvor E matcher 'Steak sandwich'»)

Og for at acceptere enhver variation, der indeholder ordet "sandwich" midt i strengen, kan du skrive:

=QUERY('data fra Airtable'!A:L; «vælg C, E, I hvor E matcher '.*sandwich.*'»; 1)

I denne seneste version er .* et standard RegEx-mønster, der betyder "enhver tegnsekvens" før eller efter ordet sandwich, hvilket giver mulighed for meget fleksible delvise matches.

GROUP BY og aggregeringsfunktioner: opsummeringer og totaler med QUERY

Når du skal opsummere data, for eksempel beregne det samlede salg pr. kunde eller den gennemsnitlige løn pr. afdeling, er kombinationen af GROUP BY og aggregeringsfunktioner Det er dette, der gør QUERY til et mini BI-værktøj i Google Sheets.

De tilgængelige aggregeringsfunktioner omfatter blandt andet sum(col), avg(col), min(col), max(col) og count(col)De anvendes normalt på numeriske kolonner og bruges i SELECT-, ORDER BY-, LABEL- og FORMAT-klausulerne og på datasæt, der muligvis allerede er grupperet eller pivoteret.

Et simpelt eksempel: Læg det samlede beløb pr. kunde sammen. Forestil dig, at kolonne C er kundens navn, og kolonne I er beløbet for hver ordre. Du kunne skrive:

=QUERY('data fra Airtable'!A:I; «vælg C, sum(I) grupper efter C»)

Denne forespørgsel grupperer alle rækker, der deler den samme værdi i C (hver kunde), og beregner for hver gruppe summen af ​​kolonne I. Outputtet er en tabel med to kolonner: kundens navn og den aggregerede total.

Hvis du vil gruppere efter mere end ét kriterium, for eksempel efter klient og efter en anden kolonne H (måske en kategori eller dato), kan du bruge:

=QUERY('data fra Airtable'!A:I; «vælg C, H, sum(I) gruppe efter C, H»)

Husk én vigtig regel: Alle kolonner, du inkluderer i SELECT-sætningen, skal inkluderes i GROUP BY-klausulen eller indpakkes i en aggregeringsfunktion.Hvis du vælger C, H og sum(I), skal C og H være på listen GROUP BY-kolonne. Ellers returnerer QUERY en fejl.

Hvis du ikke inkluderer en GROUP BY-klausul, men bruger aggregeringsfunktioner i SELECT, udføres aggregeringen den hele datasættetFor eksempel:

=QUERY('data fra Airtable'!A:I; «vælg min(B), count(C), max(I), avg(G), sum(I)»)

Her får du fem globale datapunkter i en enkelt række: den tidligste dato i B, antallet af ikke-nul-elementer i C, den maksimale værdi af I, gennemsnittet af G og den samlede sum af I. Det er en meget hurtig måde at generere samlede totaler uden at skulle bruge fem separate funktioner.

PIVOT i QUERY: konverter værdier til kolonner i pivottabelstil

PIVOT-klausulen giver dig mulighed for at transformere forskellige værdier i en kolonne til nye kolonnerDette fungerer på samme måde som en pivottabel. Det er meget nyttigt til at få tværsnitsvisninger af dine data, for eksempel produkter efter kunde, måneder efter kategori osv.

Hvis du bruger PIVOT uden GROUP BY, og værdierne i den kolonne, du pivoterer på, gentages i forskellige rækker, vil QUERY automatisk aggregere disse rækker ved hjælp af de angivne funktioner. Uden GROUP BY ender du typisk med en enkelt resultatrække, der repræsenterer alle de kombinerede data.

Et simpelt eksempel: summer en kolonne G af priser grupperet efter produkt E som kolonner:

=QUERY('data fra Airtable'!A:I; «vælg sum(G) pivot E»)

I dette tilfælde bliver hver enkelt værdi i kolonne E sin egen kolonne i outputtet, og den enkelte række, du ser, viser summen af ​​G for hvert produkt. Det er en slags "total pr. kolonne".

Hvis du vil have, at hver række skal repræsentere en kunde, og kolonnerne skal repræsentere produkter, kan du kombinere GROUP BY og PIVOT. For eksempel:

=QUERY('data fra Airtable'!A:I; «vælg C, sum(G) gruppe efter C pivot E»; 1)

Denne forespørgsel returnerer en tabel, hvor den første kolonne indeholder kunden (C), og de resterende kolonner er for hvert produkt (E). Ved hvert kunde-produkt-krydspunkt vil du se summen af ​​G (beløb) for den pågældende kombination. Det er en meget effektiv måde at opbygge krydstabeller direkte i regnearket.

ORDER BY i QUERY: sorter resultater efter tekst, tal eller dato

ORDER BY-klausulen minder meget om SQL-klausulen og giver dig mulighed for at sortere resultaterne i henhold til en eller flere kolonner. Hvis du ikke angiver rækkefølgen, vil den som standard blive taget som stigende (ASC). For at vende den om skal du tilføje DESC.

Et typisk eksempel: sorter efter ordre-ID i stigende rækkefølge, ignorer tomme rækker:

=QUERY('data fra Airtable'!A:I; «vælg * hvor A ikke er null, rækkefølge efter A»)

Tilføjelsen af ​​WHERE A IS NOT NULL er vigtig for at forhindre tomme rækker (som QUERY nogle gange betragter som en del af området) i at snige sig ind i begyndelsen eller slutningen af ​​din sorterede tabel.

Hvis du ønsker en faldende rækkefølge, skal du blot tilføje DESC efter kolonneidentifikatoren:

=QUERY('data fra Airtable'!A:I; «vælg * sorter efter A beskrivelse»)

Du kan også sortere efter dato. Hvis din bestillingsdato for eksempel er i kolonne B, kan du gøre følgende:

=QUERY('data fra Airtable'!A1:L21; «vælg * sorter efter B»; 1)

Dette vil arrangere rækkerne i stigende kronologisk rækkefølge. Hvis du vil have den seneste post først, skal du ændre klausulen til ORDER BY B DESC. QUERY behandler datoer som interne tal, så rækkefølgen fungerer på præcis samme måde som med tal.

I mere komplekse forespørgsler kan du sortere efter mere end én kolonne og angive retningen for hver kolonne: for eksempel sorterer `sorter efter A, B desc` først efter A i stigende rækkefølge og, inden for hver gruppe af A, efter B i faldende rækkefølge. Det er vigtigt at angive retningen for hver kolonne, hvis du ønsker kombinationer af forskellige sorteringsretninger.

LIMIT og OFFSET: styrer hvor mange rækker QUERY returnerer

Når du arbejder med store mængder information, ønsker du måske ikke at se hele resultatet på én gang. Klausulerne GRÆNSE og OFFSET De giver dig mulighed for at beskære og flytte det returnerede rækkesæt meget nemt.

LIMIT-klausulen begrænser antallet af viste rækker (eksklusive den oprindelige header-række i området, som håndteres af headers-parameteren). For eksempel, for kun at hente de første 5 rækker i et område:

=QUERY('data fra Airtable'!A:I; «vælg * grænse 5»)

Her modtager du maksimalt 5 rækker data plus headeren, hvis du har defineret en i det tredje argument, eller den er blevet registreret automatisk.

OFFSET-klausulen angiver derimod, hvor mange rækker fra toppen af ​​resultatet der skal springes over, før indholdet vises. For eksempel, for at springe de første 10 rækker over:

=QUERY('data fra Airtable'!A:I; «vælg * offset 10»)

Hvis du kombinerer LIMIT og OFFSET, skal du være opmærksom på, at selvom OFFSET vises efter LIMIT i syntaksen, så er det i virkeligheden den anvendes førstDet vil sige, at først springes de rækker, der er angivet med OFFSET, over, og derefter tælles de rækker, der er angivet med LIMIT. For eksempel:

=QUERY('data fra Airtable'!A:I; «vælg * grænse 5 offset 10»)

Denne forespørgsel udelader de første 10 rækker af det oprindelige datasæt og returnerer 5 rækker med resultater. Dette er f.eks. nyttigt til paginering eller til at springe over datablokke, som du allerede har behandlet et andet sted.

LABEL og FORMAT: omdøbning af kolonner og formatering med QUERY

Ofte, efter anvendelse af aggregeringsfunktioner eller beregning af afledte kolonner, er de overskrifter, der returneres af QUERY, ikke særlig brugervenlige (for eksempel "sum(I)" eller "G*H"). Klausulen MÆRKE Det giver dig mulighed for at tildele mere læsbare navne til disse kolonner direkte fra forespørgslen.

Hvis du for eksempel har en forespørgsel, der returnerer alle kolonner, og du vil omdøbe C til "kunde", E til "Hamburger" og I til "I alt betalt", kan du skrive:

=QUERY('data fra Airtable'!A:I; «vælg * label C 'kunde', E 'Burger', I 'I alt betalt'»)

Syntaksen består af at angive kolonneidentifikatoren (eller udtrykket, f.eks. sum(I)) efterfulgt af ordet label og, i enkelte anførselstegn, det nye navn. Du kan angive flere labels adskilt af kommaer i den samme klausul.

Klausulen FORMATPå den anden side bruges det til at definere outputformatet for numeriske kolonner, dato-, klokkeslæts- og dato- og klokkeslætskolonner. Det ligner Google Sheets' brugerdefinerede celleformatering og anvendes uden at påvirke det originale ark's celleformatering.

Et eksempel på kombineret brug af LABEL og FORMAT ville være:

=QUERY('data fra Airtable'!A:J; «vælg B, G, I, J label J 'Tidspunkt' format B 'dd-mmm-åååå', G '##.00', I '##.000', J 'TT'»)

I dette tilfælde udtrækker forespørgslen kolonnerne B, G, I og J. Derefter ændrer den navnet på kolonne J til "Tidspunkt" og anvender formatering: B som en dato med dag-måned forkortet som år, G med to decimaler, I med tre decimaler, og J viser kun tiden i 24-timers format. Dette resulterer i et langt mere professionelt udseende output.

Aritmetiske operatorer og skalarfunktioner i QUERY

Ud over aggregeringsfunktioner inkluderer QUERY grundlæggende aritmetiske operatorer (+, -, *, /) og skalære funktioner der tjener til at omdanne værdier fra en kolonne til andre typer data eller til nyttige afledninger (år, måned, dag, tidspunkt, store/små bogstaver osv.).

Aritmetiske operatorer giver dig mulighed for at udføre beregninger direkte i forespørgslen. Hvis G f.eks. er enhedsprisen og H er mængden, kan du oprette en kolonne med totalen ved at gange begge dele:

=QUERY('data fra Airtable'!A:I; «vælg C, I, G*H label G*H 'Aritmetisk multiplikation'»)

Forespørgslen udtrækker C, I og resultatet af G*H og omdøber sidstnævnte kolonne til LABEL. Dette undgår at skulle oprette en hjælpekolonne i regnearket til beregningen; alt håndteres i selve forespørgslen.

Skalarfunktioner anvendes på en værdi og returnerer en anden. Der findes flere familier: dem der arbejder med DATO eller DATO- OG TIDSPUNKT, dem der gør det med DATETIME eller TIMEOFDAY, dem der konverterer til store eller små bogstaver, dem der beregner datoforskelle og dem der konverterer typer.

For eksempel, for at opdele en dato (kolonne B) i år, måned, dag, kvartal og ugedag, kan du skrive:

=QUERY('data fra Airtable'!A:I; «vælg år(B), måned(B), dag(B), kvartal(B), ugedag(B)»)

På samme måde, hvis du har datetime- eller timeofday-værdier i kolonne K og vil udtrække time, minut, sekund og millisekundet, skal du bruge:

=QUERY('data fra Airtable'!A:K; «vælg time(K), minut(K), sekund(K), millisekund(K)»)

For at manipulere tekst er der funktionerne sænke() y øverst()som konverterer strenge til små eller store bogstaver. Hvis du for eksempel vil have kundens navn i begge varianter fra kolonne C:

=QUERY('data fra Airtable'!A:I; «vælg nedre(C), øvre(C)»)

Meget nyttigt, når du skal bruge disse resultater som join-nøgler senere og vil undgå problemer med store bogstaver.

Beregn datoforskelle og arbejd med now() og toDate()

En anden meget praktisk skalarfunktion er datoForskel()Denne funktion beregner forskellen mellem to datoer eller dato- eller klokkeslæt og returnerer et tal (i dage). Den accepterer to parametre af typen DATE eller DATETIME. Hvis dine datoer f.eks. er i kolonne B og K, kan du måle forskellen mellem dem således:

=QUERY('data fra Airtable'!A:K; «vælg dateDiff(B, K) label dateDiff(B, K) 'Forskel mellem to datoer, dage'»)

Denne forespørgsel returnerer en kolonne med antallet af dage mellem dato B og dato K for hver række og omdøber kolonnen med en mere beskrivende etiket. Den er ideel til beregning af varigheder, deadlines eller forløbet tid.

Hvis du vil måle forskellen mellem en dato i tabellen og det aktuelle klokkeslæt, kommer funktionen i spil. nu()som ikke kræver nogen parametre og returnerer den aktuelle dato/tid som en datetime. Kombineret med dateDiff kunne du få noget i retning af dette:

=QUERY('data fra Airtable'!A:K; «vælg dateDiff(B, now()) label dateDiff(B, now()) 'Forskel mellem dato og nu, dage'»)

På denne måde vil hver genberegning af arket opdatere forskellen i dage mellem dato B og nutiden, hvilket er meget nyttigt til at spore deadlines eller ventende opgaver.

Endelig funktionen tilDato() Dette giver dig mulighed for at konvertere en værdi i datetime-format, eller endda et tal, til en ren DATE-type. Hvis du har en datetime-værdi i kolonne K, og du kun er interesseret i datoen, kan du bruge:

=QUERY('data fra Airtable'!A:K; «vælg tilDato(K)»)

Forespørgslen returnerer kun datodelen og udelader tidspunktet. Dette forenkler sammenligninger eller grupperinger efter dato i høj grad uden at skulle rense den oprindelige kolonne på forhånd.

Brug af QUERY med flere ark, kombinerede områder og tabeller i Google Sheets

QUERY er ikke begrænset til at arbejde på et enkelt fladt ark. Du kan referere forskellige sider i det samme dokumentDu kan kombinere områder vandret eller lodret, og meget vigtigt er det, at du også kan bruge det på områder, der er formateret som en Google Sheets-tabel: i sidste ende er det celleområdet, der betyder noget, ikke den visuelle formatering.

For at bruge data fra et andet ark i den samme fil, skal du blot sætte arknavnet og et udråbstegn foran området. Eksempel:

=FORESPØRGELSE(Data!A1:E14; «vælg *»)

Hvis arknavnet indeholder mellemrum eller specialtegn, skal du omsætte det til enkelte anførselstegn, sådan her:
=QUERY('Ark med et mærkeligt navn'!A1:E14; «vælg *»)

Hvis du vil kombinere flere datakilder vertikalt (hvilket i SQL ville svare til en UNION), kan du bruge krøllede parenteser og semikolon til at stable områder med den samme struktur. For eksempel:

=FORESPØRGELSE({Ark1!A1:E; Ark2!A1:E}; «vælg *»)

Dette vil forbinde rækkerne i Ark1 og Ark2 under hinanden. Kolonneidentifikatorerne (A, B, C… eller Kol1, Kol2…) vil gælde for det kombinerede sæt. Omvendt, hvis du vil kombinere områder vandret (som et rækkeindeks JOIN), skal du bruge krøllede parenteser med kommaer i stedet for semikolon:

=FORESPØRGELSE({Ark1!A1:B; Ark2!A1:B}; «vælg *»)

I dette tilfælde tilføjes kolonnerne i Ark2 til højre for kolonnerne i Ark1, forudsat at antallet af rækker matcher, og at hver tilsvarende række kan justeres direkte.

Vedrørende Google Sheets-tabeller (De nye områder med overskrifter, hurtigfiltre og brugerdefineret formatering) er vigtige, fordi de internt forbliver et normalt celleområde. Ja, du kan bruge QUERY på dem uden problemer. Alt du skal gøre er at bruge tabellens celleområde som det første argument i QUERY, for eksempel A1:D500, selvom det visuelt er konfigureret som en tabel. Hvis du vil vælge rækker, hvor "Udgivelsesdatoen" falder inden for et bestemt datointerval, skal du blot angive den tilsvarende kolonne i SELECT- og WHERE-klausulerne og, hvis det ønskes, referere til deadlines i eksterne celler, så du ikke behøver at indtaste dem manuelt i forespørgselsstrengen.

Hvis du derefter vil sende disse filtrerede rækker til en brugerdefineret funktion (f.eks. et Apps Script), kan du bruge outputtet fra QUERY som input til den pågældende funktion, da den returnerer et normalt array, som brugerdefinerede funktioner kan behandle række for række.

QUERY versus andre værktøjer: hvornår skal man bruge formler og hvornår skal man bruge ETL

Det er sandt, at QUERY løser et stort antal use cases, men det er også sandt, at når dine dataflows bliver komplekse (flere kilder, hyppige opdateringer, mange transformationsregler), kan det blive vanskeligt at administrere alt udelukkende med formler... vanskelig at vedligeholde og tilbøjelig til fejl, og hvis du arbejder med Excel-filer, er det godt at vide Sådan forhindrer du Excel i at gå ned, når du håndterer store regneark.

I mere avancerede scenarier giver det mening at bruge ETL-værktøjer eller -forbindelser, der integrerer realtidsdata i Google Sheets og tillader transformationer uden at skrive en eneste formel. Platforme som Coupler.io giver dig f.eks. mulighed for at importere data fra andre regneark, CRM'er, databaser eller SaaS-applikationer, skjule/vise kolonner, definere filtre og sorteringsmuligheder og visuelt tilføje beregnede kolonner.

Disse løsninger har klare fordele: du sparer tid, reducerer risikoen for at lave en fejl i en kompleks forespørgselsformel og vedligeholder data opdateres automatisk (for eksempel hvert 15. minut), og generelt gør du det nemmere for andre, mindre tekniske brugere at arbejde med dataene uden at skulle lære forespørgselssproget.

Alligevel anbefales det stadig at mestre QUERY: det giver dig fin kontrol over, hvad der sker i regnearket, træner dig i syntaks, der er meget tæt på SQL (hvilket forbereder dig på at tage springet til data warehouses som BigQuery), og er i mange sammenhænge den hurtigste løsning til at opbygge effektive rapporter uden at installere noget ekstra.

Kort sagt er QUERY-funktionen i Google Sheets et af de mest fleksible værktøjer til at manipulere data i et ark: Den kombinerer avanceret filtrering, sortering, aggregeringer, pivoter, formatering og områdefletning i én formel.Alt dette gøres med en syntaks inspireret af SQL. Uanset om du starter med en simpel tabel eller flere sammenkædede ark, giver det dig mulighed for at modellere komplekse data, automatisere rapporter og i processen tilegne dig et solidt fundament i forespørgselssprog, der vil være nyttige, når du arbejder med mere seriøse databaser i fremtiden.