Werrej
Formulazzjoni tal-problema
Ejja nħarsu lejn soluzzjoni sabiħa għal waħda mis-sitwazzjonijiet standard ħafna li l-biċċa l-kbira tal-utenti ta 'Excel jiffaċċjaw illum jew għada: għandek bżonn tiġbor malajr u awtomatikament data minn numru kbir ta' fajls f'tabella finali waħda.
Ejja ngħidu li għandna l-folder li ġej, li fih diversi fajls b'dejta minn bliet fergħat:
In-numru ta 'fajls ma jimpurtax u jista' jinbidel fil-futur. Kull fajl għandu folja msemmija bejgħfejn tinsab it-tabella tad-dejta:
In-numru ta 'ringieli (ordnijiet) fit-tabelli, ovvjament, huwa differenti, iżda s-sett ta' kolonni huwa standard kullimkien.
Kompitu: biex tiġbor data mill-fajls kollha fi ktieb wieħed b'aġġornament awtomatiku sussegwenti meta żżid jew tħassar fajls tal-belt jew ringieli fit-tabelli. Skont it-tabella konsolidata finali, allura jkun possibbli li jinbnew kwalunkwe rapporti, tabelli pern, data filter-sort, eċċ. Il-ħaġa prinċipali hija li tkun tista 'tiġbor.
Aħna nagħżlu l-armi
Għas-soluzzjoni, neħtieġu l-aħħar verżjoni ta 'Excel 2016 (il-funzjonalità meħtieġa hija diġà mibnija fiha awtomatikament) jew verżjonijiet preċedenti ta' Excel 2010-2013 bil-add-in b'xejn installat Mistoqsija dwar l-Enerġija minn Microsoft (niżżlu hawn). Power Query hija għodda super flessibbli u super b'saħħitha għat-tagħbija tad-dejta f'Excel mid-dinja ta 'barra, imbagħad tqaxxarha u tipproċessaha. Power Query jappoġġja kważi s-sorsi kollha tad-dejta eżistenti – minn fajls ta’ test għal SQL u anke Facebook 🙂
Jekk ma jkollokx Excel 2013 jew 2016, allura ma tistax taqra aktar (kidding biss). F'verżjonijiet anzjani ta 'Excel, kompitu bħal dan jista' jitwettaq biss billi jiġi pprogrammat makro fil-Visual Basic (li huwa diffiċli ħafna għal dawk li jibdew) jew b'ikkuppjar manwali monotonu (li jieħu żmien twil u jiġġenera żbalji).
Pass 1. Importa fajl wieħed bħala kampjun
L-ewwel, ejja nimportaw dejta minn ktieb tax-xogħol wieħed bħala eżempju, sabiex Excel "jiġbor l-idea". Biex tagħmel dan, oħloq ktieb tax-xogħol vojt ġdid u...
- jekk għandek Excel 2016, imbagħad iftaħ it-tab Data u mbagħad Oħloq Mistoqsija - Mill-Fajl - Mill-Ktieb (Data — Mistoqsija Ġdida- Mill-fajl — Minn Excel)
- jekk għandek Excel 2010-2013 bil-Power Query add-in installat, imbagħad iftaħ it-tab Mistoqsija dwar l-Enerġija u agħżel fuqha Mill-fajl – Mill-ktieb (Mill-fajl — Minn Excel)
Imbagħad, fit-tieqa li tiftaħ, mur fil-folder tagħna bir-rapporti u agħżel kwalunkwe fajl tal-belt (ma jimpurtax liema wieħed, għax huma kollha tipiċi). Wara ftit sekondi, għandha tidher it-tieqa tan-Navigatur, fejn għandek bżonn tagħżel il-folja li neħtieġu (Bejgħ) fuq in-naħa tax-xellug, u l-kontenut tagħha jintwera fuq in-naħa tal-lemin:
Jekk tikklikkja fuq il-buttuna fir-rokna t'isfel tal-lemin ta' din it-tieqa download (Tagħbija), allura t-tabella tiġi importata immedjatament fil-folja fil-forma oriġinali tagħha. Għal fajl wieħed, dan huwa tajjeb, iżda għandna bżonn tagħbija ħafna fajls bħal dawn, għalhekk se mmorru ftit differenti u kklikkja l-buttuna Korrezzjoni (Edit). Wara dan, l-editur tal-mistoqsijiet tal-Power Query għandu jintwera f'tieqa separata bid-dejta tagħna mill-ktieb:
Din hija għodda b'saħħitha ħafna li tippermettilek "tlesti" t-tabella għall-veduta li neħtieġu. Anke deskrizzjoni superfiċjali tal-funzjonijiet kollha tagħha tieħu madwar mitt paġna, iżda, jekk fil-qosor ħafna, billi tuża din it-tieqa tista':
- iffiltra data mhux meħtieġa, linji vojta, linji bi żbalji
- issortja d-dejta b'kolonna waħda jew aktar
- jeħles mir-ripetizzjoni
- jaqsam it-test li jwaħħal bil-kolonni (b'delimitaturi, numru ta' karattri, eċċ.)
- poġġi t-test fl-ordni (neħħi l-ispazji żejda, il-każ korrett, eċċ.)
- tikkonverti t-tipi tad-dejta b'kull mod possibbli (dawwar in-numri bħal test f'numri normali u viċi versa)
- jittrasponi (dawwar) it-tabelli u jespandi t-tabelli inkroċjati bidimensjonali f'dawk ċatti
- żid kolonni addizzjonali mat-tabella u uża formuli u funzjonijiet fihom billi tuża l-lingwa M mibnija fil-Power Query.
- ...
Pereżempju, ejja nżidu kolonna bl-isem tat-test tax-xahar mat-tabella tagħna, sabiex aktar tard ikun aktar faċli li nibnu rapporti ta 'tabella pivot. Biex tagħmel dan, ikklikkja l-lemin fuq l-intestatura tal-kolonna datau agħżel il-kmand Kolonna duplikata (Kolonna Duplikata), u mbagħad ikklikkja bil-lemin fuq l-intestatura tal-kolonna duplikata li tidher u agħżel Kmandi Transform – Xahar – Isem Xahar:
Għandha tiġi ffurmata kolonna ġdida bl-ismijiet tat-test tax-xahar għal kull ringiela. Billi tikklikkja darbtejn fuq intestatura ta' kolonna, tista' tibdel l-isem minnha Kopja tad-Data għal aktar komdu Xahar, eż.
Jekk f'xi kolonni l-programm ma għarafx b'mod korrett it-tip tad-dejta, allura tista 'tgħinha billi tikklikkja fuq l-ikona tal-format fuq in-naħa tax-xellug ta' kull kolonna:
Tista' teskludi linji bi żbalji jew linji vojta, kif ukoll maniġers jew klijenti mhux meħtieġa, billi tuża filtru sempliċi:
Barra minn hekk, it-trasformazzjonijiet kollha mwettqa huma ffissati fil-pannell tal-lemin, fejn dejjem jistgħu jiġu rrumblati lura (salib) jew ibiddlu l-parametri tagħhom (irkaptu):
Ħfief u eleganti, hux?
Pass 2. Ejja nittrasformaw it-talba tagħna f'funzjoni
Sabiex sussegwentement nirrepetu t-trasformazzjonijiet tad-dejta kollha magħmula għal kull ktieb importat, irridu naqilbu t-talba maħluqa tagħna f'funzjoni, li mbagħad tiġi applikata, imbagħad, għall-fajls kollha tagħna. Biex tagħmel dan huwa fil-fatt sempliċi ħafna.
Fl-Editur tal-Mistoqsija, mur fit-tab View u kklikkja l-buttuna Editur Avvanzat (Ara — Editur Avvanzat). Għandha tiftaħ tieqa fejn l-azzjonijiet preċedenti tagħna kollha se jinkitbu fil-forma ta 'kodiċi fil-lingwa M. Jekk jogħġbok innota li l-passaġġ għall-fajl li aħna importajna għall-eżempju huwa hardcoded fil-kodiċi:
Issa ejja nagħmlu ftit aġġustamenti:
It-tifsira tagħhom hija sempliċi: l-ewwel linja (filepath) => jibdel il-proċedura tagħna f'funzjoni b'argument filepath, u hawn taħt aħna nibdlu l-mogħdija fissa għall-valur ta 'din il-varjabbli.
Kollha. Ikklikkja fuq Finatura u għandha tara dan:
Tibżax li d-dejta sparixxa - fil-fatt, kollox huwa OK, kollox għandu jidher bħal dan 🙂 Ħloqna b'suċċess il-funzjoni tad-dwana tagħna, fejn l-algoritmu kollu għall-importazzjoni u l-ipproċessar tad-dejta jiġi mfakkar mingħajr ma jkun marbut ma 'fajl speċifiku . Fadal biex tagħtiha isem aktar li jinftiehem (pereżempju getData) fil-pannell fuq il-lemin fil-qasam L-ewwel isem u tista' taħsad Home — Agħlaq u niżżel (Dar — Agħlaq u Tagħbija). Jekk jogħġbok innota li l-passaġġ għall-fajl li aħna importati għall-eżempju huwa hardcoded fil-kodiċi. Int se terġa 'lura għat-tieqa ewlenija ta' Microsoft Excel, iżda għandu jidher panel bil-konnessjoni maħluqa mal-funzjoni tagħna fuq il-lemin:
Pass 3. Il-ġbir tal-fajls kollha
L-aktar parti iebsa hija lura, il-parti pjaċevoli u faċli tibqa '. Mur fit-tab Dejta – Oħloq Mistoqsija – Mill-Fajl – Minn Folder (Data — Mistoqsija Ġdida — Mill-fajl — Mill-folder) jew, jekk għandek Excel 2010-2013, b'mod simili għat-tab Mistoqsija dwar l-Enerġija. Fit-tieqa li tidher, speċifika l-folder fejn jinsabu l-fajls kollha tal-belt sors tagħna u kklikkja OK. Il-pass li jmiss għandu jiftaħ tieqa fejn il-fajls Excel kollha li jinsabu f'dan il-folder (u s-subfolders tiegħu) u d-dettalji għal kull wieħed minnhom se jiġu elenkati:
Ikklikkja Bidla (Edit) u għal darb'oħra nidħlu fit-tieqa familjari tal-editur tal-mistoqsijiet.
Issa rridu nżidu kolonna oħra mat-tabella tagħna bil-funzjoni maħluqa tagħna, li "jiġbed" id-dejta minn kull fajl. Biex tagħmel dan, mur fit-tab Żid Kolonna - Kolonna Custom (Żid Kolonna — Żid Kolonna Personalizzata) u fit-tieqa li tidher, daħħal il-funzjoni tagħna getData, billi tispeċifika għaliha bħala argument it-triq sħiħa għal kull fajl:
Wara li tikklikkja fuq OK il-kolonna maħluqa għandha tiżdied mat-tabella tagħna fuq il-lemin.
Issa ejja nħassru l-kolonni kollha mhux meħtieġa (bħal f'Excel, billi tuża l-buttuna tal-lemin tal-maws - Neħħi), li jħallu biss il-kolonna miżjuda u l-kolonna bl-isem tal-fajl, minħabba li dan l-isem (b'mod aktar preċiż, il-belt) se jkun utli li jkun hemm fid-dejta totali għal kull ringiela.
U issa l-"mument naqra" - ikklikkja fuq l-ikona bil-vleġeġ tagħha stess fir-rokna ta' fuq tal-lemin tal-kolonna miżjuda bil-funzjoni tagħna:
… uncheck Uża l-isem tal-kolonna oriġinali bħala prefiss (Uża l-isem tal-kolonna oriġinali bħala prefiss)u kklikkja OK. U l-funzjoni tagħna se tagħbija u tipproċessa d-dejta minn kull fajl, billi ssegwi l-algoritmu rreġistrat u niġbor kollox f'tabella komuni:
Għal sbuħija sħiħa, tista 'wkoll tneħħi l-estensjonijiet .xlsx mill-ewwel kolonna bl-ismijiet tal-fajls - b'sostituzzjoni standard b' "xejn" (ikklikkja bil-lemin fuq l-intestatura tal-kolonna - Sostitut) u semmi mill-ġdid din il-kolonna għal belt. U tikkoreġi wkoll il-format tad-dejta fil-kolonna bid-data.
Kollha! Ikklikkja fuq Dar - Agħlaq u Tagħbija (Id-Dar — Agħlaq u Tagħbija). Id-dejta kollha miġbura mill-mistoqsija għall-ibliet kollha se tittella’ fuq il-folja Excel attwali fil-format ta’ “tabella intelliġenti”:
Il-konnessjoni maħluqa u l-funzjoni ta 'assemblaġġ tagħna m'għandhomx għalfejn jiġu ssejvjati separatament bl-ebda mod - huma ssejvjati flimkien mal-fajl kurrenti bil-mod tas-soltu.
Fil-futur, bi kwalunkwe tibdil fil-folder (żieda jew tneħħija ta 'bliet) jew fil-fajls (bdil tan-numru ta' linji), ikun biżżejjed li tikklikkja direttament fuq il-mejda jew fuq il-mistoqsija fil-pannell tal-lemin u tagħżel il- kmand Aġġorna u Issejvja (Aġġorna) – Power Query jerġa’ “jibni” d-dejta kollha fi ftit sekondi.
PS
Emenda. Wara l-aġġornamenti ta’ Jannar 2017, Power Query tgħallem kif tiġbor il-kotba ta’ l-Excel waħedha, jiġifieri m’hemmx għalfejn tagħmel aktar funzjoni separata – dan iseħħ awtomatikament. Għalhekk, it-tieni pass minn dan l-artikolu m'għadux meħtieġ u l-proċess kollu jsir notevolment aktar sempliċi:
- Agħżel Oħloq Talba – Minn Fajl – Minn Folder – Agħżel Folder – OK
- Wara li tidher il-lista tal-fajls, agħfas Bidla
- Fit-tieqa tal-Editur tal-Mistoqsija, jespandi l-kolonna Binarja bi vleġġa doppja u agħżel l-isem tal-folja li trid tittieħed minn kull fajl
U dak kollu! Kanzunetta!
- Disinn mill-ġdid tal-crosstab f'wieħed ċatt adattat għall-bini ta 'tabelli tal-pern
- Bini ta 'grafika tal-bużżieqa animata f'Power View
- Makro biex tiġbor folji minn fajls Excel differenti f'wieħed