In diesem Tutorial wird gezeigt, wie Sie Formeln testen, bevor Sie sie auf die bedingte Formatierung in Excel anwenden.
Testen von benutzerdefinierten Formeln - Excel
Wenn Sie eine benutzerdefinierte Formel auf die bedingte Formatierung in Excel anwenden, müssen Sie sicherstellen, dass die Formel tatsächlich den richtigen TRUE- oder FALSE-Wert in Ihrem Arbeitsblatt zurückgibt, damit die bedingte Formatierung ordnungsgemäß funktioniert.
Vor dem Erstellen der bedingten Formatierungsregel können wir die Formel an einem „freien Platz“ in unserer Excel-Arbeitsmappe erstellen und sicherstellen, dass die Formel richtig funktioniert.
Testen einer Formel zum Hervorheben einer Arbeitsblattzelle
Im obigen Beispiel möchten wir eine Formel schreiben, um zu sehen, ob jede Zelle im Arbeitsblatt größer als 5 ist, und wenn sie größer als 5 ist, möchten wir den Hintergrund der Zelle einfärben.
Wir können mit der ersten Zelle im Bereich beginnen - in diesem Fall B3, und dann können wir jede Zeile und Spalte des Bereichs testen - bis hinunter zu E11.
Ausgehend von einer einfachen Formel können wir sehen, dass B3 einen Wert von 5 hat und daher NICHT größer als 5 ist.
Wir können dann den Ziehpunkt verwenden, um diese Formel nach unten in Zeile 11 zu ziehen. Beachten Sie, dass sich der Zeilenteil der Zelladresse (dh 3) zur nächsten Zahl ändert, wenn wir die Formel nach unten durch die Zeilen ziehen, sodass B3 zu B4 wird, dann B5 und so weiter.
Wir können dann den markierten Zellbereich über 4 Spalten ziehen, um zu testen, ob die Zellen in den Spalten C bis E einen Wert größer als 5 haben. Wenn wir die Formel ziehen, ändert sich der Spaltenteil der Zelladresse entsprechend - von Spalte C bis D bis E bis F.
Wie wir jetzt sehen können, erhalten wir je nach Wert in der entsprechenden Zelle den richtigen TRUE- oder FALSE-Wert.
Wir wissen jetzt, dass unsere Formel korrekt ist, und können diese Formel verwenden, um die bedingte Formatierungsregel zu erstellen. Wie bei unserer Testformel verwenden wir die erste Zelle im Bereich B3.
=B3>5
Sobald wir Typen in der Formel haben, können wir das Format für die Hintergrundfarbe festlegen und auf OK klicken.
Wie Sie sehen, gilt die Formel =B3>5 für den Bereich B3:E11. Die Formelzelle muss immer der ersten Zelle des zu formatierenden Bereichs entsprechen.
Klicken Anwenden um die Formatierung auf Ihr Arbeitsblatt anzuwenden.
Testen einer Formel, um eine Arbeitsblattzeile hervorzuheben
Das Anwenden einer Formel zum Ändern der Hintergrundfarbe einer Zeile anstelle einer Spalte ist etwas anders und etwas komplizierter.
Betrachten Sie das folgende Arbeitsblatt.
In diesem Arbeitsblatt möchten wir eine ganze Zeile hervorheben, wenn das Projekt überfällig ist – also wenn Spalte E eine Zelle enthält, die den Wert „Überfällig" Anstatt von "Pünktlich“, möchten wir dann die gesamte Zeile hervorheben, in der sich diese Zelle befindet.
Die Formel dafür ist einfach:
=IF(E4=”Überfällig”, WAHR, FALSCH)
Wenn wir dies jedoch auf unsere bedingte Formatierung anwenden, wird dies zurückgegeben.
Nur die erste Spalte im Bereich wird formatiert.
Versuchen wir diese Formel in unserem Excel-Arbeitsblatt.
Wir erhalten das gewünschte Ergebnis, TRUE, wenn das Projekt in Spalte E überfällig ist. Wenn wir diese Formel jedoch für die 5 Spalten im Bereich kopieren würden (zB: Spalte B bis Spalte E), wäre das zurückgegebene Ergebnis FALSE .
Die Formel würde sich ändern – also würde Spalte E in Spalte F geändert – und natürlich ist in Spalte F nichts, sodass die Formel jedes Mal FALSE zurückgeben würde.
Wir müssen sicherstellen, dass die Formel NUR Spalte E betrachtet - aber das sieht die richtige Zeile aus - wenn wir die Formel also hinüberkopieren, bleibt Spalte E gleich. Dazu müssen wir eine gemischte Referenz verwenden, die die Spalte festhält.
=$E4="Überfällig"
Wenn wir es jetzt über 4 Spalten kopieren, bleibt die Spalte in der Formel gleich, aber die Zeile ändert sich.
Da unsere Formel jetzt im Arbeitsblatt funktioniert, können wir unsere benutzerdefinierte Formel in bedingter Formatierung erstellen.
Wenn wir klicken OK, und Anwenden, werden unsere Zeilen, in denen Spalte E überfällig steht, hervorgehoben.
