Costruisci tabelle con intestazioni diverse da più libri

Formulazione del problema

Abbiamo diversi file (nel nostro esempio – 4 pezzi, nel caso generale – quanti ne vuoi) in una cartella Report:

All'interno, questi file hanno il seguente aspetto:

In cui:

• La scheda tecnica di cui abbiamo bisogno è sempre chiamata Foto, ma può essere ovunque nella cartella di lavoro.
  
• Oltre il foglio Foto Ogni libro può avere altri fogli.
• Le tabelle con dati hanno un numero di righe diverso e possono iniziare con una riga diversa nel foglio di lavoro.
• I nomi delle stesse colonne in tabelle diverse possono differire (ad esempio, Quantità = Quantità = Qtà).
• Le colonne nelle tabelle possono essere disposte in un ordine diverso.

Compito: raccogliere i dati di vendita da tutti i file del foglio Foto in una tabella comune per creare successivamente un riepilogo o qualsiasi altra analisi su di essa.

Passaggio 1. Preparazione di una directory di nomi di colonne

La prima cosa da fare è preparare un libro di riferimento con tutte le opzioni possibili per i nomi delle colonne e la loro corretta interpretazione:

Convertiamo questo elenco in una tabella dinamica "intelligente" utilizzando il pulsante Formatta come tabella nella scheda Casa (Home — Formatta come tabella) o scorciatoia da tastiera Ctrl+T e caricalo in Power Query con il comando Dati: da tabella/intervallo (Dati — Da tabella/intervallo). Nelle versioni recenti di Excel, è stato rinominato in Con foglie (Dal foglio).

Nella finestra dell'editor di query di Power Query, tradizionalmente eliminiamo il passaggio Tipo modificato e aggiungi un nuovo passaggio al suo posto facendo clic sul pulsante f_xnella barra della formula (se non è visibile, puoi abilitarla nella scheda Review) e inserisci la formula lì nel linguaggio di Power Query integrato M:

=Tabella.ToRows(Fonte)

Questo comando convertirà quello caricato nel passaggio precedente Fonte tabella di riferimento in un elenco costituito da elenchi nidificati (Elenco), ciascuno dei quali, a sua volta, è una coppia di valori Era-diventato da una riga:

Avremo bisogno di questo tipo di dati un po' più tardi, quando si rinominano in massa le intestazioni di tutte le tabelle caricate.

Dopo aver completato la conversione, seleziona i comandi Home — Chiudi e carica — Chiudi e carica in... e tipo di importazione Basta creare una connessione (Home — Chiudi&Carica — Chiudi&Carica in... — Crea solo connessione) e torna su Excel.

Passaggio 2. Carichiamo tutto da tutti i file così com'è

Ora carichiamo il contenuto di tutti i nostri file dalla cartella, per ora così com'è. Scegliere le squadre Dati – Ottieni dati – Da file – Da cartella (Dati — Ottieni dati — Da file — Da cartella) e poi la cartella in cui si trovano i nostri libri di origine.

Nella finestra di anteprima, fare clic su convertire (Trasformare) or Cambiamento di (Edit):

E quindi espandere il contenuto di tutti i file scaricati (Binario) pulsante con doppie frecce nell'intestazione della colonna Contenuti:

Power Query sull'esempio del primo file (Vostok.xlsx) ci chiederà il nome del foglio che vogliamo prendere da ogni cartella di lavoro – scegli Foto e premere OK:

Dopodiché (di fatto) si verificheranno diversi eventi non evidenti all'utente, le cui conseguenze sono ben visibili nel pannello di sinistra:

1. Power Query prenderà il primo file dalla cartella (lo avremo Vostok.xlsx — vedere Esempio di file) come esempio e ne importa il contenuto creando una query Converti file di esempio. Questa query avrà alcuni semplici passaggi come Fonte (accesso al file) Navigazione (selezione del foglio) ed eventualmente alzando i titoli. Questa richiesta può caricare dati solo da un file specifico Vostok.xlsx.
   
2. Sulla base di questa richiesta verrà creata la funzione ad essa associata Converti file (indicato da una caratteristica icona f_x), dove il file di origine non sarà più una costante, ma un valore variabile, un parametro. Pertanto, questa funzione può estrarre dati da qualsiasi libro in cui inseriamo come argomento.
   
3. La funzione verrà applicata a sua volta a ciascun file (Binary) dalla colonna Contenuti – Step è responsabile di questo Chiama la funzione personalizzata nella nostra query che aggiunge una colonna all'elenco dei file Converti file con i risultati dell'importazione da ogni cartella di lavoro:

   

4. Le colonne aggiuntive vengono rimosse.
5. Il contenuto delle tabelle nidificate viene ampliato (passaggio Colonna della tabella estesa) – e vediamo i risultati finali della raccolta dei dati da tutti i libri:

   
   

Passaggio 3. Levigatura

Lo screenshot precedente mostra chiaramente che l'assemblaggio diretto "così com'è" si è rivelato di scarsa qualità:

• Le colonne sono invertite.
• Tante righe extra (vuote e non solo).
• Le intestazioni delle tabelle non vengono percepite come intestazioni e vengono mescolate con i dati.
  

Puoi risolvere tutti questi problemi molto facilmente: basta modificare la query Converti file di esempio. Tutte le modifiche che apportiamo cadranno automaticamente nella funzione Converti file associata, il che significa che verranno utilizzate in seguito durante l'importazione dei dati da ciascun file.

Aprendo una richiesta Converti file di esempio, aggiungi passaggi per filtrare le righe non necessarie (ad esempio, per colonna Column2) e alzando le intestazioni con il pulsante Usa la prima riga come intestazioni (Utilizza la prima riga come intestazioni). Il tavolo sembrerà molto meglio.

Affinché le colonne di file diversi si adattino automaticamente l'una all'altra in un secondo momento, devono avere lo stesso nome. È possibile eseguire una tale ridenominazione di massa in base a una directory creata in precedenza con una riga di codice M. Premiamo di nuovo il pulsante f_x nella barra della formula e aggiungi una funzione per modificare:

= Table.RenameColumns(#"Intestazioni elevate", Intestazioni, MissingField.Ignore)

Questa funzione prende la tabella dal passaggio precedente Intestazioni elevate e rinomina tutte le colonne al suo interno in base all'elenco di ricerca nidificato Notizie. Terzo argomento MissingField.Ignora è necessario in modo che su quelle intestazioni che sono nella directory, ma non sono nella tabella, non si verifichi un errore.

In realtà, questo è tutto.

Tornando alla richiesta Report vedremo un'immagine completamente diversa, molto più bella della precedente:

• Che cos'è Power Query, Power Pivot, Power BI e perché un utente di Excel ne ha bisogno
  
• Raccolta di dati da tutti i file in una determinata cartella
• Raccolta dei dati da tutti i fogli del libro in un'unica tabella