OK, das Wort­spiel musste sein 😎. Denn fein wird das hier gewiss, ins­beson­dere im Gegen­satz zum vorheri­gen Teil. Noch ein­mal zur Auf­frischung oder als Basis-Erk­lärung: Die Bäck­erei Klein­brot hat mit Excel monatliche Berichte erstellt, wo für jeden Tag des Monats in Spalte A das Datum einge­tra­gen wurde und in Spalte B:G die Umsätze der ver­schiede­nen Pro­duk­t­grup­pen. Alle Monate wur­den zu ein­er einzi­gen großen Tabelle zusam­menge­fügt. Das stellte (und stellt) sich dann so dar:

Die Jahreszusam­men­fas­sung in Monate und Tage aufgeteilt.

Sie erken­nen, dass Sonn- und Feiertage zwar in Spalte A aufge­führt sind aber da das Geschäft geschlossen war, wurde auch kein Umsatz gemacht. Die entsprechen­den Felder bleiben leer. Und da das Jahr 2014 kein Schalt­jahr war, gibt es 365 Zeilen mit Dat­en plus 1 Zeile für die Über­schrift..

Diese Anord­nung der Dat­en ist zwar in dieser Form üblich und auch über­sichtlich, aber für eine vernün­ftige Piv­ot-Auswer­tung aus­ge­sprochen ungeeignet. Im ersten Teil wur­den diese Dat­en in der Anord­nung für eine Piv­ot-Tabelle zwar genutzt, aber es gab doch so einige Hür­den zu über­winden. Und die weni­gen Möglichkeit­en, die dort erar­beit­et wor­den sind, streiften schon das mögliche Lim­it. Ganz zu schweigen von dem hohen Aufwand, der mitunter betrieben wer­den musste.

▲ nach oben …

Pivot-gerechte Umstrukturierung

Mit einem verän­derten Auf­bau der Dat­en in der Form, wie sie für eine PT vorge­se­hen ist wer­den Sie viel leichter, logis­ch­er und schneller zum Ziel gelan­gen. Sie erken­nen, der Auf­bau ist ganz sim­pel:

Die ersten 2 Tage in kor­rek­ter Anord­nung

Drei Spal­ten, nicht mehr. Das Datum, die Pro­duk­t­gruppe und der Umsatz des entsprechen­den Pro­duk­ts an dem Tag. 6 Pro­duk­te je Tag umfassen 6 Zeilen. 2 Tage nehmen 12 Zeilen in Anspruch. Und weil an Tagen, die nicht geöffnet waren auch kein Umsatz gener­iert wor­den ist, tauchen diese kalen­darischen Dat­en auch nicht in der Liste auf. Sie wür­den unnötig Platz weg­nehmen. Ins­ge­samt sind es 304 Tage á 6 Zeilen plus der Über­schrift. Ins­ge­samt 1.825 Zeilen.

Wir wollen Ihnen nicht die Chance nehmen, dass Sie auf welchem Wege auch immer sel­ber die kor­rek­te, eben gezeigte Basis­datei erstellen. Dazu brauchen Sie diese Datei, wo auss­chließlich die an den 365 Tagen zusam­menge­fassten Monat­sum­sätze der einzel­nen Pro­duk­t­grup­pen enthal­ten sind, also nicht die einzel­nen Monate in getren­nten Blät­tern. Und dabei gle­ich unsere Empfehlung: Nicht nur, damit Sie weniger Arbeit haben son­dern auch der „Daten­hy­giene” wegen: Tage, an denen keine Umsätze getätigt wor­den sind, gle­ich raus lassen. Da soll ja auch nichts aus­gew­ertet wer­den. Und das sind nicht nur Son­ntage, auch an Feierta­gen war der Laden geschlossen.

Die ein­fach­ste Lösung für die Umwand­lung der Liste mit den 365 Tage­sum­sätzen scheint auf den ersten Blick zu sein, einen extern­er Helfer einzuset­zen. Stich­wort: Prak­tikant bzw. Prak­tikan­tin. Aber das ist wirk­lich nicht fair und gewiss auch nicht angemessen. Es gibt doch wohl auch andere Wege, oder?

So ist es. Die eine Möglichkeit beste­ht darin, eine Formel einzuset­zen. Die mögen wir uns hier an dieser Stelle aber nicht antun; bei ein­er anderen Gele­gen­heit wer­den wir das gewiss ein­mal „durchziehen”.  😉 Was aber rasch und wirk­lich trans­par­ent und grandios ein­fach funk­tion­iert: Pow­er Query. Ab Excel 2010 kön­nen Sie dieses Add-In ein­binden und es hat ‑wenn Sie es ein­mal „intus” haben- einen unbezwing­baren Charme und erle­ichtert die Arbeit in vie­len Fällen enorm. Entwed­er in jedem einzel­nen Monat oder in der zusam­mengestell­ten Jahres­Liste einge­set­zt führt die Durch­führung zu 12 größeren Daten­blöck­en, welche Sie dann zu ein­er einzi­gen kom­pak­ten Tabelle zusam­men­fü­gen oder in der 365-Tage-Tabelle gle­ich zu der einen, großen Liste.

Unsere Alter­na­tive heißt VBA. Mit einem Makro wer­den alle 12 einzel­nen Blät­ter der Monate automa­tisch aus­gew­ertet und die der Forderung entsprechen­den Dat­en ohne Ein­griff eines Benutzers in ein definiertes Tabel­len­blatt mit dem Namen Umsatz 2014 geschrieben. Die umsat­zlosen Tage sind natür­lich erst gar nicht in die Liste einge­fügt wor­den, anson­sten gibt es ‑der Anforderung entsprechend- für jeden Tag 6 Zeilen mit einem der 6 Pro­duk­t­grup­pen und dem jew­eili­gen Umsatz. Diese Tabelle (oder eine iden­tis­che von Ihnen erstellte, welche auch dem Muster der obi­gen Abbil­dung entspricht) ist die Daten­ba­sis für die kün­ftige Piv­ot-Tabelle. In diesem Sem­i­nar wer­den Sie naturgemäß immer mit unseren Dat­en arbeit­en, welche wir Ihnen auch stets zur Ver­fü­gung stellen. – Das soll Sie natür­lich nicht daran hin­dern, mit Ihren eige­nen Dat­en zu exper­i­men­tieren.  😀 

▲ nach oben …

Erstellen der Pivot-Tabelle

Die ersten Schritte entsprechen denen, die Sie bere­its ken­nen. Sie Klick­en in ein beliebiges Feld der eben erstell­ten Daten­ba­sis oder laden Sie diese Datei von unserem Blog. Klick­en Sie in den Daten­bere­ich und wählen Sie im Menü den Weg, eine neue Piv­ot-Tabelle zu erstellen. Kon­trolle, ob der kom­plette Bere­ich auch von der „Ameisenkolonne” einge­fasst wird und im Dialogfen­ster OK anklick­en. Und ab jet­zt wird es erhe­blich kom­fort­abler als Sie es im ersten Teil ken­nen gel­ernt haben …

Auf den ersten, aber spätestens beim zweit­en Blick wird Ihnen auf­fall­en, dass die Feldliste enorm geschrumpft ist:

Die Feldliste des neuen Tabel­lenauf­baus

Nur noch drei Felder, erstaunlich. Wo sind die einzel­nen Pro­duk­te geblieben? Um das her­auszubekom­men ziehen Sie hier das Feld Pro­duk­t­gruppe nach unten in den Bere­ich SPALTEN. Über­raschung, aber eine pos­i­tive. In den Spal­ten A:G sind alle 6 Pro­duk­te aufge­führt. Und das mit jew­eils dem Namen, wie er in der Quell­datei ver­wen­det wor­den ist. Das ist gegenüber der ersten Ver­sion schon ein­mal eine enorme Erle­ichterung. Und alleine das ist schon ein Beleg dafür, dass die Umstruk­turierung der Basis­dat­en in die neue Form dur­chaus sin­nvoll ist.

Als näch­stes soll das Datum in die Zeilen. Ganz nor­mal nach unten ziehen und alle 12 Monate ste­hen in Spalte A, die Pro­duk­te sind um 1 Spalte ver­schoben wor­den. Jet­zt noch den Umsatz in den Bere­ich WERTE und das war’s. Sie sind mit diesen weni­gen Mausklicks mit Aus­nahme der For­matierung als Währung auf dem Stand, der für Teil 1 als Ziel vorgegeben war. Selb­st die Sum­men für den einzel­nen Monat, welche wir in der ersten Auflage über ein berech­netes Feld erstellen mussten, sind hier automa­tisch einge­fügt wor­den. So macht das arbeit­en Spaß!

▲ nach oben …

Erste Formatierung

Bleibt noch die For­matierung als Währung. Und auch das ist nun ein Kinder­spiel gewor­den. Klick­en Sie mit rechts in irgen­deine Umsatz­zahl und wählen Sie dann aus dem Kon­textmenü die Wert­felde­in­stel­lun­gen. Unten links in dem neuen Fen­ster ist die Schalt­fläche Zahlen­for­mat. Ein Klick darauf und es öffnet sich das typ­is­che For­matierungs­fen­ster. Nur dass hier auss­chließlich das Reg­is­ter für Zahlen einge­blendet ist. Ein Klick auf Währung und nacheinan­der in bei­den Fen­stern OK. Fer­tig, genau so sollte es sein.

KleinPivot Übersicht (Grau: In Vorbereitung)

▲ nach oben …

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!)