Frage von Pipimipi, 90

Frage zu Excel 2010: Ist eine WENN-Funktion in der bedingten Formatierung möglich?

Ich habe einen Jahresplan, die Tage und Monate verlaufen horizontal. Die Wochenenden habe ich mit der bedingten Formatierung farblich hervorgehoben -> =WOCHENTAG(D$5;2)>=6

Soweit funktioniert alles. Allerdings möchte ich parallel mehrere Zeitleisten unten im Plan farblich definieren und täglich anpassen. Auch das funktioniert, nur an den Wochenenden nicht, da ja die bedingte Formatierung Vorrang hat.

Nun zur Frage: Ist die bedingte Formatierung so gütig, dass man ihr sagen kann, nur dann ein zu greifen, wenn sonst keine Hintergrundfarbe definiert ist?

Falls nicht, gibt es eine Alternative? Schraffierte Wochenenden und bunte Zeitleisten funktionieren leider auch nicht. Geht das mit VBA?

Expertenantwort
von Ninombre, Community-Experte für Excel, 47

Hintergrundfarben kann man so ohneweiteres nicht abfragen, v.a. ist eine bedingte Formatierung gar keine "echte" Hintergrundfarbe, daher bringt auch VBA nichts. Außer man würde mit VBA auch die Wochenenden markieren, das ist aber etwas zu viel Aufwand.

Alternative 1:

"Mal" doch die Zeitleisten auch über bedingte Formatierungen. Wenn Du das Datum schon in einer Zeile hinterlegt hast, brauchst Du nur eine Spalte mit Beginn und eine mit Ende der jeweiligen Zeitleisten.

Dazu eine bedingte Formatierung (Annahme: B ist Beginn und C Ende)

=(UND($B6<=D$5;$C6>=D$5))

Wenn Du diese Regel mit den Pfeilen auf dem "Manager zur Verwaltung von bedingten Formatierungen" vor die Wochenend-Regel schiebst, greift zuerst die Zeitleiste.

Alternative 2:

Du kannst die Zellen der Zeitleiste auch mit X o.ä. markieren und eine bedingte Formatierung erstellen, die X farblich markiert (Schriftfarbe = Zellfärbung, so dass man das X nicht sieht). =D6="X"

Das X kann man dann bei der Wochened-Formel berücksichtigen.

=und(D6="";WOCHENTAG(D$5;2)>=6)

Kommentar von BPunktausH ,

Der Ansatz ist auch gut. Sollte funktionieren.

Kommentar von Ninombre ,

Ich hab nach dem Abschicken noch eine zweite Variante eingefügt, die Zeit war aber schneller zu Ende, als ich die Antwort sinnvoll anpassen konnte.

Alternative 1 und 2 laufen beide darauf hinaus, dass man die Zeitleisten über bedingte Formatierung erstellt: Entweder über Beginn- und Endedatum oder durch einen Wert in den Zellen, die markiert werden sollen. 1 ist nach meinem Verständnis sinnvoll, wenn Du die Datumsangaben direkt sichtbar brauchst, d.h. man nicht am Zeitstrahl den Beginn bzw. das Ende ablesen soll.
Bei 2 kannst Du sehr einfach verschiedene Farben einbauen, in dem Du je Buchstaben unterschiedliche Regeln anlegst: also X grün, U blau etc.(Das geht mit einer weiteren Spalte auch bei Alternative 1, noch einfach halt bei der Variante 2)

Expertenantwort
von Iamiam, Community-Experte für Excel, 39

das ginge zwar, aber ich würde als Grundlage ein Muster (zB Grau50 oder Karo) in gewünschter Farbe machen und die Bedingte Formatierung als Hintergrundfarbe drüber (bzw drunter) legen. Gibt zwar Mischfarben, kann aber trotzdem sehr kontrastreich gemacht werden. (Komplementärfarben ergeben grau)

Auch die Schriftfarbe bzw Fett/kursiv kannst Du bedingt festlegen.

Du darfst Dich bei der Bedingten nur nicht auf ein anderes Blatt beziehen.


Kommentar von Iamiam ,

übrigens: eindeutiger als die Gebietsschemaabhängige Fkt Wochentag ist die Fkt Rest("Datum";7). Da ist der Sa (zufällig) 0, der So 1, das Wochenende also <2, der Freitag 6.

Das gilt -mit einer einzigen Formel- nicht nur bei uns, sondern auch für Amerika und das analog rechnende sonstige Ausland.

Kommentar von Pipimipi ,

Die Version habe ich jetzt nicht ganz verstanden?

Kommentar von Iamiam ,

meinst Du die Fkt =Rest()?

xl beginnt am 1.1.1900 mit Tag 1 zu zählen, exakt fortlaufend (na ja, abgesehen von einem vermutlich ererbten Fehler: den 29.2.1900 gabs nicht, danach stimmts aber alles)

Da eine Woche konstant 7 Tage hat, Liefert die Teilung der Datumszahl durch 7 immer einen Rest, der dem Wochentag entspricht:

  1. Jeder Sa liefert =Rest("Datum";7)=0
  2. Jeder So liefert =Rest("Datum";7)=1
  3. Jeder Mo liefert =Rest("Datum";7)=2
  4. Jeder Di liefert =Rest("Datum";7)=3
  5. Jeder Mi liefert =Rest("Datum";7)=4
  6. Jeder Do liefert =Rest("Datum";7)=5
  7. Jeder Fr liefert =Rest("Datum";7)=6
  8. der folgende Sa dann wieder =0

so ist dann der Rest von Sa&So 0 und 1, also <2 (=2,000000 wäre schon Mo 0:00:00)

Diese Zählung ist unabhängig von der Gebietsdefinition: in Amerika ist der 1.Tag der Woche der So (=Wochentag("Datum";1)  in Europa seit einigen Jahrzehnten der 7.Tag, der erste ist der Mo. (leider!), aber der Rest, zB 0=Sa, 1=So ist für alle gleich.

Oder bezog sich Deine Frage auf den ersten Teil? dann:

Beim bedingten Zellformat sind Muster und etliche Rahmentypen, ich glaube auch die Zahlenformate nicht bedingt festzulegen. Du kannst also ein Muster samt Musterfarbe festlegen und darunter den Hintergrund bedingt formatieren. Probiers doch einfach aus, erst mal ohne Bedingung und dann mit!

Muster kriegst du mit Strg+1, Karte "ausfüllen"

Antwort
von BPunktausH, 46

Ist jetzt schwer zu erklären aber ich denke du musst die bedingte Formatierung in eine WENN Formel packen. Sie soll also nur dann Formatieren wenn nichts anderes vorhanden ist. Allerdings müsste ich jetzt erst ausprobieren ob das mit 0 für "nichts" funktioniert.

Vielleicht hilft dir der Ansatz ja weiter.

Kommentar von Pipimipi ,

Daran habe ich auch schon gedacht, ich weiß nur nicht wie man zusätzlich noch eine WENN-Funktion in die Formatierung bekommt oder wie sie überhaupt heißen könnte, mit "" oder "0", einfach mit Semikolon trennen? kp

Kommentar von BPunktausH ,

Versuchs mal ungefähr so:

=WENN(Deine Zelle=""; =WOCHENTAG(D$5;2)>=6; SONST andere Hintergrundfarbe)

Wie gesagt das "leer" ist mir grade noch ein Rätsel. Musst du mal ausprobieren

Antwort
von Pipimipi, 24

Danke, einige gute Ansätze dabei. Leider ist mir wichtig, dass die Zeitleisten keine bed. Formatierungen sind, da zu viele und von mehreren Teilnehmern eingepflegt werden.

Der Ansatz mit den Buchstaben bringt mich aber auf eine andere simple Idee. Kann ich bedingt formatieren, dass die Wochenenden keine Farbe oder Schraffur haben sondern mit XX oder ## befüllt werden?

Also gleiche Formatierung nur soll er Text in die Zellen schreiben. Dann hätte ich eine Markierung der Wochenenden und könnte trotzdem meine Zeitleisten farbig einpflegen.

Kommentar von Ninombre ,

Werte einfügen geht leider nicht über die bedingte Formatierung. Die Variante 2 kannst Du auch allgemeiner handhaben, dass man einfach irgendwas in die Zelle schreibt und das dann via bed. Formatierung färbt (=D6<>"")

Ansonsten die voll manuelle Variante, d.h. auch die Wochenenden von Hand markieren:
Hier die Idee von Iamiam aufgreifen und keine Farbe verwenden, sondern Musterformat (bei Zellen formatieren: Die Schraffur). Da bleibt das Wochende auch sichtbar, wenn eine Zeitleiste durchgeht.  Deine Anwender müssen nur daran denken, beim Löschen nur die Farbe auf weiß zu setzen (dann bleibt das Wochende schraffiert/kariert) und nicht Formatübertragen von einer weißen Zelle.

Kommentar von Pipimipi ,

Na klar das ist die Lösung. Ich hatte die von Hand markierten Wochenenden durch bedingte Formatierung ersetzt, da die Formatierung durch Verschiebungen und löschen durcheinander gerieten.

Ich werde die Spalten nun erneut manuell bearbeiten, jedoch mit der Schraffur. Dann kann nichts mehr schief gehen, weil keiner damit arbeitet und arbeiten soll.

Keine passende Antwort gefunden?

Fragen Sie die Community

Weitere Fragen mit Antworten