Custruite tavule cù diverse intestazioni da parechji libri

Formulazione di u prublema

Avemu parechji schedari (in u nostru esempiu - 4 pezzi, in u casu generale - quant'è vo vulete) in un cartulare Rivista:

Dentru, sti schedari sò cusì:

Induve:

• A scheda di dati chì avemu bisognu hè sempre chjamata Photos, ma pò esse in ogni locu in u libru di travagliu.
  
• Al di là di u fogliu Photos Ogni libru pò avè altri fogli.
• I tavule cù dati anu un nùmeru sfarente di fila è ponu principià cù una fila diversa nantu à u fogliu di travagliu.
• I nomi di e stesse colonne in diverse tavule pò esse diffirenti (per esempiu, Quantità = Quantità = Qty).
• I culonni in tavule ponu esse disposti in un ordine diversu.

Task: raccoglie dati di vendita da tutti i schedari da u fogliu Photos in una tavola cumuna per custruì in seguitu un riassuntu o qualsiasi altra analisi nantu à questu.

Passu 1. Preparate un repertoriu di nomi di colonna

A prima cosa da fà hè di preparà un libru di riferimentu cù tutte l'opzioni pussibuli per i nomi di colonna è a so interpretazione curretta:

Avemu cunvertisce sta lista in una tavula dinamica "intelligenti" usendu u Formatu cum'è tavula buttone nantu à a tabulazione Home (Casa - Formate cum'è Table) o scorciatoia da tastiera Ctrl+T è carica in Power Query cù u cumandimu Dati - Da Table / Range (Dati - Da Table/Range). In e versioni recenti di Excel, hè statu rinominatu Cù foglie (Da u fogliu).

In a finestra di l'editore di query Power Query, tradiziunale eliminemu u passu Tipu cambiatu è aghjunghje un novu passu invece di ellu clicchendu nant'à u buttone f_xin a barra di formula (se ùn hè micca visibile, pudete attivà nantu à a tabulazione recensione) è inserite a formula quì in a lingua integrata Power Query M:

=Table.ToRows(Source)

Stu cumanda hà da cunvertisce quellu caricatu in u passu precedente surghjente tabella di riferimentu in una lista custituita da liste nidificate (Lista), ognuna di quali, à u turnu, hè un paru di valori Era-divintatu da una linea:

Avemu bisognu di stu tipu di dati un pocu più tardi, quandu l'intestazione di massa rinominate da tutte e tavule caricate.

Dopu à compie a cunversione, selezziunà i cumandamenti Casa — Chiudi è Carica — Chiudi è Carica in… è tipu d'importazione Basta à creà una cunnessione (Casa - Close & Load - Close & Load to... - Crea solu cunnessione) è torna à Excel.

Step 2. Avemu carica tuttu da tutti i schedari comu hè

Avà carcà u cuntenutu di tutti i nostri schedari da u cartulare - per avà, cum'è. Scelta di squadre Dati - Get dati - Da u schedariu - Da u cartulare (Dati - Get Data - Da u schedariu - Da u cartulare) è dopu u cartulare induve i nostri libri surghjenti sò.

In a finestra di vista previa, cliccate Cunvertite (trasformazione) or Cambia (Edità):

E poi espansione u cuntenutu di tutti i schedari telecaricati (binariu) buttone cù doppia freccia in l'intestazione di a colonna cuntinutu:

Power Query nantu à l'esempiu di u primu schedariu (Vostok.xlsx) ci dumandarà u nome di u fogliu chì vulemu piglià da ogni libru di travagliu - sceglite Photos è appughjà OK:

Dopu à quessa (in fattu), parechji avvenimenti chì ùn sò micca evidenti à l'utilizatore, saranu e cunsequenze chì sò chjaramente visibili in u pannellu di manca:

1. Power Query piglià u primu schedariu da u cartulare (avemu Vostok.xlsx — vidi Esempiu di schedariu) cum'è un esempiu è importa u so cuntenutu creendu una dumanda Cunvertisce u schedariu di mostra. Sta dumanda avarà qualchi passi simplici cum'è surghjente (accessu à i schedari) navigazzioni (selezzione di fogli) è possibbilmente alzendu i tituli. Questa dumanda pò carica solu dati da un schedariu specificu Vostok.xlsx.
   
2. Basatu nantu à sta dumanda, a funzione assuciata cun ellu serà creatu Cunvertisce u schedariu (indicatu da una icona caratteristica f_x), induve u schedariu fonte ùn serà più una constante, ma un valore variabile - un paràmetru. Cusì, sta funzione pò esse estratti dati da ogni libru chì avemu slip in ellu cum'è un argumentu.
   
3. A funzione serà appiicata à turnu à ogni schedariu (Binariu) da a colonna cuntinutu - u passu hè rispunsevule per questu Chjama a funzione persunalizata in a nostra dumanda chì aghjunghje una colonna à a lista di i schedari Cunvertisce u schedariu cù i risultati impurtati da ogni libru di travagliu:

   

4. I culonni extra sò eliminati.
5. U cuntenutu di e tavule nidificate hè allargatu (passu Colonna di tavula estesa) - è vedemu i risultati finali di a cullizzioni di dati da tutti i libri:

   
   

Passu 3. Sanding

A screenshot precedente mostra chjaramente chì l'assemblea diretta "cum'è" hè stata di mala qualità:

• I culonni sò invertiti.
• Parechje linee extra (viotu è micca solu).
• L'intestazione di a tavula ùn sò micca percepite cum'è intestazioni è sò mischiate cù dati.
  

Pudete riparà tutti questi prublemi assai facilmente - basta à aghjustà a dumanda di Cunvertite Sample File. Tutti l'aghjustamenti chì facemu à ellu cascanu automaticamente in a funzione di cunversione di u schedariu assuciata, chì significa chì anu da esse utilizatu più tardi quandu importate dati da ogni schedariu.

Per apre una dumanda Cunvertisce u schedariu di mostra, aghjunghje i passi per filtrà e fila inutile (per esempiu, per colonna Column2) è alzendu i tituli cù u buttone Aduprate a prima linea cum'è intestazioni (Usate a prima fila cum'è intestazioni). A tavola serà assai megliu.

Per chì e culonne di i schedarii diffirenti si mettenu automaticamente sottu à l'altri dopu, anu da esse chjamatu u listessu. Pudete realizà un tali rinominazione di massa secondu un repertoriu creatu prima cù una linea di codice M. Prememu dinò u buttone f_x in a barra di formula è aghjunghje una funzione per cambià:

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

Sta funzione piglia a tavula da u passu precedente Intestazioni elevate è rinomina tutte e culonne in questu secondu a lista di ricerca anidata Prima. Terzu argumentu MissingField.Ignore hè necessariu per chì nantu à quelli intestazioni chì sò in u cartulare, ma ùn sò micca in a tavula, un errore ùn accade micca.

In fatti, hè tuttu.

Riturnà à a dumanda Rivista videremu una stampa completamente diversa - assai più bella di quella precedente:

• Cosa hè Power Query, Power Pivot, Power BI è perchè un utilizatore Excel ne hà bisognu
  
• Raccolta di dati da tutti i schedari in un cartulare datu
• Raccolta di dati da tutti i fogli di u libru in una tavola