Excel Ampeln

Workshops > Excel

Jeder kennt Ampeln und deren Aussage. Durch die drei Farben grün, gelb und rot symbolisieren sie für jeden verständlich, ob zum Beispiel ein Wert einer Vorgabe entspricht (grün), nur teilweise (gelb) oder gar nicht entspricht (rot).

Mit Office 2007 hat Microsoft diese Ampelsymbolik in der bedingten Formatierung integriert. Diese funktioniert zufriedenstellend, lässt aber in der Optik einige Wünche offen.

In diesem Workshop sehen Sie eine andere Möglichkeit Ampeln in Ihren Excel-Mappen einzusetzen.

Das folgende Bild zeigt eine Quartalsauwertung mit Vorgabewerten und deren Erreichung. Neben den Ampelsymbolen erkennen Sie auch die per bedingter Formatierung eingefügten Symbole unterhalb der Tabelle. Im Workshop zeige ich Ihnen auch, wie diese eingefügt werden können.

Ampel mit bedingter Formatierung erstellen

Die Erzeugung der Ampelsymbole mithilfe der bedingten Formatierung ist einfach und schnell.

Kopieren Sie die Prozentsätze per Formel in die Zellen, in denen die Symbole erscheinen sollen.
Markieren Sie die Zellen.
Im Register Start, Abschnitt Formatvorlagen klicken Sie auf Bedingte Formatierung und danach auf Symbolsätze. Wählen Sie das für Sie passende Format aus (hier die Dreier-Version mit schwarzem Hintergrund).
Die Symbole werden eingefügt und stehen jeweils mit den Werten in der Zelle.
Wählen Sie nochmals das Symbol Bedingte Formatierung und danach den Eintrag Regeln verwalten. Die eben erstellte Regel ist aufgeführt. Mit einem Klick auf Regel bearbeiten passen Sie diese an.
Aktivieren Sie das Optionskästchen Nur Symbol anzeigen, um den Prozentwert auszublenden.
Die Bezüge geben die Zellen an, in denen die Grenzwerte stehen (siehe Bild unten).

Sie sehen, dass per Bedingter Formatierung erstellte Ampeln sehr schnell integriert und sicherlich in den meisten Fällen ausreichend sind. Der Austausch der verwendeten Bilder ist bei dieser Vorgehenswweise nicht möglich. Sie müssen sich mit den von Microsoft angebotenen Varianten begnügen.

Ampel mit individuellen Bildern erstellen

Eine gänzlich andere Vorgehensweise ist notwendig, wollen Sie eigene Bilder verwenden. Der hierfür zu betreibende Aufwand ist sicherlich höher als eben beschrieben, das Ergebnis entschädigt aber meines Erachtens. Außerdem können Sie die Tabelle so gestalten, dass sie leicht wiederverwertbar ist. Dadurch lässt sich der Erstellungsaufwand drastisch reduzieren.

Im folgenden Bild sehen Sie die Tabelle mit eingeblendeten Gitternetzlinien und Zeilen-/Spaltenköpfen, sodass Sie diese leicht nachbauen können.

Beschaffen oder erstellen Sie passende Bilder. Im Beispiel habe ich in einem Grafikprogramm erzeugte eigene Bilder verwendet.

Vergrößern Sie drei Zellen über die Zeilenhöhe bzw. Spaltenbreite so, dass die Bilder ohne Probleme optisch in die Zelle passen (C3:E3). Fügen Sie die Bilder per Einfügen - Grafik ein und passen sie an die Zellgröße an.

In den darüberliegenden Zellen (C2:E2) schreiben Sie die Begriffe grün, gelb und rot (oder einen beliebigen anderen Text).

Markieren Sie die Zellen C2:E3 und klicken im Register Formeln auf Aus Auswahl erstellen (oder [Strg]+[Umschalt]+[F3]). Wählen Sie im sich öffnenden Dialog die Option Oberster Zeile. Mit dieser Aktion haben Sie den Zellen, über denen die Bilder liegen, jeweils den Namen vergeben, der in der Zelle oberhalb des Bildes steht. In obigem Beispiel erhielt die Zelle C3 den Namen grün, D3 den Namen gelb und E3 den Namen rot.

In den Zellen E4:E6 sind die Grenzwerte angegeben, die letztlich der Zuordnung der Farben zu den Zahlen dienen.

Die Zellen D9:G9 beinhalten Formeln, welche die Werte in D16:G16 in Farben umsetzen. Die Formel für Zelle D9 lautet:

=WENN(D$16>=$E$4;$G$4;WENN(D$16>=$E$5;$G$5;$G$6))

Kopieren Sie die Formel nach rechts bis G9.
Markieren Sie die Zellen D8:G9 und erzeugen wie zuvor (Formeln auf Aus Auswahl erstellen) Namen aus der obersten Zeile.
Rufen Sie im Register Formeln den Namensmanager auf und erzeugen den folgenden Namen:

Name: ampelwahl1 - Bezieht sich auf: =INDIREKT(wahl1)

Wiederholen Sie dies für die Namen ampelwahl2 bis ampelwahl4 (bezieht sich auf wahl2 bis wahl4)
Durch diese Aktion zeigt nun zum Beispiel ampelwahl1 durch die Indirekt-Funktion auf den Wert aus wahl1 (hier gelb) und dadurch letztlich auf Zelle C3.
Diesen Umstand machen wir uns für die letzte Aktion zu Nutze.

Das folgende Bild zeigt alle zuvor definierten Namen:

Als Letztes müssen wir das jeweilige Ampelbild für grün, gelb oder rot im unteren Teil der Tabelle dynamisch anzeigen. Dabei hilft uns ein Werkzeug, das es bereits seit vielen Excel-Versionen gibt: die Kamera. Sie finden diese jedoch in keinem Register (mehr).

Über Datei - Optionen fügen Sie den Befehl Kamera der Symbolleiste für den Schnellzugriff hinzu. Sie finden diesen Eintrag in der Liste Alle Befehle.
Markieren Sie Zelle C3 (nicht das darüberbefindliche Bild!)
Klicken Sie auf das Kamerasymbol und ziehen mit dem Mauszeiger an beliebiger Stelle einen Rahmen auf. Nach dem Loslassen erscheint die grüne Ampel in diesem neuen Objekt. Ändern Sie die Formel ab in

=ampelwahl1

Die Kamera macht gewissermaßen ein Bild von einem Zellbereich und zeigt diesen in einem eigenen Grafikobjekt an. Alle Inhalte der Zelle werden angezeigt, auch darüber befindliche Objekte wie Bilder. Durch die Formel wird dieses Bild jeweils dynamisch verändert.
Wiederholen Sie den Vorgang für die anderen drei Ampeln (ampelwahl2 bis ampelwahl4).
Passen Sie die Ampelbilder in der Größe an und schieben sie anschließend an die passende Stelle auf der Tabelle.

Sie haben in diesem Workshop gesehen, dass Ampeln nicht immer schmucklos und trist aussehen müssen. Statt Ampeln sind selbstredend auch andere Symbolbilder denkbar wie zum Beispiel nach oben, rechts oder unten zeigende Daumen, Pfeile oder ähnliches. Sorgen Sie auf jeden Fall dafür, dass erkennbar ist, wann welches Symbol zum Einsatz kommt. Im Beispiel erfolgt dies durch die Angaben in den Zellen C17:C19.

Zurück zur Übersicht