Controller kennen das gut: Du hast ein File heruntergeladen oder einen Report zugeschickt bekommen und das ist in einem unglücklichen Format. Um diese Daten dann vernünftig zu bearbeiten musst Du diese erst in ein verarbeitbares Format überführen und das ist teilweise sehr mühsam. Besonders dann, wenn die Spalten sehr lang sind. Dann muss man kopieren, runter scrollen, einfügen, wieder rauf, kopieren, runter scrollen, einfügen wieder rauf… Für zumindest einen Fall, der mir sehr oft unterkommt, habe ich ein weiteres kleines Excel Tool gebastelt (diesmal als Makro) das dann oft eine Menge Zeit und Mühe spart.

Für die Ungeduldigen geht’s hier direkt zum Excel-File.

 

Mehrere Datenspalten in einer Spalte zusammenführen

BWAs, Kontosalden und Monatsübersichten bekommt man oft in einem Format, in dem die Monate in Spalten aufgeteilt sind. Etwa so:

Original

 

AdWords Berichte mit Uhrzeiten werden auch in diesem Format ausgeliefert. Um die Daten vernünftig zu verarbeiten muss man in obigem Beispiel die Werte aus den Monatsspalten in einer Spalte zusammenführen und davor eine Spalte einfügen und zu den Monatswerten auch die Monate festhalten. Das Ergebnis sollte dann ungefähr so aussehen:

Final

 

Damit könnte man dann recht einfach z. B. eine Pivot Tabelle erzeugen oder die Daten in ein laufendes Reporting überführen. Dazwischen liegen aber eine ganze Reihe manueller Schritte und damit das etwas einfacher geht, hier ein Excel Makro dafür. Um das zu nutzen kannst Du entweder Deine Daten in den Reiter „Load_Data_Here“ einfügen und dann den Button „MAKRO ausführen“ drücken – das Makro führt Dich dann durch den Prozess. Oder Dein eigens File öffnen und das Makro („Columndata_collect“) aufrufen. Ich würde Dir in solchen Fällen immer raten, nicht das Originalfile mit den Daten zu nutzen – wenn mein File „kaputt“ geht, kannst Du es im Schlimmsten Fall einfach noch mal herunterladen.

 

In fünf Schritten zur sauberen Datenaggregation

Damit man eine bessere Vorstellung davon bekommt, was in den nächsten Schritten passiert, färben ich die verschiedenen Bereiche des Beispiels unterschiedlich ein:

Start

 

Grün sind die Datendimensionen („Konto“ und „Kontobezeichnung“). Dieser Block wiederholt sich für jeden Monat, wird im Aktuellen Format quasi pro Zeile zusammengefasst.

Gelb sind die Ausprägungen der Dimension Monat.

Blau sind die jeweiligen Werte pro Monat. Der Januar ist weniger stark eingefärbt, weil ich den im Grunde nicht anfassen muss – kompliziert ausgerückt ist das der Punkt, um den die Tabelle transponiert wird.

Der Teil in Lila interessiert mich in diesem Fall nicht, das ist quasi keine Daten- sondern eine Ergebnisspalte. Unten das GIF noch mal, dass den Prozess etwas veranschaulichen soll (anklicken):

Columndata_Collect_Process_GIF

 

Das Makro fragt all diese Schnittpunkte ab und übernimmt das Kopieren für Dich. Folgende Voraussetzungen müssen erfüllt sein:

  • Alle Spalten sind gleich lang – Dimensionen und Werte.
  • Alle Zellen enthalten Werte, keine Leerzellen dazwischen – falls doch bitte mit „0“ (Null) ersetzen.
  • Die Dimensionen in den Spalten (Monate) sind in nicht verbundenen Zellen in der obersten Ziele hinterlegt (grundsätzlich gilt: Zellen verbinden macht in Excel nur Probleme!).
  • Zwischen den ursprünglichen Dimensionen (grün) und den Werten sind keine Spalten, die leer sind oder ignoriert werden sollen – wenn doch, dann diese Spalten vorher löschen. Spalten danach (lila) sind kein Problem.
  • Damit das Makro grundsätzlich funktioniert, musst Du entsprechende Einstellungen in Excel vornehmen zur Marko Sicherheit. Wenn Du Dir damit nicht sicher bist, dann frag Deinen Administrator oder einen Bekannten, der sich damit auskennt. Excel Dateien mit Makros können auch Schadcode enthalten – daher der Warnhinweis.

Wenn das nicht auf Anhieb klappt, nicht verzagen – File ohne speichern schließen und einfach noch mal versuchen, dann bekommt man den Dreh schnell heraus. Zum Testen habe ich in das Excel Sheet mit diesem Beispiel als Reiter („Beispiel Start“) eingefügt – hier stehen genau die Eingaben drin, die Du machen musst. Das verschiebt die Lernkurve hoffentlich ein wenig.

Viel Spaß mit deinen neu aufbereiteten Daten und genieße die gewonnene Zeit. Falls Bugs auftreten oder Anregungen entstehen, bitte melden, ich werde dann versuchen die zu beheben oder einzufügen.

 

PS: An all die Excel-Verrückten da draußen – ja das Makro ist nicht sehr sauber geschrieben aber es funktioniert. Wer Lust hat kann das gerne sauber machen und sich bei mir melden, dann ersetze ich das…