Prima o poi, è probabile che chiunque usi excel in maniera intensiva, si trovi a dover affrontare il problema di unire più basi dati e consolidarle ad esempio in una singola tabella. Nello specifico, potremmo dire di dover effettura una unione di tabelle, una dopo l'altra.

Oggi voglio quindi condividere con voi un metodo che ho usato in passato per alcuni progetti: potete scaricare qui il file e seguire sotto per alcune spiegazioni.

exercise

Alcune note prima di iniziare:

  • Usando excel in inglese, spero di avervi tradotto correttamente le formule che vedrete nel vostro excel in italiano.
  • In excel, mi piace usare molto le tabelle e molto! Ovviamente il metodo che ho usato può essere adattato anche per range specifici, ma trovo le tabelle una soluzione più efficate ed elegante.
  • Questo è un esempio di base, il che vuol dire che non tiene in considerazioni casi in cui è necessario eliminare ad esempio i doppioni. Prevede inoltre tabelle con le stesse colonne (nome e cognome) così da rendere l'unione di esempio più semplice da comprendre.

Ok, procediamo!

Tabella di aiuto

La chiave è usare una tabella di Aiuto dove annotiamo tramite formule, quante righe ha ogni tabella e quale è la riga iniziale per ciascuna.
Vediamo più nel dettaglio le formule usate.

SUGGERIMENTO: potete controllare gli step di calcolo per ogni formula andando su Formule > Verifica Formula

NomeTabella > qui inseriamo manualmente il nome che abbiamo dato alle nostre tabelle. Una per riga.

Righe > =RIGHE(INDIRETTO([@NomeTabella]))
spiegazione: calcola il numero di righe di ogni singola tabella.
Restituisce il riferimento (INDIRETTO) specificato da una stringa di testo presa dal nome della tabella (NomeTabella) presente sulla stessa riga (@) e calcola il numero di righe (RIGHE). In questo esempio "NomeTabella" rimanda a "DipartimentoA" che occupa il range "B9:C11" ed equivale quindi a 3 righe.

RigaIniziale > =SOMMA(SCARTO([Righe];-1;0;RIGA()-RIGA(Aiuto[#Headers])))+1
spiegazione: calcola il numero di riga (progressivo) iniziale per ogni tabella.
La formula sembra complessa, ma in realtà somma l'altezza in numero di righe delle tabelle precedenti ed aggiunge +1

Tabella Unione

Ora che abbiamo definito la nostra tabella di aiuto, possiamo procedere a compilare la nostra tabella unione, con qualche pre-calcolo.

RigaID > =RIGA(Unione[@])-RIGA(Unione[[#Headers];[RigaID]])
spiegazione: un metodo semplice per numerare progressivamente le righe in una tabella (1, 2, 3, 4, 5, etc.)

Tabella > =CERCA.VERT([@RigaID];Aiuto;3;VERO)
spiegazione: cerca il NomeTabella nella tabella "Aiuto" per la RigaID appena calcolata.
E' interessante notare l'uso di VERO nella formula CERCA.VERT in quanto normalmente si usa FALSO per trovare una corrispondenza esatta. In questo esempio per la RigaID 2, la formula cerca il valore 2 nella prima colonna della tabella "Aiuto": con FALSO ci restituirebbe un errore N/A, con VERO ci restituisce il valore approssimativo che è 1. E siccome chiediamo la terza colonna, il valore finale è "DipartimentoA".

Riga > =[@RigaID]-CERCA.VERT([@RigaID];Aiuto;1;VERO)+1 spiegazione: calcola il numero di righe per ogni tabella.
In questo esempio, la tabella "DipartimentoA" è composta da 3 righe, quindi nella tabella Unione abbiamo 3 righe con 1, 2, 3.

Nome e Cognome > =INDICE(INDIRETTO([@Tabella]);[@Riga];1) and =INDICE(INDIRETTO([@Tabella]);[@Riga];2)
spiegazione: restituisce il valore della cella nella riga/colonna della tabella corrispondente.
Il nome della tabella e il numero di riga lo abbiamo appena calcolato, il numero della colonna è quel 1 (nome) e 2 (cognome) nella formula.

Fatto! Abbiamo adesso tutte e 3 le nostre tabelle unite in un'unica tabella. Provate ad aggiungere dati alle singole tabelle: trascinando il range delle tabelle Aiuto e Unione i valori si aggiorneranno automaticamente. Lo stesso approccio è anche definito in questo articolo (in inglese) che è stato tempo fa la mia base di partenza.

Fatemi sapere cosa ne pensate ed i vostri approcci.