Adatok importálása és formázása Mac Excelben (Power Query)

A Mac Excel Power Query (más néven Beolvasás és átalakítás) technológiával teszi lehetővé az adatforrások importálását, frissítését és hitelesítését, a Power Query-adatforrások kezelését, a hitelesítő adatok törlését, a fájlalapú adatforrások helyének módosítását, valamint az adatok a követelményeknek megfelelő táblázattá alakítását. A VBA használatával Power Query-lekérdezést is létrehozhat.

Microsoft 365 Örökölt

Megjegyzés: SQL Server-adatbázis adatforrás csak az Insiders bétaverzióban importálható.

Az Excelbe számos adatforrásból importálhat adatokat a Power Query használatával: Excel-munkafüzet, szöveg/CSV, XML, JSON, SQL Server-adatbázis, SharePoint Online-lista, OData, üres tábla és üres lekérdezés.

Válassza az Adatok > Adatok lekérése lehetőséget.
A kívánt adatforrás kiválasztásához válassza az Adatok lekérése (Power Query) lehetőséget.
Az Adatforrás kiválasztása párbeszédpanelen válassza ki az elérhető adatforrások egyikét.
Csatlakozzon az adatforráshoz. További információ az egyes adatforrásokhoz való csatlakozásról: Adatok importálása adatforrásokból.
Válassza ki az importálni kívánt adatokat.
Az adatok betöltéséhez kattintson a Betöltés gombra.

Eredmény

Az importált adatok egy új munkalapon jelennek meg.

Lekérdezés jellemző eredményei

Következő lépések

Ha szeretné a Power Query-szerkesztő használatával formálni és átalakítani az adatokat, válassza az Adatok átalakításalehetőséget. További információért lásd: Adatok formálása a Power Query-szerkesztővel.

Megjegyzés: Ez a funkció általánosan elérhető a Mac Excel 16.69-es (23010700) vagy újabb verzióját futtató Microsoft 365-előfizetők számára. Ha Ön a Microsoft 365-előfizető, ellenőrizze, hogy rendelkezik-e az Office legújabb verziójával.

Eljárás

Válassza az Adatok > Adatok lekérése (Power Query) lehetőséget.
A Lekérdezésszerkesztő megnyitásához válassza a Power Query-szerkesztő indítása lehetőséget.

Tipp: A Lekérdezésszerkesztőt úgy is elérheti, hogy kiválasztja az Adatok lekérése (Power Query) lehetőséget, kiválaszt egy adatforrást, majd a Tovább gombra kattint.
Alakítsa át adatait a windowsos Excelhez hasonló módon a Lekérdezésszerkesztő használatával.

További információt az Excel súgójának Power Query című témakörében talál.
Ha végzett, válassza a Kezdőlap > Bezárás és betöltés elemre.

Eredmény

Az újonnan importált adatok egy új munkalapon jelennek meg.

Lekérdezés jellemző eredményei

A következő adatforrásokat frissítheti: SharePoint-fájlok, SharePoint-listák, SharePoint-mappák, OData-fájlok, szöveg-/CSV-fájlok, Excel-munkafüzetek (.xlsx), XML- és JSON-fájlok, helyi táblázatok és tartományok, valamint Microsoft SQL Server-adatbázis.

Az első frissítés

Amikor először kísérel meg fájlalapú adatforrásokat frissíteni a munkafüzet lekérdezéseiben, előfordulhat, hogy frissítenie kell a fájl elérési útját.

Válassza az Adatok lehetőséget, a nyilat az Adatok lekérése mellett, majd az Adatforrás-beállítások lehetőséget. Megjelenik az Adatforrás-beállítások párbeszédpanel.
Jelöljön ki egy kapcsolatot, majd válassza a Fájl elérési útjának módosítása lehetőséget.
A Fájl elérési útja párbeszédpanelen válasszon ki egy új helyet, majd válassza az Adatok lekérése lehetőséget.
Válassza a Bezárás gombot.

Későbbi időpontok frissítése

A frissítéshez:

A munkafüzet összes adatforrásához válassza az Adatok > Az összes frissítése lehetőséget.
Egy adott adatforrás esetén kattintson jobb gombbal egy lekérdezési táblázatra egy munkalapon, majd válassza a Frissítéslehetőséget.
Egy kimutatás esetén jelöljön ki egy cellát a kimutatásban, majd válassza a Kimutatás elemzése > Adatok frissítéselehetőséget.

Amikor először fér hozzá a SharePointhoz, SQL Serverhez, OData-hoz vagy más, engedélyt igénylő adatforráshoz, meg kell adnia a megfelelő hitelesítő adatokat. Előfordulhat, hogy az új hitelesítő adatok megadásához törölnie kell a régieket.

Hitelesítő adatok beírása

Amikor első alkalommal frissít egy lekérdezést, előfordulhat, hogy be kell jelentkeznie. Válassza ki a hitelesítési módszert, és adja meg az adatforráshoz való csatlakozáshoz és a frissítés folytatásához szükséges bejelentkezési hitelesítő adatokat.

Ha bejelentkezés szükséges, megjelenik a Hitelesítő adatok megadása párbeszédpanel.

Például:

SharePoint hitelesítő adatok:
SQL Server hitelesítő adatok:

Hitelesítő adatok törlése

Válassza az Adatok > Adatok lekérése > Adatforrás-beállítások lehetőséget.
Az Adatforrás-beállítások párbeszédpanelen válassza ki a kívánt kapcsolatot.
A lap alján válassza az Engedélyektörlése lehetőséget.
Erősítse meg szándékát, majd válassza a Törlés lehetőséget.

Bár a Power Query-szerkesztő nem érhető el a Mac Excelben, a VBA támogatja a Power Query létrehozását. VBA-kódmodul átvitele egy fájlban a Windows Excelből a Mac Excelbe két lépésből áll. A szakasz végén egy mintaprogramot biztosítunk Önnek.

Első lépés: Windows Excel

A Windows Excelben a VBA használatával fejleszthet lekérdezéseket. Az Excel objektummodelljében a következő entitásokat használó VBA-kód a Mac Excelben is működik: Lekérdezések objektum, WorkbookQuery objektum, Workbook.Queries tulajdonságTovábbi információért lásd: Excel VBA-referencia.
Az Excelben az ALT+F11 billentyűkombinációt lenyomva győződhet meg arról, hogy a Visual Basic Editor meg van nyitva.
Kattintson a jobb gombbal a modulra, majd válassza a Fájl exportálása lehetőséget. Megjelenik az Exportálás párbeszédpanel.
Adjon meg egy fájlnevet, győződjön meg arról, hogy a fájl kiterjesztése .bas, majd válassza a Mentés lehetőséget.
Töltse fel a VBA-fájlt egy online szolgáltatásba, hogy elérhetővé tegye azt Mac gépről.

Használhatja a Microsoft OneDrive-ot. További információért lásd: Fájlok szinkronizálása a OneDrive-val Mac OS X rendszeren.

Második lépés: Mac Excel

Töltse le a VBA-fájlt egy helyi fájlba, az "Első lépés: Windows Excel" című lépésben mentett és egy online szolgáltatásba feltöltött VBA-fájlt.
A Mac Excelben válassza az Eszközök > Makró > Visual Basic Editor lehetőséget. Megjelenik a Visual Basic Editor ablak.
Kattintson jobb gombbal egy objektumra a Projekt ablakban, majd válassza a Fájl importálása lehetőséget. Megjelenik a Fájl importálása párbeszédpanel.
Keresse meg a VBA-fájlt, majd válassza a Megnyitás lehetőséget.

Mintakód

Íme néhány alapszintű kód, amelyet beépíthet és használhat. Ez egy minta lekérdezés, amely egy listát hoz létre 1 és 100 közötti értékekből.

Sub CreateSampleList()
    ActiveWorkbook.Queries.Add Name:="SampleList", Formula:= _
        "let" & vbCr & vbLf & _
            "Source = {1..100}," & vbCr & vbLf & _
            "ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error)," & vbCr & vbLf & _
            "RenamedColumns = Table.RenameColumns(ConvertedToTable,{{""Column1"", ""ListValues""}})" & vbCr & vbLf & _
        "in" & vbCr & vbLf & _
            "RenamedColumns"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=SampleList;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [SampleList]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "SampleList"
        .Refresh BackgroundQuery:=False
    End With
End Sub

Nyissa meg az Excel-munkafüzetet.
Ha biztonsági figyelmeztetést kap a külső adatkapcsolatok letiltásáról, válassza a Tartalom engedélyezése lehetőséget.
Ha megjelenik a Fájlhozzáférés engedélyezése párbeszédpanel, válassza a Kiválasztás lehetőséget, majd válassza a Hozzáférés engedélyezése lehetőséget az adatforrásfájlokat tartalmazó legfelső szintű mappánál.
Válassza az Adatok > Szövegből (örökölt) lehetőséget. Megjelenik a Kereső párbeszédpanel.
Keresse meg a .txt vagy .csv fájlt, és válassza a Megnyitás lehetőséget.Megjelenik a Szövegbeolvasó varázsló.

Tipp: Rendszeresen ellenőrizze a Kijelölt adatok előnézete panelt a választások megerősítéséhez.
Az első lapon tegye a következőket:

Fájltípus    A szövegfájl típusának kiválasztásához válassza a Tagolt vagy Rögzített szélességű lehetőséget.

Sor száma    A Beolvasás első sora lehetőségnél válassza ki a megfelelő sor számát az importálás első sorának meghatározásához.

Karakterkészlet    A Fájl eredete lehetőségnél válassza ki a szövegfájlban használt karakterkészletet. A legtöbb esetben ezt a beállítást alapértelmezetten hagyhatja.
A második oldalon tegye a következőket:

Tagolt

Ha az első oldalon a Tagolt lehetőséget választotta, a Határolójelek területen válassza ki az elválasztó karaktert, vagy az Egyéb jelölőnégyzettel adjon meg egy nem felsorolt karaktert.

Válassza az Egymást közvetlenül követő határolók egynek számítanak lehetőséget, ha az adatok egynél több karakterből álló elválasztót tartalmaznak az adatmezők között, vagy ha az adatok több egyéni elválasztót tartalmaznak.

A Szövegjelölő lehetőségnél válassza ki a karaktert, amely az értékeket foglalja magába a szövegfájlban. A leggyakrabban ez az idézőjel („).

Rögzített szélesség

Ha a Rögzített szélesség lehetőséget választotta az első lapon, kövesse az utasításokat törésvonal létrehozásához, törléséhez vagy áthelyezéséhez a Kijelölt adatok előnézete mezőben.
A harmadik lapon tegye a következőket:

A Kijelölt adatok előnézete alatt található minden oszlopnál jelölje ki, majd szükség esetén módosítsa másik oszlopformátumra. A dátumformátumot tovább állíthatja, és a számadatok beállításainak módosításához válassza a Speciális lehetőséget. Az adatokat importálás után konvertálhatja is.

Válassza a Befejezés lehetőséget. Megjelenik az Adatok importálása párbeszédpanel.
Válassza ki, hová szeretné felvenni az adatokat: a meglévő munkalapra, egy új munkalapra vagy egy kimutatásba.
Kattintson az OK gombra.

A kapcsolat működésének biztosításához adjon meg néhány adatot, majd válassza a Kapcsolatok > Frissítés lehetőséget.

Válassza az Adatok > SQL ServerODBC-ből lehetőséget. Ekkor megjelenik a Kapcsolódás SQL Server ODBC-adatforráshoz párbeszédpanel.
Adja meg a kiszolgálót a Kiszolgálónév mezőbe, és igény szerint adja meg az adatbázist az Adatbázis neve mezőbe.

Kérje le ezt az információ az adatbázis adminisztrátortól.
Az Ellenőrzés területen válasszon egy módszert a listából: Felhasználónév/jelszó, Kerberos vagy NTLM.
Adja meg a hitelesítő adatokat a Felhasználónév és a Jelszó mezőkben.
Válassza a Kapcsolódás lehetőséget. Megjelenik a Navigátor párbeszédpanel.
A bal oldali panelen keresse meg a kívánt táblázatot, majd jelölje ki.
Erősítse meg az SQL-utasítást a jobb oldali panelen. Az SQL-utasítást az igényeinek megfelelően módosíthatja.
Az adatok előnézetének megtekintéséhez válassza a Futtatás lehetőséget.
Ha elkészült, válassza az Adatok visszaküldése lehetőséget. Megjelenik az Adatok importálása párbeszédpanel.
Válassza ki, hová szeretné felvenni az adatokat: a meglévő munkalapra, egy új munkalapra vagy egy kimutatásba.
A kapcsolat tulajdonságainak beállításához a Használat és Definíció lapokon a Tulajdonságok párbeszédpanelen válassza a Tulajdonságok lehetőséget. Az adatok importálása után választhatja az Adatkapcsolatok > Kapcsolatok lehetőséget is, majd a Kapcsolat tulajdonságai párbeszédpanelen válassza a Tulajdonságok lehetőséget.
Kattintson az OK gombra.
A kapcsolat működésének biztosításához adjon meg néhány adatot, majd válassza az Adatok > Az összes frissítése lehetőséget.

Ha olyan külső forrást kíván használni, amely nem SQL-adatbázis (például FileMaker Pro), használhat ODBC (Open Database Connectivity) illesztőprogramot a Mac gépén. Az illesztőprogramokkal kapcsolatos információkat ezen a webhelyen talál. Ha telepítette az adatforrás illesztőprogramját, kövesse ezeket a lépéseket:

Válassza az Adatok > Adatbázisból (Microsoft Query) lehetőséget.
Adja hozzá az adatbázis adatforrását, majd válassza az OK lehetőséget.
Ha az SQL Server kéri a hitelesítő adatokat, adja meg az ellenőrzési módszert, a felhasználónevet és a jelszót.
A bal oldalon válassza a kiszolgáló melletti nyilat az adatbázisok megtekintéséhez.
Válassza ki a nyilat a kívánt adatbázis mellett.
Válassza ki a kívánt táblázatot.
Az adatok előnézetének megtekintéséhez válassza a Futtatás lehetőséget.
Ha elkészült, válassza az Adatok visszaküldése lehetőséget.
Az Adatok beolvasása párbeszédpanelen válassza ki, hová szeretné helyezni az adatokat: a meglévő munkalapra, egy új munkalapra vagy egy kimutatásba.
Válassza az OK gombot.
A kapcsolat működésének biztosításához adjon meg néhány adatot, majd válassza az Adatok > Az összes frissítése lehetőséget.

Ha az engedélyei nem működnek, előfordulhat, hogy először törölnie kell őket, majd bejelentkezni.

Válassza az Adatok > Kapcsolatok lehetőséget. Megjelenik a Munkafüzet kapcsolatai párbeszédpanel.
Jelölje ki a kívánt kapcsolatot a listában, majd válassza az Engedélyek törlése lehetőséget.