Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Gegeben ist eine Liste mit Namen sowie unterschiedlichen Obstsorten, die von der jeweiligen Person eingekauft werden sollen:
Die importierte Liste, als Tabelle formatiert
Das Ziel ist, daraus einerseits eine Kreuztabelle nach diesem Muster zu erstellen:
Das gegebene Ziel: Eine Kreuztabelle
… oder aber über eine Pivot Tabelle (oder auch zwei PT) eine Übersicht/Liste bzw. eine Art EinkaufsListe zu erstellen:
Beispiel einer PivotTable
Gleiche Basis-Daten, eine andere Pivot-Sicht
Kreuztabelle per Power Query
Wenn Sie mögen, tippen Sie die in der ersten Abbildung gezeigten Daten gerne ab. Alternativ biete ich Ihnen an, hier eine csv-Tabelle herunterzuladen, welche sie dann entweder in ein Excel Arbeitsblatt oder gleich in Power Query importieren. Und wenn Sie experimentierfreudig sind, dann kopieren Sie doch einfach die URL (die Internet- bzw. Link-Adresse) per Rechtsklick auf den Link hierüber und importieren die csv direkt aus dem Web in den Abfrage-Editor. In dem Fall müssen Sie aber unbedingt als ersten Schritt nach dem Import (über Daten transformieren) die Erste Zeile als Überschriften verwenden.
Nach dem Import in Power Query merken Sie sich bitte die exakte Schreibweise der Überschrift mit den Früchten; es ist Obstsorte und es handelt sich hier ja um jene Daten, welche später in der Kreuztabelle unterhalb der Namen (welche ja die Überschriften sein werden) angeordnet werden. – Markieren Sie nun die Spalte Name, wählen Gruppieren nach und ändern Sie bei Vorgang die Auswahl auf Alle Zeilen. Nach einem OK wird sich die Abfrage so darstellen:
Nach der Gruppierung stellt sich die Abfrage so dar…
Name ist auf die Unikate reduziert. Obstsorte ist scheinbar verschwunden oder durch Anzahl ersetzt und auch auf 6 Zeilen reduziert. Die Überschrift dieser zweiten Spalte ist zwar Anzahl, aber das ist nicht relevant, denn Sie werden darauf keinen Bezug nehmen. Der Inhalt jeder Zeile dieser zweiten Spalte ist durchgängig Table aber in diesem Fall werden sie ausnahmsweise einmal nicht auf den Doppelpfeil 
Klicken, um die Spalte zu erweitern; Sie belassen es bei diesem Zustand und tun an bzw. mit dieser Spalte erst einmal gar nichts.
Wechseln Sie zum Register Spalte hinzufügen | Benutzerdefinierte Spalte und tragen Sie im Feld Benutzerdefinierte Spaltenformel diese Formel exakt so ein:
= Table.Column([Anzahl], "Obstsorte")
wobei sie den Spaltennamen Benutzerdefiniert auch gerne so lassen können. Wie in Power Query (genauer gesagt: der Sprache M) üblich, müssen Sie exakt auf die Groß- Kleinschreibung achten. Den (Spalten-) Namen [Anzahl] übernehmen Sie idealerweise durch einen Doppelklick im rechten Kasten Verfügbarer Spalten. Den eben gemerkten Spaltennamen "Obstsorten" müssen sie mit den Anführungszeichen von Hand eingeben, denn unter diesem Namen existiert die Spalte ja nicht mehr, aber der Inhalt wird nach wie vor unter diesem Namen intern geführt. Das derzeitige Ergebnis stellt sich nun so dar:
Die neu erstellte Spalte enthält durchgängig den Eintrag ‘List’ (Liste).
Erweitern Sie nun die Spalte Benutzerdefiniert durch einen Klick auf den Doppelpfeil in der Überschrift und wählen Sie im DropDown die Option Werte extrahieren… Im Dialog-Fenster wählen Sie als Trennzeichen für das verketten von Listenwerten beispielsweise das in Deutschland übliche Semikolon ;, es kann aber auch Belieben ein anderes Zeichen sein. Nach dem schließen des Fensters stellt sich die Abfrage so dar (hier mit einer von mir verbreiterten Spalte Benutzerdefiniert):
Mit Semikola gesplittete Spalte ‘Benutzerdefiniert’
Nun wechseln Sie bei bestehender Markierung der Spalte Benutzerdefiniert zum Register Start | Spalte teilen und akzeptieren die Vorgabe Semikolon sowie Bei jedem Vorkommen des Trennzeichens. Das Resultat wird so sein, wie Sie es erwartet haben; die Spalte Benutzerdefiniert wurde in so viele Spalten aufgeteilt, wie die Anzahl der Semilola +1 in der ursprünglichen Zelle der jeweiligen Zeile ist.
Im folgenden Schritt löschen Sie die Spalte Anzahl (die mit dem Inhalt Table), sie wird nicht mehr benötigt. Wenn Sie möchten, dass die Namen in der zu erzeugenden Kreuztabelle alphabetisch sortiert sind, dann sortieren sie an dieser Stelle die Spalte Namen aufsteigend. Ein kleiner Tipp: eventuell werden sie diesen Schritt später noch einmal deaktivieren, rückgängig machen. Darum schlage ich folgendes vor: Im rechten Seitenfenster ein Rechtsklick auf diesen Eintrag (Sortierte Zeilen), Umbenennen und beispielsweise (Namen) anhängen.
Das Ergebnis ist nun zwar eine Kreuztabelle, aber die Zielvorstellung war ja eine andere. In der Überschrift sollten die Namen stehen und in den Zeilen darunter die entsprechenden Früchte. Um das zu erreichen, wechseln Sie zu Transformieren und wählen in der Gruppe Tabelle das Symbol Vertauschen. Prinzipiell entspricht jetzt nach dem transponieren die Anordnung der Wunschvorstellung. Das einzige, was nicht so 100% ist: Die durch Power Query automatisch erzeugten Überschriften Column1 bis Column6. Das ist aber recht schnell ins richtige Lot zu bringen. Register Start, Gruppe Transformieren | Erste Zeile als Überschriften verwenden und sie haben (endlich) das Ziel erreicht. 🙂 Nun nur noch Schließen & laden oder Schließen & laden in…, um die Daten in ein neues oder das bestehende Tabellenblatt an eine definierte Position zu schreiben.
Auswertung mit PivotTable
Gleich zu Beginn sei angemerkt, dass eine exakte oder auch nur weitgehend gleiche Darstellung wie eben mit Power Query erzeugt mit einer Pivot Tabelle nicht möglich ist. Dafür gibt es aber mit diesem Tool andere Möglichkeiten, die bei entsprechender Aufgabenstellung leichter zu handhaben sind.
PivotTable (einfache Auflistung)
Eine Pivot Tabelle kann für den „gestandene” Excel-User gewiss mit wenigen Handgriffen bzw. Mausklicks leichter erstellt werden, als die vorgestellte Kreuztabelle über den Weg des Power Query. Wie schon erwähnt, ist das Aussehen aber deutlich anders, wenn ausschließlich Name und Obstsorten gezeigt werden sollen:
Beispiel einer PivotTable
Die Schritte zum Ziel sind (stichwortartig) folgende:
- Falls noch nicht erfolgt, die Daten aus der csv in ein Tabellenblatt importieren. Hinweis: In Excel >= 2019 oder 365 werden Sie zweckmäßigerweise den Legacy-Assistenten verwenden und umgehen damit den Import per PQ.
- Nach dem Import in die Daten Klicken und per StrgT oder StrgL eine Intelligente Tabelle daraus machen.
- Klick in die Tabelle, Register Tabellentools | Entwurf markieren und in der Gruppe Tools den Punkt Mit PivotTable zusammenfassen anklicken.
- Die Tabelle bzw. der Bereich ist ja bereits ausgewählt, darum legen Sie nun fest, ob die Pivot Tabelle in ein Neues Arbeitsblatt oder Vorhandenes Arbeitsblatt geschrieben werden soll.
- Im rechten Seitenfenster PivotTable-Felder setzen Sie nun das Häkchen zuerst bei Name und anschließend bei Obstsorte.
Grundsätzlich ist damit die Pivot Tabelle erstellt. Möchten Sie das Aussehen etwas „hübschen”, dann gehen Sie über Entwurf | Berichtslayout | Im Gliederungsformat Anzeigen und anschließend bei Gesamtergebnisse die Auswahl Für Zeilen und Spalten deaktiviert treffen.
Neben der anderen Anordnung der Daten gibt es bei dieser PivotTable zwei weitere deutliche Unterschiede: Die Obstsorten sind bei jedem einzelnen Namen aufsteigend sortiert und eventuelle Dubletten bei den Obstsorten werden nicht mehrfach angezeigt. Diese PT kann man also gut und gerne als Übersicht betrachten, wer sich für welches Obst interessiert. Und es ist natürlich sehr leicht, rechts im Bereich Zeilen die Obstsorte nach oben (also an erster Position, oberhalb Name) zu ziehen und dann sofort eine andere Sichtweise auf dem schon zu haben. Das ist ein klarer Vorteil einer PivotTable.
PivotTable (mit Anzahl und Berechnung)
Mit einer etwas anderen Gestaltung der Pivot Tabelle kann auf recht einfache Weise zweierlei erreicht werden: Erstens die Darstellung als Kreuztabelle und zweitens wird durch die Angabe der Anzahl der jeweiligen Obstsorte und der automatischen Berechnung so etwas wie eine EinkaufsListe erzeugt:
Gleichen Basis-Daten, eine andere Pivot-Sicht
Natürlich werden Sie auch hier die gleichen Quelldaten verwenden wie bisher. Sie erstellen eine neue PivotTable an beliebiger Position und ziehen das Feld Name in den Bereich Spalten und das Feld Obstsorte in den Bereich Zeilen. Anschließend das Feld Obstsorte noch einmal in den Bereich Werte ziehen und das Ergebnis stellt sich beinahe so dar, wie in dem Bild hierüber. Mit zwei kleinen Änderungen erreichen Sie eine identische Darstellung: Ändern Sie in der ersten Zeile der PT die Überschrift Anzahl von Obstsorte in Obstsorten und im Berichtslayout werden sie Im Gliederungsformat anzeigen aktivieren.
Durch die andere Darstellung erkennen sie auch ganz klar, dass Jennifer bei den Aprikosen und Maren bei den Erdbeeren mit je zwei Positionen dabei ist. Die automatischen Berechnungen der Gesamtergebnisse stellt eine ideale Übersicht für beispielsweise eine EinkaufsListe dar.
Power Query (Änderung in der Sortierung)
Vielleicht kommt bei Ihnen jetzt der Wunsch auf, dass die per Power Query erstellte Kreuztabelle nicht nur bei den Namen, sondern auch bei den Obstsorten in jeder Namensspalte aufsteigend sortiert sein soll. Um das den neuen Wünschen anzupassen, öffnen Sie erst einmal die Power Query-Abfrage. Dazu markieren Sie ein beliebiges Feld in der durch PQ erzeugten Ergebnis-Tabelle und dann Abfragetools|Abfrage | Bearbeiten. Alternativ geht natürlich auch ein Doppelklick auf den entsprechenden Eintrag im rechten Seitenfenster (sofern dieses eingeblendet ist).
Sie erinnern sich, dass sie im rechten Seitenfenster den einen Schritt umbenannt haben bzw. sollten. Markieren Sie diese Zeile und Klicken dann auf das rote Kreuz links des Textes. Damit wird dieser Schritt (nach einer Bestätigung) gelöscht. Sie erkennen auch, dass in der Abfrage jetzt wieder die alte, unsortierte Reihenfolge gegeben ist. Wählen Sie nun bei Angewendete Schritte den zweiten Eintrag Geänderter Typ durch einen einfachen Klick auf diese Zeile. Sortieren Sie nun zuerst die Spalte Name Aufsteigend und gleich danach die Spalte Obstsorte ebenfalls Aufsteigend. Die Sicherheitsabfrage durch Power Query bezüglich des Einfügens eines Schritts werden sie jeweils bestätigen.
Das war’s auch schon. Ein Klick auf das Symbol Schließen & laden und Power Query wird den Abfrage-Editor schließen. Und sie werden rasch erkennen, dass die PQ-Tabelle ohne weiteres Zutun in der gewünschten Sortierung auf dem Bildschirm zu sehen ist. So ist auch in dieser Tabelle leichter erkennbar, dass Jennifer und Maren jeweils eine Obstsorte doppelt gewählt hatten. – Ändern Sie gerne in der Original-csv Textdatei oder der importierten Liste/Tabelle die Dubletten mit einem anderen Wert ab und aktualisieren Sie die PT-Tabelle und gerne auch die Pivot Tabellen. Ja, Excel ist sexy…! 😎
Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?
Dann würde ich mich über einen Beitrag (z.B. € 2,00) Ihrerseits freuen …