Combinare più file Excel in uno con Power Query: importare i dati da una cartella

Bentrovato sul mio blog, io sono Marco in arte Macro Braglia e, se non mi conosci, sono un esperto di Excel e di automazione aziendale, con le macro e con tante altre cose.

Quante volte ti sei trovato con i listini di fornitori diversi, il budget di ogni mese in un file separato o il report di magazzino che arriva ogni giorno nella stessa cartella, e un bel copia incolla da fare per mettere tutto insieme in un file solo??? Lo fai una volta e va bene, ma quei file continuano ad arrivare e il copia incolla va rifatto ogni volta.

Grazie a Power Query e alla funzionalità che ti mostrerò in questo articolo il flusso di importazione lo costruisci una volta sola: gli indichi la cartella, lui combina tutti i file che ci trova dentro, e da lì in poi ti basta cliccare su Aggiorna Tutti ogni volta che si aggiunge un nuovo file che abbia la stessa struttura. In questo articolo vediamo come:

1. capire quali file si possono combinare e preparare la cartella;
2. importare una cartella intera con Power Query;
3. sistemare la query nell'editor e caricarla in una tabella di Excel;
4. usare la tabella aggregata nel resto del file, con un esempio di elenco a discesa e CERCA.X;
5. aggiungere un nuovo file alla cartella e aggiornare il risultato.

Per farti vedere i passaggi uso il mio esempio, una cartella con i listini prezzi di fornitori diversi. Tu segui gli stessi passaggi sui tuoi file, che siano listini, budget mensili o report di magazzino: cambiano i nomi, il procedimento è lo stesso.

Se preferisci la versione video di questo tutorial, eccola qui sotto.

TL;DR

Metti i file con la stessa struttura in una cartella, poi in un file Excel nuovo segui il percorso Dati -> Recupera dati -> Da file -> Da cartella, seleziona la cartella e dal pulsante "Combina" scegli "Combina e trasforma dati". Nella finestra "Combina file" scegli la tabella o il foglio che contiene i dati, rinomina la query nell'editor di Power Query e clicca su "Chiudi e carica".

Quando cambiano i dati nei file di partenza o quando aggiungi un nuovo file nella cartella, vai su Dati -> Aggiorna tutti e la tabella aggregata si aggiorna da sola in automatico.

Quali file si possono combinare

Power Query combina tanti file Excel (ma anche csv, txt, xml, etc...) diversi ma, importantissimo, strutturalmente identici: stesse tabelle, stessi nomi di colonna, dati diversi. I nomi dei file invece non contano e possono essere tutti diversi.

Prima di iniziare, quindi:

1. metti in una cartella i file da combinare, e solo quelli, perché Power Query prende tutti i file che trova lì dentro;
2. controlla che in ogni file i dati stiano nello stesso posto, con le stesse colonne e gli stessi nomi di colonna.

Nel mio esempio la cartella contiene due listini prezzi di due fornitori: ListinoPincoPallino e Listino2. Dentro ogni file c'è un foglio Listino con il nome del fornitore in alto e una tabella Excel di quattro colonne, di cui una nascosta:

• il fornitore, che una formula ripete su ogni riga prendendolo dalla cella in alto (è la colonna nascosta);
• il codice del prodotto;
• il nome del prodotto;
• il prezzo di listino.

Nel primo file il fornitore è Pinco Pallino SpA, nel secondo è Caio Srl, e codici, nomi e prezzi sono completamente diversi: la struttura è la stessa, i dati no.

Importare una cartella intera con Power Query

Apri un file Excel nuovo, completamente vuoto. Power Query vive nella scheda Dati, nel gruppo "Recupera e trasforma dati".

Segui questi passaggi:

1. clicca su Dati -> Recupera dati -> Da file -> Da cartella;
2. seleziona la cartella con i tuoi file (la mia si chiama PQ combina files) e clicca su "Apri";
3. nella finestra che si apre trovi l'elenco dei file presenti nella cartella: clicca in basso sulla freccia del pulsante "Combina" e scegli "Combina e trasforma dati".

Occhio: i menu di Power Query cambiano nel tempo da una versione all'altra, quindi se le voci non sono esattamente in questa posizione cerca "Da cartella" tra le origini da file.

A questo punto parte una combinazione guidata, il cosiddetto wizard come si dice in inglese. Power Query ti chiede di scegliere un file di esempio e ti fa decidere quali dati prendere da quel file: salva queste scelte come template di importazione e le applica a tutti i file che trova nella cartella. Visto che i file sono strutturalmente identici, puoi lasciare tranquillamente "Primo file" come file di esempio.

Nel pannello a sinistra trovi gli oggetti del file di esempio, ognuno con il suo simbolo: fogli, tabelle e intervalli di celle con un nome. Nel mio file sono tre:

• Listino, che è il nome del foglio;
• la tabella con il listino;
• un intervallo di celle con un nome specifico.

Tu scegli quello che contiene i tuoi dati. Nel mio caso, seleziono la tabella del listino, che è quella che mi interessa, e clicco su "OK" in basso a destra.

Sistemare la query e caricarla in Excel

Dopo qualche secondo di elaborazione si apre l'editor di Power Query, il cuore dello strumento, dove si fanno tutte le trasformazioni dei dati.

Nella colonna di sinistra trovi una cartella chiamata Query Helper con il file di esempio, la funzione di trasformazione e il parametro del file di esempio. Sono le query di supporto che stanno dietro all'automazione e per ora puoi lasciarle dove sono. Quello che ci interessa è la query del risultato, che ha un nome automatico dato dal nome della cartella: rinominala subito con un nome che ti dica cosa contiene. Io l'ho chiamata listinoAggregato.

Nell'anteprima trovi le colonne dei tuoi file più una, la prima, aggiunta automaticamente da Power Query: il nome della sorgente, cioè il nome del file da cui arriva ogni riga. Nel mio caso le colonne sono cinque:

1. il nome della sorgente;
2. il fornitore;
3. il codice prodotto;
4. il nome del prodotto;
5. il prezzo di listino.

Il tipo di dati viene riconosciuto in automatico e lo vedi dal simbolino di fianco al nome di ogni colonna (nel mio caso testo per le prime, numero per il prezzo). La colonna con il nome della sorgente la puoi eliminare se non ti serve sapere da quale file arriva ogni riga: selezionala e premi Canc, oppure fai clic destro -> Rimuovi. Io la elimino, perché il fornitore è già scritto su ogni riga.

Per controllare che i file siano stati combinati, apri il filtro di una colonna che cambia da un file all'altro, nel mio caso il fornitore. Se compare l'avviso che l'elenco potrebbe essere incompleto, clicca su "Carica altro": devono esserci i valori di tutti i file, nel mio caso sia Pinco Pallino SpA sia Caio Srl. Chiudi il filtro con "OK" senza togliere nessuna spunta e, visto che non hai filtrato niente, puoi eliminare dai passaggi applicati quello delle righe filtrate.

Ora clicca su "Chiudi e carica". Il risultato arriva in una tabella di Excel collegata alla query, come vedi nel pannello "Query e connessioni" a destra.

Ogni volta che i dati cambieranno nei file di partenza, o si aggiungeranno nuovi file nella cartella, ti basterà cliccare su Dati -> Aggiorna tutti per aggiornare tutte le origini dei dati. Con i file in una cartella del tuo computer ci mette un attimo.

Usare la tabella aggregata nel resto del file

Ora che i dati di tutti i file sono in un punto solo, puoi usarli dove ti servono, come qualsiasi altra tabella. Ti faccio vedere un esempio con il mio listino: stai compilando un preventivo, scegli il codice del prodotto da un elenco a discesa e Excel ti restituisce il prezzo di listino.

Con i riferimenti strutturati indichi una colonna della tabella scrivendo il nome della tabella seguito dal nome della colonna tra parentesi quadre:

=NomeTabella[NomeColonna]

Nel mio file la tabella caricata si chiama Tabella_listinoAggregato e la colonna dei codici Codice Prodotto, quindi in una cella libera scrivo:

=Tabella_listinoAggregato[Codice Prodotto]

Con Excel 365 e le formule ad espansione dinamica, Excel espande in automatico l'elenco di tutti i valori della colonna. Copia questa porzione di formula, perché ti serve subito.

Seleziona la cella dove vuoi l'elenco a discesa e vai su Dati -> Convalida dati, poi in "Consenti" scegli "Elenco". Incollare il riferimento così com'è non basta: nell'origine devi racchiuderlo tra virgolette dentro la formula INDIRETTO. Con i miei nomi diventa:

=INDIRETTO("Tabella_listinoAggregato[Codice Prodotto]")

Se vuoi capire bene cosa fa questa formula, trovi qui il tutorial sulla formula INDIRETTO, e se ti interessano anche le altre strade per costruire un menu a tendina ci sono i sei modi per creare un elenco a discesa in Excel.

Io coloro la cella di giallo, così non la perdo. Con Excel 365 l'elenco ha anche la ricerca incorporata: se scrivi le prime lettere di un codice (nel mio caso XH) ti filtra i valori che iniziano così.

Nella cella di fianco recupera il valore che ti serve con CERCA.X, che cerca la voce scelta in una colonna e restituisce il valore corrispondente di un'altra colonna. Con l'elenco in J6 e i miei nomi la formula è:

=CERCA.X(J6;Tabella_listinoAggregato[Codice Prodotto];Tabella_listinoAggregato[Prezzo di Listino])

Excel cerca il codice scelto nella colonna Codice Prodotto e restituisce il valore della colonna Prezzo di Listino; gli altri argomenti puoi lasciarli vuoti. Scegli un altro codice dall'elenco e il prezzo cambia in automatico.

Aggiungere un nuovo file e aggiornare

Ultimo passaggio, quello che ti voglio far vedere con i tuoi occhi: arriva un nuovo file nella cartella e la tabella aggregata lo prende da solo.

Basta salvare il nuovo file nella cartella, con la stessa struttura degli altri, e cliccare su Dati -> Aggiorna tutti, oppure fare clic destro sulla tabella e scegliere "Aggiorna".

Per fartelo vedere ho simulato l'arrivo del listino di un terzo fornitore, Sempronio Srl: ho copiato uno dei listini nella cartella, l'ho rinominato Listino3Nuovo, ho cambiato il nome del fornitore, i codici e i nomi dei prodotti, e ho messo dei prezzi a caso con CASUALE.TRA diviso 100, poi copiati e incollati come valori con Ctrl+Alt+V e V, come un vero ninja di Excel.

Salvato e chiuso il file, torno sul listino aggregato, clic destro -> Aggiorna: le righe aumentano e trovo i prodotti di Sempronio Srl accanto a quelli di Pinco Pallino SpA e Caio Srl, senza aver toccato la query.

Ora non ti resta che prendere una cartella di file con la stessa struttura che oggi unisci a mano, importarla con Dati -> Recupera dati -> Da file -> Da cartella e, la prossima volta che arriva un file nuovo, cliccare su Aggiorna tutti e goderti il tuo report aggregato.

Tutto quello che hai visto poggia su due cose: le tabelle Excel e Power Query. In Excenziale, il mio corso Excel, hanno una sezione ciascuna, dentro un percorso che parte dalle basi e arriva all'introduzione a Power Query passando per le tabelle pivot.