Blätter einer Arbeitsmappe mit Power Query zusammenfassen (2)

Xtract: Mehrere oder auch alle Arbeitsblätter einer Mappe mittels Power Query zu einer einzigen Abfrage/Tabelle zusammenfassen. 2. Teil. Aufbau und Erweiterung.

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

(Fast) Alle Blätter einer Arbeitsmappe in einer Übersicht per Power Query zusammenfassen (2)

Hinweis: Dieser zweite Teil baut konsequent auf dem ersten auf. Darum sind gerade zu Beginn die Abläufe nur stichwortartig angesprochen und nicht -wie im vorherigen Beitrag zu diesem Thema- detailliert bebildert und beschrieben. Im Prinzip werden die gleichen Daten wie dort verwendet, nur dass jetzt Umsätze bis zum 10. April erfasst wurden und die Rohdaten nicht als Tabelle/Liste formatiert sind. Dadurch ergeben sich teilweise gravierend unterschiedliche Arbeitsweisen.

Öffnen Sie Excel „solo“ oder legen Sie zu Beginn erst einmal eine neue, leere Datei an. Erstellen Sie nach gehabtem Muster eine Neue Abfrage auf der Basis einer Excel-Datei (Arbeitsmappe) und wählen Sie dieses File für den Import aus. Im Navigator erkennen Sie 13 Einträge, die 12 Monate und Tabelle1. Dass Tabelle1 kein Arbeitsblatt sondern eine Intelligente Tabelle/Liste ist erkennen Sie daran, dass einerseits ein blauer Strich oberhalb des Gitters im Symbol ist und dass jedes der zwölf Arbeitsblätter unten noch zwei angedeutete Register hat.

Wählen Sie alle Monate aus und klicken dann auf Bearbeiten. ⇒ Haben Sie bemerkt, dass es auch ein Arbeitsblatt mit dem Namen Feiertage gibt? Das soll natürlich nicht mit ausgewertet werden. Da die Datei mit den Quelldaten wahrscheinlich geschlossen ist, dauert es unter Umständen einen Moment, bis der Query-Editor geöffnet ist und die Daten des Monats April anzeigt. Im linken Seitenfenster ist oben vermerkt: Abfragen: [12], womit klar ist, dass zumindest die Anzahl der importierten Blätter stimmt. Alle Monate stehen untereinander, schön nach dem Alphabet sortiert.

Falls Sie „im Eifer des Gefechts“ statt auf Bearbeiten zu klicken die Schaltfläche Laden erwischt haben, dann sehen Sie ja nicht den Query-Editor sondern es sind bereits alle 12 Monate als einzelnes Arbeitsblatt angelegt worden. Im rechten Seitenfenster sind auch die einzelnen Abfragen aufgeführt. Um wieder in den Abfrage-Editor zu kommen, beispielsweise im Seitenfenster ein Rechtsklick auf April und Bearbeiten oder ebenfalls dort den April markieren, Menü Abfragetools | Abfrage | Bearbeiten. Und als Geheimtipp: Einfach ein Doppelklick auf den gewünschten Monat, natürlich auch im rechten Seitenfenster. Natürlich dürfen Sie auch jeden anderen Monat zuerst aufrufen, denn es werden ja alle Monate zusammengefasst und Sie sortieren diese gewiss chronologisch.

Auch der nächste Schritt ist wie bereits geübt. Sie klicken auf die Schaltfläche Kombinieren und wählen dann Abfragen anfügen aus. Zuerst auf Drei oder mehr Tabellen klicken und dann nach und nach, Schritt für Schritt die weiteren Monate einzeln Hinzufügen und schlussendlich mit OK bestätigen. Bei solch nervtötenden Tätigkeiten bin ich immer wieder froh, dass sie für eine Aufgabe nur ein einziges Mal durchführen muss und nicht täglich. Und bei der Gelegenheit können Sie auch gleich im rechten Seitenfenster bei den Eigenschaften den Namen der Abfrage von April in Jahres-Übersicht oder einen Ihnen genehmen Namen ändern, so ein Name trifft ja eher den Kern der Sache.

Auf den ersten Blick hat sich sonst nichts in Bezug auf die vorherige Aufgabe geändert. Aber auf den zweiten schon, denn in der Statuszeile ist nun vermerkt, dass die Abfrage Jahres-Übersicht 391 Zeilen hat. Sie erinnern sich, beim letzten Mal waren es genau 365 Zeilen. Warum sind es jetzt mehr? Da muss ich noch einmal Ihre Erinnerung bemühen. Im ersten Teil dieser Übung haben Sie auf die Listen/Intelligenten Tabellen zugegriffen, und das waren Bereiche, die exakt die auszuwertenden Daten umfassten. Keine Summe-Zeilen und Spalten, „kein Nichts“. 😉 Und nun blättern Sie einmal durch die erstellte Jahres-Liste (Jahres-Übersicht) …

Sie erkennen, dass mindestens zwei Dinge anders sind: Die eben schon angesprochene Zeile Summe und darunter ist immer noch eine komplett leere Zeile, die ausschließlich aus dem Inhalt null besteht. Das sind Beispielsweise Zeile 31:32. Ach ja, und dass es dann noch eine Spalte Summe gibt hat zwar keinen Einfluss auf die Anzahl der Zeilen, ist aber auch ein Punkt, der erledigt werden sollte. OK, erledigen Sie den doch gleich zu Beginn. Rechtsklick in die Überschrift Summe und dann in Kontextmenü Entfernen auswählen. Das War’s auch schon. Alternativ hätten Sie natürlich auch in der Gruppe Spalten verwalten des Menüs Start den Punkt Spalten entfernen wählen können.

Nun sollen alle komplett leeren Zeilen entfernt werden. Auch wenn das gemeinsam mit den umsatzlosen Tagen machbar wäre, geht meine Empfehlung ganz klar in die Richtung der Einzelschritte. Sicher ist sicher! Im Menü Start neben der Gruppe Spalten verwalten ein Klick auf Zeilen verringern. Hier wählen Sie Zeilen entfernen | Leere Zeilen entfernen. Klicken Sie nun auf den DropDown-Pfeil DropDownPfeil in der ersten Spalte (Datum) und ziehen Sie den Rollbalken ganz nach unten. Beim letzten Eintrag Summe entfernen Sie das Häkchen und anschließend OK. In der Statuszeile steht nun ein Ihnen sehr bekannter Wert: 365 Zeilen.

Und die (vorerst einmal) restlichen Schritte unterscheiden sich nicht von der vorhergehenden Übung. Sie klicken bei Brot auch auf  DropDownPfeil und löschen die Markierung beim ersten Eintrag (NULL). Ach ja, vielleicht wundern Sie sich über die Zahlen mit einer Unmenge an Nachkommastellen. Die sind dadurch entstanden, dass ich die Umsätze per Zufallszahl generiert habe und manchmal kommt auch hier trotz Eingrenzung eine etwas „krumme“ Zahl heraus.

Wenn Sie mögen, können Sie ja die Spalte Datum aufsteigend sortieren. Das macht sich einfach besser. Jetzt noch Schließen & laden und die Aufgabe ist gelöst. Die überzähligen erstellten Tabellenblätter können Sie verstecken oder löschen und im Blatt mit der Zusammenfassung werden Sie vielleicht Spalte A als Datum formatieren müssen, die Umsätze machen sich gewiss besser als Währung. Und das Registerblatt der Tabelle verdient auch einen „sprechenden“ Namen … 😎 

…“werden Sie vielleicht als Datum formatieren müssen“ habe ich ja nicht umsonst so geschrieben. Es kann nämlich sein, dass die Spalte A schon als Datum formatiert ist. Dann haben Sie nämlich schon …

Aber tun wir einfach einmal so, als wenn in der Spalte die serielle Zahl steht. Und gehen wir der Sache einfach einmal auf den Grund.

  • Öffnen Sie den Query-Editor beispielsweise durch einen Doppelklick auf den August im rechten Seitenfenster. Der ist nämlich in jedem Fall noch „unberührt“.
  • Sehen Sie sich einmal genau die Überschrift der ersten Spalte an. Links ein Symbol, dann der Text der Überschrift.
  • Vergleichen Sie nun einmal mit der Überschrift eines Produkts. Dort ist nur ein anderes Symbol und natürlich ein anderer Text, sonst ist alles gleich.
  • Das Datum an sich ist rechtsbündig, also eine Zahl. Aktivieren Sie noch einmal die Überschrift Datum und schauen Sie nach, welcher Datentyp hier zugewiesen wurde:

Der zugewiesene Datentyp ist "Beliebig" und nicht Datum …

Der zugewiesene Datentyp ist „Beliebig“ und nicht Datum …

  • Wechseln Sie zur Abfrage mit der Jahres-Zusammenfassung.
  • Kontrollieren Sie, welcher Datentyp hier in der ersten Spalte zugewiesen wurde und korrigieren Sie (erforderlichenfalls) auf den Typ Datum.
  • Prüfen Sie vorsichtshalber auch einmal den Datentyp eines oder aller Produkte.
  • Schließen Sie das Editor-Fenster.

Sie erkennen, dass nun in jedem Fall ein Datum und keine serielle Zahl in Spalte A steht. Und Sie wissen nun, dass das „vielleicht“ nur dann zum tragen gekommen ist, wenn Sie im Query-Editor das Datum noch nicht umformatiert hatten.

▲ nach oben …

So, und nun bin ich richtig gemein. 😈 Öffnen Sie die Datei mit den Quelldaten, also Kleinbrot_12_Mon_bis_Anfang_April. Wählen Sie den Monat Oktober und tragen Sie am 3.10. für jedes Produkt eine 0 (null) ein. Es könnte ja sein, dass eine Praktikantin oder ein neuer Angestellter es besonders gut gemeint hat; und prinzipiell ist solch ein Eintrag ja auch korrekt, denn es wurde ja kein Umsatz generiert.

Speichern Sie die Änderungen, lassen aber die Datei zweckmäßigerweise geöffnet, Sie werden dort gleich noch einmal tätig werden.

Wechseln Sie wieder zu der Auswertung, ein Klick auf Abfrage | Aktualisieren oder Daten | Alle aktualisieren und schon ist die Auswertung um 1 Zeile länger geworden.

Warum die eine Zeile mehr? Sie erinnern sich, dass Sie im Abfrage-Editor die Spalte Brot gefiltert hatten und alle Werte mit null entfernt haben. Hier wird aber ganz deutlich: null ist nicht 0. Das erste ist ein Synonym für „nichts“, das zweite ist eine Zahl mit einem definierten Wert. Wenn Sie solche Zeilen auch ausblenden wollen, dann filtern Sie auch nach dem Wert  0.

Aber ich wäre nicht ich, wenn ich nicht noch ein Beispiel in petto hätte.  😆 Wechseln Sie zu den Basisdaten und tragen am Sonntag, 5. Oktober einen Umsatz von beispielsweise 60,00€ für Kuchen ein. Das war eine Vorbestellung und die wurde auch erst am Sonntag abgeholt und entsprechend auch dann erst verbucht. Natürlich wieder speichern und wenn Sie mögen, dann schließen Sie auch die Quelldatei. Zurück in der Abfrage einfach auf beliebige Weise Aktualisieren.

Hmmm, bringt nichts. Der eingegebene Umsatz wird nicht angezeigt. Aber vielleicht in der generierten Liste mit der Zusammenfassung? Also den Editor schließen und: Wieder nichts. Na gut, Menü Daten, Gruppe Verbindungen und Alle Aktualisieren. Das hilft sonst immer. Sonst ja, hier nicht. Können Sie sich denken, woran das liegt? Klar, die Spalte Brot hat an diesem Tag den Wert null und der sorgt dafür, dass die Zeile wegen des Filters nicht angezeigt wird.

Der Ausweg wird Ihnen nicht wirklich gefallen, sorgt aber dafür, dass Sie einerseits künftig alle Parameter möglichst im Vorwege abwägen (hier konnten Sie es nicht, ich habe Sie ja mit der neuen Situation überfallen) und andererseits werden Sie Gelegenheit haben, etwas mehr mit PQ zu üben.  😎

Solange Sie noch nicht so vertraut mit Power Query sind, werden Sie die gesamte Abfrage noch einmal neu aufbauen müssen. Mit folgendem Unterschied: Sie löschen die Summe-Spalte nicht gleich zu Beginn sondern erst ganz zum Schluss. Sie filtern auch nicht die Brot-Spalte nach irgend welchen Werten sondern verwenden stattdessen die Summe-Spalte. Filtern Sie diese so, dass null und 0 nicht angezeigt werden, und Sie haben den gewünschten Effekt. Und Sie haben es geschafft.


Blätter einer Arbeitsmappe zusammenfassen Teil 1
Blätter einer Arbeitsmappe zusammenfassen Teil 2
Blätter einer Arbeitsmappe zusammenfassen Teil 3

▲ nach oben …

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

Dann würde ich mich über einen Beitrag Ihrerseits z.B. 2,50  freuen …

Dieser Beitrag wurde unter Daten zusammenführen, Daten-Import / -Export, Musterlösungen, Ohne Makro/VBA, Power Query, Tabelle und Zelle, Vergleichen (Daten, Tabellen, Abfragen) abgelegt und mit , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.