Táblák egyesítése az Excelben a Power Query használatával (egyszerű, lépésről lépésre szóló útmutató)

A Power Query segítségével a munkalapokon vagy akár munkafüzetekben szétszórt adatokkal való munka könnyebbé vált.

Az egyik dolog, ahol a Power Query sok időt takaríthat meg, amikor különböző méretű táblázatokat és oszlopokat kell egyesítenie egy megfelelő oszlop alapján.

Az alábbiakban egy videó látható, ahol pontosan megmutatom, hogyan lehet egyesíteni a táblázatokat Excelben a Power Query használatával.

Abban az esetben, ha inkább a szöveget szeretné elolvasni, mint a videó megtekintését, az alábbiakban olvasható az írásbeli utasítás.

Tegyük fel, hogy van egy táblázat az alábbiak szerint:

Ez a táblázat tartalmazza a használni kívánt adatokat, de még mindig hiányzik két fontos oszlop - a „Termékazonosító” és a „Régió”, ahol az értékesítési képviselő működik.

Ezeket az információkat külön táblázatokban adjuk meg, az alábbiak szerint:

Ahhoz, hogy mindezeket az információkat egyetlen táblázatba gyűjtse össze, össze kell egyesítenie ezt a három táblázatot, hogy aztán létrehozhasson egy kimutatástáblát, és elemezhesse, vagy használhassa más jelentési/irányítópult -célokra.

És az összevonás alatt nem egy egyszerű másolásra gondolok.

Térképeznie kell az 1. táblázat vonatkozó rekordjait a 2. és 3. táblázat adataival.

Most a VLOOKUP -ra vagy az INDEX/MATCH -ra támaszkodhat.

Vagy ha VBA -zseni vagy, írhatsz ehhez kódot.

De ezek a lehetőségek időigényesek és bonyolultak a Power Queryhez képest.

Ebben az oktatóanyagban megmutatom, hogyan lehet egyesíteni ezt a három Excel -táblázatot.

Ahhoz, hogy ez a technika működjön, összekötő oszlopokkal kell rendelkeznie. Például az 1. és a 2. táblázatban a közös oszlop a „Cikk”, az 1. és a 3. táblázatban pedig az „Értékesítési képviselő” oszlop. Ezenkívül vegye figyelembe, hogy ezekben az összekötő oszlopokban nem lehet ismétlés.

Megjegyzés: A Power Query bővítményként használható az Excel 2010-ben és 2013-ban, és az Excel 2016-tól kezdődően beépített szolgáltatás. Az Ön verziója alapján egyes képek másként néznek ki (az oktatóanyagban használt képfelvételek az Excel 2016 -ból származnak).

Táblák egyesítése a Power Query használatával

Ezeket a táblázatokat az alábbiak szerint neveztem el:

1. 1. táblázat - Értékesítési_adatok
2. 2. táblázat - Pdt_Id
3. 3. táblázat - Vidék

Nem kötelező átnevezni ezeket a táblázatokat, de jobb, ha olyan neveket adunk meg, amelyek leírják a táblázat lényegét.

Egyszerre csak két táblát egyesíthet a Power Queryben.

Tehát először az 1. táblázatot és a 2. táblázatot kell egyesítenünk, majd a következő táblázatban a 3. táblázatot kell beleolvasztanunk.

1. és 2. táblázat egyesítése

A táblák összevonásához először ezeket a táblákat kell kapcsolatokká alakítani a Power Query -ben. Ha megvannak a kapcsolatok, könnyen összevonhatja ezeket.

Az alábbi lépésekkel mentheti az Excel táblázatot kapcsolatként a Power Query -ben:

1. Válassza ki a Sales_Data táblázat bármely celláját.
2. Kattintson az Adatok fülre.
3. A Get & Transform csoportban kattintson a „Táblázatból/tartományból” elemre. Ez megnyitja a Lekérdezés szerkesztőt.
4. A Lekérdezésszerkesztőben kattintson a „Fájl” fülre.
5. Kattintson a „Bezárás és betöltés” ​​lehetőségre.
6. Az „Adatok importálása” párbeszédpanelen válassza a „Csak kapcsolat létrehozása” lehetőséget.
7. Kattintson az OK gombra.

A fenti lépések kapcsolatot hoznának létre a Sales_Data névvel (vagy az Excel táblához adott bármely névvel).

Ismételje meg a fenti lépéseket a 2. táblázathoz és a 3. táblázathoz.

Tehát ha elkészült, három kapcsolata lesz (Sales_Data, Pdt_Id és Region névvel).

Most nézzük meg, hogyan egyesíthetjük a Sales_Data és a Pdt_Id táblát.

1. Kattintson az Adatok fülre.
2. Az Adatok lekérése és átalakítása csoportban kattintson az Adatok lekérése lehetőségre.
3. A legördülő menüben kattintson a Lekérdezések kombinálása elemre.
4. Kattintson az Egyesítés gombra. Ezzel megnyílik az Egyesítés párbeszédpanel.
5. Az Egyesítés párbeszédpanelen válassza az „Értékesítési_adatok” lehetőséget az első legördülő menüből.
6. A második legördülő menüből válassza a „Pdt_Id” lehetőséget.
7. Az „Értékesítési_adatok” előnézetben kattintson a „Cikk” oszlopra. Ezzel kiválasztja a teljes oszlopot.
8. A „Pdt_Id” előnézetben kattintson a „Tétel” oszlopra. Ezzel kiválasztja a teljes oszlopot.
9. A „Join Kind” legördülő menüben válassza a „Left Outer (minden az elsőtől, egyezés a másodiktól”) lehetőséget.
10. Kattintson az OK gombra.

A fenti lépések megnyitnák a Lekérdezésszerkesztőt, és megjelenítenék a Sales_Data adatait egy további (a Pdt_Id) oszloppal.

Az Excel táblázatok egyesítése (1. és 2. táblázat)

Most a táblázatok összevonásának folyamata a Lekérdezés szerkesztőben történik a következő lépésekkel:

1. A további oszlopban (Pdt_Id) kattintson a fejléc kettős hegyű nyíljára.
2. A megnyíló beállítások mezőben törölje az összes oszlopnév jelölését, és csak az Elemet válassza. Ennek oka az, hogy a meglévő táblázatban már megtalálható a terméknév oszlop, és minden termékhez csak a termékazonosítót szeretnénk.
3. Törölje a jelölést az „Eredeti oszlopnév használata előtagként” jelölőnégyzetből.
4. Kattintson az OK gombra.

Ez megadja az eredményül kapott táblázatot, amely minden rekordot tartalmaz a Sales_Data táblából, és egy további oszlopot, amely szintén tartalmaz termékazonosítókat (a Pdt_Id táblázatból).

Ha most csak két táblázatot szeretne kombinálni, akkor betöltheti ezt az Excel -t.

De három táblázatot kell összevonnunk, így további tennivalók vannak.

Ezt a kapott táblázatot kapcsolatként kell mentenie (hogy felhasználhassuk azt a 3. táblával).

Az egyesített táblázat (Sales_Data és Pdt_Id tábla adataival) kapcsolatként történő mentéséhez tegye a következő lépéseket:

1. Kattintson a Fájl fülre
2. Kattintson a „Bezárás és betöltés” ​​lehetőségre.
3. Az „Adatok importálása” párbeszédpanelen válassza a „Csak kapcsolat létrehozása” lehetőséget.
4. Kattintson az OK gombra.

Ezzel az újonnan egyesített adatokat kapcsolatként menti. Ezt a kapcsolatot átnevezheti, ha akarja.

A 3. táblázat egyesítése az eredménytáblával

A harmadik táblázatnak az eredő táblával való egyesítésének folyamata (amit az 1. és a 2. táblázat egyesítésével kaptunk) pontosan ugyanaz.

Íme a táblázatok egyesítésének lépései:

1. Kattintson az Adatok fülre.
2. Az Adatok lekérése és átalakítása csoportban kattintson az „Adatok lekérése” gombra.
3. A legördülő menüben kattintson a "Lekérdezések kombinálása" elemre.
4. Kattintson az „Egyesítés” gombra. Ezzel megnyílik az Egyesítés párbeszédpanel.
5. Az Egyesítés párbeszédpanelen válassza az „Összevonás1” lehetőséget az első legördülő menüből.
6. A második legördülő menüből válassza a „Régió” lehetőséget.
7. Az „Egyesítés1” előnézetben kattintson az „Értékesítési képviselő” oszlopra. Ezzel kiválasztja a teljes oszlopot.
8. A régió előnézetében kattintson az „Értékesítési képviselő” oszlopra. Ezzel kiválasztja a teljes oszlopot.
9. A „Csatlakozás kedves” legördülő menüben válassza a Bal oldali lehetőséget (minden az elsőtől, egyezés a másodiktól).
10. Kattintson az OK gombra.

A fenti lépések megnyitnák a Lekérdezésszerkesztőt, és megjelenítenék az egyesítés1 adatait egy további oszloppal (régió).

Most a táblázatok összevonásának folyamata a Lekérdezés szerkesztőben történik a következő lépésekkel:

1. A további oszlopban (Régió) kattintson a fejléc kettős hegyű nyíljára.
2. A megnyíló beállítások mezőben törölje az összes oszlopnév jelölését, és csak a Régió lehetőséget válassza.
3. Törölje a jelet az „Eredeti oszlopnév használata előtagként” jelölőnégyzetből.
4. Kattintson az OK gombra.

A fenti lépésekkel egy táblázatot kaphat, amely mind a három táblát egyesítette (Sales_Data tábla egy oszloppal a Pdt_Id és egy régióhoz).

A táblázat Excelben történő betöltésének lépései:

1. Kattintson a Fájl fülre.
2. Kattintson a „Bezárás és betöltés” ​​gombra.
3. Az „Adatok importálása” párbeszédpanelen válassza a Táblázat és az Új munkalapok lehetőséget.
4. Kattintson az OK gombra.

Ezzel a kapott egyesített táblázatot kapja egy új munkalapon.

A Power Query egyik legjobb tulajdonsága, hogy egyszerűen frissítheti az alapvető adatok (1., 2. és 3. táblázat) változásait.

Tegyük fel például, hogy Laurát Ázsiába viszik át, és új adatokat kap a következő hónapra. Most már nem kell megismételnie a fenti lépéseket. Mindössze annyit kell tennie, hogy frissíti az asztalt, és mindent újra megtesz érted.

Néhány másodpercen belül megkapja az új egyesített táblázatot.

A következő Power Query oktatóanyagok is tetszhetnek:

• Több munkafüzet adatait egyesítheti Excelben (a Power Query használatával).
• Több munkalap adatait egyesítheti egyetlen Excel -munkalapba.
• Hogyan lehet kibontani az adatokat az Excelben a Power Query (más néven Get & Transform) használatával
• Fájlnevek listájának megszerzése a mappákból és almappákból (a Power Query használatával)