Rasanble tab ki soti nan diferan fichye Excel ak Power Query

Fòmasyon pwoblèm nan

Ann gade nan yon solisyon bèl pou youn nan sitiyasyon yo trè estanda ke pifò itilizatè Excel fè fas a pi bonè oswa pita: ou bezwen byen vit epi otomatikman kolekte done ki sòti nan yon gwo kantite dosye nan yon tab final. 

Sipoze nou gen katab sa a, ki gen plizyè dosye ak done ki soti nan vil branch yo:

Nimewo a nan dosye pa gen pwoblèm epi yo ka chanje nan lavni. Chak fichye gen yon fèy ki rele Vannkote tab done yo ye:

Kantite ranje (lòd) nan tab yo, nan kou, diferan, men seri kolòn yo estanda toupatou.

Objektif Travay la: kolekte done ki sòti nan tout dosye nan yon sèl liv ak mizajou otomatik ki vin apre lè ajoute oswa efase fichye vil oswa ranje nan tablo yo. Dapre tablo final la konsolide, Lè sa a, li pral posib yo bati nenpòt rapò, tab pivot, done filtre-sort, elatriye Bagay pwensipal lan se pou kapab kolekte.

Nou chwazi zam

Pou solisyon an, nou bezwen vèsyon an dènye nan Excel 2016 (fonksyonalite ki nesesè yo deja bati nan li pa default) oswa vèsyon anvan yo nan Excel 2010-2013 ak ajoute nan gratis enstale. Pouvwa rechèch soti nan Microsoft (telechaje li isit la). Power Query se yon zouti super fleksib ak super pwisan pou chaje done nan Excel soti nan mond lan deyò, Lè sa a, nidite ak trete li. Power Query sipòte prèske tout sous done ki egziste deja - soti nan dosye tèks nan SQL e menm Facebook 🙂

Si ou pa gen Excel 2013 oswa 2016, Lè sa a, ou pa ka li plis (jis plèzantri). Nan vèsyon ki pi gran nan Excel, yon travay konsa ka fèt sèlman lè w pwograme yon makro nan Visual Basic (ki trè difisil pou débutan) oswa pa kopi manyèl monotòn (ki pran anpil tan epi jenere erè).

Etap 1. Enpòte yon dosye kòm yon echantiyon

Premyèman, ann enpòte done ki sòti nan yon liv travay kòm yon egzanp, pou ke Excel "ranmase lide a". Pou fè sa, kreye yon nouvo liv travay vid ak ...

• si ou gen Excel 2016, Lè sa a, louvri tab la Done ak Lè sa a, Kreye Rekèt - Soti nan Fichye - Soti nan Liv (Done - Nouvo Rekèt - Soti nan fichye - Soti nan Excel)
• si ou gen Excel 2010-2013 ak Power Query ajoute nan enstale, Lè sa a, louvri tab la Pouvwa rechèch epi chwazi sou li Soti nan dosye - Soti nan liv (Soti nan fichye - Soti nan Excel)

Lè sa a, nan fenèt la ki louvri, ale nan katab nou an ak rapò epi chwazi nenpòt nan fichye vil yo (li pa gen pwoblèm ki youn, paske yo tout se tipik). Apre yon koup de segonn, fenèt Navigatè a ta dwe parèt, kote ou bezwen chwazi fèy la nou bezwen (Komèsyal) sou bò gòch la, ak sa ki ladan yo pral parèt sou bò dwat la:

Si w klike sou bouton an ki nan kwen pi ba dwat fenèt sa a download (Chajman), Lè sa a, tab la pral imedyatman enpòte nan fèy la nan fòm orijinal li. Pou yon fichye sèl, sa a bon, men nou bezwen chaje anpil dosye sa yo, kidonk nou pral ale yon ti jan diferan epi klike sou bouton an. Koreksyon (Edit). Apre sa, editè demann Power Query yo ta dwe parèt nan yon fenèt separe ak done nou yo ki nan liv la:

Sa a se yon zouti trè pwisan ki pèmèt ou "fini" tab la nan gade nan nou bezwen an. Menm yon deskripsyon supèrfisyèl nan tout fonksyon li yo ta pran apeprè yon santèn paj, men, si yon ti tan, lè l sèvi avèk fenèt sa a ou ka:

• filtre done ki pa nesesè, liy vid, liy ki gen erè
• klase done pa youn oswa plizyè kolòn
• debarase m de repetisyon
• divize tèks kolan pa kolòn (pa delimiter, kantite karaktè, elatriye)
• mete tèks nan lòd (retire espas siplemantè, korije majiskil, elatriye)
• konvèti kalite done nan tout fason posib (vire nimewo tankou tèks nan nimewo nòmal ak vis vèrsa)
• transpoze (wotasyon) tab yo epi elaji tab kwa ki genyen de dimansyon nan tab yo ki plat
• ajoute kolòn adisyonèl nan tablo a epi sèvi ak fòmil ak fonksyon ladan yo lè l sèvi avèk lang M ki bati nan Power Query.
• ...

Pou egzanp, an n ajoute yon kolòn ak non tèks mwa a nan tablo nou an, pou pita li ta pi fasil yo bati rapò tablo pivot. Pou fè sa, klike sou tit kolòn nan datepi chwazi kòmandman an Kopi kolòn (Kolòn kopi), ak Lè sa a, dwa-klike sou tèt la nan kolòn nan kopi ki parèt epi chwazi Kòmandman Transfòme – Mwa – Non Mwa:

Yo ta dwe fòme yon nouvo kolòn ak non tèks mwa a pou chak ranje. Lè w fè doub klike sou yon tit kolòn, ou ka chanje non li Kopi Dat nan yon pi konfòtab Mwa, egzanp.

Si nan kèk kolòn pwogram nan pa t 'byen kòrèkteman rekonèt kalite done a, Lè sa a, ou ka ede li pa klike sou icon nan fòma sou bò gòch nan chak kolòn:

Ou ka eskli liy ki gen erè oswa liy vid, osi byen ke manadjè oswa kliyan ki pa nesesè, lè l sèvi avèk yon filtè senp:

Anplis, tout transfòmasyon ki fèt yo fikse nan panèl dwat la, kote yo ka toujou woule tounen (kwa) oswa chanje paramèt yo (kovèti pou):

Limyè ak elegant, pa vre?

Etap 2. Ann transfòme demann nou an nan yon fonksyon

Yo nan lòd yo imedyatman repete tout transfòmasyon done yo te fè pou chak liv enpòte, nou bezwen konvèti demann nou an kreye nan yon fonksyon, ki pral Lè sa a, aplike, nan vire, nan tout dosye nou yo. Pou fè sa se aktyèlman trè senp.

Nan Editè Rekèt la, ale nan tab la View epi klike sou bouton an Editè avanse (View — Editè avanse). Yon fenèt ta dwe louvri kote tout aksyon anvan nou yo pral ekri nan fòm lan nan kòd nan lang M. Tanpri sonje ke chemen an nan dosye a ke nou enpòte pou egzanp lan se difisil nan kòd la:

Koulye a, ann fè yon koup nan ajisteman:

Siyifikasyon yo se senp: premye liy lan (filechemen) => vire pwosedi nou an nan yon fonksyon ak yon agiman filepath, ak anba a nou chanje chemen fiks la nan valè a nan varyab sa a. 

Tout. Klike sou Fini epi yo ta dwe wè sa a:

Ou pa bezwen pè ke done yo te disparèt - an reyalite, tout bagay se OK, tout bagay ta dwe sanble tankou sa a 🙂 Nou te kreye avèk siksè fonksyon koutim nou an, kote tout algorithm pou enpòte ak trete done yo sonje san yo pa mare nan yon dosye espesifik. . Li rete pou ba li yon non ki pi konprann (pa egzanp getData) nan panèl la sou bò dwat nan jaden an Prénoms epi ou ka rekòlte Kay — Fèmen epi telechaje (Kay — Fèmen ak Chaje). Tanpri sonje ke chemen an nan dosye a ke nou enpòte pou egzanp lan se hardcoded nan kòd la. Ou pral retounen nan fenèt prensipal Microsoft Excel la, men yon panèl ki gen koneksyon kreye ak fonksyon nou an ta dwe parèt sou bò dwat la:

Etap 3. Kolekte tout dosye yo

Tout pati ki pi difisil la se dèyè, pati ki bèl ak fasil rete. Ale nan tab la Done - Kreye Rekèt - Soti nan Fichye - Soti nan Dosye (Done — Nouvo Rekèt — Soti nan fichye — Soti nan katab) oswa, si ou gen Excel 2010-2013, menm jan ak tab la Pouvwa rechèch. Nan fenèt ki parèt, presize katab kote tout fichye vil sous nou yo ye epi klike sou OK. Pwochen etap la ta dwe louvri yon fenèt kote tout fichye Excel yo te jwenn nan katab sa a (ak subfolders li yo) ak detay pou chak nan yo pral lis:

Klike sou Chanje (Edit) epi ankò nou jwenn nan fenèt editè rechèch la abitye.

Koulye a, nou bezwen ajoute yon lòt kolòn nan tab nou an ak fonksyon kreye nou an, ki pral "rale" done ki soti nan chak dosye. Pou fè sa, ale nan tab la Ajoute Kolòn - Kolòn Custom (Ajoute kolòn — Ajoute kolòn Customized) ak nan fenèt la ki parèt, antre nan fonksyon nou an getData, espesifye pou li kòm yon agiman chemen konplè nan chak dosye:

Apre klike sou OK kolòn kreye a ta dwe ajoute sou tab nou an sou bò dwat la.

Koulye a, ann efase tout kolòn ki pa nesesè yo (tankou nan Excel, lè l sèvi avèk bouton dwat sourit la - Retire), kite sèlman kolòn nan te ajoute ak kolòn nan ak non fichye a, paske non sa a (pi jisteman, vil la) ap itil yo gen nan done yo total pou chak ranje.

Epi, koulye a "wow moman an" - klike sou icon nan ak pwòp flèch li yo nan kwen anwo dwat nan kolòn nan ajoute ak fonksyon nou an:

… dezaktive Sèvi ak non kolòn orijinal kòm prefiks (Sèvi ak non kolòn orijinal kòm prefiks)epi klike sou OK. Ak fonksyon nou an pral chaje ak trete done ki soti nan chak dosye, swiv algorithm anrejistre a ak kolekte tout bagay nan yon tablo komen:

Pou yon bèlte konplè, ou kapab tou retire ekstansyon .xlsx yo nan premye kolòn nan ak non fichye yo - pa ranplasman estanda ak "pa gen anyen" (klike dwa sou tèt kolòn nan - Ranplase) epi chanje non kolòn sa a City. Epi tou korije fòma done a nan kolòn ak dat la.

Tout! Klike sou Kay – Fèmen ak Chaje (Kay — Fèmen & Chaje). Tout done rechèch la kolekte pou tout vil yo pral telechaje sou fèy Excel aktyèl la nan fòma "smart table":

Koneksyon an kreye ak fonksyon asanble nou an pa bezwen sove separeman nan okenn fason - yo sove ansanm ak dosye aktyèl la nan fason nòmal la.

Nan tan kap vini an, ak nenpòt chanjman ki fèt nan katab la (ajoute oswa retire vil) oswa nan dosye (chanje kantite liy), li pral ase klike sou dwa dirèkteman sou tab la oswa sou rechèch la nan panèl la dwat epi chwazi a. kòmandman Mete ajou & Sove (Rafrechi) - Power Query pral "rebati" tout done yo ankò nan kèk segond.

PS

Amannman. Apre mizajou janvye 2017 yo, Power Query te aprann kijan pou l kolekte liv Excel pou kont li, sa vle di pa bezwen fè yon fonksyon separe ankò - sa rive otomatikman. Kidonk, dezyèm etap la nan atik sa a pa nesesè ankò e tout pwosesis la vin pi senp:

1. Chwazi Kreye Demann - Soti nan Fichye - Soti nan Dosye - Chwazi Dosye - OK
2. Apre lis fichye yo parèt, peze Chanje
3. Nan fenèt Editè Rekèt la, elaji kolòn binè a ak yon flèch doub epi chwazi non fèy yo dwe pran nan chak dosye.

Epi se tout! Chante!

• Redesign nan crosstab la nan yon sèl plat apwopriye pou bati tab pivot
• Bati yon tablo ti wonn anime nan Power View
• Macro pou rasanble fèy ki soti nan diferan fichye Excel nan yon sèl