9
EXCEL: Bestimmten Datumsbereich mit Wert aus mehreren Jahren ermitteln und farbig hinterlegen
Hallo Leute - ich hätte da mal wieder eine Excel Frage an euch! 
Ich benötige aus einem Zeitraum (Spalte mit Datum) von 2012-2017 jeweils einen bestimmten Zeitraum, welcher mit einem bestimmten Wert hinterlet ist, aus jedem Jahr.
Die Bereich sind wie folgt fixiert:
WERT
Bereich 1: 01.10. 01.12. 8
Bereich 2: 20.12. 01.02. 4
Bereich 3: 01.03. 31.03. 8
Jetzt habe ich eine Datum-Spalte und eine Spalte mit den Werten. Jetzt wurde ich gerne die Zellen farbig hinterlegen welche den Werten aus dem Bereich entspreicht.
zb. Wenn am 01.01.2012 der Wert lt. Spalte 8 ist aber lt. dem fix definierten Bereich nur 4 ist, sollte dieser ROT markiert werden - wenn der WERT mit dem definierten WERT übereinstimmt oder darunter liegt sollte er GRÜN hinterlegt werden.
Damit das ganze vielleicht etwas klarer wird habe ich ein Screenshot beigefügt (Bilder sagen mehr als 1000Worte)
DANKE für eure HILFE
sims
Ich benötige aus einem Zeitraum (Spalte mit Datum) von 2012-2017 jeweils einen bestimmten Zeitraum, welcher mit einem bestimmten Wert hinterlet ist, aus jedem Jahr.
Die Bereich sind wie folgt fixiert:
WERT
Bereich 1: 01.10. 01.12. 8
Bereich 2: 20.12. 01.02. 4
Bereich 3: 01.03. 31.03. 8
Jetzt habe ich eine Datum-Spalte und eine Spalte mit den Werten. Jetzt wurde ich gerne die Zellen farbig hinterlegen welche den Werten aus dem Bereich entspreicht.
zb. Wenn am 01.01.2012 der Wert lt. Spalte 8 ist aber lt. dem fix definierten Bereich nur 4 ist, sollte dieser ROT markiert werden - wenn der WERT mit dem definierten WERT übereinstimmt oder darunter liegt sollte er GRÜN hinterlegt werden.
Damit das ganze vielleicht etwas klarer wird habe ich ein Screenshot beigefügt (Bilder sagen mehr als 1000Worte)
DANKE für eure HILFE
sims
Auf Facebook teilen
Auf Twitter teilen
Auf Reddit teilen
Auf Linkedin teilenBitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben
Content-ID: 342377
Url: https://administrator.de/contentid/342377
Ausgedruckt am: 16.11.2024 um 09:11 Uhr
9 Kommentare
Neuester Kommentar
Moin,
da die Jahreszahl variabel ist, kommst du um eine Lösung mit VBA nicht wirklich herum.
Eine solche Lösung ist aber nicht mal nebenher in einem Forum erklärt.
Gruß
da die Jahreszahl variabel ist, kommst du um eine Lösung mit VBA nicht wirklich herum.
Eine solche Lösung ist aber nicht mal nebenher in einem Forum erklärt.
Gruß
Moin sims,
ist im Prinzip mit einer SUMMENPRODUKT()-Formel als bedingte Formatierung hinzubekommen.
Allerdings würde ich ganz pragmatisch deinen "Bereich 2" aufsplitten in zwei Bereiche 2a und 2b, um mit dem "Zeitraum über Jahreswechsel" umzugehen.
Alt:
Bereich 1 01.10 bis 01.12 Wert 8
Bereich 2: 20.12. bis 01.02 Wert 4
Bereich 3: 01.03. bis 31.03 Wert 8
Neu ( bei mir in Zellen $H$5...$K$8):
Bereich 1 01.10 bis 01.12 Wert 8
Bereich 2a: 20.12. bis 31.12 Wert 4
Bereich 2b: 01.01. bis 01.02 Wert 4
Bereich 3: 01.03. bis 31.03 Wert 8
Dann würde eine Teil-Formatier-mir-Grün-Formel (für Bereich 2b) so lauten
Für "Rot"
Wenn denn "Datumsbereich" von B$4 bis B1001, Wertbereich von C$4 bis C1001,
"Bereiche" 4 Spalten x 4 Zeilen in den Zellen $H$5 bis $K$8 stehen.
So habe ich es in einem Klone deines Screenshots eben ausprobiert.
Die oben stehendenden GRÜN- bzw. ROT-Formeln für "Bereich 2b" müsseten dann jeweils mit "+" "ODER-verkettet werden mit den GRÜN- bzw ROT-Formeln für Bereich 1, Bereich 2a und Bereich 3 nach dem Schema
GRÜN-Formel
ROT-Formel analog.
Also all in all eine Sache von 6 Minuten.
Grüße
Biber
ist im Prinzip mit einer SUMMENPRODUKT()-Formel als bedingte Formatierung hinzubekommen.
Allerdings würde ich ganz pragmatisch deinen "Bereich 2" aufsplitten in zwei Bereiche 2a und 2b, um mit dem "Zeitraum über Jahreswechsel" umzugehen.
Alt:
Bereich 1 01.10 bis 01.12 Wert 8
Bereich 2: 20.12. bis 01.02 Wert 4
Bereich 3: 01.03. bis 31.03 Wert 8
Neu ( bei mir in Zellen $H$5...$K$8):
Bereich 1 01.10 bis 01.12 Wert 8
Bereich 2a: 20.12. bis 31.12 Wert 4
Bereich 2b: 01.01. bis 01.02 Wert 4
Bereich 3: 01.03. bis 31.03 Wert 8
Dann würde eine Teil-Formatier-mir-Grün-Formel (für Bereich 2b) so lauten
=SUMMENPRODUKT((MONAT($B4)>=MONAT($I$7))*(MONAT($B4)<=MONAT($J$7))*($K$7>=$C4))Für "Rot"
=SUMMENPRODUKT((MONAT($B4)>=MONAT($I$7))*(MONAT($B4)<=MONAT($J$7))*($K$7<$C4))Wenn denn "Datumsbereich" von B$4 bis B1001, Wertbereich von C$4 bis C1001,
"Bereiche" 4 Spalten x 4 Zeilen in den Zellen $H$5 bis $K$8 stehen.
So habe ich es in einem Klone deines Screenshots eben ausprobiert.
Die oben stehendenden GRÜN- bzw. ROT-Formeln für "Bereich 2b" müsseten dann jeweils mit "+" "ODER-verkettet werden mit den GRÜN- bzw ROT-Formeln für Bereich 1, Bereich 2a und Bereich 3 nach dem Schema
GRÜN-Formel
=(Summenproduktfromel für Bereich 1 GRÜN) +(Summenproduktfromel für Bereich 2a GRÜN) +(Summenproduktfromel für Bereich 2b GRÜN) +(Summenproduktfromel für Bereich 3 GRÜN) Also all in all eine Sache von 6 Minuten.
Grüße
Biber
Hi!
Wird eine ziemlich lange Formel, da du (wie oben bereits von anderen erwähnt wurde) den Übergang über das Jahr noch rausbringen musst (das habe ich hier mit "Datum(Text())" gemacht). Im beigelegten Screenshot wurden 2 Bedingte Formatierungen für den Bereich "Werte" (also B4 bis B1000 oder so markieren) eingefügt. Der Unterschied der beiden Formeln ist nur ">" statt "<=", welche Fett und Unterstrichen sind. Das erste soll grün, das zweite rot markiert werden...
Formel 1 (grün):
=ODER(UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$4;"MM");TEXT($H$4;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$4;"MM");TEXT($I$4;"TT"));B3<=$J$4);UND(DATUM(1;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$5;"MM");TEXT($H$5;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$5;"MM");TEXT($I$5;"TT"));B3<=$J$5);UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$6;"MM");TEXT($H$6;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$6;"MM");TEXT($I$6;"TT"));B3<=$J$6))
Formel 2 (rot) :
=ODER(UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$4;"MM");TEXT($H$4;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$4;"MM");TEXT($I$4;"TT"));B3>$J$4);UND(DATUM(1;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$5;"MM");TEXT($H$5;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$5;"MM");TEXT($I$5;"TT"));B3>$J$5);UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$6;"MM");TEXT($H$6;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$6;"MM");TEXT($I$6;"TT"));B3>$J$6))
mfG
Stefan Steiner
Wird eine ziemlich lange Formel, da du (wie oben bereits von anderen erwähnt wurde) den Übergang über das Jahr noch rausbringen musst (das habe ich hier mit "Datum(Text())" gemacht). Im beigelegten Screenshot wurden 2 Bedingte Formatierungen für den Bereich "Werte" (also B4 bis B1000 oder so markieren) eingefügt. Der Unterschied der beiden Formeln ist nur ">" statt "<=", welche Fett und Unterstrichen sind. Das erste soll grün, das zweite rot markiert werden...
Formel 1 (grün):
=ODER(UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$4;"MM");TEXT($H$4;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$4;"MM");TEXT($I$4;"TT"));B3<=$J$4);UND(DATUM(1;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$5;"MM");TEXT($H$5;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$5;"MM");TEXT($I$5;"TT"));B3<=$J$5);UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$6;"MM");TEXT($H$6;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$6;"MM");TEXT($I$6;"TT"));B3<=$J$6))
Formel 2 (rot) :
=ODER(UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$4;"MM");TEXT($H$4;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$4;"MM");TEXT($I$4;"TT"));B3>$J$4);UND(DATUM(1;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$5;"MM");TEXT($H$5;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$5;"MM");TEXT($I$5;"TT"));B3>$J$5);UND(DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))>=DATUM(;TEXT($H$6;"MM");TEXT($H$6;"TT"));DATUM(;TEXT(A3;"MM");TEXT(A3;"TT"))<=DATUM(;TEXT($I$6;"MM");TEXT($I$6;"TT"));B3>$J$6))
mfG
Stefan Steiner
1
PS: das Datum bei Bereich 1-3 ist ein Datum mit Jahreszahl (welche ist egal), die Anzeige ist nur so formatiert...
Na dann,
nach der guten Vorarbeit von Stone90 reduziert sich bei Verwendung von Text(datum;"MMTT") meine Formel in C4:Cxxxx auf:
GRÜN
ROT:
Ist zwar eigentlich eine Matrixformel, d.h. müsste in normalen Zellen mit Ctrl-Shift-Enter abgeschlossen werden.
Aber in deiner bed. Formatierung geht es auch so.
Ach ja, dann reduziert sich natürlich auch der ganze Aufwand für die bedingte Formatierung von 6 Minuten auf 3 Minuten.
Dennoch: bei Tageswerten von 2012 bis 2017 bringst du aber den Rechner schon etwas ins Schwitzen bei der Menge der bedingten Formatierungen.
Andererseits werden nur die Zellen berechnet, die jeweils gerade auf dem Monitor angezeigt werden.
Stinkelangsam wird also das Blättern durch die Daten oder irgendwelche Aktionen mit Autofilter o.ä.
Grüße
Biber
nach der guten Vorarbeit von Stone90 reduziert sich bei Verwendung von Text(datum;"MMTT") meine Formel in C4:Cxxxx auf:
GRÜN
=SUMME(SUMMENPRODUKT((TEXT($B4;"MMTT")>=TEXT($I$5:$I$8;"MMTT"))*(TEXT($B4;"MMTT")<=TEXT($J$5:$J$8;"MMTT"))*($K$5:$K$8>=$C4))) ROT:
=SUMME(SUMMENPRODUKT((TEXT($B4;"MMTT")>=TEXT($I$5:$I$8;"MMTT"))*(TEXT($B4;"MMTT")<=TEXT($J$5:$J$8;"MMTT"))*($K$5:$K$8<$C4))) Ist zwar eigentlich eine Matrixformel, d.h. müsste in normalen Zellen mit Ctrl-Shift-Enter abgeschlossen werden.
Aber in deiner bed. Formatierung geht es auch so.
Ach ja, dann reduziert sich natürlich auch der ganze Aufwand für die bedingte Formatierung von 6 Minuten auf 3 Minuten.
Dennoch: bei Tageswerten von 2012 bis 2017 bringst du aber den Rechner schon etwas ins Schwitzen bei der Menge der bedingten Formatierungen.
Andererseits werden nur die Zellen berechnet, die jeweils gerade auf dem Monitor angezeigt werden.
Stinkelangsam wird also das Blättern durch die Daten oder irgendwelche Aktionen mit Autofilter o.ä.
Grüße
Biber
3
Moin @Biber,
sehr interessanter Lösungsansatz! Ich muss gestehen, dass ich diesen nur bedingt nachvollziehen kann - Summenprodukt ist mir nicht geläufig.
Ich würde in diesem Fall aber trotzdem von einer Lösung mit Standardformeln absehen. Nicht nur, das die Performance tatsächlich ein Problem sein kann, wie ich gerade feststellen durfte, sind solche Formeln nur sehr schwer wartbar / erweiterbar.
Trotzdem nen geiler Ansatz
Gruß
PS: Nicht einmal ein Danke vom TO... immerhin hat er eure Antworten als Lösung markiert...
sehr interessanter Lösungsansatz! Ich muss gestehen, dass ich diesen nur bedingt nachvollziehen kann - Summenprodukt ist mir nicht geläufig.
Ich würde in diesem Fall aber trotzdem von einer Lösung mit Standardformeln absehen. Nicht nur, das die Performance tatsächlich ein Problem sein kann, wie ich gerade feststellen durfte, sind solche Formeln nur sehr schwer wartbar / erweiterbar.
Trotzdem nen geiler Ansatz
Gruß
PS: Nicht einmal ein Danke vom TO... immerhin hat er eure Antworten als Lösung markiert...
Stimmt, der Lösungsansatz mit Summenprodukt (übrigens eine sehr praktisch Lösung für diverse Ansätze) ist zwar vielleicht nicht so leicht zu kapieren, aber einfacher und übersichtlicher und funktioniert genauso gut. Auch das mit dem Übergang des Jahreswechsels ist von Biber etwas einfacher gelöst, würde ich vielleicht doch auch so machen...
Hallo Leute!
Besten DANK für euren Einsatz - habe es soeben getestet und "jawohl" es funktioniert einwandfrei!!!!!
DANKE für den MEGA Support
Besten DANK für euren Einsatz - habe es soeben getestet und "jawohl" es funktioniert einwandfrei!!!!!
DANKE für den MEGA Support
Moin Kraemer,
SUMMENPRODUKT() ist nicht so schwer nachzuvollziehen - empfehle ich dir mal anzuschauen.
Vereinfacht ausgedrückt ist Summenprodukt auf eine Zeile bezogen nur das Produkt von mehreren numerischen Werten.
Also z.B Summenprodukt zur Berechnung Umsatz (einer Zeile) = Summenprodukt( ZelleMitMenge*ZelleMitPreis)
Gesamtumsatz wäre dann =Summenprodukt(SpalteMitMenge*SpalteMitPreis).
Genau das zeilenweise Summenprodukt() mache ich mit WAHR/FALSCH-Werten statt numerischen Werten.
Demnach kommt bei mir je Zeile heraus
Also am Ende des Tages entweder der Wert 1, wenn alle 3 Bedingungen erfüllt sind oder 0, wenn nicht.
Und dieses 0/1 reicht vollkommen, um die bed.Formatierung zu steuern.
Ich glaube auch offen gestanden nicht, dass die Umsetzung mit einer VBA-Function oder auch mit einem in C#-programmierten AddIn in einer Com-DLL schneller wäre. Von Les- und Wartbarkeit mal abgesehen.
Wenn da 2000 Zellen bedingt zu formatieren sind, dann muss eben 2000x irgendein beknackter Algorithmus rumrödeln und auch notgedrungen 2000x aufgerufen werden. Und er muss eben auch 2000 rattern, wenn dadurch keine einzige Zelle bunter wird als vorher.
Da ist nicht Performance-Optimierung die Lösung, sondern Überprüfung der Sinnhaftigkeit der Anforderung ("Muss ich wirklich jeden Tag auf dem Schirm sehen, welche Tage im Januar 2013 rote oder grüne Werte hatten?")
Grüße
Biber
SUMMENPRODUKT() ist nicht so schwer nachzuvollziehen - empfehle ich dir mal anzuschauen.
Vereinfacht ausgedrückt ist Summenprodukt auf eine Zeile bezogen nur das Produkt von mehreren numerischen Werten.
Also z.B Summenprodukt zur Berechnung Umsatz (einer Zeile) = Summenprodukt( ZelleMitMenge*ZelleMitPreis)
Gesamtumsatz wäre dann =Summenprodukt(SpalteMitMenge*SpalteMitPreis).
Genau das zeilenweise Summenprodukt() mache ich mit WAHR/FALSCH-Werten statt numerischen Werten.
Demnach kommt bei mir je Zeile heraus
=Summenprodukt( ( "WAHRFALSCH_für_MMTT>=MMTTUnterlimit")
* ("WAHRFALSCH_für_MMTT<MMTTOberlimit") *
("WAHRFALSCH_für_Wert_ist_Kleiner_als_Wertlimit")) Also am Ende des Tages entweder der Wert 1, wenn alle 3 Bedingungen erfüllt sind oder 0, wenn nicht.
Und dieses 0/1 reicht vollkommen, um die bed.Formatierung zu steuern.
Ich glaube auch offen gestanden nicht, dass die Umsetzung mit einer VBA-Function oder auch mit einem in C#-programmierten AddIn in einer Com-DLL schneller wäre. Von Les- und Wartbarkeit mal abgesehen.
Wenn da 2000 Zellen bedingt zu formatieren sind, dann muss eben 2000x irgendein beknackter Algorithmus rumrödeln und auch notgedrungen 2000x aufgerufen werden. Und er muss eben auch 2000 rattern, wenn dadurch keine einzige Zelle bunter wird als vorher.
Da ist nicht Performance-Optimierung die Lösung, sondern Überprüfung der Sinnhaftigkeit der Anforderung ("Muss ich wirklich jeden Tag auf dem Schirm sehen, welche Tage im Januar 2013 rote oder grüne Werte hatten?")
Grüße
Biber
