Excel 2010: Mit einem Ampelsymbol das Wochenende kennzeichnen mittels Formel in der Bedingten Formatierung

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 Kriterium keine Zahlen verfügbar sind. Wie lassen sich beispielsweise alle Datumsangaben, die an einem Wochenende liegen, mit einem Ampelsymbol oder einem roten Kreis versehen? 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 19. Mai 2012).

  • 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 Zahlen in Spalte B verfügbar sind.

    Schritt 2: Eine Formel in Spalte B einfügen

    Dazu führe ich in Spalte B eine Berechnung durch, die mir die erforderlichen Werte zur Kennzeichnung des Wochenendes liefert. Ich verwende die Funktion WOCHENTAG, die als Ergebnis eine Zahl zwischen 1 und 7 liefert.

    Woher weiß ich, 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 – den Eintrag Typ 2. Damit wird der Montag als Wochenbeginn festgelegt und erhält die Zahl 1, der Sonntag als letzter Tag der Woche bekommt die Zahl 7.

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

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

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

    Schritt 3: Den Symbolsatz anpassen

    Da nun Zahlen zwischen 1 und 7 in Spalte B  stehen, kann ich mit diesen Werten 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

    Noch ein Beispiel: Wenn der Freitag auf den 13. fällt

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

    Freitag, den 13. kennzeichnen

    Für die Lösung werden in Spalte B die Funktionen WOCHENTAG und TAG mit einem logischen UND verknüpft: =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 einer 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

  • Dieser Beitrag wurde unter Excel abgelegt und mit , , , , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

    Eine Antwort auf Excel 2010: Mit einem Ampelsymbol das Wochenende kennzeichnen mittels Formel in der Bedingten Formatierung

    1. Tim sagt:

      Hallo,

      sehr gut erklärt, wie die Bedingte Formatierung funktioniert. Danke.
      Gruß Tim

    Schreibe einen Kommentar

    Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.