Cynnwys
Ffurfio'r broblem
Gadewch i ni edrych ar ateb hardd ar gyfer un o'r sefyllfaoedd safonol iawn y mae'r rhan fwyaf o ddefnyddwyr Excel yn eu hwynebu yn hwyr neu'n hwyrach: mae angen i chi gasglu data o nifer fawr o ffeiliau yn gyflym ac yn awtomatig i mewn i un tabl terfynol.
Tybiwch fod gennym y ffolder canlynol, sy'n cynnwys sawl ffeil gyda data o ddinasoedd cangen:
Nid yw nifer y ffeiliau o bwys a gall newid yn y dyfodol. Mae gan bob ffeil ddalen wedi'i henwi Salesble mae'r tabl data wedi'i leoli:
Mae nifer y rhesi (gorchmynion) yn y tablau, wrth gwrs, yn wahanol, ond mae'r set o golofnau yn safonol ym mhobman.
Tasg: i gasglu data o'r holl ffeiliau i mewn i un llyfr gyda diweddaru awtomatig dilynol wrth ychwanegu neu ddileu ffeiliau dinas neu resi mewn tablau. Yn ôl y tabl cyfunol terfynol, yna bydd yn bosibl adeiladu unrhyw adroddiadau, tablau colyn, data didoli hidlo, ac ati. Y prif beth yw gallu casglu.
Rydym yn dewis arfau
Ar gyfer yr ateb, mae arnom angen y fersiwn ddiweddaraf o Excel 2016 (mae'r swyddogaeth angenrheidiol eisoes wedi'i hymgorffori ynddo yn ddiofyn) neu fersiynau blaenorol o Excel 2010-2013 gyda'r ychwanegiad rhad ac am ddim wedi'i osod Ymholiad Pwer oddi wrth Microsoft (lawrlwythwch ef yma). Offeryn hynod hyblyg a hynod bwerus yw Power Query ar gyfer llwytho data i Excel o'r byd y tu allan, yna ei dynnu a'i brosesu. Mae Power Query yn cefnogi bron pob ffynhonnell ddata bresennol - o ffeiliau testun i SQL a hyd yn oed Facebook 🙂
Os nad oes gennych Excel 2013 neu 2016, yna ni allwch ddarllen ymhellach (dim ond kidding). Mewn fersiynau hŷn o Excel, dim ond trwy raglennu macro yn Visual Basic (sy'n anodd iawn i ddechreuwyr) neu drwy gopïo â llaw undonog (sy'n cymryd amser hir ac yn cynhyrchu gwallau) y gellir cyflawni tasg o'r fath.
Cam 1. Mewngludo un ffeil fel sampl
Yn gyntaf, gadewch i ni fewnforio data o un llyfr gwaith fel enghraifft, fel bod Excel yn “codi'r syniad”. I wneud hyn, crëwch lyfr gwaith gwag newydd a…
- os oes gennych Excel 2016, yna agorwch y tab Dyddiad ac yna Creu Ymholiad - O Ffeil - O Lyfr (Data - Ymholiad Newydd - O ffeil - O Excel)
- os oes gennych Excel 2010-2013 gyda'r ategyn Power Query wedi'i osod, yna agorwch y tab Ymholiad Pwer a dewis arno O ffeil - O lyfr (O ffeil - O Excel)
Yna, yn y ffenestr sy'n agor, ewch i'n ffolder gydag adroddiadau a dewiswch unrhyw un o'r ffeiliau dinas (does dim ots pa un, oherwydd maen nhw i gyd yn nodweddiadol). Ar ôl ychydig eiliadau, dylai'r ffenestr Navigator ymddangos, lle mae angen i chi ddewis y daflen sydd ei hangen arnom (Gwerthiant) ar yr ochr chwith, a bydd ei chynnwys yn cael ei harddangos ar yr ochr dde:
Os cliciwch ar y botwm yng nghornel dde isaf y ffenestr hon Lawrlwytho (Llwyth), yna bydd y tabl yn cael ei fewnforio ar unwaith i'r daflen yn ei ffurf wreiddiol. Ar gyfer un ffeil, mae hyn yn dda, ond mae angen i ni lwytho llawer o ffeiliau o'r fath, felly byddwn yn mynd ychydig yn wahanol ac yn clicio ar y botwm Cywiro (Golygu). Ar ôl hynny, dylai golygydd ymholiad Power Query gael ei arddangos mewn ffenestr ar wahân gyda'n data o'r llyfr:
Mae hwn yn arf pwerus iawn sy'n eich galluogi i "orffen" y bwrdd i'r olygfa sydd ei hangen arnom. Byddai hyd yn oed disgrifiad arwynebol o'i holl swyddogaethau yn cymryd tua chant o dudalennau, ond, os yn fyr iawn, gan ddefnyddio'r ffenestr hon gallwch:
- hidlo allan data diangen, llinellau gwag, llinellau gyda gwallau
- didoli data yn ôl un neu fwy o golofnau
- cael gwared ar ailadrodd
- rhannu testun gludiog â cholofnau (yn ôl amffinyddion, nifer y nodau, ac ati)
- rhoi testun mewn trefn (tynnwch fylchau ychwanegol, cas gywir, ac ati)
- trosi mathau o ddata ym mhob ffordd bosibl (trowch rifau fel testun i rifau arferol ac i'r gwrthwyneb)
- trawsosod (cylchdroi) tablau ac ehangu croesfyrddau dau ddimensiwn yn rhai gwastad
- ychwanegu colofnau ychwanegol at y tabl a defnyddio fformiwlâu a swyddogaethau ynddynt gan ddefnyddio'r iaith M sydd wedi'i chynnwys yn Power Query.
- ...
Er enghraifft, gadewch i ni ychwanegu colofn gydag enw testun y mis at ein bwrdd, fel y byddai'n haws adeiladu adroddiadau tabl colyn yn ddiweddarach. I wneud hyn, de-gliciwch ar bennawd y golofn dyddiada dewiswch y gorchymyn Colofn ddyblyg (Colofn Dyblyg), ac yna de-gliciwch ar bennawd y golofn ddyblyg sy'n ymddangos a dewiswch Commands Trawsnewid – Mis – Enw Mis:
Dylid ffurfio colofn newydd gydag enwau testun y mis ar gyfer pob rhes. Trwy glicio ddwywaith ar bennawd colofn, gallwch ei ailenwi o Dyddiad Copi i fwy cyfforddus Mis, ee.
Os nad oedd y rhaglen yn adnabod y math o ddata yn gywir mewn rhai colofnau, yna gallwch chi ei helpu trwy glicio ar yr eicon fformat ar ochr chwith pob colofn:
Gallwch wahardd llinellau â gwallau neu linellau gwag, yn ogystal â rheolwyr neu gwsmeriaid diangen, gan ddefnyddio hidlydd syml:
Ar ben hynny, mae'r holl drawsnewidiadau perfformio wedi'u gosod yn y panel cywir, lle gellir eu rholio'n ôl (croes) neu newid eu paramedrau (gêr):
Ysgafn a chain, ynte?
Cam 2. Gadewch i ni drawsnewid ein cais yn swyddogaeth
Er mwyn ailadrodd yr holl drawsnewidiadau data a wnaed ar gyfer pob llyfr a fewnforir, mae angen i ni drosi ein cais a grëwyd yn swyddogaeth, a fydd wedyn yn cael ei gymhwyso, yn ei dro, i'n holl ffeiliau. Mae gwneud hyn mewn gwirionedd yn syml iawn.
Yn y Golygydd Ymholiad, ewch i'r tab View a chliciwch ar y botwm Uwch Olygydd (Gweld - Golygydd Uwch). Dylai ffenestr agor lle bydd ein holl weithredoedd blaenorol yn cael eu hysgrifennu ar ffurf cod yn yr iaith M. Sylwch fod y llwybr i'r ffeil a fewnforiwyd gennym ar gyfer yr enghraifft wedi'i god caled yn y cod:
Nawr, gadewch i ni wneud ychydig o addasiadau:
Mae eu hystyr yn syml: y llinell gyntaf (llwybr ffeil) => yn troi ein trefn yn swyddogaeth â dadl llwybr ffeil, ac isod rydym yn newid y llwybr sefydlog i werth y newidyn hwn.
I gyd. Cliciwch ar Gorffen a dylai weld hyn:
Peidiwch â bod ofn bod y data wedi diflannu - mewn gwirionedd, mae popeth yn iawn, dylai popeth edrych fel hyn 🙂 Rydym wedi creu ein swyddogaeth arferol yn llwyddiannus, lle mae'r algorithm cyfan ar gyfer mewnforio a phrosesu data yn cael ei gofio heb gael ei glymu i ffeil benodol . Erys i roi enw mwy dealladwy iddo (er enghraifft caelData) yn y panel ar y dde yn y cae Enw Cyntaf a gallwch fedi Hafan - Cau a lawrlwytho (Cartref - Cau a Llwyth). Sylwch fod y llwybr i'r ffeil a fewnforiwyd gennym ar gyfer yr enghraifft wedi'i god caled yn y cod. Byddwch yn dychwelyd i brif ffenestr Microsoft Excel, ond dylai panel gyda'r cysylltiad a grëwyd i'n swyddogaeth ymddangos ar y dde:
Cam 3. Casglu'r holl ffeiliau
Mae'r rhan anoddaf i gyd ar ei hôl hi, mae'r rhan ddymunol a hawdd yn aros. Ewch i'r tab Data - Creu Ymholiad - O Ffeil - O Ffolder (Data - Ymholiad Newydd - O'r ffeil - O'r ffolder) neu, os oes gennych Excel 2010-2013, yn yr un modd â'r tab Ymholiad Pwer. Yn y ffenestr sy'n ymddangos, nodwch y ffolder lle mae ein holl ffeiliau dinas ffynhonnell wedi'u lleoli a chliciwch OK. Dylai'r cam nesaf agor ffenestr lle bydd yr holl ffeiliau Excel a geir yn y ffolder hwn (a'i is-ffolderi) a manylion pob un ohonynt yn cael eu rhestru:
Cliciwch Newid (Golygu) ac eto rydym yn mynd i mewn i ffenestr gyfarwydd y golygydd ymholiad.
Nawr mae angen i ni ychwanegu colofn arall at ein tabl gyda'n swyddogaeth a grëwyd, a fydd yn “tynnu” y data o bob ffeil. I wneud hyn, ewch i'r tab Ychwanegu Colofn - Colofn Custom (Ychwanegu Colofn - Ychwanegu Colofn Custom) ac yn y ffenestr sy'n ymddangos, nodwch ein swyddogaeth caelData, gan nodi ar ei gyfer fel dadl y llwybr llawn i bob ffeil:
Ar ôl clicio ar OK dylid ychwanegu'r golofn a grëwyd at ein tabl ar y dde.
Nawr, gadewch i ni ddileu pob colofn ddiangen (fel yn Excel, gan ddefnyddio botwm de'r llygoden - Dileu), gan adael dim ond y golofn ychwanegol a'r golofn gydag enw'r ffeil, oherwydd bydd yr enw hwn (yn fwy manwl gywir, y ddinas) yn ddefnyddiol i'w gael yn y cyfanswm data ar gyfer pob rhes.
Ac yn awr y “foment waw” - cliciwch ar yr eicon gyda'i saethau ei hun yng nghornel dde uchaf y golofn ychwanegol gyda'n swyddogaeth:
… dad-diciwch Defnyddiwch enw'r golofn wreiddiol fel rhagddodiad (Defnyddiwch enw gwreiddiol y golofn fel rhagddodiad)a chliciwch OK. A bydd ein swyddogaeth yn llwytho ac yn prosesu'r data o bob ffeil, gan ddilyn yr algorithm a gofnodwyd a chasglu popeth mewn tabl cyffredin:
Ar gyfer harddwch llwyr, gallwch hefyd dynnu'r estyniadau .xlsx o'r golofn gyntaf gydag enwau ffeiliau - trwy amnewid safonol gyda “dim byd” (cliciwch ar y dde ar bennawd y golofn - Dirprwy) ac ailenwi'r golofn hon i Dinas. A hefyd cywirwch y fformat data yn y golofn gyda'r dyddiad.
I gyd! Cliciwch ar Cartref - Cau a Llwyth (Cartref - Cau a Llwyth). Bydd yr holl ddata a gesglir gan yr ymholiad ar gyfer pob dinas yn cael ei lanlwytho i'r daflen Excel gyfredol yn y fformat “tabl smart”:
Nid oes angen cadw'r cysylltiad a grëwyd a'n swyddogaeth cydosod ar wahân mewn unrhyw ffordd - maent yn cael eu cadw ynghyd â'r ffeil gyfredol yn y ffordd arferol.
Yn y dyfodol, gydag unrhyw newidiadau yn y ffolder (ychwanegu neu ddileu dinasoedd) neu mewn ffeiliau (newid nifer y llinellau), bydd yn ddigon de-glicio'n uniongyrchol ar y bwrdd neu ar yr ymholiad yn y panel cywir a dewis y gorchymyn Diweddaru ac Arbed (Adnewyddu) - Bydd Power Query yn “ailadeiladu” yr holl ddata eto mewn ychydig eiliadau.
PS
Diwygiad. Ar ôl diweddariadau Ionawr 2017, dysgodd Power Query sut i gasglu llyfrau gwaith Excel ar ei ben ei hun, hy nid oes angen gwneud swyddogaeth ar wahân mwyach - mae'n digwydd yn awtomatig. Felly, nid oes angen yr ail gam o'r erthygl hon bellach ac mae'r broses gyfan yn dod yn amlwg yn symlach:
- Dewiswch Creu Cais - O Ffeil - O Ffolder - Dewiswch Ffolder - Iawn
- Ar ôl i'r rhestr o ffeiliau ymddangos, pwyswch Newid
- Yn ffenestr y Golygydd Ymholiad, ehangwch y golofn Ddeuaidd gyda saeth ddwbl a dewiswch enw'r ddalen i'w chymryd o bob ffeil
A dyna i gyd! Cân!
- Ailgynllunio'r crosstab yn un fflat sy'n addas ar gyfer adeiladu byrddau colyn
- Adeiladu siart swigen animeiddiedig yn Power View
- Macro i gydosod dalennau o wahanol ffeiliau Excel yn un