Bedingte Formatierung in Abh. von Nachbarzelle

Computer: Software & Programmierung Office Software
#1

Hallo ihr Profis,

ich möchte in einer Zeile erreichen, daß all jene Zellen farblich markiert werden, in deren Wert mit einem anderen übereinstimmt, und deren darunterliegende Zelle das heutige Datum enthält. In der Bedingten Formatierung (BF) hatte ich dafür die Formel hinterlegt

=(UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))

Das funktioniert auch bei der ersten Zelle. Wenn ich aber den Bereich der BF nun über „Wird angewendet auf“ auf die ganze Zeile ausdehne, bezieht sich die BF in jeder Zelle immer nur auf A1 und A2. Wie erreiche ich, daß sich beim horizontalen Kopieren der BF die Werte entsprechend ändern? Daß also in den jeweils rechts danebenliegenden Zellen die Formel der BF wie folgt hochzählt:

Zelle A1: =(UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))
Zelle A2: =(UND(B1=KALENDERWOCHE(JETZT());JAHR(B2)=JAHR(JETZT()))
Zelle A3: =(UND(C1=KALENDERWOCHE(JETZT());JAHR(C2)=JAHR(JETZT()))
Zelle A4: =(UND(D1=KALENDERWOCHE(JETZT());JAHR(D2)=JAHR(JETZT()))
etc.

Vielen Dank und ein schönes Wochenende
Hanno

#2

Hallo Hanno,
du kopierst die Formel zeilenweise nach unten,
der Bezug soll sich dabei aber spaltenweise verschieben
Das funktioniert nicht mit einfachen kopieren / einfügen.

=INDIREKT(„Z1S“&ZEILE(A1);0)
=INDIREKT(„Z2S“&ZEILE(A1);0)

Diese beiden Formeln erzeugen einen Bezug nach A1 und A2.
Da sich Zeile() beim kopieren nach unten erhöht „wandert“ der Bezug wie gewünscht spaltenweise nach rechts.

Gruß Holger

#3

Kalenderwoche() rechnet falsch
Hallo Hanno,

schau mal wegen Kalenderwoche hier:
http://www.excelformeln.de/formeln.html?welcher=7

Du benutzt bei deiner Formel die Form:
=(…)
Die äußeren Klammern kannste löschen.
Weil ich das gelegentlich sehe frage ich mich warum man das macht.
Kommt das irgendwo aus der Excel-Hilfe her? Aus einem Buch?

Wenn das von die eine Eigenerfindung ist nach dem Motto sicher ist sicher und ich klammer das ein so ist das okay.
Ich würde nur gerne wissen ob es dafür andere Gründe gibt.

Zu deiner Anfrage selbst. Mich irritiert daß du da wohl Formeln in A1:B1 o.ä. horizontal kopieren willst, also navh rechts.
Wie es aussehen soll stellst du aber dar als ob du die Formeln vertikal also nach unten kopiert hast.
Das irritiert mich.

Vielleicht blicke ich grad nicht durch und Holgers Antwort ist richtig.
Quin sabe :smile:

Schönes WE an alle

Gruß
Reinhard

#4

Danke, bei mir ist es aber nur horizontal
Hallo Holger,

super, der Tipp zur Herangehensweise ist schon mal sehr hilfreich vielen Dank (dafür ein Sternchen).

du kopierst die Formel zeilenweise nach unten,
der Bezug soll sich dabei aber spaltenweise verschieben
Das funktioniert nicht mit einfachen kopieren / einfügen.

Nicht ganz: die ganze Sache spielt sich horizontal ab. Ich benötige also eine Formel die sich beim kopieren nach rechts entsprechend anpaßt.

=INDIREKT(„Z1S“&ZEILE(A1);0)
=INDIREKT(„Z2S“&ZEILE(A1);0)

Diese beiden Formeln erzeugen einen Bezug nach A1 und A2.
Da sich Zeile() beim kopieren nach unten erhöht „wandert“ der
Bezug wie gewünscht spaltenweise nach rechts.

Ich schreibe nochmal, was ich habe. In der Zeile 2 eine Reihe von Datumswerten (alles Montage), in Zeile 1 die daraus errechnete jeweilige Kalenderwoche:

 1 2 3 4 ...
01.01.2011 08.01.2011 15.01.2011 22.01.2011 ...

Da sich das über mehrere Jahre hinzieht, muß ich beim markieren der aktuellen Kalenderwochen-Zelle auch das zugehörige, darunterstehende Jahr überprüfen.

Ich werde mal mit Deinem Ansatz herumdoktern und schauen, ob ichs auf dieses Problem übertragen kann.

Viele Grüße
Hanno

#5

Hallo Hanno,

um das abzukürzen, erstelle mal eine Beispielmappe.
In Blatt1 sieht man die Daten und deine bisherigen Formeln wie sie aussehen nach dem Kopieren.
In Blatt 2 ähnlich aber da änderst du MaNUELL die Formeln so wie es für dich sein soll.

Lad das dann hoch mit file-upload o.ä., s. FAQ:2606

Gruß
Reinhard

#6

Beispiel-Datei
Hallo Reinhard,

um das abzukürzen, erstelle mal eine Beispielmappe.

Gute Idee: http://www.file-upload.net/download-3517542/Mappe1.x…

Sorry, hatte zu schnell gelesen und nur die Grundlage geschaffen mit der Formel, wie ich dachte, daß sie funktionieren müßte… Sollte aber eigentlich alles erklären. Ich will eben, daß die Zelle der ersten Zeile mit der aktuellen Kalenderwoche markiert wird.

Danke ganz doll
Hanno

P. S.: Das Excel-Kalenderwochenproblem ist mir bekannt. Ich verwende auch schon eine eigene Funktion dafür, wollte deren Bezeichnung aber nicht hier verwenden, um Verwirrungen zu vermeiden und Erklärnöte zu reduzieren. Danke für den Tipp.

#7

Zelle A1: =(UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))
Zelle A2: =(UND(B1=KALENDERWOCHE(JETZT());JAHR(B2)=JAHR(JETZT()))
Zelle A3: =(UND(C1=KALENDERWOCHE(JETZT());JAHR(C2)=JAHR(JETZT()))
Zelle A4: =(UND(D1=KALENDERWOCHE(JETZT());JAHR(D2)=JAHR(JETZT()))

Hallo,
du hast deine BF erweitert auf =$A$1:blush:F$1
Dort steht aber etwas anderes! siehe Zeile A1:A4

Für dein Beispiel funktioniert die Formel doch … für A1:F1
Da wir heute den 18.06.2011 haben befinden wir uns in der 25kW
Daher wird natürlich nur Spalte C eingefärbt
http://www.mayhemmichi.de/echo/TempJean275.htm

Gruß Holger

1 Like
#8

Hallo Holger,

Zelle A1:
=(UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))
Zelle A2:
=(UND(B1=KALENDERWOCHE(JETZT());JAHR(B2)=JAHR(JETZT()))
Zelle A3:
=(UND(C1=KALENDERWOCHE(JETZT());JAHR(C2)=JAHR(JETZT()))
Zelle A4:
=(UND(D1=KALENDERWOCHE(JETZT());JAHR(D2)=JAHR(JETZT()))

Sorry, das verstehe ich nicht:

du hast deine BF erweitert auf =$A$1:blush:F$1

Wenn mit „erweitert“ gemeint ist, daß ich die BF auf alle Zellen der ersten Zeile anwende, dann ist das ja nur logisch. Es soll ja geprüft werden, welche Zelle die aktuelle KW des aktuellen Jahres enthält.

Dort steht aber etwas anderes! siehe Zeile A1:A4

Du meinst vermutlich Spalte A1:A4, und da stehen natürlich andere Sachen. Warum denn auch nicht!? Das verstehe ich nun absolut nicht.

Für dein Beispiel funktioniert die Formel doch … für A1:F1

Also jetzt doch? Warum dann die Einwände oben?

Da wir heute den 18.06.2011 haben befinden wir uns in der 25kW
Daher wird natürlich nur Spalte C eingefärbt

Bei mir wird eben leider gar nichts eingefärbt. Und wenn, dann sollte auch nur die Zelle C1 eingefärbt werden, nicht die ganze Spalte.

Sorry wenn ich mich so begriffstutzig anstelle, aber ich verstehe es wirklich nicht.

Viele Grüße
Hanno

#9

Hallo

Für dein Beispiel funktioniert die Formel doch … für A1:F1
Also jetzt doch? Warum dann die Einwände oben?

Weil sich dein Wunsch nach Beispiel nicht auf A1:F1 sondern auf A1:A4 Bezog.
Du wolltest in deinem ersten Beispiel das sich die Zellen A1:A4 einfärben während die Werte in A1:smiley:2 stehen.

Zelle A1:…
Zelle A2:…
Zelle A3:…
Zelle A4:…

A1:F1 verschiebt sich Spaltenweise
A1:A4 verschiebt sich Zeilenweise
Wenn sich sowohl deine Werte wie auch die formatierten Zellen Spaltenweise verschieben, verstehe ich deine ganze Frage nicht, denn dann funktioniert deine Formel, mit der Ausnahme, das in der Formel eine Klammer zufiel ist.

Es wird nur der Bereich formatiert auf den sich die Formel bezieht.

Gruß Holger

1 Like
#10

Hallo,

Für dein Beispiel funktioniert die Formel doch … für A1:F1
Also jetzt doch? Warum dann die Einwände oben?

Weil sich dein Wunsch nach Beispiel nicht auf A1:F1 sondern
auf A1:A4 Bezog.
Du wolltest in deinem ersten Beispiel das sich die Zellen
A1:A4 einfärben während die Werte in A1:smiley:2 stehen.

Genau.

Zelle A1:…
Zelle A2:…
Zelle A3:…
Zelle A4:…

Was hat das mit den Zellen A3 und A4 zu tun? Ich habe da doch nur Notizen hinterlegt? Für die BF sind nur A1:F2 relevant.

A1:F1 verschiebt sich Spaltenweise
A1:A4 verschiebt sich Zeilenweise
Wenn sich sowohl deine Werte wie auch die formatierten Zellen
Spaltenweise verschieben, verstehe ich deine ganze Frage
nicht, denn dann funktioniert deine Formel, mit der Ausnahme,
das in der Formel eine Klammer zufiel ist.

Es wird nur der Bereich formatiert auf den sich die Formel
bezieht.

Richtig. Und bezogen habe ich die BF auf den Bereich A1:F1, weil sich hierin die Zellen befinden, die eingefärbt werden sollen, wenn die Kalenderwoche der einzufärbenden Zelle und das Jahr der Zelle darunter mit den aktuellen Werten von KW und Jahr übereinstimmen.

Ich habe mal noch ein Beispiel erstellt: http://www.file-upload.net/download-3517845/Mappe1.x…

Grüße
Hanno

#11

Hallo Hanno,
du schreibst nicht das was du zuerst wolltest,
daher war zuerst der Lösungsansatz auch ein anderer.

Zelle A1: =(UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))
Zelle A2: =(UND(B1=KALENDERWOCHE(JETZT());JAHR(B2)=JAHR(JETZT()))
Zelle A3: =(UND(C1=KALENDERWOCHE(JETZT());JAHR(C2)=JAHR(JETZT()))
Zelle A4: =(UND(D1=KALENDERWOCHE(JETZT());JAHR(D2)=JAHR(JETZT()))

Dein Wunsch zuerst:
A1 färben wenn A1 und A2 WAHR ist;
A2 färben wenn B1 und B2 WAHR ist;
A3 färben wenn C1 und C2 WAHR ist;
A4 färben wenn D1 und D2 WAHR ist;
Das geht nur über =indirekt()

Jetzt soll sich
A1 färben wenn A1 und A2 WAHR ist;
B1 färben wenn B1 und B2 WAHR ist;
C1 färben wenn C1 und C2 WAHR ist;
D1 färben wenn D1 und D2 WAHR ist;

Das funktioniert mit deiner Formel einwandfrei.

Gruß Holger

1 Like
#12

Mir ist gerade aufgefallen…
…daß die Formel mit den Einzeltermen

=JAHR(INDIREKT("Z2S"&SPALTE();0))=JAHR(JETZT()

sowie

=INDIREKT("Z1S"&SPALTE();0)=KALENDERWOCHE(JETZT())

so arbeitet, wie man es erwarten würde. Sobald ich aber nur ein UND oder ODER davorsetze, funktioniert die Bedingung nicht mehr.

Also

=INDIREKT("Z1S"&SPALTE();0)=KALENDERWOCHE(JETZT())

färbt soweit richtig (würde ja auch ausreichen, wenn es nicht nach rechts hin noch mehr Jahre geben sollen könnte), und

=UND(INDIREKT("Z1S"&SPALTE();0)=KALENDERWOCHE(JETZT())

funktioniert nicht mehr.

Warum?

#13

Hallo
Wenn du zwei Formeln per und() knüpfen willst, muss das = dazwischen raus und durh ; ersetzt werden

zum Abschluss, verwende deine erste Formel, die funktioniert einwandfrei:
=UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))

1 Like
#14

Hallo Holger,

Dein Wunsch zuerst:
A1 färben wenn A1 und A2 WAHR ist;
A2 färben wenn B1 und B2 WAHR ist;
A3 färben wenn C1 und C2 WAHR ist;
A4 färben wenn D1 und D2 WAHR ist;
Das geht nur über =indirekt()

Das wollte ich nie, da hatte ich mich wohl blöd ausgedrückt. Sorry…

Jetzt soll sich
A1 färben wenn A1 und A2 WAHR ist;
B1 färben wenn B1 und B2 WAHR ist;
C1 färben wenn C1 und C2 WAHR ist;
D1 färben wenn D1 und D2 WAHR ist;

Dafür brauche ich aber doch auch „=INDIREKT()“, oder nicht?

Das funktioniert mit deiner Formel einwandfrei.

Tja, bei mir (siehe Beispieldatei) funktioniert es eben nicht!!! Sag bloß, meine Beispieldatei ist bei Dir in C1 eingefärbt? Ich werd verrückt…

#15

Verwende deine Erste Formel die funktioniert einwandfrei:
=UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))

Da war bei dir allerdings eine Klammer zu viel drin,
wenn du eine falsche Syntax im der bedingten Formatierung drin hattest ist das möglicher weise der Grund dafür das es nicht funktioniert hat.

Gruß Holger

#16

Hallo,

Wenn du zwei Formeln per und() knüpfen willst, muss das =
dazwischen raus und durh ; ersetzt werden

„Dazwischen“ habe ich keine =.

zum Abschluss, verwende deine erste Formel, die funktioniert
einwandfrei:
=UND(A1=KALENDERWOCHE(JETZT());JAHR(A2)=JAHR(JETZT()))

Das heißt, daß ich ganz ohne INDIREKT() auskomme? Demnach wird bei einer Formel für die BF die jeweils erste Zelle des angewendeten Bereichs als Basis herangezogen? Ich meine, weil ich ja A1 und A2 verwende, obwohl in den danebenliegenden Spalten ja auf B1 und B2, C1 und C2 usw. bezogen wird. Dann ist ja alles viel viel einfacher.

Dennoch komisch, daß in meinem hochgeladenen Beispiel die Chose klappt, solange sie nicht durch „UND( a = b ; c = d)“ verknüpft zusammen vorkamen. Kurz: Es klappt jetzt auch bei mir, aber ganz ehrlich, und ich meine das ganz ohne übertreibung: Ich verstehe jetzt wirklich weniger als zuvor.

Grübelnd
Hanno

#17

=UND(INDIREKT(„Z1S“&SPALTE();0)=KALENDERWOCHE(JETZT())
funktioniert nicht mehr.

wie auch lol

UND(
INDIREKT(
„Z1S“&SPALTE();0
)
=KALENDERWOCHE(
JETZT()
)

ist doch ein syntax fehler , da fehlt am ende eine klammer , da meldet mir jedenfals Excel …

aufjedenfall funktioniert
=UND(INDIREKT(„Z1S“&SPALTE();0)=KALENDERWOCHE(JETZT()))

#18

Unser Problem war das du mich zuerst in die Falsche Richtung geführt hast.
Wenn die Spalte formatiert werden soll, dabei die Werte aber aus einer Zeile kommt, dann geht das nur mit Indirekt() und zeile()

Wenn sich aber die Formel der bedingten Formatierung genau so bewegen wie die Werte ist indirekt() überflüssig.

Funktion Indirekt()
INDIREKT(„Z1S1“;0)
= Zeile1 / Spalte1 = Bezug A1

INDIREKT(„Z1S“&Zeile();0)
= Zeile 1 / Spalte je nach Position der Formel
Position der Formel A1 ; A2 ; A3 … Bezug A1 ; B1 ; C1 …

http://www.online-excel.de/excel/singsel.php?f=24

Gruß Holger

1 Like
#19

Danke.
Hallo Holger,

vielen Dank für Deine Hilfe und Geduld.

Unser Problem war das du mich zuerst in die Falsche Richtung
geführt hast.
Wenn die Spalte formatiert werden soll, dabei die Werte aber
aus einer Zeile komm**en, dann geht das nur mit Indirekt() und
zeile()

Wenn sich aber die Formel der bedingten Formatierung genau so
bewegen wie die Werte ist indirekt() überflüssig.

Funktion Indirekt()
INDIREKT(„Z1S1“;0)
= Zeile1 / Spalte1 = Bezug A1

INDIREKT(„Z1S“&Zeile();0)
= Zeile 1 / Spalte je nach Position der Formel
Position der Formel A1 ; A2 ; A3 … Bezug A1 ; B1 ; C1 …**

Japp, jetzt habe ich das doch noch verstanden :smile: Nochmals allerbesten Dank (ich verteile gleich noch ein paar Sternchen).

Dennoch bleibt das Rätsel, weshalb die einzelnen Terme in der mit INDIREKT formulierten BF funktioniert haben, nicht aber, wenn sie mit UND verknüpft waren (ein =, das hätte entfernt werden müssen, habe ich nicht gefunden). Aber das soll an dieser Stelle mal egal sein.

Viele Grüße
Hanno

#20

Dennoch bleibt das Rätsel, weshalb die einzelnen Terme in der
mit INDIREKT formulierten BF funktioniert haben, nicht aber,
wenn sie mit UND verknüpft waren (ein =, das hätte entfernt
werden müssen, habe ich nicht gefunden). Aber das soll an
dieser Stelle mal egal sein.

Also das was du da sagst halte ich für ein gerücht , lade mal ein beweis irgentwo hin.
Ich denke eher du hast einfach einen fehler in der formel .
Den so wie sie da steht funktioniert sie (siehe mein Beitrag).