Summierung einmal anders …

Wege nach Rom: Summierung einmal anders …

In einem Forum wurde hier unter dem Titel „Tabellen zusammenfassen“ eine scheinbar recht einfache Frage gestellt. Zumindest auf den ersten Blick. Beim zweiten Hinsehen zeigte sich, dass doch etwas mehr „Gehirnschmalz“ erforderlich ist, um eine einigermaßen „schlanke“ und auch nachvollziehbare Lösung zu finden.

Damit Sie gleich jene Datei zur Verfügung haben, welche ich für dieses für diese Musterlösung verwende, laden Sie idealerweise dieses File von unserem Server herunter. Wenn sie nun im Forum den ersten Beitrag zum Thema durchlesen werden sie erkennen, dass in den Zeilen 2:12 die auszuwertenden Daten stehen, in Zeile 1 (zumindest teilweise) die Überschriften. In den Zeilen 14:21 ist das Wunschergebnis des Fragestellers dargestellt.

Da es ein „ungeschriebenes Gesetz“ (grundsätzlich eine Voraussetzung) ist, dass in einer Liste jede Spalte eine eindeutige Überschrift haben muss, habe ich in den ersten drei Spalten einfach eine eigene, hoffentlich sinnvolle Überschrift eingefügt: Spalte A: Song, Spalte_B: Band/Interpret und Spalte C einfach ein Fragezeichen ?, da ich die Bedeutung nicht kenne. Das stellt sich nun so dar:

Die Roh-Daten vor dem Import

Die Roh-Daten vor dem Import

… Und da ja bekanntlich viele Wege nach Rom führen, stelle ich Ihnen hier diesen oder jenen Weg zum Erproben vor.

Vorarbeit für jede der Möglichkeiten

Im ersten Schritt werden Sie eine beliebige Zelle der Rohdaten markieren und StrgT oder StrgL klicken, um daraus eine so genannte Intelligente Tabelle zu machen. Den gleichen Effekt erreichen Sie, wenn sie im Register Start Gruppe Formatvorlagen auf das Symbol Als Tabelle formatieren klicken. Und diese Tabelle werden Sie jetzt in den Power Query-Editor importieren. Das geht am besten, wenn Sie entweder das Register Power Query (Exel 2010/13) bzw. in neueren Versionen das Register Daten aktivieren und dort auf Von Tabelle bzw. Aus Tabelle klicken. Die Daten werden in den Editor importiert und es stellt sich nun so dar:

Die Daten sind in den Power Query-Editor importiert

Die Daten sind in den Power Query-Editor importiert

Die Forderung des Fragestellers das war ja, dass zuerst die Spalte 1 (Song) und anschließend die Spalte 2 (Band/Interpret) jeweils aufsteigend sortiert werden soll. Darum klicken Sie zuerst in die Spalte Song, erweitern die Überschrift durch einen Klick auf  und sortieren die Daten aufsteigend. Anschließend gleichermaßen die Spalte Band/Interpret aufsteigend sortieren. Sie erkennen, dass diese Vorgehensweise anders ist als in Excel, in Power Query wird stets die logische Reihenfolge des Sortieren angewendet.  😎 

Falls Sie sich wundern, dass ich trotz offensichtlich bestehender korrekter Sortierung diesen Vorgang noch einmal durchführe, dann hat es durchaus seinen Grund: Zu oft erlebe ich, dass irgendwann neue Daten hinzugefügt werden und die Sortierung dann nicht mehr so ist, wie sie sein soll. Auf diese Weise erledigt Power Query für Sie die Arbeit ganz alleine bei jedem Aktualisieren der Datei.

▲ nach oben …

(1) Sicher ist Sicher

  Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung   

Sie kennen das vielleicht von Flughäfen oder anderen sicherheitsrelevanten Institutionen, die Sicherheitskontrollen sind für alle Beteiligten immer mit etwas mehr Aufwand verbunden aber dafür gilt der Satz „safety first“ (Sicherheit geht vor). Und so ist es auch hier in Power Query. Etwas mehr Aufwand, dafür aber die Sicherheit, dass nicht irgendwo ein Mausklick vergessen wird und es dadurch zu verfälschen Ergebnissen kommt. Und im Endeffekt bietet solch eine Lösung fast immer mehr Flexibilität für künftige Wünsche, welche mit diesen Basisdaten verwirklicht werden sollen.

Der aus meiner Sicht sicherste Weg geht über den Vorgang des Entpivotieren und anschließend (nach einer Bearbeitung) wieder Pivotieren der Daten. Und das große „Aber“ dabei ist, dass sie idealerweise einige Kenntnisse in Power Query haben sollten oder wenn sie Einsteiger in Sachen PQ sind, ein gerüttelt Maß an Lernbereitschaft mitbringen. Aber wie gesagt, dieser Weg ist sicher, wenn Sie die folgende Anweisung haarklein nachvollziehen. – Und im kommenden Abschnitt ist auch ein Weg für Einsteiger beschrieben.

Um mir die folgenden Schritte etwas leichter zu gestalten, führe ich die Spalten Song und Band/Interpret derart zusammen, dass sie in einer einzigen Spalte stehen und mit Tabstopp als Trennzeichen versehen sind. Den automatisch generierten Namen Zusammengeführt belasse ich der Klarheit wegen. In einer der letzten Schritte bekommen die wieder getrennten Spalten dann die ursprünglichen Bezeichnungen zurück.

Nun markieren Sie die Spalte Zusammengeführt, Rechtsklick in die Überschrift und anschließend Andere Spalten entpivotieren. Im Anschluss stellt sich das so dar:

Die Daten direkt nach dem entpivotieren

Die Daten direkt nach dem entpivotieren

Diesen Stand in der Abfrage werden sie später noch einmal brauchen. Von dieser Query stellen Sie vor weiteren Schritten ein Duplikat auf der Basis des Verweises. Klicken Sie zu Beginn in den Streifen links der Abfrage (in den Text Abfragen) und es öffnet sich ein Seitenfenster. Rechtsklick in den Namen des einzigen Eintrags Tabelle1, Verweis und es wird automatisch eine neue Tabelle mit dem Namen Tabelle1 (2) erstellt. An der Markierung sowie im rechten Seitenfenster erkennen Sie, dass die neue Tabelle auch die aktuelle ist. Filtern Sie nun die Spalte Attribut so, dass nur die Zeilen mit dem Eintrag ? Sichtbar sind.

Markieren Sie die Spalte Zusammengeführt, Gruppieren nach und im Dialog geben Sie als Neuer Spaltenname beispielsweise Summe ein, bei Vorgang wählen Sie ebenfalls Summe. Als Spalte wählen Sie naturgemäß die Spalte Wert. Schließen Sie den Dialog mit OK. Sie erkennen, dass die identischen Zeilen aus Zusammengeführt zu 1 Zeile zusammengefasst worden sind und die in der Spalte Summe entsprechend addiert worden sind:

Gruppiert und addiert, die Werte der Spalte Zusammengeführt und Wert

Gruppiert und addiert, die Werte zweier Spalten

Nach diesem ersten Schritt ist es an der Zeit, die Abfragen zu sichern. Markieren Sie dazu Abfrage1, Datei, Schließen & laden in… | Nur Verbindung. Öffnen Sie anschließend wieder Abfrage1 auf beliebige Weise. 

Erstellen Sie erneut ein Verweis-Duplikat der Tabelle1, welche jetzt automatisch den Namen Tabelle1 (3) erhält. Filtern Sie hier die Spalte Attribut so, dass die Zeilen mit dem Inhalt ? nicht mehr angezeigt werden (und auch nicht mehr in der Abfrage enthalten sind). An dieser Stelle ist es besonders deutlich sichtbar, dass beim Entpivotieren jene Spalten nicht übernommen worden sind, wo ausschließlich null enthalten war, die also komplett leer waren bzw. sind. Durch Ihr Tun sind jetzt nur noch die Spalten 1977 und 1978 dargestellt, erfasst worden.

Markieren Sie die Spalten Zusammengeführt und Attribut, dann ein Klick auf Gruppieren nach. Bei Neuer Spaltenname geben Sie beispielsweise Summe ein, unter Vorgang wählen Sie ebenfalls Summe. Im Feld Spalte wählen Sie wie beim vorherigen Beispiel Wert und schließen anschließend den Dialog. Und das Ergebnis stellt sich nun so dar:

Gruppieren, dieses Mal mit einem anderen Ergebnis

Gruppieren, dieses Mal mit einem anderen Ergebnis

Sie werden jetzt schon bei einer kleinen Stichprobe feststellen, dass die Werte dem Wunsch-Ergebnis des Fragesteller es entsprechen. Im nächsten Schritt werden die Daten wieder pivotiert, also in eine Kreuztabelle übertragen. Markieren Sie darum jene Spalte, welche die Überschriften enthält: Attribut. Anschließend ein Wechsel zum Register Transformieren. Wählen Sie hier in der Gruppe Beliebige Spalte das Symbol für Pivotieren . Im Dialog ändern Sie die Werte-Spalte auf Summe und anschließend OK. Wenn sie nun in Gedanken die dritte Spalte der Rohdaten (?) ausblenden, dann stimmt das Ergebnis weitestgehend mit dem Wunsch-Ergebnis überein. (Ja, die erste Spalte muss natürlich noch geteilt werden, klar…)

Nun werden Sie die fehlende Spalte in genau diese Abfrage einfügen. Zu Beginn achten Sie darauf, dass die Abfrage1 (3) aktiviert ist. Start, Kombinieren | Abfragen zusammenführen | Abfragen als neue Abfrage zusammenführen. Im Dialog Zusammenführen wählen Sie im mittleren, kleinen Kombinationsfeld den Wert Tabelle1 (2). Verknüpfen Sie nun im oberen als auch unteren Block die beiden Felder Zusammengeführt, indem sie jeweils in die Überschrift oder ein beliebiges Feld in der Spalte klicken. Das stellt sich nun so dar:

Der erste Schritt beim Zusammenführen der beiden Abfragen

Der erste Schritt beim Zusammenführen der beiden Abfragen

Der vorgeschlagene Wert bei Join-Art kann so bleiben. Nach dem Schließen des Dialogs sieht die Abfrage so aus:

Vielleicht nicht Ihren Erwartungen entsprechend, aber korrekt

Vielleicht nicht Ihren Erwartungen entsprechend, aber korrekt

Sie erkennen, dass im Anschluss an die bisherige letzte Spalte eine neue Spalte mit dem Namen der Abfrage Tabelle1 (2) hinzu gekommen ist. In jeder Zeile der Spalte steht Table (Tabelle) als Inhalt. Erweitern Sie nun die Überschrift in dieser Spalte durch einen Klick auf den Doppelpfeil Doppelpfeil. Entfernen Sie im Dialogfenster das Häkchen bei Zusammengeführt und unterhalb des Kastens bei Ursprünglichen Spaltennamen als Präfix verwenden. Der einzig markierte Wert Anzahl wird sofort der Inhalt der aktuellen Spalte sein. Ziehen Sie diese Spalte Anzahl an die 2. Position von links (also vor 1977).

Bleibt noch die Trennung der zuvor zusammengeführten zwei Spalten. Markieren Sie dazu die Spalte Zusammengeführt, Start und Spalte teilen | Nach Trennzeichen. Die Vorgabe ist der Tabstopp und Sie werden sich erinnern, dass das genau das Trennzeichen beim Zusammenfügen der Spalten war. Also akzeptieren Sie das und schließen den Dialog.

Bingo! So soll es sein, das Ergebnis ist absolut dem Wunsch entsprechend. Jetzt bleibt nur noch, die Query via Schließen & laden in ein neues Sheet zu schreiben oder per Schließen & laden in… an beliebiger Position zu platzieren. – Mir ist bewusst, dass auf diese Weise nur die Jahre dargestellt werden, wo auch Daten enthalten sind. Es wäre durchaus möglich, auch die „leeren“ Jahre im Ergebnis mit darzustellen, aber einerseits ist das nicht unbedingt aussagekräftig und andererseits war es nicht gefordert (wobei ich davon ausgehe, dass in der Original-Datei alle Spalten Daten enthalten).

▲ nach oben …

(2) Gruppieren, Gruppieren

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

Dieser Abschnitt ist eigentlich nur entstanden, weil im Forum jemand schrieb, dass die Lösung „nur“ mit der Funktionalität des Gruppierens, also ohne (ent)pivotieren möglich sei. Es gab einiges an Widerspruch anderer Helfer und das hat meinen Ehrgeiz angestachelt …  😎 Ich räume aber auch ein, dass ich mir das etwas einfacher vorgestellt hatte, ohne die erforderlichen Umwege. Aber schauen Sie selbst. Und im Endeffekt sieht die Lösung ganz einfach aus. Wie so oft im Leben, „man“ muss nur wissen, wie es geht…  💡 

Wie Sie die Basis-Daten in den Power Query-Editor importieren, ist bereits ganz zu Beginn dieses Beitrages detailliert erklärt. Was den ersten Schritt, das Zusammenführen der beiden ersten Spalten betrifft: Den Punkt bzw. Schritt können und sollten Sie hier auch gerne anwenden, obwohl es auch einen etwas aufwändigeren Weg ohne das zusammenführen und späterem entkoppeln der beiden Spalten gibt. Da würden Sie dann in der Gruppierung stets beide Spalten markieren. – Zu Beginn stellt sich die Abfrage so dar:

Um die beiden Spalten Song und Band/Interpret zusammenzuführen, markieren Sie erforderlichenfalls erst die Spalte Song und danach Shift und ein Klick in die Überschrift der Spalte Band/Interpret. Wechseln Sie nun zum Register Transformieren und wählen dort in der Gruppe Textspalte den Eintrag Spalten zusammenführen. Im Dialog wählen Sie im Kombinationsfeld Trennzeichen den Eintrag Tabstopp. Bei Neue Spaltenname (optional) ist der Name Zusammengeführt vorgeschlagen, dabei können, sollten Sie es belassen. Das Ergebnis stellt sich zu Beginn so dar:

Die beiden ersten Überschriften sind zu 1 Spalte zusammengefasst

Die beiden ersten Überschriften sind zu 1 Spalte zusammengefasst

Markieren Sie nun ausschließlich die erste Spalte Zusammengeführt. Wählen Sie im Menüband des Symbol Gruppieren nach, unten tragen in das Feld Neuer Spaltenname beispielsweise Summe ein. Sie könnten auch ein ? schreiben, denn hier wird genau diese Spalte summiert. Ich bleibe aber im Script beim von mir vorgesehenen Namen. Bei Vorgang wählen Sie im Kombinationsfeld naturgemäß auch Summe und bei Spalte wählen Sie ?, denn diese Spalte soll für jeden der Begriffe der Spalte Zusammengeführt die Addition der einzelnen Werte repräsentieren. Oder anders ausgedrückt: Gleiche Inhalte in der ersten Spalte werden zusammengefasst und summiert ausgegeben, einzelne Werte bleiben naturgemäß so, wie sie derzeit sind.

Wenn Sie jetzt das Dialog-Fenster mit OK jetzt schließen würden, dann sähe das Ergebnis so aus:

Das Ergebnis stimmt, aber …

Das Ergebnis stimmt, aber …

Die Zahlen stimmen. Aber es fehlen natürlich ganz entscheidende Spalten, die Jahres-Zahlen. Auf jeden Fall sind es die Werte jener Jahre, die nicht ausschließlich leere Felder enthalten. Darum ist noch ein weiterer Schritt im Gruppieren-Dialog erforderlich. – Soweit Sie den Dialog nicht geschlossen und den Text hierüber ausschließlich zur Kenntnis genommen haben, gehen Sie direkt zum folgenden Absatz und führen die dargestellten Schritte aus. Wenn Sie ein wenig experimentiert haben, um das Ergebnis nachvollziehen zu können, dann klicken Sie in der letzten Zeile des Bereichs Angewendete Schritte im rechten Seitenfenster auf das Zahnrad-Symbol rechts und der Dialog wird sich im vorherigen Zustand wieder öffnen:

"Darf es etwas mehr sein?" (smile)

„Darf es etwas mehr sein?“ 😉

Auf den ersten Blick scheint es so zu sein, dass sich dort nicht viel ändern lässt. Aber wenn sie genau hinsehen werden sie erkennen, dass direkt unter der Überschrift Gruppieren nach das Optionsfeld Standard markiert ist; daneben gibt es aber auch noch das Feld Weitere. Genau dieses werden sie nun per Klick aktivieren. Das Fenster vergrößert sich und zusätzlich zur Schaltfläche Gruppierung hinzufügen gibt es im unteren Bereich eine weitere, neue Schaltfläche: Aggregation hinzufügen. Ein Klick darauf wird unterhalb der eben ausgefüllten Zeile mit den drei Feldern eine neue Zeile mit der gleichen Anzahl von Feldern Feldern darstellen.

Wie zuvor bereits einmal angemerkt, muss jedes Feld in einer Liste (und natürlich auch auch in Power Query) einen eindeutigen Namen haben. Darum geben Sie in diese neue Zeile unter Neuer Spaltenname eine beliebige Bezeichnung ein. Ich verwende dazu meistens nur ein , weil ich diesem Feldnamen Prinzipiell nicht mehr verwenden werde (Power Query aber sehr wohl). Bei Vorgang wählen Sie in die zweite Zeile den Begriff Alle Zeilen, das Feld Spalte ist nicht auswählbar. Klicken Sie nun auf OK und das Ergebnis wird sich so darstellen:

"Table" steht hier für "Tabelle"

„Table“ steht in der neuen Spalte für „Tabelle

Klicken Sie in der Überschrift der neuen Spalte auf das Symbol mit dem Doppelpfeil Doppelpfeil, um den Inhalt der Spalte zu erweitern. Das stellt sich anschließend so dar:

Erst einmal etwas ungewohnt, aber gar nicht so schlimm … (smile)

Erst einmal etwas ungewohnt, aber gar nicht so schlimm … 😉

Aktivieren Sie im ersten Schritt das Optionsfeld Aggregieren (ganz oben) und entfernen Sie dann das Häkchen bei Ursprünglichen Spaltennamen als Präfix verwenden (ganz unten). Sie werden beim Durchscrollen der Liste erkennen, dass es zwei unterschiedliche führende Zeichen gibt: die Raute # sowie das Summenzeichen . Setzen Sie das Häkchen bei den einzigen Jahren mit Daten, also bei 1977 und 1978, alle anderen Felder bleiben leer:

Die Jahres-Spalten aggregieren

Die Jahres-Spalten aggregieren

Das Ergebnis wird auch Sie überzeugen. Der Zusatz „Summe von “ in der Überschrift wurde durch Power Query automatisch hinzugefügt, kann aber aus meiner Sicht bestehen bleiben. Oder aber Sie ändern die Überschriften so, wie Sie diese gerne hätten; entweder per Doppelklick oder markieren und dann F2. Es ist aber auch eine Überlegung wert, eine solche Änderung erst in der erstellten Excel-Tabelle per Ersetzen durchzuführen.

Diese Lösung kann sich gewiss sehen lassen

Diese Lösung kann sich gewiss sehen lassen

Jetzt bleibt praktisch nur noch, die Spalte Zusammengeführt wieder in ihre eigentlichen Ursprünge zurück zu „verwandeln“. Dazu markieren Sie diese Spalte durch einen Klick in die Überschrift, Start, Spalte teilen | Nach Trennzeichen | Tabstopp | Bei jedem Vorkommen des Trennzeichens und dann OK. Jetzt noch die Überschriften der beiden ersten Spalten wieder auf Song und Band/Interpret ändern und jetzt bedarf es gewiss keiner weiteren Änderung der Abfrage.

Start oder Datei, Schließen & laden führt dazu, dass die Abfrage in ein Excel-Arbeitsblatt geschrieben wird. Wenn Sie gerne eine andere Position hätten, können Sie die erzeugte Tabelle problemlos ausschneiden und an gewünschter Position wieder einfügen.

Sie werde erkennen, dass sich dieser Aufwand wirklich lohnt, wenn Sie die Daten und der Ursprungstabelle ändern oder ergänzen. Nach einem Öffnen der *.xlsx wird sich zu Beginn keine Änderung zeigen. Das ist gewollt so, denn es kann ja sein, dass Sie Ihre Arbeit am Vorabend unterbrochen haben und nun am folgenden Morgen genau an der Stelle weitermachen wollen. Wenn Sie bei entsprechender Gelegenheit aber einen Klick auf Aktualisieren durchführen, dann haben Sie sofort alle neu berechneten und aktualisierten Daten auf dem Bildschirm.

Mein Tipp an dieser Stelle: Arbeiten Sie sich mit der Zeit in die fortgeschrittene Lösung des ersten Abschnitts ein. Es lohnt sich gewiss, denn Sie werden auch in vielen anderen Fällen von den erlernten Funktionalitäten des Power Query profitieren.


Hinweis: Im Forum wurde dieses Thema heiß diskutiert. Während ich dieses Skript verfasste, kamen diverse Beiträge und Lösungsvorschläge dazu. Ich habe bewusst während der Schreib-Phase nicht im Forum nachgeschaut, was sich dort „getan hat“. Allerdings wurde mir mit schöner Regelmäßigkeit angezeigt, dass dort ein neuer Beitrag (mit einem Auszug des Titels) eingestellt worden ist. Ich werde mich im neuen Jahr (2019) dort einmal „durchwühlen“. Vielleicht gibt es ja ähnliche oder auch bessere Lösungen. Wobei ich generell mit der Qualifizierung „besser“ meine Probleme habe, denn wenn das Ergebnis auch in Grenzfällen stimmt, dann ist jeder Weg gut. Ob nun kürzer oder länger, verschlungener oder gerader, das ist eher eine Frage des Geschmacks. Ich vertrete die Überzeugung, dass ein Weg, eine Funktion oder Formel bzw. ein Code transparent sein soll. Übersicht kostet mitunter die eine oder andere Zeile oder auch manch einen Schritt mehr. Nutzer, die nur das Ergebnis wollen, ohne den Anspruch zu haben, selbst am Projekt weiter zu arbeiten, werden das nicht unbedingt zu schätzen wissen und wahrscheinlich den kürzeren, geraden Weg wählen. Diesem Anspruch will und werde ich nie gerecht werden, denn ich habe an meine Leser den Anspruch, dass sie aus meine Beiträgen Wissen schöpfen, um auf diesem Fundament ihr eigenes Schaffensgebäude aufzubauen. (Das Datum der Veröffentlichung ist nicht ohne Grund Silvester 2018… 💡 )

▲ nach oben …

(3) Übung mit realistischen Daten, Mut zur Lücke  😉 

  Wissensstand: Level 1 ⇒ Einsteiger in PQ, keine/kaum Erfahrung   

 

Da wir heute am letzten Tag des Jahres 2018 stehen ist schon am Horizont sichtbar, dass eine weitere Spalte für das kommende Jahr eingefügt werden wird. Gleichermaßen für das Jahr 2020, und, und, und… Darum ist es hilfreich, wenn der Aufbau der Abfrage so ist, dass automatisch alle Spalten, also alle Jahre für die Berechnung berücksichtigt werden. Selbst dann, wenn da einmal ein Jahr keine Daten enthält. Für den Wissensstand des ersten Abschnitts (etwas Erfahrung) wäre das prinzipiell kein Problem, für Einsteiger ist vielleicht der eine oder andere kleine Umweg ganz hilfreich. 🙂 

Ich habe Ihnen im Muster-File eine tabellarische Aufstellung zusammengestellt, wo zwar durch die Funktion ZUFALLSBEREICH() Fantasie-Zahlen generiert worden sind, die aber dennoch denkbar sein können. In den Jahren 2012, 2016 und 2019 sind (noch) keine Daten erfasst, die Spalten sollen aber in der endgültigen Darstellung im Datenbereich auch leer angezeigt werden.  Gehen Sie dazu so vor:

  • Laden Sie dieses File von unserem Server und importieren Sie die Daten auf dem bekannten Wege in den Power Query-Editor.
  • Sortieren Sie die erste und auch zweite Spalte aufsteigend.
  • Markieren Sie alle Spalten ab der dritten Spalte (?) bis zur letzten Spalte (derzeit 2019).
  • Auch wenn bei Datentyp: Ganze Zahl angezeigt wird, explizit noch einmal Ganze Zahl wählen.
  • Wenn Sie ein neues Jahr in den Rohdaten erstellt haben und dort noch keine Daten enthalten sind, muss für diese Spalte (beispielsweise 2020) auch einmalig dieser Datentyp gesetzt werden. 
  • Bestätigen Sie mit OK.

Im nächsten Schritt erstellen Sie aus dieser Abfrage ein (1) Duplikat als Verweis: Register Start, Gruppe Abfrage | Verwalten | Verweis. Wechseln Sie erforderlichenfalls zur neu erstellten Abfrage mit der Bezeichnung Tabelle 1 (2) durch einen Klick im linken Seitenfenster auf diesen Eintrag. Falls links der Abfrage nur ein schmaler Streifen mit dem Text Abfrage sichtbar ist, einfach darauf klicken. – Hinweis: Auch wenn die der Erstellung der Verweis-Abfrage nicht in jedem Fall erforderlich ist so halte ich das für eine gute Vorsichtsmaßnahme. „Man“ weiß ja nie, ob die Original-Daten noch einmal gebraucht werden oder irgendwie im Kern angepasst werden sollen.

Markieren Sie hier in Tabelle1 (2) die Spalten Song und Band/Interpret und in der Gruppe Transformieren wählen Sie Gruppieren nach (das geht übrigens auch über einen Rechtsklick). Den Inhalt des Feldes Neuer Spaltenname (Anzahl) können Sie so belassen. Bei Vorgang wählen Sie den Eintrag Alle Zeilen. Spalte ist zwar nicht ausgegraut, Sie können aber dort dennoch nichts eintragen.

Klicken Sie nun auf Aggregation hinzufügen und tragen Sie in das leere Feld unterhalb  Neuer Spaltenname beispielsweise Summe ein. Vorgang ist hier naturgemäß Summe und bei Spalte wählen Sie in der gleichen Zeile die Spalte ?. Das Ganze zeigt sich nun so:

Eine etwas aufwändigere Gruppierung der Daten

Eine etwas aufwändigere Gruppierung der Daten

Das Ergebnis wird Sie vielleicht etwas überraschen:

Das Ergebnis ist vielleicht irritierend, aber korrekt

Das Ergebnis ist vielleicht irritierend, aber korrekt

Verschieben Sie nun die Spalte Summe an die 3. Position, also nach Band/Interpret. Entweder klicken Sie dazu in die Überschrift der Spalte Summe und ziehen Sie diese per Hand dort hin, alternativ geht das auch über das Kontextmenü, wenn Sie die Überschrift markiert haben: Verschieben | Links.

Die nun letzte Spalte hat ja die vielleicht irritierende Überschrift Anzahl, aber das ist in diesem Fall nicht wirklich relevant. Aber falls es Ihnen lieber ist, ändern Sie die Überschrift auf Jahre. Ich habe das auch einfach einmal der Transparenz wegen getan. Was aber gewiss befremdlich sein kann ist, dass jede einzelne Zeile den Inhalt Table hat. Klicken Sie dort zum Erweitern auf den Doppelpfeil Doppelpfeil. Und der Dialog, welcher sich nun öffnet, ist für sie wahrscheinlich auch nicht viel aussagekräftiger. 😉 Aber in diesem Fall sieht es vielleicht schlimmer aus, als es ist; Sie werden gar nicht so übermäßig viel tun müssen. 😎 

Einige Häkchen setzen und entfernen, und das klappt dann auch …

Einige Häkchen setzen und entfernen, und das klappt dann auch … 😉

Zuerst achten Sie darauf, dass die Options-Schaltfläche Aggregieren aktiviert ist. Anschließend markieren Sie die oberste Auswahl (Alle Spalten auswählen). Entfernen Sie dann alle Häkchen, wo keine Jahreszahl steht (also bei den ersten 3 Spaltennamen) und auch das Häkchen ganz unten bei Ursprünglichen Spaltennamen als Präfix verwenden. Klicken Sie nun auf OK, um den Dialog zu schließen.

Das war’s prinzipiell auch schon. Schließen & laden wird die Abfrage schließen und in ein Arbeitsblatt schreiben. Aber da hier mit Sicherheit auch „Spezis“ mitlesen …

Wissensstand: Level 3 ⇒ Solide Erfahrung, gute Kenntnis der meisten Symbole   

… Und (nicht ausschließlich) für die habe ich noch ein kleines „Schmankerl“. Die Überschriften der soeben berechneten Spalten für die einzelnen Jahre enthalten ja alle den Zusatzsumme von. Natürlich können die alle einzeln bereinigt werden, indem sie die Überschrift von Hand ändern. Wenn Sie aus Gründen der Sicherheit eine Kopie der fertigen *.xlsx anlegen, dann probieren Sie doch einmal folgendes: Öffnen Sie die Abfrage Tabelle1 (2) und positionieren Sie im rechten Seitenfenster die Markierung auf die Zeile Aggregierte Anzahl. Das sollte auch der letzte Eintrag in der Liste sein. Erweitern Sie nun die Editierzeile oberhalb der Überschriften durch einen Klick auf den nach unten zeigenden Pfeil, das schafft mehr Überblick.  😉 Klicken Sie in den durch Power Query generierten Code und StrgA, um den gesamten Text dieses Bereich zu markieren:

Der Funktions-Bereich ist per Strg-A komplett markiert worden

Der Funktions-Bereich ist per Strg-A komplett markiert worden

Das Markieren des Bereichs mit den durch Power Query erstellten Code ist natürlich kein Selbstzweck gewesen. StrgC, um den markierten Text in die Zwischenablage zu kopieren. Öffnen Sie nun einen beliebigen Text-Editor (keine Textverarbeitung wie MS Word, etc.) sondern Notepad (Editor) oder noch besser Notepad++, ein kostenloser, freier Editor der Extraklasse.

Fügen Sie im Editor in einem garantiert leeren Dokument den Inhalt der Zwischenablage beispielsweise mit StrgV ein. Hier bietet sich nun die Möglichkeit, den Text „Summe von “ (mit dem angehängten Leerzeichen) durch nichts zu ersetzen. Markieren Sie nun hier im Editor komplett den korrigierten Text, kopieren ihn in die Zwischenablage, wechseln wieder in den Power Query-Editor und fügen den Inhalt der Zwischenablage in den immer noch markierten Editor- bzw. Formelbereich ein. Erforderlichenfalls werden Sie nochmals den kompletten Text vorher mit StrgA markieren. Anschließend ein Klick irgendwo in die Daten und die Überschriften sind wieder „reine“ Jahreszahlen.

▲ nach oben …


  Wissensstand: Level 2 ⇒ Solides Basiswissen in PQ, etwas Erfahrung   

Und weil das Jahr einen guten Abschluss haben soll, hier noch einmal die Lösung für Anwenderinnen und Anwender mit fortgeschrittenem Wissensstand in Sachen Power Query. Eine kleine Einschränkung muss ich allerdings machen: Mein Ehrgeiz ist es, diesen Beitrag noch im Jahr 2018 zu veröffentlichen. Und da ab ca. 20:00 Uhr wirklich nur Familie angesagt ist, bleiben mir nur noch weniger als 4 Stunden, um den Text zu verfassen, alles Korrektur zu lesen (und natürlich bei Bedarf zu verbessern) und schlussendlich zu veröffentlichen. Darum wird manches eher in Stichworten behandelt, nicht so detailliert sein, wie vielleicht an anderen Stellen hier im Blog. Bitte haben Sie dafür Verständnis. – Sie dürfen mich aber gerne darauf hinweisen, wenn etwas unverständlich oder allzu knapp dargestellt wurde, da werde ich dann gewiss nachbessern. 😳 

OK, let’s go! (Hinweis: Hier ist die Vorgehensweise ab Excel 2016 beschrieben!)

  • Die importierte Datei liegt auf C:\Data. 
  • Neue, leere Excel-Mappe, Daten, Aus Datei | Aus Arbeitsmappe.
  • Wechsel zu C:\Data, die eben geladene Datei markieren und auf Importieren klicken. (Eventuell den Pfad anpassen)
  • In der linken Hälfte Tabelle1 markieren und rechts vergleichen, ob es die richtigen Daten sind.
  • Erweitern Sie die Schaltfläche Laden und wählen Sie Laden in…
  • Öffnen Sie die eben erstellte und durch PQ geschlossene Abfrage wieder.
  • „Registrieren“ Sie für sich selber, dass die erste Zeile eine durch Power Query erstellte, nicht aussagekräftige Überschrift bekommen hat.
  • Start, Gruppe Transformieren, Erste Zeile als Überschrift verwenden.
  • Sortieren Sie die erste und auch zweite Spalte aufsteigend.
  • Register (nicht Spalte!) Transformieren, die ersten beiden Spalten markieren und Spalten zusammenführen. Hinweis: Geht auch per Rechtsklick)
  • Als Trennzeichen beispielsweise den Tabstopp auswählen, dann OK.
  • Start, Gruppe Abfrage | Symbol Verwalten | Verweis. Alternative: Im linken Seitenfenster per Rechtsklick.
  • Zusammengeführt markieren, Rechtsklick in die Überschrift, und Gruppieren nach…
  • Neuer Spaltenname: Summe, Vorgang: Summe, Spalte: ?

Diese Abfrage besteht nun aus 2 Spalten: Zusammengeführt und Summe (die summierte Spalte ?). Weiter geht’s…

  • Erstellen Sie einen weiteren Verweis der Tabelle1.
  • Löschen Sie die Spalte ?.
  • Spalte Zusammengeführt markieren, Rechtsklick in die Überschrift, Andere Spalten entpivotieren.
  • Spalte Attribut markieren und Aufsteigend sortieren.
  • Register Transformieren, Gruppe Beliebige Spalte | Spalte pivotieren.
  • Werte-Spalte: Wert.
  • Klick auf den Text Erweiterte Optionen, Aggregatwertfunktion: Summe und OK.
  • Aktivieren Sie Tabelle1 (2).
  • StartKombinieren | Abfragen als neue Abfrage zusammenführen.
  • Wählen Sie im mittleren, kleinen Kombinationsfeld Tabelle1 (3).
  • Markieren Sie in beiden großen Kasten jeweils die Spalte Zusammengeführt durch einen Klick in die Daten oder die Überschrift.
  • Belassen Sie Join-Art bei der Vorgabe, per OK schließen.
  • Erweitern Sie in der Abfrage Merge1 die Spalte Tabelle1 (3) durch Klick auf den DoppelpfeilDoppelpfeil.
  • Entfernen Sie das Häkchen bei Zusammengeführt und unterhalb des Kastens bei Ursprünglichen Spaltennamen als Präfix verwenden.
  • Schließen Sie den Dialog.
  • Zusammengeführt markieren, Start, Spalte Teilen | Nach Trennzeichen | Tabstopp akzeptieren und Dialog schließen. (→ auch per Rechtsklick möglich)
  • Start, Schließen & laden 
  • Löschen oder verstecken Sie die Tabellenblätter Tabelle1 .. Tabelle3 und benennen Sie Tabelle4 nach eigenem Wunsch um.

Das war’s. Viel Text, ich weiß. Aber gerade für Einsteiger ist es mitunter wichtig, dass der Weg ausführlich, Schritt für Schritt beschrieben wird. Und im Abschnitt direkt hierüber habe ich ja auch gezeigt, dass es auch mit wenigen Stichworten machbar ist. 😉 

Danke fürs Lesen und vor einigen viel Erfolg beim umsetzen in die Praxis, falls Sie dieses Thema so interessant gefunden haben, dass sie es selber nachvollziehen wollen. Und eventuell können Sie ja den einen oder anderen Tipp auch in Ihren eigenen Projekten einsetzen.

▲ nach oben …


▲ nach oben …

Hat Ihnen der Beitrag gefallen?
Erleichtert dieser Beitrag Ihre Arbeit?

Dann würde ich mich über einen Beitrag Ihrerseits freuen …

Dieser Beitrag wurde unter Datentyp anpassen, Foren-Q&A, Ohne Makro/VBA, Power Query, Spalten bearbeiten, Wege nach Rom abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.