Kimutatás több munkalapról? Lehetséges, attól függ az eszköz, hogy hogyan állnak rendelkezésre az adataid!

  1. Van olyan eset, amikor több munkalapon tárolt adatokból szeretnél egy közös kimutatást készíteni. Például egy kereskedő cég, külön munkalapokon tárolja a havi forgalmát. Aztán negyedévente egy-egy kimutatást szeretne készíteni a havi forgalomról. Zárójelben jegyzem meg, hogy kerüld az adatok szétdarabolását! Amennyiben lehetséges, akkor egy táblába gyűjtsd az adatokat! Ha mégis ilyen szétdarabolt adatok állnak rendelkezése, akkor a Power Query tud segíteni. A PowerQuery-vel lényegében összefűzzük ezeket a különálló munkalapokat egy táblázattá, amelyből már készíthető kimutatás. Ráadásul dinamikus: ha újabb hónapok érkeznek a fájlba, egy kattintással frissítheted és bekerülnek az új hónapok is a kimutatás forrásába.
  2. Ha neked nem szükséges a táblák összefűzése, de szükséges közöttük kapcsolatot teremteni, pl: számlák, vevők, szállítók, üzletek stb., akkor a Power Pivot segít neked egy kimutatásban összegezni a különálló táblákat. Erről olvashatsz a Pivot tábla több munkalapról – Excel Power Pivot bejegyzésünk.
Excel kimutatás több munkalapról – Power Query-vel

Kimutatás több munkalapról – Az adatok előkészítése

A feladatot innen töltheted le: Kimutatás több munkalapról

Az adatok egy Excel fájl munkalapjain vannak tárolva. A munkalapokon egy kereskedő cég havi forgalmát láthatod:

Pivot adatok

Az összes olyan munkalapon lévő adatot át kell alakítani táblázattá, amit fel szeretnénk dolgozni a Power Query-vel. Ez azért hasznos, mert így előre elnevezheted a táblákat. Táblázattá alakítás nélkül a Power Query fogja táblázattá alakítani, de automatikus elnevezéssel (Táblázat1, Táblázat2 stb.) A táblázatokról részletesen az Excel táblázat – Részletes leírás példákkal című bejegyzésünkben olvashatsz.

A példában a hónapnak megfelelően hoztam létre a táblázatokat, és FONTOS, hogy az elnevezésekben legyen valami egységes. A hónap nevei után a következő „_ho” kiegészítést írtam. Ennek az elnevezésnek jelentősége van, később visszatérek rá.

Excel táblázat

Így létrehoztam a „Januar_ho”, „Februar_ho”, „Marcius_ho” és „Aprilis_ho” nevű táblázatokat, mindegyiket a saját munkalapján. A névkezelőben le tudod ellenőrizni:

Excel névkezelő

Kimutatás több munkalapról – Power Query indítása

A táblázatok beolvasásához először hozz létre egy üres lekérdezést!

A menüszalagon kattints az „Adatok” fülre, ott nyisd le az „Adatok beolvasása” lenyíló gombot, mutass rá a „Más forrásból” és válaszd ki az „Üres lekérdezés” gombot!

PowerQuery- üres lekérdezés

Megjelenik a Power Query üres lekérdezése. A szerkesztőlécben, akár az Excelben, írd be azt, hogy = Excel.CurrentWorkbook() és nyomj egy entert. Figyelj a kis és nagybetűkre, mert számítanak!

Excel current workbook

Szűrés Power Query-ben

Az aktuális Excel fájlból ki fogja listázni a már létrehozott táblázatokat a Power Query.

A lekérdezés beállításai

1. lépés: Szűrés

A „Name” oszlopban szerepelnek a táblázataid nevei. Amint írtam korábban, lényeges, hogy valami egységes karakter/karakter sorozat legyen a nevekben, mert az első művelet az lesz, hogy szűrni kell a „Name” oszlopot. Ebben az esetben a szöveg végére fogsz szűrni:

Power Query tábla szűrése
Power Query szűrés

Adatok kibontása

Ezzel biztosítod azt, hogy a későbbiekben létrehozott összessítő táblázatokat ne olvassa be újra, megnyitáskor vagy frissítéskor a Power Query.

A „Name” oszlopot elnevezheted „Hónapok”-nak. Ha duplán kattintasz a „Name” fejlécre, akkor ott át lehet írni, illetve, jobb klikk a fejlécen és az „Átnevezés”-t választva szintén át lehet írni a nevét.

2. lépés: Kibontás

A két felé irányuló nyílra kattintva, az előugró ablakon ki tudod választani, hogy mely adatokat szeretnéd megjeleníteni a kibontott táblázatban. Ebben a példában mindent jelöld be!

Power Query

Az „OK” gombra kattintás után megjelennek az adatok. Látható, hogy a Power Query sok adatot felismert (pl. a dátumot), de nem megfelelően jelenített meg.

kimutatás több munkalapról

Adattípusok

3. lépés: Adattípus

Ebben a lépésben át tudod állítani az adattípusokat. A példában, a kedvezményt százalékosra, a vásárlás dátumát dátum adattípusra alakítsd át. Az adattípusukat a fejléc bal szélén található pl. ABC/123 ikonra kattintva lehet elérni.

Power Query-adattípus
Power Query-adattípus

A hónapok oszlop esetében az „_” karaktertől levághatod a szöveget, már nincs rá szükség, csak az 1. lépésben leírt szűrés miatt kellett.

Jelöld ki az oszlopot, aztán az „átalakítás” menü alatt, nyisd le a „Kinyerés” gombot és válaszd ki a „Határolójel előtti szöveg” parancsot!

Power Qery szöveg levágása

A felugró ablakban add meg az „_” karaktert!

Power Query adatok tisztítása

Az „OK” gombra kattintás után már jól jelennek meg a hónapok nevei.

Lekérdezés betöltése

4. lépés: Power Query bezárása és a lekérdezés betöltése

Ha elvégeztél minden módosítást, amit szerettél volna, akkor vissza kell lépni az Excel programba. A „Kezdőlap” menü bal szélső gombjára kattintva, el tudod menteni ezt a lekérdezést, és be tudod tölteni egy adott helyre az Excel-be.

Power Query adatok betöltése

Az Excel felületén a felugró ablakban be tudod állítani, hogy milyen adatot, és hol szeretnéd látni. A példában „Táblázat”-ot és „Új munkalap”-ot választottam. Ha nem akarod az adatokat betölteni egy táblában, akkor válaszd a kimutatást, ekkor rögtön a kimutatás elkészítése következik.

Power Query betöltés

A példában a „Munka1” nevű munkalapra betöltötte a Power Query a lekérdezést, és ebből a táblázatból már tudunk különböző kimutatásokat létrehozni. Ehhez nyújthat segítséget a Pivot tábla készítése 3 perc alatt című bejegyzésünk.

Power Query táblázat

A Power Query nem változtatja meg az eredeti adatokat, csak lekérdezi azokat, tehát, ha bármi változás történik bármelyik alap táblázatban, akkor egy frissítés, vagy megnyitás után a „Munka1” munkalapon lévő táblázat is frissülni fog.

Power Pivot

Ha a táblázatokat nem összefűzni szeretnéd, hanem egymás mellé kellene másolni a kimutatáshoz, ahhoz a Power Pivot tud segíteni neked. Az összemásolás helyett a kapcsolatok kialakításával tudod létrehozni a kimutatást több munkalapról. Ehhez olvasd el a Pivot tábla több munkalapról – Excel Power Pivot bejegyzésünket!

Bízom benne, hogy sikerült megcsinálnod ezt a példát, és kedvet kaptál a Power Query lekérdezéseihez! Ha tetszett, kérlek oszd meg másokkal is.

Pivot tippek

Beállítási lehetőségeket, feladatokat találsz az alábbi oldalunkon is:

Pivot feladatok az Excellence.hu oldalunkon