Több munkalapon vannak az adataid és ebből szeretnél kimutatást készíteni? Azt már biztosan tudod, hogy hagyományos kimutatással ilyen formában nem lehetséges, csak akkor, ha egy táblázatba másolod az adatokat. Ez sok időt vesz igénybe, vagy ismerned kell hozzá a Power Query-t, vagy lehet, hogy nem is tudsz belenyúlni ilyen szinten a táblába, mert nem csak Te használod, esetleg a későbbiekben is jobb, ha külön szerepelnek a táblák.
Bármelyik is álljon fenn, megmutatom, hogyan tudsz mégis kimutatást készíteni úgy, hogy a külön táblák is megmaradjanak.
A feladatban használt fájlt innen letöltheted: Pivot tábla több munkalapról feladat
Pivot tábla több munkalapról – példa
Adott három munkalap, az egyiken a forgalom, a másikon a vevők adatai, a harmadikon a termékkategóriák szerepelnek.
Szeretnék készíteni egy kimutatást, amelyben termékkategóriánként és vevőnevenként látszik a bevétel. Hagyományos kimutatással ez nem megoldható. Nézzük tehát a Power Pivottal.
Ha még nem ismered a Power Pivot-ot, olvasd el a Mi az az Excel Power Pivot és az Excel Power Pivot telepítése bejegyzéseinket.
Pivot tábla több munkalapról – első lépés
Először is alakítsuk táblázattá (Excel táblázat – Részletes leírás példákkal) az adatokat. Nem gond, ha nem táblázat, mert akkor is be lehet olvasni, de ha előre átalakítjuk, adhatunk neki egyedi nevet, így könnyebben tájékozódunk majd a táblák között.
Kattints bele bárhova a Forgalom munkalapon lévő adatokba, majd Beszúrás -> Táblázat (Insert -> Table)
A megjelenő ablakot csak okézd le, ugyanis helyesen ismeri fel a tábla határait, fejléceit. Ha nem lenne bepipálva, hogy fejléceket tartalmazó tábla, jelöld be!
Felül megjelenik a Táblázateszközök menü, kattints rá, majd bal oldalon nevezd át a táblát, én Forgalom_tabla néven neveztem. Fontos, hogy szóközöket ne használj és Enterrel rögzítsd, miután beírtad a nevet.
Végezd el ugyanezt a másik két munkalapon lévő adatokra, legyenek a nevek: Vevok_tabla és Kategoria_tabla.
Táblák beolvasása Power Pivotba
Ha ez megvan, a táblákat egyesével be kell olvasnunk a Power Pivot adatmodellbe. Kattints bárhova a Forgalom táblába, majd a Power Pivot menüben kattints a Hozzáadás adatmodellhez gombra.
2010-es verzióban ugyanebben a menüben, de más néven szerepel: Csatolt táblázat létrehozása
Meg fogja nyitni a Power Pivot ablakot, ahol bal alul láthatod is, hogy létrehozta ezt a lapot (csakúgy, ahogy az Excelben is vannak munkalapjaink, itt külön lapokon fogod látni a különböző táblázatokat)
Végezd el ezt a másik két táblára is. Visszamenni úgy tudsz az Excelbe, hogy a bal felső sarokban a Válts a munkafüzetre (Excel) ikonra kattintasz.
Beolvasások után alul láthatod a három táblát
Pivot tábla több munkalapról – Kapcsolatok létrehozása
Ahhoz, hogy ezt a három táblát egy kimutatásban tudjuk használni, létre kell hoznunk a közöttük lévő kapcsolatokat. Kapcsolatok kiépítése a táblák között, ez teszi lehetővé, hogy elkerüld az FKERES-ezést és egy táblát kelljen készítened az adatokból.
Kapcsolatokat többféleképpen is létrehozhatunk, mutatom a vizuálisabbat. Először is válts át Diagramnézetre a Power Pivot ablakban, a Kezdőlapon jobbra találod.
Itt úgy adjuk meg a kapcsolatokat, hogy a Forgalom táblában fogd meg a Vevő ID mezőt és húzd a Vevők táblának Vevő ID mezőjére
Láthatod, hogy létre is hozta a kapcsolatot
A nyilak végén az 1 és *azt jelentik, hogy Vevő tábla egy-egy vevő ID értékéhez a Forgalom tábla több sora is tartozhat, de fordítva nem igaz, vagyis a Forgalom tábla egy-egy sorához, csak egy sor tartozik a Vevő tábla soraiból.
Hozd létre a kapcsolatot a másik táblával is a Termékkategóriára.
Ha ezek megvannak, létre is hozhatjuk a kimutatásunkat!
Pivot tábla több munkalapról – Kimutatás létrehozása
Kattints a Power Pivot ablak Kezdőlapján a Kimutatás gombra
Megkérdezi, hogy hova szeretnéd elhelyezni, legyen új munkalap:
Az új munkalapon jobb oldalon meg is jelennek a táblák, azokat lenyitva pedig a mezők
Amire szükségünk van, az a Forgalom táblából az Összeg az értékekhez, a Kategória táblából a Kategória neve a sorokhoz, és a Vevők táblából a Vevő neve a sorokhoz.
Így néz ki tehát a kimutatás és a kimutatásmezők elhelyezése:
Pivot tábla több munkalapról – Összefoglalás
A Power Pivottal tehát meg tudtuk oldani, hogy különböző forrásokból készítsünk egyetlen kimutatást anélkül, hogy össze kellene másolgatni őket egy táblába.
Ha tetszett, kérlek, oszd meg másokkal is!
