vsebina
Formulacija problema
Kot vhodne podatke imamo Excel datoteko, kjer je na enem od listov več tabel s podatki o prodaji naslednje oblike:
Upoštevajte, da:
- Tabele različnih velikosti in z različnimi nabori izdelkov in regij v vrsticah in stolpcih brez razvrščanja.
- Med tabele lahko vstavite prazne vrstice.
- Število tabel je lahko poljubno.
Dve pomembni predpostavki. Predpostavlja se, da:
- Nad vsako tabelo je v prvem stolpcu ime vodje, čigar prodajo tabela ponazarja (Ivanov, Petrov, Sidorov itd.)
- Imena blaga in regij so v vseh tabelah zapisana enako – z velikimi in malimi črkami.
Končni cilj je zbrati podatke iz vseh tabel v eno ravno normalizirano tabelo, primerno za kasnejšo analizo in sestavljanje povzetka, tj.
Korak 1. Povežite se z datoteko
Ustvarimo novo prazno datoteko Excel in jo izberimo na zavihku datum Ukaz Pridobite podatke – iz datoteke – iz knjige (Podatki — Iz datoteke — Iz delovnega zvezka). Določite lokacijo izvorne datoteke s podatki o prodaji in nato v oknu navigatorja izberite list, ki ga potrebujemo, in kliknite gumb Pretvori podatke (Pretvori podatke):
Posledično bi morali biti vsi podatki iz njega naloženi v urejevalnik Power Query:
2. korak. Počistite smeti
Izbrišite samodejno ustvarjene korake spremenjeni tip (Spremenjena vrsta) и Povišane glave (Promovirane glave) in se s filtrom znebite praznih vrstic in vrstic s skupnimi vrednostmi null и SKUPAJ po prvem stolpcu. Kot rezultat dobimo naslednjo sliko:
Korak 3. Dodajanje upraviteljev
Da bi kasneje razumeli, kje je čigava prodaja, je treba v našo tabelo dodati stolpec, kjer bo v vsaki vrstici ustrezen priimek. Za to:
1. Z ukazom dodamo pomožni stolpec s številkami vrstic Dodaj stolpec – Indeksni stolpec – Od 0 (Dodaj stolpec — Stolpec indeks — Od 0).
2. Z ukazom dodajte stolpec s formulo Dodajanje stolpca – stolpec po meri (Dodaj stolpec — stolpec po meri) in tam uvedite naslednjo konstrukcijo:
Logika te formule je preprosta – če je vrednost naslednje celice v prvem stolpcu »Produkt«, potem to pomeni, da smo naleteli na začetek nove tabele, zato prikažemo vrednost prejšnje celice z ime upravitelja. V nasprotnem primeru ne prikažemo ničesar, tj.
Da dobimo nadrejeno celico s priimkom, se najprej obrnemo na tabelo iz prejšnjega koraka #»Dodan indeks«, in nato določite ime stolpca, ki ga potrebujemo [Stolpec 1] v oglatih oklepajih in številko celice v tem stolpcu v zavitih oklepajih. Številka celice bo za eno manjša od trenutne, ki jo vzamemo iz stolpca KazaloOz.
3. Ostaja še zapolniti prazne celice z null imena iz višjih celic z ukazom Transformiraj – Zapolni – Dol (Pretvorba — Polnjenje — Dol) in izbrišite nepotreben stolpec z indeksi in vrsticami s priimki v prvem stolpcu. Kot rezultat dobimo:
Korak 4. Združevanje v ločene tabele po upravljavcih
Naslednji korak je združevanje vrstic za vsakega upravitelja v ločene tabele. To storite tako, da na zavihku Transformacija uporabite ukaz Združi po (Transform – Group By) in v oknu, ki se odpre, izberete stolpec Upravitelj in operacijo Vse vrstice (Vse vrstice), da preprosto zberete podatke brez uporabe kakršne koli funkcije združevanja njih (vsota, povprečje itd.). P.):
Kot rezultat dobimo ločene tabele za vsakega upravitelja:
5. korak: Pretvorite ugnezdene tabele
Zdaj podajamo tabele, ki ležijo v vsaki celici nastalega stolpca Vsi podatki v spodobni formi.
Najprej v vsaki tabeli izbrišite stolpec, ki ga ne potrebujete več Manager. Ponovno uporabljamo Stolpec po meri tab Preoblikovanje (Pretvorba — stolpec po meri) in naslednja formula:
Nato z drugim izračunanim stolpcem dvignemo prvo vrstico v vsaki tabeli do naslovov:
In končno izvedemo glavno transformacijo – razgrnemo vsako tabelo z uporabo M-funkcije Table.UnpivotOtherColumns:
Imena regij iz glave bodo šla v nov stolpec in dobili bomo ožjo, a hkrati daljšo normirano tabelo. Prazne celice z null se ne upoštevajo.
Če se znebimo nepotrebnih vmesnih stolpcev, imamo:
6. korak Razširite ugnezdene tabele
Vse normalizirane ugnezdene tabele je treba razširiti v en seznam z uporabo gumba z dvojnimi puščicami v glavi stolpca:
... in končno dobimo, kar smo želeli:
Nastalo tabelo lahko izvozite nazaj v Excel z ukazom Domov — Zapri in naloži — Zapri in naloži v… (Domov — Zapri&Naloži — Zapri&Naloži na…).
- Sestavite tabele z različnimi glavami iz več knjig
- Zbiranje podatkov iz vseh datotek v določeni mapi
- Zbiranje podatkov z vseh listov knjige v eno tabelo