Gruppierte Excel-Dimensionsliste aus einer Word-Quelldatei mittels VBA erstellen | Teil 1 – praktische Anwendung

Publiziert von

Eine der zentralen Business Intelligence Aufgaben ist die Gestaltung von Stammdaten in Form von Dimensionen und Hierarchien. Häufig liegen diese Stammdaten vorerst nur in schlecht verarbeitbarer Form vor – im folgenden Beitrag sehen wir wie der Leistungskatalog für das Projektcontrolling von Bauprojekten aus dem Word-Quellformat in eine gruppierte Excel-Dimensionsliste effizient transformiert wird.

Wir starten mit dem Leistungskatalog aus der Honorar Information Architektur HIA_9_2010 im Word Format:

 Wir markieren den gesamten Inhalt des Word-Dokuments (Strg+A), kopieren (Strg+C) und fügen den  Inhalt in eine neue Excel Datei (Strg + V) ein. Nach einigen Bereinigungsarbeiten – überschüssige Textzeilen entfernen, Einrückungen entfernen, unnötige Leerzeichen entfernen und unerwünschte Einfüge-Formatveränderungen am Key korrigieren – erhalten wir eine saubere Liste mit den Keys und der Textbeschreibung des Keys (Spalte B und C):

Die Ebeneninformation (Spalte A) – das sind die Überschriftenformate in der Word-Ursprungsdatei – enthalten wir bei diesem Datenmaterial am einfachsten mit der Länge-Formel  auf den Key. Eine Key-Länge von 2 Textzeichen entspricht der Ebene 1, eine Key-Länge von 5 Textzeichen entspricht der Ebene 2 und so weiter bis zur Ebene 4.

Den Anzeige-Key, das ist die Kombination des Keys mit der Textbeschreibung zur späteren einspaltigen Verwendung von Key und Text, erhalten wir sehr einfach durch eine Verkettungsformel nach dem Muster: Zelle D1 =B2&“  “ &C2

Jetzt kommt die eigentliche Herausforderung, die automatische Gruppierung der Zeilen sowie die ebenenabhängige Einrückung der Anzeige-Keys in Spalte D. Wir automatisieren diese Aufgabe mit einem einfachen VBA-Makro. Im Menü Ansicht->Makros->Makros anzeigen starten wir das zuvor hinterlegte Makro „Automatische_Ebenen_Gruppierung“ durch Betätigen des Buttons „Ausführen“:

Die geforderten Eingaben geben wir für den vorhandenen Datenbestand folgendermassen an:

Die Durchführung des VBA-Makros benötigt für die 1150 Zeilen rund 15 Sekunden, das beinahe fertige Ergebnis sieht jetzt so aus:

 Die Gruppierungen sind jetzt entsprechend der Ebeneninformationen aus Spalte A in ebenfalls 4 Ebenen automatisch angelegt. Zusätzlich ist der Positions-Key ebenfalls entsprechend der Ebeneninformation aus Spalte A entsprechend eingerückt. Das Sheet „(Originaldaten)“ wurde übrigens vor der Ausführung des VBA-Makros als „Formatiert“ kopiert um den Vorgang beliebig oft aus den Rohdaten wiederholen zu können.

Das Ergebnis kann jetzt weiter verfeinert werden durch die Anwendung der automatischen Formatierung im Menü Daten->Gliederung->Einstellungen:

Das Makro wird dazu nochmals auf die Originaldaten angewendet sodaß die geänderte Option nun zur Anwendung kommt. Die Hilfsspalten (A, B und C) können jetzt bereits ausgeblendet werden und die eigentliche Anwendung, hier die strukturierte Erfassung der Projekt-Vorkalkulation des Architekten – auf Basis des strukturierten Leistungskatalogs erstellt werden:

Im nächsten Teil dieser Beitragsserie sehen wir uns den Aufbau des zugrundeliegenden VBA-Makros an.

Interessant sind auch die Aufgaben der individuellen Anpassung der automatischen Formatierung sowie die automatische Summenbildung auf jeder Gruppierungsebene – dazu vielleicht mehr in einem weiteren Beitrag.

Robert Lochner

Robert Lochner ist seit 2001 als Unternehmer tätig und Gründer der Linearis. Seit seinem Betriebswirtschaftsstudium an der WU Wien ist er als Unternehmer in der BI Branche tätig, ist Trainer für Excel BI und Power BI und unterstützt Unternehmen bei der digitalen Transformation ihrer Business Intelligence Prozesse. Er ist Autor des Linearis Blogs und mehrerer fachlicher Publikationen.

Weitere Beiträge

Kategorien: Analyse, Excel als BI Frontend