Kimutatás több munkalapról? Lehetséges, attól függ az eszköz, hogy hogyan állnak rendelkezésre az adataid!
- 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.
- 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.
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:
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á.
Í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:
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!
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!
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:
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!
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.
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.
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!
A felugró ablakban add meg az „_” karaktert!
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.
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.
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.
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
