Hogyan használható az Power Query az Excelben?
Az Excel Power Query adatforrások keresésére, az adatforrásokkal való kapcsolatok létrehozására, majd az adatok elemzési követelményünknek megfelelő alakítására szolgál. Miután elvégeztük az adatok igényünknek megfelelő formálását, megoszthatjuk eredményeinket és különféle jelentéseket készíthetünk több lekérdezés felhasználásával.
Lépések
Alapvetően 4 lépés van, és a Power Query 4 lépésének sorrendje a következő:
- Csatlakozás: Először kapcsolódunk az adatokhoz, amelyek lehetnek valahol, a felhőben, a szolgáltatásban vagy lokálisan.
- Átalakítás: A második lépés az adat alakjának megváltoztatása a felhasználói igényeknek megfelelően.
- Kombinálás: Ebben a lépésben végrehajtunk néhány átalakítási és összesítési lépést, és egyesítjük mindkét forrás adatait kombinált jelentés készítéséhez.
- Kezelés: Ez egyesíti és csatolja a lekérdezés oszlopait a munkafüzet többi lekérdezésében szereplő oszlopokkal.
Az Excel Power Query számos szuperhatékony funkcióval rendelkezik.
Tegyük fel, hogy 180 fájlban vannak az elmúlt 15 év vásárlási adatai. Most egy szervezet vezetése megkövetelné a számok összevonását, mielőtt elemezné őket. A menedzsment a következő módszerek bármelyikét alkalmazhatja:
- Megnyitnák az összes fájlt, és egy fájlba másolnák.
- Másrészt bölcs megoldást alkalmazhatnak, amely képletek alkalmazása, mivel ez hajlamos a tévedésre.
Bármelyik módszert is választják, sok kézi munkát tartalmaz, és néhány hónap elteltével új értékesítési adatok lennének a hozzáadott időtartamra. Újra meg kell csinálniuk ugyanazt a gyakorlatot.
A Power Query azonban segíthet nekik abban, hogy ne végezzék el ezt az unalmas és ismétlődő munkát. Értsük meg ezt az excel teljesítmény lekérdezést egy példával.
Példa
Tegyük fel, hogy szöveges fájlok vannak egy értékesítési adatokat tartalmazó mappában, és ezeket az adatokat az excel fájlunkba szeretnénk kapni.
Amint az alábbi képen láthatjuk, hogy két típusú fájl van a mappában, de csak az Excel fájlban lévő szöveges fájlok adatait szeretnénk megszerezni.
Ugyanez a lépés a következő:
1. lépés: Először meg kell szereznünk az adatokat a Power Query-ben, hogy meg tudjuk valósítani a szükséges módosításokat az adatokban az importáláshoz egy Excel fájlba.
Ehhez ugyanazt fogjuk választani a „From Folder” opciót a „From File” menü után kattintva a parancs „Get Data” a „Get & Transform” csoport a „Data” fülre.
2. lépés: Válassza ki a mappa helyét böngészéssel.
Kattintson az "OK" gombra
3. lépés: Megnyílik egy párbeszédpanel, amely tartalmazza a kiválasztott mappában található összes fájl listáját, amelynek oszlopfejlécei: „Tartalom”, „Név”, „Kiterjesztés”, „Hozzáférés dátuma”, „ Módosítás dátuma”, „Létrehozás dátuma”. 'Attribútumok' és 'Mappaútvonal'.
Három lehetőség közül választhat: Összevonás , Betöltés és Átalakítás .
- Kombinálás : Ezzel az opcióval egy képernyőre léphetünk, ahol kiválaszthatjuk, mely adatokat kombináljuk. A Szerkesztés lépést kihagyja ennél az opciónál, így nem tudjuk ellenőrizni, mely fájlokat kell kombinálni. A Combine funkció a mappában lévő összes fájlt konszolidálja, ami hibákhoz vezethet.
- Betöltés: Ez az opció csak a táblázatot tölti be a képen látható módon az Excel munkalapra a fájlokban szereplő tényleges adatok helyett.
- Adatok átalakítása: A 'Combine' paranccsal ellentétben , ha ezt a parancsot használjuk, akkor kiválaszthatjuk, mely fájlokat kívánjuk kombinálni, azaz csak egyetlen fájltípust (azonos kiterjesztést) kombinálhatunk.
Csakúgy, mint esetünkben, csak szöveges fájlokat (.txt) akarunk kombinálni; választjuk az „Adatok átalakítása” parancsot.
Az ablak jobb oldalán láthatjuk az „Alkalmazott lépéseket”. Egyelőre csak egyetlen lépés van, amely a fájlok részleteinek levétele a mappából.
4. lépés: Van egy „Extension” nevű oszlop, ahol láthatjuk, hogy az oszlopban szereplő értékeket mindkét esetben írják, azaz nagy és kisbetűket.
Azonban az összes értéket kisbetűvé kell alakítanunk, mivel a szűrő megkülönbözteti mindkettőt. Ehhez ki kell választanunk az oszlopot, majd a „Formátum” parancs menüjében a „ Kisbetűk” lehetőséget kell választanunk .
5. lépés: Szűrjük az adatokat a „Bővítmény” oszlop segítségével a szöveges fájlokhoz.
6. lépés: Mindkét szövegfájl adatait egyesítenünk kell az első „Tartalom” oszlop segítségével . Kattintson az oszlop neve jobb oldalán elhelyezett ikonra.
7. lépés: Megnyílik a „Fájlok kombinálása” feliratú párbeszédpanel, ahol ki kell választanunk az elválasztót „Tab” -ként a szöveges fájlokhoz (a .txt kiterjesztésű fájlok), és kiválaszthatjuk az alapot az adattípus észleléséhez. És kattintson az "OK" gombra.
Az 'OK' gombra kattintás után megkapjuk a szöveges fájlok összesített adatait a 'Power Query' ablakban.
Szükség szerint megváltoztathatjuk az oszlopok adattípusát. A "Bevétel" oszlopban az adattípust "Pénznem" -re változtatjuk .
Az ablak jobb oldalán található áramellátási lekérdezés segítségével láthatjuk az adatokra alkalmazott lépéseket.
Miután elvégeztük az összes szükséges változtatást az adatokban, betölthetjük az adatokat egy excel munkalapra a „Bezárás és betöltés” paranccsal a „Kezdőlap” fül „Bezárás” csoportjában .
Ki kell választanunk, hogy táblázatként vagy kapcsolatként szeretnénk-e betölteni az adatokat. Ezután kattintson az 'OK' gombra.
Most táblázatként láthatjuk az adatokat a munkalapon.
És a jobb oldalon található "Munkafüzet-lekérdezések" ablaktábla, amelyet felhasználhatunk a lekérdezések szerkesztésére, másolására, egyesítésére, kiegészítésére és sok más célra.
Az Excel Power Query nagyon hasznos, mivel láthatjuk, hogy 601612 sort töltöttek be néhány percen belül.
Dolgok, amikre emlékezni kell
- A Power Query nem változtatja meg az eredeti forrásadatokat. Az eredeti forrásadatok megváltoztatása helyett minden egyes lépést rögzít, amelyet a felhasználó az adatok összekapcsolása vagy átalakítása közben megtett, és miután a felhasználó befejezte az adatok alakítását, a finomított adatsort veszi és beviszi a munkafüzetbe.
- A Power Query megkülönbözteti a kis- és nagybetűket.
- A fájlok összevonása során a megadott mappában meg kell győződnünk arról, hogy az „Extension” oszlopot használva ki kell-e zárni az ideiglenes fájlokat (amelyek kiterjesztése „.tmp”, és ezeknek a fájloknak a neve „~” jellel kezdődik), A Power Query ezeket a fájlokat is importálhatja.
