Der Stand der Dinge

Mehrere Abteilun­gen eines Unternehmens liefern Verkaufs­dat­en in eine einzige Excel-Tabelle. Das führt dazu, dass ein Kunde am gle­ichen Tag oder an ver­schiede­nen Tagen mehrfach in der Monat­süber­sicht auf­taucht. Der Kunde 4711 ist ein­er der „Kan­di­dat­en”. Die Tabelle kann hier herunter geladen wer­den und stellt sich etwa so dar:

Auss­chnitt aus der Roh-Dat­en Tabelle

Sie erken­nen, dass in Spalte A die Kun­den­num­mern einge­tra­gen sind, in Zeile 1 ab Spalte B die Arbeit­stage, hier für den Jan­u­ar 2014. Unter­halb des Datums sind die ver­schiede­nen Umsätze einge­tra­gen.

▲ nach oben …

Die Forderung

So, wie die Tabelle derzeit vor­liegt, lässt sie sich nicht wirk­lich leicht auswerten. Darum wird fol­gende Forderung aufgestellt: Statt in ein­er Kreuzta­belle sollen alle Kun­den mit ihren jew­eili­gen Umsätzen und dem Datum in ein­er neuen Tabelle in drei Spal­ten erfasst wer­den. Jede Kun­den­num­mer (ist hier iden­tisch mit der Zeile, auch wenn der Kunde mehrfach in Spalte A erscheint) soll in eine Zeile, daneben das Datum des Kaufs und der Umsatz. Für den ersten Kun­den aus der Liste (KdNr. 4582) sähe das dann so aus:

Die ersten Zeilen des Wun­sch-Ergeb­niss­es

Der Über­sicht wegen habe ich hier auch die erste Zeile des 2. Kun­den mit einge­fügt.

Zugegeben, es geht mit reinen Excel-Funk­tio­nen. Aber die Formel ist nicht wirk­lich sehr über­sichtlich und trans­par­ent. Und wenn sich dann ein­mal etwas ändert, ist extrem viel Aufmerk­samkeit erforder­lich. Darum wählen wir hier den Weg über ein VBA-Pro­gramm. Diese Rou­tine ist sehr flex­i­bel und passt sich automa­tisch an die Größe der Tabelle an. – Selb­stver­ständlich bleibt es Ihnen unbenom­men, Teile des Makros anzu­passen, soweit erforder­lich. Bitte belassen Sie auch in dem Fall den Kom­men­tar-Kopf mit dem Hin­weis auf das Copy­right im Makro-Bere­ich.

Apro­pos Formel: In Tabelle Monat ste­ht in Zeile 1 ab Spalte C eine Formel. Vielle­icht kön­nen auch Sie davon prof­i­tieren, auf die Schnelle nur die  typ­is­chen Arbeit­stage einzu­tra­gen. In B1 das erste Datum des Zeitraums, in C1 die Formel und dann ein­fach nur nach rechts rüberziehen. Spal­ten mit Feierta­gen allerd­ings müssen von Hand gelöscht wer­den, aber das ist ja nicht ganz so aufwendig.

▲ nach oben …

Zum üben find­en Sie hier unsere Arbeitsmappe. Das erste Arbeits­blatt ist Monat und enthält die Verkaufs­dat­en.  Fol­gende Vor­gaben müssen zum Funk­tion­ieren des Makros einge­hal­ten wer­den:

• In A1 ste­ht ein Text, hier KdNr.
• In B1:??1 ste­hen die kalen­darischen Dat­en des auszuw­er­tenden Zeit­bere­ichs. Rechts davon darf in Zeile 1 nichts mehr ste­hen.
• In A2:A?? ste­hen die Kun­den­num­mern der Käufer. Auch hier darf in Spalte A unter­halb der let­zten Kun­den­num­mer nichts mehr ste­hen.
• Die Umsätze wer­den am richti­gen Tag der kor­rek­ten Kun­den­num­mer zuge­ord­net; sie kön­nen, müssen aber nicht als Währung for­matiert sein.
• Kom­plett leere Zeilen inner­halb des Daten­bere­ichs wer­den zwar akzep­tiert, aber die Ergeb­nisse wer­den unter Umstän­den ver­fälscht, weil der Kun­den­num­mer 0 (Null) dann der nicht vorhan­dene Umsatz mit 0,00€ zuge­ord­net wird. Ide­al­er­weise: Diese Zeile(n) löschen.

In der zweit­en Tabelle KD-Sortiert soll­ten in A1:C1 die Beze­ich­n­er ste­hen. Eventuell vorhan­dene Dat­en (wie hier bere­its für Demon­stra­tionszwecke einge­tra­gen) wer­den automa­tisch gelöscht, bevor die transponierten Dat­en hier hinein geschrieben wer­den.

Sie kön­nen das Makro auf dem Stan­dard-Weg öff­nen: AltF8, dann das Mod­ul auswählen (falls noch nicht geschehen) und Aus­führen anklick­en. Falls Sie das Ganze bess­er nachvol­lziehen möcht­en, dann geht auch der Modus Schritt. Hier wird die VBA-Rou­tine dann Schritt für Schritt abgear­beit­et. Dieser Weg per AltF8 funk­tion­iert von jedem Tabel­len­blatt aus.

Makro über Alt-F8 aufrufen

Wenn Sie sich ger­ade in dem Arbeits­blatt Monat befind­en, dann kön­nen Sie den Ablauf auch durch einen Dop­pelk­lick auf A1 (KdNr.) starten. Hier dann allerd­ings ohne Einzelschritt – Möglichkeit.

▲ nach oben …

Pivot-Auswertung

Diese Form der Auf­bere­itung wird vielfach als Grund­lage für eine Auswer­tung in ein­er Piv­ot-Tabelle genutzt. Und die ist wirk­lich sehr aus­sagekräftig und mit weni­gen Klicks erstellt. Wenn Sie inter­essiert sind, wie diese Dat­en in ein­er PT ausse­hen kön­nen, dann lassen Sie doch ein­fach ein­mal das ver­bor­gene Arbeits­blatt ein­blenden. Recht­sklick auf ein Reg­is­ter und das einzige ver­bor­gene Blatt, Ein­blenden… und dann per OK ein­blenden lassen.

Ver­bor­genes Tabel­len­blatt anzeigen lassen

Sie wer­den jet­zt vielle­icht sagen, dass diese Piv­ot-Tabelle prak­tisch genau so aussieht wie die Ursprungsta­belle. Na ja, es sieht auf den ersten Blick gewiss danach aus. Aber die Stärke liegt in den Möglichkeit­en, die Dat­en nach ver­schieden­sten Kri­te­rien anzuord­nen, zusam­men­z­u­fassen oder zu fil­tern. Zum The­ma PT gibt es zwis­chen­zeitlich einige weit­ere Beiträge, weit­ere sind in Pla­nung.

[NachOben­Let­zte Verweis=„ML: Transponieren ‘Spezial‘”]