Quando lavoriamo in Excel su più tabelle di dati, è inevitabile ritrovarsi a dover gestire problematiche relative all’unione di queste, sia per la visualizzazione dei risultati che per l’elaborazione. Un modo per unire tabelle è quello di usare formule Excel con funzioni di ricerca e in questo post vediamo le formule per farlo nel modo più efficiente.
Cosa significa unire tabelle
Unire due tabelle significa riportare uno o più campi di informazioni di una tabella nell’altra. Cioè aggiungere alla prima tabella una o più colonne popolandole con i dati della seconda tabella.
Questo può essere fatto in modi diversi, più o meno efficienti. Per esempio il modo più efficiente è usare power query, ora chiamato Recupera e trasforma, mentre il modo tradizionale è quello di usare formule Excel con funzioni di ricerca.
In questo post vediamo come usare le formule Excel per unire tabelle, mentre dedicheremo altri post per i modi alternativi, come Power Query.
Unire due o più tabelle attraverso l’uso di formule Excel, significa aggiungere campi calcolati, cioè colonne di formule che richiamano e visualizzano i valori della seconda tabella nella prima.
Naturalmente per farlo sono necessari dei presupposti e dei requisiti. Vediamo quali sono.
Requisiti per l’uso delle formule Excel
Una base dati nasce dalla registrazione di record in strutture standardizzate, cioè tabelle di dati, o basi dati. Naturalmente quando la nostra base dati è molto grande o quando ci sono registrazioni diverse, allora non possiamo registrare tutti i dati in singola tabella.
Avremo quindi tabelle diverse che contengono dati connessi tra loro. Per esempio in azienda abbiamo un database clienti, un database fatture, un database magazzino, eccetera.
Sono tabelle diverse correlate tra di loro grazie a valori univoci che ci permettono di identificare in modo certo i singoli record, come per esempio il codice cliente, il codice prodotto, il numero fattura, eccetera.
D’altra parte il database dei clienti non riporta i dati relativi alle vendite, il database prodotto non riporta i dati dei clienti che li hanno acquistati, eccetera. Tutti queste base dati sono registrate in modo da inserire record che contengono “chiavi” univoche che si riferiscono a record di altre tabelle.
Per esempio nelle fatture abbiamo l’indicazione del codice prodotto, del codice cliente, eccetera. E quando interroghiamo i server o il gestionale usiamo queste chiavi per estrarre quello che ci interessa.
Per unire due tabelle di dati in Excel abbiamo bisogno di campi di valori univoci che permettono alle formule Excel di individuare con precisione il valore che vogliamo riportare nella tabella.
Un altro requisito è quello di avere uno strumento che individui ed estragga i dati, e contemporaneamente sappia gestire errori e lacune. Nel caso delle formule Excel parliamo delle funzioni di ricerca, nel caso di power query parliamo delle query realizzate con un linguaggio apposito, eccetera.
Un ulteriore requisito è avere a disposizione basi dati di qualità e affidabili elevata, cosa che naturalmente noi presupponiamo.
Perché unire tabelle con le formule Excel
I motivi fondamentali per cui abbiamo bisogno di unire più tabelle sono questi:
- visualizzare i dati, in una tabella Excel o di formule, per essere consultate,
- integrare i dati in un’unica tabella, che poi verrà elaborata attraverso gli appositi strumenti di analisi.
Le funzioni di ricerca possono essere usate direttamente in formule Excel che elaborano direttamente i dati individuati, ma questo non riguarda l’unione di tabelle, ma l’elaborazione dei dati.
Perché usare le formule Excel per unire tabelle di dati?
Perché è comodo e semplice, ma soprattutto perché le formule Excel sono dinamiche, cioè al variare dei dati a cui si riferiscono restituiscono dati aggiornati.
Naturalmente la dinamicità diventa massima quando usiamo le tabelle Excel e i nomi. A proposito ti invito a leggere la guida alle tabelle Excel e la guida ai nomi di Excel.
Il modo migliore per unire tabelle
È importante sottolineare che il modo migliore per unire più tabelle è sempre e solo uno: lavorare a monte per ottenere unica base dati dalla fonte principale, per esempio dal server sql aziendale.
Quindi se è possibile, prima di iniziare a unire tabelle, chiedi all’ufficio It di fornirti una base dati unica con tutti i campi che ti servono per le tue analisi o elaborazioni. Oppure chiedi al tuo consulente, oppure prova a estrarre dal gestionale una base dati grezza o più ampia che puoi elaborare.
Se invece i dati sono inseriti manualmente, o non hai la possibilità di poter lavorare a monte, o provengono da fonti diverse, allora devi usare gli strumenti a tua disposizione. Nel nostro caso le formule Excel.
Come unire tabelle con le formule Excel
In pratica si aggiunge una nuova colonna alla tabella Excel, o alla tabella di formule, e si imposta una formula usando la funzione di ricerca più adeguata all’individuazione del dato che vogliamo visualizzare nella colonna. Per esempio, possiamo usare la funzione cerca verticale per visualizzare nella tabella Excel del fatturato il costo degli articoli venduti.
Ma perché parlo di formule Excel e non semplicemente di funzioni di ricerca?
Perché come abbiamo visto nel post relativo, le funzioni di ricerca sono in realtà sempre accompagnate da altre funzioni necessarie per la gestione degli errori tipici di questo tipo di funzioni, o per renderle più flessibili e dinamiche.
Volendo schematizzare, in pratica si procede in questo modo:
- se necessario trasformi le tabelle in tabelle Excel, a proposito ti invito a leggere la guida relativa e a guardare questo tutorial,
- individui con precisione il campo della seconda tabella che vuoi riportare nella prima, prendendo nota del nome e della posizione nella tabella,
- aggiungi l’intestazione della nuova colonna, la tabella Excel si adatterà in automatico; in generale ti consiglio di usare lo stesso nome del campo di origine,
- scegli la funzione di ricerca che vuoi usare, in relazione ai diversi fattori (uso della tabella, dinamicità, modifiche future, gestione, eccetera),
- se non usi tabelle Excel, o se hai bisogno di usare riferimenti diversi da quelli impliciti delle tabelle Excel, ti invito a preimpostare i nomi degli intervalli necessari alle formule,
- imposti la formula nella prima cella, avendo cura di verificare i riferimenti; se la formula è lunga e complessa, ti invito a scriverla una porzione alla volta; per esempio prima la funzione di ricerca, poi la gestione degli errori, o le condizionalità; nelle tabella Excel la formula si propagherà in automatica per tutto il campo, mentre nelle tabelle di formule sarà necessario propagare la formula usando il doppio clic sul cursore del bordo della selezione o il classico copia e incolla.
Le funzioni di ricerca
Le funzioni di ricerca più usate nell’unione di tabelle Excel in particolare sono:
- vert().
- la coppia Indice() e Confronta().
Vediamo brevemente la sintassi di queste funzioni.
Cerca Verticale
In sintesi la funzione Cerca.Vert() permette di cercare nella prima colonna di una tabella (matrice_tabella) il valore di riferimento (valore) per individuare il record in cui la funzione cerca e restituisce il valore presente nella ennesima (indice) colonna verso destra.
La sintassi è: CERCA.VERT(valore; matrice_tabella; indice; intervallo)
I parametri della funzione sono:
valore
È il valore da cercare e può essere di qualsiasi tipo (numero, stringa, ecc). Di solito è indicato come riferimento a una cella di un campo diverso dello stesso record che contiene il valore, tipicamente una chiave univoca come il codice cliente o il numero fattura. Esempio =CERCA.VERT(B27; matrice_tabella; indice; intervallo) cerca il valore contenuto nella cella B27.
matrice_tabella
È una tabella di due o più colonne o campi di dati che nella prima colonna a sinistra contiene il valore di riferimento. Per esempio, =CERCA.VERT(B27; H2:N107; indice; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107.
indice
È il numero della colonna in matrice_tabella da cui la funzione restituisce il valore cercato. La colonna si conta da sinistra a destra da 1 all’indice. Per esempio, =CERCA.VERT(B27; H2:N107; 3; intervallo) cerca il valore della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.
intervallo
Indica se vuoi fare una ricerca a corrispondenza esatta (falso o 0) o di una corrispondenza approssimativa (Vero o 1 o omesso). Per esempio =CERCA.VERT(B27; H2:N107; 3; 0) cerca il valore esatto della cella B27 nella prima colonna della matrice H2:N107, cioè in H2:H107, e restituisce il valore della terza colonna, cioè J2:J107, sulla stessa riga che contiene il valore di riferimento in H.
Quando uniamo tabelle, quasi sempre si usa la corrispondenza esatta, in quanto vogliamo visualizzare il valore che dovrebbe essere presente.
I limiti del Cerca Verticale
Come avrai già notato Cerca.Vert() ha diversi limiti:
- cerca il valore sempre nella prima colonna della matrice,
- è probabile che restituisca errori, che devono essere gestiti;
- ricerca un valore singolo solo nella prima colonna della matrice, restituendo il primo che trova;
- l’indicazione della colonna del risultato è un numero intero, non un riferimento a un nome di campo, quindi in caso di modifiche della tabella, per esempio aggiungendo campi nella tabella a sinistra del campo indicato dall’indice, la formula necessiterà di essere corretta.
In realtà questi limiti possono essere gestiti. Vediamo come:
Cerca il valore sempre nella prima colonna della matrice
Il campo di riferimento deve essere sempre a sinistra del campo dei valori da restituire e se non è già così è necessario intervenire sulla struttura della tabella. Oppure aggiungere un campo “copia” della colonna di riferimento che però può generare complicazioni “formule ricorsive”.
È probabile che restituisca errori
È intrinseco nella natura delle funzioni di ricerca la possibilità di presentare errori, per ragioni diverse, che quindi devono essere gestiti. Come? Con funzioni apposite come Se.Errore(), oppure con la coppia Se() e Val.Errore().
Per esempio: =SE.ERRORE( CERCA.VERT( Ricerca;A2:S100;2;FALSO); “Non trovo il valore”), oppure =SE(VAL.ERRORE(CERCA.VERT(Parametri));”Non presente”;CERCA.VERT(Parametri)).
In queste formule Excel, se Cerca.Vert() non trova il valore e restituisce un errore, questo viene sostituito con una stringa o con altri valori opportuni. Per approfondire la gestione degli errori delle formule ti invito a leggere la guida relativa.
L’indicazione della colonna del risultato è un numero intero
Essendo l’indicatore del campo da restituire un numero e non il nome del campo, in caso di variazioni della tabella, la formula restituirà il valore di un campo diverso e dovrà essere corretta.
Questo può essere risolto con l’utilizzo della funzione Confronta(), che è in grado di restituire il numero di una colonna a partire dal nome del campo. Per esempio: =CONFRONTA( “ARTICOLO”; Articoli[#Intestazioni]; 0), individua il nome “ARTICOLO” tra le intestazioni di una tabella “Articoli”.
Inserire questa funzione al posto dell’indice, del numero di colonna, rende la formula resistente all’aggiunta, rimozione o spostamenti di colonne della tabella dei dati. D’altra parte questo richiede che il nome del campo non venga alterato, altrimenti la formula non funzionerà più. Quindi possiamo rendere più flessibile il Cerca.Vert, ma limitando le modifiche alle intestazioni dei campi.
Su cerca.vert() ti invito a leggere questa guida al cerca verticale, oppure a consultare la guida alle funzioni di Excel.
Indice e Confronta
Sintassi: INDICE (matrice; riga; col)
In realtà la funzione Indice() ha una doppia forma: matrice e riferimento; la prima restituisce un valore di una cella di una matrice, la seconda restituisce il riferimento.
A noi interessa la forma matrice che restituisce il valore della cella di una tabella individuandola tramite l’indicazione di riga e colonna. Per esempio =Indice( A3:E70; 23; 4) mostrerà il valore della cella localizzata alla riga 23 della colonna 4 dell’intervallo A3:E70. Facile.
Sintassi: CONFRONTA (val; matrice; corr)
Confronta() invece cerca un valore in una matrice, il primo se ce ne sono più di uno, e ne restituisce la posizione.
Per esempio Confronta(F1; C3:C70; 0) cerca il valore esatto (corr = 0) corrispondente al valore della cella F1 nell’intervallo C3:C70 e ne indica la posizione. Altrimenti restituisce un errore che dovremo gestire, in modo analogo a quanto abbiamo visto per Cerca.Vert().
Oltre a cercare il primo valore preciso indicato (terzo parametro = 0), qualunque sia l’ordine dei record della matrice, Confronta permette di cercare anche valori approssimati. Ma come abbiamo già detto, nell’unione di tabelle di solito si vuole visualizzare o riportare il valore esatto.
Indice e Confronta insieme nelle formule Excel
Quando questa coppia di funzioni viene usata insieme in una formula Excel su una tabella standard, diventa un ottimo strumento di ricerca di valori che può essere usato anche per unire più tabelle.
La sintassi è: INDICE (matrice; CONFRONTA (val; matrice; corr); col).
Come puoi vedere Confronta cerca e restituisce il valore che Indice userà come riga per individuare la cella con il valore che stiamo cercando. Indice e Confronta permettono di trovare e restituire il valore di una cella di una matrice/tabella partendo dal valore assegnato.
Per esempio in =Indice(A3:E70; Confronta(F1; C3:C70; 0); 4), la funzione Confronta() cerca e identifica la prima cella della colonna indicata “C3:C70”, che contiene il valore della cella F1, e di conseguenza identifica la riga relativa, mentre Indice() restituisce il valore della cella alla stessa riga, ma della colonna D, la quarta della matrice.
Naturalmente quando le usiamo insieme è necessario che gli intervalli indicati nei parametri delle due funzioni siano coerenti, cioè siano della stessa grandezza (es: A3:E70 e C3:C70), o potremmo generare errori diversi.
Perché usare Indice e Confronta
Questo metodo di ricerca è usato come alternativa a Cerca.Vert per diversi motivi che lo rendono preferibile. Per esempio:
- perché permette di cercare i valori non solo nella prima colonna della matrice,
- permette di gestire con più precisione l’individuazione del valore,
- indice e confronta realizzano formule dinamiche, in grado di funzionare correttamente anche dopo modifiche alla tabella dei dati come aggiunte e rimozioni di righe e colonne,
- permette di gestire valori di testo lunghi (con più di 255 caratteri),
- richiede meno risorse (memoria, dimensioni del foglio, ecc), caratteristica importante quando usiamo le formule di ricerca in tabelle molto grandi con migliaia di record.
Quando parlo di formule dinamiche, mi riferisco alla possibilità di usare la funzione Confronta anche per l’individuazione della colonna e non solo della riga della matrice su cui cerchiamo i valori.
Per esempio =INDICE( DatiProduzione[#Tutti]; CONFRONTA(A5; DatiProduzione[[#Tutti];[ARTCOD]]; 0); CONFRONTA(“Produzione”; DatiProduzione[#Intestazioni]; 0)), cerca il valore presente in A5 nella tabella Excel con nome “DatiProduzione” nel campo “Artcod” e restituisce il valore corrispondente nella colonna con titolo “Produzione”.
Dietro la relativa complessità, questa formula continuerà a lavorare nonostante le modifiche compiute sulla tabella, a meno di alterazioni dei titoli dei campi indicati (Artcod e Produzione).
Gestire gli errori di Indice e Confronta
Anche Indice e Confronta, come le altre funzioni di ricerca, richiede la gestione degli errori. In fondo se una funzione di ricerca non trova il valore indicato, non può restituirlo. Quindi anche queste funzioni vengono accompagnate da altre funzioni per la gestione degli errori come Se.Errore o Se e Val.Errore.
Per esempio: = SE.ERRORE( INDICE( DatiProduzione[#Tutti]; CONFRONTA(A5; DatiProduzione[[#Tutti];[ARTCOD]]; 0); 4); “Non trovo il valore”) in caso di valore assente, restituisce un “marcatore” invece che l’errore previsto.
Quale formula Excel usare?
Quindi quale formula devo impostare nel campo che voglio aggiungere alla mia tabella per visualizzare i dati giusti della seconda tabella?
Di base ti invito a usare le formule Excel che conosci meglio, sia come ricerca (tra Cerca Verticale e Indice e Confronta) che gestione degli errori. Mentre ti invito a scegliere solo nelle situazioni particolari in cui vengono messi alla prova i limiti delle funzioni.
Le formule Excel di base per l’unione sono:
=Se.Errore( Cerca.Vert( RIF_CELLA_CON_VALORE; INTERVALLO_TABELLA; NUMERO_COLONNA; 0); “Non trovo il valore”)
Oppure quando usi le notazioni delle tabelle Excel:
=Se.Errore( Cerca.Vert( [@RIF_COLONNA_TABEXCEL]; NOME_TABEXCEL[#Tutti]; NUMERO_COLONNA; 0); “Non trovo il valore”)
Invece con Indice e Confronta:
=Se.Errore( Indice( INTERVALLO_TABELLA; Confronta( RIF_CELLA_CON_VALORE; INTERVALLO_CAMPO_VALORE; 0); NUMERO_COLONNA ); “Non trovo il valore”)
Oppure quando usi le notazioni delle tabelle Excel:
=Se.Errore( Indice( DatiProduzione[#Tutti]; Confronta( [@RIF_COLONNA_TABEXCEL]; NOME_TABEXCEL[[#Tutti];[NOME_CAMPO]]; 0); NUMERO_COLONNA); “Non trovo il valore”)
Puoi copiarle e sostituire i termini in maiuscolo (es: INTERVALLO_TABELLA, NUMERO_COLONNA,e cc) con i riferimenti corretti, tradizionali o relativi alle tabelle Excel. Ma piuttosto ti invito a studiare e comprendere queste formule Excel per imparare a scriverle autonomamente, sia perché è molto più efficiente scriverle con l’ausilio degli strumenti di Excel per la scrittura delle formule, sia perché ti sarà utile in molte altre occasioni.
Per ripassare e approfondire la notazione delle tabelle Excel ti invito a leggere la guida relativa. Inoltre per rendere più efficienti le formule ti invito a usare i nomi al posto dei riferimenti tradizionali. Se non usi i nomi di Excel ti invito a leggere la guida relativa.
Controindicazioni dell’unione con le formule Excel
È fondamentale comprendere che quando uniamo 2 o più tabelle con formule Excel che contengono funzioni di ricerca stiamo usando strumentI che richiedono significative risorse. Ogni singola formula va a cercare un valore in un’intera tabella di dati.
Se ci limitiamo ad aggiungere una colonna con qualche centinaio formule Excel, Excel non avrà difficoltà alcuna a gestirne il calcolo. Se invece aggiungiamo una o più colonne di decine o centinaia di migliaia di formule Excel con funzioni di ricerca, il nostro foglio mostrerà un calo di efficienza, perfino rallentamenti e un aumento delle sue dimensioni.
In tal caso sconsiglio l’uso delle formule per l’unione delle basi dati e invito a lavorare a monte, o a utilizzare altri strumenti.
Alternative alle formule Excel
Quali sono le alternative alle formule Excel quando abbiamo bisogno di unire tabelle?
La prima alternativa è non doverlo fare, cioè ottenere una base dati preparata per le attività che dobbiamo fare. Questo significa estrarre i dati dal server Sql già modellati, usando la query adatta, oppure usare Power Query per fare la stessa cosa, modellando una query attraverso lo strumento Recupera e Trasforma.
Questo è il modo più efficace se i dati li otteniamo da una fonte affidabile a monte. In caso contrario, se i dati sono ottenuti da fonti diverse, che non possono fornirceli nella forma più adatto, o se i dati sono inseriti direttamente in un foglio Excel, allora è necessario lavorare in locale.
In tal caso il modo più comodo ed efficiente per unire 2 o più tabelle è usare Recupera e trasforma per connettere e modellare i dati delle tabelle. Parleremo di questo strumento e di come usarlo in un post dedicato.
Conclusioni
Unire due tabelle consiste nell’aggiunta alla tabella principale di uno o più campi, colonne di dati, presi dalla tabella secondaria, allo scopo di visualizzarli per la consultazione dell’operatore o l’elaborazione.
Usare le formule Excel è uno modo classico e relativamente semplice per farlo. Si usano formule di ricerca in grado di individuare dati nelle tabelle, attraverso l’indicazione di un valore di riferimento, una chiave univoca che permette di individuare il valore cercato.
Per esempio, possiamo individuare la quantità di pezzi a magazzino partendo dal codice articolo. Possiamo farlo per un codice articolo, quando per esempio interroghiamo il gestionale del magazzino, oppure possiamo farlo per tutti gli articoli se abbiamo bisogno di integrare l’informazione nella base dati che vogliamo successivamente analizzare. Per esempio per valutare le giacenze e i costi relativi.
Le formule non sono l’unico modo per unire due tabelle, ne sono il modo più efficiente, ma è un modo efficace e piuttosto semplice per farlo in pochi minuti. Naturalmente ti invito a provare e ad approfondire la conoscenza delle formule Excel relative.
PS: Se questa guida ti è piaciuta o ti è stato utile, condividila con amici e colleghi sui social.
Puoi farlo attraverso i pulsanti qui di seguito. Grazie.
