Foglio di calcolo come un database: cosa sono le query function

• Business
• Howto

Tutti coloro che sono in possesso di un account Google possono utilizzare gratuitamente Fogli.
Google Fogli è un potente foglio elettronico parte integrante della suite per l’ufficio basata interamente sul cloud. Abbiamo presentato le sue principali caratteristiche nell’articolo Fogli Google: guida alle principali funzionalità.

Uno strumento molto utile ma ancora poco conosciuto di Google Fogli sono le query function: esse permettono di interagire con qualunque foglio di calcolo come se fosse un database.

A riprova di ciò, la sintassi delle query function di Google Fogli è molto simile a quella di SQL ed è riassunta in questo documento di supporto.
Come si vede si può “interrogare” un foglio di calcolo estraendo i dati che servono usando query SELECT per poi usare WHERE, ad esempio, al fine di specificare i criteri di selezione di dati.

Non è ovviamente necessario utilizzare tutte le varie clausole per costruire la propria query function da usare con Fogli Google ma è importante seguire l’ordine indicato nella tabella (da SELECT a scendere).
La documentazione di Google (fare riferimento alla colonna di destra cliccando su Language clauses) fornisce tutte le informazioni sulla sintassi utilizzabile.

All’interno delle query function di Google Fogli si possono usare anche funzioni di aggregazione (i.e. somma, media, conta, valore massimo, valore minimo), funzioni scalari (anno, mese, giorno, ora, minuti, secondi, millisecondi, giorno della settimana, ora di sistema, differenza tra date, conversione di stringhe in maiuscolo o minuscolo,…) e operatori aritmetici.

Le righe del foglio di calcolo sono i record della base dati, le colonne sono assimilabili ai singoli campi del database. Utilizzando una sintassi complessivamente semplice si possono così estrarre molto rapidamente i dati che servono da uno o più fogli di calcolo (eventualmente combinandoli assieme) trasformandoli in informazioni utili per la propria impresa o professione.

Come usare le query function in Google Fogli

Si supponga di avere un foglio di calcolo contenente tanti dati organizzati in colonne identificate dalle rispettive intestazioni.
Aprendo tale documento con Google Fogli si creerà un nuovo foglio di calcolo agendo sul pulsante “+” in basso a sinistra.

Il foglio di calcolo Dati contiene le informazioni da elaborare con la query function; il nuovo foglio creato più a destra (in questo caso chiamato Report) ospiterà i risultati della query function.

Digitando =query nel campo funzione posto immediatamente sotto i menu di Google Fogli si potrà selezionare dal primo foglio elettronico (Dati, in questo caso) tutte le colonne che dovranno essere parte delle elaborazioni e usate come base dati per le interrogazioni.

Selezionando le varie colonne, Google Fogli riporterà nella query function l’intervallo di colonne che saranno prese in considerazione. Digitando poi un punto e virgola (;) nella query function si potrà specificare la SELECT vera e propria.

Prendiamo come esempio questo foglio di calcolo.

Digitando quanto segue, nel foglio di calcolo Report, si otterranno i contenuti delle sole colonne A, B, C, J e L. Google Fogli selezionerà però solo le righe (un po’ come i record di un database) in cui la colonna L (Profitto) abbia un valore superiore a 10.000 dollari:

=query(Dati!A:P;"SELECT A,B,C,J,L WHERE L>10000";1)

L’ultima cifra (1) consente di specificare quante righe di intestazione ci sono. Utilizzando -1 Google Fogli provvederà a individuare automaticamente le intestazioni ma specificando correttamente il valore si sarà più sicuri che non vengano commessi errori.

Tornando alla cella A1 del foglio di calcolo Report, si provi quindi a modificare la query function nel seguente modo:

=query(Dati!A:P;"SELECT A,SUM(J),SUM(L) WHERE P='2014' GROUP BY A";1)

Come si può notare, usando una sintassi SQL-like, questa volta abbiamo chiesto a Google Fogli di usare le funzioni di aggregazione “somma” (SUM()) per sommare vendite (colonna J) e profitti (L).
I dati ottenuti dalle due somme vengono raggruppati sulla base del valore della colonna A (Segment): in questo modo si ottengono i valori complessivi di vendite e profitti per ogni singolo segmento dell’attività.
La selezione viene poi limitata all’anno 2014, come specificato con la clausola (WHERE P='2014'). Tale clausola permette infatti di selezionare solo e soltanto le righe che nella colonna P (Anno) riportano 2014.

I valori ottenuti possono essere normalizzati usando il pulsante Altri formati della barra degli strumenti di Google Fogli oppure usando FORMAT direttamente a livello di query function.

Se volessimo istantaneamente ottenere la somma delle vendite suddivise non soltanto per segmento ma anche per singolo Paese, basterà usare la clausola pivot (la colonna B del foglio di calcolo contiene l’indicazione della nazione di riferimento):

=query(Dati!A:P;"SELECT A, SUM(J) WHERE P='2014' GROUP BY A PIVOT B";1)

Per ottenere la lista dei Paesi nei quali i singoli segmenti dell’attività hanno venduto di più si può utilizzare la query function seguente:

=query(Dati!A:P;"SELECT A, B, SUM(J) WHERE P='2014' GROUP BY A,B ORDER BY SUM(J) DESC,A";1)

Come si vede, la clausola ORDER BY ordina la somma dei valori di vendita in maniera tale da mostrarli in ordine decrescente e il raggruppamento viene effettuato per le colonne A e B (GROUP BY A,B) ovvero segmento e Paese.

Aggiungendo anche LABEL si possono assegnare nuove intestazioni personalizzate alle varie colonne, comprese quelle frutto di un’ulteriore elaborazione:

=query(Dati!A:P;"SELECT A, B, SUM(J) WHERE P='2014' GROUP BY A,B ORDER BY SUM(J) DESC,A LABEL A 'Segmento', B 'Paese', SUM(J) 'Vendite'";1)

Utilizzando eventualmente gli operatori AND e OR si potranno realizzare query function ancora più avanzate e creare l’equivalente delle query SQL JOIN. Google Fogli permette infatti non soltanto di accedere ai dati delle colonne presenti nello stesso foglio di calcolo ma anche inserire riferimenti diretti ad altri file. La differenza importante è che in questo secondo caso, all’interno della query function di Google Fogli, bisognerà avere cura di utilizzare i numeri delle colonne e non le lettere corrispondenti (Col1, Col2, Col3,…).