cuntenutu
Formulazione di u prublema
Fighjemu una bella suluzione per una di e situazioni assai standard chì a maiò parte di l'utilizatori di Excel affruntà prima o dopu: avete bisognu di cullà rapidamente è automaticamente e dati da un gran numaru di schedari in una tabella finale.
Suppone chì avemu u cartulare seguente, chì cuntene parechji schedari cù dati da e cità di filiale:
U numaru di schedari ùn importa micca è pò cambià in u futuru. Ogni schedariu hà un fogliu chjamatu Ventesinduve si trova a tabella di dati:
U numaru di fila (ordini) in i tavulini, sicuru, hè diversu, ma u settore di culonni hè standard in ogni locu.
Task: per cullà e dati da tutti i fugliali in un libru cù l'aghjurnamentu automaticu sussegwente quandu aghjunghje o sguassate i fugliali di cità o file in tavule. Sicondu a tavula cunsulidata finali, allora serà pussibule di custruisce qualsiasi rapporti, tavule pivot, dati di filtru-sort, etc. A cosa principal hè di pudè cullà.
Selezziemu l'arme
Per a suluzione, avemu bisognu di l'ultima versione di Excel 2016 (a funziunalità necessaria hè digià integrata in questu per automaticamente) o versioni precedenti di Excel 2010-2013 cù l'add-in gratuitu installatu. dumanda di putenza da Microsoft (scaricate quì). Power Query hè un strumentu super flessibile è super putente per caricare dati in Excel da u mondu esternu, poi sguassate è trasfurmà. Power Query supporta quasi tutte e fonti di dati esistenti - da i fugliali di testu à SQL è ancu Facebook 🙂
Se ùn avete micca Excel 2013 o 2016, allora ùn pudete micca leghje più (solu scherzu). In e versioni più vechje di Excel, un tali compitu pò esse realizatu solu da prugrammazione di una macro in Visual Basic (chì hè assai difficiule per i principianti) o da una copia manuale monotona (chì dura assai tempu è genera errori).
Passu 1. Import un schedariu cum'è una mostra
Prima, importamu dati da un libru di travagliu cum'è un esempiu, cusì chì Excel "coglie l'idea". Per fà questu, crea un novu libru di travagliu in biancu è ...
- se avete Excel 2016, apre a tabulazione Dati è dopu Crea Query - From File - From Book (Dati - Nova Query - Da u schedariu - Da Excel)
- se avete Excel 2010-2013 cù l'add-in Power Query installatu, dopu apre a tabulazione dumanda di putenza è selezziunate nantu à questu Da u schedariu - Da u libru (Da u schedariu - Da Excel)
Allora, in a finestra chì apre, andate à u nostru cartulare cù i rapporti è selezziunate qualsiasi di i schedarii di a cità (ùn importa quale, perchè sò tutti tipici). Dopu à un coppiu di sicondi, a finestra di u Navigatore deve esse cumparisce, induve avete bisognu di selezziunà u fogliu chì avemu bisognu (Vendite) à u latu manca, è u so cuntenutu serà mostratu à u latu drittu:
Sè vo cliccate nant'à u buttone in u cantonu in basso dritta di sta finestra Download (Carica), allura a tavula serà impurtata immediatamente à u fogliu in a so forma originale. Per un unicu schedariu, questu hè bonu, ma avemu bisognu di carricà parechji tali schedari, cusì andemu un pocu sfarente è cliccate u buttone currezzione (Edità). Dopu quì, l'editore di query Power Query deve esse affissatu in una finestra separata cù i nostri dati da u libru:
Questu hè un strumentu assai putente chì permette di "finisce" a tavula à a vista chì avemu bisognu. Ancu una descrizzione superficiale di tutte e so funzioni pigliarà circa un centu di pagine, ma, s'ellu hè assai breve, cù sta finestra pudete:
- filtrà i dati inutili, linee viote, linee cù errori
- sorte i dati per una o più colonne
- sguassate di a ripetizione
- divide u testu appiccicosu per colonne (per delimitatori, numeru di caratteri, etc.)
- mette u testu in ordine (sguassate spazii extra, casu currettu, etc.)
- cunvertisce i tipi di dati in ogni modu pussibule (trasforma i numeri cum'è u testu in numeri normali è viceversa)
- traspone (rotate) i tavulini è espansione e tavule incruciate bidimensionali in i piani
- aghjunghje colonne supplementari à a tavula è utilizate formule è funzioni in elli cù a lingua M integrata in Power Query.
- ...
Per esempiu, aghjustemu una colonna cù u nome di testu di u mese à a nostra tavula, perchè più tardi serà più faciule per custruisce rapporti di tabella pivot. Per fà questu, fate un clic drittu nantu à l'intestazione di a colonna dataè selezziunate u cumandamentu Colonna duplicata (Colonna duplicata), è dopu cliccate right-click nantu à l'intestazione di a colonna duplicata chì appare è selezziunate Cumandamenti Trasfurmà - Mesi - Mesi Nome:
Una nova colonna deve esse furmatu cù i nomi di testu di u mese per ogni fila. Facendu un doppiu clicu nantu à un intestazione di colonna, pudete rinominà da Copia Data à un più còmode Mese, p.e.
Se in certi culonni u prugramma ùn hà micca ricunnisciutu bè u tipu di dati, pudete aiutà clicchendu nantu à l'icona di furmatu à u latu manca di ogni colonna:
Pudete escludiri linee cù errori o linee viote, è ancu amministratori o clienti innecessarii, utilizendu un filtru simplice:
Inoltre, tutte e trasfurmazioni eseguite sò fissate in u pannellu drittu, induve ponu sempre esse rotulati (croce) o cambià i so parametri (gear):
Luce è elegante, ùn hè micca?
Step 2. Andemu trasfurmà a nostra dumanda in una funzione
In ordine dopu à ripetiri tutti i trasfurmazioni dati fatti per ogni libru impurtatu, avemu bisognu di cunvertisce a nostra dumanda criata in una funzione, chì sarà poi appiicata, à turnu, à tutti i nostri schedari. Per fà questu hè veramente assai simplice.
In l'Editor di Query, andate à a tabulazione View è cliccate u buttone Editor avanzatu (Vede - Editor avanzatu). Una finestra deve esse aperta induve tutte e nostre azzioni previ seranu scritte in forma di codice in lingua M. Per piacè nutate chì a strada di u schedariu chì avemu impurtatu per l'esempiu hè codificata in u codice:
Avà facemu un paru di aghjustamenti:
U so significatu hè simplice: a prima linea (filepath) => trasforma a nostra prucedura in una funzione cù un argumentu filepath, è quì sottu cambiemu u percorsu fissu à u valore di sta variabile.
Tuttu. Cliccate nant'à Finisci è duverebbe vede questu:
Ùn àbbia paura chì i dati sò spariti - in fattu, tuttu hè bè, tuttu deve esse cusì 🙂 Avemu creatu cù successu a nostra funzione persunalizata, induve tuttu l'algoritmu per impurtà è trasfurmà e dati hè ricurdatu senza esse ligatu à un schedariu specificu. . Resta à dà un nome più comprensibile (per esempiu getData) in u pannellu à a diritta in u campu nome è pudete coglie Home - Chiudi è scaricate (Casa - Chiudi è Carica). Per piacè nutate chì a strada di u schedariu chì avemu impurtatu per l'esempiu hè codificata in u codice. Ritornarete à a finestra principale di Microsoft Excel, ma un pannellu cù a cunnessione creata à a nostra funzione deve apparisce à a diritta:
Step 3. Cugliera tutti i schedari
Tuttu u più duru hè daretu, a parte piacevule è faciule ferma. Andà à a tabulazione Dati - Crea Query - From File - From Folder (Dati - Nova Query - Da u schedariu - Da u cartulare) o, sè vo avete Excel 2010-2013, simile à a tabulazione dumanda di putenza. In a finestra chì appare, specificate u cartulare duve si trovanu tutti i nostri schedarii di a cità surghjente è cliccate OK. U prossimu passu duveria apre una finestra induve tutti i fugliali Excel truvati in stu cartulare (è i so subfolders) è i dettagli per ognuna di elli seranu listati:
Clicca Cambia (Edità) è di novu entremu in a finestra di l'editore di query familiar.
Avà avemu bisognu di aghjunghje una altra colonna à a nostra tavula cù a nostra funzione creata, chì "tirà" i dati da ogni schedariu. Per fà questu, andate à a tabulazione Aggiungi Colonna - Colonna Personalizzata (Aggiungi una colonna - Aggiungi una colonna persunalizata) è in a finestra chì appare, entre in a nostra funzione getData, specificendu per ellu cum'è argumentu a strada completa à ogni schedariu:
Dopu avè cliccatu OK a colonna creata deve esse aghjuntu à a nostra tavula à a diritta.
Avà sguassate tutte e colonne inutili (cum'è in Excel, usendu u buttone dirittu di u mouse - Eliminar), lascendu solu a colonna aghjunta è a colonna cù u nome di u schedariu, perchè stu nome (più precisamente, a cità) serà utile per avè in a dati tutali per ogni fila.
È avà u "momentu wow" - cliccate nantu à l'icona cù e so frecce in l'angulu superiore dirittu di a colonna aghjuntu cù a nostra funzione:
… disattivate Aduprate u nome di a colonna originale cum'è prefissu (Aduprate u nome di a colonna originale cum'è prefissu)è cliccate OK. È a nostra funzione caricarà è processerà e dati da ogni fugliale, seguitu l'algoritmu arregistratu è cullendu tuttu in una tabella cumuna:
Per a bellezza cumpleta, pudete ancu sguassà l'estensioni .xlsx da a prima colonna cù i nomi di schedari - per rimpiazzamentu standard cù "nunda" (cliccate u dirittu nantu à l'intestazione di a colonna - Sustituitu) è rinominate sta colonna à Certu. È ancu corrette u formatu di dati in a colonna cù a data.
Tuttu ! Cliccate nant'à Casa - Chiudi è Caricà (Casa - Chiudi & Carica). Tutte e dati raccolti da a dumanda per tutte e cità seranu caricati in a foglia Excel attuale in u formatu "tavola intelligente":
A cunnessione creata è a nostra funzione di assemblea ùn anu micca bisognu di esse salvatu separatamente in ogni modu - sò salvati inseme cù u schedariu attuale in u modu di solitu.
In u futuru, cù qualsiasi mudificazione in u cartulare (aghjunghje o sguassate cità) o in i schedari (cambiendu u numeru di linii), basterà à cliccà dritta direttamente nantu à a tavula o nantu à a quistione in u pannellu drittu è selezziunate u cumanda Aghjurnà & Salvà (rinfriscà) - Power Query "ricustruisce" tutte e dati di novu in pochi seconde.
PS
Amenda. Dopu à l'aghjurnamenti di ghjennaghju 2017, Power Query hà amparatu à cullà i libri di Excel da ellu stessu, vale à dì ùn hè più bisognu di fà una funzione separata - succede automaticamente. Cusì, u sicondu passu da questu articulu ùn hè più necessariu è tuttu u prucessu diventa notevolmente più simplice:
- Scegli Crea Request - From File - From Folder - Select Folder - OK
- Dopu chì a lista di i schedari appare, appughjà Cambia
- In a finestra di l'Editor di Query, espansione a colonna Binary cù una freccia doppia è selezziunate u nome di u fogliu per esse pigliatu da ogni schedariu.
È questu hè tuttu ! Canzona !
- Redesign of the crosstab in un pianu adattatu per a custruzzione di tavule pivot
- Custruì un graficu di bolle animatu in Power View
- Macro per assemblà fogli da diversi schedari Excel in una sola