Come utilizzare SQL in Excel

In questa guida vedremo come utilizzare SQL in Excel, utilizzando sia strumenti interni a Excel come Power Query sia esterni, come SQLookup. SQL è il linguaggio per la gestione dei database più diffuso al mondo e credo l'interoperabilità tra Excel e SQL meriti un approfondimento in questa guida, anche per aiutarti a espandere i tuoi orizzonti nella parte di ottenimento e analisi dei dati.

Che cos'è SQL?

SQL (Structured Query Language) è un linguaggio di programmazione standardizzato utilizzato per gestire e manipolare dati in un sistema di gestione di basi di dati relazionali (RDBMS). Potresti aver sentito parlare di MySQL o PostgreSQL, che sono fra i più famosi sistemi di gestione di database relazionali. Ci sono differenze di linguaggio fra i diversi sistemi, ma le basi sono pressochè lo stesse a prescindere dal sistema scelto.

È un linguaggio dichiarativo, per cui l'utente specifica cosa desidera ottenere senza reale necessità di programmare e può essere utilizzato per eseguire diverse operazioni, come l'inserimento, l'aggiornamento, la cancellazione e la selezione dei dati.

Nello specifico, le operazioni principali che SQL consente di eseguire includono:

• SELECT: per recuperare dati dalle tabelle.
• INSERT: per inserire nuovi dati nelle tabelle.
• UPDATE: per aggiornare dati esistenti.
• DELETE: per eliminare dati.
• CREATE: per creare nuove tabelle e altre strutture di dati

SQL è un linguaggio consolidato e ampiamente diffuso, che in Excel è sempre stato utilizzato marginalmente. Vediamo però dove potremmo incontrarlo.

Come utilizzare SQL in Power Query in Excel

Excel è già predisposto per lavorare con fonti dati SQL tramite Power Query, che puoi approfondire nella guida dedicata.

Cliccando su Dati > Recupera Dati > Da database, vedrai come fra le opzioni ci sia anche Da database SQL Server. In questo caso possiamo accedere direttamente a un server e poi scrivere codice direttamente dall'interfaccia.

Ti servirebbero, in questo caso, alcune informazioni:

• L'indirizzo del server
• Il database che ti interessa utilizzare

Nella parte chiamata Istruzione SQL, puoi scrivere SQL, ad esempio:

SELECT * FROM dbSales

Prenderebbe tutti i dati presenti nel database chiamato dbSales, se disponibile. In questo modo puoi semplificare il processo di recupero dei dati, evitando di prendere tutto il database e riducendone la dimensione, prima di passare in Power Query.

Il processo è analogo nel caso dovessi agganciarti a PostgreSQL, che richiede di passare dal comando Da altre origini > Da ODBC, creando una connessione dedicata sul tuo PC. Qui c'è una differenza rilevante con Power BI, dato che in Power BI esiste proprio un connettore dedicato per PostgreSQL.

Anche in questo caso, puoi inserire un'istruzione SQL specifica nella sezione dedicata. In questo caso, puoi selezionare anche il database che ti interessa, direttamente nella scrittura del codice.

L'ultimo caso è quello relativo a opzioni legacy quali Microsoft Query, che puoi abilitare da Opzioni > Dati > Mostra le importazioni guidate di dati legacy. Questo è un caso limite, che esiste per motivi di compatibilità per supportare anche casi nei quali Power Query non era ancora adottato nelle aziende o presente in Excel. Anche in questo caso, puoi utilizzare SQL nel processo.

In sintesi, Power Query non usa quindi direttamente SQL per le sue operazioni di trasformazione dei dati, ma può interagire con SQL quando si connette a una fonte di dati che supporta il linguaggio SQL, come un database relazionale (ad esempio, SQL Server, MySQL, PostgreSQL, Oracle, ecc.).

Come utilizzare SQL all'interno di un foglio Excel

SQL non è supportato nativamente all'interno di Excel, ma c'è un modo per poter analizzare i dati come faremmo con una formula standard in Excel. In questo caso, utilizzeremo SQLOOKUP, un componente gratuito aggiuntivo creato da Felix Zumstein.

SQLOOKUP può essere scaricato dall'app store di Microsoft gratuitamente e funziona con Excel su Windows, macOS e Web (se il componente è installato).

Potresti vederlo anche scritto come Ricerca SQL, se hai il browser in Italiano - ma se vedi che il creatore è Zoomer Analytics GmbH, sei nel posto giusto. Una volta cliccato su Get it now / Ottienilo ora, sarà possibile aprire Excel e cominciare a utilizzarlo.

L'icona del componente apparirà nella scheda Formule.

Fra i vari vantaggi, invece, di questo add-in è che l'esecuzione delle istruzioni avviene sulla macchina evitando così qualsiasi problema di protezione dati dovuto all'elaborazione in cloud. I calcoli sono, in linea di massima, piuttosto veloci.

Fra i limiti che ho riscontrato usando questo add-in, il mio importante è che SQLOOKUP richiede una connessione a Internet per funzionare, altrimenti non si caricherà correttamente. Una volta caricato in Excel, puoi anche essere offline, ma inizialmente dovrai essere connesso a Internet. A volte ci sono dei rallentamenti nei calcoli e troverai l'errore #BUSY!, ma sono casi abbastanza rari. Se invece disinstalli il plugin, chiaramente Excel non riuscirà a riconoscere la formula e ti restituirà un errore.

Come utilizzare SQLOOKUP per sfruttare SQL all'interno di Excel

Il dataset su cui lavoreremo per imparare SQLookup è un dataset di fantasia analizzato da un ricercatore di un'università che sta seguendo un progetto sugli orsi. Vengono esaminate quattro specie di orsi: grizzly, polare, panda gigante e malese.

La prima tabella riporta i dati con il peso medio degli orsi (TBL_PESI), la seconda il numero di orsi contati in un anno e la dimensione media (TBL_NUM), mentre la terza tabella è uguale alla seconda ma con più rilevazioni (TBL_NUM_GRANDE).

Il componente SQLookup contiene esclusivamente la funzione SQLOOKUP(), quindi i nomi del componente e della funzione sono identici. Una sola funzione, estremamente dinamica e in grado d'interrogare i dati nella cartella di lavoro di Excel usando SQL (dialetto SQLite).

Utilizzeremo questo dataset per fare qualche esperimento, ma chiaramente puoi seguire la guida anche prendendo un file qualsiasi di lavoro.

Come scrivere le istruzioni con SQLOOKUP

Se usi SQLOOKUP, la formula deve essere scritta con un ordine preciso:

=SQLOOKUP(
query,
[alias a], table a,
[alias b], table b,
…
[parse_dates]
)

Ogni componente ha un ruolo specifico:

• query è una stringa con l'istruzione select, ad esempio, "SELECT * FROM Table"
• alias x [facoltativo] rappresenta l'alias/nome della tabella a cui si fa riferimento nell'argomento successivo (è simile al funzionamento della formula LET). Se omesso, la prima tabella verrà chiamata a, la seconda b, ecc
• table x è un riferimento a un intervallo di celle o a una tabella Excel, ad esempio, A1:C20 o Table1[#Tutti]
• parse_dates [facoltativo] specifica quali colonne devono essere interpretate come valori di data/ora. Inserisci i nomi delle colonne come una stringa separata da virgole, ad esempio, "date,_at". Puoi anche inserire parti dei nomi delle colonne, ad esempio, "date", trasformerà le colonne updated_date, as_of_date, ecc
• NULL: le celle vuote vengono interpretate come NULL e viceversa
• Escape: se i nomi delle colonne utilizzano spazi, racchiudili tra parentesi quadre in questo modo: "SELECT [] FROM a"

La parte teorica, se non hai mai visto SQL, può essere poco immediata. Vediamo quindi qualche esempio pratico. Se volessimo prendere tutti i dati da una tabella chiamata TBL_PESI, SQLOOKUP sarebbe così:

=SQLOOKUP("SELECT * FROM a";TBL_PESI[#Tutti])

In sostanza, stiamo prendendo tutto ciò che abbiamo in TBL_PESI, includendo le intestazioni. Nota come i comandi siano fra virgolette ", a differenza di quanto vediamo con le formule standard in Excel.

Facciamo qualche altro esempio.

Come effettuare una selezione dei dati in Excel con SQL e SQLookup

La prime operazioni fondamentali da fare con SQL sono quelle che permettono di richiamare i dati ed eseguire dei semplici filtri. In questo caso, vogliamo ottenere gli orsi che abbiano un peso superiore a 110 KG:

=SQLOOKUP(
"SELECT *
FROM tp
WHERE Peso>110";
"tp";TBL_PESI[#Tutti]
)

Notiamo intanto che anche in questo caso (come già visto con Python in Excel) è possibile utilizzare la barra della formula come un vero e proprio editor per il codice e scrivere "in verticale". Diamo un occhio alle parole-chiave principali utilizzate per la formulazione di una query:

• FROM: indica la tabella da cui richiamare i dati, in questo caso tp
• SELECT: seleziona i campi (l'asterisco * indica che li consideriamo tutti)
• WHERE introduce il filtro sulle righe, utilizzando le intestazioni di colonna
• l'ultima riga è l'alias cioè il nome che attribuiamo alla tabella e che utilizziamo nella scrittura del codice. Abbiamo quindi tp che corrisponde a TBL_PESI[#Tutti], ovvero all'intera tabella TBL_PESI

Come noterai, nonn è necessario immettere argomenti vuoti, quando si salta un argomento facoltativo.

Come fare una Inner Join con SQL e SQLookup in Excel

Una Inner Join è una funzione, in SQL, che unisce delle tabelle (join), considerando solo le righe che hanno una corrispondenza (inner). In mancanza di una corrispondenza, le righe non appariranno nel set di risultati. Questa è una versione, via codice, di un'operazione quale il CERCA.VERT/CERCA.X o il merge di query in Power Query. Ti serve una chiave comune fra le due sorgenti per farla funzionare, altrimenti non appaiono dati utili.

In questo caso vogliamo unire i dati di due tabelle, una chiamata TBL_PESI e una chiamata TBL_NUM. Ecco un semplice esempio per dimostrare come funziona una Inner Join:

=SQLOOKUP(
"SELECT tp.Specie, tp.Peso, tn.Numero, tn.Dimensione
FROM tn
INNER JOIN tp ON tp.Specie = tn.Specie
WHERE tn.Specie IS NOT NULL";
"tp";TBL_PESI[#Tutti];
"tn";TBL_NUM[#Tutti]
)

Vediamola nel dettaglio:

• tn corrisponde a TBL_NUM
• tp corrisponde a TBL_PESI
• Il FROM tn indica che partiamo da TBL_NUM, come sorgente
• Facciamo poi una INNER JOIN utilizzando la chiave comune, che è Specie per entrambi i dataset, questo si nota da tp.Specie=tn.Specie
• Inseriamo una condizione WHERE per indicare che ci interessa solo quando la specie in tn non è vuota
• Selezioniamo poi le colonne che ci servono: tp.Specie, tp.Peso, tn.Numero, tn.Dimensione

Avremo quindi un risultato finale che è un aggregato delle due tabelle precedenti.

Come effettuare un GROUP BY con SQL e SQLookup in Excel

GROUP BY è una funzione fondamentale in SQL e ha trovato, di recente, il suo corrispettivo in Excel con la funzione RAGGRUPPAPER / GROUPBY. Questa funzione permette di riassumere i dati velocemente, raggruppandoli a seconda di alcune condizioni. In Power Query, questa operazione può essere fatta direttamente cliccando su Raggruppa per nella schermata Home. La funzione GROUP BY, comunque, viene utilizzata per aggregare i dati che hanno gli stessi valori in uno specifico campo. I casi tipici di utilizzo sono calcoli relativamente semplici, quali la somma, la media, il minimo, il massimo e il conteggio di una specifica categoria.

In questo caso, prendiamo una tabella con vari orsi che vogliamo riassumere. In particolare, vogliamo vedere il numero totale di orsi per ogni tipologia (Grizzly, Malese, Panda Gigante, Polare):

=SQLOOKUP(
"SELECT Specie,SUM(Numero) as Totale
FROM tng
GROUP BY Specie";
"tng";TBL_NUM_GRANDE[#Tutti]
)

Vediamo anche in questo caso come possiamo leggere il codice:

• tng è la nostra tabella chiamata TBL_NUM_GRANDE
• vogliamo ottenere due colonne, che sono Specie e la somma (SUM) della colonna Numero, che andrà evidenziata come Totale nell'intestazione
• Il GROUP BY raggruppa per Specie, rendendo il nostro calcolo sensato. In genere, si raggruppa per un elemento testuale che possa identificare velocemente una categoria specifica, come, in questo caso, gli orsi

Non stiamo che scalfendo la superficie di questo strumento, che sono certo ti darà molte soddisfazioni se hai già abitudine a usare SQL nel tuo lavoro di tutti giorni.

Come è stato creato SQLOOKUP: xlwings Lite

SQLOOKUP è stato creato con xlwings Lite, che permette di creare componenti aggiuntivi moderni di Excel usandoPython. Per l'utilizzo di questi componenti non è richiesta l'installazione di Python, né sui computer degli utenti finali né sul server. Più in dettaglio, xlwings Lite è basato su Pyodide, che è una distribuzione Python per WebAssembly (Wasm). WebAssembly è una tecnologia che consente di eseguire linguaggi di programmazione come Python direttamente nei browser Web a velocità quasi nativa. Per maggiori informazioni, consulta la documentazione di xlwings Lite.

Conclusioni

In questa guida abbiamo visto come utilizzare SQL in Excel, utilizzando principalmente due strumenti:

• Power Query, che permette di utilizzare SQL all'interno di sorgenti dati specifiche
• SQLookup, il componente che consente di interrogare i dati nella cartella di lavoro di Excel usando SQL.

Anche se varie logiche di SQL sono replicate in Excel con funzioni native, quali RAGGRUPPAPER oppure FILTRO, penso che usare SQL possa essere un'alternativa interessante per compiere azioni complesse quali join o simili. Dipende molto dall'utilizzatore, dato che un utente medio in Excel magari non ha necessità nè conoscenza di SQL - ma credo che SQL sia una skill importante da avere per chi lavora con i dati e che sia interessante sperimentare direttamente sui nostri file Excel, anzichè utilizzare altri ambienti di lavoro!