Collegare diverse tabelle su Excel può risultare complicato, soprattutto se i dati originari non sono particolarmente puliti o esplicativi. In questa guida, vediamo tre metodi diversi per collegare delle tabelle, utilizzando formule, Power Query e Power Pivot.
L'importante è sempre partire da tabelle con dati già puliti o sistemati, altrimenti tutte queste procedure non avranno un risultato soddisfacente. In questo caso, parliamo unicamente di come riportare, su un'unica vista, informazioni che arrivano da tabelle che possono essere in qualche modo collegate fra di loro.
In questo caso, abbiamo tre tabelle diverse:
- TBL_VENDITE presenta i dati di vendita per singola giornata e comprende le colonne da B a G. Di queste, la colonna "SKU" è la chiave per collegarla con la TBL_PRODOTTI, mentre "NATION_ID" è la chiave per collegarla con la TBL_NAZIONI. Tutte le colonne da H in poi sono inserite con formule dedicate
- TBL_PRODOTTI ha le colonne "SKU", "PRODOTTO", "COLORE", "BRAND" e "COLLEZIONE". Per collegarla alla TBL_VENDITE, dovremo lavorare sulla colonna "SKU"
- TBL_NAZIONI ha le colonne "ID", "PAESE" e "AGENTE". In questo caso, dovremo utilizzare il collegamento fra le colonne "NATION_ID" (della TBL_VENDITE) e "ID" (della TBL_NAZIONI)
In questo caso specifico è molto importante parlare di chiavi, cioè delle colonne che hanno informazioni comuni a diverse tabelle. Se non abbiamo delle colonne in comune, non potremo collegare le nostre sorgenti dati con velocità.
Chiaramente puoi applicare gli stessi concetti anche sui tuoi file, ma se vuoi seguire la guida puoi scaricare il file dedicato!
Come collegare tabelle su Excel utilizzando le formule
Esistono vari metodi per collegare delle tabelle su Excel utilizzando le formule. La scelta dipende prevalentemente dalla versione a nostra disposizione, dato che il risultato sarà esattamente lo stesso a prescindere dalle formule utilizzate.
Le tre possibilità sono:
Come collegare tabelle su Excel utilizzando CERCA.VERT
Vediamo il caso di CERCA.VERT, compatibile con tutte le versioni di Excel:
=CERCA.VERT([@SKU];TBL_PRODOTTI;2;FALSO)
La formula cerca, per ogni riga, il valore di SKU specificato nell'intera TBL_PRODOTTI, dato che SKU si trova nella prima colonna. Successivamente, la formula restituisce il valore corrispondente alla seconda colonna della tabella (2), cioè il prodotto. Il quarto argomento (FALSO) specifica che la ricerca deve essere esatta e non approssimata.
La formula precedente è uguale a =CERCA.VERT([@SKU];TBL_PRODOTTI[[SKU]:[COLLEZIONE]];2;FALSO), solo che in questo caso stiamo considerando tutta la tabella quindi non ci serve specificare tutte le colonne.
Il limite di CERCA.VERT è sia a livello di comprensione, sia a livello di possibilità di ricerca - se "SKU" fosse stata più a destra rispetto a "PRODOTTO" nella TBL_PRODOTTI, non avremmo potuto utilizzare questa formula per restituire i dati dato che va soltanto verso destra.
Come collegare tabelle su Excel utilizzando INDICE CONFRONTA
Le tabelle Excel si possono collegare anche con INDICE CONFRONTA:
=INDICE(TBL_PRODOTTI[COLORE];CONFRONTA([@SKU];TBL_PRODOTTI[SKU];0))
La versione con INDICE CONFRONTA è sicuramente più comprensibile e funzionerebbe anche nel caso trovassimo i valori da restituire sulla destra rispetto alla nostra ricerca. Nello specifico, CONFRONTA cerca il valore specificato (SKU) nella colonna "SKU" della tabella TBL_PRODOTTI e restituisce la posizione esatta (riga) in cui si trova quel valore. In questo caso, il terzo argomento (0) specifica che la ricerca deve essere esatta. INDICE, invece, recupera il valore corrispondente alla riga specificata dalla funzione CONFRONTA nella colonna "COLORE" della tabella TBL_PRODOTTI.
Più semplicemente, INDICE ci segnala il valore che desideriamo restituire, cioè "COLORE". Conoscendo il valore del singolo SKU e cercandolo nella colonna "SKU", questo viene confrontato per darne la posizione relativa. Se 1001 fosse nella terza posizione della tabella, allora INDICE ci ridarebbe il terzo valore dall'alto della colonna "COLORE" e così via.
INDICE CONFRONTA è una combinazione molto flessibile che può andare in tutte le direzioni e è compatibile con tutte le versioni di Excel. Personalmente, penso sia preferibile rispetto a CERCA.VERT.
Come collegare tabelle su Excel utilizzando CERCA.X
Vediamo ora come collegare le tabelle Excel con la funzione CERCA.X:
=CERCA.X([@SKU];TBL_PRODOTTI[SKU];TBL_PRODOTTI[BRAND];"")
CERCA.X è compatibile solamente con le versioni da Excel 2021 in poi, ma sarebbe la soluzione ideale per questo problema. In questo caso la struttura è simile a INDICE CONFRONTA, dato che come primo parametro indichiamo che la formula deve cercare il nostro valore (SKU) nella sua colonna di riferimento "SKU" della TBL_PRODOTTI. Come valore restituito, vogliamo che ci sia la colonna "BRAND" sempre della TBL_PRODOTTI. Se non trova niente, ci restituisce il valore vuoto ("").
Utilizzo CERCA.X sui miei file di lavoro dato che penso sia anche la più facile da comprendere. Evitiamo di utilizzare due formule come con INDICE CONFRONTA o di contare come con CERCA.VERT, semplificando notevolmente la comprensione finale. Purtroppo, nel caso utilizzi una versione di Excel datata, non ti sarà possibile utilizzarla.
Come collegare tabelle su Excel utilizzando Power Query
Si possono collegare le tabelle su Excel anche utilizzando Power Query. In questo caso, dovremo fare due merge di query unendo le tre tabelle in step separati.
Cominciamo cliccando su Recupera dati > Dati da altre origini > Da tabella/intervallo per ognuna delle tre tabelle.
Una volta aperte su Power Query, per il momento clicchiamo su Home > Chiudi e Carica > Chiudi e carica in... e selezioniamo Crea solo connessione. Questo perchè non ci interessa che siano nuovamente caricate su Excel, ci interessa solo poterle utilizzare per metterle poi tutte assieme tramite i vari merge di query.
Merge di query significa che stiamo unendo fra di loro query che abbiano una chiave in comune. La chiave è una colonna comune alle due tabelle, che viene utilizzata poi per collegare tutte le altre colonne delle due tabelle considerate.
Vediamo i passaggi logici che ci servono per collegare le nostre tabelle Excel con Power Query:
- Clicchiamo su Home > Merge di query > Unisci query come nuova. Questo ci permetterà di leggere facilmente la nuova tabella, dato che sarà separata dalle altre e creata da zero
- Un merge di query collegherà TBL_VENDITE e TBL_PRODOTTI utilizzando la colonna "SKU", che è la chiave comune alle due tabelle
- La nuova tabella si chiamerà TBL_VENDITE_PRODOTTI (temporaneamente, giusto per seguire il ragionamento)
- Un secondo merge di query collegherà la tabella TBL_VENDITE_PRODOTTI con la TBL_NAZIONI utilizzando la colonna "NATION_ID" e la colonna "ID", che sono le chiavi comuni alle due tabelle
- Espanderemo le due tabelle per riassumere tutte le informazioni che ci servono in una sola tabella, che si chiamerà TBL_VENDITE_COMPLESSIVA. Come vedi dall'immagine, basta cliccare sulle due frecce e poi selezionare le colonne che ci interessano. Ti consiglio sempre di non selezionare Usa il nome della colonna originale come prefisso e di non riportare nuovamente la colonna utilizzata come chiave, dato che ci sarebbe due volte (come in questo caso ID)
Il codice necessario per arrivare a questo risultato è il seguente:
let
Origine = Table.NestedJoin(TBL_VENDITE, {"SKU"}, TBL_PRODOTTI, {"SKU"}, "TBL_PRODOTTI", JoinKind.LeftOuter),
Merge_TBL_NAZIONI = Table.NestedJoin(Origine, {"NATION_ID"}, TBL_NAZIONI, {"ID"}, "TBL_NAZIONI", JoinKind.LeftOuter),
#"TBL_PRODOTTI espansa" = Table.ExpandTableColumn(Merge_TBL_NAZIONI, "TBL_PRODOTTI", {"PRODOTTO", "COLORE", "BRAND", "COLLEZIONE"}, {"PRODOTTO", "COLORE", "BRAND", "COLLEZIONE"}),
#"TBL_NAZIONI espansa" = Table.ExpandTableColumn(#"TBL_PRODOTTI espansa", "TBL_NAZIONI", {"PAESE", "AGENTE"}, {"PAESE", "AGENTE"})
in
#"TBL_NAZIONI espansa"
Se desideri una spiegazione più dettagliata dei singoli passaggi, puoi dare un'occhiata qui:
- Table.NestedJoin: questa funzione permette di eseguire un join tra due tabelle in base a una o più colonne. In questo caso, viene utilizzata per eseguire un join tra la tabella TBL_VENDITE e la tabella TBL_PRODOTTI sulla colonna "SKU". Il tipo di join utilizzato è "LeftOuter", che significa che vengono mantenute tutte le righe della tabella di sinistra anche se non c'è un corrispondente nella tabella di destra. Questa è l'origine del nostro processo su Power Query, dato che abbiamo creato un Merge di query cliccando su Unisci query come nuova
- Table.NestedJoin: questa funzione viene utilizzata nuovamente per unire la tabella risultante dal primo passaggio con la tabella TBL_NAZIONI sulla colonna "NATION_ID". Anche in questo caso, il tipo di join utilizzato è "LeftOuter". In questo modo, stiamo collegando anche la terza tabella che ci serve, recuperando "PRODOTTO", "COLORE", "BRAND" e "COLLEZIONE"
- Table.ExpandTableColumn: questa funzione viene utilizzata per espandere la colonna TBL_PRODOTTI dalla tabella risultante del secondo passaggio in modo da visualizzare i valori di tutte le colonne della tabella TBL_PRODOTTI come colonne separate nella tabella principale. La stessa procedura viene poi ripetuta per la TBL_NAZIONI inserita successivamente, in modo da avere tutte le informazioni in una sola tabella aggiungendo anche "PAESE" e "AGENTE"
Come collegare tabelle su Excel utilizzando Power Pivot
Un altro metodo per collegare delle tabelle su Excel è utilizzare Power Pivot. In questo caso devi verificare di averlo installato su Excel, altrimenti non potrai utilizzarlo. Se non vedi la dicitura nel menu, puoi fare riferimento a questa guida che spiega come aggiungere Power Pivot su Excel.
Se abbiamo delle tabelle, possiamo replicare quanto visto in precedenza e definire quale sia la chiave perchè le due si parlino e possano scambiarsi informazioni.
Dovremo andare su Dati > Strumenti dati > Relazioni. Vedrai che ha un'icona con 3 tabelle o database collegati fra di loro.
A questo punto dovrai definire come collegarle fra di loro. In questo caso abbiamo un collegamento fra la TBL_VENDITE e la TBL_PRODOTTI che avviene tramite la colonna "SKU", esattamente come indicato in precedenza. Devi selezionare la tabella singola e la colonna che deve essere collegata anche con l'altra in modo che la relazione funzioni correttamente.
Puoi fare la stessa operazione anche per collegare TBL_VENDITE e TBL_NAZIONI. Come vedi, le colonne possono anche avere intestazioni diverse, come in questo caso (NATION_ID e ID).
A questo punto, clicca sull'icona verde che trovi sotto Relazioni > Passa alla finestra Power Pivot.
Vedrai una nuova interfaccia sul tuo Excel, ma non ti preoccupare. A noi basta cliccare su Visualizza > Vista diagramma per visualizzare meglio il nostro modello dei dati. Il modello dei dati non è altro che una rappresentazione grafica di quanto abbiamo fatto poco fa - abbiamo collegato fra di loro le tre tabelle e ora possiamo lavorarci più facilmente.
Questo è il nostro modello dei dati, dove vediamo che ci sono due diverse connessioni:
- Da TBL_VENDITE (*) a TBL_NAZIONI (1)
- Da TBL_VENDITE (*) a TBL_PRODOTTI (1)
Questo succede perchè nella TBL_VENDITE potremmo trovare più volte sia le nazioni (potremmo vendere più volte nello stesso Paese) o i prodotti (potremmo vendere più volte lo stesso prodotto). Tuttavia, i riferimenti sia alle nazioni sia ai prodotti non cambiano da vendita a vendita, dato che saranno sempre associati nella stessa maniera: il prodotto con il codice 1002 sarà sempre una T-shirt Adidas di colore giallo, mentre la singola nazione sarà sempre associata con un solo agente. Nel caso non ti ricordassi bene quali siano le colonne di collegamento, puoi passare con il mouse sulle singole connessioni. Vedrai che le colonne inserite in Relazioni nello step precedente appariranno evidenziate, chiarendo meglio i singoli passaggi.
A questo punto, possiamo cliccare su Tabella pivot e creiamo una nostra pivot per riassumere i singoli risultati. In questo caso vogliamo sapere, per ogni brand, quale sia il totale venduto dai singoli agenti ordinando poi il totale per fatturato.
A questo punto, possiamo creare una pivot che si origina da tabelle diverse. Possiamo quindi prendere le singole colonne da TBL_NAZIONI, TBL_PRODOTTI e TBL_VENDITE. Sono tutte collegate, quindi potremo effettuare calcoli che prima erano impossibili - uno è il totale di vendita per brand, suddiviso per agente.
Come vedi, sposto "BRAND" da TBL_PRODOTTI e "AGENTE" da TBL_NAZIONI nelle Righe. Avremo quindi una suddivisione per brand e poi per agente. Inseriamo poi "TOTALE" da TBL_VENDITE e avremo inserito tre diverse colonne da tre diverse tabelle.
Il risultato sarà una pivot simile a questa, dalla quale potremo effettuare combinazioni con tutte le colonne che ci interessano. In questo caso, il procedimento è particolarmente efficiente, dato che passando per il modello dei dati avremo sicuramente la maggiore velocità di calcolo rispetto a tutti i casi precedenti.
Conclusione: i tre metodi per collegare tabelle su Excel
Fra le tre possibilità, la mia preferita in assoluto è tramite Power Query. La flessibilità è massima e, se prendiamo dati da fonti diverse, possiamo creare un processo scalabile e facile da seguire anche per un utente meno competente. Se i dati non sono eccessivi, Excel può reggere i merge di query senza grandi problemi.
Power Pivot ha grandi vantaggi sia in termini di performance sia in termini di scalabilità, ma potrebbe essere più difficile da modificare e da spiegare con facilità. La vista diagramma aiuta molto, ma avendo come output unicamente tabelle pivot o grafici, può risultare più complesso da utilizzare in campo business. Se i nostri database non sono enormi, usare Power Pivot potrebbe essere eccessivo. In caso contrario, Power Pivot è lo strumento cardine per database complessi e con oltre 1M di righe.
Veniamo poi alle formule. Se hai 365, CERCA.X è una formula di facile comprensione che puoi utilizzare in svariati casi, incluso questo. Eviterei di usare CERCA.VERT dato che ormai è superata, mentre INDICE CONFRONTA è una combinazione versatile che però non è facilissima da spiegare. Usare le formule può essere utile per risolvere un problema temporaneo, ma per creare strutture scalabili le altre due soluzioni sono decisamente superiori.
Sono un formatore e consulente esperto nell’uso e nell’insegnamento di Microsoft Excel.
Negli ultimi 3 anni ho tenuto corsi presso realtà in multinazionali come Aruba, Bridor, IMI Orton, Primadonna e SISAL, oltre a PMI e startup di diverso genere.
Realizzo corsi di formazione Excel dedicati per aziende, supporto professionisti 1:1 a distanza con call mirate e collaboro con aziende offrendo servizi di consulenza quali creazione di business plan, dashboard di vendita e non solo.
Vuoi saperne di più? Prenota una call gratuita di 15′!
