Общая формула
=COUNTIF(list,A1)=0
Резюме
Чтобы сравнить списки и выделить значения, которые существуют в одном, но не в другом, вы можете применить условное форматирование с помощью формулы, основанной на функции СЧЁТЕСЛИ. Например, чтобы выделить значения A1: A10, которые не существуют C1: C10, выберите A1: A10 и создайте правило условного форматирования на основе этой формулы:
=COUNTIF($C$1:$C$10,A1)=0
Примечание. При условном форматировании важно вводить формулу относительно «активной ячейки» в выделенном фрагменте, которым в данном случае считается A1.
Объяснение
Эта формула вычисляется для каждой из 10 ячеек в A1: D10. A1 изменится на адрес оцениваемой ячейки, а C1: C10 вводится как абсолютный адрес, поэтому он не изменится вообще.
Ключом к этой формуле является = 0 в конце, который «переворачивает» логику формулы. Для каждого значения в A1: A10 функция СЧЁТЕСЛИ возвращает, сколько раз значение появляется в C1: C10. Пока значение появляется хотя бы один раз в C1: C10, СЧЁТЕСЛИ вернет ненулевое число, а формула вернет ЛОЖЬ.
Но когда значение не найдено в C1: C10, СЧЁТЕСЛИ возвращает ноль, и, поскольку 0 = 0, формула вернет ИСТИНА и будет применено условное форматирование.
Именованные диапазоны для простого синтаксиса
Если вы дадите имя искомому списку (в данном случае C1: C10) именованным диапазоном, формулу будет проще читать и понимать:
=COUNTIF(list,A1)=0
Это работает, потому что именованные диапазоны автоматически являются абсолютными.
Версия с учетом регистра
Если вам нужен счетчик с учетом регистра, вы можете использовать такую формулу:
=SUMPRODUCT((--EXACT(A1,list)))=0
Функция EXACT выполняет оценку с учетом регистра, а функция SUMPRODUCT подсчитывает результат. Как и в случае с СЧЁТЕСЛИ, эта формула вернется, когда результат будет нулевым. Поскольку в тесте учитывается регистр, «яблоко» будет отображаться как отсутствующее, даже если «Яблоко» или «ЯБЛОКО» появятся во втором списке. См. Эту страницу для более подробного объяснения.
