Excel-TippBedingte Formatierung in Diagrammen
Die bedingte Formatierung ist ein sehr nützliches Feature in Excel, mit dem Sie Daten in Abhängigkeit von ihren Werten dynamisch hervorheben können. Leider können Sie die bedingte Formatierung nicht in Diagrammen einsetzen. Mit dem folgenden Trick können Sie Ihre Daten auch in Diagrammen ähnlich wie bei der bedingten Formatierung einfärben.
Beispiel: Diagrammfarbe abhängig von der Umsatzhöhe
Die folgenden Umsatzdaten sollen als Säulendiagramm dargestellt werden. Die Säulen sollen hierbei dynamisch wie folgt eingefärbt werden:
- grün – Umsatz > 2.000
- gelb – Umsatz > 1.000 und <= 2.000
- rot – Umsatz <= 1.000
Mit Hilfsspalten arbeiten
Um ein Säulendiagramm mit „bedingter Formatierung“ zu erstellen, müssen Sie für jede Farbe eine separate Datenreihe erstellen (Hilfsspalten). Es werden dann nur die Datenreihen im Diagramm dargestellt, für die der Wert zutrifft.
Erfassen Sie daher rechts neben der Ausgangstabelle in dem Bereich D2:F2 die folgenden Formeln:
- Zelle D2 – Datenreihe für grüne Säule: =WENN(B2>2000;B2;NV())
Die Formel besagt: Wenn der Umsatz in München (B2) größer als 2000 ist, dann wird der Umsatz als Wert ausgegeben. Sonst wird der Fehlerwert #NV als Ergebnis ausgegeben. Der Fehlerwert #NV bewirkt in Diagrammen, dass keine Darstellung erfolgt. - Zelle E2 – Datenreihe für gelbe Säule: =WENN(UND(B2>1000;B2<=2000);B2;NV())
Hier gilt die Bedingung: Der Umsatz in München (B2) muss größer als 1000 und kleiner oder gleich 2000 sein; dann wird er als Wert ausgegeben. Sonst wird der Fehlerwert #NV als Ergebnis ausgegeben. - Zelle F2 – Datenreihe für rote Säule: =WENN(B2<=1000;B2;NV())
Sollte der Umsatz in München kleiner oder gleich 1000 sein, dann wird der Umsatz – sonst der Fehlerwert #NV() dargestellt.
Sie erhalten somit den Umsatzwert immer in genau einer Spalte dargestellt. In den anderen beiden Spalten wird der Fehlerwert #NV ausgegeben.
Als nächstes kopieren Sie die Formeln für die anderen Filialen entsprechend nach unten, damit Sie für jede Filiale einen Wert in den Hilfsspalten darstellen. Hinweis: Der Umsatzwert darf genau einmal in den Hilfsspalten pro Filiale erscheinen. In den anderen beiden Hilfsspalten muss der Fehlerwert #NV ausgegeben werden.
Das dynamische Diagramm erstellen
Markieren Sie jetzt den kompletten Datenbereich der Hilfsspalten (D1:F6) inklusive der ersten Zeile mit den Spaltenbeschriftungen (grün, gelb, rot) und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Einfügen > Befehlsgruppe Diagramme > Befehl Säulen- oder Balkendiagramme einfügen > Befehl Gestapelte Säulen.
Excel erstellt automatisch ein gestapeltes Säulendiagramm als separates Objekt. Das Diagramm schaut auf den ersten Blick schon sehr gut aus, da grundsätzlich immer nur eine Datenreihe dargestellt wird. Die anderen beiden Datenreihen, die nicht sichtbar sind, haben als Wert den Fehlerwert #NV als Quelle. Sie können dies an den unterschiedlichen Farben erkennen. Allerdings stimmen die Farben nicht mit den gewünschten Ampelfarben überein.
Die gewünschten Diagrammfarben einstellen
Im nächsten Schritt müssen Sie die Farben der Säulen anpassen. Klicken Sie einmal mit der linken Maustaste auf die erste Säule im Diagramm. Hierdurch markieren Sie die Datenreihe für die graue Säule. Sie können dies an den Markierungspunkten im Diagramm erkennen, die nur die grauen Säulen im Diagramm umranden.
Drücken Sie jetzt als nächstes die Tastenkombination Strg + 1. Durch diese Tastenkombination wird am rechten Rand der Aufgabenbereich Datenreihen formatieren eingeblendet. Wählen Sie hier oben das Symbol für Füllung und Linie aus und aktivieren Sie anschließend das Optionsfeld Einfarbige Füllung. Unter Füllfarbe wählen Sie die Farbe Rot aus, da die ausgewählte Datenreihe dieser Ampelfarbe (<=1000) entspricht. Welche Säule welche Farbe erhalten soll, sehen Sie auch mithilfe der Legende (grau = rot).
Aufgrund dieser Einstellung wird die markierte Datenreihe (Säulen) automatisch rot eingefärbt.
Wiederholen Sie diese Schritte jetzt für die anderen beiden Hilfsspalten (Säulen), indem Sie diese grün und gelb einfärben.
Wenn Sie jetzt die Umsatzwerte in der Spalte B verändern, dann passen sich die Säulen und deren Farben entsprechend dynamisch an.
Beschriftung richtig einstellen
Die horizontale Achsenbeschriftung im Diagramm stimmt allerdings noch nicht, da hier nicht die Namen der Städte angezeigt werden. Diese Beschriftung können Sie mit wenigen Klicks anpassen. Vergewissern Sie sich, dass das Diagramm im Tabellenblatt ausgewählt ist, und aktivieren Sie im Menüband die Befehlsfolge Kontextbezogene Registerkarte Diagrammentwurf > Befehlsgruppe Daten > Befehl Daten auswählen.
Es öffnet sich das Dialogfeld Datenquelle auswählen. Klicken Sie hier auf die Schaltfläche Bearbeiten.
Es wird das Dialogfeld Achsenbeschriftungen geöffnet. Erfassen Sie hier unter Achsenbeschriftungsbereich den Bereich, in dem die Stadtnamen stehen. Dies ist im Beispiel der Bereich A2:A6.
Schließen Sie alle offenen Dialogfelder durch Klick auf Ok. Sie erhalten jetzt die Namen der Städte als horizontale Achsenbeschriftung angezeigt. Ändern Sie den Stadtname in der Spalte A, dann wird der Name im Diagramm ebenfalls geändert.
Im letzten Schritt vergeben Sie nun noch einen aussagekräftigen Diagrammtitel. Klicken Sie mit der linken Maustaste auf dem Diagrammtitel, so dass dieser ausgewählt ist. Durch einen erneuten Klick mit der linken Maustaste auf dem Diagrammtitel wird der Bearbeitungsmodus aktiviert. Sie können jetzt mit der Tastatur einfach einen Titel erfassen.
Und wenn Sie die Legende (unten) für überflüssig halten, klicken Sie diese an und entfernen (Entf) Sie die Legende.
