Sisu
Probleemi sõnastamine
Sisendandmetena on meil Exceli fail, kus ühel lehel on mitu tabelit müügiandmetega järgmisel kujul:
Pange tähele, et:
- Erinevas suuruses ja erinevate toodete ja piirkondadega tabelid ridades ja veergudes ilma sorteerimiseta.
- Tabelite vahele saab sisestada tühje ridu.
- Tabelite arv võib olla mis tahes.
Kaks olulist eeldust. Eeldatakse, et:
- Iga tabeli kohal, esimeses veerus, on juhi nimi, kelle müüki tabel illustreerib (Ivanov, Petrov, Sidorov jne)
- Kaupade ja piirkondade nimetused on kõikides tabelites kirjutatud ühtemoodi – käände täpsusega.
Lõppeesmärk on koguda andmed kõigist tabelitest ühte tasasele normaliseeritud tabelisse, mis on mugav hilisemaks analüüsiks ja kokkuvõtte koostamiseks, st selles:
Samm 1. Ühendage failiga
Loome uue tühja Exceli faili ja valime selle vahekaardilt kuupäev käsk Andmete hankimine – failist – raamatust (Andmed — failist — töövihikust). Määrake müügiandmetega lähtefaili asukoht ja seejärel valige navigaatori aknas meile vajalik leht ja klõpsake nuppu Teisenda andmed (teisenda andmed):
Selle tulemusena tuleks kõik selle andmed laadida Power Query redaktorisse:
2. samm. Koristage prügikast
Kustutage automaatselt loodud sammud muudetud tüüp (Muudetud tüüp) и Kõrgendatud päised (Reklaamitud päised) ja vabaneda tühjadest ridadest ja kogusummadega ridadest filtri abil tühjaks и KOKKU esimese veeru järgi. Selle tulemusena saame järgmise pildi:
3. samm. Haldurite lisamine
Et hiljem aru saada, kus kelle müük on, on vaja meie tabelisse lisada veerg, kus igal real on vastav perekonnanimi. Selle jaoks:
1. Lisame käsu abil reanumbritega abiveerg Lisa veerg – indeksi veerg – alates 0 (Lisa veerg — indeksi veerg — alates 0).
2. Lisage käsuga veerg valemiga Veeru lisamine – kohandatud veerg (Lisa veerg – kohandatud veerg) ja tutvustage seal järgmist konstruktsiooni:
Selle valemi loogika on lihtne – kui esimese veeru järgmise lahtri väärtus on “Toode”, siis see tähendab, et oleme sattunud uue tabeli algusesse, mistõttu kuvame eelmise lahtri väärtuse juhataja nimi. Muidu me midagi ei kuva, st null.
Perekonnanimega vanemlahtri saamiseks viitame esmalt eelmise etapi tabelile #"Indeks lisatud"ja seejärel määrake meile vajaliku veeru nimi [Veerg1] nurksulgudes ja lahtri number selles veerus lokkis sulgudes. Lahtri number on ühe võrra väiksem kui praegune, mille võtame veerust indeks, Vastavalt.
3. Jääb üle tühjad lahtrid täita tühjaks nimed kõrgematest rakkudest käsuga Teisenda – Täida – Alla (Teisenda – Täida – alla) ja kustutage esimesest veerust enam mittevajalik veerg koos indeksite ja perekonnanimedega ridadega. Selle tulemusena saame:
4. samm. Rühmitamine eraldi tabeliteks juhtide kaupa
Järgmine samm on rühmitada iga halduri read eraldi tabelitesse. Selleks kasutage vahekaardil Transformation käsku Group by (Transform – Group By) ja valige avanevas aknas veerg Haldur ja toiming Kõik read (Kõik read), et lihtsalt koguda andmeid ilma koondamisfunktsiooni rakendamata. (summa, keskmine jne). P.):
Selle tulemusena saame iga halduri jaoks eraldi tabelid:
5. samm: teisendage pesastatud tabelid
Nüüd anname tabelid, mis asuvad saadud veeru igas lahtris Kõik andmed korralikus vormis.
Kõigepealt kustutage igas tabelis veerg, mida enam ei vajata Juht. Kasutame uuesti Kohandatud veerg tab Transformation (Teisendus – kohandatud veerg) ja järgmine valem:
Seejärel tõstame teise arvutatud veeruga iga tabeli esimese rea pealkirjadesse:
Ja lõpuks teostame peamise teisenduse – iga tabeli lahti voltimise M-funktsiooni abil Tabel.UnpivotOtherColumns:
Päisest pärit piirkondade nimed lähevad uude veergu ja saame kitsama, kuid samas pikema normaliseeritud tabeli. Tühjendage lahtrid koos tühjaks eiratakse.
Vabanedes tarbetutest vahepealsetest veergudest, on meil:
6. samm Laiendage pesastatud tabelid
Kõik normaliseeritud pesastatud tabelid tuleb laiendada üheks loendiks, kasutades veeru päises topeltnooltega nuppu:
… ja lõpuks saame selle, mida tahtsime:
Saadud tabeli saate käsuga Excelisse eksportida Avaleht — sulge ja laadi — sulge ja laadi... (Avaleht — Sulge&laadi — Sule&laadi...).
- Koostage mitmest raamatust erinevate päistega tabeleid
- Andmete kogumine kõigist antud kaustas olevatest failidest
- Andmete kogumine raamatu kõigilt lehtedelt ühte tabelisse