In diesem Tutorial wird gezeigt, wie Sie Excel-Diagramme mit bedingter Formatierung in allen Excel-Versionen erstellen: 2007, 2010, 2013, 2016 und 2022.
Bedingte Formatierung ist das Zuweisen einer benutzerdefinierten Formatierung zu Excel-Zellen – Farbe, Schriftart usw. – basierend auf den angegebenen Kriterien (Bedingungen). Die Funktion hilft dabei, Daten zu analysieren, statistisch signifikante Werte zu finden und Muster innerhalb eines bestimmten Datensatzes zu identifizieren.
Aber leider bietet Excel keine integrierten Tools zum Anwenden der bedingten Formatierung auf Excel-Diagramme.
Mit einem Tropfen Tabellenkalkulationsmagie ist jedoch kein Problem unüberwindbar. In diesem Tutorial erfahren Sie, wie Sie ein dynamisches Diagramm mit bedingter Formatierung erstellen, bei dem die gezeichneten Datenpunkte automatisch basierend auf ihren jeweiligen tatsächlichen Werten formatiert werden.
Schauen Sie sich als Beispiel dieses Säulendiagramm an, in dem die Leistung eines Vertriebsleiters über einen Zeitraum von acht Monaten aufgeschlüsselt wird. Beachten Sie, wie sich die Farbe der Spalte automatisch an den zugrunde liegenden Wert anpasst:
Einstieg
Betrachten Sie dieses Beispiel-Dataset mit Verkaufsleistungsdaten:
Bevor wir beginnen, verschieben Sie Ihr Dataset so, dass Sie über der Tabelle drei leere Zeilen mit Ihren Rohdaten haben. Die Zeilen werden zum Festlegen von Regeln für die bedingte Formatierung verwendet.
Schritt 1: Bereiten Sie die Diagrammdaten vor.
Damit die Dinge funktionieren, müssen Sie der Gleichung einige zusätzliche Diagrammdaten hinzufügen. Werfen Sie einen Blick darauf, wie Ihre Diagrammdaten am Ende der Vorbereitungsphase aussehen sollten:
Unser erster Schritt besteht darin, die Grenzwerte (Bedingungen) zu definieren, die die tatsächlichen Werte in mehrere Kategorien aufteilen sollen, sodass sie als separate Datenreihen dargestellt werden können.
Aus dem obigen Screenshot können Sie beispielsweise sehen, dass die Verkaufszahlen für einen bestimmten Monat im Bereich von 90.000 bis 150.000 US-Dollar liegen müssen, um als zufriedenstellend zu gelten.
Verwenden Sie zum Erstellen der Regeln die drei leeren Zeilen über dem Dataset:
- Zeile 1 (Etikett): Verwenden Sie diese Zellen zum Beschriften der Formatierungsregeln.
- Zeile 2 (Min. Wert): Die Werte in dieser Zeile definieren die untere Grenze für jede Bedingung.
- Zeile 3 (Max. Wert): Ebenso legen diese Werte die oberen Grenzen fest.
Mit dieser Methode können Sie beliebig viele Regeln für die bedingte Formatierung erstellen - achten Sie nur darauf, dass sich die Wertebereiche nicht überschneiden.
Entwerfen Sie beim Einrichten Ihres Diagramms benutzerdefinierte Elemente für die Diagrammlegende, die den Kontext für das Diagramm bereitstellen. Geben Sie diese Formel ein in C4 und kopiere es nach D4 durch Ziehen des Füllziehpunktes:
| 1 | =C1&": von "&TEXT(C2, "$#,##")&" bis "&TEXT(C3, "$#,##") |
Die Formel stellt basierend auf den zuvor festgelegten Regeln für die bedingte Formatierung ein übersichtliches dynamisches Label zusammen. Die TEXT-Funktion formatiert die Werte als Währung. Wenn sich Ihr Datentyp jedoch unterscheidet, wenden Sie stattdessen diese Formel an:
| 1 | =C1&": von "&TEXT(C2, "#,##")&" bis "&TEXT(C3, "#,##") |
Oder diese, wenn Sie mit Prozentsätzen arbeiten:
| 1 | =C1&": von "&TEXT(C2, "#%")&" bis "&TEXT(C3, "#%") |
Suchen Sie schließlich die Diagrammwerte. Geben Sie die folgende Formel ein in C5, kopiere es runter nach C12 und hinüber zu den passenden Zellen in Spalte D:
| 1 | =WENN(UND(C$2<$B5,$B5<=C$3),$B5,NA()) |
Die Formel vergleicht einen gegebenen Istwert in Spalte B mit Hilfe der WENN- und UND-Funktionen gegen die vorgegebenen Grenzwerte. Wenn der Wert innerhalb des Bereichs liegt, wird er in diese Spalte kopiert. Andernfalls gibt die Formel den Fehler #N/A zurück, um zu verhindern, dass derselbe Wert mehrmals geplottet wird.
Schritt 2: Richten Sie ein Säulendiagramm ein.
Nachdem Sie alle Diagrammdaten gesammelt haben, richten Sie ein einfaches Säulendiagramm oder alternativ ein Balkendiagramm ein:
- Heben Sie alle Diagrammdaten mit Ausnahme der Spalten mit den tatsächlichen Werten und den Regeln hervor, indem Sie die Taste . gedrückt halten Strg Schlüssel (A4:A12 und C4:D12).
- Gehe zum Einfügung Tab.
- Auswählen "Säulen- oder Balkendiagramm einfügen.”
- Wählen "Gruppierte Spalte/Gruppierte Leiste.”
Als Ergebnis erhalten Sie ein Diagramm mit Datenpunkten, die basierend auf den Regeln für die bedingte Formatierung gezeichnet wurden – genau wie versprochen:
Schritt 3: Ändern Sie die Werte für Überlappung und Lückenbreite.
Beheben Sie vor dem Schließen dieses Tutorials das Problem der falsch platzierten Spalten, das durch die unsichtbaren leeren Werte (die #N/A-Fehler) im Diagramm verursacht wird.
Klicken Sie mit der rechten Maustaste auf eine der Spalten und wählen Sie „Datenreihen formatieren“ aus dem Kontextmenü, das sich öffnet.
Ändern Sie im Aufgabenbereich die Position und Breite der Spalten:
- Wechseln Sie zum Serienoptionen Tab.
- Ändern "Serienüberlappung" zu "100%.”
- Stellen Sie die Spaltbreite zu "60%.”
Schritt #4: Passen Sie das Farbschema an.
Fügen Sie zuletzt den letzten Schliff hinzu. Ohne das zu schließen Datenreihen formatieren Aufgabenbereich, ändern Sie das Farbschema des Diagramms:
- Gehe zum Füllen & Linie Tab.
- Unter "Füllen," wählen "Feste füllung.”
- Drücke den "Füllfarbe”-Symbol und wählen Sie Grün aus der Farbpalette.
Dort angekommen, färben Sie die anderen Datenreihen neu ein, ändern Sie den Diagrammtitel, und Ihr vollständig dynamisches Diagramm mit bedingter Formatierung ist einsatzbereit!
