A Power Query egy hihetetlenül hasznos eszköz az Excel-felhasználók számára, akik külső adatokat szeretnének importálni, csatlakozni és formálni. Az eszköz Excelhez való hozzáadásának folyamata a használt verziótól függ.
Az Excel 2016 vagy újabb verzióit használóknak nem kell a Power Query-t hozzáadniuk az Excelhez. Beépített adatgyűjtés és átalakítás” funkció az Adatok lapon. Az Excel 2013 és 2010 felhasználóinak le kell tölteniük és telepíteniük kell a Microsoft Power Query bővítményt.
Használhatja az Excel-táblázatokat, CSV-fájlokat, adatbázisokat és még sok más lehetőséget. Innentől kezdve a hatékony funkciók lehetővé teszik az adatok szerkesztését, átalakítását és elemzését.
Tartalomjegyzék
A Power Query bővítmény telepítése az Excel 2010 vagy 2013 programhoz
Vegye figyelembe, hogy a Mac felhasználóknak legalább Excel 2016-tal kell rendelkezniük, mivel a Power Query nem érhető el az Excel korábbi verzióihoz Mac OS rendszeren. A Windows-felhasználók az Excel 2000 ingyenes bővítményét használhatják.
A bővítmény letöltése előtt tudnia kell, hogy az Excel 32 bites vagy 64 bites telepítése van-e. A letöltési oldal megkérdezi, hogy melyikkel rendelkezik.
Az alábbi képen a kiválasztott 64 bites opció látható.
2010 és 2013 között van egy kis különbség az információ megtalálásában. Mindkét verziónál bontsa ki a Fájl menüt, és kattintson a „Súgó” lehetőségre. A 32-bites/64-bites információk a következők valamelyike alatt találhatók:
„A Microsoft Excelről” az Excel 2010-ben
„Az Excelről” az Excel 2013-ban
A bővítmény telepítési folyamata lényegében azonos mindkét verziónál. A Power Query bővítmény telepítéséhez kövesse az alábbi lépéseket:
Zárja be az Excelt.
Nyissa meg a Microsoft hivatalos letöltési oldalát.
Kattintson a „Letöltés” gombra.
Válassza ki a megfelelő verziót (32 bites vagy 64 bites) az Excel telepítése alapján.
Futtassa a letöltött fájlt, és kövesse a telepítővarázsló utasításait.
Ha az Excel hibaüzenetet jelenít meg a telepítés során, ellenőrizze, hogy rendelkezik-e az Ön verziójához tartozó legújabb frissítésekkel.
Amikor a telepítés befejeződött, engedélyeznie kell a bővítményt az Excelben, hogy megjelenjen a Power Query lap.
Nyissa meg az Excelt.
Válassza a Fájl -> Beállítások lehetőséget.
Kattintson a „Bővítmények” elemre a bal oldali ablaktáblában.
Az ablak alján található legördülő menüben válassza a „COM-bővítmények” lehetőséget.
Jelölje be a „Microsoft Power Query for Excel” melletti négyzetet, és kattintson az „OK” gombra.
A Power Query mostantól elérhető az Excel szalag saját lapján!
Excel Power Query az Excel 2016 és újabb verzióihoz
Az Excel 2016 és újabb verzióiban a Power Query mostantól az Excel menüszalag része. A hozzáféréshez kövesse az alábbi lépéseket:
Nyissa meg az Excelt, és hozzon létre egy új dokumentumot, vagy töltsön be egy meglévőt.
Lépjen az „Adatok” fülre.
A Power Query eszközök eléréséhez használja az „Adatok lekérése és átalakítása” csoport gombjait.
Hogyan találhat Power Query eszközöket az Adatok lapon
Az Adatok lap „Adatok lekérése és átalakítása” szakasza gombokat kínál számos olyan eszköz és szolgáltatás eléréséhez, amelyek az Excel Power Query részét képezik.
Itt található a hét gomb és funkció leírása.
1. Get Data
Ez a funkció lehetővé teszi a külső adatforrásokhoz való csatlakozást és adatok importálását. A leggyakrabban használt lehetőségek közül néhány:
Fájlból: importálás Excel-munkafüzetekből, CSV-, szöveg-, XML- vagy JSON-fájlokból.
Adatbázisból: importálás SQL Serverből, Accessből, Oracle-ből és egyebekből.
Azure-ból: importálás az Azure SQL Database-ból, az Azure Blob Storage-ból és egyebekből.
2. Szöveg/CSV fájlból
A „Szövegből/CSV-ből” gomb lehetővé teszi, hogy szöveges fájlhoz vagy CSV-fájlhoz (vesszővel tagolt értékek) csatlakozzon, és adatokat importáljon azokból. Megnyílik egy Adatok importálása párbeszédpanel, ahol kiválaszthatja a fájlt.
Ugyanazt a TXT- vagy CSV-fájlt érheti el az Adatok lekérése legördülő menüvel. A „Szövegből/CSV-ből” gomb parancsikont biztosít.
Az alábbi képen látható a használt parancsikon. Az ablakban megjelenik a fájlban található adatok előnézete is.
3. Webről
Ez egy másik parancsikon az „Adatok lekérése” legördülő menüben elérhető funkcióhoz. A „Webről” gomb lehetővé teszi, hogy csatlakozzon weboldalakhoz, és adatokat importáljon azokról az URL-címeik használatával.
4. Táblázatból/Tartományból
Ezzel a gombbal konvertálhat egy kijelölt cellatartományt vagy az Excel-munkafüzet meglévő táblázatát Power Query-lekérdezéssé.
Ha kiválaszt egy tartományt, és rákattint erre a gombra, a Power Query Editor automatikusan megnyílik. Ezzel a szerkesztővel egy későbbi rész foglalkozik.
5. Képből
Bár a „Képből” funkció a Power Query részben van csoportosítva, valójában nem része a Power Querynek.
Ez egy külön funkció, amely lehetővé teszi, hogy a készülék kamerájával adatokat rögzítsen nyomtatott vagy kézzel írt táblázatból, és az Excelben digitális táblázattá alakítsa át.
Optikai karakterfelismerő (OCR) technológiát használ a szöveg és a számok felismerésére és a képből való kiemelésére.
6. Friss források
Ez a gomb gyors hozzáférést biztosít azokhoz az adatforrásokhoz, amelyekhez nemrég csatlakozott, és amelyekről adatokat importált az Excelben. Értékes időmegtakarítást jelent az adatlekérdezések során.
Ha a „Legutóbbi források” gombra kattint, egy legördülő menüben megjelenik a legutóbb használt adatforrások listája, például online kapcsolatok és Excel-fájlok.
7. Meglévő kapcsolatok
Ez egy újabb időmegtakarítás. Ez egy listát ad az összes adatkapcsolatról, amely korábban létrejött az aktuális munkafüzetben.
Power Query csatlakozók hozzáadása az Excelhez
Lehetnek olyan speciális Power Query-funkciók vagy csatlakozók, amelyek csak az Excel bizonyos kiadásaiban érhetők el, például az Excel 2016 Professional Plus vagy az Excel for Microsoft Office 365.
Például a SharePoint-összekötő, amely lehetővé teszi a SharePoint-mappákhoz való csatlakozást, csak vállalati előfizetésekben érhető el.
Ha van otthoni vagy üzleti előfizetése, akkor ez a lehetőség nem jelenik meg a csatlakozók listájában.
A Microsoft elérhetőségi listájának alábbi kivonata azt mutatja, hogy a SharePoint- és PDF-kapcsolatok nem minden kiadásban érhetők el.
Ha hiányoznak a szükséges csatlakozók és források, fontolja meg a magasabb kiadásra való frissítést. Ez engedélyezi a hiányzó opciókat.
Power Query engedélyek hozzáadása az Excelhez
Előfordulhatnak bizonyos csatlakozók vagy funkciók, amelyek további engedélyeket vagy beállításokat igényelnek, például szervezetspecifikus adatforrások.
Ha problémákba ütközik bizonyos adatforrásokhoz való kapcsolódás vagy bizonyos szolgáltatások használata során, annak oka lehet a szervezeti szintű engedélyek vagy beállítások. Ilyen esetekben segítségért forduljon szervezete informatikai rendszergazdájához vagy támogatási csapatához.
Ha további engedélyeket biztosítanak Windows-fiókjához, indítsa újra az Excelt, hogy hozzáférjen a magasabb szintű jogosultságokhoz.
A Power Query szerkesztő elérése
Itt töltheti a legtöbb időt a Power Query használatával. Ez egy grafikus felület, amelyen létrehozhatja, módosíthatja és kezelheti lekérdezéseit.
A szerkesztő vizuálisan is megjeleníti a lekérdezés szerkezetét, és számos különböző nézetet támogat.
A szerkesztőt többféleképpen is elérheti. Gyakran létre kell hozni egy kapcsolatot vagy importálni egy fájlt, amely elindítja a Szerkesztőt.
Alternatív megoldásként megnyithatja a Szerkesztőt üres lekérdezésként, és beállíthatja a kapcsolatot abból. Ezt a következőképpen teheti meg az Adatok lekérése és átalakítása csoportban:
Lépjen az Adatok fülre.
Kattintson az „Adatok lekérése” elemre az „Adatok lekérése és átalakítása” csoportban.
Válassza a „Power Query Editor indítása” lehetőséget.
Az adatok eléréséhez használja a szalag jobb oldalán található lehetőségeket.
Az „Új lekérdezés” opciócsoport a szalag jobb szélén található. Innen importálhat fájlokat, csatlakozhat adatbázishoz, és akár manuálisan is megadhat adatokat.
Szalag és Lapok
A Power Query szerkesztő szalagja számos lapot tartalmaz, amelyeken a könnyű hozzáférés érdekében csoportokba rendezett beállítások találhatók.
Ezek a lapok különböző célokat szolgálnak, olyan eszközöket és funkciókat biztosítva, amelyekre a Power Query használata során szüksége lesz. A négy lap a következő:
itthon
Átalakítani
Oszlop hozzáadása
Kilátás
Ezen a képen az ablak tetején lévő fülek láthatók. A szalag alatti adatok előnézete egy Excel-tábla tartalmát mutatja. A Lekérdezések panel a bal oldalon található.
Adat-előnézeti ablaktábla
Az adatok előnézeti ablaktáblája nem csak az Ön adatait jeleníti meg. Ebben az ablakban módosíthatja az oszlopfejlécet és az adattípust.
A panel tetején lévő képletsor az adatbázis-lekérdezés mögötti speciális kódot mutatja.
Speciális szerkesztő
A bonyolultabb műveletekhez vagy testreszabáshoz a Power Query az Advanced Editort kínálja. Ez a szerkesztő lehetővé teszi az M-kód közvetlen szerkesztését, amely a Power Query alapnyelve.
Az M-kód módosításával finomhangolhatja lekérdezéseit, és olyan további funkciókhoz juthat, amelyek esetleg nem érhetők el a grafikus felületen keresztül.
A Speciális szerkesztő elérése:
Nyissa meg a Kezdőlap lapot a Power Query-szerkesztőben.
Kattintson a „Speciális szerkesztő” elemre a Lekérdezés csoportban.
Ha szeretné felfedezni a kód néhány aspektusát, íme néhány oktatóanyag:
Íme egy videó az egyes kifejezések használatáról.
2 módszer a Power Query testreszabására
Testreszabhatja Power Query-élményét:
a lekérdezési beállítások módosítása
globális és adatvédelmi opciók kezelése
1. A lekérdezési beállítások módosítása
A lekérdezési beállítások elérése:
Nyissa meg a Power Query szerkesztőt.
Tekintse meg a beállításokat a jobb oldali ablaktáblában.
Kattintson az „Összes tulajdonság” hivatkozásra.
Igény szerint módosítja a felugró ablak részleteit.
A Microsoft-lekérdezés számos aspektusát módosíthatja, például:
Név: nevezze át a lekérdezést valami leíróbbra.
Leírás: adjon hozzá leírást, hogy több kontextust biztosítson.
Beállítások betöltése: válassza ki, hogy az adatokat az adatmodellbe töltse be, vagy csak kapcsolatként.
Az alábbi képen a „tábla” elnevezést átváltoztatom informatívabb „értékesítésre” és leírást adok hozzá.
2. A globális beállítások módosítása
A Power Query lehetővé teszi az általános élmény testreszabását is. A lehetőségek eléréséhez kövesse az alábbi lépéseket:
Az Excelben lépjen az Adatok lapra.
Kattintson a Get Data legördülő menüre.
Válassza alul a Lekérdezési beállítások lehetőséget.
Ezzel megnyílik a Lekérdezési beállítások párbeszédpanel. Itt módosíthatja a Power Query Editor elrendezését és viselkedését egy jelölőnégyzet bejelölésével vagy törlésével.
3. A biztonsági és adatvédelmi beállítások módosítása
A Lekérdezési beállítások tartalmaznak egy biztonsági és adatvédelmi részt.
A biztonsági szakasz lehetővé teszi a tanúsítvány-ellenőrzések és a webes figyelmeztetések szabályozását, ha lekérdezései egy külső online adatforráshoz érkeznek.
Az Adatvédelem részben beállíthatja, hogy a Power Query hogyan kezelje és védje a bizalmas adatokat.
További eszközök hozzáadása a Power Query javításához
Vannak más bővítmények és eszközök is, amelyek segítségével kibővítheti a Power Query funkcióit. Íme három népszerű lehetőség:
1. Power Pivot
A Power Pivot egy hatékony adatmodellező és -elemző eszköz, amely az Excel újabb verzióiba épült, és jól működik a Power Queryvel.
Lehetővé teszi, hogy:
összetett adatmodellek létrehozása.
táblák közötti kapcsolatok kezelése.
speciális számításokat végezhet Data Analysis Expressions (DAX) használatával.
Ha Excel for Microsoft 365, Excel 2016 vagy Excel 2019 alkalmazást használ, kövesse az alábbi lépéseket a Power Pivot aktiválásához a kimutatástáblázatokhoz:
Lépjen a Fájl fülre.
Az Excel beállításai párbeszédpanel megnyitásához válassza a Beállítások lehetőséget.
Kattintson a „Bővítmények” kategóriára a bal oldali ablaktáblában.
Válassza a „COM-bővítmények” elemet az alján található legördülő menüből, majd kattintson a „Go” gombra.
Keresse meg a listában a „Microsoft Power Pivot for Excel” elemet, és jelölje be a mellette lévő négyzetet.
Kattintson az „OK” gombra a Power Pivot engedélyezéséhez.
A Power Pivot engedélyezése után egy új „Power Pivot” lap jelenik meg az Excel szalagján. A következő lépés az, hogy megtanulja, hogyan kell dolgozni egy kimutatástáblával a Power Pivot ablakban.
Ezen a képen a Power Pivot és a Power Map is látható a bővítmények ablakában:
2. Power Map (3D térképek)
A Power Map, más néven 3D térkép, egy adatmegjelenítési eszköz az Excelhez, amely lehetővé teszi interaktív 3D térinformatikai vizualizációk létrehozását az adatok felhasználásával. Ez egy nagyszerű módja a földrajzi adatok megjelenítésének a Power Queryből.
Ha Excel for Microsoft 365, Excel 2016 vagy Excel 2019 alkalmazást használ, kövesse az alábbi lépéseket a Power Map engedélyezéséhez:
Lépjen a „Beszúrás” fülre.
Kattintson a „3D térkép” gombra a „Túrák” csoportban.
Ha a rendszer kéri, hogy engedélyezze az adatelemző bővítményeket, kattintson az „Engedélyezés” gombra.
Megnyílik a 3D térképek ablaka, ahol a következőket teheti:
megjelenítheti térinformatikai adatait három dimenzióban
interaktív túrákat hozhat létre
testreszabhatja térképei megjelenését.
3. LuckyTemplates
amelyek segíthetnek az adatok elemzésében és megjelenítésében.
Ez nem az Excel bővítménye. Ehelyett ez egy különálló, LuckyTemplates Desktop nevű alkalmazás. Windows 10 vagy újabb rendszerre telepíthető, és 64 bites operációs rendszer szükséges.
Az Excel Mac felhasználók használhatják a LuckyTemplates szolgáltatást, amely a LuckyTemplates felhő alapú verziója, amely a webböngészőben fut.
Utolsó szavunk vagy kettő
Megtanulta, hogyan veheti fel vagy érheti el a Power Query eszközt az Excel-munkalapon. Ezzel az eszközzel összetett üzleti elemzést végezhet néhány kattintással.
Lehet, hogy kissé túlterheltek az átalakítás lapon és a szerkesztőben található összes funkció miatt. Próbáljon meg új adatbázis-lekérdezést létrehozni, vagy végezzen adatbetöltést szöveges fájlokból, hogy gyakorolja az adatkészletek átalakítását.
Hamarosan úton lesz a Power Query erejének kiaknázása felé.
Ismerje meg, hogyan töltheti le és telepítheti a DAX Studio és a Tabular Editor 3 alkalmazást, és hogyan konfigurálhatja őket a LuckyTemplates és az Excel programban való használatra.
Ez a blog tartalmazza a Shape Map vizualizációt a LuckyTemplates térbeli elemzéséhez. Megmutatom, hogyan használhatja hatékonyan ezt a vizualizációt annak funkcióival és elemeivel.
Ebben az oktatóanyagban egy egyedülálló ötletet mutatok be a pénzügyi jelentésekkel kapcsolatban, amely az eredmények hozzárendelését jelenti a LuckyTemplates-en belüli táblázatsablonok előre meghatározásához.
Hozzon létre DAX-mértékeket a LuckyTemplates alkalmazásban meglévő mértékek vagy képletek használatával. Ezt nevezem mértékelágazási technikának.
Ebben a blogban fedezze fel a LuckyTemplates adatkészletet, a leghatékonyabb függvényhívást, amely M- és DAX-függvények ezreit juttatja a keze ügyébe.
A mai oktatóanyagban megosztok néhány adatmodellezési technikát a DAX-mérések jobb rendszerezéséhez a hatékonyabb munkafolyamat érdekében.
A LuckyTemplates egy nagyszerű eszköz a pénzügyi jelentésekhez. Itt található egy oktatóanyag, amely bemutatja, hogyan hozhat létre személyre szabott táblázatokat a LuckyTemplates pénzügyi irányítópultjához.
Ez az oktatóanyag megvitatja a Power Query nyelvi folyamatot, és azt, hogyan segíthet zökkenőmentes és hatékony adatjelentések létrehozásában.
Az egyik kedvenc technikámról fogok beszélni a LuckyTemplates egyéni ikonok körül, amely dinamikusan használja az egyéni ikonokat a LuckyTemplates vizualizációkban.
Ebben a blogban megmutatom, hogyan hozhat létre LuckyTemplates táblázatokat olyan képlet segítségével, amely egyesíti az UNION és a ROW függvényt.
