Office-Blog

Ein Besucher unseres Blogs stellte heute die Frage, was einzustellen ist, damit die Säulen eines Diagramm je nach Wert automatisch eine von 3 Farben annehmen.

• Farbe 1 sollen alle Säulen mit Werten kleiner 6  bekommen,
• Farbe 2 soll bei Werten zwischen 6 und 15 zugewiesen und
• Farbe 3 soll bei Werten größer 15 verwendet werden.

Vorschau auf das Ampel-Diagramm, in dem die Säulen je nach Wert automatisch 3 unterschiedliche Farben erhalten

In der folgenden Anleitung beschreibe ich, wie sich diese Aufgabe in 5 Schritten mit Hilfe eines gestapelten Säulendiagramms lösen lässt.

Ampel-Diagramme bieten gleich 2 Aussagen

Das kennen wir alle: Je mehr Säulen zu sehen sind, desto schwieriger fällt es uns, die entscheidenden Aussagen zu finden und herauszufiltern. Mit Farben lässt sich steuern, was erkannt werden soll und wo z. B. abweichende Werte liegen.

Das oben gezeigte Ampel-Diagramm liefert zweierlei Informationen:

1. Es erlaubt einen Mengenvergleich der monatlichen Werte.
2. Zugleich zeigt es, wann bestimmte Grenzwerte unter- oder überschritten wurden.

1) Die Datenbasis für das Ampel-Diagramm anlegen

Die folgende Abbildung zeigt die Ausgangstabelle mit den Werten für 12 Monate. Darüber in den Zellen D2 und E2 stehen die beiden Grenzwerte, die beim Zuweisen der Farben relevant sind.

Die Ausgangstabelle, über der bereits die zwei Grenzwerte stehen

Damit die Säulen automatisch in einer der 3 Farben erscheinen können, müssen nun in Hilfsspalten (hier Spalte D bis F) die Daten für 3 mögliche Säulensegmente vorbereitet werden.

• Dazu gebe ich in D5, E5 und F5 die unten gezeigten WENN-Formeln ein:
  D5: =WENN(C5<$D$2;C5;NV())
  E5: =WENN(UND(C5>=$D$2;C5<=$E$2);C5;NV())
  F5: =WENN(C5>$E$2;C5;NV())
• Diese drei Formeln kopiere ich dann in jeder Spalte nach unten bis Zeile 16.

Diese WENN-Funktionen in D5, E5 und F5 sorgen für die passenden Daten in dem Hilfsspalten

Mit den WENN-Formeln stelle ich sicher, dass pro Monat nur ein Wert als sichtbare Säule im Diagramm erscheint. Die Farben dieser Säule steuere ich dann nach dem Erstellen des Diagramms.

Profi-Tipp

Möglicherweise fragen sich einige „Warum steht in den WENN-Formeln jeweils NV() und nicht einfach nur 0?“

Die Antwort: Alle Nullen würden später beim Zuweisen der Datenbeschriftungen für die Säulensegmente angezeigt und für Chaos sorgen. Die Funktion NV() hingegen bewirkt in der Tabelle die Anzeige von #NV und dies wiederum wird bei den Datenbeschriftungen nicht berücksichtigt.

Auf Basis der so vorbereiteten Daten kann das Diagramm erstellt werden

2) Das gestapelte Säulendiagramm erstellen

• Ich markiere zunächst B5 bis B16, also alle Monatsnamen.
• Mit gedrückter Strg-Taste erweitere ich die Markierung um die Daten in den 3 Hilfsspalten, also D5 bis F16.
• Dann wähle ich Einfügen > Diagramme > 2D-Säule > Gestapelte Säulen.

Mit Strg die Spalten B, D, E und F markieren und dann den Diagrammtyp Gestapelte Säulen wählen

• Gleich nach dem Einfügen des Diagramms wechsle ich zur Registerkarte Diagrammentwurf (in älteren Versionen Diagrammtools/Entwurf).
• Dort wähle ich links bei Schnelllayouts das Layout 2.
• Damit habe ich das Diagramm zu 80 % in die gewünschte Form gebracht.
• Nur Diagrammtitel und Legende stören noch und werden gelöscht.

Mit dem Schnelllayout 2 das Diagramm in Form bringen

3) Die Farben im Diagramm anpassen

In der Rohversion des Diagramms weise ich jetzt die gewünschten drei Farben zu.

• Dazu klicke ich auf die erste sichtbare Säule links. Alle Säulen, die zu dieser Wertegruppe gehören, sind nun markiert.
• Dieser Segmentgruppe weise ich über die Registerkarte Format (früher Diagrammtools/Format) und Fülleffekt die erste Farbe zu.
• Beide Schritte wiederhole ich, um die Farben für die anderen beiden Segmentgruppen festzulegen.

4) Die Position der Datenbeschriftungen ändern

Momentan sind die Datenbeschriftungen in jedem der Säulensegmente vertikal mittig zentriert. Das ändere ich wie folgt:

• Ich klicke eine der Datenbeschriftungen an. Alle Beschriftungen, die zu dieser Wertegruppe gehören, sind nun markiert.
• Mit Strg + 1 öffne ich den unten gezeigten Aufgabenbereich.
• Dort klicke ich oben rechts auf das Symbol für Beschriftungsoptionen (das kleine Symbol mit dem Säulendiagramm).
• Unter Beschriftungsposition klicke ich folgende Option an: Am Ende innerhalb.
• Den Schritt wiederhole ich für die Datenbeschriftungen der anderen beiden Wertegruppen.

Die Position der Datenbeschriftung ändern

5) Feinschliff: In der Tabelle die Anzeige #NV unsichtbar machen

Viele werden sich an den zahlreichen Zellen mit #NV stören. Dieser Mangel ist mit einem kleinen Kunstgriff schnell behoben:

• Ich markieren die Zellen, in denen #NV vorkommen kann, also D5 bis F16.
• Auf der Registerkarte Start wähle ich Bedingte Formatierung > Neue Regel und dann den letzten Regeltyp.
• Als Formel gebe ich ein =ISTNV(D5).
• Anschließend klicke ich rechts unten auf Formatieren.
• Im folgenden Dialogfeld stelle ich auf der Registerkarte Schrift die gleich Schriftfarbe ein, die die Zellen D5 bis F16 als Füllfarbe haben (hier im Beispiel ist es ein helles Grau).
• Mit zweimal OK schließe ich den Vorgang ab.

Mit diesem Trick wird die Anzeige von #NV ausgeblendet

Tipp: Positive und negative Werte automatisch in Grün und Rot

Wer wissen möchte, wie sich Säulen mit positiven Werten automatisch in Grün und die mit negativen Werten automatisch in Rot anzeigen lassen, findet in diesem Beitrag von Ute Simon eine detaillierte Anleitung.

Beispieldatei mit Ampel-Diagramm zum Download