Bedingte Formatierung in Excel?
Moin an alle hier im Forum - i need help.
Ich habe in der Firma eine Exceltabelle erstellt - diese rechnet mir aus Soll- und Ist Werten einen Faktor aus. Dieser Faktor soll nun farblich eingefärbt werden um auf einen Blick zu sehen was passt, was grenzwertig ist und was gar nicht geht.
In Zeile A1-A20 stehen meine Sollwerte (natürlich alles verschiedene). In B1-B20 steht die Abweichung welche diese haben dürfen (Stab-Wert) und in C1-C20 meine gemessenen Ist Werte. Nun soll wenn der Ist Wert innerhalb der Toleranzen welche in B angegeben ist die Zelle grün werden. Ist diese außerhalb einer Standardabweichung aber innerhalb vom dreifachen soll diese orange werden. Ist sie größer als das dreifache dann rot. Wichtig dabei ist das bei den Grenzen der letztmögliche Wert noch mitzählt. Als Beispiel: 0,5 Istwert - Toleranzen 0,01. Bei einem Wert von 0,47000 soll die Zelle noch orange sein ab 0,469999 dann rot. Ich habe es geschafft die bedingte Formatierung dafür zu erstellen - jedoch müsste ich das für jede Zelle neu machen und das bei über 4000 Werten ist etwas langwierig. Auf einem anderen Blatt habe ich für die Erleichterung eine Nebenrechnung angelegt. Diese rechnet automatisch den eingetragenen Sollwert +/- die Toleranzen aus. Die Bezüge der bedingten Formatierung laufen dann auf dieser Nebenrechnung.
Weiters ändern sich die Werte natürlich immer wieder. Also darf die bedingte Formatierung nicht auf die aktuellen Werte bezogen sein. Dies funktioniert mit der Variante über die Nebenrechnung aber einwandfrei.
Nun zur Frage. Wie kann ich diese Formatierung auf alle Zellen übertragen ohne bei jeder einzelnen die Bezüge neu anpassen zu müssen? Noch zur Info:
A1/B1/C1 gehören zusammen und das natürlich fortlaufend. Da jede Toleranz unterschiedlich ist, kann ich nicht alle Formatierungen auf eine Zelle zusammenfassen.
Vielen Dank für die Hilfe vorab.
MfG
4 Antworten
Dafür brauchst du (mindestens) zwei bedingte Formatierungen. Ich würde dir empfehlen, dass du erstmal alle Ist-Werte rot machst und sie dann mit den bedingten Formatierungen orange oder grün färbst. Dadurch fällt es auch eher auf, wenn die Formatierung irgendwo verloren gegangen ist.
Grüne Formatierung:
Erstmal markierst du alle Zellen, auf die die Formatierung angewendet werden soll, dann erstellst du eine bedingte Formatierung mit "Formel zur Ermittlung [...]". Die Formel ist diese:
=ABS(C1-A1)<=B1
und wählst halt bei "Formatieren..." aus, dass die Hintergrundfarbe grün werden soll.
Orangene Formatierung:
Das gleiche, aber mit dieser Formel:
=ABS(C1-A1)<=3*B1
Bei den Formeln für die bedingte Formatierung ist es wichtig zu wissen, dass du sie so eingeben musst, als würdest du sie für die erste Zelle eingeben, für die die Formatierung gilt und sie von da aus auf die anderen Zellen erweiterst. Also in meinem Beispiel, als wenn du sie für C1 eingibst und dann runterziehst.
Das rote ist einfach der Standard Hintergrund. Also du machst alle Zellen rot und die beiden bedingten Formatierungen sorgen dann für die anderen beiden Farben.
Waagerecht kannst du die Formatierungen auch übernehmen, wenn du mehrmals die Spalten in der Anordnung Soll-Toleranz-Ist hast. Die Formel in den bedingten Formatierungen gilt dann immer für die erste formatierten Zelle, also die oberste und linkeste.
Vielleicht kann dir das helfen:
Zur Info ich hatte leider gerade keinen PC in der nähe daher musste ich die App nehmen. Hier gibt es keine Bedingte Formatierung daher hab ich die Formeln symbolisch in die Zellen geschrieben.
Wenn du die dann als Bedingte Formatierung einträgt ist wichtig, dass die rote an oberster Stelle ist und grüne an unterster Stelle.
Würde mich über Rückmeldung freuen.
Natürlich kannst du auch die rote weglassen und DaCypher seine "Sparvariante" nutzen. Ich hab aber lieber alle Bedingungen drin. Am Ende ist es Geschmackssache.
Die Zeilen sind einer Spalte zugehörig.
Was passiert, wenn du die gesamte Spalte, in der die Werte drin sind, markiest und erst dann die bedingte Formatierung anlegst / anwendest?
Hey,
habe ich eben schon versucht. Ich habe die Formatierung auf Beispielsweise A1:A10 mit dem Bezug zu der Nebenrechnung - Zwischen C1:C10 und B1:B10 „rot“. Leider ohne Erfolg da sich die Formatierung immer nur auf eine Zelle bezieht. Zumindest rechnet er es nicht richtig, da er sich nicht auf den zugehörigen Wert in der Nebenrechnung bezieht.
Hast diu mit dem $-Zeichen gearbeitet um Bezugszeilen, die sich nicht verändern dürfen, festzusetzen?
Falls das auch nicht klappt, dann vesuche es mal mit dem Formatierung übertragen - Symbol
Also meinst du das ich mit den oben genannten Angaben, insofern ich diese unter $ setze immer auf die Zeile darunter komme. Heißt - A1 bezieht sich immer auf B und C1. A5 auf B und C5?
Ist aus der Ferne schwer zu beurteilen, ohne alle Exel-Tabellen gesehen zu haben.
Schwer zu verstehen was du ganz genau meinst, sorry.
Hast du keine Person in deinem Umfeld die du fragen kannst?
Mach die bedingte Formatierung in einer Formel, die die Daten aus der Nebenrechnung zieht. Vergiss nicht die $ wegzumachen und dann Formatierung übertragen
Problem dabei ist das jene Zelle die ich bedingt Formatiere, die Werte ebenfalls aus einer anderen Tabelle zieht. Das heißt in den Zellen ist schon =Tabelle1“A1“ hinterlegt. Somit kann ich dort keine zweite Formel anwenden.
Somit kann ich dort keine zweite Formel anwenden.
Du kannst so viele unterschiedliche Formatierungen anlegen, wie Du benötigst.
Vielen Dank für deine Antwort - ich glaube du kannst mir tatsächlich helfen um mein Disaster zu lösen. Das einzige was ich gerade nicht ableiten kann ist wo du die rote Formatierung gemacht hast. Und geht das nur senkrecht (bezüglich formel runterziehen) oder such waagerecht?