Renate Schwenken

Lässt sich eine Excel PivotTable nach IBCS® formatieren?

Die PivotTable ist das Standardwerkzeug für die Datenanalyse in Excel. Wer nun aus seiner Datenanalyse mit der PivotTable einen IBCS® (International Business Communication Standard) konforme Tabelle erzeugen möchte steht vor der Frage der Formatierung. Ein häufiger Lösungsansatz ist, dass man die Daten aus dem PivotTable mit dynamischen Formeln in einer nach IBCS®-Regeln formatierten Tabelle darstellt. Doch lässt sich eine PivotTable auch direkt IBCS® konform formatieren? Dieser Fragestellung wollen wir in dem folgenden Beitrag nachgehen.

Ausgangssituation

Das Standardlayout einer PivotTable im Kurzformat ergibt folgende Darstellung:

Die Zielformatierung, eine IBCS® konforme Tabelle, dieser Daten sieht wie folgt aus:

1. Ändern der Standardeinstellungen und Beschriftungen des PivotTable

  • Spaltenbreite bei Aktualisierung beibehalten: PivotTable aktivieren unter PivotTable-Tools ->Analysieren -> PivotTable -> PivotTable Optionen -> Layout & Format -> Spaltenbreiten bei Aktualisierung automatisch anpassen deaktivieren.

  • Evtl. muss die Spaltensumme deaktiviert werden: Pivottable-Tools -> Entwurf -> Gesamtergebnisse – Nur für Spalten aktivieren.
  • Das Wort „Zeilenbeschriftungen“ und ggf. „Spaltenbeschriftungen“ wird nicht benötigt Zelle auswählen -> Bezeichnung im Formelfeld entfernen.

  • Nach IBCS® werden Zwischensummen und Summen unterhalb der Werte angezeigt, in PivotTable werden die Zwischensummen standardmäßig oberhalb angezeigt. Diese Einstellung kann unter PivotTable-Tools -> Entwurf -> Teilergebnisse -> Alle Teilergebnisse in der Gruppe unten anzeigen verändert.

  • In der nun ergänzten Zeile für die Zwischensumme unterhalb der Werte wird automatisch das Wort „Ergebnis“ angehängt, z. B. „1. Deckungsbeitrag Ergebnis“, diesen Zusatz wird wieder entfernt. Zelle auswählen -> Bezeichnung „Ergebnis“ im Formelfeld. Wenn man den restlichen Teil des Feldes unberührt lässt, löscht Excel automatisch die Ergänzung in den anderen Zwischensummen der gleichen Hierarchie.
  • Die Bezeichnung „Gesamtergebnis“ soll in unserem Fall in „EBIT“ geändert werden. Hierzu Zelle auswählen -> Bezeichnung im Formelfeld ändern.

  • Die Bezeichnungen der Spaltenüberschriften lassen sich in den Wertfeldeinstellungen der betreffenden Kennzahl ändern. PivotTable Feldliste anzeigen -> Werte -> Kennzahl auswählen -> Kontextmenü Werteinstellungen öffnen.

Im Wertfeldeinstellungs- bzw. Feldeinstellungsmenü die gewünschte Bezeichnung als Benutzerdefinierter Name eintragen.

  • Spaltenüberschriften rechtsbündig formatieren.

Das PivotTable sieht jetzt wie folgt aus:

2. Erstellen und Anwenden eines benutzerdefiniertes PivotTable-Formats

Nun folgt der entscheidende Schritt auf dem Weg zu einer IBCS® konformen Tabelle: eine benutzerdefinierte Formatvorlage.

Hierzu unter PivotTable-Tools -> Entwurf ->PivotTable-Formate -> Neue PivotTable-Formatvorlage auswählen.

Im nachfolgenden Menü wird ein Name für die Formatvorlage hinterlegt z.B. „IBCS“.

Die Formatvorlage ist zunächst leer. Für jedes Tabellenelement kann eine eigene Formatierung erstellt werden. Welches Tabellenelement welches Element in dem PivotTable steuert ist nicht selbsterklärend, hier muss man ausprobieren.

Als erstes wird das Tabellenelement „Ganze Tabelle“ formatiert: Tabellenelement -> Ganze Tabelle -> Formatieren auswählen. Dann öffnet sich das bekannte „Zellen formatieren“ Menü.

Hier unter Rahmen eine gepunktete horizontale Mittellinie in hellgrau einfügen. Anschließend mit OK bestätigen. Diese Einstellung sorgt dafür das zwischen allen Zeilen des PivotTable eine gepunktete Linie eingefügt wird.

Mit dem gleichen Vorgehen werden noch weitere Veränderungen vorgenommen. Wichtig ist, dass nur die erforderlichen Änderungen durchgeführt werden. Sämtliche anderen Formatierungen bleiben unberührt. Die nachfolgende Tabelle zeigt alle erforderlichen Änderungen:

Nach der Durchführung der beschriebenen Schritte für alle Tabellenelemente ist die Formatvorlage fertig. PivotTable-Format Menü mit OK verlassen.

Jetzt kann die eigene Formatvorlage auf die Tabelle angewendet werden. Hierzu unter PivotTable-Tools-> Entwurf -> PivotTable-Formate-> Benutzerdefinierte Formate die Vorlage „IBCS“ auswählen.

Nun hat die PivotTable folgenden Look:

3. Leerspalte einfügen
IBCS® Tabellen haben zwischen den Spalten zur Erhöhung der Lesbarkeit eine Unterbrechung der horizontalen Linien. Um dies in einem PivotTable zu realisieren kann man entweder ein zusätzliches Leer-Measure (z.B. leer:=““) bei der Verwendungs eines PowerPivot Datenmodells oder ein berechnetes Feld bei einer normalen PivotTable verwenden.

Für normale PivotTables unter PivotTable-Tools -> Analysieren -> Berechnungen -> Felder, Elemente und Gruppen -> Berechnetes Feld auswählen.

Im anschließenden Menü einen beliebigen Namen eingeben und als Formel =““ eintragen und mit Hinzufügen bestätigen.

Die Felder des Berechneten Feldes zeigen jetzt alle die Fehlermeldung #WERT. Unter PivotTable Optionen -> Layout&Format -> Für Fehlerwerte anzeigen aktivieren. Jetzt erhält man anstatt der Fehlermeldung leere Felder.

Das berechnete Feld kann in dem PivotTable mehrfach als Wert verwendet werden.

4. Finale Formatierungen

  • Leerspalte formatieren
    Nun müssen wir noch die einfügte Leerspalte formatieren. Insbesondere möchten wir eine Unterbrechung der Querlinien. Hierzu mit der Maus über den Spaltenkopf fahren bis ein kleiner Pfeil nach unten erscheint. Jetzt per Mausklick die Spalte auswählen.

Dann mit rechter Maustaste das Kontextmenü Zellen formatieren auswählen und unter Rahmen weiße horizontale Linien oben, zentriert und unten einfügen.

Für die Spaltenüberschriften „leer“, usw. Schriftfarbe weiß auswählen.

  • Szenario-Markierung einfügen: Die nach IBCS empfohlene Szenario Markierung für Ist, Plan, Vorjahr kann mit einer normalen Zellformatierung darstellt werden. Eine Straffierung wie z.B. für den Forecast ist auf diese Weise nicht möglich, dann empfiehlt es sich oberhalb des PivotTable eine Überschriftenzeile mit den Szenario-Markierungen einzufügen und die eigentlichen Zellüberschriften auszublenden.
  • Zum Abschluss verformeln wir noch den Inhalt der PivotTable Filter dynamisch in die Überschrift und blenden den Filterbereich aus.

Damit ist die Formatierung des PivotTable abgeschlossen.

Kritische Würdigung

Wenn wir unsere IBCS-Zielformatierung anschauen, so haben wir folgende Punkte nicht erreicht:

  • Eine Szenario-Markierung unterhalb des EBIT lässt sich nicht einfügen. Da das PivotTable dynamisch von der Zeilenanzahl ist, sollte man in der letzten Zeile nicht einfach die Zellformatierung wie bei der Überschrift ändern. Auch eine bedingte Formatierung führt nicht zum Ziel, da hier weder dicke Linien für VJ und Ist noch doppelte Linien für Plan unterstützt werden.
  • Auf die Linksbündigkeit der Zeilenbeschriftungen haben wir bewusst verzichtet. Für die Linksbündigkeit herzustellen kann man die Schaltflächen zum Erweitern/Reduzieren weglassen (PivotTable-Optionen -> Anzeige -> Schaltflächen zum Erweitern/Reduzieren anzeigen – deaktivieren) und den Einzug auf 0 setzen (PivotTable-Optionen ->Layout&Format -> Für Kurzformat Einzug der Zeilenbeschriftungen – 0 Zeichen). Dadurch kann man diese praktische Möglichkeit Zeilen ein- und auszublenden allerdings nicht mehr nutzen.
  • Leerzeilen nach einer Summe lassen sich nicht einfügen, sondern ergeben sich je nach der Struktur der Daten. Konkret können lässt sich in diesem Beispiel oberhalb des Gesamtergebnisses keine Leerzeile einfügen.
  • Die benutzerformatierte PivotTable bietet eine begrenzte Anzahl an Tabellenelemente, die formatiert werden können. Bei einer zu tiefgehenden Hierarchie reichen die 3 zur Verfügung stehenden Hierarchieebenen für die Ergebniszeilen (Zeilenunterüberschrift 1-3 und Zwischensumme 1 bzw. Teilergebnis 2-3) nicht aus. Des Weiteren kann es in bestimmten Datenkonstellationen zu Anomalien kommen bei denen Workarounds benötigt werden, um die Tabelle wie gewünscht anzuzeigen.
  • Für eine aussagekräftige Visualisierung würde man natürlich noch die Abweichung mit einem grün/roten Abweichungsbalken visualisieren. Auch hier gibt es noch weitergehende Optionen innerhalb des PivotTables.

Fazit

Ein PivotTable lässt sich nicht vollständig IBCS® konform formatieren. Allerdings kommt man mit dem beschriebenem Vorgehen dem Standard für viele Einsatzzwecke schon sehr nahe und kann so die Vorteile eines PivotTables z.B. das Einfügen/Löschen von Attributen oder Filtern nutzen. Die benutzerdefinierte Formatvorlage muss leider in manchen Datenkonstellationen individuell angepasst werden.

Weiterführende Links

Informationen zum Layout von Tabellen nach IBCS®: https://www.ibcs.com/de.

Benutzerdefinierte PivotTable Formatvorlagen: https://www.contextures.com/excel-pivot-tableformat.html