Gegevens importeren en vormgeven in Excel voor Mac (Power Query)

Gegevensbronnen importeren

U kunt gegevens importeren in Excel met behulp van Power Query uit een groot aantal gegevensbronnen: Excel werkmap, Text/CSV, XML, JSON, SQL Server Database, SharePoint Online Lijst, OData, Lege tabel en Lege query.

1. Selecteer Gegevens>ophalen.

   

2. Als u de gewenste gegevensbron wilt selecteren, selecteert u Gegevens ophalen (Power Query).

3. Selecteer in het dialoogvenster Gegevensbron kiezen een van de beschikbare gegevensbronnen.

   

4. Maak verbinding met de gegevensbron. Zie Gegevens importeren uit gegevensbronnen voor meer informatie over het maken van verbinding met elke gegevensbron.

5. Kies de gegevens die u wilt importeren.

6. Laad de gegevens door op de knop Laden te klikken.

Resultaat

De geïmporteerde gegevens worden weergegeven in een nieuw blad.

Volgende stappen

Als u gegevens wilt vormgeven en transformeren met behulp van de Power Query-editor, selecteert u Gegevens transformeren. Zie Shapegegevens met Power Query-editorvoor meer informatie.

Gegevens vormgeven met Power Query-editor-

Procedure

1. Selecteer Gegevens>ophalen (Power Query).

2. Als u de Power Query-editor wilt openen, selecteert u Power Query-editor starten.

   

   Tip

   U kunt de Query-editor ook openen door Gegevens ophalen (Power Query) te selecteren en vervolgens op Volgendete klikken.

3. Vorm en transformeer uw gegevens met behulp van de Query-editor zoals u zou doen in Excel voor Windows.

   

   Zie Power Query voor Excel Help voor meer informatie.

4. Wanneer u klaar bent, selecteert u Start>Sluiten & Laden.

Resultaat

De zojuist geïmporteerde gegevens worden weergegeven in een nieuw blad.

Gegevensbronnen vernieuwen

U kunt de volgende gegevensbronnen vernieuwen: SharePoint-bestanden, SharePoint-lijsten, SharePoint-mappen, OData, tekst-/CSV-bestanden, Excel-werkmappen (.xlsx), XML- en JSON-bestanden, lokale tabellen en bereiken, een Microsoft SQL Server-database en mappen.

De eerste keer vernieuwen

De eerste keer dat u gegevensbronnen op basis van bestanden in uw werkmapquery's probeert te vernieuwen, moet u mogelijk het bestandspad bijwerken.

1. Selecteer Gegevens, de pijl naast Gegevens ophalen, en vervolgens Gegevensbroninstellingen. Het dialoogvenster Gegevensbroninstellingen wordt weergegeven.
2. Selecteer een verbinding en selecteer vervolgens Bestandspad wijzigen.
3. Selecteer in het dialoogvenster Bestandspad een nieuwe locatie en selecteer vervolgens Gegevens ophalen.
4. Selecteer Sluiten.

Volgende keren vernieuwen

Vernieuwen:

• Alle gegevensbronnen in de werkmap selecteert u Alles>vernieuwen.
• Een specifieke gegevensbron, klik met de rechtermuisknop op een querytabel op een blad en selecteer vervolgens Vernieuwen.
• Een draaitabel, selecteer een cel in de draaitabel en selecteer vervolgens Draaitabel Analyseren>Vernieuwingsgegevens.

Referenties invoeren en wissen

De eerste keer dat u toegang krijgt tot SharePoint, SQL Server, OData of andere gegevensbronnen waarvoor toestemming is vereist, moet u de juiste referenties opgeven. U kunt ook de referenties wissen om nieuwe referenties in te voeren.

Referenties invoeren

Wanneer u een query voor de eerste keer vernieuwt, wordt u mogelijk gevraagd om u aan te melden. Selecteer de verificatiemethode en geef de aanmeldingsreferenties op om verbinding te maken met de gegevensbron en ga door met het vernieuwen.

Als aanmelding is vereist, wordt het dialoogvenster Referenties invoeren weergegeven.

Bijvoorbeeld:

• SharePoint-referenties:

  

• SQL Server referenties:

  

Referenties wissen

1. SelecteerInstellingen voor gegevensbron>ophalen>.
2. In het dialoogvenster Gegevensbroninstelling selecteert u de verbinding die u wilt.
3. Selecteer onderaan Machtigingen wissen.
4. Bevestig dat u dit wilt doen en selecteer vervolgens Verwijderen.

VBA-code schrijven en overdragen Power Query

Hoewel creatie in de Power Query-editor niet beschikbaar is in Excel voor Mac, biedt VBA wel ondersteuning voor Power Query ontwerpen. Het overdragen van een VBA-codemodule in een bestand van Excel voor Windows naar Excel voor Mac is een proces in twee stappen. Aan het einde van deze sectie vindt u een voorbeeldprogramma.

Stap één: Excel voor Windows

1. In Excel Windows kunt u query's ontwikkelen met behulp van VBA. VBA-code die gebruikmaakt van de volgende entiteiten in het objectmodel van Excel, werkt ook in Excel voor Mac: Query's-object, WerkmapQuery-object, Eigenschap Workbook.Querys. Zie Excel VBA-verwijzing voor meer informatie.

2. Controleer in Excel of de Visual Basic Editor is geopend door op ALT+F11 te drukken.

3. Klik met de rechtermuisknop op de module en selecteer vervolgens Bestand exporteren. Het dialoogvenster Exporteren wordt weergegeven.

4. Voer een bestandsnaam in, controleer of de bestandsextensie .bas is en selecteer vervolgens Opslaan.

5. Upload het VBA-bestand naar een onlineservice om het bestand toegankelijk te maken vanaf de Mac.

   U kunt Microsoft OneDrive gebruiken. Zie Bestanden synchroniseren met OneDrive op Mac OS X voor meer informatie.

Stap twee: Excel voor Mac

1. Download het VBA-bestand naar een lokaal bestand, het VBA-bestand dat u hebt opgeslagen in Stap één: Excel voor Windows en geüpload naar een onlineservice.
2. Selecteer in Excel voor Mac Extra>Macro>Visual Basic Editor. Het Visual Basic Editor venster wordt weergegeven.
3. Klik met de rechtermuisknop op een object in het projectvenster en selecteer vervolgens Bestand importeren. Het dialoogvenster Bestand importeren wordt weergegeven.
4. Zoek het VBA-bestand en selecteer vervolgens Openen.

Voorbeeldcode

Hier volgt basiscode die u kunt aanpassen en gebruiken. Dit is een voorbeeldquery waarmee een lijst met waarden van 1 tot 100 wordt gemaakt.

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- of CSV-bestanden importeren en vernieuwen

1. Open de Excel-werkmap.

2. Als u een beveiligingswaarschuwing krijgt over het uitschakelen van externe gegevensverbindingen, selecteert u Inhoud inschakelen.

3. Als het dialoogvenster Bestandstoegang verlenen wordt weergegeven, selecteert u Selecteeren selecteert u vervolgens Toegang verlenen aan de map op het hoogste niveau met de gegevensbronbestanden.

4. Selecteer Gegevens>uit tekst (verouderd). Het dialoogvenster Finder wordt weergegeven.

5. Zoek het .txt- of .csv-bestand en selecteer Openen. De wizard Tekst importeren wordt weergegeven.

   Tip Controleer herhaaldelijk het deelvenster Voorbeeld van geselecteerde gegevens om uw keuzes te bevestigen.

6. Ga op de eerste pagina als volgt te werk:
   Bestandstype Als u het type tekstbestand wilt kiezen, selecteert u Gescheiden of Vaste breedte.

   Rijnummer In Beginnen met importeren bij rij, selecteert u een rijnummer om de eerste rij met gegevens op te geven die u wilt importeren.

   Tekenset Selecteer in Oorsprong van bestand de tekenset die in het tekstbestand wordt gebruikt. In de meeste gevallen kunt u deze instelling op de standaardinstelling laten staan.

7. Ga op de tweede pagina als volgt te werk:

   Met scheidingsteken

   Als u op de eerste pagina gescheiden hebt gekozen , schakelt u onder Scheidingstekens het scheidingsteken in of gebruikt u het selectievakje Overige om een niet-weergegeven scheidingsteken in te voeren.

   Selecteer Opeenvolgende scheidingstekens behandelen als één als uw gegevens een scheidingsteken van meer dan één teken tussen gegevensvelden bevatten of als uw gegevens meerdere aangepaste scheidingstekens bevatten.

   Selecteer in Tekstkwalificatie het teken dat waarden in het tekstbestand omsluit. Dit is meestal het aanhalingsteken (").
   Vaste breedte

   Als u Vaste breedte hebt gekozen op de eerste pagina, volgt u de instructies voor het maken, verwijderen of verplaatsen van een onderbrekingslijn in het vak Voorbeeld van geselecteerde gegevens .

8. Ga op de derde pagina als volgt te werk:

   Voor elke kolom onder Voorbeeld van geselecteerde gegevens selecteert u deze en wijzigt u deze desgewenst in een andere kolomindeling. U kunt de datumnotatie verder instellen en Geavanceerd selecteren om de instellingen voor numerieke gegevens te wijzigen. U kunt de gegevens ook converteren nadat u deze hebt geïmporteerd.

   Selecteer Finish. Het dialoogvenster Gegevens importeren wordt weergegeven.

9. Kies waar u de gegevens wilt toevoegen: op het bestaande blad, op een nieuw blad of in een draaitabel.

10. Selecteer OK.
    Als u wilt controleren of de verbinding werkt, voert u enkele gegevens in en selecteert u Vervolgens Verbindingen>vernieuwen.

Verbinding maken met een SQL Database (ODBC)

1. Selecteer Gegevens>uit SQL Server ODBC. Het dialoogvenster Verbinding maken ODBC-gegevensbron SQL Server wordt weergegeven.

   

2. Voer de server in het vak Servernaam in en voer eventueel de database in het vak Databasenaam in.

   Haal deze informatie op bij de databasebeheerder.

3. Selecteer onder Verificatieeen methode uit de lijst: Gebruikersnaam/Wachtwoord, Kerberos, of NTLM.

4. Voer referenties in de vakken Gebruikersnaam en Wachtwoord in.

5. Selecteer Verbinding maken. Het dialoogvenster Navigator wordt weergegeven.

6. Navigeer in het linkerdeelvenster naar de gewenste tabel en selecteer deze.

7. Bevestig de SQL-instructie in het rechterdeelvenster. U kunt de SQL-instructie naar wens wijzigen.

8. Als u een voorbeeld van de gegevens wilt bekijken, selecteert u Uitvoeren.

9. Wanneer u klaar bent, selecteert u Gegevens teruggeven. Het dialoogvenster Gegevens importeren wordt weergegeven.

   

10. Kies waar u de gegevens wilt toevoegen: op het bestaande blad, op een nieuw blad of in een draaitabel.

11. Als u verbindingseigenschappen wilt instellen op de tabbladen Gebruik en definitie van het dialoogvenster Eigenschappen , selecteert u Eigenschappen. Nadat u de gegevens hebt geïmporteerd, kunt u ook Gegevensverbindingen> selecteren en vervolgens in het dialoogvenster Verbindingseigenschappende optie Eigenschappen selecteren.

12. Selecteer OK.

13. Als u wilt controleren of de verbinding werkt, voert u enkele gegevens in en selecteert u vervolgens Alles vernieuwen>.

Verbinding maken met een andere database (Microsoft Query)

Als u een externe bron wilt gebruiken die niet een SQL Database (bijvoorbeeld FileMaker Pro), kunt u een ODBC-stuurprogramma (Open Database Connectivity) gebruiken dat op uw Mac is geïnstalleerd. Op deze webpagina vindt u informatie over stuurprogramma's. Zodra het stuurprogramma voor uw gegevensbron is geïnstalleerd, volgt u deze stappen:

1. Selecteer Gegevens>uit database (Microsoft Query).

2. Voeg de gegevensbron voor uw database toe en selecteer vervolgensOK.

3. Voer bij de SQL Server referentiesprompt de verificatiemethode, de gebruikersnaam en het wachtwoord in.

   

4. Selecteer aan de linkerkant de pijl naast de server om de databases weer te geven.

5. Selecteer de pijl naast de gewenste database.

6. Selecteer de gewenste tabel.

7. Als u een voorbeeld van de gegevens wilt bekijken, selecteert u Uitvoeren.

8. Wanneer u klaar bent, selecteert u Gegevens teruggeven.

9. Kies in het dialoogvenster Gegevens importeren waar u de gegevens wilt bevinden: op het bestaande blad, op een nieuw blad of in een draaitabel.

10. Selecteer OK.

11. Als u wilt controleren of de verbinding werkt, voert u enkele gegevens in en selecteert u vervolgens Alles vernieuwen>.

Uw aanmeldingsreferenties wissen

Als uw machtigingen niet werken, kunt u deze mogelijk niet eerst wissen en u vervolgens aanmelden.

1. Selecteer Gegevensverbindingen>. Het dialoogvenster Werkmapverbindingen wordt weergegeven.

2. Selecteer de gewenste verbinding in de lijst en selecteer vervolgens Machtigingen wissen.