Bedingte Formatierung für Controlling-Berichte in MS Excel 2003 und 2007

Im Controlling ist das Berichtswesen (Reporting) ein wesentlicher Bestandteil. Es stellt die wichtigsten Informationen für verschiedene Adressaten zur Verfügung. Da in einem Unternehmen verschiedenste Daten entstehen, müssen diese im Berichtswesen durch den Controller aufbereitet werden. Neben der Verdichtung und Auswertung der Daten gehört auch die optische Aufbereitung dazu, um somit für die Adressaten wichtige Inhalte besser hervorzuheben. 

Aus diesem Grund soll in diesem Excel-Tipp vorgestellt werden, wie ein Controller mit der bedingten Formatierung von MS Excel verschiedene Formatierungen durchführen kann. 

1. Allgemeines 

In MS Excel 2003 oder früheren Versionen konnten über die Standardfunktion der bedingten Formatierung nur 3 bedingte Formatierungen eingefügt werden. Wenn diese Regel umgangen werden sollte, musste auf VBA zurückgegriffen werden. Zusätzlich waren nur einfache Effekte mit der Standardfunktion durchführbar. 

Diesbezüglich wurde in MS Excel 2007 und neuere Versionen einiges verändert. Es stehen dem Nutzer jetzt mehr als drei Formatierungen zur Auswahl und zusätzlich können verschiedene grafische Effekte ergänzt werden. Diese Neuerungen haben jedoch den Nachteil, dass die bedingten Formatierungen nicht mehr kompatibel mit MS Excel 2003 sind und somit in den älteren Versionen nicht dargestellt werden können. 

Daher werden zunächst in diesem Tipp bedingte Formatierungen vorgestellt, die in MS Excel 2003 und 2007 funktionieren und im Anschluss folgen bedingte Formatierungen, die nur ab MS Excel 2007 zur Verfügung stehen. 

Um bedingte Formatierungen einzugeben, benötigten Sie das Eingabefenster für bedingte Formatierungen. Dies können Sie wie folgt öffnen. 

MS Excel 2003 oder älter: Format/Bedingte Formatierung… 

MS Excel 2007 oder neuer: Start/Formatvorlagen/Bedingte Formatierung/Regeln verwalten…/Neue Regel 
2. Jede zweite Zeile einfärben

Häufig wird in Listen gewünscht, dass jede zweite Zeile eine andere Farbe enthält, um somit die Liste besser lesen zu können. In diesem Fall ist eine Umsatzliste gegeben.

Diese Formatierung können Sie am besten umsetzen, wenn Sie die betroffenen Zellen markieren und in den Formeleditor für bedingte Formatierung folgende Formel eintragen und die gewünschte Formatierung wählen. In diesem Beispiel sollen die zu verändernden Zeilen hellgrau eingefärbt werden.

Formel: =Rest(Zeile();2)




Dies kann auch analog dazu mit Spalten durchgeführt werden, hierfür ersetzen Sie Zeile() durch Spalte().

3. Den größten oder kleinsten Wert hervorheben

Diese Funktion kann genutzt werden um zum Beispiel den größten Umsatz in einer Region hervorzuheben. Jedoch wird selten nur die einzelne Zahl benötigt, meist soll die ganze Zeile markiert werden.

In diesem Beispiel soll der größte Wert grün und der kleinste Wert rot hervorgehoben werden.

Hierfür markieren Sie die Tabelle mit den Daten und geben in den Editor folgende Formeln ein.

Formel für größten Wert: =$G5=MAX($G$5:$G$64)
Formel für kleinsten Wert: =$G5=MIN($G$5:$G$64)

Die Formel bezieht sich auf eine Wertetabelle, in der in Spalte G in den Zeilen 5 bis 64 die Werte (Umsätze) enthalten sind.

 
Abb. 2 Max und Min

4. Einen bestimmten Wertebereich hervorheben

Der Controller möchte in der Umsatztabelle sämtliche Umsätze hervorheben, die zwischen zwei bestimmten Werten liegen. Der größte Grenzwert soll in Zelle N5 definiert werden und der kleinste Grenzwert in N6. Hierfür wird der Zellenbereich der Datentabelle ebenfalls markiert und im Editor für die bedingte Formatierung folgende Formel erfasst.

Formel:  =UND($K5<=$N$5;$K5>=$N$6)

Als Formatierung wurde ein gelber Hintergrund gewählt. In diesem Fall sind in Spalte K die Umsatzzahlen enthalten, beginnend in Zeile 5.

 
Abb. 3 Wertebereich

5. Neue Möglichkeiten ab MS Excel 2007

Neben den bisher genannten Beispielen, die in beiden Excel Versionen umsetzbar sind, sollen in diesem Punkt noch einige schnelle Formatierungen mit Bedingungen vorgestellt werden, welche ab MS Excel 2007 verfügbar sind. Dafür wird von der gleichen Umsatztabelle ausgegangen.

In diesem Tipp sollen drei Möglichkeiten vorgestellt werden.

5.1. Datenbalken

Diese Option fügt einen grafischen Balken in den Datensatz ein, der anzeigt, wie groß der Wert im Gegensatz zu den anderen Werten ist. Hierfür wird im Menü für bedingte Formatierungen die Option Datenbalken geöffnet und eine beliebige Formatierung angeklickt.


Abb. 4 Datenbalken

5.2 Farbskalen

Auch diese Formatierung kann im Menü für bedingte Formatierungen aufgerufen werden. Bei dieser Option erhalten die Werte eine Hintergrundfarbe, welche zum Beispiel einen guten oder schlechten Wert darstellt. Dabei werden zusätzlich unterschiedliche Schattierungen genutzt, um eine Aussage zu dem Wert selbst zu treffen.


Abb. 5 Farbskalen

5.3 Symbolsätze

Die Symbolsätze funktionieren genauso wie die Farbskalen. Jedoch wird hierbei nicht die Hintergrundfarbe geändert, sondern ein Symbol vor dem Wert zeigt dessen Wertigkeit an.

Die Symbolsätze fügen Sie genauso wie Datenbalken oder Farbskalen ein.


Abb. 6 Symbolsätze

Die hier vorgestellten Möglichkeiten sind nur Anregungen, natürlich können Sie weitere Regeln oder andere Kombinationen verwenden.

Die Beispieldateien bedingte Formatierungen können Sie hier herunterladen >>

 

 

letzte Änderung Alexander Wildt am 09.09.2019

Autor:in

weitere Fachbeiträge des Autors | Forenbeiträge