Contenuti
Formulazione del problema
Come dati di input, abbiamo un file Excel, dove uno dei fogli contiene diverse tabelle con dati di vendita della seguente forma:
Nota che:
- Tabelle di diverse dimensioni e con diversi insiemi di prodotti e regioni in righe e colonne senza alcun ordinamento.
- È possibile inserire righe vuote tra le tabelle.
- Il numero di tabelle può essere qualsiasi.
Due presupposti importanti. Si presume che:
- Sopra ogni tabella, nella prima colonna, c'è il nome del manager di cui la tabella illustra le vendite (Ivanov, Petrov, Sidorov, ecc.)
- I nomi delle merci e delle regioni in tutte le tabelle sono scritti allo stesso modo, con una precisione maiuscola.
L'obiettivo finale è quello di raccogliere i dati da tutte le tabelle in un'unica tabella normalizzata piatta, conveniente per analisi successive e costruire un riepilogo, ad esempio in questa:
Passaggio 1. Connettiti al file
Creiamo un nuovo file Excel vuoto e selezioniamolo nella scheda Dati Comando Ottieni dati - Da file - Da libro (Dati — Da file — Da cartella di lavoro). Specificare la posizione del file sorgente con i dati di vendita e quindi nella finestra del navigatore selezionare il foglio di cui abbiamo bisogno e fare clic sul pulsante Converti dati (Trasforma dati):
Di conseguenza, tutti i dati da esso dovrebbero essere caricati nell'editor di Power Query:
Passaggio 2. Pulisci la spazzatura
Elimina i passaggi generati automaticamente tipo modificato (Tipo modificato) и Intestazioni elevate (Intestazioni promosse) ed elimina le righe vuote e le righe con i totali utilizzando un filtro nullo и TOTALE dalla prima colonna. Di conseguenza, otteniamo la seguente immagine:
Passaggio 3. Aggiunta di gestori
Per capire in seguito dove sono le vendite, è necessario aggiungere una colonna alla nostra tabella, dove in ogni riga ci sarà un cognome corrispondente. Per questo:
1. Aggiungiamo una colonna ausiliaria con i numeri di riga usando il comando Aggiungi colonna – Colonna indice – Da 0 (Aggiungi colonna — Colonna indice — Da 0).
2. Aggiungi una colonna con una formula con il comando Aggiunta di una colonna – Colonna personalizzata (Aggiungi colonna — Colonna personalizzata) e introduci qui la seguente costruzione:
La logica di questa formula è semplice: se il valore della cella successiva nella prima colonna è "Prodotto", significa che ci siamo imbattuti nell'inizio di una nuova tabella, quindi visualizziamo il valore della cella precedente con il nome del gestore. In caso contrario, non visualizziamo nulla, ovvero null.
Per ottenere la cella padre con il cognome, facciamo prima riferimento alla tabella del passaggio precedente #"Indice aggiunto", quindi specificare il nome della colonna di cui abbiamo bisogno [Colonna 1] tra parentesi quadre e il numero di cella in quella colonna tra parentesi graffe. Il numero di cella sarà uno in meno rispetto a quello corrente, che prendiamo dalla colonna Indice, Rispettivamente.
3. Resta da riempire le celle vuote con nullo nomi dalle celle superiori con il comando Trasforma – Riempi – Giù (Trasforma — Riempi — Giù) ed elimina la colonna non più necessaria con gli indici e le righe con i cognomi nella prima colonna. Di conseguenza, otteniamo:
Passaggio 4. Raggruppamento in tabelle separate dai gestori
Il passaggio successivo consiste nel raggruppare le righe per ciascun manager in tabelle separate. Per fare ciò, nella scheda Trasformazione, utilizza il comando Raggruppa per (Trasforma – Raggruppa per) e nella finestra che si apre, seleziona la colonna Gestore e l'operazione Tutte le righe (Tutte le righe) per raccogliere semplicemente i dati senza applicare alcuna funzione di aggregazione a loro (somma, media, ecc.). P.):
Di conseguenza, otteniamo tabelle separate per ogni manager:
Passaggio 5: trasforma le tabelle nidificate
Ora diamo le tabelle che si trovano in ogni cella della colonna risultante Tutti i dati in forma decente.
Innanzitutto, elimina una colonna che non è più necessaria in ogni tabella direttore. Usiamo di nuovo Colonna personalizzata linguetta Trasformazione (Trasforma — Colonna personalizzata) e la seguente formula:
Quindi, con un'altra colonna calcolata, eleviamo la prima riga di ogni tabella alle intestazioni:
E infine, eseguiamo la trasformazione principale, spiegando ogni tabella usando la funzione M Table.UnpivotAltre Colonne:
I nomi delle regioni dall'intestazione andranno in una nuova colonna e otterremo una tabella normalizzata più ristretta, ma allo stesso tempo più lunga. Celle vuote con nullo sono ignorati
Eliminando le colonne intermedie non necessarie, abbiamo:
Passaggio 6 Espandi le tabelle nidificate
Resta da espandere tutte le tabelle nidificate normalizzate in un unico elenco utilizzando il pulsante con le doppie frecce nell'intestazione della colonna:
… e finalmente otteniamo ciò che volevamo:
È possibile esportare la tabella risultante in Excel utilizzando il comando Home — Chiudi e carica — Chiudi e carica in... (Home — Chiudi&Carica — Chiudi&Carica in...).
- Costruisci tabelle con intestazioni diverse da più libri
- Raccolta di dati da tutti i file in una determinata cartella
- Raccolta dei dati da tutti i fogli del libro in un'unica tabella