tartalom
A probléma megfogalmazása
Bemeneti adatként egy Excel fájl áll rendelkezésünkre, ahol az egyik lapon több táblázat található a következő formájú értékesítési adatokkal:
Vegye figyelembe, hogy:
- Különböző méretű és különböző termék- és régiókészletekkel ellátott táblázatok sorokban és oszlopokban, rendezés nélkül.
- A táblázatok közé üres sorok is beszúrhatók.
- Az asztalok száma tetszőleges lehet.
Két fontos feltételezés. Feltételezhető, hogy:
- Minden táblázat felett, az első oszlopban annak a menedzsernek a neve, akinek eladásait a táblázat szemlélteti (Ivanov, Petrov, Sidorov stb.)
- Az áruk és régiók nevei minden táblában azonos módon – kisbetűs pontossággal – vannak felírva.
A végső cél az, hogy az összes tábla adatait egyetlen lapos normalizált táblázatba gyűjtsük, amely kényelmes a későbbi elemzéshez és az összefoglaló elkészítéséhez, azaz ebben:
1. lépés: Csatlakozzon a fájlhoz
Hozzon létre egy új üres Excel fájlt, és válassza ki a lapon dátum parancs Adatok beszerzése – Fájlból – Könyvből (Adatok — Fájlból — Munkafüzetből). Adja meg az értékesítési adatokat tartalmazó forrásfájl helyét, majd a navigátor ablakban válassza ki a szükséges lapot, és kattintson a gombra Adatok konvertálása (Adatok átalakítása):
Ennek eredményeként az összes adatot be kell tölteni a Power Query szerkesztőbe:
2. lépés: Takarítsa el a szemetet
Az automatikusan generált lépések törlése módosított típus (Módosított típus) и Emelt fejlécek (Kiemelt fejlécek) és egy szűrő segítségével megszabaduljon az üres soroktól és az összegző soroktól null и ÖSSZESEN az első oszlop által. Ennek eredményeként a következő képet kapjuk:
3. lépés: Vezetők hozzáadása
Annak érdekében, hogy később megértsük, hol vannak az eladások, hozzá kell adni egy oszlopot a táblázatunkhoz, ahol minden sorban lesz egy megfelelő vezetéknév. Ezért:
1. Adjunk hozzá egy segédoszlopot sorszámokkal a paranccsal Oszlop hozzáadása – Index oszlop – 0-tól (Oszlop hozzáadása — Index oszlop — 0-tól).
2. Adjon hozzá egy oszlopot képletekkel a paranccsal Oszlop hozzáadása – Egyéni oszlop (Oszlop hozzáadása – Egyéni oszlop) és vezesse be ott a következő konstrukciót:
A képlet logikája egyszerű – ha az első oszlopban a következő cella értéke „Termék”, akkor ez azt jelenti, hogy egy új tábla elejére botlottunk, ezért az előző cella értékét jelenítjük meg a a menedzser neve. Ellenkező esetben nem jelenítünk meg semmit, azaz nullát.
A vezetéknévvel ellátott szülőcella lekéréséhez először az előző lépés táblázatát kell figyelembe venni #"Index hozzáadva", majd adja meg a szükséges oszlop nevét [1. oszlop] szögletes zárójelben, az oszlopban lévő cellaszám pedig göndör zárójelben. A cellaszám eggyel kisebb lesz, mint a jelenlegi, amit az oszlopból veszünk index, Ill.
3. Marad az üres cellák kitöltése null neveket magasabb cellákból a paranccsal Átalakítás – Kitöltés – Le (Átalakítás - Kitöltés - Le) és törölje a már nem szükséges oszlopot indexekkel és vezetéknevekkel ellátott sorokat az első oszlopban. Ennek eredményeként a következőket kapjuk:
4. lépés: Csoportosítás külön táblákba vezetők szerint
A következő lépés az egyes kezelők sorainak külön táblázatokba történő csoportosítása. Ehhez az Átalakítás lapon használja a Csoportosítás szerint parancsot (Transform – Group By), és a megnyíló ablakban válassza ki a Kezelő oszlopot és az Összes sor (Minden sor) műveletet, hogy egyszerűen gyűjtsön adatokat anélkül, hogy összesítő funkciót alkalmazna. (összeg, átlag stb.). P.):
Ennek eredményeként minden menedzserhez külön táblázatot kapunk:
5. lépés: A beágyazott táblák átalakítása
Most megadjuk azokat a táblázatokat, amelyek az eredményül kapott oszlop minden cellájában találhatók Minden adat tisztességes formában.
Először töröljön egy olyan oszlopot, amelyre már nincs szükség az egyes táblákban menedzser. Ismét használjuk Egyéni oszlop lap Átalakítás (Átalakítás – Egyéni oszlop) és a következő képlet:
Ezután egy másik számított oszloppal az egyes táblázatok első sorát a címsorokhoz emeljük:
Végül végrehajtjuk a fő átalakítást – az egyes táblázatok kibontását az M-függvény segítségével Table.UnpivotOtherColumns:
A fejlécből a régiók nevei egy új oszlopba kerülnek, és egy szűkebb, de egyben hosszabb normalizált táblát kapunk. Ürítse ki a cellákat ezzel null figyelmen kívül hagyják.
Megszabadulunk a felesleges köztes oszlopoktól, a következőket kínáljuk:
6. lépés Bontsa ki a Beágyazott táblázatokat
Továbbra is ki kell bontani az összes normalizált beágyazott táblát egyetlen listává az oszlopfejlécben található dupla nyíl gombbal:
… és végre megkaptuk, amit akartunk:
A kapott táblát a paranccsal exportálhatja vissza Excelbe Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…).
- Hozzon létre táblázatokat különböző fejlécekkel több könyvből
- Adatgyűjtés egy adott mappában lévő összes fájlból
- Adatgyűjtés a könyv összes lapjáról egy táblázatba