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

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

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

Auswertung in der aktiven Arbeitsmappe

Dieser Beitrag ist als Ergänzung zu den beiden vorherigen des gleichen Themas gedacht, Teil 1 und Teil 2. Darum wird vieles auch nur Stichpunktweise erörtert. Der wesentliche Unterschied besteht darin, dass die 12 Monate des Jahres nicht in einer externen, extra zu öffnenden Datei liegen sondern im gleichen Workbook.

Ausgangslage ist wiederum diese Datei mit den Umsätzen der Bäckerei Kleinbrot, hier die Zahlen des gesamten Jahres. Nun soll in einem getrennten Blatt die Zusammenfassung aller Monate geschehen. Natürlich bleibt das Register Feiertage außen vor, dort sind keine auswertbar Zahlen vorhanden. Sie starten damit, dass sie die Datei ganz normal in Excel öffnen:

Die Ursprungs-Daten für den Januar

Die Ursprungs-Daten für den Januar

Sorgen Sie dafür, dass das Blatt Januar aktiv und eine beliebige Zelle innerhalb der Daten markiert ist. Sie aktivieren nun die Funktionalität für das Power Query (Menüpunkt Power Query oder Daten). In der Menüleiste erkennen sie nun den Punkt Von Tabelle bzw. Aus Tabelle. - Ein Klick darauf, Bestätigung dass die Tabelle Überschriften enthält und in der Query Editor wird sich mit den Daten dieser Tabelle öffnen. Ein Nebeneffekt, den sie später noch sehen werden: Die Daten im Arbeitsblatt werden auch als echte Tabelle formatiert. Das Ganze stellt sich im Abfrage Editor nun so dar:

Nach dem Import im Abfrage-Editor

Nach dem Import im Abfrage-Editor

Idealerweise geben Sie im rechten Seitenfenster dieser Abfrage einen „sprechenden“ Namen, beispielsweise qry_Januar und anschließend rufen Sie den Punkt Schließen & laden auf. 

Dann Wechsel zum Blatt Februar, die aktive Zelle ist in den Daten, Aus/Von Tabelle, Namen der Abfrage ändern und auch diese Abfrage Schließen & laden. – Das gleiche Vorgehen mit den restlichen Blättern der Monate März bis Dezember. Im rechten Seitenfenster sind nun die Abfragen für jeden Monat des Jahres zu sehen:

Jeder Monat 1 Abfrage …

Jeder Monat 1 Abfrage …

▲ nach oben …

Aus den vorigen Teilen zu diesem Thema wissen Sie, das an den Tabellen noch etwas geändert werden muss. Beispielsweise soll die Spalte Summe gelöscht werden, gleichermaßen auch die jeweils letzte Zeile mit der Summe der einzelnen Produkte. Zusätzlich ist es eine Überlegung wert, ob nur die Tage mit getätigten Umsatz dargestellt werden sollen oder jeder Tag des Jahres. Und letztendlich sollen in der Auswertungsspalte Datum auch „vernünftige“ kalendarischen Daten und nicht die serielle Zahl stehen.

Vielleicht ist ihre erste Überlegung, jeden Monat einzelnen anzufassen und dort die Änderungen vorzunehmen. Das wäre aber keineswegs zweckmäßig, weil sich das Ganze in einem Rutsch erledigen lässt. Also werden sie damit beginnen, die Monats-Abfragen in eine neue, gemeinsame Abfrage zu integrieren. Klicken Sie im rechten Seitenfenster mit rechts auf die Abfrage für den Januar und wählen im Kontextmenü den Punkt Duplizieren. Aus dieser Abfrage wird nun ein Duplikat erstellt, welches Sie Schritt für Schritt um die restlichen Monate des Jahres ergänzen werden. Dass es sich um das Duplikat handelt erkennen sie am neu vergebenen Namen; er wurde eine Leerstelle und dann in Klammern die Zahl (2) ergänzt. Nun ein Klick auf die Schaltfläche Kombinieren und in dem Kontextmenü wählen Sie Abfragen anfügen. Markieren Sie die Auswahl Drei oder mehr Tabellen und fügen nach und nach die restlichen Monate des Jahres durch markieren und Klick auf die Schaltfläche Hinzufügen in den rechten Bereich ein. Spätestens jetzt sollten Sie diese Abfrage umbenennen, ich verwende den Namen qry_Jahr.

▲ nach oben …

Welchen Datentyp haben eigentlich die Daten in der Spalte Datum? Normalerweise sollte es ja Datum/Uhrzeit sein, aber das ist aus gutem Grunde hier nicht der Fall. Es gibt ja noch die Zeilen, wo in Spalte A das Wort Summe enthalten ist. Und genau diese Zeilen sollen ja auch entfernt, gelöscht werden. Und da ja sowieso nur das Datum ohne Uhrzeit in der ersten Spalte stehen soll, ändern Sie den Datentyp einfach auf Datum.

Und nun schauen Sie mal, was in der 1. Spalte nach dem 31. Januar steht: error. Es ist ja ganz logisch, denn ein Text kann nicht in einen Datumswert umgewandelt werden. Und genau diese Fehlermeldung machen sie sich zunutze. Achten Sie darauf, dass die Spalte Datum markiert ist und wählen Sie in der Gruppe Zeilen verringern das Symbol Zeilen entfernen. Im DropDown bietet sich dann förmlich der letzte Punkt Fehler entfernen an. Bingo, das war’s! Für das ganze Jahr sind jetzt nur noch die kalendarischen Daten-Zeilen enthalten. – Dass sie die letzte Spalte (Summe) per Rechtsklick in die Überschrift löschen können, das wissen Sie bereits.

Wenn Sie für jeden Tag des Jahres einen Eintrag haben wollen, können Sie es dabei belassen. Wollen Sie aber nur Tage mit Umsatz in der zu erstellenden Tabelle darstellen, dann sollten Sie den letzten Schritt, das Entfernen der Spalte Summe rasch wieder rückgängig machen. Einfach im rechten Seitenfenster auf das loeschkreuz_schritte neben dem entsprechenden Eintrag klicken. Denn hier können Sie am ehesten feststellen, ob an dem Tag Umsatz gemacht worden ist oder nicht. Filtern Sie diese Spalte einfach auf >0 und es bleiben nur noch die gewünschten Werte übrig. Ich war beim 1. Mal so bequem, dass ich den normalen Filter verwendet hatte nur bei der 0 das Häkchen entfernt hatte. Das ging aber schief, denn an diversen Tagen war in der Summenspalte statt der Ziffer 0 der Wert null, welcher für ein leeres, inhaltsloses Feld steht. Darum empfehle ich, den Zahlenfilter entweder mit Größer 0 oder Ist nicht gleich… 0 zu wählen. Jetzt werden sie natürlich wiederum die letzte Spalte (Summe) löschen. Diese brauchen sie nun wirklich nicht mehr.

Das Ziel ist erreicht, sie können diese endgültige Abfrage per Schließen & laden in ein neues Tabellenblatt speichern.Das Datum ist schon korrekt formatiert, die restlichen Spalten können Sie bei Bedarf noch als Währung formatieren. – Wenn Sie die Daten noch mit einer PivotTable auswerten wollen, dann ist das mit diesem Tabellenaufbau nicht möglich. Aber hier im Blog zeigen wir Ihnen auf, wie sie solch eine Kreuztabelle so umbauen können, dass sie ideal per PT ausgewertet werden kann. – Theoretisch könnten sie die Arbeitsblätter mit den einzelnen Monaten aus der Mappe löschen, sogar die Aktualisierung geht danach noch; ich empfehle ihn jedoch, diese nur zu verstecken. Das Blatt mit der Jahres-Zusammenfassung sollten Sie entsprechend noch sinnvoll umbenennen.


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,00  freuen …

Dieser Beitrag wurde unter Daten zusammenführen, Datentyp anpassen, Filtern & Sortieren, Power Query abgelegt und mit , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.