Bedingte Formatierung: zwischen 2 "Datums" einfärben.

[Pu der Baer]

Hallo,

ich will einen Zeitplan erstellen in dem in einer Zeitleiste farbige Zellen erscheinen und zwar abhängig von zwei Tagen (Tagesdaten bzw. Plural von Tagesdatum) , sprich Startdatum und Enddatum.
Das soll/kann zur Visualisierung von Urlaubsplänen oder Projektplänen oder ähnlichem dienen.

vereinfachter Aufbau der Tabelle:

in Spalte A (A2 bis A5) befindet sich das Startdatum.
in Spalte B (B2 bis B5) befindet sich das Schlußdatum.
In Zeile 1 (C1 bis B365) sind die Tage eines Jahres durchgängig aufgelistet (1.1.2011 bis 31.12.2011) = "Spaltenbeschriftung")

Ziel:
Die Zellen in Zeile 2 bis 5 (C2...NC2 bis C5...NC5 - Spalte NC weil das dann 365 Tage nebeneinander ergibt-) , sind (zunächst?) ohne Inhalt, sollen sich aber einfärben, sobald die Spaltenbeschriftung (=Datum der Zeitleiste) zwischen (einschließlich) dem Startdatum in Spalte A UND dem Schlußdatum in Spalte B liegt.

Beispiel:
A2=4.1.2011
B2=6.1.2011

dann sollen sich im Bereich der Zeile 2 die Zellen einfärben, die als Spaltenbeschriftung die Daten vom 4.1.2011, 5.1.2011 und 6.1.2011 tragen.

Klingt komplizierter als es ist, ich hoffe es ist trotzdem verständlich.
Als Ergebnis erscheint eine übersichtliche farbige Kalendermarkierung, die den Bereich von Startdatum bis Schlußdatum zeigt.

über die bedingte Formatierung ist es mir bislang gelungen, jeweils anhand eines Datums, z.B. des Startdatums die Zelle farbig zu markieren:
und zwar für Zelle C2 (das die erste der Zeitachse, also 1.1.2011) mit folgender Formel:
Formel ist: $Tabelle1.$A2=$Tabelle1.C$1

Wie muss die Formel lauten, damit mein Ziel erreicht wird.
Ich sehe grob zwei erste Ansätze, ohne diese Formeln gut genug zu kennen - geschweige denn in Zusammenhang mit der Anwendung bei bedingten Formatierungen:

a) Zwei Bedingungen müssen gelten und zwar: Spaltenüberschrift (Kalenderdatum) ist größer oder gleich Startdatum UND Spaltenüberschrift ist kleiner oder gleich dem Schlußdatum, damit sich die Zelle verfärbt. Eine Art UND-Verknüpfung von größer-gleich und kleiner-gleich-Prüfung, falls es so etwas gibt.

b) es wird geprüft, ob es wahr [oder falsch] ist, daß die jeweilige Spaltenüberschrift (Kalenderdatum) die entsprechenden Merkmale (Abhängigkeit sinngemäß wie bei a). Also eine Art WAHRHEITsprüfung. Wenn Aussage WAHR, dann bitte Zelle einfärben.

Für weiterführende Gedanken und Verständnis-Nachfragen (oder Hinweise auf nichtgefundenen Lösungen im Netz zu diesem Problem)
dankt schon jetzt

Pu der Baer
ein Bär von geringem Verstand

[Karolus]

Hallo
Siehe Anhang:

Ferien_bed_Format.ods

(12.6 KiB) 3411-mal heruntergeladen

Karo

[Pu der Baer]

Hallo Karo,

vielen Dank für dieses tolle Beispiel.
Ich hatte gerade mit anderen Dingen viel Streß, weshalb ich erst heute wieder dazu komme, micht mit dem Thema zu beschäftigen. Ich hoffe Du liest das trotzdem und kannst mir noch über die letzten Hürden helfen.


Ich begreife die Funktion der Tabelle bislang nur ansatzweise, weshalb es mir noch nicht gelingt, sie exakt nach meinen Wünschen anzupassen:

A)
Du hast ja im Bereich "ferien" eine Tabelle mit Feriendaten erstellt, die dann über alle Zeilen des Bereichs "Kalender" die entsprechenden Zeiträume einfärbt.

Ich will diese Prozedur jedoch für eine Projektplanung nutzen, kann auch für die Einsatz- oder Urlaubsplanung von verschiedenen Mitarbeitern etc. eingesetzt werden.

Dafür sollten nur die Kalenderbereiche in der jeweiligen Zeile gefärbt sein. Um bei Deinem Beispiel zu bleiben, sollen nur folgende Zellen farbig werden:

Zeile 3: F3 bis I3
Zeile 4: Q4 bis V4
Zeile 5: BC5 bis BN 5

Ich nehme an, daß dann SVerweis mit dem Bereich Ferien nicht ganz korrekt ist...

B) bislang unerwähnter Zusatztraum (eine Frage für die echten Calc-Kenner):
Die Farbe der Markierung ändert sich in Abhängigkeit von einem Namenskürzel oder einer Bezeichnung in derselben Zeile.
Beispiel: es wird eine Spalte mit Ferienbezeichnungen hinzugefügt. (und es gibt mehr als 3 verschiedene Ferienarten)
04.01.12 07.01.12 Winter
15.01.12 20.01.12 Ski
22.02.12 04.03.12 Fasching

Jetzt will ich (z.B. irgendwie in einer anderen Tabelle hinterlegt) dass sich die zeilenweisen Kalenderbereiche von der Ferienart einfärben, also

Winterferien werden blau, Skiferien weiß und Fasching wird rot-gelb gestreift (oder wenigstens einfarbig rot, wenn das einfacher ist).


Schon mal ganz riesigen herzlichen Dank für Deine Hilfe - oder die Hilfe aller anderen.

sagt Pu der Baer
Ein Bär von geringem Verstand

[Pu der Baer]

Ha, wenn ein Bär von geringem Verstand seine Bärenbauernschläue aktiviert, kommt er irgendwann auch etwas weiter.

Ich habe mal auf die Bereichskennzeichnung (des Bereichs) Kalender verzichtet und die bedingte Formatierung nur auf eine einzelne Zelle angewandt, und diese dann einfach als Format über den restlichen Kalender-Bereich "gezogen" bzw. kopiert.

Für die Zelle F3 lautet nun die Eingabe für die bedingte Formatierung im Beispiel:

Formel ist F$1<=SVERWEIS(F$1;$Tabelle1.$A3:$B3;2)

Damit bleibe ich beim SVerweis als Formel, beziehe diesen aber nur auf die jeweilige Zeile (in Zeile 3 also SVerweis für Zeile 3, in Zeile 4 SVerweis für Zeile 4 etc.)
Aber ist das nicht etwas mit Kanonen auf Spatzen geschossen? Gäbe es nicht noch etwas eleganteres?

Wer noch Ideen hat: Immer her damit.

Und zum Teil B) des Rätsels: Die unterschiedlichen Farben in Kombination von einer Bezeichnung, da habe ich noch gar keine Idee und freue mich über weiterführende Hinweise und Ideen.

es dankt und denkt etwas weiter

Pu der Baer
ein Bär von geringem Verstand

[Karolus]

Hallo

Zeile 3: F3 bis I3
Zeile 4: Q4 bis V4
Zeile 5: BC5 bis BN 5

Hier nochmal ein Beispiel mit 'zeilenversetzter' Einfärbung

Ferien_bed_Format.ods

(16.13 KiB) 1135-mal heruntergeladen

Karo

[Pu der Baer]

Hallo Karolus,

wieder ein paar Tage später - bin halt nur ab und zu dran, in Zukunft wohl doch etwas mehr dran sein.

Deine Variante der bedingten Formatierung lautet ja elegant:

(F$1<=SVERWEIS(F$1;ferien;2))*(VERGLEICH(F$1;anfang)=ZEILE())

Zum Verständnis der Formel:
a) Bewirken die alleräußeren Klammern und das Sternchen in der Mitte, daß beide Bedingungen erfüllt sein müssen, damit die gewünschte Formatierung ausgewählt wird?

b) wenn a) wahr ist, wie könnte ich das einsetzen, um Texte zu vergleichen um die Formatierung wie oben beschrieben zu ändern?
Sprich: Nehmen wir an, es wird eine neue Spalte C eingefügt, dort stehen in C3 die Worte: "Peter Ferien"
Wie kann ich es hinbekommen, dass sich die Farbe ändert, wenn nicht nur der Zeitraum stimmt, sondern auch das Wort "Oeter" in der Spalte C der jeweiligen Zeile aufgelistet ist?
Ich lade mal ein Beispiel rauf, hoffe das das klappt.


- SVerweis setzt ja wohl voraus, das die Daten in Spalte A ("Anfang") nach Größe sortiert sind? Kann es zu Schwierigkeiten kommen, wenn das nicht der Fall ist?.

Für Hinweise danke ich sehr.

Pu der Baer

Ferien_bed_Format-zellenbed-einfaerben-Typabhaengig.ods

Zielvorstellung der abhängigen Einfärbung als Beispieldatei

(15.23 KiB) 583-mal heruntergeladen

[Karolus]

Hallo

(F$1<=SVERWEIS(F$1;ferien;2))*(VERGLEICH(F$1;anfang)=ZEILE())

Zum Verständnis der Formel:
a) Bewirken die alleräußeren Klammern und das Sternchen in der Mitte, daß beide Bedingungen erfüllt sein müssen, damit die gewünschte Formatierung ausgewählt wird?

Das Sternchen ist eine Multiplikation, und bewirkt das gleiche wie ein logisches UND

b) wenn a) wahr ist, wie könnte ich das einsetzen, um Texte zu vergleichen um die Formatierung wie oben beschrieben zu ändern?
Sprich: Nehmen wir an, es wird eine neue Spalte C eingefügt, dort stehen in C3 die Worte: "Peter Ferien"
Wie kann ich es hinbekommen, dass sich die Farbe ändert, wenn nicht nur der Zeitraum stimmt, sondern auch das Wort "Oeter" in der Spalte C der jeweiligen Zeile aufgelistet ist?
Ich lade mal ein Beispiel rauf, hoffe das das klappt.


- SVerweis setzt ja wohl voraus, das die Daten in Spalte A ("Anfang") nach Größe sortiert sind? Kann es zu Schwierigkeiten kommen, wenn das nicht der Fall ist?.

Die hier genutzte SVERWEIS -variante ist zwingend auf eine aufsteigend sortierte Suchspalte angewiesen.

Allerdings hat sich deine Fragestellung völlig geändert, du musst nur jeweils in der gleichen Zeile vergleichen, und kannst die Einträge in Spalte C auch gleich als Namen für die zugehörigen Zellvorlagen hernehmen:

Formatierung_per_Zellformel.ods

(26.23 KiB) 708-mal heruntergeladen

Gruß Karo

[Pu der Baer]

Hallo Karo,

WoW,
Das ist ja eine Superformel und erzielt in der Tat das gewünschte Ergebnis.

Nach meiner Recherche scheint die Formel nur anhand eines Makros zu funktionieren, das erst bei einem neuen Tabellenblatt installiert oder integriert werden muss (Was ich dann hoffentlich irgendwie noch hinbekomme. Ich weiß bislang noch nicht wie das geht.)

Ein anderes Manko ist, das diese Formel nicht mehr funktioniert, wenn das Tabellenblatt mal als Excel gespeichert wird, damit die doofen M$-Anwender auch etwas damit anfangen können.

Nehmen wir an, ich würde mich erstmal auf 3 bedingte Formate (Farben) beschränken, und die Formel ausschließlich ausschließlich in der bedingten Formatierung hinterlegen.

Dann kann doch dort in der Bedingten Formatierung (für D2) irgendetwas in folgender Art stehen:
Formel ist ((D$1>=$A2)*(D$1<=$B2)*(C2="Mary Ferien"))

Im ersten Versuch, scheint das zu klappen. Gibt es noch eine elegantere Lösung, die Excel-Format-tauglich ist? (ausser vielleicht die Methode, ganze Zellbereiche entsprechend zu benennen.)

Hoffentlich nicht zu sehr mit diesen Fragen nervend

dankt schon jetzt glücklich mit einem riesigen Topf Winterwaldhonig

Pu der Baer
ein Bär von gar nicht mehr ganz so geringem Verstand.








vielen Dank
Pu der Baer

[Stephan]

Nehmen wir an, ich würde mich erstmal auf 3 bedingte Formate (Farben) beschränken

falls der Grund dafür ist das in OOo nur 3 bedingte Formate über den Dialog einstellbar sind, dann nimm mottco:
http://www.calc-info.de/makros.htm#mottco

entgegen der Aussage das irgendwo auch steht das das nicht für *xls funktioniert stimmt das nicht, denn auch dort funktionieren mit mottco bearbeitete DAteien, es ist in MS Excel nur nicht mehr möglich Veränderungen vorzunehmen weil dort beim Aufruf des Dialogs für bedingte Formatierungen MS Excel abstürtzt. Das gilt zumindest für ältere Excel-Versionen. Wie sich neuere , die 64 Bedingungen für bedingte formatierung erlauben, verhalten weiß ich nicht konkret.

Ich hänge spaßeshalber eine mit mottco bearbeitete xls mit 5 Bedingungen an (die nur zeigt das es geht, aber nicht konkret auf DEine Datei angepast ist)


Gruß
Stephan

[Pu der Baer]

Hallo Stephan,

A) vielen Dank für das neue Makro, ich probiere es mal auf meinem Heimrechner aus.

Das erste o.g. Makro von Karo funktioniert bei mir wirklich nur in .ods Version. Schon mit OO als .xls wird überall "makro" angezeigt.

Die andere Version habe ich noch nicht ausprobiert. Aus Sicherheitsgründen möchte ich hier auf dem Fremdcomputer keine Makros installieren, daher präferiere ich doch eine Makro-freie Version, selbst wenn das jetzt die Beschränkung auf 3 Farben bedeutet.

B) Sorry, das das jetzt so stückchenweise kommt: Wer richtig knobeln mag: Wie müsste die Formel aussehen, wenn nur Textteile aus der Zelle C ausgewertet werden müssten. Sprich: in C steht "Mary Ferien", aber die Zelle verfärbt sich aufgrund bedingter Formatierung, weil "Mary" drin steht. Also auch wenn "Hut von Mary" oder "Mary and Gordy" in der Zelle steht, wird die Farbe von Mary angenommen.

Es dankt
P.U. Baer

Ein Bär von geringem Verstand

[Stephan]

vielen Dank für das neue Makro

mein Beispieldokument enthält keinerlei Makros

Das erste o.g. Makro von Karo funktioniert bei mir wirklich nur in .ods Version. Schon mit OO als .xls wird überall "makro" angezeigt.

Na das klärt zumindestens warum Du davon sprachst das 'die Formel anscheinend nur aufgrund von makros zu funktionieren scheint',

Du unterliegt hier einem fundamentalen Irrtum, denn es gibt keinerlei Makros, sondern der ZUsammenhang ist Folgender:

Karo verwendet z.B. die Formel:

=WENN((H$1>=$A3)*(H$1<=$B3);T(VORLAGE($C3));T(VORLAGE("Standard")))

darin ist die Funktion namens VORLAGE() eine ganz normale Tabellenfunktion, wenn man jedoch die Datei (nachdedm man sie als *.xls gespeichert hat) in MS Excel öffnet so erkennt Excel die Funktion VORLAGE() nicht weil es diese in Excel nicht gibt (Calc beinhaltet insgesamt mehr Tabellenfunktionen als Excel) und so glaubt Excel das es sich um eine sog. benutzerdefinierte Funktion handelt (also eine "Function" in Basic) und zeigt das als Fehlermeldsung "#MAKRO?" an (also als "Sub" in Basic) weil Excel einfach keinen Basic-Code findet weil garkeiner da ist.

Es ist also gerade kein Makro vorhanden und Du interpretierst die Meldung "#MAKRO?" gerade falsch rum nämlich als ob eines da wäre, in Wirklichkeit vermisst Excel aber ein Makro und gibt deswegen die Meldung "#MAKRO?" aus und das auch irrtümlich denn Excel glaubt ja nur das VORLAGE() ein Makro wäre was es nie ist bzw. war und zum Zweiten irrtümmlich weil selbst wenn es Basic-Code geben würde wäre der im Konkreten eine Function und die Fehlermeldung müßte eigentlich lauten "#Function?", eine solche FEhlermeldung gibt es aber garnicht.

Calc zeigt hingegen diese irreführende Meldung nur weil es sich möglichst kompatibel zu Excel verhält.

daher präferiere ich doch eine Makro-freie Version

nochmals:
sowohl mein Vorschlag als auch er von Karo SIND makrofrei.

Du kannst das z.B. bei meiner Datei in Excel auch sehr leicht prüfen indem Du die Makroausführung blockierst (In Excel: Extras-Makro-Sicherheit... und auf "Sehr hoch" stellen) und du wirst sehen das die bedingte Formatierung in meiner DAtei immer noch funktioniert, was nicht so wäre wenn sie auf Makros beruhen würde.


Gruß
Stephan

[Pu der Baer]

Hallo Stephan,

danke für Deine ausführliche und gut erklärende Antwort. O.K. Ich bin nur ein Bär von geringem Verstand.

mein Beispieldokument enthält keinerlei Makros

Es sind also Formeln (die in .XLS nicht funktionieren) oder im Fall von Mottoc zumindest eine Extension , die aber von jedem installiert werden muss, der die Datei bearbeiten soll.

Beides ist schön, aber für meine aktuellen Zwecke nicht so richtig geeignet.

In Deiner Beispieldatei mit den bunten Feldern anhand der Zahlen 1-5 kann ich auch weiterhin nur die ersten 3 Bedingungen sehen und bei Bedarf bearbeiten, ich nehme an, dass der Rest nach Installation der Extension bearbeitbar wäre?

Wobei ich, wie gesagt, auch mit den 3 Bedingungen leben kann, wenn es dadurch kompatibel oder einfacher bleibt.


Was noch das Sahnehäubchen auf der Honigschnitte wäre, wäre die Einfärbung oder Markierung anhand eines Teils des Inhalts, wie weiter oben im Thread (hoffentlich verständlich??) beschrieben.

Nochmals vielen vielen Dank für die gute Hilfe hier.

Ich habe schon viel gelernt, was ich hoffentlich nicht wieder vergesse

Pu der Baer
ein Bär von geringem Verstand

P.S.: Ich fand den Hinweis auf mottco noch an anderer Stelle im Netz und da steht er unter der Überschrift "Makros", was vielleicht neben der von Dir beschriebenen #MAKRO Fehlermeldung meinen Irrtum erklärt.

[Stephan]

Ich bin nur ein Bär von geringem Verstand

Nee, wieso denn? Ich habe nur ein paar Fakten aufgezählt, das hat nur was mit Wissen zu tun nicht mit Verstand. Ich weiß darüber hinaus so Vieles andere nicht und ich bin auch sicher das du Dinge weißt die ich nicht verstehe.

Es sind also Formeln (die in .XLS nicht funktionieren) oder im Fall von Mottoc zumindest eine Extension , die aber von jedem installiert werden muss, der die Datei bearbeiten soll.

Beides ist schön, aber für meine aktuellen Zwecke nicht so richtig geeignet.

Du solltest einmal genau überlegen was denn bearbeitet werden muß, denn man muß ja die bedingten Formatierungen garnicht direkt bearbeiten können müssen um deren Wirkung/Verhalten anzupassen, das ginge ja auch indirekt über Zellformeln indem Du nur deren Rückgabe in die bedingte Formatierung einfließen ließest.

In Deiner Beispieldatei mit den bunten Feldern anhand der Zahlen 1-5 kann ich auch weiterhin nur die ersten 3 Bedingungen sehen und bei Bedarf bearbeiten, ich nehme an, dass der Rest nach Installation der Extension bearbeitbar wäre?

Ja, nach Installation der EXtension wären alle 5 bearbeitbar.

Was noch das Sahnehäubchen auf der Honigschnitte wäre, wäre die Einfärbung oder Markierung anhand eines Teils des Inhalts, wie weiter oben im Thread (hoffentlich verständlich??) beschrieben.

Ich fürchte das wird nicht gehen weil es ja in Calc und Excel funktionieren soll, aber vielleicht weiß jemand anderes eine Lösung.

Ich fand den Hinweis auf mottco noch an anderer Stelle im Netz und da steht er unter der Überschrift "Makros"

Das ist eine FRage der Perspektive, denn mottco ist ein Makro aber gleichzeitig auch eine Extension, im Prinzip ist eine Extension ein 'Container' der z.B. Makros enthalten kann (so wie es hier bei mottco der Fall ist) oder anderes, z.B. Dokumentvorlagen oder Symbolleisten.
Bei einer Extension die ein Makro enthält ist es deshalb etwas wahlfrei ob man das als Makro oder Extension anspricht.


Gruß
Stephan

[Stephan]

Ich fürchte das wird nicht gehen weil es ja in Calc und Excel funktionieren soll,

Nö, da habe ich Quatsch erzählt.

als Ersatz für:

C2="Mary Ferien"

sollte, in Calc und Excel, funktionieren:

NICHT(ISTFEHLER(SUCHEN("Mary";C2;1)))

womit dann auch Zellinhalte wie "Hut von Mary" oder "Mary and Gordy" ausgewertet werden können.



Gruß
Stephan