Assemblaggio di tabelle da diversi file Excel con Power Query

Formulazione del problema

Diamo un'occhiata a una bella soluzione per una delle situazioni molto standard che la maggior parte degli utenti di Excel prima o poi deve affrontare: è necessario raccogliere rapidamente e automaticamente i dati da un gran numero di file in un tavolo finale. 

Supponiamo di avere la seguente cartella, che contiene diversi file con i dati delle filiali:

Il numero di file non ha importanza e potrebbe cambiare in futuro. Ogni file ha un foglio denominato Venditedove si trova la tabella dati:

Il numero di righe (ordini) nelle tabelle, ovviamente, è diverso, ma l'insieme di colonne è standard ovunque.

Compito: raccogliere i dati da tutti i file in un libro con successivo aggiornamento automatico quando si aggiungono o eliminano file di città o righe nelle tabelle. Secondo la tabella consolidata finale, sarà quindi possibile costruire eventuali report, tabelle pivot, dati di filter-sort, ecc. L'importante è essere in grado di raccogliere.

Selezioniamo le armi

Per la soluzione, abbiamo bisogno dell'ultima versione di Excel 2016 (la funzionalità necessaria è già integrata per impostazione predefinita) o versioni precedenti di Excel 2010-2013 con il componente aggiuntivo gratuito installato Power Query di Microsoft (scaricalo qui). Power Query è uno strumento super flessibile e super potente per caricare i dati in Excel dal mondo esterno, quindi rimuoverli ed elaborarli. Power Query supporta quasi tutte le origini dati esistenti, dai file di testo a SQL e persino Facebook 🙂

Se non hai Excel 2013 o 2016, non puoi leggere oltre (scherzo). Nelle versioni precedenti di Excel, un'operazione del genere può essere eseguita solo programmando una macro in Visual Basic (che è molto difficile per i principianti) o con una copia manuale monotona (che richiede molto tempo e genera errori).

Passaggio 1. Importa un file come esempio

Innanzitutto, importiamo i dati da una cartella di lavoro come esempio, in modo che Excel "raccolga l'idea". Per fare ciò, crea una nuova cartella di lavoro vuota e...

• se hai Excel 2016, apri la scheda Dati e poi Crea query – Da file – Da libro (Dati — Nuova query- Da file — Da Excel)
• se hai Excel 2010-2013 con il componente aggiuntivo Power Query installato, apri la scheda Power Query e selezionalo Da file – Da libro (Da file — Da Excel)

Quindi, nella finestra che si apre, vai nella nostra cartella con i rapporti e seleziona uno qualsiasi dei file della città (non importa quale, perché sono tutti tipici). Dopo un paio di secondi, dovrebbe apparire la finestra del Navigatore, dove è necessario selezionare il foglio di cui abbiamo bisogno (Vendite) sul lato sinistro, e il suo contenuto verrà visualizzato sul lato destro:

Se fai clic sul pulsante nell'angolo inferiore destro di questa finestra Scaricare (Caricare), la tabella verrà immediatamente importata nel foglio nella sua forma originale. Per un singolo file, va bene, ma dobbiamo caricare molti di questi file, quindi andremo in modo leggermente diverso e faremo clic sul pulsante Correzione (Edit). Successivamente, l'editor di query di Power Query dovrebbe essere visualizzato in una finestra separata con i nostri dati dal libro:

Questo è uno strumento molto potente che ti permette di "finire" la tabella alla vista di cui abbiamo bisogno. Anche una descrizione superficiale di tutte le sue funzioni richiederebbe un centinaio di pagine, ma, se molto brevemente, utilizzando questa finestra è possibile:

• filtra i dati non necessari, le righe vuote, le righe con errori
• ordina i dati per una o più colonne
• sbarazzarsi della ripetizione
• dividere il testo appiccicoso per colonne (per delimitatori, numero di caratteri, ecc.)
• mettere in ordine il testo (rimuovere spazi extra, correggere maiuscole e minuscole, ecc.)
• convertire i tipi di dati in ogni modo possibile (trasformare numeri come il testo in numeri normali e viceversa)
• trasporre (ruotare) le tabelle ed espandere le tabelle incrociate bidimensionali in quelle piatte
• aggiungi colonne aggiuntive alla tabella e usa formule e funzioni in esse usando il linguaggio M integrato in Power Query.
• ...

Ad esempio, aggiungiamo una colonna con il nome testuale del mese alla nostra tabella, in modo che in seguito sia più semplice creare report di tabelle pivot. Per fare ciò, fare clic con il pulsante destro del mouse sull'intestazione della colonna quandoe selezionare il comando Colonna duplicata (Colonna duplicata), quindi fare clic con il pulsante destro del mouse sull'intestazione della colonna duplicata visualizzata e selezionare Comandi Trasforma – Mese – Nome del mese:

Dovrebbe essere formata una nuova colonna con i nomi di testo del mese per ogni riga. Facendo doppio clic sull'intestazione di una colonna, puoi rinominarla da Data di copia ad un più comodo Mese, per esempio.

Se in alcune colonne il programma non ha riconosciuto correttamente il tipo di dati, puoi aiutarlo facendo clic sull'icona del formato sul lato sinistro di ciascuna colonna:

Puoi escludere righe con errori o righe vuote, nonché gestori o clienti non necessari, utilizzando un semplice filtro:

Inoltre, tutte le trasformazioni eseguite sono fissate nel pannello di destra, dove possono sempre essere ripristinate (croce) o modificarne i parametri (ingranaggio):

Leggero ed elegante, vero?

Passaggio 2. Trasformiamo la nostra richiesta in una funzione

Per ripetere successivamente tutte le trasformazioni dei dati effettuate per ogni libro importato, dobbiamo convertire la nostra richiesta creata in una funzione, che verrà poi applicata, a sua volta, a tutti i nostri file. Per fare questo è in realtà molto semplice.

Nell'editor di query, vai alla scheda Visualizza e fai clic sul pulsante Editor avanzato (Visualizza — Editor avanzato). Dovrebbe aprirsi una finestra in cui tutte le nostre azioni precedenti verranno scritte sotto forma di codice nel linguaggio M. Tieni presente che il percorso del file che abbiamo importato per l'esempio è codificato nel codice:

Ora facciamo un paio di aggiustamenti:

Il loro significato è semplice: la prima riga (percorso file)=> trasforma la nostra procedura in una funzione con un argomento percorso del filee sotto cambiamo il percorso fisso al valore di questa variabile. 

Tutto. Clicca su Fine e dovrebbe vedere questo:

Non temere che i dati siano scomparsi, infatti è tutto a posto, tutto dovrebbe assomigliare a questo 🙂 Abbiamo creato con successo la nostra funzione personalizzata, dove l'intero algoritmo per l'importazione e l'elaborazione dei dati viene ricordato senza essere legato a un file specifico . Resta da dargli un nome più comprensibile (ad esempio getData) nel pannello a destra nel campo Nome e puoi mietere Home — Chiudi e scarica (Home — Chiudi e carica). Tieni presente che il percorso del file che abbiamo importato per l'esempio è codificato nel codice. Tornerai alla finestra principale di Microsoft Excel, ma sulla destra dovrebbe apparire un pannello con la connessione creata alla nostra funzione:

Passaggio 3. Raccolta di tutti i file

Tutta la parte più difficile è dietro, resta la parte piacevole e facile. Vai alla scheda Dati – Crea query – Da file – Da cartella (Dati — Nuova query — Da file — Da cartella) o, se hai Excel 2010-2013, in modo simile alla scheda Power Query. Nella finestra che appare, specifica la cartella in cui si trovano tutti i nostri file di città di origine e fai clic OK. Il passaggio successivo dovrebbe aprire una finestra in cui verranno elencati tutti i file Excel trovati in questa cartella (e le sue sottocartelle) e i dettagli per ciascuno di essi:

Clicchi Cambiamento di (Edit) e di nuovo entriamo nella familiare finestra dell'editor di query.

Ora dobbiamo aggiungere un'altra colonna alla nostra tabella con la nostra funzione creata, che "estrarrà" i dati da ogni file. Per fare ciò, vai alla scheda Aggiungi colonna – Colonna personalizzata (Aggiungi colonna — Aggiungi colonna personalizzata) e nella finestra che appare, accedi alla nostra funzione getData, specificando come argomento il percorso completo di ogni file:

Dopo aver fatto clic su OK la colonna creata dovrebbe essere aggiunta alla nostra tabella a destra.

Ora eliminiamo tutte le colonne non necessarie (come in Excel, usando il tasto destro del mouse – Rimuovere), lasciando solo la colonna aggiunta e la colonna con il nome del file, perché questo nome (più precisamente la città) sarà utile avere nel totale dei dati per ogni riga.

E ora il "momento wow" - clicca sull'icona con le sue frecce nell'angolo in alto a destra della colonna aggiunta con la nostra funzione:

... deselezionare Usa il nome della colonna originale come prefisso (Utilizza il nome della colonna originale come prefisso)e fare clic su OK. E la nostra funzione caricherà ed elaborerà i dati da ogni file, seguendo l'algoritmo registrato e raccogliendo tutto in una tabella comune:

Per una bellezza completa, puoi anche rimuovere le estensioni .xlsx dalla prima colonna con i nomi dei file, sostituendo standard con "niente" (fai clic con il pulsante destro del mouse sull'intestazione della colonna - Sostituire) e rinomina questa colonna in Città. E correggi anche il formato dei dati nella colonna con la data.

Tutto! Clicca su Home – Chiudi e carica (Home — Chiudi e carica). Tutti i dati raccolti dalla query per tutte le città verranno caricati sul foglio Excel corrente nel formato "tabella intelligente":

La connessione creata e la nostra funzione di assemblaggio non devono essere salvate separatamente in alcun modo: vengono salvate insieme al file corrente nel solito modo.

In futuro, con eventuali modifiche nella cartella (aggiunta o rimozione di città) o nei file (modifica del numero di righe), sarà sufficiente fare clic con il tasto destro direttamente sulla tabella o sulla query nel pannello di destra e selezionare il comando Aggiorna e salva (Ricaricare) – Power Query "ricostruirà" nuovamente tutti i dati in pochi secondi.

PS

Emendamento. Dopo gli aggiornamenti di gennaio 2017, Power Query ha imparato come raccogliere le cartelle di lavoro di Excel da solo, ovvero non è più necessario creare una funzione separata: avviene automaticamente. Pertanto, il secondo passaggio di questo articolo non è più necessario e l'intero processo diventa notevolmente più semplice:

1. Scegli Crea richiesta – Da file – Da cartella – Seleziona cartella – OK
2. Dopo che viene visualizzato l'elenco dei file, premere Cambiamento di
3. Nella finestra dell'editor di query, espandi la colonna Binary con una doppia freccia e seleziona il nome del foglio da prendere da ogni file

E questo è tutto! Canzone!

• Riprogettazione della tabella incrociata in una tabella piatta adatta per la costruzione di tabelle pivot
• Creazione di un grafico a bolle animato in Power View
• Macro per assemblare fogli da diversi file Excel in uno