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.

pivot több munkalapról tábla
Pivot tobb munkalapról 2
Pivot több munkalapról 3

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)

Pivot tábla több munkalapról táblázat

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!

Táblázattá alakítás Power Pivot

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.

pivot több munkalapról táblázat

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.

Hozzáadás adamodellhez, pivot több munkalapról

2010-es verzióban ugyanebben a menüben, de más néven szerepel: Csatolt táblázat létrehozása

hozzáadás adatmodellhez - pivot több munkalapról

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)

Power Pivot tábla beolvasása 1

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.

Power Pivot ablak váltás

Beolvasások után alul láthatod a három táblát

Power pivot táblák beolvasva

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.

Power Pivot diagram nézet

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

Power PIvot kapcsolatok létrehozása

Láthatod, hogy létre is hozta a kapcsolatot

Power Pivot kapcsolatok

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.

Power Pivot kapcsolatok táblák között

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

Power Pivot kimutatás

Megkérdezi, hogy hova szeretnéd elhelyezni, legyen új munkalap:

Power Pivot új kimutatás

Az új munkalapon jobb oldalon meg is jelennek a táblák, azokat lenyitva pedig a mezők

Power Pivot kimutatásmező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 eredmény

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!