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

Excel für den Mac enthält die Power Query-Technologie (auch Get & Transform genannt), um mehr Möglichkeiten beim Importieren, Aktualisieren und Authentifizieren von Datenquellen, beim Verwalten von Power Query-Datenquellen, beim Löschen von Anmeldeinformationen, beim Ändern des Speicherorts von dateibasierten Datenquellen und beim Gestalten der Daten in einer Tabelle zu bieten, die Ihren Anforderungen entspricht. Sie können auch eine Power Query Abfrage mithilfe von VBA erstellen.

Microsoft 365 (Vorgängerversion)



Hinweis: Die Datenquelle SQL Server Database kann nur in Insiders Beta importiert werden.

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.

Wählen Sie Daten > Daten abrufen.
Um die gewünschte Datenquelle auszuwählen, wählen Sie Daten abrufen (Power Query).
Wählen Sie im Dialogfeld Datenquelle auswählen eine der verfügbaren Datenquellen aus.
Mit der Datenquelle verbinden. Weitere Informationen über die Verbindung zu den einzelnen Datenquellen finden Sie unter Importieren von Daten aus Datenquellen.
Wählen Sie die Daten, die Sie importieren möchten.
Laden Sie die Daten, indem Sie auf die Schaltfläche Laden klicken.

Result

Die importierten Daten werden in einem neuen Blatt angezeigt.

Typische Ergebnisse für eine Abfrage

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.

Hinweis: Diese Funktion ist generell für Microsoft 365-Abonnenten verfügbar, die Excel für Mac in der Version 16.69 (23010700) oder höher ausführen. Wenn Sie Microsoft 365-Abonnent sind, vergewissern Sie sich, dass Sie über die neueste Office-Version verfügen

Vorgehensweise

Wählen Sie Daten > Daten abrufen (Power Query).
Um den Abfrage-Editor zu öffnen, wählen Sie Power Query Editor starten.

Tipp: Sie können auch auf die Abfrage-Editor zugreifen, indem Sie Daten abrufen (Power Query), eine Datenquelle auswählen und dann auf Weiterklicken.
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.
Wenn Sie fertig sind, wählen Sie Home > Schließen & Laden.

Result

Die neu importierten Daten werden in einem neuen Blatt angezeigt.

Typische Ergebnisse für eine Abfrage

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 sowie eine Microsoft SQL Server-Datenbank.

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.

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

Nachfolgende Zeiten aktualisieren

Zum Aktualisieren:

Alle Datenquellen in der Arbeitsmappe, wählen Sie Daten > Alle aktualisieren.
Eine bestimmte Datenquelle, klicken Sie mit der rechten Maustaste auf eine Abfragetabelle auf einem Blatt, und wählen Sie dann Aktualisieren.
Eine PivotTable, markieren Sie eine Zelle in der PivotTable, und wählen Sie dann PivotTable-Analyse > Daten aktualisieren.

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

Wählen Sie Daten > Daten abrufen > Datenquelleneinstellungen.
Wählen Sie im Dialogfeld Datenquelleneinstellungen die gewünschte Verbindung aus.
Wählen Sie im unteren Bereich die Option Berechtigungen löschen.
Bestätigen Sie, dass Sie dies tun möchten, und wählen Sie dann Löschen.

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

Unter Excel Windows entwickeln Sie Abfragen mit VBA. VBA-Code, der die folgenden Entitäten im Objektmodell von Excel verwendet, funktioniert auch in Excel für Mac: Queries-Objekt, WorkbookQuery-Objekt, Workbook.Queries-Eigenschaft. Weitere Informationen finden Sie unter Excel VBA-Referenz.
Stellen Sie in Excel sicher, dass die Visual Basic-Editor geöffnet ist, indem Sie ALT+F11 drücken.
Klicken Sie mit der rechten Maustaste auf das Modul, und wählen Sie dann Datei exportieren aus. Das Dialogfeld Exportieren wird angezeigt.
Geben Sie einen Dateinamen ein, vergewissern Sie sich, dass die Dateierweiterung .bas lautet, und wählen Sie dann Speichern.
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

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.
Wählen Sie in Excel für Mac Tools > Makro > Visual Basic-Editor aus. Das Visual Basic-Editor-Fenster wird geöffnet.
Klicken Sie mit der rechten Maustaste auf ein Objekt im Projektfenster und wählen Sie dann Datei importieren. Das Dialogfeld Datei importieren wird angezeigt.
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

Öffnen Sie die Excel-Arbeitsmappe.
Wenn Sie eine Sicherheitswarnung erhalten, dass externe Datenverbindungen deaktiviert sind, wählen Sie Inhalt aktivieren.
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.
Wählen Sie Daten > Aus Text (Legacy). Das Dialogfenster Finder erscheint.
Suchen Sie die .txt- oder .csv-Datei, und wählen Sie Öffnen. Der Textimport-Assistent wird angezeigt.

Tipp Überprüfen Sie wiederholt den Bereich Vorschau der ausgewählten Daten, um Ihre Auswahl zu bestätigen.
Gehen Sie auf der ersten Seite wie folgt vor:

Dateityp    Um den Typ der Textdatei auszuwählen, wählen Sie mit Trennzeichen oder Feste Breite.

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 Datei-Ursprung den Zeichensatz, der in der Textdatei verwendet wird. In den meisten Fällen können Sie diese Einstellung auf die Standardeinstellung festlegen.
Gehen Sie auf der zweiten Seite wie folgt vor:

Durch Trennzeichen getrennte

Wenn Sie auf der ersten Seite mit Trennzeichen ausgewählt haben, wählen Sie unter Trennzeichendas Trennzeichen aus, 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 gewählt haben, befolgen Sie die Anweisungen zum Erstellen, Löschen oder Verschieben einer Umbruchlinie im Feld Vorschau der ausgewählten Daten .
Auf der dritten Seite gehen Sie wie folgt vor:

Wählen Sie jede Spalte unter Vorschau der ausgewählten Daten aus, und ändern Sie dann das Spaltenformat, wenn Sie dies wünschen. Sie können außerdem das Datumsformat einstellen 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 Fertigstellen aus. Das Dialogfeld Daten importieren wird angezeigt.
Wählen Sie aus, wo die Daten hinzugefügt werden sollen: entweder auf dem vorhandenen Blatt, auf einem neuen Blatt oder in einer PivotTable.
Wählen Sie OK aus.

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

Wählen Sie Daten > Von SQL Server ODBC. Das Dialogfeld Mit SQL Server-ODBC-Datenquelle verbinden wird angezeigt.
Geben Sie den Server in das Feld Servername ein, und geben Sie optional die Datenbank in das Feld Datenbankname ein.

Rufen Sie diese Informationen vom Datenbankadministrator ab.
Wählen Sie unter Authentifizierung eine Methode aus der Liste aus: Benutzername/Passwort, Kerberos oder NTLM.
Geben Sie die Anmeldeinformationen in die Felder Benutzername und Kennwort ein.
Wählen Sie Verbinden aus. Das Dialogfeld Navigator wird angezeigt.
Navigieren Sie im linken Bereich zu der gewünschten Tabelle, und wählen Sie sie aus.
Bestätigen Sie die SQL-Anweisung im rechten Bereich. Sie können die SQL-Anweisung nach Bedarf ändern.
Um eine Vorschau der Daten anzuzeigen, wählen Sie Ausführenaus.
Wenn Sie bereit sind, wählen Sie Rückgabedatenaus. Das Dialogfeld Daten importieren wird angezeigt.
Wählen Sie aus, wo die Daten hinzugefügt werden sollen: entweder auf dem vorhandenen Blatt, auf einem neuen Blatt oder in einer PivotTable.
Um die 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 Daten > Verbindungen auswählen und dann im Dialogfeld Verbindungseigenschaften die Option Eigenschaften auswählen.
Wählen Sie OK aus.
Um sicherzustellen, dass die Verbindung funktioniert, geben Sie einige Daten ein, und wählen Sie dann Daten > Alle aktualisieren aus.

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:

Wählen Sie Daten > Aus Datenbank (Microsoft Query).
Fügen Sie die Datenquelle für Ihre Datenbank hinzu, und wählen Sie dann OK.
Geben Sie an der Eingabeaufforderung für die SQL Server-Anmeldeinformationen die Authentifizierungsmethode, den Benutzernamen und das Kennwort ein.
Wählen Sie auf der linken Seite den Pfeil neben dem Server aus, um die Datenbanken anzuzeigen.
Wählen Sie den Pfeil neben der gewünschten Datenbank aus.
Wählen Sie die gewünschte Tabelle aus.
Um eine Vorschau der Daten anzuzeigen, wählen Sie Ausführen aus.
Wenn Sie bereit sind, wählen Sie Rückgabedatenaus.
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.
Wählen Sie OK aus.
Um sicherzustellen, dass die Verbindung funktioniert, geben Sie einige Daten ein, und wählen Sie dann Daten > Alle aktualisieren aus.

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

Wählen Sie Daten > Connections aus. Das Dialogfeld Arbeitsmappenverbindungen wird angezeigt.
Wählen Sie die gewünschte Verbindung in der Liste aus, und wählen Sie dann Berechtigungen löschen.