Gegeben ist diese Excel-Mappe, wo für jeden Monat ein Arbeits­blatt existiert. Zusät­zlich gibt es noch ein Sheet, wo die Feiertage des Jahres ver­merkt sind. Alle Monats-Blät­ter sind iden­tisch aufge­baut, die Anzahl der Tage vari­iert natür­lich zwis­chen 28 und 31. Die eigentlichen Dat­en sind bis Mitte März bere­its erfasst. Die Umsätze wer­den täglich einge­tra­gen und natür­lich auch weit­er­hin erfasst. Und ein weit­er­er wichtiger Punkt: Die Dat­en ein­schließlich der Über­schriften sind bis zum Ulti­mo des jew­eili­gen Monats als Intel­li­gente Tabelle for­matiert, auch wenn noch kein­er­lei Umsätze einge­tra­gen wor­den sind. Das ist eine Voraus­set­zung für die hier vorgestellte Lösung.

Es soll in ein­er getren­nten Datei auf einem Blatt eine Zusam­men­fas­sung erstellt wer­den, die immer den tage­sak­tuellen Stand abbildet. Ein­er­seits soll dieses eine fort­laufende Tabelle sein, ander­er­seits ist es später das Ziel, die erfassten Dat­en in ein­er Piv­ot­Table zumin­d­est min­i­mal­is­tisch auszuw­erten.

Öff­nen Sie zu Beginn den Bere­ich, wo Sie die Pow­er Query Funk­tion­al­ität nutzen kön­nen (⇒ mehr). Das ist der gle­ich­namige Menüpunkt in Excel ²⁰¹⁰⁄₁₃ und das Menü Dat­en in Excel 2016. Die Beschrei­bung hier bezieht sich auf Excel 2016. – Erstellen Sie eine neue Abfrage aus ein­er Datei:

Abfrage aus ein­er Excel-Datei erstellen (2016)

Als Quelle biet ich Ihnen das oben genan­nte File an. Nach der Auswahl per Dop­pelk­lick oder dem Klick auf Importieren zeigt sich dieser Dia­log:

Der Inhalt der gewählten Arbeitsmappe, Arbeits­blät­ter und Tabellen

Gelb gemark­ert ist hier der Hin­weis, dass 26 Ele­mente aus­gewählt wer­den kön­nen. Für 12 Monate scheint das etwas viel … 😉 Aber schauen Sie sich gerne ein­mal die bei­den (markierten) Monate April an. Ein­mal mit dem Prä­fix tbl_ und ein­mal ohne. Das bet­rifft übri­gens alle weit­eren Ein­träge gle­icher­maßen. Der Hin­ter­grund: tbl_Januar (als Beispiel) ist eine «Intel­li­gente» Tabelle mit genau diesem Namen, Jan­u­ar ist der Name des Tabellenblatts. Das Sym­bol links der Beze­ich­nung ist entsprechend unter­schiedlich, auch wenn der Name ein­mal iden­tisch sein sollte. Da Pow­er Query so oder so eine Liste/Tabelle erwartet, wählen Sie doch gle­ich die for­matierten Tabellen als Quelle aus.

Da Sie ja mehrere Objek­te markieren und auswählen wollen, set­zen Sie zuerst das Häkchen ganz oben bei Mehrere Ele­mente auswählen. Ein Klick auf tbl_April, Shift und ein Klick auf tbl_September. Dann noch ein Klick etwa in der Mitte der Auswahl auf tbl_Feiertage um die Markierung wieder zu ent­fer­nen, denn diese Dat­en brauchen Sie in dieser Auswer­tung nicht. Ach ja, seit „Gen­er­a­tio­nen” erlebe ich die „Macke”, dass das zuun­ter­st angek­lick­te Kästchen bei gedrück­ter Shift-Taste nicht aktiviert wird. Also ein­fach auch dieses Kästchen noch mit einem ein­fachen Klick markieren. – Sie kön­nten jet­zt auf Laden Klick­en, aber da Sie die Dat­en noch bear­beit­en wer­den, Klick­en Sie auch auf die Schalt­fläche Bear­beit­en bzw. in neueren Ver­sio­nen auf Dat­en trans­formieren.

Die Dat­en wer­den ein­ge­le­sen und Monat für Monat in je ein­er eige­nen Abfrage im Pow­er Query-Edi­tor dargestellt. Im linken Seit­en­fen­ster genügt ein Klick auf einen Monat und die Dat­en wer­den im mit­tleren Bere­ich entsprechend angezeigt:

Darstel­lung der Dat­en im Edi­tor, hier der Monat Jan­u­ar

Sie erken­nen, dass es 12 einzelne Abfra­gen sind. Prinzip­iell der Spiegel der einzel­nen Tabellen. Das Ziel ist ja, alle 12 Monate in 1 Tabelle/Liste zusam­men­z­u­fassen, zu kom­binieren. Und da bietet sich sehr weit rechts im Menüband, direkt neben der Gruppe Trans­formieren die Schalt­fläche Kom­binieren an. Ein Klick auf diese Schalt­fläche und Sie sollen sich entschei­den, ob Sie Abfra­gen zusam­men­führen oder anfü­gen wollen. Sie wer­den an die derzeit aktive Abfrage die anderen Abfra­gen Anfü­gen. Im sich öff­nen­den Fen­ster …

Der Dia­log, um an die aktuelle Abfrage weit­ere anzufü­gen

… markieren Sie erst den Punkt Drei oder mehr Tabellen und markieren links den zweit­en Monat in der Liste der ver­füg­baren Tabellen, dann Hinzufü­gen. Sie sehen den Erfolg sofort auf der recht­en Seite. Das wieder­holen Sie (in älteren Ver­sio­nen) für den drit­ten bis zwölften Monat. Lei­der ist eine Mehrfach­markierung nicht möglich, auch ein Dop­pelk­lick zeigt keine Wirkung. Ungewöhn­lich für Microsoft, aber hier ist aus mein­er Sicht Nachbesserungs­be­darf gegeben. Und in neueren Excel-Ver­sio­nen geht das auch! Wenn alle Monate in der recht­en Liste aufge­führt sind, OK.

Die Abfrage tbl_April ist immer noch markiert, in der Sta­tuszeile unten links ste­ht, dass sie 7 Spal­ten und 365 Zeilen enthält. Pri­ma, 2014 war kein Schalt­jahr, also passt das mit den 365 Tagen auch. Im recht­en Seit­en­fen­ster sehen Sie, dass zwar (min­destens) eine Abfrage ange­fügt ist und dass der Name immer noch tbl_April ist:

Ändern Sie den Namen der Abfrage

Der besseren Iden­ti­fizierung wegen wegen ändern Sie den Namen der Abfrage bitte auf tbl_Zusammenfassung. Blät­tern Sie nun ein­mal durch die Daten­sätze. Sie erken­nen, dass eine sehr große Menge an Zeilen mit Aus­nahme des Datums keine Dat­en enthält. Und da stellt sich die Frage, was mit diesen Zeilen geschehen kann. Ein­er­seits soll der Jahresüberblick aktuell sein, ander­er­seits machen sich diese vie­len umsat­zlosen Tage nicht gut und erschw­eren auch die Trans­parenz der Liste.

Aber erst ein­mal soll nach dem Datum sortiert wer­den. Ein Klick in das Über­schrift-Feld auf    und Sie kön­nen Auf­steigend sortieren. In der zweit­en Spalte (Brot) Klick­en Sie auch auf das Drop­Down-Sym­bol und ent­fer­nen jet­zt das Häkchen bei (NULL), es ste­ht ganz oben, direkt unter Alles auswählen. OK und ein Blick in die Sta­tuszeile zeigt, dass nur noch 58 Zeilen vorhan­den sind. Jet­zt noch ein Klick auf Schließen & laden und prinzip­iell ist die Auf­gabe erledigt.

Aber Sie wer­den gewiss über­rascht sein, denn es wur­den 12 Tabel­len­blät­ter erzeugt und im recht­en Seit­en­fen­ster sind noch alle Abfra­gen sicht­bar. Klick­en Sie auf die Abfrage tbl_Zusammenfassung und sofort wird genau dieses Arbeits­blatt zum aktiv­en Sheet. Löschen Sie alle restlichen Blät­ter (nicht die Abfra­gen), die Sheets wer­den nicht mehr gebraucht. Ide­al­er­weise benen­nen Sie das Blatt mit der Zusam­men­fas­sung, dem Ergeb­nis der Abfrage auch um, beispiel­sweise Jahr 2014 oder Zusam­men­fas­sung.Und falls Sie das rechte Neben­fen­ster irri­tiert, kön­nen Sie es auch mit dem großen X neben Arbeitsmap­pen­abfra­gen schließen.

Der let­zte Ein­trag ist vom 10.03.2014, was ja auch der let­zte Tag mit Umsatz ist. Den ganzen Aufwand wer­den Sie natür­lich am 11.03. nicht noch ein­mal durch­führen wollen, nur weil an dem Tag die Umsätze aktu­al­isiert wor­den sind. Sie ahnen es, das brauchen Sie auch nicht. – Tra­gen Sie in der Tabelle mit den Roh-Dat­en am 11. März (oder auch weit­eren Tagen) beliebige Umsatz­zahlen ein und spe­ich­ern Sie die Mappe, sie brauchen sie aber nicht zu schließen. Wech­seln Sie wieder zur Zusam­men­fas­sung und im Menü Dat­en, Gruppe Verbindun­gen ein Klick auf Alle Aktu­al­isieren. Schon haben Sie das neue Ergeb­nis. – Apro­pos Ergeb­nis: Hier find­en Sie die fer­tige Auswer­tung.

Diese Liste kön­nen Sie nach Belieben bear­beit­en, for­matieren, auswerten. Im kleinen Rah­men auch als Piv­ot Tabelle. Wie Sie solch eine Kreuzta­belle dann auch noch wesentlich bess­er mit Piv­ot­Table auswerten kön­nen, das lesen Sie beispiel­sweise hier oder auch in diesem Beitrag nach. Übri­gens auch mit Hil­fe von Pow­er Query. 😎

▲ nach oben …

Rück­mel­dun­gen / Feed­back gerne per Mail an mich (G.Mumme@Excel-ist-sexy.de)

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

Dann würde ich mich über einen Beitrag Ihrer­seits z.B. 2,00 € freuen … (← Klick mich!)