Обща формула
=COUNTIF(list,A1)=0
Обобщение
За да сравните списъци и да подчертаете стойности, които съществуват в единия, но не и в другия, можете да приложите условно форматиране с формула, базирана на функцията COUNTIF. Например, за да подчертаете стойности A1: A10, които не съществуват C1: C10, изберете A1: A10 и създайте правило за условно форматиране въз основа на тази формула:
=COUNTIF($C$1:$C$10,A1)=0
Забележка: при условно форматиране е важно да въведете формулата спрямо "активната клетка" в селекцията, която в този случай се приема за A1.
Обяснение
Тази формула се оценява за всяка от 10-те клетки в A1: D10. A1 ще се промени на адреса на оценяваната клетка, докато C1: C10 се въвежда като абсолютен адрес, така че изобщо няма да се промени.
Ключът към тази формула е = 0 в края, което „обръща“ логиката на формулата. За всяка стойност в A1: A10, COUNTIF връща броя показвания на стойността в C1: C10. Докато стойността се появи поне веднъж в C1: C10, COUNTIF ще върне ненулево число и формулата ще върне FALSE.
Но когато стойност не е намерена в C1: C10, COUNTIF връща нула и тъй като 0 = 0, формулата ще върне TRUE и ще се приложи условното форматиране.
Именувани диапазони за прост синтаксис
Ако наименувате списъка, който търсите (C1: C10 в този случай) с наименуван диапазон, формулата е по-лесна за четене и разбиране:
=COUNTIF(list,A1)=0
Това работи, тъй като наименуваните диапазони са автоматично абсолютни.
Версия, чувствителна към малки и големи букви
Ако имате нужда от преброяване на регистрите, можете да използвате формула като тази:
=SUMPRODUCT((--EXACT(A1,list)))=0
Функцията EXACT извършва оценка на регистъра и SUMPRODUCT изчислява резултата. Както при COUNTIF, тази формула ще се върне, когато резултатът е нула. Тъй като тестът има регистър на регистъра, „apple“ ще се покаже като липсващ, дори ако във втория списък се появи „Apple“ или „APPLE“. Вижте тази страница за по-подробно обяснение.
