relative Bezüge für bedingte Formatierung

Bild

Betrifft: relative Bezüge für bedingte Formatierung
von: valentin
Geschrieben am: 07.07.2015 17:29:23

Nachdem ich im Netz lange erfolglos nach einer Lösung für dieses Problem gesucht hatte, ist mir selber eine eingefallen. Ich poste sie hier für alle Geplagten.
Problem: Seit excel 2007 werden relative Bezüge in bedingten Formatierungen ignoriert, jedenfalls dann, wenn man eine Formel benutzt.
Beispiel: ich möchte erreichen, dass in meinem Sheet alle Zellen rot hinterlegt werden, deren Inhalt gleich der 3.Potenz von Zelle A1 ist. Ich beginne dazu mit Zelle B2 und verwende die bedingte Formatierungsformel
=(b2=$a$1^3)
Jetzt will ich den Effekt z.B. auf Zelle C3 übertragen. In Excel 2003 hätte ich dazu einfach die Formatierung von B2 nach C3 kopieren können, und die Formatierungsformel hätte sich dabei zu =(c3=$a$1^3) gewandelt. Nicht so ab Excel 2007! Nach dem Kopieren lautet die Formel auch für Zelle C2 vielmehr weiterhin =(b2=$a$1^3). Der relative Bezug (b2) wird wie ein absoluter ($b$2) behandelt.
Abhilfe schaffen einmal mehr die Lieblingsformeln des Matrixlers. Die Zelle, auf welche die bedingte Formatierung angewendet wird, adressiere man mit dem Term:
INDIREKT(ADRESSE(ZEILE();SPALTE()))
Die obenstehende Formel lautet dann also:
=(INDIREKT(ADRESSE(ZEILE();SPALTE()))=$a$^3)
Eine bedingte Formatierung mit dieser Formel kann man überall hinkopieren, und sie verwendet immer den Wert der jeweiligen Zelle. Will man einen andere Zelle relativ adressieren, dann kann man das durch Addieren der Koordinatenabstände zu Zeile() und Spalte() erreichen.

Bild

Betrifft: Finde ich prinzipiell gut...
von: Michael (migre)
Geschrieben am: 07.07.2015 17:51:28
Hallo Valentin,
... wenn Workarounds oder eigene Lösungen für alle angeboten werden; dafür schon mal Danke.
Aber mir hat sich nicht ganz erschlossen, warum Du den "Umweg" über Indirekt & Co nehmen musst:
=$B2=$A$1^3
lässt sich als bedingtes Format problemlos auf andere Zellen übertragen (bei mir in Excel 2010). Aber, wie immer, ist nicht ausgeschlossen, dass ich gerade auf dem Schlauch stehe ;-)...
LG
Michael

Bild

Betrifft: AW: Finde ich prinzipiell gut...
von: Daniel
Geschrieben am: 07.07.2015 18:09:13
Hi
mir wäre jetzt auch nicht bekannt, dass es da bei solchen kopieraktionen Probleme mit den relativen Zellbezügen gibt.
Wenn du sicher gehen willst, dann wechsle zum Bearbeiten der bedingten Formate auf die Z1S1-Bezugsart.
In der Z1S1-Bezugsart lassen sich die relativen Zellbezüge ach relativ beschreiben und damit sollte es dann auf jeden Fall funktionieren.
Gruß Daniel

Bild

Betrifft: Das ist doch Nonsense, ...
von: Luc:-?
Geschrieben am: 07.07.2015 19:23:53
…Valentin,
der HauptUnterschied in dieser Frage zwischen der alten und der neuen Bedingt­Formatierung ist doch nur, dass die Fmln früher direkt sichtbar an den Standort der BedFmtFml, jetzt nur noch intern angepasst wdn*. Man sieht die Anpassung also nicht. Sie richtet sich aber nach dem angegebenen Geltungs­bereich und berechnet relative Adressen neu in Abhängigkeit vom Standort der jeweiligen Zelle innerhalb des Geltungs­bereichs.
Dein Problem kann also nur auftreten, wenn dein Geltungs­bereich immer nur eine Zelle umfasst und du die Fml direkt einträgst. Dabei hätten sich Relativ­Adressen aber auch früher nicht angepasst! Kopierst und überträgst du das Format oder auch die ganze Zelle, wdn Relativ­Adressen sogar direkt sichtbar angepasst.
Es ist also nicht nachvoll­ziehbar, warum du meinst, so etwas zu benötigen!
Außerdem würde in deinem BspFall auch BedFmtTyp1, Zellwert(-Vgl), reichen, also gleich - =$A$1^3. Dann würdest du das nicht mal merken!
* Beides hat Vor- und Nachteile!
Gruß, Luc :-?

Besser informiert mit …

Bild

Betrifft: dazu ... und zu "Lieblingsformeln" ...
von: der neopa C
Geschrieben am: 08.07.2015 16:33:19
Hallo Valentin,
... wenn dem wirklich so wäre, wie Du schreibst, dann läge in Excel2007 ein Fehler vor. Doch glauben kann ich das nicht. Du unterliegst mE wahrscheinlich einer "Täuschung", wie vor Dir auch schon andere.
Luc hat Dir das ja auch schon nahe gelegt.
Vielleicht ergänzend noch: Die bedingten Formatierungsdefinitionen sind ab Excel 2007 immer im "Zusammenspiel" Formel und Bereichsdefinition zu sehen, wenn die so formatierten Zellen benachbart sind.
Mehr zu bedingten Formatierungen ab 2007 findest Du u.a. hier: http://www.online-excel.de/excel/singsel.php?f=74 und ff.
Jedenfalls liegt in Excel 2010 ein solcher "Fehler" nicht vor, wie Dir ja auch schon Michael bestätigte.
Mein eigentliche Anlass dessen, dass ich mich in diesen thread auch noch einklinke, ist jedoch Deine weitere Aussage: "...die Lieblingsformeln des Matrixlers.", womit Du offensichtlich den Einsatz von INDIREKT() meinst. Das kann so nicht unkommentiert stehen lassen.
Es gibt sicher (MATRIX-)Formeln, wo man auf INDIREKT() angewiesen ist, doch in der Mehrzahl der Formeln, wo diese Funktion (leider noch) immer eingesetzt werden (egal in welcher Excelversion), kann man die volatile Funktion INDIREKT() günstiger durch INDEX() ersetzen. Dies auch z.B. in dem von Dir aufgezeigten Formeln, wobei man allerdings wie bereits geschrieben, hier weder sie eine und erst Recht nicht der anderen Funktion benötigt. Wie auch immer, es kann Dir mE nur zum Nutzen sein, Dich noch etwas mehr mit INDEX() auseinanderzusetzen. Vielleicht änderst Du danach sogar Deine derzeitige Meinung ;-)
Gruß Werner
.. , - ...
oT: @Michael, für Deine weiteren Bemühungen um meinen thread, schreibe ich Dir gleich hier: Danke!


 Bild

Beiträge aus den Excel-Beispielen zum Thema "relative Bezüge für bedingte Formatierung"