Excel – Diagramm mit bedingter Formatierung

In Excel lassen sich Zellen mit bedingter Formatierung Zellen in Abhängigkeit vom Zellinhalt einfärben: ist die Zahl größer als 100, dann fülle die Zelle rot. Für Diagramme gibt es das leider nicht. Aber man es mit einem Trick nachbauen.

Erstellt werden soll ein Diagramm, bei dem die Säulen rot eingefärbt werden, wenn ein Zielwert überschritten wird.

09.01.2020_0002

Im April war der Zielwert 130 und wurde weit überschritten; in den anderen Monaten bleiben die Zahlen unter dem Zielwert.

Ich werde in zwei Folgen zeigen, wie man dieses Diagramm erstellt.

Heute geht es darum, die Säulen rot einzufärben, sobald die Zahl den Zielwert überschreitet und die Beschriftung für den Zielwert einzustellen. Am Dienstag, den 11.02.2020, zeige ich, wie die Beschriftungen in die Säulen kommen.

Wie gesagt, Excel kennt keine bedingte Formatierung für Diagramme. Wir müssen also tricksen.

Die Ausgangstabelle sieht so aus:

09.01.2020_0003

In Spalte A stehen die Monate, in Spalte B die Zielzahlen und in C die Kosten. Soweit so normal. In D und E berechne ich, ob die Kosten über oder unter dem Ziel sind.

Die Formel in D2:
=WENN(C2>B2;C2;““)

Die Formel in E2:
=WENN(C2<B2;C2;““)

In D2 wird berechnet: wenn die Zahl in C2 größer als die in B2 ist, dann schreibe den Wert aus C2. Ansonsten schreibe nichts (“”). Das passiert im Monat April – 180 ist größer als 130, also wird 180 geschrieben. In allen Monaten sind die Werte kleiner und die Spalte D bleibt leer.

In E wird berechnet: wenn die Zahl in C2 kleiner als in B2 ist, dann schreibe den Wert aus C2. Ansonsten soll wieder nichts geschrieben werden. Jetzt bleibt nur der April leer, alle anderen Monate liegen unter dem Zielwert und werden ausgefüllt.

09.01.2020_0004Das Diagramm wird jetzt aus den Spalten A (die Beschriftung), B (den Zielwerten) und den Spalten D und E erstellt. Markieren Sie also A1:A7, B1:B7, D1:E7.

Wählen Sie “Einfügen | Diagramm | 2D-Säulen – Gruppierte  Säulen”. Sie erhalten jetzt ein Diagramm mit Säulen für Ziel, drüber und drunter. Daraus wird anschließend ein Verbunddiagramm gebaut, bei dem das Ziel als Linie gezeigt wird.

Klicken Sie in das Diagramm und wählen Sie “Diagrammentwurf | Diagrammtyp ändern”. Wählen den Typ “Kombi”. Für die Datenreihe “Ziel” stellen Sie ein “Linie mit Datenpunkten”, die Datenreihen “drüber” und “drunter” bleiben “Gruppierte Säulen”. Es wird keine Sekundärachse eingestellt. Bestätigen Sie das mit OK.

09.01.2020_0005

Klicken Sie im Diagramm die Säulen für die Datenreihe “drunter” mit rechter Maustaste an und wählen Sie “Datenreihe formatieren”. Wählen Sie die Füllfarbe “grün”.

Klicken Sie dann die Säule für die Datenreihe “drüber” an und füllen Sie sie mit roter Farbe.

Probieren Sie aus, ob es funktioniert, indem Sie für einige Monate die Kosten höher als die Zielzahl setzen – diese | müssen jetzt rot gefärbt werden.

Allerdings sind die Säulen unterschiedlich weit auseinander, weil die leeren Zellen ja auch irgendwie dargestellt werden – eben als Leerraum. Markieren Sie eine Säule, egal aus welcher Datenreihe und wählen Sie im Aufgabenbereich “Datenreihe formatieren” den Punkt “Datenreihenoptionen”.

09.01.2020_0006Stellen Sie ein:

  • Reihenachsenüberlappung 100  %
  • Abstandsbreite 150 % oder ein ähnlicher Wert – je höher der Wert ist, desto schmaler werden die Säulen und die Abstände umso breiter. Wenn Sie dicke Balken möchten, dann stellen Sie den Wert kleiner.

Markieren Sie die Linie für die Zielwerte. Wählen Sie “Diagrammentwurf | Diagrammelement hinzufügen”. Klicken Sie auf “Datenbeschriftungen | Über”. Alternativ können Sie rechts neben dem Diagramm auf das Pluszeichen klicken, dann auf “Datenbeschriftung” zeigen, das Dreieck am rechten Rand anklicken und dann “Über” wählen.

09.01.2020_0007

Lassen Sie die blaue Ziellinie markiert und wählen Sie bei den Datenreihenformatierung den Punkt “Füllung und Linie”. Stellen Sie ein “Keine Linie”. Klicken Sie auf den Punkt “Markierung” und wählen auch hier “Keine Markierung”.

09.01.2020_0008

Klicken Sie jetzt im Diagramm eine der Zahlen der Ziel-Beschriftung an – es müssen alle Zahlen mit Kästchen markiert werden. In den Formatierungen wählen Sie den Punkt “Füllung und Linie” und geben der Textbox eine weiße Füllung. Das ist wichtig, weil man die Beschriftung sonst nicht mehr lesen kann, sobald eine Säule den Zielwert überschreitet. Sie können den Textfeldern auch eine dünne rote Rahmenlinie geben.

Jetzt sieht das Diagramm so aus:

09.01.2020_0009

Am Dienstag zeige ich, wie die Datenbeschriftung ordentlich in die Säulen kommt.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s