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