Nemrég kaptam egy olvasótól egy kérdést, hogy ugyanazon munkafüzet több munkalapját egyetlen munkalapba egyesíti.
Megkértem, hogy használja a Power Query -t különböző lapok kombinálására, de aztán rájöttem, hogy a Power Query -ben újonnan dolgozók számára ez nehéz lehet.
Ezért úgy döntöttem, hogy megírom ezt az oktatóanyagot, és megmutatom a pontos lépéseket, hogy több lapot egyetlen táblába egyesítsenek a Power Query használatával.
Az alábbi videó alatt bemutatom, hogyan kombinálhatom több lap/táblázat adatait a Power Query használatával:
Az alábbiakban írásos utasítások találhatók több lap kombinálására (ha inkább az írott szöveget részesíti előnyben a videó helyett).
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öbb munkalap adatainak egyesítése a Power Query használatával
Amikor különböző lapok adatait kombinálja a Power Query használatával, az adatoknak Excel táblázatban (vagy legalábbis megnevezett tartományokban) kell szerepelniük. Ha az adatok nincsenek Excel táblázatban, az itt bemutatott módszer nem működik.
Tegyük fel, hogy négy különböző lapja van - kelet, nyugat, észak és dél.
Ezen munkalapok mindegyike Excel -táblázatban tartalmazza az adatokat, és a táblázat szerkezete konzisztens (azaz a fejlécek azonosak).
Kattintson ide az adatok letöltéséhez és kövesse nyomon.
Ezeket a fajta adatokat rendkívül könnyű kombinálni a Power Query használatával (ami igazán jól működik az Excel táblázat adataival).
Ahhoz, hogy ez a technika a legjobban működjön, jobb, ha az Excel -tábláknak nevei vannak (anélkül is működhet, de könnyebb használni, ha a táblákat elnevezik).
A táblázatoknak a következő neveket adtam: East_Data, West_Data, North_Data és South_Data.
Az alábbi lépésekkel kombinálhat több munkalapot az Excel táblákkal a Power Query használatával:
- Lépjen az Adatok fülre.
- Az Adatok lekérése és átalakítása csoportban kattintson az „Adatok lekérése” lehetőségre.
- Válassza a „Más forrásokból” lehetőséget.
- Kattintson az „Üres lekérdezés” lehetőségre. Ez megnyitja a Power Query szerkesztőt.
- A Lekérdezés szerkesztőben írja be a következő képletet a képletsávba: = Excel.CurrentWorkbook(). Ne feledje, hogy a Power Query képletei megkülönböztetik a kis- és nagybetűket, ezért a pontos képletet kell használnia az említett módon (különben hibaüzenetet kap).
- Nyomja meg az Enter billentyűt. Ez megmutatja a teljes munkafüzet összes táblanevét (ezenkívül a megnevezett tartományokat és/vagy kapcsolatokat is megjeleníti, ha létezik ilyen a munkafüzetben).
- [Választható lépés] Ebben a példában az összes táblázatot szeretném egyesíteni. Ha csak egyes Excel táblázatokat szeretne kombinálni, kattintson a névfejléc legördülő ikonjára, és válassza ki a kombinálni kívánt táblázatokat. Hasonlóképpen, ha tartományokat vagy kapcsolatokat nevezett meg, és csak táblázatokat szeretne kombinálni, akkor eltávolíthatja a megnevezett tartományokat is.
- A Tartalom fejléc cellában kattintson a kettős hegyű nyílra.
- Válassza ki az egyesíteni kívánt oszlopokat. Ha az összes oszlopot kombinálni szeretné, győződjön meg arról, hogy a (Minden oszlop kijelölése) jelölőnégyzet be van jelölve.
- Törölje a jelölést az „Eredeti oszlopnév használata előtagként” jelölőnégyzetből.
- Kattintson az OK gombra.
A fenti lépések az összes munkalap adatait egyetlen táblába egyesítenék.
Ha alaposan megnézi, az utolsó oszlopban (a jobb szélső oldalon) található az Excel táblák neve (East_Data, West_Data, North_Data és South_Data). Ez egy azonosító, amely megmondja, hogy melyik rekord melyik Excel táblából származik. Ez az oka annak is, hogy azt mondtam, hogy jobb, ha leíró nevek vannak az Excel táblákhoz.
Az alábbiakban néhány módosítást végezhet a Power Query kombinált adatain:
- Húzza és helyezze a Név oszlopot az elejére.
- Távolítsa el az „_adatokat” a név oszlopból (így marad a keleti, nyugati, északi és déli névnév). Ehhez kattintson a jobb gombbal a Név fejlécre, és kattintson az Értékek cseréje elemre. Az Értékek cseréje párbeszédpanelen cserélje le az _adatokat üresre.
- Módosítsa az Adatok oszlopot, hogy csak a dátumokat jelenítse meg (és ne az időt). Ehhez kattintson a Dátum oszlop fejlécére, lépjen az „Átalakítás” fülre, és módosítsa az adattípust Dátumra.
- Nevezze át a lekérdezést ConsolidatedData névre.
Most, hogy megvannak a Power Query összes munkalapjának összesített adatai, betöltheti azokat az Excelbe - új táblázatként új munkalapon.
Ezt csináld meg. kövesse az alábbi lépéseket:
- Kattintson a "Fájl" fülre.
- Kattintson a Bezárás és betöltés gombra.
- Az Adatok importálása párbeszédpanelen válassza a Táblázat és az Új munkalap beállításait.
- Kattintson az OK gombra.
A fenti lépések egyesítenék az összes munkalap adatait, és ezeket az adatokat egy új munkalapon adnák meg.
Egy probléma, amelyet meg kell oldania a módszer használata során
Ha a fenti módszert használta a munkafüzet összes táblázatának egyesítésére, akkor valószínűleg problémával szembesül.
Tekintse meg az egyesített adatok sorainak számát - 1304 (ez helyes).
Most, ha frissítem a lekérdezést, a sorok száma 2607 -re változik. Frissítés újra, és 3910 -re változik.
Itt a probléma.
A lekérdezés minden frissítésekor hozzáadja az eredeti adatok összes rekordját a kombinált adatokhoz.
Megjegyzés: Ezzel a problémával csak akkor szembesül, ha a Power Query -t használta az egyesítéshez MINDEN EXCEL TÁBLÁZAT a munkafüzetben. Abban az esetben, ha bizonyos táblázatokat választott össze, akkor nem szembesül ezzel a problémával.Megértjük ennek a problémának az okát, és hogyan javítsuk ki.
Amikor frissít egy lekérdezést, az visszamegy, és követi az összes lépést, amelyet az adatok egyesítésére tettünk.
Abban a lépésben, amikor a képletet használtuk = Excel.CurrentWorkbook (), megadta nekünk az összes táblázat listáját. Ez először jól működött, mivel csak négy asztal volt.
Frissítéskor azonban öt táblázat található a munkafüzetben - beleértve a Power Query által beillesztett új táblázatot, ahol a kombinált adatokat tároljuk.
Tehát minden alkalommal, amikor frissíti a lekérdezést, a négy egyesítendő Excel -táblázaton kívül a meglévő lekérdezési táblázatot is hozzáadja a kapott adatokhoz.
Ezt rekurziónak hívják.
Íme, hogyan lehet megoldani ezt a problémát.
Miután beillesztette az = Excel.CurrentWorkbook () parancsot a Power Query képlet sávjába, és megnyomja az enter billentyűt, megjelenik az Excel táblázatok listája. Annak biztosítása érdekében, hogy csak a táblázatokat kombinálhassa a munkalapról, valahogy csak ezeket a táblákat kell szűrnie, amelyeket össze szeretne vonni, és eltávolít minden mást.
A következő lépésekkel győződjön meg arról, hogy csak a szükséges táblázatokkal rendelkezik:
- Kattintson a legördülő menüre, és vigye a kurzort a szövegszűrőkre.
- Kattintson a Tartalmaz opcióra.
- A Sorok szűrése párbeszédpanelen írja be az _Data mezőt a „tartalmazza” lehetőség melletti mezőbe.
- Kattintson az OK gombra.
Előfordulhat, hogy nem lát változást az adatokban, de ezzel megakadályozza, hogy a kapott táblázat újra hozzáadásra kerüljön a lekérdezés frissítésekor.
Ne feledje, hogy a fenti lépésekben „_Adat”Szűrni, ahogy így neveztük ki a táblákat. De mi van akkor, ha tábláit nem nevezik következetesen? Mi van, ha az összes táblázat neve véletlenszerű, és nincs semmi közös bennük.
Ezt a módszert a következőképpen oldhatja meg - használja a „nem egyenlő” szűrőt, és írja be a Lekérdezés nevét (ami példánkban ConsolidatedData lenne). Ez biztosítja, hogy minden változatlan marad, és a létrehozott lekérdezéstábla kiszűrésre kerül.
Eltekintve attól a ténytől, hogy a Power Query meglehetősen egyszerűvé teszi ezt az egész folyamatot a különböző lapok (vagy akár ugyanazon lap) adatainak kombinálásáról, a használat másik előnye, hogy dinamikussá teszi őket. Ha további rekordokat ad hozzá bármelyik táblázathoz, és frissíti a Power Query -t, az automatikusan megadja a kombinált adatokat.Fontos megjegyzés: Az oktatóanyagban használt példában a fejlécek azonosak voltak. Ha a fejlécek eltérőek, a Power Query egyesíti és létrehozza az új táblázat összes oszlopát. Ha az adott oszlophoz rendelkezésre állnak adatok, akkor azok megjelennek, ellenkező esetben null értéket mutatnak.
A következő Power Query oktatóanyagok is tetszhetnek:
- Több munkafüzet adatait egyesítheti Excelben (a Power Query használatával).
- 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)
- Táblák egyesítése Excelben a Power Query használatával.
- Két Excel -lap/fájl összehasonlítása
