Fehlende Werte markieren

Formel

=ZÄHLENWENN(Bereich; Suchkriterium)=Häufigkeit

Überblick

Um fehlende Werte in einem Bereich zu markieren, benötigen wir eine bedingte Formatierung. Diese Formatierung basiert auf einer Formel, welche prüft, ob der Wert in der linken Spalte in der Abgleichspalte rechts fehlt. Wenn der Wert fehlt, die Funktion also = 0 bzw. WAHR ist, wird die bedingte Formatierung ausgelöst und die Zelle entsprechende eingefärbt.

Funktionsweise der Formel

Es muss für jede Zelle gezählt werden, wie häufig der Wert auch in der Abgleichspalte vorkommt.

In Zelle A7 wird also einmal durchgezählt wie oft der Begriff "Produkt 11" in der Abgleichspalte vorkommt.
In Zelle A8 wird das gleiche für den Begriff "Produkt 12" gemacht usw. 

Dafür können wir die ZÄHLENWENN-Funktion verwenden. 

=ZÄHLENWENN($C$7:$C$16; "Produkt 11") = 0 = WAHR
=ZÄHLENWENN($C$7:$C$16; "Produkt 12") = 0 = WAHR
=ZÄHLENWENN($C$7:$C$16; "Produkt 6") = 0 = FALSCH

usw.

Wenn das Ergebnis der Funktion, also wie oft der Wert vorkommt, wird genommen und mit 0 verglichen (Ergebnis=0). Wenn der Wert rechts nicht auftaucht, wird die Formel WAHR und die bedingte Formatierung ausgelöst.

Um die bedingte Formatierung korrekt anzulegen. Markieren wir A7, gehen dann im Menüband unter Start auf "Bedingte Formatierung" -> "Neue Regel" -> "Formel zur Ermittlung der zu formatierenden Zellen verwenden" und verwenden dann folgende Formel:

=ZÄHLENWENN($C$7:$C$16; A7) = 0

Anschließend muss noch festgelegt werden, wie die Zellen hervorgehoben werden sollen, einfach auf "Formatieren" klicken und dann z.B. eine Hintergrundfarbe auswählen.

Zum Abschluss muss diese Formel dann noch auf den gesamten Bereich $A$7:$A$16 angewendet werden.

Zu beachten

  • Bei der Erstellung der bedingten Formatierung müssen wir darauf achten, dass wir in der Formel die aktuell aktivierte Zelle verwenden. D.h. wenn wir uns in Zelle A7 befinden müssen wir auch A7 in der Formel angeben. Wenn wir dann die Formel auf den gesamten Bereich anwenden, wird immer die richtige aktuelle Zelle herangezogen.
    Wenn wir uns aber z.B. in Zelle A10 befinden und in der Formel A8 verwenden, hängt die Formatierung von dem Wert in A8 ab, also einer Zelle, die sich 2 Zeilen über der aktuellen Zelle befindet.