cuntenutu
Formulazione di u prublema
Cum'è dati di input, avemu un schedariu Excel, induve unu di i fogli cuntene parechje tavule cù dati di vendita di a forma seguente:
Nota:
- Tavule di diverse dimensioni è cù diversi setti di prudutti è regioni in fila è culonne senza alcunu ordinamentu.
- E linee in biancu ponu esse inserite trà e tavule.
- U numaru di tavule pò esse qualsiasi.
Dui ipotesi impurtanti. Si assume chì:
- Sopra ogni tavula, in a prima colonna, ci hè u nome di u manager chì vende a tavola illustra (Ivanov, Petrov, Sidorov, etc.)
- I nomi di merchenzie è regioni in tutti i tavulini sò scritti in u listessu modu - cù una precisione di casu.
L'ultimu scopu hè di cullà e dati da tutte e tavule in una tavola normalizzata piatta, cunvene per l'analisi sussegwente è custruisce un riassuntu, vale à dì in questu:
Passu 1. Connect à u schedariu
Creemu un novu schedariu Excel viotu è selezziunate nantu à a tabulazione Dati Command Get Data - From File - From Book (Dati - Da u schedariu - Da u libru di travagliu). Specificate u locu di u schedariu fonte cù dati di vendita è dopu in a finestra di u navigatore selezziunate u fogliu chì avemu bisognu è cliccate nantu à u buttone Cunvertite Dati (trasforma i dati):
In u risultatu, tutte e dati da ellu deve esse caricate in l'editore Power Query:
Passu 2. Pulite a basura
Elimina i passi generati automaticamente tipu mudificatu (Tipu cambiatu) и Intestazioni elevate (Intestazioni prumosse) è sguassate di e linee viote è e linee cù totali cù un filtru null null и TOTAL da a prima colonna. In u risultatu, avemu a seguente stampa:
Step 3. Adding managers
Per capiscenu più tardi induve sò e vendite, hè necessariu aghjunghje una colonna à a nostra tavula, induve in ogni fila ci sarà un cognome currispundente. Per questu:
1. Aghjunghjite una colonna ausiliaria cù numeri di linea cù u cumandimu Add Column - Index Column - Da 0 (Aggiungi a colonna - Colonna Index - Da 0).
2. Aghjunghjite una colonna cù una formula cù u cumandimu Aghjunghjite una Colonna - Colonna Personalizzata (Aggiungi colonna - Colonna persunalizata) è introduci quì a seguente custruzzione:
A logica di sta formula hè simplice - se u valore di a prossima cellula in a prima colonna hè "Product", allora questu significa chì avemu stumbled in u principiu di una nova tavula, cusì vedemu u valore di a cellula precedente cù u nome di u manager. Altrimenti, ùn mostremu nunda, vale à dì null.
Per uttene a cellula parente cù u cognome, avemu prima riferitu à a tavula da u passu precedente #"Indice aghjuntu", è dopu specificate u nome di a colonna chì avemu bisognu [Colonna 1] in parentesi quadrate è u numeru di cellula in quella colonna in parentesi curly. U numaru di cellula serà unu menu di l'attuale, chì pigliamu da a colonna Index, rispettivamente.
3. Resta à riempie e cellule viote cun null null nomi da cellule più altu cù u cumandamentu Trasfurmà - Riempi - Down (Trasforma - Riempi - Down) è sguassate a colonna chì ùn hè più necessariu cù indici è fila cù i cognomi in a prima colonna. In u risultatu, avemu:
Step 4. Grouping in tavule separati da i gestiunari
U prossimu passu hè di raggruppà e fila per ogni manager in tavule separati. Per fà questu, in a tabulazione Trasfurmazione, aduprate u Gruppu per cumandamentu (Trasforma - Gruppu Per) è in a finestra chì apre, selezziunate a colonna Manager è l'operazione Tutte e fila (Tutte i fila) per simplificà a cugliera di dati senza applicà alcuna funzione di aggregazione. elli (summa, media, etc.). p.):
In u risultatu, avemu tavule separati per ogni manager:
Passu 5: Trasfurmà i Tabelle Nidificate
Avà damu e tavule chì si trovanu in ogni cellula di a colonna resultanti Tutti i dati in forma decente.
Prima, sguassate una colonna chì ùn hè più necessariu in ogni tavula manager. Avemu aduprà di novu Colonna persunalizata Insignia Mutazioni (Trasforma - Colonna persunalizata) è a seguente formula:
Allora, cù una altra colonna calculata, alzamu a prima fila in ogni tavula à i tituli:
È infine, realizamu a trasfurmazioni principali - unfolding ogni tavula cù a funzione M Table.UnpivotOtherColumns:
I nomi di e regioni da l'intestazione andaranu in una nova colonna è averemu una più stretta, ma à u stessu tempu, una tavola normalizzata più longa. Cellule viote cun null null sò ignurati.
Per sguassà e colonne intermedie innecessarii, avemu:
Passu 6 Espandi i Tabelle Nidificate
Resta per espansione tutte e tavule nidificate nurmalizate in una sola lista utilizendu u buttone cù freccia doppia in l'intestazione di a colonna:
... è infine ottene ciò chì vuliamu:
Pudete esportà a tavola resultanti torna à Excel cù u cumandimu Casa — Chiudi è Carica — Chiudi è Carica in… (Home — Close&Load — Close&Load to…).
- Custruite tavule cù diverse intestazioni da parechji libri
- Raccolta di dati da tutti i schedari in un cartulare datu
- Raccolta di dati da tutti i fogli di u libru in una tavola