Bedingte Formatierung in Pivot-Tabellen 2

Bedingte Formatierung und Pivot-Tabellen passen hervorragend zusammen - wenn man ein paar Dinge beachtet!
 

Wer diesen Blog schon länger liest, der weiß, dass ich ein großer Fan von bedingter Formatierung und von Pivot-Tabellen bin.
Beides sind für sich genommen schon mächtige und extrem nützliche Funktionen, um mehr Licht in seine Zahlenfriedhöfe zu bringen.

Noch besser wird es, wenn man bedingte Formatierung und Pivot-Tabellen miteinander kombiniert. Hierbei gibt es ein paar Besonderheiten zu beachten.

Und so geht’s:

Bedingte Formatierung ohne Pivot-Tabellen

Wenn man in einer normalen 08/15-Tabelle eine neue Formatierungsregel einfügt (Start | Bedingte Formatierung | Neue Regel…), dann erhält man ein zweigeteiltes Fenster:
(1) Eine Liste mit Regeltypen
(2) Die Regelbeschreibung, die je nach Regeltyp variiert

Formatierungsregeln in normalen Tabellen

Formatierungsregeln in normalen Tabellen

Das einzige, das man hier falsch machen kann ist, dass man zu Beginn nicht erst den gewünschten Zellenbereich markiert hat.
So weit, so gut.

Bedingte Formatierung mit Pivot-Tabellen

Befindet man sich mit der aktiven Zelle beim Einfügen einer neuen Formatierungsregel jedoch innerhalb einer Pivot-Tabelle, sieht das Regelfenster ein wenig anders aus:

Formatierungsregeln in Pivot-Tabellen

Formatierungsregeln in Pivot-Tabellen

Dort gibt es oben im Fenster einen dritten Bereich, der eben nur in Pivot-Tabellen angezeigt wird. Hier muss man sich entscheiden, auf welchen Teil der Pivot-Tabelle diese Regel angewendet werden soll. Dabei gibt es drei Möglichkeiten:

  • Markierte Zellen
  • Alle Zeilen mit „(Name des Wertefeldes)“ Werten
  • Alle Zeilen mit „(Name des Wertefeldes)“ Werten für „(Name des Zeilenfeldes)“ und „(Name des Spaltenfeldes)“

Die Unterschiede dieser drei Optionen sehen wir uns gleich näher an.

(Solltest du bisher um Pivot-Tabellen einen großen Bogen gemacht haben, empfehle ich dir mein Buch „Excel Pivot-Tabellen für Dummies“. Oder meine beiden Einsteiger-Artikel „Der beste Freund des Analysten: Pivot-Tabellen“ und „Der Horror geht weiter: Pivot-Tabellen Teil 2“)

Option 1: Markierte Zellen

Diese Option ist die Voreinstellung und entspricht auf den ersten Blick genau dem, was man normalerweise auch bei einer bedingten Formatierung in einer ganz normalen Datentabelle erwartet. Sie wird nur auf den Zellenbereich angewendet, der im Eingabefeld markiert wird.

Option 1: Markierte Zellen

Option 1: Markierte Zellen

Im Bild oben wurden beispielsweise nur die Werte für das Ursprungsland Frankreich markiert, daher werden die Formatierungsregeln erwartungsgemäß auch nur darauf angewendet:

Gewünschten Wertebereich markieren

Gewünschten Wertebereich markieren

Das Besondere an dieser Variante wird jedoch sichtbar, wenn die Pivot-Tabelle anschließend umgebaut wird. Im folgenden Bild habe ich die Zeilen und Spalten vertauscht und wie man sieht, wird die Formatierungsregel weiterhin korrekt angewendet:

Die Regel greift auch bei Änderungen

Die Regel greift auch bei Änderungen

Schon ganz praktisch, oder? Aber es geht noch besser!

In Kombination mit einer weiteren Regel ergeben sich daraus noch interessante Möglichkeiten. So lassen sich beispielsweise für jedes Land die Farbbalken aus- oder einschalten. Zunächst wird für jedes Land eine eigene Regel angelegt noch oben gezeigtem Muster angelegt, im nachfolgenden Bild beispielhaft für FR und DE:

Eine eigene Formatierungsregel für jedes Land

Eine eigene Formatierungsregel für jedes Land

Als nächstes richten wir in einem separaten Bereich, beispielsweise oberhalb der Pivot-Tabelle je Land eine Art „An/Aus“-Schalter ein:

Einen "Schalter"-Bereich einrichten

Einen „Schalter“-Bereich einrichten

Als nächstes wird für die beiden Länder je eine weitere Regel benötigt, die den Wert in Zelle B2 bzw. F2 überprüft. Dazu wird wieder der jeweilige Wertebereich für das Land markiert, für FR also B8:B18. Als Regeltyp wird diesmal jedoch „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ ausgewählt und dann folgende Formel eingetippt:
=$B$2="aus"
Es wird jedoch keine Formatierung festgelegt, denn damit soll die Formatierung ja ausgeschaltet werden.

Regel für den ersten Schalter

Regel für den ersten Schalter

Eine zweite Regel wird analog für das Land DE angelegt, nur eben mit Bezug auf die Zelle F2 (statt B2).

Regel für den zweiten Schalter

Regel für den zweiten Schalter

Damit unsere „Schalter“ auch tatsächlich funktionieren, muss noch eine Kleinigkeit geändert werden. Dazu wird der Regelmanager geöffnet (Start | Bedingte Formatierung | Regeln verwalten…)
Zuerst muss sichergestellt sein, dass die „Schalter“-Regeln an erster Stelle in der Liste stehen, also vor den Regeln für die Datenbalken.
Dann muss jeweils das Häkchen „Anhalten“ gesetzt sein. Damit wird sichergestellt, dass die nachfolgenden Regeln für den jeweiligen Datenbereich nicht mehr ausgeführt werden.

Reihenfolge + Anhalten-Flag sind wichtig

Reihenfolge + Anhalten-Flag sind wichtig

Und schon funktionieren unsere Schalter:

Die Schalter in Aktion

Die Schalter in Aktion

Option 2: Alle Zeilen mit „(Name des Wertefeldes)“ Werten

Diese Option sollte man dann verwenden, wenn man sämtliche Werte einschließlich der Summenzeilen und -spalten in die Formatierung einbeziehen möchte. Die etwas sperrige Bezeichnung hängt mit dem jeweiligen Namen und der angewendeten Funktion des Wertefeldes zusammen. Im Beispiel ist das Wertefeld „Menge“ und die Funktion „Summe“:

Option 2: Alle Werte inklusive Summen

Option 2: Alle Werte inklusive Summen

Im Ergebnis stechen natürlich die Summenzeilen und -spalten am stärksten heraus:

Ergebnis für Option 2

Ergebnis für Option 2

Option 3: Alle Zeilen mit „(Name des Wertefeldes)“ Werten für „(Name des Zeilenfeldes)“ und „(Name des Spaltenfeldes)“

Diese Option dürfte vermutlich in den meisten Fällen die passende sein. Hier wird die Formatierung auf sämtliche Werte angewendet, mit Ausnahme der Summenzeile und -spalte:

Option 3: Nur der Wertebereich ohne Summen

Option 3: Nur der Wertebereich ohne Summen

Das Ergebnis sieht dann so aus:

Ergebnis für Option 3

Ergebnis für Option 3

Wie du gesehen hast, bilden bedingte Formatierung und Pivot-Tabellen ein ziemlich gutes Team. Manchmal ist nur etwas Fantasie gefragt.

 

Bitte weitersagen, wenn dir der Artikel gefallen hat!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

2 Gedanken zu “Bedingte Formatierung in Pivot-Tabellen