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.
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:
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.
Das 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.
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”.
Stellen 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.
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”.
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:
Am Dienstag zeige ich, wie die Datenbeschriftung ordentlich in die Säulen kommt.
