Office-Blog

Excel 2010: Die Einsatzmöglichkeiten für Ampeln & Co. mit Hilfe von Formeln deutlich erweitern

24.10.2011   Hildegard Hügemann

Symbolsätze mit Ampeln, bunten Fähnchen oder Pfeilen können Tabellen informativer und übersichtlicher machen. Allerdings stößt die Verwendung der Symbole an Grenzen, wenn als Zuweisungskriterium keine Zahlen verfügbar sind.

Wie lassen sich beispielsweise alle Datumsangaben mit einem roten Kreis versehen, die an einem Wochenende liegen? Das folgende Bild zeigt die Lösung. Basis dafür ist eine Formel, die in Spalte B den Wochentag berechnet und Werte zwischen 1 und 7 liefert.

Die fertige Lösung: Roter Kreis am Wochenende

Schritt 1: Den gewünschten Symbolsatz zuweisen

Das Zuweisen eines bestimmten Symbolsatzes ist kein Problem (nachzulesen auch im Beitrag vom 3.10.2011).

  • Zunächst markiere ich den betreffenden Bereich – hier also B2 bis B8.
  • Auf der Registerkarte Start klicke ich auf Bedingte Formatierung und dann auf Neue Regel.
  • Im unten gezeigten Dialogfeld werden Symbolsätze nur zur Verfügung gestellt, wenn ich oben den Regeltyp Alle Zellen basierend auf ihren Werten formatieren (1) wähle.
  • Im Listenfeld unter Formatstil wähle ich dann den Eintrag Symbolsätze (2) und entscheide mich anschließend darunter für die passende Symbolart (3).

Im Dialogfeld Neue Formatierungsregel einen Symbolsatz zuweisen

Wenn ich nun in dem Dialogfeld statt der 67 unter Wert eine Formel eingeben möchte, macht mir Excel klar, dass dies nicht geht. Was beim Zuweisen einer Hintergrundfarbe kein Problem ist, erweist sich bei bei Symbolen als unmöglich.

Für das Zuweisen von Symbolen ist Zahlenmaterial erforderlich. Also muss ich einen Umweg gehen und dafür sorgen, dass in Spalte B Zahlen verfügbar sind.

Schritt 2: Eine Formel in Spalte B einfügen

In Spalte B führe ich eine Berechnung durch, die mir Werte zur Kennzeichnung des Wochenendes liefert. Hierzu verwende ich die Funktion WOCHENTAG, die eine Zahl zwischen 1 und 7 ausgibt.

Woher weiß ich nun, welcher Tag hinter der ‚1‘ oder der ‚7‘ steckt. Ein Blick auf die Syntax dieser Funktion bringt schnell Klarheit:

Wochentag

Ich wähle – nach mitteleuropäischer Logik – Typ 2. Damit wird der Montag als Wochenbeginn festgelegt – dargestellt durch die Zahl 1, der Sonntag als letzter Tag der Woche bekommt die Zahl 7.

Typ 2 erleichtert zudem die Abfrage nach Wochenende (>=6) oder nicht Wochenende (<6).

Übrigens: Mit Excel 2010 sind 7 neue Typen hinzugekommen (11-17), welche die ganze Bandbreite des Wochenbeginns in Folge ermöglichen.

Mit diesem Wissen ausgerüstet, füge ich nun die Formel wie folgt ein:

  • Ich markiere wieder den Bereich B2:B8.
  • Nun gebe ich die Formel =WOCHENTAG(A2;2) ein und schließe mit Strg + Enter ab.

Schritt 3: Den Symbolsatz anpassen

Da nun in Spalte B Zahlen zwischen 1 und 7 stehen, kann ich damit die Regel für den Symbolsatz “füttern”.

  • Ich lasse B2:B8 markiert und rufe über Bedingte Formatierung den Befehl Regeln verwalten auf.
  • Per Doppelklick auf die bestehende Regel gelange ich in das unten gezeigte Dialogfeld.

Im Dialogfeld Neue Formatierungsregel den Symbolsatz anpassen

  • Dort setze ich ein Häkchen bei Nur Symbol anzeigen (4). Die Zahlen in Spalte B werden damit ausgeblendet.
  • Nun ändere ich den Typ von Prozent auf Zahl (5).
  • Unter Wert gebe ich die Zahl 6 ein (6), denn die Wochentage, die größer oder gleich 6 sind, fallen auf den Samstag oder Sonntag – sind also Wochenende.
  • Danach ändere ich das Symbol von grüner auf roter Kreis (7).
  • Abschließend – und das geht erst ab Excel 2010 so einfach – schalte ich die Symbole für die anderen Zahlen  aus, indem ich zweimal Kein Zellsymbol wähle (8).

Wie sieht es mit der Kompatibilität aus?

Öffne ich meine .xlsx-Datei in Excel 2007, erhalte ich das folgende Bild:

Kompatibilitätsproblem mit Excel 2007 bei Typ 2

Die bedingte Formatierung mittels Symbol ist nicht mehr vorhanden. Stattdessen sind die Ergebnisse der Funktion WOCHENTAG in Spalte B nur noch als Zahlen sichtbar.

Verwende ich bei meiner Wochentagsberechnung statt Typ 2 den neuen Typ 11, der die Woche ebenfalls am Montag beginnen lässt, so ist beim Öffnen der .xlsx-Datei in Excel 2007 die Welt zunächst noch in Ordnung (abgesehen von den fehlenden Symbolen). Doch nach einer Neuberechnung taucht dann eine Fehlermeldung auf, die auf den nicht bekannten Typ 11 hinweist:

Kompatibilitätsproblem mit Excel 2007 bei Typ 11

 

Ein zweites Beispiel: Wenn der Freitag auf den 13. fällt

Freitag, der 13. ist für manche ein besonderer Tag. Daher soll er mit einem weißen Kreuz in einem roten Kreis gekennzeichnet werden.

Freitag, den 13. kennzeichnen

Hierzu verknüpfe ich in Spalte B die Funktionen WOCHENTAG und TAG mit einem logischen UND

= UND(WOCHENTAG(A2;2)=5;TAG(A2)=13)

Liefert die Funktion WOCHENTAG eine 5 zurück und gleichzeitig die Funktion TAG die 13, so setze ich den Wert in der Zelle mit Hilfe der WENN-Funktion auf 0, sonst auf 1.

  • Damit ergibt sich für Zelle B2 die folgende Formel, die ich dann nach unten kopiere:
    =WENN(UND(WOCHENTAG(A2;2)=5;TAG(A2)=13);0;1)
  • Anschließend wende ich – wie im folgenden Dialogfeld zu sehen – die Bedingte Formatierung mit dem gewünschten Symbol auf Spalte B an.

Die Einstellungen, um das Symbol Roter Kreis mit Kreuz zuzuweisen

Über den Autor

Hildegard Hügemann

- Dipl.-Informatikerin
- Zertifizierte Office-Trainerin mit den Schwerpunkten Excel, Access, Word
- Entwicklerin von Excel-Tools zur Projekt-, Produktions- und Verkaufssteuerung
- Coach bei der Datenaufbereitung mit Access, Power Query und Power Pivot
- Entwicklerin von Access-Datenbanken für Mittelständler und Behörden
- Bloggerin zu Excel und Access auf www.huegemann-informatik.de
- Autorin für Microsoft Press, dpunkt.verlag und das »Projekt Magazin«

3 Comments so far

RicoPosted on  2:24 pm - Apr 27, 2016

Hallo,

leider finde ich keine passende Lösung und hoffe hier auf Hilfe.
Ich nutze die Excel-Vorlage „Aufgabenlisten“ und möchte nun eine kombinierte bedingte Formatierung erzeugen.

Es soll hier eine Ampelfunktion kombiniert werden. Das erste Kriterium das das Datum entscheidendend ist, funktioniert. Jetzt soll noch ein Bereich ab Zelle B5, B6…etc. wo Erledigt? Ja oder Nein ausgewählt werden kann eine Auswirkung auf die Ampel nehmen >> d.h. wenn das Kriterium „Ja“ ausgewählt wird, dann soll die Ampel grün anzeigen > bei Nein entsprechend „Rot“ > wobei das Datum in der ersten bedingten Formatierungsregel Vorrang hat, puh ich hoffe das war verständlich
BG Rico

Eva EutebachPosted on  2:38 pm - Okt 6, 2014

Hallo Frau Hügemann,

habe ein großes Problem mit meiner Wenn-Formel mit der ich die prozentualle Abweichung zwischen den Plan-Zahlen und Ist-Kosten ermitteln soll:

Beispiel:

Plan Ist-Kosten Abweichung
Euro Euro Euro %

20.000 0,00 -20.000,00 -100,00
100.000 53.120,00 -46.880,00 -46,88
360.000 133.107,50 -226.892,50 -63,03
0 15.000,00 15.000,00 100,00
110.000 183.427,80 73.427,80 66,75

a) Die negative Euro-Abweichung (mit Minus vorne) wird in rot ausgegeben. Deswegen soll auch die negative prozentualle Abweichung (mit Minus vorne) auch in rot ausgegeben werden.

b) wenn Ist-Kosten Null sind muss 100 % stehen statt 0,00 %

c) wenn Plan-Kosten Null sind muss ebenfalls 100 % stehen statt 0,00 %

Würde mich sehr über Ihr Lösungsvorschlag freuen :)

Vielen vielen lieben Dank :))

Eva

TimPosted on  4:07 pm - Feb 14, 2012

Danke, Danke, Danke – genau das hatte ich gesucht. Für die Planung benutze ich die Haken, so kann man gut sehen, welche der angelegten Aufgaben ich bereits erledigt habe.