Supportnet / Forum / Tabellenkalkulation
Problem bei bedingter Formatierung
Frage
Hallo Excelfreunde,
Folgender Ausgangspunkt:
In Spalte B stehen die Vorjahreszahlen summiert, in Spalte C stehen die bis zum Ende des Vormonats summierten Werte. Nun möchte ich in Spalte D die zum Jahresende prognostizierten Werte darstellen und über eine bedingte Formatierung die Zelle entsprechend rot oder grün hinterlegen. Der für die Abfrage vorgegebene Monat steht als Zahl (1-12) in der Zelle G1, oder noch besser sollte über ein Listenfeld Januar….Dezember auswählbar sein.
Die Prognoseberechnung in Spalte D habe ich soweit umgesetzt mit der Formel
=WENN(B5="";0;(B5/$G$1*12))
Nun habe ich aber das Problem mit der bedingten Formatierung: Ist in der Spalte B (Vorjahreszahl) nichts eingetragen, also 0 UND in der Spalte C (Summe aktuelles Jahr ) kein Wert eingetragen, also auch 0, soll der ermittelte Wert = 0 z.B. orange hinterlegt werden.
Die derzeitige bedingte Formatierung in Spalte D sieht so aus:
a) Ist der prognostizierte Wert größer oder gleich der Vorjahressumme, dann GRÜN.
b) Ist der prognostizierte Wert kleiner der Vorjahressumme, dann ROT
Ich möchte jedoch unter b) eine Ausnahme einbringen, wenn beide Zellen den Wert ““, bzw. 0 haben, dann soll die Zelle orange hinterlegt werden.
Wie kann ich das hinbekommen? Bin für jeden Lösungsvorschlag dankbar!
MfG
nightdream
Antwort 1 von CaroS
Hallo nightdream,
das sollte kein Problem sein. In D5 wird also der prognostizierte Wert berechnet und diese Zelle soll auch entsprechend eingefärbt werden?
1. Bedingung:
Formel ist -- =UND(B5="";$G$1="") -- Format... = orange
Hinzufügen >> 2. Bedingung:
Zellwert ist -- größer oder gleich -- =$B$5 -- -- Format... = grün
Hinzufügen >> 3. Bedingung:
Zellwert ist -- kleiner -- =$B$5 -- -- Format... = rot
Die $-Zeichen musst Du nur bei Bedarf verwenden, wenn die bed. Formatierung mit Anpassung der Formeln auf andere Zellen kopiert werden soll.
Ein Problem sehe ich allerdings bei der Berechnung des prognostizierten Werts: =WENN(B5="";0;(B5/$G$1*12)). Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist. Kann sein, dass Du daran gedacht hast, aber dann müsstest Du =WENN($G$1=""; ... statt =WENN(B5=""; ... schreiben.
Gruß,
CaroS
das sollte kein Problem sein. In D5 wird also der prognostizierte Wert berechnet und diese Zelle soll auch entsprechend eingefärbt werden?
1. Bedingung:
Formel ist -- =UND(B5="";$G$1="") -- Format... = orange
Hinzufügen >> 2. Bedingung:
Zellwert ist -- größer oder gleich -- =$B$5 -- -- Format... = grün
Hinzufügen >> 3. Bedingung:
Zellwert ist -- kleiner -- =$B$5 -- -- Format... = rot
Die $-Zeichen musst Du nur bei Bedarf verwenden, wenn die bed. Formatierung mit Anpassung der Formeln auf andere Zellen kopiert werden soll.
Ein Problem sehe ich allerdings bei der Berechnung des prognostizierten Werts: =WENN(B5="";0;(B5/$G$1*12)). Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist. Kann sein, dass Du daran gedacht hast, aber dann müsstest Du =WENN($G$1=""; ... statt =WENN(B5=""; ... schreiben.
Gruß,
CaroS
Antwort 2 von Primut
Hey Nightdream,
eigentlich ganz einfach. Wichtig zu wissen: Die Entsprechenden Bedingungen(1-3) der bed. Formatierung werden nacheinander abgearbeitet, d.h. wenn eine Bedingung erfüllt ist, werden folgende nicht mehr geprüft.
Es reicht also schon, deine Ausnahmebedingung nur als 2. Bedingung vor der 3. Bedingung anzuordnen.
Dafür wählst du nicht Zellwert sondern Formel ist und trägst anschließend ein: =UND(Zelle1=Zelle2;Zelle1=0) Format Orange
Die entsprechenden Zellen mußt du natürlich selbst eintragen. Hier wird nur geprüft, ob beide Zellen gleich sind und 0 sind. Ist eine Zellle nur 0, trifft diese Bedingung also nicht zu.
Viel Spaß beim weiter arbeiten
Gruß Primut
eigentlich ganz einfach. Wichtig zu wissen: Die Entsprechenden Bedingungen(1-3) der bed. Formatierung werden nacheinander abgearbeitet, d.h. wenn eine Bedingung erfüllt ist, werden folgende nicht mehr geprüft.
Es reicht also schon, deine Ausnahmebedingung nur als 2. Bedingung vor der 3. Bedingung anzuordnen.
Dafür wählst du nicht Zellwert sondern Formel ist und trägst anschließend ein: =UND(Zelle1=Zelle2;Zelle1=0) Format Orange
Die entsprechenden Zellen mußt du natürlich selbst eintragen. Hier wird nur geprüft, ob beide Zellen gleich sind und 0 sind. Ist eine Zellle nur 0, trifft diese Bedingung also nicht zu.
Viel Spaß beim weiter arbeiten
Gruß Primut
Antwort 3 von nightdream
Hallo CaroS und Primut,
vielen Dank zunächst,
habe es ausprobiert und ES KLAPPT :-))
CaroS schrieb:
Wenn es denn einen Monat 0 geben würde, stimme ich Dir zu ;-)
Habt ihr vielleicht noch ´ne Idee, wie ich die Zelle "Monat" als Listenfeld in Zelle $G$1 einbinden kann? Also nicht als Zahl 1- 12, sondern als Januar, usw.
Mir schwebt da ´ne Matrix ähnlich SVERWEIS vor, nur fehlt mir im Moment der zündende Funke...
Gruß
nightdream
vielen Dank zunächst,
habe es ausprobiert und ES KLAPPT :-))
CaroS schrieb:
Zitat:
Ein Problem sehe ich allerdings bei der Berechnung des prognostizierten Werts: =WENN(B5="";0;(B5/$G$1*12)). Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist.
Ein Problem sehe ich allerdings bei der Berechnung des prognostizierten Werts: =WENN(B5="";0;(B5/$G$1*12)). Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist.
Wenn es denn einen Monat 0 geben würde, stimme ich Dir zu ;-)
Habt ihr vielleicht noch ´ne Idee, wie ich die Zelle "Monat" als Listenfeld in Zelle $G$1 einbinden kann? Also nicht als Zahl 1- 12, sondern als Januar, usw.
Mir schwebt da ´ne Matrix ähnlich SVERWEIS vor, nur fehlt mir im Moment der zündende Funke...
Gruß
nightdream
Antwort 4 von Primut
Hi Nightdream,
ich würd das Listenfeld so machen: Schreib irgenwo, wo es nicht stört alle 12 Monate untereinander hin, markiere alle Felder und gib ihnen einfach den Namen zB Monat. (Wenn Sie alle markiert sind, einfach Monat in das Feld ganz links oben über der A -Spalte Schreiben, oder über Einfügen / Namen/Definieren)
Damit hast du der Monatsliste den Namen Monat zugeordnet. Jetzt einfach in $G$1: Daten / Gültigkeit /Zulassen: Liste und dort bei Quelle einfach =Monat eintragen.
Ist auch möglich ohne Listennamen gleich dort bei Quelle =Bereich zu markieren. Wobei der Bereich dann die 12 Monatsfelder sein müßten. Hoffe das es das ist, was du gewünscht hast.
Gruß Primut
ich würd das Listenfeld so machen: Schreib irgenwo, wo es nicht stört alle 12 Monate untereinander hin, markiere alle Felder und gib ihnen einfach den Namen zB Monat. (Wenn Sie alle markiert sind, einfach Monat in das Feld ganz links oben über der A -Spalte Schreiben, oder über Einfügen / Namen/Definieren)
Damit hast du der Monatsliste den Namen Monat zugeordnet. Jetzt einfach in $G$1: Daten / Gültigkeit /Zulassen: Liste und dort bei Quelle einfach =Monat eintragen.
Ist auch möglich ohne Listennamen gleich dort bei Quelle =Bereich zu markieren. Wobei der Bereich dann die 12 Monatsfelder sein müßten. Hoffe das es das ist, was du gewünscht hast.
Gruß Primut
Antwort 5 von nightdream
Hi Primut,
danke Dir für den Hinweis. Das WIE mit der Liste war mir schon klar, allerdings muß ich der Formel ja irgendwie sagen, dass sie z.B. bei einem Wert "März" mit der Zahl 3, oder September mit der Zahl 9 rechnen soll.
Ich hab´s mal mit SVERWEIS versucht:
=WENN(AC6="";0;(AC6/(SVERWEIS($AG$2;AM5:AN16;2;0)*12)))
wenn Zelle AC6 leer, dann schreibe 0, sonst dividiere den Wert AC6 durch Monat ($AG$2) "März" (Matrix für SVERWEIS = AM5:AN16) .März entspricht Zahl 3, und multipliziere das Ergebnis mit der Zahl 12.
Irgendwie kommt da Schrott raus=> 0,2222
Bei AC6 = 8 und dem Monat März müsste ich als Hochrechnung den Wert 32 haben! Was mache ich da falsch. Kann mir jemand auf die Sprünge helfen?
Danke
nightdream
danke Dir für den Hinweis. Das WIE mit der Liste war mir schon klar, allerdings muß ich der Formel ja irgendwie sagen, dass sie z.B. bei einem Wert "März" mit der Zahl 3, oder September mit der Zahl 9 rechnen soll.
Ich hab´s mal mit SVERWEIS versucht:
=WENN(AC6="";0;(AC6/(SVERWEIS($AG$2;AM5:AN16;2;0)*12)))
wenn Zelle AC6 leer, dann schreibe 0, sonst dividiere den Wert AC6 durch Monat ($AG$2) "März" (Matrix für SVERWEIS = AM5:AN16) .März entspricht Zahl 3, und multipliziere das Ergebnis mit der Zahl 12.
Irgendwie kommt da Schrott raus=> 0,2222
Bei AC6 = 8 und dem Monat März müsste ich als Hochrechnung den Wert 32 haben! Was mache ich da falsch. Kann mir jemand auf die Sprünge helfen?
Danke
nightdream
Antwort 6 von nightdream
Hallo Leute,
habe gerade den Fehler selbst gefunden!
Ein Satz Klammern (nach dem Divisionszeichen bei AC6) war zu viel, jetzt klappt es wunderbar.
die Formel lautet jetzt
=WENN(AC6="";0;(AC6/SVERWEIS($AG$2;AM5:AN16;2;0)*12))
Danke an alle Helferlein
nightdream
habe gerade den Fehler selbst gefunden!
Ein Satz Klammern (nach dem Divisionszeichen bei AC6) war zu viel, jetzt klappt es wunderbar.
die Formel lautet jetzt
=WENN(AC6="";0;(AC6/SVERWEIS($AG$2;AM5:AN16;2;0)*12))
Danke an alle Helferlein
nightdream
Antwort 7 von CaroS
Hallo nightdream,
wenn man sich im Forum mit Leuten wie Dir in einer freundlichen Grundstimmung auszutauschen kann, macht es selbst dann noch Spaß, wenn man mal eine kritische Bemerkung zu einem Beitrag bekommt. Man macht ja doch mal was falsch oder hat was überlesen. Dass in Deiner Zelle G1 ein Monat (Zahl 1 - 12) stehen soll hatte ich allerdings nicht überlesen. ;-)
CaroS schrieb:
und Du hast geantwortet:
CaroS schreibt jetzt noch dazu:
Ja, wenn es auch noch einen Monat 0 geben würde, dann wäre das Problem noch größer. Aber Du hast Glück und brauchst Dich deswegen nicht sorgen, sondern nur darum, dass in G1 auch wirklich immer etwas steht und G1 nie leer ist. Denn da lauert ja die Division durch 0 und ich hatte Dich vor G1="" gewarnt. ;-))
Übrigens, wenn in F1 ein Monatsname steht, wäre folgende Formel zur
Berechnung der Monatsnummer (1 - 12) in G1 noch eine Alternative zum SVERWEIS:
=MONAT(DATWERT("01. "&F1&" 1900"))
(Da MONAT den Monatsnamen in F1 nicht direkt umwandeln kann, sondern ein voll-ständiges Datum braucht, werden mit DATWERT zuerst Tag und Jahr (beliebig) dran-gebastelt. Bei MONAT kommt eine Zahl raus, damit kann man dann auch rechnen.)
Gruß,
CaroS
wenn man sich im Forum mit Leuten wie Dir in einer freundlichen Grundstimmung auszutauschen kann, macht es selbst dann noch Spaß, wenn man mal eine kritische Bemerkung zu einem Beitrag bekommt. Man macht ja doch mal was falsch oder hat was überlesen. Dass in Deiner Zelle G1 ein Monat (Zahl 1 - 12) stehen soll hatte ich allerdings nicht überlesen. ;-)
CaroS schrieb:
Zitat:
Ein Problem sehe ich ... Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist.
Ein Problem sehe ich ... Hier lauert eine Division durch 0, nämlich wenn B5<>"" und G1=0 oder G1="" ist.
und Du hast geantwortet:
Zitat:
Wenn es denn einen Monat 0 geben würde, stimme ich Dir zu ;-)
Wenn es denn einen Monat 0 geben würde, stimme ich Dir zu ;-)
CaroS schreibt jetzt noch dazu:
Ja, wenn es auch noch einen Monat 0 geben würde, dann wäre das Problem noch größer. Aber Du hast Glück und brauchst Dich deswegen nicht sorgen, sondern nur darum, dass in G1 auch wirklich immer etwas steht und G1 nie leer ist. Denn da lauert ja die Division durch 0 und ich hatte Dich vor G1="" gewarnt. ;-))
Übrigens, wenn in F1 ein Monatsname steht, wäre folgende Formel zur
Berechnung der Monatsnummer (1 - 12) in G1 noch eine Alternative zum SVERWEIS:
=MONAT(DATWERT("01. "&F1&" 1900"))
(Da MONAT den Monatsnamen in F1 nicht direkt umwandeln kann, sondern ein voll-ständiges Datum braucht, werden mit DATWERT zuerst Tag und Jahr (beliebig) dran-gebastelt. Bei MONAT kommt eine Zahl raus, damit kann man dann auch rechnen.)
Gruß,
CaroS