Funktionieren Bedingte Formatierungsformeln Nicht? – Excel und Google Sheets
In diesem Lehrgang wird gezeigt, wie Sie Formeln testen können, bevor Sie diese auf die bedingte Formatierung in Excel anwenden.
Benutzerdefinierte Formeln testen
Wenn Sie eine benutzerdefinierte Formel auf die bedingte Formatierung in Excel anwenden, müssen Sie sicherstellen, dass die Formel in Ihrem Arbeitsblatt tatsächlich den richtigen WAHR- oder FALSCH-Wert zurückgibt, damit die bedingte Formatierung korrekt funktioniert. Bevor Sie die Regel für die bedingte Formatierung erstellen, können Sie die Formel an einer freien Stelle in der Arbeitsmappe erstellen und sicherstellen, dass die Formel korrekt funktioniert.
Testformel zum Hervorheben einer Zelle
Im obigen Beispiel möchten Sie eine Formel schreiben, um festzustellen, ob jede Zelle im Arbeitsblatt größer als 5 ist, und wenn sie größer als 5 ist, möchten Sie den Hintergrund der Zelle einfärben.
- Beginnen Sie mit der ersten Zelle des Bereichs – in diesem Fall B3 – und testen Sie dann jede Zeile und Spalte des Bereichs – bis hinunter zu E11. Anhand einer einfachen Formel können Sie feststellen, dass B3 den Wert 5 hat und somit NICHT größer als 5 ist.
- Ziehen Sie diese Formel dann mit dem Griff nach unten in die Zeile 11. Beachten Sie dabei, dass sich der Zeilenteil der Zelladresse (z. B. 3) in die nächste Zahl ändert, wenn Sie die Formel durch die Zeilen nach unten ziehen, so dass B3 zu B4 wird, dann zu B5 und so weiter.
- Ziehen Sie dann den markierten Bereich von Zellen über vier Spalten, um zu prüfen, ob die Zellen in den Spalten C bis E einen Wert größer als 5 haben. Beim Ziehen der Formel ändert sich der Spaltenteil der Zelladresse entsprechend – von Spalte C nach D nach E nach F.
-
- Wie Sie jetzt sehen können, erhalten Sie den richtigen WAHR- oder FALSCH-Wert, je nach dem Wert in der entsprechenden Zelle. Dies bestätigt, dass die Formel korrekt ist, und Sie können diese Formel verwenden, um die Regel für die bedingte Formatierung zu erstellen. Wie bei der Testformel verwenden Sie die erste Zelle des Bereichs, B3.
=B3>5
- Sobald Sie die Formel eingegeben haben, können Sie das Format für die Hintergrundfarbe festlegen und auf OK klicken.
Wie Sie sehen können, gilt die Formel =B3>5
für den Bereich B3:E11. Die Formelzelle muss immer mit der ersten Zelle des zu formatierenden Bereichs übereinstimmen.
- Klicken Sie auf Übernehmen, um die Formatierung auf Ihr Arbeitsblatt anzuwenden.
Testformel zum Hervorheben einer Zeile
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 Sie eine ganze Zeile hervorheben, wenn das Projekt überfällig ist – wenn also Spalte E eine Zelle hat, die den Wert überfällig anstelle von pünktlich liefert, möchten Sie die gesamte Zeile hervorheben, in der sich diese Zelle befindet. Die Formel (unter Verwendung der WENN-Funktion) dafür ist einfach:
=WENN(E4="Überfällig", WAHR, FALSCH)Wenn Sie dies jedoch auf die bedingte Formatierung anwenden, erhalten Sie folgendes Ergebnis.
Nur die erste Spalte des Bereichs wird formatiert. Probieren Sie nun diese Formel in Ihrem Arbeitsblatt aus.
Sie erhalten das gewünschte Ergebnis: WAHR, wenn das Projekt in Spalte E überfällig ist. Wenn Sie diese Formel jedoch für die fünf Spalten im Bereich (Spalte B bis Spalte E) kopieren würden, wäre das Ergebnis FALSCH.
Die Formel würde sich ändern, d. h. Spalte E würde zu Spalte F – und natürlich steht in Spalte F nichts, so dass die Formel jedes Mal ein FALSCH zurückgeben würde. Stellen Sie sicher, dass die Formel NUR die Spalte E betrachtet – aber in der richtigen Zeile – so dass beim Kopieren der Formel die Spalte E gleich bleibt. Verwenden Sie dazu einen gemischten Verweis, der die Spalte festhält.
=$E4="überfällig"
Wenn Sie sie nun über vier Spalten kopieren, bleibt die Spalte in der Formel gleich, aber die Zeile ändert sich.
Da die Formel nun im Arbeitsblatt funktioniert, können Sie eine benutzerdefinierte Formel in der bedingten Formatierung erstellen.
Wenn Sie auf OK und dann auf Übernehmen klicken, werden die Zeilen mit der Spalte E überfällig hervorgehoben.
