Efnisyfirlit
Mótun vandans
Við skulum skoða fallega lausn fyrir eina af þeim mjög stöðluðu aðstæðum sem flestir Excel notendur standa frammi fyrir fyrr eða síðar: þú þarft að safna gögnum úr miklum fjölda skráa á fljótlegan og sjálfvirkan hátt í eina lokatöflu.
Segjum að við höfum eftirfarandi möppu, sem inniheldur nokkrar skrár með gögnum frá útibúsborgum:
Fjöldi skráa skiptir ekki máli og gæti breyst í framtíðinni. Hver skrá hefur blað sem heitir Salahvar gagnataflan er staðsett:
Fjöldi raða (raða) í töflunum er auðvitað mismunandi, en dálkasamstæðan er alls staðar staðlað.
Verkefni: að safna gögnum úr öllum skrám í eina bók með sjálfvirkri uppfærslu í kjölfarið þegar borgarskrám eða línum í töflum er bætt við eða eytt. Samkvæmt endanlegri samstæðutöflu, þá verður hægt að byggja hvaða skýrslur sem er, pivot töflur, síu-flokkað gögn osfrv. Aðalatriðið er að geta safnað.
Við veljum vopn
Fyrir lausnina þurfum við nýjustu útgáfuna af Excel 2016 (nauðsynleg virkni er nú þegar innbyggð í hana sjálfgefið) eða fyrri útgáfur af Excel 2010-2013 með ókeypis viðbótinni uppsettu Orkufyrirspurn frá Microsoft (halaðu því niður hér). Power Query er ofursveigjanlegt og ofur öflugt tól til að hlaða gögnum inn í Excel frá umheiminum, svo afmá þau og vinna úr þeim. Power Query styður næstum allar núverandi gagnagjafar – allt frá textaskrám til SQL og jafnvel Facebook 🙂
Ef þú ert ekki með Excel 2013 eða 2016, þá geturðu ekki lesið lengra (bara að grínast). Í eldri útgáfum af Excel er slíku verkefni aðeins hægt að framkvæma með því að forrita fjölvi í Visual Basic (sem er mjög erfitt fyrir byrjendur) eða með eintóna handvirkri afritun (sem tekur langan tíma og skapar villur).
Skref 1. Flytja inn eina skrá sem sýnishorn
Fyrst skulum við flytja inn gögn úr einni vinnubók sem dæmi, svo að Excel „taki upp hugmyndina“. Til að gera þetta skaltu búa til nýja tóma vinnubók og ...
- ef þú ert með Excel 2016, opnaðu þá flipann Gögn og þá Búa til fyrirspurn - Úr skrá - Úr bók (Gögn - Ný fyrirspurn - Úr skrá - Frá Excel)
- ef þú ert með Excel 2010-2013 með Power Query viðbótina uppsetta skaltu opna flipann Orkufyrirspurn og veldu á það Úr skrá – Úr bók (Úr skrá - Frá Excel)
Síðan, í glugganum sem opnast, farðu í möppuna okkar með skýrslum og veldu hvaða borgarskrár sem er (það skiptir ekki máli hver, því þær eru allar dæmigerðar). Eftir nokkrar sekúndur ætti Navigator glugginn að birtast, þar sem þú þarft að velja blaðið sem við þurfum (Sala) vinstra megin og innihald þess birtist hægra megin:
Ef þú smellir á hnappinn neðst í hægra horninu í þessum glugga Eyðublað (Hlaða), þá verður taflan strax flutt inn á blaðið í upprunalegri mynd. Fyrir eina skrá er þetta gott, en við þurfum að hlaða mörgum slíkum skrám, svo við förum aðeins öðruvísi og smellum á hnappinn Leiðrétting (Breyta). Eftir það ætti Power Query fyrirspurnaritillinn að birtast í sérstökum glugga með gögnum okkar úr bókinni:
Þetta er mjög öflugt tól sem gerir þér kleift að „klára“ borðið í þá mynd sem við þurfum. Jafnvel yfirborðsleg lýsing á öllum aðgerðum þess myndi taka um hundrað blaðsíður, en ef það er mjög stutt, með því að nota þennan glugga geturðu:
- sía út óþarfa gögn, tómar línur, línur með villum
- raða gögnum eftir einum eða fleiri dálkum
- losna við endurtekningar
- deila klístruðum texta eftir dálkum (eftir afmörkun, fjölda stafa o.s.frv.)
- setja texta í röð (fjarlægðu aukabil, leiðréttu hástafi osfrv.)
- umbreyttu gagnategundum á allan mögulegan hátt (breyttu tölum eins og texta í venjulegar tölur og öfugt)
- umbreyta (snúa) töflum og stækka tvívíðar krosstöflur í flatar
- bættu viðbótardálkum við töfluna og notaðu formúlur og aðgerðir í þeim með því að nota M tungumálið sem er innbyggt í Power Query.
- ...
Við skulum til dæmis bæta dálki með textaheiti mánaðarins við töfluna okkar, svo að seinna verði auðveldara að búa til pivot-töfluskýrslur. Til að gera þetta skaltu hægrismella á dálkfyrirsögnina dagsetningog veldu skipunina Tvítekið dálk (Tvítekinn dálkur), og hægrismelltu síðan á hausinn á tvíteknum dálki sem birtist og veldu Skipanir Umbreyta – Mánaðar – Mánaðarheiti:
Nýr dálkur ætti að myndast með textaheitum mánaðarins fyrir hverja línu. Með því að tvísmella á dálkfyrirsögn er hægt að endurnefna hana frá Afrita dagsetningu til þægilegra Mánuður, td.
Ef forritið þekkti ekki gagnagerðina alveg rétt í sumum dálkum, þá geturðu hjálpað því með því að smella á sniðstáknið vinstra megin við hvern dálk:
Þú getur útilokað línur með villum eða tómum línum, svo og óþarfa stjórnendur eða viðskiptavini, með því að nota einfalda síu:
Þar að auki eru allar framkvæmdar umbreytingar festar á hægri spjaldið, þar sem alltaf er hægt að rúlla þeim til baka (kross) eða breyta breytum þeirra (gír):
Létt og glæsileg, er það ekki?
Skref 2. Við skulum breyta beiðni okkar í fall
Til þess að endurtaka allar gagnabreytingar sem gerðar eru fyrir hverja innflutta bók í kjölfarið, þurfum við að umbreyta sköpuðu beiðni okkar í fall, sem síðan verður beitt á allar skrárnar okkar. Að gera þetta er í raun mjög einfalt.
Í fyrirspurnarritlinum, farðu í View flipann og smelltu á hnappinn Háþróaður ritstjóri (Skoða — Ítarleg ritstjóri). Gluggi ætti að opnast þar sem allar fyrri aðgerðir okkar verða skrifaðar í formi kóða á M tungumálinu. Vinsamlegast athugaðu að slóðin að skránni sem við fluttum inn fyrir dæmið er harðkóða í kóðanum:
Nú skulum við gera nokkrar breytingar:
Merking þeirra er einföld: fyrsta línan (skráarslóð)=> breytir málsmeðferðinni okkar í fall með rökum skráarslóð, og hér að neðan breytum við föstu leiðinni í gildi þessarar breytu.
Allt. Smelltu á Ljúka og ætti að sjá þetta:
Ekki vera hrædd um að gögnin séu horfin – í raun er allt í lagi, allt ætti að líta svona út 🙂 Við höfum búið til sérsniðna aðgerðina okkar, þar sem allt reikniritið fyrir innflutning og vinnslu gagna er munað án þess að vera bundið við ákveðna skrá . Það á eftir að gefa því skiljanlegra nafn (td getData) í spjaldinu hægra megin í reitnum Fornafn og þú getur uppskorið Heima — Loka og hlaða niður (Heima - Loka og hlaða). Vinsamlegast athugaðu að slóðin að skránni sem við fluttum inn fyrir dæmið er harðkóðun í kóðanum. Þú munt fara aftur í aðal Microsoft Excel gluggann, en spjaldið með tengingunni við aðgerðina okkar ætti að birtast til hægri:
Skref 3. Safna öllum skrám
Allt það erfiðasta er að baki, skemmtilegi og auðveldi hlutinn er eftir. Farðu í flipann Gögn – Búa til fyrirspurn – Úr skrá – Úr möppu (Gögn — Ný fyrirspurn — Úr skrá — Úr möppu) eða, ef þú ert með Excel 2010-2013, svipað og flipann Orkufyrirspurn. Í glugganum sem birtist skaltu tilgreina möppuna þar sem allar upprunaborgarskrárnar okkar eru staðsettar og smelltu OK. Næsta skref ætti að opna glugga þar sem allar Excel skrár sem finnast í þessari möppu (og undirmöppur hennar) og upplýsingar um hverja þeirra verða skráðar:
Smellur Breyta (Breyta) og aftur komumst við inn í kunnuglega fyrirspurnarritaragluggann.
Nú þurfum við að bæta öðrum dálki við töfluna okkar með aðgerðinni okkar sem búið var til, sem mun „toga“ gögnin úr hverri skrá. Til að gera þetta, farðu í flipann Bæta við dálki - Sérsniðinn dálk (Bæta við dálki - Bæta við sérsniðnum dálki) og sláðu inn aðgerðina okkar í glugganum sem birtist getData, tilgreina fyrir það sem rök alla slóðina að hverri skrá:
Eftir að smella á OK búið til dálkinn ætti að bæta við töfluna okkar til hægri.
Nú skulum við eyða öllum óþarfa dálkum (eins og í Excel, með því að nota hægri músarhnappinn - Fjarlægja), og skilur aðeins viðbættan dálkinn og dálkinn eftir með skráarnafninu, því þetta nafn (nánar tiltekið, borgin) mun vera gagnlegt að hafa í heildargögnum fyrir hverja röð.
Og nú „vá augnablikið“ - smelltu á táknið með eigin örvum í efra hægra horninu á dálknum sem bætt var við með aðgerðinni okkar:
… hakið úr Notaðu upprunalega dálknafnið sem forskeyti (Notaðu upprunalegt dálknafn sem forskeyti)og smelltu OK. Og aðgerðin okkar mun hlaða og vinna úr gögnum úr hverri skrá, fylgja skráðu reikniritinu og safna öllu í sameiginlega töflu:
Fyrir fullkomna fegurð geturðu líka fjarlægt .xlsx viðbæturnar úr fyrsta dálknum með skráarnöfnum - með því að skipta út venjulegu fyrir "ekkert" (hægrismelltu á dálkhausinn - Staðgengill) og endurnefna þennan dálk í Borg. Og leiðréttu einnig gagnasniðið í dálknum með dagsetningunni.
Allt! Smelltu á Heim - Lokaðu og hlaða (Heima — Loka og hlaða). Öllum gögnum sem safnað er með fyrirspurninni fyrir allar borgir verður hlaðið upp á núverandi Excel blað á „snjalltafla“ sniði:
Ekki þarf að vista tenginguna og samsetningaraðgerðina okkar sérstaklega á nokkurn hátt - þær eru vistaðar ásamt núverandi skrá á venjulegan hátt.
Í framtíðinni, með öllum breytingum á möppunni (bæta við eða fjarlægja borgir) eða á skrám (breyta fjölda lína), mun það vera nóg að hægrismella beint á töfluna eða á fyrirspurnina í hægra spjaldinu og velja skipun Uppfærðu og vistaðu (Endurnýja) – Power Query mun „endurbyggja“ öll gögnin aftur á nokkrum sekúndum.
PS
Breyting. Eftir uppfærslurnar í janúar 2017 lærði Power Query hvernig á að safna Excel vinnubókum af sjálfu sér, þ.e. engin þörf á að búa til sérstaka aðgerð lengur - það gerist sjálfkrafa. Þannig er annað skrefið úr þessari grein ekki lengur þörf og allt ferlið verður áberandi einfaldara:
- Veldu Búa til beiðni – Úr skrá – Úr möppu – Veldu möppu – Í lagi
- Eftir að listi yfir skrár birtist skaltu ýta á Breyta
- Í fyrirspurnarritlinum skaltu stækka tvíundar dálkinn með tvöfaldri ör og velja nafn blaðsins sem á að taka úr hverri skrá
Og það er allt! Lag!
- Endurhönnun krosstöflunnar í flatan sem hentar til að byggja snúningsborð
- Að búa til hreyfimyndatöflu í Power View
- Fjölvi til að setja saman blöð úr mismunandi Excel skrám í eitt