Bedingte Formatierung in Pivot-Tabellen 7

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.

 

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 zu Andi Antworten abbrechen

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

7 Gedanken zu “Bedingte Formatierung in Pivot-Tabellen

  • Harald Gelsen

    Hallo Herr Weiß,
    Ich verfolge Ihre Blogs schon seit einiger Zeit und habe schon wunderbare Tipps gefunden.
    Ich habe aktuell ein Problem zu genau diesem Thema, mit dem ich nicht weiter komme, und hoffe auf eine gute Idee.
    Ihre Beispieltabellen oben sind ja aus verständlichen Gründen noch recht einfach gehalten.
    Meine Tabelle sieht z.B. folgendermaßen aus:
    AT DE
    Lampen Rot 20 60
    Gelb 40 50
    Grün 50 30
    Lampen Ergebnis 110 140
    Halter Rund 10 15
    Eckig 90 35
    flach 30 40
    Ergebnis Halter 130 90
    Und dann gibts vielleicht noch in Zukunft blaue Lampen und dreieckige Halter, die nur aktuell keine Menge haben und daher in der Pivot (noch) nicht erfasst werden.
    Nun möchte ich den jeweils höchsten Wert je Artikelgruppe (Lampen, Halter) und je Land hervorheben, also für AT gelbe Lampen und eckige Halter oder für DE rote Lampen und flache Halter.
    Klar kann man jetzt jeden Zellenbereich einzeln markieren und die Formatierung darauf anwenden.
    Und man kann vermutlich auchüber Format kopieren arbeiten.
    Wenn die Tabelle aber wie bei mir 50 Länder und hunderte von Artikelgruppen umfasst dann macht das keinen Spaß mehr.
    Ich könnte mir vorstellen, dass es dort unter Verwendung von MAX und BEREICH.VERSCHIEBEN irgendeine Möglihckeit gibt, eine tolle Formel zu bauen. Aber ich bekomme es nicht zusammen (s.o. Das Problem sitzt meist vor dem Computer).
    Ich hoffe ich konnte mich verständlich ausdrücken und habe ihren detektivischen Spürsinn geweckt.
    Wäre toll, eine Lösung für das Problem zu finden.

    Viele Grüße
    Harald

    • Martin Weiß Autor des Beitrags

      Hallo Harald,

      das sollte mit den speziellen bedingten Formatierungen für Pivot-Tabellen schon funktionieren. Gehen Sie dazu folgendermaßen vor:
      – markieren Sie die gesamte Pivot-Tabelle
      – Legen Sie eine neue bedingte Formatierungsregel an (Start | Bedingte Formatierung | Neue Regel)
      – da Sie zuvor die Pivot-Tabelle markiert haben, sieht das Regelfenster jetzt etwas anders aus (so wie im Artikel oben im zweiten Bild)
      – Markieren Sie im oberen Bereich des Fensters die dritte Option „Alle Zellen mit Summe von … für …“
      – Wählen Sie als Regeltyp „Nur obere oder untere Werte formatierung
      – In der Regelbeschreibung wählen Sie „Obere“ und den Wert 1 und, wichtig!!!, im Dropdownfeld daneben den Eintrag „jede Zeilengruppe“

      Damit sollte pro Land und Produkt der jeweils höchste Wert hervorgehoben werden.

      Schöne Grüße,
      Martin

      • Harald Gelsen

        Hallo Herr Weiß,

        Danke für die Antwort, aber leider geht es genau so nicht.
        Ich habs auf dem beschriebenen Weg probiert mit „nehme die oberen 100“. Was dann als Ergebnis passiert ist, dass er sich aus der gesamten Tabelle die 100 höchsten Werte heraussucht und die formatiert. Egal in welcher Zeilen oder Spaltengruppe der Wert vorkommt. Immerhin betrachtet er die Zwischenergebnisse nicht mit.
        Also leider nicht das gewünschte Ergebnis – alles zurück auf Anfang

  • AnonUser

    Kurze Frage dazu…
    Ich habe eine Liste mit mehreren Zehntausend Zeilen.
    In 5 Balken habe ich pro Jahr Umsätze / Werte.
    Ich habe die erst Zeile markiert und ein Farbskala gemacht.
    In Zeile 1 sieht man also sehr schön den Trend über die Jahre, von links nach rechts.

    Problem ist, ich kann das nicht runterkopieren.
    Wenn ich den gesamten Bereich markiere, dann färbt er ausgehend von ALLEN Werten ein.

    Ich möchte aber das jede Zeile für sich selbst nach Trend eingefärbt ist.

    Können Sie mir da helfen?

    • Martin Weiß Autor des Beitrags

      Hallo,

      bei einer Farbskala geht das leider nicht. Genauer gesagt hängt es vom Regeltyp ab. Im ersten Regeltyp „Alle Zellen basierend auf ihren Werten formatieren“ lässt sich so etwas nicht einstellen – und das ist genau der Regeltyp, der bei einer Farbskala angewendet wird. Auch der zweite Regeltyp „Nur Zellen formatieren, die enthalten“ ist das nicht möglich.
      Lediglich bei den beiden Regeltypen „Nur obere oder untere Werte formatieren“ und „Nur Werte über oder unter dem Durchschnitt formatieren“ kann man angeben, dass die Regel nur innerhalb einer Zeilengruppe oder Spaltengruppe angewendet werden soll.

      Schöne Grüße,
      Martin