Excel bedingte Formatierung allgemeiner Zellenbezug?
Hallo Leute.
Ist wahrscheinlich eine recht nischige Frage aber hoffentlich kann mir hier trotzdem jemand damit helfen. Folgendes möchte ich tun:
Ich möchte in einem Excel-Dokument jeweils pro Zeile (8 Zellen pro Zeile, Werte zwischen 1 und 5), dann die Zelle gelb markieren, wenn der Wert dieser Zelle mehr als 1 von einer anderen Zelle in der Zeile abweicht. D.h. wenn die Werte so aus sehen: 3,3,2,3,3,3,4,4, dann sollten die 2er und 4er gelb markiert werden, die 3er sollten weiß bleiben (da sie von keinem anderen Wert mehr als 1 abweichen).
Bezogen auf eine Zelle habe ich das mit der Formel ODER(ABS(A1-B1)>1;ABS(A1-C1)>1...) hinbekommen, dass funktioniert aber logischerweise nur in Zelle A1, für B1 müsste es dann wieder neu angepasst werden usw. Einfach die Formatierung kopieren oder verschieben funktioniert auch nicht. Daher wollte ich fragen: Gibt es die möglich eine Formel zu erstellen die anstatt auf die jeweilige konkrete Zelle (A1, B1 usw.) Bezug zu nehmen, einfach immer auf die Zelle Bezug nimmt, in welcher die Formatierung gerade angewendet wird? Ist halbwegs verständlich was ich damit meine?
Es sind mehrere hundert Zellen, d.h. jede Formel für jede Zelle einzeln anzupassen ist keine Option....
Schon mal Danke
1 Antwort
Ich würde mit MIN und MAX arbeiten. Ich mache mal die Annahme 3,3,2,3,3,3,4,4 stehen in den Zellen A1 bis H1.
Dann würde ich in der Formel für die bedingte Formatierung etwa
OR(A1-MIN($A1:$H1)>1;MAX($A1:$H1)-A1>1)
Ich habe das jetzt nicht getestet, aber diese Formel muss nicht angepasst werden, wenn Du in der bedingten Formatierung den Range wirklich auch bei A1 beginnen lässt und bis - was weiß ich - H10000 laufen lässt. Es ist ganz wichtig, für das Funktionieren einer Formeln in der bedingten Formatierung, dass die erste Zelle des Ranges auch in der Formel die zu berechnende Anhängigkeit relativ adressiert enthält (in meinem Beispiel A1). In den übrigen Zeilen muss man in Bezügen in aller Regel relative und absolute Adressierung mischen. In meinem Beispiel: $A1:$H1. Das sorgt dafür, dass hier für alle Zellen einer Reihe stets der gleiche Bereich zum Vergleich herangezogen wird. In der nächsten Zeile ist der Vergleichsbereich aber automatisch $A2:$H2. Das macht der Algorithmus der bedingten Formatierung von selbst so, während er den Bereich "abarbeitet", für den eine bedingte Formatierung definiert wurde.
Mit dem $-Zeichen fixierst du in diesem Fall nur die Spalten. Die Zeilen werden trotzdem weitergezählt. (Damit die Zeilen fixiert werden, müsste vor den Zahlen auch ein $ stehen, also z.B. $A$1).
Wenn du die bedingte Formatierung auf andere Zeilen übertragen willst, ist es am Besten, wenn du die Regel bearbeitest und dort bei "wird angewendet auf" den ganzen Bereich einträgst, auf den sich die Formel bezieht. Also z.B. =A1:H100
Bei mir funktioniert die Formel problemlos. Du musst halt noch ein Gleich-Zeichen vorne dran machen und statt "OR" die deutsche Bezeichnung "ODER" benutzen.
=ODER(A1-MIN($A1:$H1)>1;MAX($A1:$H1)-A1>1)
Ich bin davon ausgegangen, dass Du in Zeile 2 dann eben neue Zahlen in den Zellen A2:H2 hast, in Zeile 3 wieder neue Zahlen usw. usf., und daher diese Zahlen entsprechend einfärben willst. Und dann muss da $A1:$H1 stehen, damit die Zeilennummer auch angepasst wird. Alles andere macht für mein Verständnis der Frage - das falsch sein kann - keinen Sinn.
Super vielen Dank schonmal, das funktioniert soweit gut, zumindest innerhalb der Zeilen. Damit hast du mir schon einmal sehr geholfen. Nur das mit dem automatischen Anpassung pro Zeile funktioniert bei mir irgendwie nicht. Der Bereich ist ja wie du geschrieben hast mit dem $-Zeichen "Fixiert", d.h. wenn ich die Formel in die 2. Zeile verschiebe ändern sich die freien A1 zu A2, aber der Bereich für Min und Max bleibt bei A1:H1... Hab ich dich hier falsch verstanden?