Generische Formel
=COUNTIF(list,A1)=0
Zusammenfassung
Um Listen zu vergleichen und Werte hervorzuheben, die in der einen, aber nicht in der anderen vorhanden sind, können Sie die bedingte Formatierung mit einer Formel anwenden, die auf der COUNTIF-Funktion basiert. Um beispielsweise Werte A1: A10 hervorzuheben, die nicht vorhanden sind C1: C10, wählen Sie A1: A10 aus und erstellen Sie eine bedingte Formatierungsregel basierend auf dieser Formel:
=COUNTIF($C$1:$C$10,A1)=0
Hinweis: Bei der bedingten Formatierung ist es wichtig, die Formel relativ zur "aktiven Zelle" in die Auswahl einzugeben, die in diesem Fall als A1 angenommen wird.
Erläuterung
Diese Formel wird für jede der 10 Zellen in A1: D10 ausgewertet. A1 ändert sich in die Adresse der auszuwertenden Zelle, während C1: C10 als absolute Adresse eingegeben wird, sodass sie sich überhaupt nicht ändert.
Der Schlüssel zu dieser Formel ist = 0 am Ende, wodurch die Logik der Formel "umgedreht" wird. Für jeden Wert in A1: A10 gibt COUNTIF zurück, wie oft der Wert in C1: C10 angezeigt wird. Solange der Wert in C1: C10 mindestens einmal vorkommt, gibt COUNTIF eine Zahl ungleich Null zurück und die Formel gibt FALSE zurück.
Wenn jedoch in C1: C10 kein Wert gefunden wird, gibt COUNTIF Null zurück. Da 0 = 0 ist, gibt die Formel TRUE zurück und die bedingte Formatierung wird angewendet.
Benannte Bereiche für einfache Syntax
Wenn Sie die gesuchte Liste (in diesem Fall C1: C10) mit einem benannten Bereich benennen, ist die Formel einfacher zu lesen und zu verstehen:
=COUNTIF(list,A1)=0
Dies funktioniert, da benannte Bereiche automatisch absolut sind.
Groß- und Kleinschreibung beachten
Wenn Sie eine Groß- und Kleinschreibung benötigen, können Sie eine Formel wie die folgende verwenden:
=SUMPRODUCT((--EXACT(A1,list)))=0
Die EXACT-Funktion führt eine Groß- und Kleinschreibung durch und SUMPRODUCT bewertet das Ergebnis. Wie beim COUNTIF wird diese Formel zurückgegeben, wenn das Ergebnis Null ist. Da bei dem Test zwischen Groß- und Kleinschreibung unterschieden wird, wird "Apfel" auch dann als fehlend angezeigt, wenn "Apfel" oder "APFEL" in der zweiten Liste angezeigt wird. Auf dieser Seite finden Sie eine ausführlichere Erklärung.
