Загальна формула
=COUNTIF(list,A1)=0
Резюме
Щоб порівняти списки та виділити значення, які існують в одному, але не в іншому, ви можете застосувати умовне форматування за допомогою формули на основі функції COUNTIF. Наприклад, щоб виділити значення A1: A10, які не існують C1: C10, виберіть A1: A10 і створіть умовне правило форматування на основі цієї формули:
=COUNTIF($C$1:$C$10,A1)=0
Примітка: при умовному форматуванні важливо ввести формулу щодо "активної комірки" у виділення, яке в цьому випадку вважається A1.
Пояснення
Ця формула обчислюється для кожної з 10 клітинок A1: D10. А1 зміниться на адресу комірки, що перевіряється, тоді як С1: С10 введено як абсолютну адресу, тому вона взагалі не зміниться.
Ключем до цієї формули є = 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". Дивіться цю сторінку для більш детального пояснення.
