Turinys
Problemos formulavimas
Kaip įvesties duomenis turime Excel failą, kuriame viename iš lapų yra kelios lentelės su tokios formos pardavimo duomenimis:
Prisimink tai:
- Įvairių dydžių lentelės su skirtingais produktų rinkiniais ir regionais eilutėse ir stulpeliuose be jokio rūšiavimo.
- Tarp lentelių galima įterpti tuščias eilutes.
- Lentelių skaičius gali būti bet koks.
Dvi svarbios prielaidos. Manoma, kad:
- Virš kiekvienos lentelės pirmame stulpelyje yra vadovo, kurio pardavimus iliustruoja lentelė, pavardė (Ivanovas, Petrovas, Sidorovas ir kt.)
- Prekių ir regionų pavadinimai visose lentelėse rašomi vienodai – raidžių tikslumu.
Galutinis tikslas yra surinkti duomenis iš visų lentelių į vieną plokščią normalizuotą lentelę, patogią tolesnei analizei ir santraukos sudarymui, ty šioje:
1 veiksmas. Prisijunkite prie failo
Sukurkime naują tuščią „Excel“ failą ir pasirinkite jį skirtuke Duomenys Komanda Gaukite duomenis – iš failo – iš knygos (Duomenys – iš failo – iš darbaknygės). Nurodykite šaltinio failo vietą su pardavimo duomenimis, tada navigatoriaus lange pasirinkite mums reikalingą lapą ir spustelėkite mygtuką Konvertuoti duomenis (Transformuoti duomenis):
Dėl to visi duomenys iš jo turėtų būti įkelti į Power Query redaktorių:
2 veiksmas. Išvalykite šiukšles
Ištrinkite automatiškai sugeneruotus veiksmus modifikuotas tipas (Pakeistas tipas) и Paaukštintos antraštės (Reklamuojamos antraštės) ir pašalinkite tuščias eilutes bei eilutes su sumomis naudodami filtrą null и IŠ VISO pagal pirmą stulpelį. Dėl to gauname tokį vaizdą:
3 veiksmas. Vadovų pridėjimas
Norint vėliau suprasti, kur yra pardavimai, būtina į mūsų lentelę įtraukti stulpelį, kur kiekvienoje eilutėje bus atitinkama pavardė. Už tai:
1. Naudodami komandą pridėkime pagalbinį stulpelį su eilučių numeriais Pridėti stulpelį – rodyklės stulpelį – nuo 0 (Pridėti stulpelį – rodyklės stulpelį – nuo 0).
2. Su komanda pridėkite stulpelį su formule Stulpelio pridėjimas – pasirinktinis stulpelis (Pridėti stulpelį – tinkintas stulpelis) ir ten pristatyti tokią konstrukciją:
Šios formulės logika paprasta – jei kito langelio reikšmė pirmame stulpelyje yra „Produktas“, tai reiškia, kad mes suklupome ant naujos lentelės pradžios, todėl ankstesnio langelio reikšmę rodome su vadovo pavardė. Kitu atveju nieko nerodome, ty nul.
Norėdami gauti pirminį langelį su pavarde, pirmiausia kreipiamės į lentelę iš ankstesnio veiksmo #„Indeksas pridėtas“, tada nurodykite mums reikalingo stulpelio pavadinimą [1 stulpelis] laužtiniuose skliaustuose, o langelio numeris tame stulpelyje – riestiniuose skliaustuose. Ląstelių skaičius bus vienu mažesnis nei dabartinis, kurį paimame iš stulpelio rodyklė, Atitinkamai.
3. Belieka užpildyti tuščias ląsteles null vardai iš aukštesnių langelių su komanda Transformuoti – užpildyti – žemyn (Transformuoti – užpildyti – žemyn) ir ištrinkite nebereikalingą stulpelį su indeksais ir eilutes su pavardėmis pirmame stulpelyje. Dėl to gauname:
4 žingsnis. Grupavimas į atskiras lenteles pagal vadovus
Kitas žingsnis – sugrupuoti kiekvieno valdytojo eilutes į atskiras lenteles. Norėdami tai padaryti, skirtuke Transformacija naudokite komandą Grupuoti pagal (Transform – Grupuoti pagal) ir atsidariusiame lange pasirinkite stulpelį Valdytojas ir operaciją Visos eilutės (Visos eilutės), kad tiesiog rinktumėte duomenis netaikant jokios agregavimo funkcijos. juos (suma, vidurkis ir kt.). P.):
Dėl to kiekvienam valdytojui gauname atskiras lenteles:
5 veiksmas: pakeiskite įdėtas lenteles
Dabar pateikiame lenteles, kurios yra kiekviename gauto stulpelio langelyje Visi duomenys padorios formos.
Pirmiausia ištrinkite stulpelį, kurio nebereikia kiekvienoje lentelėje Vadovas. Vėl naudojame Pasirinktinis stulpelis kortelė Transformacija (Transformacija – tinkintas stulpelis) ir ši formulė:
Tada su kitu apskaičiuotu stulpeliu pakeliame pirmąją kiekvienos lentelės eilutę į antraštes:
Ir galiausiai atliekame pagrindinę transformaciją – kiekvienos lentelės išlankstymą naudojant M funkciją Lentelė.UnpivotOtherColumns:
Regionų pavadinimai iš antraštės pateks į naują stulpelį ir gausime siauresnę, bet tuo pačiu ir ilgesnę normalizuotą lentelę. Tuščios ląstelės su null yra ignoruojami.
Atsikratydami nereikalingų tarpinių stulpelių turime:
6 veiksmas Išplėskite įdėtas lenteles
Belieka išplėsti visas normalizuotas įdėtas lenteles į vieną sąrašą, naudojant mygtuką su dvigubomis rodyklėmis stulpelio antraštėje:
... ir pagaliau gauname tai, ko norėjome:
Galite eksportuoti gautą lentelę atgal į „Excel“ naudodami komandą Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...).
- Kurkite lenteles su skirtingomis antraštėmis iš kelių knygų
- Duomenų rinkimas iš visų failų duotame aplanke
- Duomenų surinkimas iš visų knygos lapų į vieną lentelę