Di recente ho ricevuto una domanda da un lettore sulla combinazione di più fogli di lavoro nella stessa cartella di lavoro in un unico foglio di lavoro.
Gli ho chiesto di usare Power Query per combinare fogli diversi, ma poi ho capito che per qualcuno che non conosce Power Query, farlo può essere difficile.
Quindi ho deciso di scrivere questo tutorial e mostrare i passaggi esatti per combinare più fogli in un'unica tabella utilizzando Power Query.
Di seguito un video in cui mostro come combinare i dati da più fogli/tabelle utilizzando Power Query:
Di seguito sono riportate le istruzioni scritte su come combinare più fogli (nel caso in cui si preferisca il testo scritto rispetto al video).
Nota: Power Query può essere usato come componente aggiuntivo in Excel 2010 e 2013 ed è una funzionalità integrata da Excel 2016 in poi. In base alla tua versione, alcune immagini potrebbero avere un aspetto diverso (le acquisizioni di immagini utilizzate in questo tutorial provengono da Excel 2016).
Combina dati da più fogli di lavoro usando Power Query
Quando si combinano dati da fogli diversi utilizzando Power Query, è necessario disporre dei dati in una tabella di Excel (o almeno in intervalli denominati). Se i dati non sono in una tabella Excel, il metodo mostrato qui non funzionerebbe.
Supponiamo di avere quattro fogli diversi: Est, Ovest, Nord e Sud.
Ciascuno di questi fogli di lavoro ha i dati in una tabella Excel e la struttura della tabella è coerente (ovvero, le intestazioni sono le stesse).
Clicca qui per scaricare i dati e seguire.
Questo tipo di dati è estremamente facile da combinare utilizzando Power Query (che funziona molto bene con i dati nella tabella Excel).
Affinché questa tecnica funzioni al meglio, è meglio avere nomi per le tabelle Excel (funziona anche senza, ma è più facile da usare quando le tabelle sono nominate).
Ho dato alle tabelle i seguenti nomi: Dati_Est, Dati_Ovest, Dati_Nord e Dati_Sud.
Ecco i passaggi per combinare più fogli di lavoro con tabelle di Excel utilizzando Power Query:
- Vai alla scheda Dati.
- Nel gruppo Ottieni e trasforma dati, fai clic sull'opzione "Ottieni dati".
- Vai all'opzione "Da altre fonti".
- Fai clic sull'opzione "Query vuota". Verrà aperto l'editor di Power Query.
- Nell'editor di query, digita la seguente formula nella barra della formula: =Eccellere.Cartella di lavoro attuale(). Si noti che le formule di Power Query fanno distinzione tra maiuscole e minuscole, quindi è necessario utilizzare la formula esatta come indicato (altrimenti si otterrà un errore).
- Premi il tasto Invio. Questo ti mostrerà tutti i nomi delle tabelle nell'intera cartella di lavoro (ti mostrerà anche gli intervalli e/o le connessioni denominati nel caso in cui esista nella cartella di lavoro).
- [Passaggio facoltativo] In questo esempio, voglio combinare tutte le tabelle. Se desideri combinare solo tabelle Excel specifiche, puoi fare clic sull'icona a discesa nell'intestazione del nome e selezionare quelle che desideri combinare. Allo stesso modo, se sono stati denominati intervalli o connessioni e si desidera solo combinare tabelle, è possibile rimuovere anche tali intervalli denominati.
- Nella cella di intestazione Contenuto, fare clic sulla freccia a doppia punta.
- Seleziona le colonne che vuoi combinare. Se vuoi combinare tutte le colonne, assicurati che (Seleziona tutte le colonne) sia selezionato.
- Deseleziona l'opzione "Usa il nome della colonna originale come prefisso".
- Fare clic su OK.
I passaggi precedenti combinerebbero i dati di tutti i fogli di lavoro in un'unica tabella.
Se guardi da vicino, troverai che l'ultima colonna (più a destra) ha il nome delle tabelle Excel (East_Data, West_Data, North_Data e South_Data). Questo è un identificatore che ci dice quale record proviene da quale tabella Excel. Questo è anche il motivo per cui ho detto che è meglio avere nomi descrittivi per le tabelle di Excel.
Ecco alcune modifiche che puoi apportare ai dati combinati in Power Query stesso:
- Trascina e posiziona la colonna Nome all'inizio.
- Rimuovi "_Data" dalla colonna del nome (quindi ti rimangono Est, Ovest, Nord e Sud nella colonna del nome). Per fare ciò, fare clic con il pulsante destro del mouse sull'intestazione Nome e fare clic su Sostituisci valori. Nella finestra di dialogo Sostituisci valori, sostituire _Data con uno spazio vuoto.
- Modifica la colonna Dati per mostrare solo le date (e non l'ora). Per fare ciò, fai clic sull'intestazione della colonna Data, vai alla scheda "Trasforma" e modifica il tipo di dati in Data.
- Rinominare la query in ConsolidatedData.
Ora che hai i dati combinati di tutti i fogli di lavoro in Power Query, puoi caricarli in Excel, come una nuova tabella in un nuovo foglio di lavoro.
Per fare questo. seguire i passaggi seguenti:
- Fare clic sulla scheda "File".
- Fare clic su Chiudi e carica in.
- Nella finestra di dialogo Importa dati, selezionare le opzioni Tabella e Nuovo foglio di lavoro.
- Fare clic su OK.
I passaggi precedenti combinerebbero i dati di tutti i fogli di lavoro e fornirebbero i dati combinati in un nuovo foglio di lavoro.
Un problema che devi risolvere quando usi questo metodo
Nel caso in cui tu abbia utilizzato il metodo sopra per combinare tutte le tabelle nella cartella di lavoro, è probabile che tu debba affrontare un problema.
Vedi il numero di righe dei dati combinati - 1304 (che è giusto).
Ora, se aggiorno la query, il numero di righe cambia in 2607. Aggiorna di nuovo e cambierà in 3910.
Ecco il problema.
Ogni volta che si aggiorna la query, vengono aggiunti tutti i record nei dati originali ai dati combinati.
Nota: affronterai questo problema solo se hai utilizzato Power Query per combinare TUTTI I TAVOLI EXCEL nella cartella di lavoro. Nel caso in cui hai selezionato tabelle specifiche da combinare, non dovrai affrontare questo problema.Comprendiamo la causa di questo problema e come correggerlo.
Quando aggiorni una query, questa torna indietro e segue tutti i passaggi che abbiamo eseguito per combinare i dati.
Nel passaggio in cui abbiamo usato la formula =Excel.CurrentWorkbook(), ci ha fornito un elenco di tutti i tavoli. Questo ha funzionato bene la prima volta perché c'erano solo quattro tavoli.
Ma quando aggiorni, ci sono cinque tabelle nella cartella di lavoro, inclusa la nuova tabella inserita da Power Query in cui abbiamo i dati combinati.
Quindi, ogni volta che aggiorni la query, oltre alle quattro tabelle Excel che vogliamo combinare, aggiunge anche la tabella della query esistente ai dati risultanti.
Questa si chiama ricorsione.
Ecco come risolvere questo problema.
Dopo aver inserito =Excel.CurrentWorkbook() nella barra della formula di Power Query e aver premuto invio, si ottiene un elenco di tabelle Excel. Per assicurarti di poter combinare solo le tabelle dal foglio di lavoro, devi in qualche modo filtrare solo queste tabelle che desideri combinare e rimuovere tutto il resto.
Ecco i passaggi per assicurarti di avere solo le tabelle richieste:
- Fare clic sul menu a discesa e posizionare il cursore su Filtri di testo.
- Fare clic sull'opzione Contiene.
- Nella finestra di dialogo Filtra righe, inserisci _Data nel campo accanto all'opzione "contiene".
- Fare clic su OK.
Potresti non vedere alcuna modifica nei dati, ma in questo modo impedirai che la tabella risultante venga aggiunta nuovamente quando la query viene aggiornata.
Nota che nei passaggi precedenti abbiamo usato "_Dati” per filtrare come abbiamo chiamato le tabelle in questo modo. Ma cosa succede se le tue tabelle non sono nominate in modo coerente. E se tutti i nomi delle tabelle fossero casuali e non avessero nulla in comune.
Ecco il modo per risolvere questo problema: usa il filtro "non è uguale" e inserisci il nome della query (che sarebbe ConsolidatedData nel nostro esempio). Ciò garantirà che tutto rimanga lo stesso e che la tabella di query risultante che viene creata venga filtrata.
A parte il fatto che Power Query rende abbastanza semplice l'intero processo di combinazione di dati da fogli diversi (o anche dallo stesso foglio), un altro vantaggio dell'utilizzo è che lo rende dinamico. Se aggiungi più record a una qualsiasi delle tabelle e aggiorni la Power Query, ti verranno automaticamente forniti i dati combinati.Nota importante: nell'esempio utilizzato in questo tutorial, le intestazioni erano le stesse. Nel caso in cui le intestazioni siano diverse, Power Query combinerà e creerà tutte le colonne nella nuova tabella. Se i dati sono disponibili per quella colonna, verranno mostrati, altrimenti mostreranno null.
Potrebbero piacerti anche i seguenti tutorial di Power Query:
- Combina dati da più cartelle di lavoro in Excel (usando Power Query).
- Come sbloccare i dati in Excel utilizzando Power Query (noto anche come Ottieni e trasforma)
- Ottieni un elenco di nomi di file da cartelle e sottocartelle (usando Power Query)
- Unisci tabelle in Excel utilizzando Power Query.
- Come confrontare due fogli/file Excel
