Több munkafüzet adatainak egyesítése Excelben (a Power Query használatával)

A Power Query nagy segítség lehet, ha több munkafüzetet szeretne egyetlen munkafüzetbe egyesíteni.

Tegyük fel például, hogy megvannak a különböző régiók (kelet, nyugat, észak és dél) értékesítési adatai. A különböző munkafüzetekből származó adatokat egyetlen munkalapba egyesítheti a Power Query használatával.

Ha ezek a munkafüzetek különböző helyeken/mappákban vannak, érdemes ezeket egyetlen mappába helyezni (vagy másolatot létrehozni, és a munkafüzet másolatát ugyanabba a mappába helyezni).

Kezdetnek tehát négy munkafüzetem van egy mappában (az alábbiakban látható módon).

Most ebben az oktatóanyagban három forgatókönyvet fedek le, amelyekben a Power Query használatával egyesítheti a különböző munkafüzetek adatait:

• Minden munkafüzet Excel -táblázatban tartalmazza az adatokat, és minden tábla neve azonos.
• Minden munkafüzet azonos munkalapnévvel rendelkezik. Ez akkor fordulhat elő, ha az összes munkafüzetben található „összefoglaló” vagy „adat” nevű lap, és ezeket mind kombinálni szeretné.
• Minden munkafüzet sok lapot és táblázatot tartalmaz, és egyes táblázatokat/lapokat szeretne kombinálni. Ez a módszer akkor is hasznos lehet, ha olyan táblázatokat/lapokat szeretne kombinálni, amelyeknek nincs egységes neve.

Nézzük meg, hogyan egyesíthetjük az egyes munkafüzetek adatait minden esetben.

Minden munkafüzet ugyanazon felépítésű Excel -táblázatban tartalmazza az adatokat

Az alábbi technika akkor működik, ha az Excel táblákat ugyanúgy felépítették (azonos oszlopnevek).

Az egyes táblázatok sorainak száma változhat.

Ne aggódjon, ha az Excel táblázatok némelyike ​​további oszlopokkal rendelkezik. Kiválaszthatja a táblázatok egyikét sablonként (vagy a „kulcsként”, ahogy a Power Query nevezi), és a Power Query ezt használja az összes többi Excel -táblázat kombinálására.

Abban az esetben, ha további oszlopok vannak más táblázatokban, azokat figyelmen kívül hagyja, és csak a sablonban/kulcsban megadott oszlopokat kombinálja. Például, ha a kiválasztott sablon/kulcs tábla 5 oszlopot tartalmaz, és egy másik munkafüzet egyik táblája 2 további oszlopot tartalmaz, akkor ezeket a további oszlopokat figyelmen kívül hagyja.

Most négy munkafüzetem van egy mappában, amelyeket egyesíteni szeretnék.

Az alábbiakban pillanatkép látható az egyik munkafüzetben található táblázatról.

Íme a lépések az egyes munkafüzetek adatainak egyetlen munkafüzetbe történő egyesítéséhez (egyetlen táblázatként).

1. Lépjen az Adatok fülre.
2. A Get & Transform csoportban kattintson az Új lekérdezés legördülő menüre.
3. Vigye a kurzort a „Fájlból” elemre, és kattintson a „Mappából” elemre.
4. A Mappa párbeszédpanelen adja meg a fájlokat tartalmazó mappa fájl elérési útját, vagy kattintson a Tallózás gombra, és keresse meg a mappát.
5. Kattintson az OK gombra.
6. A megnyíló párbeszédpanelen kattintson a kombináció gombra.
7. Kattintson a „Kombinálás és betöltés” ​​gombra.
8. A megnyíló „Fájlok egyesítése” párbeszédpanelen válassza ki a táblázatot a bal oldali ablaktáblában. Ne feledje, hogy a Power Query az első fájl táblázatát mutatja. Ez a fájl sablonként (vagy kulcsként) szolgál más fájlok egyesítéséhez. A Power Query most megkeresi az „1. ​​táblázatot” más munkafüzetekben, és összekapcsolja ezzel.
9. Kattintson az OK gombra.

Ez betölti a végeredményt (kombinált adatokat) az aktív munkalapra.

Ne feledje, hogy az adatokkal együtt a Power Query automatikusan hozzáadja a munkafüzet nevét a kombinált adatok első oszlopához. Ez segít nyomon követni, hogy melyik munkafüzetből milyen adatok származtak.

Ha először szeretné szerkeszteni az adatokat, mielőtt betölti őket az Excelbe, a 6. lépésben válassza a „Kombinálás és szerkesztés” lehetőséget. Ez megnyitja a végeredményt a Power Query szerkesztőben, ahol szerkesztheti az adatokat.

Néhány tudnivaló:

• Ha Excel táblázatot választ sablonként (a 7. lépésben), a Power Query a táblázatban található oszlopneveket használja a többi táblázat adatainak egyesítésére. Ha más táblázatok további oszlopokkal rendelkeznek, azokat figyelmen kívül hagyja. Abban az esetben, ha a többi táblázatnak nincs oszlopa, amely ott van a sablon táblázatában, a Power Query csak a „null” értéket írja be.
• Az oszlopoknak nem kell ugyanabban a sorrendben lenniük, mint a Power Query az oszlopfejléceket használja az oszlopok feltérképezéséhez.
• Mivel az 1. táblázatot választotta kulcsként, a Power Query megkeresi az 1. táblázatot az összes munkafüzetben, és mindezt egyesíti. Ha nem talál azonos nevű Excel -táblázatot (ebben a példában az 1. táblázatot), a Power Query hibaüzenetet ad.

Új fájlok hozzáadása a mappához

Most szánjunk egy percet, és értsük meg, mit tettünk a fenti lépésekkel (ami csak néhány másodpercet vett igénybe).

Négy különböző munkafüzet adatait egyesítettük egyetlen táblázatban néhány másodperc alatt anélkül, hogy kinyitnánk a munkafüzeteket.

De ez még nem minden.

A Power Query igazi POWER -je az, hogy most, amikor további fájlokat ad hozzá a mappához, nem kell megismételnie ezeket a lépéseket.

Mindössze annyit kell tennie, hogy áthelyezi az új munkafüzetet a mappába, frissíti a lekérdezést, és automatikusan egyesíti az adott mappa összes munkafüzetének adatait.

Például a fenti példában, ha új munkafüzetet adok hozzá - 'Mid-West.xlsx' a mappába, és frissítse a lekérdezést, azonnal megadja az új kombinált adatkészletet.

Így frissítheti a lekérdezést:

• Kattintson a jobb gombbal a munkalapra betöltött Excel táblára, majd kattintson a Frissítés gombra.
• Kattintson a jobb gombbal a Lekérdezés elemre a „Munkafüzet lekérdezés” panelen, majd kattintson a Frissítés gombra
• Lépjen az Adatok fülre, és kattintson a Frissítés gombra.

Minden munkafüzet azonos munkalapnévvel rendelkezik

Abban az esetben, ha az Excel táblázatban nincsenek adatok, de az összes munkalap neve (amelyből össze szeretné vonni az adatokat) ugyanaz, akkor használhatja az ebben a szakaszban bemutatott módszert.

Van néhány dolog, amivel óvatosnak kell lennie, ha csak táblázatos adatokról van szó, nem pedig Excel -táblázatról.

• A munkalapok neveinek azonosnak kell lenniük. Ez segíti a Power Query -t a munkafüzetek áttekintésében és az egyes munkafüzetekben azonos nevű munkalapok adatainak egyesítésében.
• A Power Query megkülönbözteti a kis- és nagybetűket. Ez azt jelenti, hogy az „adatok” és az „Adatok” elnevezésű munkalapok különböznek. Hasonlóképpen a „Store” és a „store” fejléccel rendelkező oszlopok eltérőek.
• Bár fontos, hogy azonos oszlopfejlécek legyenek, nem fontos, hogy azonos sorrendben legyenek. Ha az „East.xlsx” 2. oszlopa a „West.xlsx” 4. oszlopa, a Power Query a fejlécek leképezésével helyesen illeszkedik hozzá.

Most nézzük meg, hogyan lehet gyorsan kombinálni a különböző munkafüzetek adatait, ahol a munkalap neve azonos.

Ebben a példában van egy mappám négy fájllal.

Minden munkafüzetben van egy adatlap nevű munkalapom, amely a következő formátumban tartalmazza az adatokat (vegye figyelembe, hogy ez nem Excel táblázat).

Az alábbi lépésekkel egyesítheti több munkafüzet adatait egyetlen munkalapba:

1. Lépjen az Adatok fülre.
2. A Get & Transform csoportban kattintson az Új lekérdezés legördülő menüre.
3. Vigye a kurzort a „Fájlból” elemre, és kattintson a „Mappából” elemre.
4. A Mappa párbeszédpanelen adja meg a fájlokat tartalmazó mappa fájl elérési útját, vagy kattintson a Tallózás gombra, és keresse meg a mappát.
5. Kattintson az OK gombra.
6. A megnyíló párbeszédpanelen kattintson a kombináció gombra.
7. Kattintson a „Kombinálás és betöltés” ​​gombra.
8. A megnyíló "Fájlok egyesítése" párbeszédpanelen válassza ki az "Adatok" lehetőséget a bal oldali ablaktáblában. Ne feledje, hogy a Power Query megjeleníti az első fájl munkalapjának nevét. Ez a fájl kulcsként/sablonként fog működni más fájlok egyesítéséhez. A Power Query átnézi az egyes munkafüzeteket, megkeresi az „Adatok” nevű lapot, és mindezt egyesíti.
9. Kattintson az OK gombra. Most a Power Query végigmegy minden munkafüzeten, keresse meg az „Adatok” nevű munkalapot, majd egyesítse ezeket az adatkészleteket.

Ez betölti a végeredményt (kombinált adatokat) az aktív munkalapra.

Ha először szeretné szerkeszteni az adatokat, mielőtt betölti őket az Excelbe, a 6. lépésben válassza a „Kombinálás és szerkesztés” lehetőséget. Ez megnyitja a végeredményt a Power Query szerkesztőben, ahol szerkesztheti az adatokat.

Minden munkafüzet különböző táblázat- vagy munkalapnevekkel rendelkezik

Előfordulhat, hogy nem kap strukturált és következetes adatokat (például az azonos nevű táblázatokat vagy az azonos nevű munkalapokat).

Tegyük fel például, hogy olyan személytől szerezte az adatokat, aki létrehozta ezeket az adatkészleteket, de a munkalapokat keleti adatnak, nyugati adatnak, északi adatnak és déli adatnak nevezte el.

Vagy a személy létrehozhat Excel táblákat, de különböző nevekkel.

Ilyen esetekben továbbra is használhatja a Power Query -t, de meg kell tennie néhány további lépéssel.

1. Lépjen az Adatok fülre.
2. A Get & Transform csoportban kattintson az Új lekérdezés legördülő menüre.
3. Vigye a kurzort a „Fájlból” elemre, és kattintson a „Mappából” elemre.
4. A Mappa párbeszédpanelen adja meg a fájlokat tartalmazó mappa fájl elérési útját, vagy kattintson a Tallózás gombra, és keresse meg a mappát.
5. Kattintson az OK gombra.
6. A megnyíló párbeszédpanelen kattintson a Szerkesztés gombra. Ez megnyitja a Power Query szerkesztőt, ahol látni fogja a mappa összes fájljának részleteit.
7. Tartsa lenyomva a Control billentyűt, és válassza ki a „Tartalom” és a „Név” oszlopokat, kattintson a jobb egérgombbal, és válassza az „Egyéb oszlopok eltávolítása” lehetőséget. Ez eltávolítja az összes többi oszlopot, kivéve a kijelölt oszlopokat.
8. A Lekérdezésszerkesztő szalagon kattintson az „Oszlop hozzáadása”, majd az „Egyéni oszlop” elemre.
9. Az Egyéni oszlop hozzáadása párbeszédpanelen nevezze el az új oszlopot „Adatok importálása” néven, és használja a következő képletet = Excel.Munkafüzet ([TARTALOM]). Ne feledje, hogy ez a képlet megkülönbözteti a kis- és nagybetűket, és pontosan úgy kell megadnia, ahogy itt bemutatom.
10. Most egy új oszlopot fog látni, amelyben táblázat szerepel. Most elmagyarázom, mi történt itt. Ön megadta a Power Query -nek a munkafüzetek nevét, a Power Query pedig lekérte az objektumokat, például a munkalapokat, táblázatokat és a megnevezett tartományokat az egyes munkafüzetekből (amely jelenleg a Táblázat cellában található). Kattintson a Táblázat szöveg melletti fehér mezőre, és láthatja az információkat alul. Ebben az esetben, mivel minden munkafüzetben csak egy táblázat és egy munkalap található, csak két sort láthat.
11. Kattintson az „Adatok importálása” oszlop tetején található dupla nyíl ikonra.
12. A megnyíló oszlopadatok mezőben törölje az „Eredeti oszlop használata előtagként” jelölőnégyzet jelölését, majd kattintson az OK gombra.
13. Most egy kibontott táblázatot fog látni, ahol egy sor látható a táblázat minden objektumához. Ebben az esetben minden munkafüzet esetében a lapobjektum és a táblaobjektum külön szerepel.
14. A Kind oszlopban szűrje a listát, hogy csak a táblázat jelenjen meg.
15. Tartsa lenyomva a vezérlőgombot, és válassza ki a Név és adatok oszlopot. Most kattintson a jobb gombbal, és távolítsa el az összes többi oszlopot.
16. Az Adatok oszlopban kattintson az adatfejléc jobb felső sarkában található dupla nyíl ikonra.
17. A megnyíló oszlopadatok mezőben kattintson az OK gombra. Ez egyesíti az összes táblázat adatait, és megjeleníti a Power Query -ben.
18. Most elvégezheti a szükséges átalakításokat, majd lépjen a Kezdőlap fülre, és kattintson a Bezárás és betöltés gombra.

Most hadd próbáljam meg gyorsan elmagyarázni, mit tettünk itt. Mivel a lapnevekben vagy a táblázatnevekben nem volt következetesség, az = Excel.Workbook képletet használtuk a Power Query munkafüzetek összes objektumának lekéréséhez. Ezek az objektumok lapokat, táblázatokat és megnevezett tartományokat tartalmazhatnak. Miután megkaptuk az összes objektumot az összes fájlból, ezeket leszűrtük, hogy csak az Excel táblákat vegyük figyelembe. Ezután kibővítettük a táblázatokban szereplő adatokat, és ezeket összevontuk.

Ebben a példában úgy szűrtük le az adatokat, hogy csak az Excel táblákat használjuk (a 13. lépésben). Abban az esetben, ha lapokat szeretne kombinálni, nem táblázatokat, szűrheti a lapokat.

Megjegyzés - ez a technika akkor is megadja a kombinált adatokat, ha az oszlopnevek nem egyeznek. Például, ha az East.xlsx fájlban van egy hibásan írt oszlopa, akkor végül 5 oszlopot kap. A Power Query kitölti az oszlopokban lévő adatokat, ha megtalálja, és ha nem talál oszlopot, akkor az értéket „null” értékként jeleníti meg.

Hasonlóképpen, ha van néhány további oszlopa a táblázatok bármelyik munkalapján, akkor ezek szerepelnek a végeredményben.

Ha most több munkafüzetet kap, amelyekből egyesítenie kell az adatokat, egyszerűen másolja be a mappába, és frissítse a Power Query-t