Importieren und Strukturieren von Daten in Excel für Mac (Power Query)

Datenquellen importieren

Sie können mit Power Query-Daten aus einer Vielzahl von Datenquellen in Excel importieren: Excel-Arbeitsmappe, Text/CSV, XML, JSON, SQL Server-Datenbank, SharePoint Online-Liste, OData, leere Tabelle und leere Abfrage.

1. Wählen Sie Daten>abrufen aus.

   

2. Um die gewünschte Datenquelle auszuwählen, wählen Sie Daten abrufen (Power Query).

3. Wählen Sie im Dialogfeld Datenquelle auswählen eine der verfügbaren Datenquellen aus.

   

4. Mit der Datenquelle verbinden. Weitere Informationen zum Herstellen einer Verbindung mit den einzelnen Datenquellen finden Sie unter Importieren von Daten aus Datenquellen.

5. Wählen Sie die Daten, die Sie importieren möchten.

6. Laden Sie die Daten, indem Sie auf die Schaltfläche Laden klicken.

Ergebnis

Die importierten Daten werden in einem neuen Blatt angezeigt.

Nächste Schritte

Um Daten mithilfe des Power Query-Editor zu strukturieren und zu transformieren, wählen Sie Daten transformierenaus. Weitere Informationen finden Sie unter Gestalten von Daten mit Power Query-Editor.

Strukturieren von Daten mit Power Query-Editor

Prozedur

1. Wählen Sie Daten>abrufen (Power Query) aus.

2. Wählen Sie Power Query-Editor starten aus, um die Abfrage-Editor zu öffnen.

   

   Tipp

   Sie können auch auf die Abfrage-Editor zugreifen, indem Sie Daten abrufen (Power Query), eine Datenquelle auswählen und dann auf Weiterklicken.

3. Gestalten und transformieren Sie Ihre Daten mit dem Abfrage-Editor, wie Sie es von Excel für Windows gewohnt sind.

   

   Weitere Informationen finden Sie unter Power Query für Excel-Hilfe.

4. Wenn Sie fertig sind, wählen Sie Start>schließen & Laden aus.

Ergebnis

Die neu importierten Daten werden in einem neuen Blatt angezeigt.

Datenquellen aktualisieren

Sie können die folgenden Datenquellen aktualisieren: SharePoint-Dateien, SharePoint-Listen, SharePoint-Ordner, OData, Text-/CSV-Dateien, Excel-Arbeitsmappen (.xlsx), XML- und JSON-Dateien, lokale Tabellen und Bereiche, eine Microsoft SQL Server-Datenbank und Ordner.

Beim ersten Mal aktualisieren

Wenn Sie zum ersten Mal versuchen, dateibasierte Datenquellen in Ihren Arbeitsmappenabfragen zu aktualisieren, müssen Sie möglicherweise den Dateipfad aktualisieren.

1. Wählen Sie Daten, den Pfeil neben Daten abrufen, und dann Datenquelleneinstellungen. Das Dialogfeld Datenquelleneinstellungen wird angezeigt.
2. Wählen Sie eine Verbindung aus, und wählen Sie dann Dateipfad ändern.
3. Wählen Sie im Dialogfeld Dateipfad einen neuen Speicherort und dann Daten abrufen aus.
4. Wählen Sie Schließen aus.

Nachfolgende Zeiten aktualisieren

Zum Aktualisieren:

• Wählen Sie alle Datenquellen in der Arbeitsmappe Daten>aktualisieren aus.
• Eine bestimmte Datenquelle, klicken Sie mit der rechten Maustaste auf eine Abfragetabelle auf einem Blatt, und wählen Sie dann Aktualisieren.
• Wählen Sie eine PivotTable aus, wählen Sie eine Zelle in der PivotTable aus, und wählen Sie dann PivotTable Daten aktualisieren analysieren> aus.

Eingeben und Löschen von Anmeldeinformationen

Wenn Sie zum ersten Mal auf SharePoint, SQL Server, OData oder andere Datenquellen zugreifen, für die eine Berechtigung erforderlich ist, müssen Sie die entsprechenden Anmeldeinformationen angeben. Sie können auch die Anmeldedaten löschen, um neue einzugeben.

Eingeben von Anmeldeinformationen

Wenn Sie eine Abfrage zum ersten Mal aktualisieren, werden Sie möglicherweise aufgefordert, sich anzumelden. Wählen Sie die Authentifizierungsmethode aus, und geben Sie die Anmeldeinformationen an, um eine Verbindung mit der Datenquelle herzustellen und mit der Aktualisierung fortzufahren.

Wenn eine Anmeldung erforderlich ist, wird das Dialogfeld Anmeldeinformationen eingeben angezeigt.

Beispiel:

• SharePoint-Anmeldeinformationen:

  

• SQL Server Anmeldeinformationen:

  

Anmeldeinformationen löschen

1. Wählen Sie DataGet DataSource Settings (Einstellungen> für Datenquellen > abrufen) aus.
2. Wählen Sie im Dialogfeld Datenquelleneinstellungen die gewünschte Verbindung aus.
3. Wählen Sie unten Berechtigungen löschen aus.
4. Bestätigen Sie, dass Sie dies tun möchten, und wählen Sie dann Löschen.

Power Query VBA-Code verfassen und übertragen

Obwohl die Erstellung im Power Query-Editor in Excel für Mac nicht verfügbar ist, unterstützt VBA die Erstellung von Power Query. Das Übertragen eines VBA-Codemoduls in einer Datei von Excel für Windows nach Excel für Mac ist ein zweistufiger Prozess. Ein Beispielprogramm finden Sie am Ende dieses Abschnitts.

Schritt eins: Excel für Windows

1. Unter Excel Windows entwickeln Sie Abfragen mit VBA. VBA-Code, der die folgenden Entitäten im Excel-Objektmodell verwendet, funktioniert auch in Excel für Mac: Queries-Objekt, WorkbookQuery-Objekt, Workbook.Queries-Eigenschaft. Weitere Informationen finden Sie in der VBA-Referenz zu Excel.

2. Stellen Sie in Excel sicher, dass die Visual Basic-Editor geöffnet ist, indem Sie ALT+F11 drücken.

3. Klicken Sie mit der rechten Maustaste auf das Modul, und wählen Sie dann Datei exportieren aus. Das Dialogfeld Exportieren wird angezeigt.

4. Geben Sie einen Dateinamen ein, vergewissern Sie sich, dass die Dateierweiterung .bas lautet, und wählen Sie dann Speichern.

5. Laden Sie die VBA-Datei in einen Online-Dienst hoch, um die Datei vom Mac aus zugänglich zu machen.

   Sie können Microsoft OneDrive verwenden. Weitere Informationen finden Sie unter Synchronisieren von Dateien mit OneDrive auf Mac OS X.

Schritt 2: Excel für Mac

1. Laden Sie die VBA-Datei in eine lokale Datei herunter, die VBA-Datei, die Sie in "Schritt eins: Excel für Windows" gespeichert und auf einen Online-Dienst hochgeladen haben.
2. Wählen Sie Excel für Mac Extras>Makro-Visual>Basic-Editor aus. Das Visual Basic-Editor-Fenster wird geöffnet.
3. Klicken Sie mit der rechten Maustaste auf ein Objekt im Projektfenster und wählen Sie dann Datei importieren. Das Dialogfeld Datei importieren wird angezeigt.
4. Suchen Sie die VBA-Datei, und wählen Sie Öffnen.

Beispielcode

Hier ist ein einfacher Code, den Sie anpassen und verwenden können. Dies ist eine Beispielabfrage, die eine Liste mit Werten von 1 bis 100 erstellt.

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

Importieren und Aktualisieren von TXT- oder CSV-Dateien

1. Öffnen Sie die Excel-Arbeitsmappe.

2. Wenn eine Sicherheitswarnung angezeigt wird, dass externe Datenverbindungen deaktiviert werden, wählen Sie Inhalt aktivieren aus.

3. Wenn das Dialogfeld Dateizugriff gewähren angezeigt wird, wählen Sie Auswählen und dann Zugriff gewähren auf den obersten Ordner, der die Dateien der Datenquelle enthält.

4. Wählen Sie Daten>aus Text (Legacy) aus. Das Dialogfeld Finder wird angezeigt.

5. Suchen Sie die .txt- oder .csv-Datei, und wählen Sie Öffnen aus. Der Textimport-Assistent wird angezeigt.

   Tipp Überprüfen Sie wiederholt den Bereich Vorschau der ausgewählten Daten , um Ihre Auswahl zu bestätigen.

6. Gehen Sie auf der ersten Seite wie folgt vor:
   Dateityp Um den Typ der Textdatei auszuwählen, wählen Sie Durch Trennzeichen oder Feste Breite aus.

   Zeilennummer Wählen Sie in Import in Zeile eine Zeilennummer aus, um die erste Datenzeile anzugeben, die Sie importieren möchten.

   Zeichensatz Wählen Sie unter Dateiursprung den Zeichensatz aus, der in der Textdatei verwendet wird. In den meisten Fällen können Sie diese Einstellung auf die Standardeinstellung festlegen.

7. Gehen Sie auf der zweiten Seite wie folgt vor:

   Mit Trennzeichen

   Wenn Sie auf der ersten Seite Trennzeichen ausgewählt haben, aktivieren Sie unter Trennzeichen das Trennzeichen, oder verwenden Sie das Kontrollkästchen Andere, um ein nicht aufgeführtes Zeichen einzugeben.

   Wählen Sie Aufeinanderfolgende Trennzeichen als eins behandeln, wenn Ihre Daten ein Trennzeichen von mehreren Zeichen zwischen Datenfeldern enthalten oder wenn Ihre Daten mehrere benutzerdefinierte Trennzeichen enthalten.

   Wählen Sie unter Textbezeichner das Zeichen aus, das die Werte in Ihrer Textdatei umschließt, meist das Anführungszeichen (").
   Feste Breite

   Wenn Sie auf der ersten Seite Feste Breite ausgewählt haben, befolgen Sie die Anweisungen zum Erstellen, Löschen oder Verschieben einer Umbruchlinie im Feld Vorschau der ausgewählten Daten .

8. Gehen Sie auf der dritten Seite wie folgt vor:

   Wählen Sie für jede Spalte unter Vorschau der ausgewählten Daten diese aus, und ändern Sie sie dann bei Bedarf in ein anderes Spaltenformat. Sie können das Datumsformat weiter festlegen und Erweitert auswählen, um die Einstellungen für numerische Daten zu ändern. Sie können die Daten auch nach dem Import konvertieren.

   Wählen Sie Fertig stellen aus. Das Dialogfeld Daten importieren wird angezeigt.

9. Wählen Sie aus, wo die Daten hinzugefügt werden sollen: entweder auf dem vorhandenen Blatt, auf einem neuen Blatt oder in einer PivotTable.

10. Wählen Sie OK aus.
    Um sicherzustellen, dass die Verbindung funktioniert, geben Sie einige Daten ein, und wählen Sie dann Verbindungen>aktualisieren aus.

Herstellen einer Verbindung mit einer SQL-Datenbank (ODBC)

1. Wählen Sie Daten>aus SQL Server ODBC aus. Das Dialogfeld Mit SQL Server-ODBC-Datenquelle verbinden wird angezeigt.

   

2. Geben Sie den Server in das Feld Servername und optional die Datenbank in das Feld Datenbankname ein.

   Rufen Sie diese Informationen vom Datenbankadministrator ab.

3. Wählen Sie unter Authentifizierung eine Methode aus der Liste aus: Benutzername/Passwort, Kerberos oder NTLM.

4. Geben Sie anmeldeinformationen in die Felder Benutzername und Kennwort ein.

5. Wählen Sie Verbinden aus. Das Dialogfeld Navigator wird angezeigt.

6. Navigieren Sie im linken Bereich zu der gewünschten Tabelle, und wählen Sie sie aus.

7. Bestätigen Sie die SQL-Anweisung im rechten Bereich. Sie können die SQL-Anweisung nach Bedarf ändern.

8. Um eine Vorschau der Daten anzuzeigen, wählen Sie Ausführenaus.

9. Wenn Sie bereit sind, wählen Sie Rückgabedatenaus. Das Dialogfeld Daten importieren wird angezeigt.

   

10. Wählen Sie aus, wo die Daten hinzugefügt werden sollen: entweder auf dem vorhandenen Blatt, auf einem neuen Blatt oder in einer PivotTable.

11. Um Verbindungseigenschaften auf den Registerkarten Verwendung und Definition des Dialogfelds Eigenschaften festzulegen, wählen Sie Eigenschaften aus. Nachdem Sie die Daten importiert haben, können Sie auch Datenverbindungen> und dann im Dialogfeld Verbindungseigenschaftendie Option Eigenschaften auswählen.

12. Wählen Sie OK aus.

13. Um sicherzustellen, dass die Verbindung funktioniert, geben Sie einige Daten ein, und wählen Sie dann Daten>aktualisieren alle aus.

Herstellen einer Verbindung mit einer anderen Datenbank (Microsoft Query)

Wenn Sie eine externe Datenquelle verwenden möchten, bei der es sich nicht um eine SQL-Datenbank handelt, muss auf Ihrem Mac der geeignete ODBC-Treiber (Open Database Connectivity) installiert sein. Informationen zu Treibern finden Sie auf dieser Website. Führen Sie nach der Installation des Treibers für Ihre Datenquelle die folgenden Schritte aus:

1. Wählen Sie Daten>aus Datenbank (Microsoft Query) aus.

2. Fügen Sie die Datenquelle für Ihre Datenbank hinzu, und wählen Sie dann OK.

3. Geben Sie an der Eingabeaufforderung für die SQL Server-Anmeldeinformationen die Authentifizierungsmethode, den Benutzernamen und das Kennwort ein.

   

4. Wählen Sie auf der linken Seite den Pfeil neben dem Server aus, um die Datenbanken anzuzeigen.

5. Wählen Sie den Pfeil neben der gewünschten Datenbank aus.

6. Wählen Sie die gewünschte Tabelle aus.

7. Um eine Vorschau der Daten anzuzeigen, wählen Sie Ausführen aus.

8. Wenn Sie bereit sind, wählen Sie Rückgabedatenaus.

9. Wählen Sie im Dialogfeld Daten importieren aus, wo sich die Daten befinden sollen: entweder auf dem vorhandenen Blatt, auf einem neuen Blatt oder in einer PivotTable.

10. Wählen Sie OK aus.

11. Um sicherzustellen, dass die Verbindung funktioniert, geben Sie einige Daten ein, und wählen Sie dann Daten>aktualisieren alle aus.

Löschen Ihrer Anmeldeinformationen

Wenn Ihre Berechtigungen nicht funktionieren, müssen Sie sie möglicherweise nicht zuerst löschen und sich dann anmelden.

1. Wählen Sie Datenverbindungen>aus. Das Dialogfeld Arbeitsmappenverbindungen wird angezeigt.

2. Wählen Sie die gewünschte Verbindung in der Liste aus, und wählen Sie dann Berechtigungen löschen.