Bedingte Formatierung mit veränderlichen Bezugszellen

[bjoernj]

Hallo liebes Excel-Forum,

ich habe einige Zeit Google bemüht, um eine elegante Lösung für mein Problem zu finden. Das war leider nicht von Erfolg gekrönt also wende ich mich nun an euch.

Zunächst erstmal mein tolles Excel-Sheet (anklicken zum vergrößern):
[url=
Dateiupload bitte im Forum! So geht es: Klick mich!
]

Ich möchte euch nun mein Anliegen anhand der Zellen D21 bis 23 und Q12 erläutern.
D21 ist der Soll-Wert
D22 und D23 sind Messwerte.
Q12 ist die maximal erlaubte Abweichung in %

D22 und D23 sollen nun grün eingefärbt werden falls die abweichung im Bereich +/- 1% liegt, also der Wert zwischen 49,5 und 50,5.
Liegt der Wert nicht dazwischen wird rot eingefärbt.
Überhaupt kein Problem mit den Regeln "zwischen" und "nicht zwischen" bei den bedingten Formatierungen.

Das Problem liegt nun darin, dass ich keine Lust habe für derzeit 10 Spalten pro Maschinenkopf (werden noch mehr) jeweils Regeln zu erstellen die sich nur durch den geänderten Bezug (Sollwert-Zelle) unterscheiden. Ist mir erstens zu viel Aufwand und zweitens wird das Sheet von vielen Nutzern benutzt und 40+ Regeln im "Regel verwalten..."-Fenster werden schnell unübersichtlich.

Meine Idee:
Eine Regel die sich auf alle Messwert-Zellen anwenden lässt und quasi einen dynamischen Bezug auf die Zelle "eins höher" oder "zwei höher" beinhaltet. D21 soll also grün eingefärbt werden, wenn sie nicht mehr als 1% von der Zelle "eins höher" abweicht usw.

Jemand eine Idee wie sich das ausdrücken lässt? Ich spreche Excel noch nicht so fließend

MfG,
Björn

[WillWissen]

Hi Björn,

Das Problem liegt nun darin, dass ich keine Lust habe für derzeit 10 Spalten pro Maschinenkopf (werden noch mehr) jeweils Regeln zu erstellen die sich nur durch den geänderten Bezug (Sollwert-Zelle) unterscheiden. Ist mir erstens zu viel Aufwand und zweitens wird das Sheet von vielen Nutzern benutzt und 40+ Regeln im "Regel verwalten..."-Fenster werden schnell unübersichtlich.

die meisten Helfer keine Tabelle nach einem Bildchen nachbauen wollen.

Meine Idee:

Eine Regel die sich auf alle Messwert-Zellen anwenden lässt und quasi einen dynamischen Bezug auf die Zelle "eins höher" oder "zwei höher" beinhaltet. D21 soll also grün eingefärbt werden, wenn sie nicht mehr als 1% von der Zelle "eins höher" abweicht usw.

Du stellst uns deine Tabelle vor und anonymisierst die auf deinem Bildchen geschwärzten sensiblen Daten.

Dazu lies dir bitte diesen Beitrag durch: http://www.clever-excel-forum.de/thread-326.html

[bjoernj]

Hallo,

oh das wusste ich nicht. Dachte das reicht um das Problem zu erläutern.
Als Attachment nun also die hoffentlich anonymisierte Tabelle. Ist zwar sowieso nichts wildes, aber man nimmt "Confidential" hier ziemlich ernst.

[Winny]

Hi

ersetze deine Bedingte Formatierung hierdurch:

zum Markieren der Werte innerhalb der Toleranz: 

Code:

=UND(D22>=D$21*(1-$Q$12);D22<=D$21*(1+$Q$12))

außerhalb der Toleranz: 

Code:

=ODER(D22<D$21*(1-$Q$12);D22>D$21*(1+$Q$12))

[bjoernj]

Hallo Winny,

Wenn ich jetzt per Format übertragen auf die anderen Spalten übertrage, funktioniert es wunderbar.
Vielen Dank!
Wenn noch eine Minute Zeit übrig ist, könntest du mir erklären was genau die Formel jetzt ausmacht, dass der Bezug übernommen wird? Die $-Zeichen Geschichte?

MfG,
Björn

[Winny]

Deine Formel habe ich mir nicht angesehen, die steht auch innerhalb Anführungszeichen und kann alleine deshalb nicht funktionieren. Die solltest Du eigentlich ersetzen da sie bereits die richtigen Bezüge hatte, dann ist auch die Übertragung mit Pinsel unnötig.