Egal ob Controlling, Fertigungssteuerung oder Budgetplanung. In vielen Bereichen ist es überaus wichtig, schnell und unkompliziert die wichtigsten Kennzahlen im Blick zu haben und zu entscheiden, wo Handlungsbedarf besteht. Das lässt sich unter anderem mittels Ampel-Diagramm umsetzen.

Mit so einem Ampel-Diagramm erkennt jeder, anhand der Farben ob alles im „grünen Bereich“ ist oder ob Handlungsbedarf besteht. Die Ergebnisse sind sehr einfach verständlich und auch von Personen, welche die Datei nicht kennen ohne viel Aufwand zu beurteilen.

In diesem Artikel zeige ich dir wie du für deine Bedürfnisse schnell und einfach ein Ampel-Diagramm erstellen kannst, was du dazu benötigst und Alternativen, wie es ohne Diagramm funktioniert.

Also steigen wir gleich ein.

Wie man ein Ampel-Diagramm erstellt, möchte ich dir Anhand von zwei Beispielen erklären.

Im ersten Beispiel soll eine Ampel mit einem Segment dazu verwendet werden, in einem Haushaltsbuch das jährliche Sparziel zu überwachen.

Als zweites Beispiel sollen zwei Ampeln mit jeweils drei Segmenten dazu genutzte werden, den monatlichen Umsatz einer Brauerei in zwei unterschiedlichen Absatzmärkten zu visualisieren.

Wie immer, findest du zu diesem Artikel eine Übungsdatei, die du nach Belieben nutzen kannst. Lade sie dir hier gleich herunter: Übungsdatei Ampel-Diagramm

Diese Ausgangsdaten benötigst du 

Zur Erstellung des Diagramms benötigen wir nicht viel.

Eine Berechnung ob unsere Zielwerte erreicht wurden, die Grenzen für die Ampelfarben, die Datentabelle für die Ampel und das Diagramm selbst.

Beginnen wir also mit den Zielwerten, die wir erreichen möchten. Oder noch weiter vorne mit unseren Ausgangsdaten. Diese umfassen unser gespartes Geld je Monat und das Ziel, welches wir für dieses Jahr erreichen möchten.

Bei dem Sparziel wollen wir feststellen, ob die aktuellen Werte noch innerhalb unserer Vorgaben sind oder ob wir unsere Sparleistung erhöhen müssen. Das können wir feststellen, indem wir ermitteln, wieviel wir im aktuellen Monat hätten sparen müssen und ob unsere aktuelle Sparleistung größer oder kleiner ist.

  1. Wie viel hätten wir bis zum aktuellen Monat sparen müssen.

Zur Auswertung dient folgende Formel:

=WENN(((D2/12)*(ANZAHL(Tabelle1[gespart])))<Tabelle1[[#Ergebnisse];[gespart]];1;Tabelle1[[#Ergebnisse];[gespart]]/((D2/12)*(ANZAHL(Tabelle1[gespart]))))

Die Formel berechnet in der Prüfung ob unser Ziel unter unserem aktuellen Sparergebnis liegt. Wenn dem so ist, dann ist die Prüfung WAHR und als Ergebnis wird 1 ausgegeben. Weil wir maximal 100% für das Kreisdiagramm benötigen. Wenn die Prüfung FALSCH ergibt, also wenn unsere Sparleistung unter unserem Ziel liegt, dann wird der prozentuale Wert unserer Sparleistung im Vergleich zum Ziel ermittelt.

Dieser Teil berechnet lediglich wie hoch die Sparleistung bis zum aktuellen Monat sein sollte.

((D2/12)*(ANZAHL(Tabelle1[gespart])))

Dabei wird unser Ziel durch 12 dividiert, um den Wert je Monat zu erhalten und mit der Anzahl der aktuellen Monate multipliziert.

Hier die Formel in den einzelnen Schritten berechnet.

=WENN(250<223;1;223/250)

Als Ergebnis erhalten wir in Zelle F4 einen Prozent Wert, der angibt wie viel Prozent vom Ziel erreicht wurde.

2. Die Grenzen für die Ampelfarben definieren.

Das ist eine einfache Liste, die aussagt, bis zu welchen Grenzen, welche Farbe auf der Ampel angezeigt werden soll. Du musst diese Liste in der Form nicht unbedingt erstellen, wie du später noch sehen wirst, könntest du die Grenzen auch direkt in der Formel definieren.

Um nachträglich aber einfach Änderungen vorzunehmen, ist die Liste von Vorteil.

ACHTUNG: Bitte die Liste nicht in eine Tabelle umwandeln, ansonsten werden unsere Formeln später automatisch korrigiert und das wollen wir nicht.

3. Die Datentabelle für die Ampel vorbereiten.

Was uns jetzt noch fehlt, sind Daten, die dem Diagramm sagen, welche Farbe dieses annehmen soll. Diese fügen wir zu unserer bestehenden Liste mit den Grenzwerten hinzu.

Das machen wir mit einer WENN Formel. Die Formel soll bestimmen, ob der Grad unsere Zielerfüllung innerhalb oder außerhalb unserer Grenzen ist.

Hier die Formel für die Zelle H7.

=WENN(UND($F$4>=$F7;$F$4<=$G7);1;0)

Jetzt kommen wir zum Grund, warum du die Liste nicht in eine Tabelle umwandeln darfst. Die Formel in der folgenden Zelle müssen wir nämlich ändern, um auch wirklich alle Werte abzudecken. Also ändere das erste Argument in der UND Funktion von >= in >:

=WENN(UND($F$4>$F8;$F$4<=$G8);1;0)

Die Formel in Zelle J7 kannst du wieder von der Zelle H7 kopieren.

Möchtest du mehr über die WENN Funktion erfahren, dann schau dir jetzt diese Artikel an: Die WENN Funktion.

Nun haben wir alle Daten, die wir brauchen, um das Diagramm zu erstellen. Dann machen wir das auch

Ampel-Diagramm einfügen

Markiere die Werte in der Spalte „Farbe aktiv“ in unserer Tabelle und fügen ein Kreisdiagramm über Registerkarte Einfügen–>Diagramme–>Kreis- oder Ringdiagramme einfügen–>Kreis ein.

Wenn du jetzt die Werte Testweise änderst wirst du sehen, dass sich je nach erreichtem Prozentwert des Zieles, die Diagrammfarbe ändert. Wenn du möchtest, kannst du dir auch gerne eine weitere Kommastelle in den errechneten Prozentwerten deiner Zielerfüllung anzeigen lassen.

Wie du dazu vorgehen musst, zeige ich dir in diesem Artikel: Zellen formatieren

So ganz sieht das aber noch nicht nach Ampel aus. Um das zu ändern, müssen wir das Diagramm anders formatieren.

Ampel-Diagramm formatieren

Jetzt müssen wir unser Kreisdiagramm nur noch etwas formatieren, damit es mehr nach Ampel aussieht.

Ich weiß, dass Thema Diagramm Erstellung und Formatierung ist ein riesiges und füllt so manches Buch. Wenn du dich näher damit beschäftigen möchtest oder nur den ein oder anderen Trick kennen lernen willst, dann schau dir meine Artikelserie dazu an. Den ersten Teil findest du hier: Diagramme Allgemein

Als erstes entfernen wir Titel und Legende im Diagramm. Markiere dazu eines der beiden und drücke die Taste ENTFERNEN.

Als nächstes kümmern wir uns um den Strich im Kreis, der ein mögliches Segment anzeigen würde.

Markiere das Diagramm und drücke die Tastenkombination STRG+1 um das Fenster zum Formatieren des Diagramms aufzurufen.

Markiere dann den Kreis selbst und wähle Füllung und Linien–>Rahmen–>keine Linien. Damit ist die Linie entfernt.

Als nächstes schieben wir das Diagramm an die gewünschte Position. Außerdem passen wir die Größe noch an.

TIPP: Halte während des Verschiebens oder während du die Größe änderst, die Taste ALT gedrückt. Dadurch kannst du das Diagramm an den Zellen ausrichten.

Jetzt fehlen uns nur noch die richtigen Farben.

Ändere dazu die Werte in der Spalte „gespart“ sodass wir 100% erreichen. Dann markierst du wieder den Kreis im Diagramm. Diesmal klickst du ihn aber ein zweites Mal an, dann ändert sich die Markierung in 2 Punkte. Dies zeigt an, dass jetzt nur noch ein Datenpunkt ausgewählt ist. Genau das wollen wir auch erreichen, nur den Datenpunkt für die Farbe Grün auswählen.

ACHTUNG: Würdest du den Kreis kein zweites Mal anwählen, würdest du immer alle Datenpunkte also die gesamte Datenreihe umfärben.

Jetzt kannst du einen beliebigen Grünton wählen.

Mit dem Befehl Füllung und Linien–>Füllung–>Farbe

Das Vorgehen wiederholst du auch bei den anderen beiden Farben.

Dadurch ergibt sich folgendes Bild für die 3 Szenarien:

Das wars dann auch schon. Wir bekommen jetzt angezeigt, ob wir mit unserer Sparleistung unser Ziel erreichen oder ob wir mehr Geld sparen müssen. Ganz einfach visualisiert mit einem Ampel-Diagramm als Anzeige.

So fügst du auch einen Fortschrittskreis ein 

Die Ampel-Anzeige funktioniert soweit schon sehr gut. Jetzt kann es aber vorkommen, dass die Anzeige gesondert in einem Dashboard oder in einem anderen Tabellenblatt verwendet wird.

Ohne die Prozent Werte erkennt man so leider nicht, wie weit der Fortschritt ist. Daher kann man als Ergänzung auch einen Fortschrittskreis einfügen.

Dazu brauchen wir eigentlich nur den Rest auf 100% von unserer Zielerreichung zu berechnen.

Wir erhalten diesen Wert, indem der bisher errechnete Erfüllungsgrat von 1 subtrahiert wird.

Zweite Datenreihe einfügen

Jetzt fügen wir noch eine zweite Datenreihe zu unserem Diagramm hinzu.

Öffne das Dialogfenster „Datenquellen auswählen“. Aktiviere dazu das Diagramm und wähle Registerkarte Entwurf–>Daten auswählen.

In diesem Dialogfeld klickst du auf „Hinzufügen“.

Hier kannst du der Datenreihe einen Namen geben. In unserem Fall würde sich „Fortschritt“ anbieten.

TIPP: Ich empfehle dir, nimm dir die Zeit und benenne jedes Diagramm, jede Datenreihe, jede Tabelle oder jedes Arbeitsblatt eindeutig und verständlich. Das spart dir später viel Zeit und Nerven.

Als Reihenwerte definieren wir unsere beiden Prozentwerte.

Zweimal mit „OK“ bestätigen und die Datenreihe ist zum Diagramm hinzugefügt, auch wenn wir sie aktuell noch nicht sehen.

Um die zweite Datenreihe anzuzeigen folge diesem Pfad.

Registerkarte Entwurf–>Diagrammtyp ändern–>Alle Diagramme–>Verbund

Nun wählst du für beide Diagrammtypen Kreis und aktivierst nur für die Datenreihe „Fortschritt“ die Sekundärachse. Dadurch werden beide Datenreihen übereinandergelegt.

Jetzt erkennt man bereits, dass die Datenreihe „Fortschritt“ als Kreissegment dargestellt wird.

So formatierst du die Fortschrittsanzeige

Wie du sicher bemerkt hast, wurden die zuvor eingestellten Farben wieder verworfen. Also dürfen wir die drei Ampel Datenpunkte gleich nochmal umfärben. Sehr gut, dann bleiben wir in Übung. J

Hier findest du die genau Anleitung.

Bevor du die Datenpunkte umfärben kannst musst du allerdings noch etwas erledigen.

ACHTUNG: Da die Datenreihen übereinander liegen, kannst du keinen Datenpunkt der unteren Datenreihe anwählen. Vorher musst du im Diagrammfilter die Datenpunkte der Datenreihe „Fortschritt“ ausblenden.

Wähle den Diagrammfilter und deaktiviere die Checkbox der beiden Datenpunkten aus der Datenreihe „Fortschritt“ und bestätige mit „Übernehmen“.

Jetzt kannst du die Farben der unteren Datenreihe ändern und anschließend deaktivierst du den Filter wieder.

Um das Diagramm noch etwas ansprechender zu gestalten, müssen wir auch die Datenreihe „Fortschritt“ Formatieren.

Wähle dazu die Datenreihe aus und deaktiviere die Linien, genauso wie oben beschrieben.

Als nächstes wählst du den Datenpunkt, der den Rest zu den 100% darstellt. Wähle als Farbe „weiß“ und stelle die Transparenz auf „30%“. Beim anderen Datenpunkt wählst du bei Transparenz „100%“.

Ich bin schon ganz zufrieden mit unserer Ampel. Alternativ könnte man jetzt noch die Prozentwerte der Zielerreichung einblenden, vorerst belassen wir es aber dabei.

Wenn du mehr über Diagramme und deren Formatierung lernen möchtest und dass kann ich dir nur wärmstens empfehlen, weil es wirklich sinnvoll ist, dann lies meine Artikelserie Diagramme erstellen und formatieren. Du findest diese hier: Diagramme

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.

Zusammenfassung der einzelnen Schritte

Bevor wir nun mit dem Diagramm mit 3 Segmenten weiter machen, möchte ich hier alle Schritte kurz zusammenfassen.

Folgende Schritte sind zusätzlich für die Erweiterung des Ampel-Diagramms mit einer Fortschrittsanzeige notwendig.

So kannst du ein Ampel-Diagramm mit drei Segmenten erstellen

Manchmal kann es Sinn machen, eine Ampel mit drei Segmenten einzusetzen. Also wie du sie wahrscheinlich aus dem Straßenverkehr kennst.

Ob man eine Ampel mit einem oder mit drei Segmenten verwendet hängt einzig und allein von deinen persönlichen Wünschen und eventuellen Designvorgaben ab.

Als Beispiel soll uns wie oben bereits erwähnt, eine Brauerei dienen, welche die Umsatzziele in zwei verschiedenen Absatzmärkten mit der Ampel-Anzeige überwacht. Diese Anzeige soll dann im monatlichen Reporting an die Geschäftsleitung verwendet werden.

So erstellest du die Ampelfarben

Die Ausgangsdaten sind die gleichen, wie bei der Ampel mit einem Segment. Einziger Unterschied besteht im Diagramm selbst. Da immer nur eine Farbe aktiv ist, müssen die anderen beiden Farben währenddessen ausgegraut werden. Diesmal fügen wir außerdem je Farbe ein eigenes Diagramm ein.

Also erstellen wir ein Kreisdiagramm und als Datenreihe wählen wir immer nur eine Farbe aus unserer Datentabelle.

Bei der Auswahl der Datenreihe gehen wir diesmal wie folgt vor. Markiere die Farbe in Spalte „Farbe“ und den dazu gehörigen Wert in der Spalte „Farbe aktiv“. Jetzt wählst du Registerkarte Einfügen–>Diagramme–>Kreis- oder Ringdiagramme einfügen–>Kreis. Nun hast du ein Kreisdiagramm eingefügt und gleichzeitig den Datenreihennamen definiert. Dieser wird zukünftig aus der Zelle in der Spalte Farbe gezogen. So ersparst du dir das nachträgliche händische benennen der Datenreihen.

Das wiederholst du für jede Farbe. Als Hintergrund wählen wir wieder unser Grau.

TIPP: Du kannst das Diagramm ganz einfach kopieren, und die Quelle ändern. Dann geht es etwas schneller.

Wenn du jetzt noch die Diagramme richtig anordnest, sollte das Arbeitsblatt folgendermaßen aussehen, als Beispiel auf dem Bild die Datenreihe für die rote Ampel.

So erstellest du die Platzhalter

Was uns jetzt noch fehlt, sind Platzhalter, die angezeigt werden, wenn eine Farbe gerade nicht aktiv ist.

Dazu fügen wir eine weiter Datenreihe hinzu. Wie das geht, habe ich dir in diesem Absatz gezeigt.

Nun müssen wir nur definieren, was unsere Reihenwerte sind.

Es soll immer dann ein grauer Kreis angezeigt werden, wenn eine andere Farbe aktiv ist.

Das könnten wir machen, indem wir uns eine Hilfsspalte erzeugen, in der der Wert immer 1 wird, wenn der aktuelle Wert von „Farbe aktiv“ 0 ist.

Oder noch besser, wir wählen einfach die beiden Zellen, die nicht der gerade aktiven Farbe entsprechen.

In dem folgenden Bild siehst du die Werte für den Kreis, wenn die Farbe Rot nicht aktiv ist. Ich habe dazu einfach die Werte aus den Zellen Gelb und Grün genommen. Egal welche der beiden Farben aktiv ist, die Rote soll es nicht sein.

ACHTUNG: Wenn du den Platzhalter für die gelbe Farbe erstellst, musst du die beiden Zellen anwählen, indem du die STRG-Taste gedrückt hältst.

Auch hier erzeugen wir wieder einen Diagrammverbund und legen die Datenreihe für den Grauwert auf die Sekundärachse.

Wenn du das bei den anderen beiden Farben wiederholst, hast du es auch schon geschafft. Das Ergebnis sollte nun so aussehen:

Genau das gleich machst du nun auch für die zweite Ampel, dann ist unser Report für die Brauerei-Leitung auch schon fertig.

Leider sind die Umsätze unserer Brauerei nicht besonders zufriedenstellend. Naja, wenn gewünscht könnte ich da etwas unterstützen 😊.

Neben den gerade gezeigten Möglichkeiten eines Ampel-Diagramms gibt es noch weitere Möglichkeiten mittels Ampeln oder Ampelfarben die Wertigkeit von Daten anzuzeigen.

Alternativen zur Erstellung einer Ampel-Anzeige ohne Ampel-Diagramm

Wie du mittels Ampel-Diagramm Daten visualisieren kannst sollte jetzt klar sein. Jetzt möchte ich dir noch weitere Möglichkeiten zeigen Ampel-Symbole in Excel einzusetzen.

So erstellst du eine Ampel-Anzeige mit der bedingten Formatierung 

Eine schnelle und einfache Möglichkeit Daten mit einer Ampel-Anzeige zu visualisieren, bietet der Einsatz einer bedingten Formatierung.

So kannst du in nur wenigen Klicks eine Reihe von Daten analysieren.

Als kleines Beispiel folgende Daten. Angenommen du möchtest dir anzeigen lassen, welche Zahlen im unteren, mittleren und oberen Drittel liegen. Dann kannst du folgender maßen vorgehen.

Markiere die Daten im Arbeitsblatt „bed. From. und Symbolsätze“ in Spalte B und folge dem Pfad: Registerkarte Start–>Gruppe Formatvorlage–>Bedingte Formatierung–>Symbolsätze–>Formen. Nun sind Symbole abhängig von den Zellenwerten eingefügt und das untere, mittlere und obere Drittel mit unterschiedlichen Farben markiert.

Excel drittelt die Daten automatisch. Die Grenzwerte kannst du aber jederzeit deinen Wünschen entsprechend ändern. Wie du das machst, erkläre ich dir in diesem Artikel: Bedingte Formatierung

Bezogen auf unser konkretes Beispiel müssen wir folgendermaßen vorgehen.

Kopiere die Ausgangsdaten des Sparziels in dein Arbeitsblatt.

Markiere alle Zellen von „Jänner“ bis „Dezember“ in der Spalte „gespart“ und füge die Symbolsätze wie vorhin ein.

Allerdings wollen wir diesmal nicht die automatisch voreingestellte drittel Aufteilung, sondern eine die wir bestimmen. Öffne dazu das Dialogfeld „Regeln verwalten“, dass du unter folgendem Pfad findest: Registerkarte Start–>Gruppe Formatvorlage–>Bedingte Formatierung–>Regeln verwalten wähle die erstellte Regel und klicke auf „Regel bearbeiten“. Es wird folgendes Dialogfeld geöffnet:

Nun wählst du in dem Dropdown „Typ“ den Eintrag „Formel“ und gibst im Feld „Wert“ die Formel

=($G$2/12)*0,9

für die grüne Ampelfarbe ein und

=($G$2/12)*0,5

für die gelbe Farbe.

Die Formel berechnet ein zwölftel unseres Sparziels und davon 90% oder 50%.

Diese Werte bestimmen nichts anderes als die Bedingung für das Anzeigen einer grünen, gelben oder roten Ampel. Genau das, was wir mit unseren Formeln in der Datentabelle beim Ampel-Diagramm gemacht haben. Nachdem du durch Bestätigen von „OK“, „Übernehmen“ und nochmal „OK“ die Dialogfelder geschlossen hast, werden die geänderten Symbole angezeigt.

Für übersichtliche Daten wie in diesem Fall, würde ich auf alle Fälle die Variante der bedingten Formatierung bevorzugen. Sie ist schnell und einfach zu erstellen. Außerdem benötigst du keinen zusätzlichen Platz für das Kreisdiagramm.

Du kannst darüber hinaus die Symbole für die Anzeige schnell ändern und anpassen, was dir weitere Flexibilität bietet.

Die bedingte Formatierung bietet noch viele weitere Einsatzmöglichkeiten. Einen ersten Überblick über die bedingte Formatierung findest du in diesem Artikel: Bedingte Formatierung

So erstellst du eine Ampel-Anzeige mit Symbolen

Als nächste Alternative zum Ampel-Diagramm möchte ich dir das Erstellen einer Ampel-Anzeige mit Symbolen zeigen.

In der Schriftart Webdings (Ja die Schriftart heißt wirklich so 😊) werden Buchstaben als Symbole dargestellt. Und der Buchstabe „n“ entspricht einem Kreis.

Dazu brauchen wir also 3 Zellen übereinander mit dem Buchstaben „n“ und der Schriftart Webdings formatiert. Dann noch eine Zelle in welche der IST-Wert eingetragen wird. Außerdem noch etwas bedingte Formatierung. Und wenn du es flexibler haben möchtest, die definierten Grenzen, um diese einfach ändern zu können?

Unsere Ausgangsdaten sehen also folgendermaßen aus. Achte dabei auf den Zelleninhalt in der aktiven Zelle, es ist der Buchstabe „n“ in grauer Schriftfarbe in der Schriftart Webdings. Der Zellenhintergrund ist schwarz definiert.

Soweit eigentlich kein großes Problem. Jetzt kommen wir wieder zur bedingten Formatierung, mit der wir die Ampelfarben definieren.

Markiere die erste Zelle und erstelle eine neue Regel für die bedingte Formatierung. Registerkarte Start–>Gruppe Formatvorlage–>Bedingte Formatierung–>Neue Regel…

Jetzt wählst du „Formel zur Ermittlung der zu formatierenden Zelle verwenden“. Wie der Name schon andeutet, werden wir mittels Formel eine Bedingung erstellen, die die Formatierung der Zelle ändert (in unserem Fall die Farbe), wenn die Bedingung erfüllt ist.

Unsere Bedingung lautet, die Zelle soll nur dann die Farbe Rot annehmen, wenn die Grenze, die wir definiert haben, überschritten wird. Also sieht unsere Formel wie folgt aus:

Für die Farbe Gelb müssen wir wieder zwei Bedingungen verbinden.

Die Farbe Grün ist wieder etwas einfacher definiert.

Und schon hast du über die bedingte Formatierung und eine etwas außergewöhnliche Schriftart eine Ampel-Anzeige erstellt.

Wie sieht das nun in Verbindung mit unseren Daten aus.

Fügen wir dazu wieder unsere Übersicht der Sparrate ein. Anschließend schreiben wir in der Spalte rechts davon in jede Zeile den Buchstaben „n“ in der Schriftart Webdings in grauer Farbe und mit schwarzem Hintergrund.

Nun fehlt noch die bedingte Formatierung. Diesmal wollen wir alle drei Ampelfarben in einer Zelle unterbringen. Dazu müssen wir je Zelle drei Regeln für die Formatierung erstellen.

Dazu nutzen wir wieder die Formeln aus diesem Beispiel hier.

Der Wert in der Zelle soll dann grün werden, wenn der Wert in der Zelle links davon größer oder gleich 90% des Jahreszielwertes durch 12 ist.

Die Formel der bedingten Formatierung für Zelle M5 lautet also

=($L5>=($M$2/12)*0,9)

Dass war auch schon die erste Regel, kümmern wir uns gleich um die zweite.

Bestätige die Eingabe der Formel mit „OK“ und wähle „Neue Regel…“ um eine weitere zu erstellen. Vergiss aber nicht die grüne Schriftfarbe vorher einzustellen.

Der Wert soll gelb werden, wenn der Wert in der Zelle daneben kleiner 90% und größer oder gleich 50% des Jahreszielwertes durch 12 ist.

=UND(($L5<($M$2/12)*0,9);($L5>=($M$2/12)*0,5))

Als letzte Regel müssen wir nun die, für die rote Farbe definieren.

=($L5<($M$2/12)*0,9)

Hast du alles eingegeben, solltest du folgendes Bild im Dialogfeld für die bedingte Formatierung vor dir sehen.

Erweiterst du die Formel in die Zellen darunter, erhältst du folgendes Ergebnis:

Und schon geschafft, das war die zweite Alternative zum Ampel-Diagramm. Und eine weitere Möglichkeit, Daten visuell zu analysieren.

Ich kann es nicht oft genug wiederholen, setz dich bitte mit den Funktionen der bedingten Formatierung auseinander. Das wird dein Spektrum der grafischen Datenauswertung unglaublich erweitern. Sieh dir dazu meinen Einführungs-Artikel an: Bedingte Formatierung

So erstellst du eine Ampel-Anzeige mit Bildern

Eine weitere sehr großartige Möglichkeit mit Bilder eine Ampel-Anzeige zu erstellen, hat Thomas Käflein in seinem Artikel beschrieben, hier kannst du dir den Artikel durchlesen.

Fazit

Wie du siehst, gibt es unterschiedliche Möglichkeiten Datenwertigkeiten mittels Ampelfarben visuell darzustellen. Angefangen vom Ampel-Diagramm über die Bedingte Formatierung bis hin zum Einsatz von Bildern, lassen sich Ampel-Anzeigen vielfältig einsetzen.

Am besten von allen Möglichkeiten finde ich die Variante mit der Bedingten Formatierung in den jeweiligen Zellen. Sie ist platzsparend am einfachsten umzusetzen und meiner Ansicht nach am ansprechendsten.

Der Vorteil einer Ampel-Anzeige liegt für mich ganz klar darin, dass man die Datenwertigkeit sehr schnell erkennen kann auch ohne die Grenzen oder Bedingungen zu kennen. Das ist besonders dann von Vorteil, wenn Nutzer mit der Datei oder der Auswertung arbeiten, welche die Datei vielleicht zum ersten Mal sehen oder nur sehr selten damit konfrontiert sind. Diese erkennen dann sehr schnell, ob alles ok ist oder nicht.

Ich verschicke hin und wieder nützliche Tipps und Tricks rund um Excel per E-Mail. Wenn du diese nicht verpassen möchtest, dann trag dich doch in meinen kostenlosen Newsletter ein. Du kannst dich hier eintragen.

Als kleines Dankeschön erhältst du ein eBook mit 17 nützlichen Tipps, mit denen du in kurzer Zeit dein Excel Können auf den nächsten Level bringst.

Den kostenlosen Newsletter und das Geschenk findest du hier.

Du kannst dich jederzeit auch wieder austragen.

Dein Michael, alias Doktor-Excel der smarte Excel Problem Löser

PS.: Schreib mir doch in die Kommentare, ob du noch weitere Möglichkeiten kennst, mit Excel eine Ampel-Anzeige zu erstellen.

Geballtes Wissen an einem Ort

In meinem Blog findest du viele hilfreiche Artikel, die dir dabei helfen, dein Wissen aufs nächste Level zu heben. Ganz egal ob du nur nach einem einmaligen Problemlöser suchst oder ob du dauerhaft profitieren möchtest. In meinem Blog wirst du fündig.

Geballtes Wissen in einem Buch

Mit diesen hilfreichen Tipps kannst du in nur 17 Minuten, 90% der anderen Excel Nutzer übertrumpfen.