Mit Excel–Ampeln auf die Überholspur wechseln

Google findet zu dem Suchbegriff „Ampel Excel“ rund 50‘000 Einträge.

Offenbar ein sehr gesuchtes Thema. Tatsächlich ist die „Ampel“ ein äusserst nützlicher Helfer in vielen Situationen. In diesem Blog-Beitrag  wird Schritt für Schritt erklärt, wie sich eine Ampel bauen lässt. Ich bitte Sie, das Beispiel wie die Lösung selbst nachzubauen.

1 Wofür zu gebrauchen?

Unter anderem können durch eine Ampel 2 bis 3 Zustände einer Zahl visuell hervorgehoben werden. Der Betrachter sieht sofort, wo Abweichungen vorhanden sind. Bei wenigen Zahlen ist dies auch ohne Ampel kein Problem. Hat man mit sehr vielen Daten zu tun, ist die Visualisierung ein starkes Hilfsmittel.

Ein einfaches Beispiel als Ausgangsbasis.

2 Parameter setzen

Der Umsatz von 10 wird als Neutral festgelegt. Alles darunter ist Negativ, alles darüber Positiv.

3 Lösungsvorschlag

Hier eine Lösung mit integrierter visueller Entscheidungshilfe. (Darstellung Excel 2010)

Lesbarer oder nicht – Ihr Urteil?

4 Nachbau der Ampel

Bauen Sie das Beispiel zuerst selbstständig nach.

Die Zellen D5 bis I5 sind vorerst leer. Im Feld D5 wird der Buchstabe „n“ eingetragen. Dieser verwandelt sich durch die Formatierung mit der Schriftart Webdings in einen Kreis.

Hier als Bild dargestellt.

In D5 steht nun ein schwarzer Kreis anstelle von „n“. Dieser Kreis wollen wir mittels „Bedingte Formatierung“ ein.

Vielleicht kennen Sie diese nützliche Formatierung noch nicht. Deshalb ein kleiner Exkurs in diesen Bereich. Die Möglichkeiten werden in einem späteren Blog-Beitrag ausführlicher erklärt.

4.1 Bedingte Formatierung – Aufgepasst Unterschiede vorhanden!

Die optische Darstellung dieser Formatierung ist nicht in allen Office-Versionen gleichgeschaltet. Daher zeige ich die „Bedingte Formatierung“ jeder Excel-Version (2003, 2007, 2010) einzeln. Springen Sie einfach zu derjenigen Version, mit welcher Sie arbeiten.

Identisch bleibt die eigentliche Formel sowie das Einfärben der Schriftart in Grün, Gelb und Rot.

4.1.1 Bedingte Formatierung – Excel 2003

Zu finden ist die „Bedingte Formatierung“ in der Menüleiste unter Format / Bedingte Formatierung.  Mit dieser Version können Sie die drei Bedingungen direkt nacheinander eintragen. Excel 2003 lässt nur drei Bedingungen zu, dies nebenbei.

Die drei Regeln ergeben dieses Bild. Selbstverständlich ist es gleichgültig mit welcher Regel begonnen wird.

Die erste Ampel ist beinahe fertig. Kopieren Sie jetzt den gesamten Zellinhalt nach rechts, um die Visualisierung zu komplettieren. Mit jeder Veränderung der Umsätze ändert die jeweilige Ampel ebenfalls. Der Hintergrund fehlt noch, gehen Sie nun direkt zum Punkt „Hintergrund“.

4.1.2 Bedingte Formatierung – Excel 2007

Klicken Sie auf das unten ersichtliche Icon. Sie finden es in der Registerkarte „Start“.

Wählen Sie den Menuepunkt „Neue Regel“, klicken „Formel zur Ermittlung der zu formatierenden Zellen verwenden“ an und füllen die Formel ein. Danach wird die Schriftfarbe ausgewählt.

Schliessen Sie mit OK ab. Gehen Sie erneut in die „Bedingte Formatierung“ und wählen „Regeln verwalten“. Nun werden die bestehende Regel für die Zelle D5 angezeigt. Tragen Sie nun die anderen beiden Regeln ein. Klicken Sie dazu auf „Neue Regel“ und tragen die nächste Bedingung ein. Das gleiche Prozedere für die Dritte.

Bitte beachten Sie, dass bei „Wird angewendet auf“ =D5 die Formel eventuell manuell überschrieben werden muss. Excel zeigt automatisch einen absoluten Wert an. Das bedeutet, die Formel ändert sich in =D$5$.

Die drei Regeln ergeben dieses Bild. Selbstverständlich ist es gleichgültig mit welcher Regel begonnen wird.

Sobald der Vorgang mir „OK“ abschlossen wird, wird die Zelle D5 eingefärbt. Kopiert man die ganze Zelle nach rechts, verändert sich die „Bedingte Formatierung“ im Hintergrund. Die Formel wird „absolut“ über den ausgesuchten Bereich dargestellt. Dies sieht dann folgendermassen aus.

Sie könnten die oben gezeigte Formel (über den gesamten Bereich) auch direkt eintragen. Dieser Punkt überlasse ich Ihren Kenntnissen über Formeln.

Das Thema „absolute oder relative Werte“ wird in einem anderem Blog-Beitrag genauer erklärt.

Die erste Ampel ist beinahe fertig. Kopieren Sie jetzt den gesamten Zellinhalt nach rechts, um die Visualisierung zu komplettieren. Mit jeder Veränderung der Umsätze ändert die jeweilige Ampel ebenfalls. Der Hintergrund fehlt noch, gehen Sie nun direkt zum Punkt „Hintergrund“.

4.1.3 Bedingte Formatierung – Excel 2010

Gleiches Vorgehen wie bei Excel 2007. Die Ansicht kann optisch je nach Betriebssystem und den persönlichen Einstellungen leicht variieren.

Zusatzinfo: Alternative

Anstelle einer Formel lässt sich das erklärte Beispiel ebenso über den Zellwert lösen. Probieren Sie aus, was Ihnen besser gefällt oder besser ins Konzept passt.

5 Hintergrund

Um die Visualisierung noch deutlicher aufzuzeigen, ist es empfehlenswert den Hintergrund der Zellen einzufärben. Dazu eigenen sich möglichst dunkle Farben oder Muster.

Dieser Schritt wird meinerseits mit Excel 2010 durchgeführt, ist jedoch bei allen Versionen genau gleich.

Markieren Sie auf die Zellen D5 bis I5. Dann wählen Sie Ihre gewünschte Zellenfarbe.

Nun sind die Ampeln als solche zu erkennen.

6 Umsatzänderung = Anpassung der Ampel

Mit jeder Veränderung der Umsätze ändert die jeweilige Ampel ebenfalls. Der Wert von 10 (neutral) ist der Einfachheit halber direkt in der Formel integriert. Ist eine Dynamik erwünscht, muss die betroffene Zelle in die Formel eingebunden werden. Bei Problemen damit einfach Kommentar hinterlassen.

7 Visuelle Alternative

Farben sind nicht Ihr Ding oder Sie suchen etwas Spezielles?

Markieren Sie die Zellen D5 bis I5. Entfernen den Inhalt sowie die gesamte Formatierung (Schrift und Hintergrund)

Dann schreiben Sie die nachstehende  Formel in die Zelle D5 =WENN(D4=10;”K”;WENN(D4>10;”J”;”L”)). Die Schriftart ist auf Wingdings zu ändern. Nach rechts kopieren. Fertig ist das folgende Bild. Die Grösse der Smilies können Sie durch die Anpassung der Schriftgrösse steuern.

Varianten der Ampel und deren Anwendung gibt es viele. Viel Spass beim Ausprobieren!