Um die Daten in einer Excel-Tabelle schnell und flexibel mit Hilfe von PivotTables und PivotCharts analysieren zu können, ist der Aufbau der Tabelle und die Anordnung der Spalten von entscheidender Bedeutung. Falls der Tabellenaufbau für Analysen nicht gut geeignet sein sollte, können Sie in vielen Fällen mit dem Add-In Power Query für Excel 2010 und Excel 2013 die Tabelle für die Analyse passend umstellen, ohne dass Sie zahlreiche Formeln eingeben oder sogar VBA-Makros erstellen müssen.
In unserem Beispiel soll eine Tabelle mit den geplanten monatlichen Absatzmengen für 3 Artikel so umgestellt werden, dass die Daten problemlos mit einer PivotTable analysiert werden können. Die Tabelle sieht folgendermaßen aus:
Die flexible Analyse der Daten mit Hilfe von PivotTables wird dadurch erschwert, dass die Planmengen für die einzelnen Monate sich in verschiedenen Spalten befinden. Da das Transponieren der Zeilen und Spalten in diesem Fall nicht weiterhilft, soll mit Hilfe des Add-Ins Power Query die Tabelle umgestellt werden.
Falls das Add-In Power Query auf Ihrem Rechner installiert und aktiviert ist, finden Sie im Menüband von Excel die Registerkarte Power Query.
Gehen Sie folgendermaßen vor, um die Tabelle umzustellen:
- Aktivieren Sie eine Zelle in der Tabelle mit den geplanten Absatzmengen.
-
Klicken Sie im Menüband auf der Registerkarte Power Query in der Gruppe Excel-Daten auf die Schaltfläche Von Tabelle. Es erscheint das folgende Dialogbild.
-
Aktivieren Sie das Kontrollkästchen Meine Tabelle hat Überschriften und klicken Sie anschließend auf die Schaltfläche OK. In Power Query erscheint das folgende Dialogbild
- Markieren Sie die Spalte Artikel, in dem Sie mit der Maus in den Spaltenkopf klicken.
-
Klicken Sie auf im Menüband auf der Registerkarte Transformieren in der Gruppe Beliebige Spalte auf die Schaltfläche Andere Spalten entpivotieren, d.h. es sollen alle Spalten bis auf die Spalte Artikel entpivotiert werden. Im Abfrageeditor sieht die Tabelle danach folgendermaßen aus (Ausschnitt):
Beim Entpivotieren werden die betroffenen Spalten Periode 1 bis Periode 12 gelöscht. Es werden zwei neue Spalten mit den Namen Attribut und Wert erzeugt. In unserem Fall werden die neuen Spalten hinter der verbleibenden Spalte Artikel eingefügt. Für die Planmengen der 3 Artikel in den 12 Perioden werden Attribut-Wert-Paare gebildet. Dadurch hat die Tabelle nach dem Entpivotieren statt 3 Zeilen nun 36 Zeilen (3*12=36).
- Klicken Sie doppelt in den Spaltenkopf der Spalte Attribut und benennen Sie die Spalte in Periode um.
-
Im nächsten Schritt soll der Ausdruck Periode aus den Zeilen der Spalte Periode entfernt werden, damit nur die die Zahlen übrig bleiben. Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf die Schaltfläche Werte ersetzen. Das folgende Dialogbild erscheint:
- Geben Sie im Eingabefeld Zu suchender Wert den Ausdruck Periode ein und klicken Sie dann auf die Schaltfläche OK. Das Eingabefeld Ersetzen durch bleibt leer, weil in jeder Zeile der Spalte nur die Zahl erhalten bleiben soll.
-
Im nächsten Schritt soll aus den Werten in der Spalte Periode Datumswerte erzeugt werden. Markieren Sie die Spalte Periode und klicken Sie im Menüband auf der Registerkarte Spalte hinzufügen in der Gruppe Allgemein auf die Schaltfläche Benutzerdefinierte Spalte einfügen. Es erscheint das folgende Dialogbild:
- Löschen Sie im Eingabefeld Neuer Spaltenname den vorbelegten Namen Custom und tragen Sie den Namen Datum ein.
-
Geben Sie die folgende benutzerdefinierte Spaltenformel ein, um z.B. die Zahl 1 umzuwandeln in den Datumswert 01.01.2015.
=Date.FromText([Periode]&”/2015/01″)
-
Prüfen Sie, ob nach der Eingabe der Formel der folgende Hinweis erscheint: Es wurden kein Syntaxfehler gefunden. Klicken Sie auf die Schaltfläche OK. Die Tabelle hat folgendes Aussehen (Ausschnitt):
- Verschieben Sie die Spalte Datum an den Anfang der Tabelle.
- Löschen Sie Spalte Periode aus der Tabelle, in dem Sie auf der Registerkarte Start in der Gruppe Spalten verwalten auf die Schaltfläche Spalten entfernen klicken. Die Spalte wird nicht mehr benötigt.
- Markieren Sie die Spalte Datum und klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Transformieren auf den Pfeil im Kombinationsfeld Datentyp. Klicken Sie auf den Eintrag Datum. Die Tabelle ist nun für die Übertragung in ein Excel-Tabellenblatt vorbereitet.
-
Klicken Sie im Menüband auf der Registerkarte Start in der Gruppe Schließen auf die Schaltfläche Schließen und Laden. In der Excel-Arbeitsmappe wird ein neues Tabellenblatt angelegt mit den Daten aus der mit Power Query umgewandelten Tabelle. Das Tabellenblatt sieht folgendermaßen aus:
Am rechten Rand des neu angelegten Tabellenblatts erscheint der folgende Auswahlbereich:
-
Aus dieser Tabelle können Sie mit wenigen Mausklicks z. B. die folgende PivotTable erstellen.
Hinweise
- Wenn Sie die Planwerte nachträglich ändern, können Sie Power Query veranlassen, die Daten erneut aus dem Tabellenblatt zu lesen, umzuwandeln und in das dafür vorgesehene Tabellenblatt zurückzuschreiben. Klicken Sie im Auswahlbereich Arbeitsmappenabfragen auf die Schaltfläche mit dem Aktualisierungssymbol am rechten oberen Rand der Schaltfläche Planzahlen.
- Bei der Übernahme der Plandaten aus dem Excel-Tabellenblatt nach Power Query hat das Add-In den Bereich mit den Ursprungsdaten in eine Excel-interne Tabelle umgewandelt. Diese Umwandelung hat den Vorteil, dass Sie in diese Tabelle sogar Planwerte für zusätzliche Artikel eingeben können. Starten Sie danach die Arbeitsmappenabfrage erneut, um die Daten der neuen Artikel ebenfalls in das vorgesehene Tabellenblatt zurückzuschreiben.
Bei meiner Power Query Tabelle ist das Datum als Text wie folgt formatiert: 160201 160202 160203 etc. Ich würde das gerne als Datumsformat auslesen. Date.FromText https://msdn.microsoft.com/en-us/library/mt260687.aspx oder Date.From funktioniert nicht