Efnisyfirlit
Mótun vandans
Sem inntaksgögn höfum við Excel skrá, þar sem eitt blaðanna inniheldur nokkrar töflur með sölugögnum á eftirfarandi formi:
Athugaðu að:
- Töflur af mismunandi stærðum og með mismunandi settum af vörum og svæðum í röðum og dálkum án nokkurrar flokkunar.
- Hægt er að setja inn tómar línur á milli taflna.
- Fjöldi borða getur verið hvaða sem er.
Tvær mikilvægar forsendur. Gert er ráð fyrir að:
- Fyrir ofan hverja töflu, í fyrsta dálki, er nafn framkvæmdastjórans sem töflunni sýnir sölu hans (Ivanov, Petrov, Sidorov o.s.frv.)
- Heiti vöru og svæða í öllum töflum eru skrifuð á sama hátt - með nákvæmni.
Lokamarkmiðið er að safna gögnum úr öllum töflum í eina flata staðlaða töflu, þægilegt fyrir síðari greiningu og að búa til samantekt, þ.e. í þessari:
Skref 1. Tengstu við skrána
Við skulum búa til nýja tóma Excel skrá og velja hana á flipanum Gögn Skipun Fáðu gögn - úr skrá - úr bók (Gögn — Úr skrá — Úr vinnubók). Tilgreindu staðsetningu upprunaskrárinnar með sölugögnum og veldu síðan í flakkaglugganum blaðið sem við þurfum og smelltu á hnappinn Umbreyta gögnum (Umbreyta gögnum):
Þar af leiðandi ætti að hlaða öllum gögnum úr því inn í Power Query ritilinn:
Skref 2. Hreinsaðu upp ruslið
Eyða sjálfkrafa mynduðum skrefum breytt gerð (Breytt gerð) и Hækkaðir hausar (Kostaðir hausar) og losaðu þig við tómar línur og línur með heildartölum með því að nota síu null и SAMTALS við fyrsta dálkinn. Fyrir vikið fáum við eftirfarandi mynd:
Skref 3. Bæta við stjórnendum
Til að skilja síðar hvar salan er, er nauðsynlegt að bæta dálki við töfluna okkar, þar sem í hverri röð verður samsvarandi eftirnafn. Fyrir þetta:
1. Við skulum bæta við aukadálki með línunúmerum með því að nota skipunina Bæta við dálki – Vísitöludálki – Frá 0 (Bæta við dálki - Vísindadálki - Frá 0).
2. Bættu við dálki með formúlu með skipuninni Að bæta við dálki - Sérsniðinn dálkur (Bæta við dálki - sérsniðinn dálkur) og kynna þar eftirfarandi smíði:
Rökfræði þessarar formúlu er einföld - ef gildi næsta reits í fyrsta dálki er „Vöru“, þá þýðir það að við höfum rekist á upphaf nýrrar töflu, þannig að við birtum gildi fyrri reits með nafn framkvæmdastjóra. Annars birtum við ekkert, þ.e. núll.
Til að fá foreldrahólfið með eftirnafninu vísum við fyrst í töfluna frá fyrra skrefi #“Vísi bætt við“, og tilgreindu síðan nafn dálksins sem við þurfum [Dálkur1] innan hornklofa og frumunúmerið í þeim dálki innan krullaðra sviga. Hólfnúmerið verður einum færra en núverandi, sem við tökum úr dálknum Index, í sömu röð.
3. Það á eftir að fylla út í tómu hólfin með null nöfn úr hærri frumum með skipuninni Umbreyta – Fylla – Niður (Umbreyta - Fylla - Niður) og eyða dálknum sem ekki þarf lengur með vísitölum og línum með eftirnöfnum í fyrsta dálknum. Fyrir vikið fáum við:
Skref 4. Flokkun í aðskildar töflur eftir stjórnendum
Næsta skref er að flokka línurnar fyrir hvern stjórnanda í aðskildar töflur. Til að gera þetta, á Umbreytingaflipanum, notaðu Flokka eftir skipun (Umbreyta – Flokka eftir) og í glugganum sem opnast velurðu Stjórnandi dálkinn og aðgerðina Allar línur (Allar línur) til að safna einfaldlega gögnum án þess að nota neina söfnunaraðgerð á þær (summa, meðaltal osfrv.). P.):
Fyrir vikið fáum við sérstakar töflur fyrir hvern stjórnanda:
Skref 5: Umbreyttu hreiðri töflum
Nú gefum við töflurnar sem liggja í hverri reit í dálknum sem myndast Öll gögn í þokkalegu formi.
Fyrst skaltu eyða dálki sem er ekki lengur þörf í hverri töflu framkvæmdastjóri. Við notum aftur Sérsniðinn dálkur flipi Umbreyting (Umbreyta - sérsniðinn dálkur) og eftirfarandi formúla:
Síðan, með öðrum útreiknuðum dálki, lyftum við fyrstu röðinni í hverri töflu upp í fyrirsagnirnar:
Og að lokum framkvæmum við helstu umbreytingu - að brjóta upp hvert borð með M-fallinu Tafla.UnpivotAðrir dálkar:
Nöfn svæðanna úr hausnum fara í nýjan dálk og við fáum þrengri en um leið lengri eðlilega töflu. Tómar frumur með null eru hunsuð.
Til að losna við óþarfa millidálka höfum við:
Skref 6 Stækkaðu hreiður töflur
Það er eftir að stækka allar eðlilegar hreiður töflur í einn lista með því að nota hnappinn með tvöföldum örvum í dálkhausnum:
… og loksins fáum við það sem við vildum:
Þú getur flutt töfluna sem myndast aftur í Excel með skipuninni Heim — Loka og hlaða — Loka og hlaða inn... (Heima — Loka&hlaða — Loka&hlaða til...).
- Búðu til töflur með mismunandi hausum úr mörgum bókum
- Að safna gögnum úr öllum skrám í tiltekinni möppu
- Að safna gögnum úr öllum blöðum bókarinnar í eina töflu