Seit einiger Zeit können Sie in Excel mit den verknüpften Datentypen Aktien oder Geografie aktuelle Informationen aus dem Internet über Formeln direkt in ein Excel-Tabellenblatt laden oder über ein Karte anzeigen lassen. Mit Hilfe von Power Query können Sie Ihre eigenen Datentypen erzeugen und als Tabelle in ein Excel-Tabellenblatt laden. In diesem Tipp erfahren Sie, wie Sie eigene Datentypen erstellen und in Excel verwenden. Auf diese Weise wird Ihr Tabellenblatt übersichtlicher und einige Abfragen einfacher.

Beispiel

Für die Erzeugung eigener Datentypen wird Power Query benötigt.

In unserem Beispiel werden in Power Query Daten aus einer Access-Datenbank geladen und umgewandelt. Im letzten Schritt der Abfrage wird ein eigener Datentyp erzeugt. Die Daten dieser Abfrage werden in ein Excel-Tabellenblatt geladen.

Ausgangsdaten

Die Daten in der Access-Datenbank sehen folgendermaßen aus:

Daten in Access-Datenbank

Laden der Daten in Power Query

Um die Daten aus der Access-Datenbank in Power Query zu laden, gehen Sie bitte folgendermaßen vor:

1. Klicken in Excel auf der Registerkarte Daten auf die Schaltfläche Daten abrufen.
2. Klicken Sie auf Aus Datenbank und dann auf Aus Microsoft Access-Datenbank.
3. Wählen Sie im Explorer die Access-Datenbank aus.
4. Markieren Sie im sich öffnenden Navigator die Tabelle mit den Daten. In unserem Beispiel ist es die Tabelle tblKunde.
   

   Navigator in Power Query

5. Klicken Sie auf die Schaltfläche Daten transformieren.
6. Die Daten werden in Power Query geladen. Im nächsten Schritt entfernen Sie die Spalten KdID und KdName2. Sie werden für die Datentypen nicht benötigt.
7. Im folgenden Schritt benennen Sie alle verbliebenen Spalten so um, wie sie später bei dem Aufruf des Datentyps in Excel erscheinen sollen. Das Präfix Kd wird u. a. bei allen Spalten bis auf die Spalte Kd-Nr.
8. In einem weiteren Schritt ändern Sie den Datentyp der Spalte Erstauftrag auf Datum ab.
9. Im nächsten Schritt wird der neue Datentyp erzeugt. Markieren Sie alle Spalten der Abfrage in der Reihenfolge, wie Sie sie in den Datentyp aufnehmen möchten. Die erste zu markierende Spalte ist die sogenannte Anzeigespalte. Sie sollte sorgfältig gewählt werden. Am besten nehmen Sie eine Spalte mit eindeutigen und gleichzeitig sprechenden Werten. In unserem Beispiel sind die Werte in den Spalten Kd-Nr. und Suchbegriff jeweils eindeutig. Der Suchbegriff ist als Anzeigespalte besser geeignet, da er sprechender ist als die Kundennummer. Markieren Sie daher zunächst die Spalte Suchbegriff und danach alle anderen Spalten. Halten Sie dabei die STRG-Taste gedrückt.
10. Klicken Sie in Power Query auf der Registerkarte Transformieren in der Gruppe Strukturierte Spalte auf die Schaltfläche Datentyp erstellen. Das folgende Dialogbild erscheint:
    

    Neuer Datentyp in Power Query

11. Wählen Sie alle Datentypname den Namen Kunden. Der Eintrag Suchbegriff in dem Eingabefeld Spalte anzeigen brauchen Sie nicht mehr ändern. Klicken Sie auf die Schaltfläche Danach sieht die Abfrage in Power Query so aus:
    

    Abfrage in Power Query mit Datentyp

    Es werden nur noch die Daten aus der gewählten Anzeigespalte Suchbegriff dargestellt. Die Spaltenüberschrift Kunden entspricht dem Namen des Datentyps. Das Symbol links neben der Überschrift Kunden ist das Symbol für einen Datentyp.

12. Die Abfrage in Power Query ist nun fertig. Klicken Sie abschließend auf der Registerkarte Daten auf die Schaltfläche Schließen & Laden. Die Daten werden in ein neues Tabellenblatt übertragen.

Das neu erzeugte Tabellenblatt sieht so aus:

Tabelle mit Excel eigenem Datentyp

Datentypen in Excel verwenden

Karten anzeigen

In dem Excel-Tabellenblatt sehen Sie jetzt in der Spalte Kunden das Symbol für einen Datentyp, wie Sie es bereits von Datentypen Aktien und Geografie kennen.

Wenn Sie auf das Datentyp-Symbol einer Zelle klicken, erscheint eine Karte mit sämtlichen Informationen zum ausgewählten Kunden:

Karte eines Excel-Datentyps

Tabelle erweitern

Sie können diese einspaltige Tabelle um weitere Spalten ergänzen. Es sind die Spalten, die Sie vorher in Power Query vor dem Erzeugen des Datentyps markiert haben. Aktivieren Sie dazu eine beliebige Zelle in der Spalte Kunden.

Spalte aus Excel-Datentyp abrufen

Klicken Sie auf die Schaltfläche rechts neben der Überschrift Kunden.

Folgendes Bild wird eingeblendet.

Auswahl einer Spalte aus einem Excel-Datentyp

Wählen Sie nacheinander die Spalten Ort, Erstauftrag und Umsatz Vorjahr aus. Die Tabelle hat danach folgendes Aussehen.

Excel-Tabelle mit mehreren Spalten eines Datentyps

Die Formel, die in allen Zellen der Spalte Ort steht, sieht so aus:

=[@Kunden].Ort

Die Formel in der Spalte Umsatz Vorjahr sieht folgendermaßen aus:

=[@Kunden].[Umsatz Vorjahr]

Wert mit XVERWEIS aus einer Tabelle mit Datentypen abrufen.

Sie können einen beliebigen Wert aus der Tabelle mit dem Datentyp Kunden in eine beliebige Zelle übernehmen, wenn Sie den Suchbegriff für den Kunden verwenden. Besonders geeignet für die Art von Abfragen ist die Funktion XVERWEIS.

In unserem Beispiel möchten wir den Umsatz des Vorjahres für den Kunden Lehmann ermitteln. Wenn der Suchbegriff Lehmann in Zelle A2 steht, lautet die Formel:

=XVERWEIS(A2;WERTZUTEXT(tblKunde[Kunden]);tblKunde[Kunden].[Umsatz Vorjahr])

Der erste Parameter bezeichnet den Suchbegriff, der in Zelle A2 steht.

Der zweite Parameter WERTZUTEXT(tblKunde[Kunden]) bezieht sich auf die Suchmatrix. Die Suchmatrix ist der Datentyp Kunden in der dynamischen Tabelle mit dem Namen tblKunde. Für die Suche nach gewünschten Kunden muss der Datentyp vorher in einen Text umgewandelt werden, um über den Anzeigenamen des Datentyps den gesuchten Kunden zu finden. Dazu wird die Funktion WERTZUTEXT eingesetzt. Verwenden Sie die die Umwandlungsfunktion WERTZUTEXT nicht, erscheint der Fehlerwert #NV in der Zelle.

Der dritte, notwendige Parameter bezeichnet die Rückgabematrix. Sie lautet in dem Beispiel: tblKunde[Kunden].[Umsatz Vorjahr]. Gesucht wird der Umsatz des Vorjahres für den gewählten Kunden in der Tabelle tblKunde über den Datentyp Kunden.

Mit der folgenden Formel können Sie in einer Zelle B2 einen Datentyp erzeugen und auf Basis dieser Zelle weitere Informationen einblenden:

=XVERWEIS(A2;WERTZUTEXT(tblKunde[Kunden]);tblKunde[Kunden])

Excel-Datentyp über Formel abrufen

Der dritte Parameter in der obigen Formel tblKunde[Kunden] erzeugt einen Datentyp in Zelle B2.

Bei der Erstellung einer Formel in Zelle C2 mit Bezug zum Datentyp in Zelle B2 werden Ihnen wieder die zur Auswahl stehenden Felder angezeigt.

Hinweise

• Für die Erzeugung von Datentypen können Sie in Power Query neben Access-Datenbanken alle anderen verfügbaren Konnektoren einsetzen wie z. B. Webabfragen oder andere Datenbanken. Vor dem Erzeugen des Datentyps können Sie alle notwendigen Transformationen und Filterungen vornehmen.

Wenn Sie aufbauend auf der von Power Query erzeugten Tabelle eine Auswertung mit einer PivotTable vornehmen möchten, müssen Sie vorher alle benötigten Spalten über Formeln in der Tabelle einblenden.