Office: Zellen farbig machen

Hallo, kann mir jemand sagen wo mein Problem ist? Problem 1: :-( Ich habe über eine Bedinge Formatierung angegeben dass ale Wochenendtage, also "Sa" und "So" gelb markiert werden. es sollen aber die drei Zellen darunter auch gelb werden. Prolem 2: :-( In dem Blatt Vorlage habe ich jedes Datum, das ein Feiertag ist eingetragen. Über die Namensdefinition habe ich sie als Feiertage gekenzeichnet. Leider zeigt es mir die Feiertage nicht an und wie unter Problem 1 sollen auch hier die drei Zellen darunter markeirt werden. Hier jedoch in Cyan. Ich hoffe ihr könnt mir helfen. :-)

 

Hallo,

ich habe Dir Deine Excel-Datei entsprechend angepasst, sodass jetzt die bedingte Formatierung der Samstage/Sonntage/Feiertage funktioniert. Fällt ein Feiertag auf einen Samstag/Sonntag, wird die Feiertagsformatierung angezeigt. Sollte dies nicht gewünscht sein, müsste die Reihenfolge der Regeln umgekehrt werden.

 

Super, Danke! Kannst du mir auch sagen wo mein Fehler war?

 

Hallo,
das Formatierungsverfahren heißt bekanntlich "bedingte Formatierung", weil die Formatierung nicht fix ist, sondern

• entweder von den Werten der betreffenden Zelle, die formatiert werden soll, abhängt
• oder aber von den Werten irendwelcher anderer Zellen (Kriteriumszellen).

In diesem Fall trifft hauptsächlich die 2. Möglichkeit zu, denn die Formatierung hängt zu 75% von den Werten anderer Zellen (12 Wochentagszeilen bzw. 12 Tagesdatumszeilen) ab. Liegt das Kriterium für die Formatierung in einer anderen Zelle, kommt nur mehr die Möglichkeit infrage, dass das Kriterium in einem logischen Formelausdruck besteht.

Vorgangsweise:

1. Man sieht nach, welche Zellen müssen ein bedingtes Format erhalten, und ferner, wo sind die Zellen, die die Wertekriterien enthalten für diese Zellformatierungen. Betrachtet man bei Dir den Januar-Zellbereich (B2:AF5), der bedingt formatiert werden soll, so liegen die Zellen mit den Kriteriumswerten für Sa/So-Formatierung im Bereich (B2:AF2) bzw. für Feiertagsformatierung im Bereich (B3:AF3). Dagegen gelten für den Februar (bis Dezember) davon völlig getrennte (Bedingungs)bereiche.
2. Daher muss man zuerst diesen 1. Bereich, dessen Zellen alle anhand der gleichen Kriteriumszellen bedingt formatiert werden sollen, markieren (Bereich B2:AF5).
3. Dann ruft man das Dialogfenster für die bedingte Formatierung auf (Menüband > Tab. "Start" > Gruppe "Formatvorlagen" > Button "Bedingte Formatierung" > Befehl "Regeln verwalten"); sodann den Button "Neue Regel".
4. Im Fenster "Neue Formatierungsregel" wählt man den Regeltyp aus: Da es nur eine Regel gibt, in der man eine Formel als Kriterium angeben kann, nimmt man die Regel "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
5. Dadurch erscheint die Eingabezeile für die Kriteriumsformel. Ich möchte zuerst einmal die bedingte Formatierung für Sa/So-Spalten im Januar-Bereich (B2:AF5) erstellen. Die Kriteriumszellen befinden sich dazu in der Zeile B2:AF2: Überall, wo dort "Sa" bzw. "So" steht, soll in den 4 Zellen der betreffenden Spalte(n) die Gelbformatierung eintreten.
6. Die dafür passende Formel lautet: =ODER((B$2="Sa");(B$2="So")) Ich habe dafür die ODER(...)-Funktion verwendet: Ist entweder B2="Sa" oder B2="So", dann soll die Gelbformatierung eintreten. Die große Frage ist dann aber, welche Zellen von der Gelbformatierung betroffen sind, falls die blaue Formel WAHR ist. Da bekommen die beiden $-Zeichen in der Formel eine ganz wichtige Bedeutung: Sie stehen in diesem Fall vor der "2", nicht jedoch vor dem "B", was soviel bedeuten soll, dass der "2" für die Zeile fix ist, während das "B" für die Spalte veränderlich sein darf. Will ich die 4 Zellen der 1. Spalte des Januar-Bereichs (B2:B5) mit vorgenannter Formel überprüfen, ob sie gelb werden sollen, so befindet sich für alle 4 Zellen die Kriteriumszelle in B2 (da die Zeile 2 fix ist wegen des $-Zeichens). Will ich jetzt die 4 Zellen der 2. Spalte des Januar-Bereichs (C2:C5) mit vorgenannter Formel überprüfen, so befindet sich für alle 4 Zellen die Kriteriumszelle in C2 (da die Zeile 2 fix ist, während die Spalte B - sprich C - variabel ist). Dieses Verfahren setzt sich für alle 31 Januar-Spalten fort. Es ist ganz wichtig, dass man bei der bedingten Formatierung diesen Mechanismus mit der fixen und variablen Zelladressierung im Formelkriterium versteht.
7. Der Rest ist relativ einfach: Button "Formatieren..." anklicken und das gewüschte Format auswählen (in diesem Fall: gelbe Hintergrundfarbe).
8. Dagegen hast Du sämtlichen Zeilen ($1:$1048576) zur bedingten Formatierung für dasselbe Kriterium ausgewählt, wobei Du als Kriterium den Zellwert jeder einzelnen Zelle genommen hast, um ihn mit dem Wert "Sa" bzw. "So" zu vergleichen. Das würde dann funktionieren, wenn einer dieser beiden Werte in jeder gelb zu formatierenden Zelle enthalten sein müsste. Tatsächlich kommen diese beiden Werte aber nur in der 1. Zeile des Monatsbereiches vor, nicht jedoch auch in den anderen 3 Zeilen, obwohl auch diese gelb einzufärben sind, wenn in der 1. Zeile "Sa"/"So" steht.
9. Bei der Feiertagseinfärbung hast Du wieder auf sämtliche Zeilen ($1:$1048576) dasselbe Kriterium angewendet. Diesmal zwar korrekterweise bereits das Formelkriterium, aber leider ist die Formel: =WENNFEHLER(SVERWEIS($1:$1048576;Feiertage;2;0);"")<>"" nicht korrekt. Diese muss lauten: =WENNFEHLER(SVERWEIS(B$3;Feiertage;1;0);0)<>0 Dafür gibt es mehrere Gründe: In der SVERWEIS-Funktion bezeichnet B$3 die Kriteriumszelle. Da sich diese im Januar-Bereich stets in Zeile 3 befindet, bekommt die "3" das $-Zeichen. Der benannte Bereich "Feiertage" bezieht sich auf "=Vorgabe!E3:E31" und ist damit ein 1-spaltiger Bereich. Da dies Excel erkennt, darf man als Spaltenoffset in der SVERWEIS-Funktion nicht "2" verwenden, sondern kann in diesem Fall wieder nur maximal "1" verwenden. In dieser 1. Spalte von "Feiertage" stehen entweder Datumswerte (=numerische Werte) oder Leerzellen. Das ist der Grund, warum die WENNFEHLER-Funktion auf <>0 getestet werden muss und nicht auf <>"" und in der WENNFEHLER-Funktion muss als 2. Alternativwert auch wieder "0" stehen und nicht "" (wegen der numerischen Datumswerte), da die 0 die Stelle der Leerzellen einnimmt.

 

Super, danke, das war mal verständlich erklärt!

 

Dafür gibt's den Award für die ausführlichste Erklärung ;-)