Transponieren „Spezial“

Durch spezielles transponieren
für Pivot-Tabelle vorbereiten

Der Stand der Dinge

Mehrere Abteilungen eines Unternehmens liefern Verkaufsdaten in eine einzige Excel-Tabelle. Das führt dazu, dass ein Kunde am gleichen Tag oder an verschiedenen Tagen mehrfach in der Monatsübersicht auftaucht. Der Kunde 4711 ist einer der „Kandidaten“. Die Tabelle kann hier herunter geladen werden und stellt sich etwa so dar:

Ausschnitt aus der Roh-Daten-Tabelle

Ausschnitt aus der Roh-Daten Tabelle

Sie erkennen, dass in Spalte A die Kundennummern eingetragen sind, in Zeile 1 ab Spalte B die Arbeitstage, hier für den Januar 2014. Unterhalb des Datums sind die verschiedenen Umsätze eingetragen.

▲ nach oben …

Die Forderung

So, wie die Tabelle derzeit vorliegt, lässt sie sich nicht wirklich leicht auswerten. Darum wird folgende Forderung aufgestellt: Statt in einer Kreuztabelle sollen alle Kunden mit ihren jeweiligen Umsätzen und dem Datum in einer neuen Tabelle in drei Spalten erfasst werden. Jede Kundennummer (ist hier identisch 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 Kunden aus der Liste (KdNr. 4582) sähe das dann so aus:

Die ersten Zeilen des Wunsch-Ergebnisses

Die ersten Zeilen des Wunsch-Ergebnisses

Der Übersicht wegen habe ich hier auch die erste Zeile des 2. Kunden mit eingefügt.

Zugegeben, es geht mit reinen Excel-Funktionen. Aber die Formel ist nicht wirklich sehr übersichtlich und transparent. Und wenn sich dann einmal etwas ändert, ist extrem viel Aufmerksamkeit erforderlich. Darum wählen wir hier den Weg über ein VBA-Programm. Diese Routine ist sehr flexibel und passt sich automatisch an die Größe der Tabelle an. – Selbstverständlich bleibt es Ihnen unbenommen, Teile des Makros anzupassen, soweit erforderlich. Bitte belassen Sie auch in dem Fall den Kommentar-Kopf mit dem Hinweis auf das Copyright im Makro-Bereich.

Apropos Formel: In Tabelle Monat steht in Zeile 1 ab Spalte C eine Formel. Vielleicht können auch Sie davon profitieren, auf die Schnelle nur die  typischen Arbeitstage einzutragen. In B1 das erste Datum des Zeitraums, in C1 die Formel und dann einfach nur nach rechts rüberziehen. Spalten mit Feiertagen allerdings müssen von Hand gelöscht werden, aber das ist ja nicht ganz so aufwendig.

▲ nach oben …

Zum üben finden Sie hier unsere Arbeitsmappe. Das erste Arbeitsblatt ist Monat und enthält die Verkaufsdaten.  Folgende Vorgaben müssen zum Funktionieren des Makros eingehalten werden:

  • In A1 steht ein Text, hier KdNr.
  • In B1:??1 stehen die kalendarischen Daten des auszuwertenden Zeitbereichs. Rechts davon darf in Zeile 1 nichts mehr stehen.
  • In A2:A?? stehen die Kundennummern der Käufer. Auch hier darf in Spalte A unterhalb der letzten Kundennummer nichts mehr stehen.
  • Die Umsätze werden am richtigen Tag der korrekten Kundennummer zugeordnet; sie können, müssen aber nicht als Währung formatiert sein.
  • Komplett leere Zeilen innerhalb des Datenbereichs werden zwar akzeptiert, aber die Ergebnisse werden unter Umständen verfälscht, weil der Kundennummer 0 (Null) dann der nicht vorhandene Umsatz mit 0,00€ zugeordnet wird. Idealerweise: Diese Zeile(n) löschen.

In der zweiten Tabelle KD-Sortiert sollten in A1:C1 die Bezeichner stehen. Eventuell vorhandene Daten (wie hier bereits für Demonstrationszwecke eingetragen) werden automatisch gelöscht, bevor die transponierten Daten hier hinein geschrieben werden.

Sie können das Makro auf dem Standard-Weg öffnen: AltF8, dann das Modul auswählen (falls noch nicht geschehen) und Ausführen anklicken. Falls Sie das Ganze besser nachvollziehen möchten, dann geht auch der Modus Schritt. Hier wird die VBA-Routine dann Schritt für Schritt abgearbeitet. Dieser Weg per AltF8 funktioniert von jedem Tabellenblatt aus.

Makro über Alt-F8 aufrufen

Makro über Alt-F8 aufrufen

Wenn Sie sich gerade in dem Arbeitsblatt Monat befinden, dann können Sie den Ablauf auch durch einen Doppelklick auf A1 (KdNr.) starten. Hier dann allerdings ohne Einzelschritt – Möglichkeit.

▲ nach oben …

Pivot-Auswertung

Diese Form der Aufbereitung wird vielfach als Grundlage für eine Auswertung in einer Pivot-Tabelle genutzt. Und die ist wirklich sehr aussagekräftig und mit wenigen Klicks erstellt. Wenn Sie interessiert sind, wie diese Daten in einer PT aussehen können, dann lassen Sie doch einfach einmal das verborgene Arbeitsblatt einblenden. Rechtsklick auf ein Register und das einzige verborgene Blatt, Einblenden… und dann per OK einblenden lassen.

Verborgenes Tabellenblatt anzeigen lassen

Verborgenes Tabellenblatt anzeigen lassen

Sie werden jetzt vielleicht sagen, dass diese Pivot-Tabelle praktisch genau so aussieht wie die Ursprungstabelle. Na ja, es sieht auf den ersten Blick gewiss danach aus. Aber die Stärke liegt in den Möglichkeiten, die Daten nach verschiedensten Kriterien anzuordnen, zusammenzufassen oder zu filtern. Zum Thema PT gibt es zwischenzeitlich einige weitere Beiträge, weitere sind in Planung.

▲ nach oben …

Dieser Beitrag wurde unter Mit VBA/Makro, Musterlösungen, Tabelle und Zelle abgelegt und mit , , , , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.