Formuła ogólna
=COUNTIF(list,A1)=0
Podsumowanie
Aby porównać listy i zaznaczyć wartości, które istnieją w jednej, ale nie w drugiej, możesz zastosować formatowanie warunkowe za pomocą formuły opartej na funkcji LICZ.JEŻELI. Na przykład, aby wyróżnić wartości A1: A10, które nie istnieją C1: C10, wybierz A1: A10 i utwórz regułę formatowania warunkowego na podstawie tej formuły:
=COUNTIF($C$1:$C$10,A1)=0
Uwaga: w przypadku formatowania warunkowego ważne jest, aby wprowadzić formułę względem „aktywnej komórki” w zaznaczeniu, która w tym przypadku jest równa A1.
Wyjaśnienie
Ta formuła jest obliczana dla każdej z 10 komórek w A1: D10. A1 zmieni się na adres ocenianej komórki, podczas gdy C1: C10 zostanie wprowadzony jako adres bezwzględny, więc w ogóle się nie zmieni.
Kluczem do tej formuły jest = 0 na końcu, co „odwraca” logikę formuły. Dla każdej wartości w A1: A10 funkcja LICZ.JEŻELI zwraca, ile razy wartość pojawia się w C1: C10. Tak długo, jak wartość pojawia się co najmniej raz w C1: C10, LICZ.JEŻELI zwróci liczbę niezerową, a formuła zwróci FALSE.
Ale jeśli wartość nie zostanie znaleziona w C1: C10, LICZ.JEŻELI zwraca zero, a ponieważ 0 = 0, formuła zwróci wartość PRAWDA i zostanie zastosowane formatowanie warunkowe.
Nazwane zakresy dla prostej składni
Jeśli nazwiesz wyszukiwaną listę (w tym przypadku C1: C10) nazwanym zakresem, formuła będzie prostsza do odczytania i zrozumienia:
=COUNTIF(list,A1)=0
Działa to, ponieważ nazwane zakresy są automatycznie bezwzględne.
Wersja z rozróżnianiem wielkości liter
Jeśli potrzebujesz zliczania wielkości liter, możesz użyć następującego wzoru:
=SUMPRODUCT((--EXACT(A1,list)))=0
Funkcja PORÓWNAJ wykonuje ocenę z uwzględnieniem wielkości liter, a SUMA PROCEDURA potwierdza wynik. Podobnie jak w przypadku funkcji LICZ.JEŻELI, ta formuła zwróci wynik, gdy wynik będzie równy zero. Ponieważ w teście rozróżniana jest wielkość liter, „apple” będzie wyświetlane jako brakujące, nawet jeśli na drugiej liście pojawi się „Apple” lub „APPLE”. Zobacz tę stronę, aby uzyskać bardziej szczegółowe wyjaśnienie.
