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

Adatforrások importálása

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.

1. Válassza az Adatok>lekérése lehetőséget.

   

2. A kívánt adatforrás kiválasztásához válassza az Adatok lekérése (Power Query) lehetőséget.

3. Az Adatforrás kiválasztása párbeszédpanelen válassza ki az elérhető adatforrások egyikét.

   

4. Csatlakozzon az adatforráshoz. Az egyes adatforrásokhoz való csatlakozásról további információt az Adatok importálása adatforrásokból című témakörben talál.

5. Válassza ki az importálni kívánt adatokat.

6. Töltse be az adatokat a Betöltés gombra kattintva.

Eredmény

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

További 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.

Adatok formálása a Power Query-szerkesztővel

Eljárás

1. Válassza az Adatok>lekérése (Power Query) lehetőséget.

2. 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.

3. 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.

4. Ha végzett, válassza a Kezdőlap>bezárása & a Betöltés lehetőséget.

Eredmény

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

Adatforrások frissítése

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, Microsoft SQL Server-adatbázis, valamint mappák.

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.

1. 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.
2. Jelöljön ki egy kapcsolatot, majd válassza a Fájl elérési útjának módosítása lehetőséget.
3. A Fájl elérési útja párbeszédpanelen válasszon egy új helyet, majd válassza az Adatok lekérése lehetőséget.
4. 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álasszaaz Ö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.
• Kimutatás, jelöljön ki egy cellát a kimutatásban, majd válassza a Kimutatás elemzése>Adatfrissítés lehetőséget.

Hitelesítő adatok megadása és törlése

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

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

Power Query VBA-kód létrehozása és átvitele

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

1. 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ódok a Mac Excel alkalmazásban is működnek: Queries objektum, WorkbookQuery objektum, Workbook.Queries tulajdonság. További információ: Excel VBA-referencia.

2. Az Excelben az ALT+F11 billentyűkombinációt lenyomva győződhet meg arról, hogy a Visual Basic Editor meg van nyitva.

3. 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.

4. 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.

5. 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

1. 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.
2. A Mac Excel alkalmazásban válassza az Eszközök>Makró>Visual Basic Editor lehetőséget. Megjelenik a Visual Basic Editor ablak.
3. 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.
4. 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

TXT- vagy CSV-fájlok importálása és frissítése

1. Nyissa meg az Excel-munkafüzetet.

2. Ha biztonsági figyelmeztetést kap a külső adatkapcsolatok letiltásáról, válassza a Tartalom engedélyezése lehetőséget.

3. 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.

4. Adatok>kijelölése szövegből (örökölt). Megjelenik a Finder párbeszédpanel.

5. Keresse meg a .txt vagy .csv fájlt, és válassza a Megnyitás lehetőséget. Megjelenik a Szövegbeolvasó varázsló .

   Tipp A kiválasztott adatok előnézetét többször ellenőrizve erősítse meg a választásait.

6. 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 a 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 mezőben válassza ki a szövegfájlban használt karakterkészletet. A legtöbb esetben ezt a beállítást alapértelmezetten hagyhatja.

7. A második lapon tegye a következőket:

   Határolt

   Ha az első oldalon a Határolójelet választotta, a Határolójelek területen jelölje 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 az első oldalon a Rögzített szélesség lehetőséget választotta, 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.

8. A harmadik lapon tegye a következőket:

   A Kijelölt adatok előnézete alatt lévő 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 numerikus adatok 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.

   Kattintson vagy koppintson a Befejezés gombra. Megjelenik az Adatok importálása párbeszédpanel.

9. Válassza ki, hová szeretné felvenni az adatokat: a meglévő munkalapra, egy új munkalapra vagy egy kimutatásba.

10. 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ése lehetőséget.

Csatlakozzon egy SQL-adatbázishoz (ODBC)

1. Adatok>kijelölése az SQL Server ODBC-ből. Ekkor megjelenik a Kapcsolódás SQL Server ODBC-adatforráshoz párbeszédpanel.

   

2. Adja meg a kiszolgálót a Kiszolgáló neve mezőben, és szükség esetén az Adatbázis neve mezőben.

   Kérje le ezt az információ az adatbázis adminisztrátortól.

3. Az Ellenőrzés területen válasszon egy módszert a listából: Felhasználónév/jelszó, Kerberos vagy NTLM.

4. Adja meg a hitelesítő adatokat a Felhasználónév és a Jelszó mezőben.

5. Válassza a Csatlakozás lehetőséget. Megjelenik a Kezelő párbeszédpanel.

6. A bal oldali panelen keresse meg a kívánt táblázatot, majd jelölje ki.

7. 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.

8. Az adatok előnézetének megtekintéséhez válassza a Futtatás lehetőséget.

9. Ha elkészült, válassza az Adatok visszaküldése lehetőséget. Megjelenik az Adatok importálása párbeszédpanel.

   

10. Válassza ki, hová szeretné felvenni az adatokat: a meglévő munkalapra, egy új munkalapra vagy egy kimutatásba.

11. A kapcsolat tulajdonságainak beállításához válassza a Tulajdonságok párbeszédpanel Használat és Definíció lapját. Az adatok importálása után választhatja azAdatkapcsolatok> lehetőséget is, majd a Kapcsolat tulajdonságai párbeszédpanelen válassza a Tulajdonságok gombot.

12. Kattintson az OK gombra.

13. A kapcsolat működésének biztosításához adjon meg néhány adatot, majd válasszaaz Összes frissítése> lehetőséget.

Csatlakozás másik adatbázishoz (Microsoft Query)

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:

1. Adatok>kijelölése adatbázisból (Microsoft Query).

2. Adja hozzá az adatbázis adatforrását, majd válassza az OK lehetőséget.

3. 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.

   

4. A bal oldalon válassza a kiszolgáló melletti nyilat az adatbázisok megtekintéséhez.

5. Válassza ki a nyilat a kívánt adatbázis mellett.

6. Válassza ki a kívánt táblázatot.

7. Az adatok előnézetének megtekintéséhez válassza a Futtatás lehetőséget.

8. Ha elkészült, válassza az Adatok visszaküldése lehetőséget.

9. 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.

10. Kattintson az OK gombra.

11. A kapcsolat működésének biztosításához adjon meg néhány adatot, majd válasszaaz Összes frissítése> lehetőséget.

Hitelesítő adatok törlése

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

1. Válassza azAdatkapcsolatok> lehetőséget. Megjelenik a Munkafüzet kapcsolatai párbeszédpanel.

2. Jelölje ki a kívánt kapcsolatot a listában, majd válassza az Engedélyek törlése lehetőséget.