Общая формула
=IF(COUNTIF(list,value),"OK","Missing")
Резюме
Чтобы определить значения в одном списке, которые отсутствуют в другом списке, можно использовать простую формулу, основанную на функции СЧЁТЕСЛИ с функцией ЕСЛИ. В показанном примере формула в G6:
=IF(COUNTIF(list,F6),"OK","Missing")
где «список» - именованный диапазон B6: B11.
Объяснение
Функция СЧЁТЕСЛИ подсчитывает ячейки, соответствующие критериям, и возвращает количество найденных вхождений. Если ни одна ячейка не соответствует критериям, СЧЁТЕСЛИ возвращает ноль. Вы можете использовать поведение непосредственно внутри оператора IF, чтобы отмечать значения с нулевым счетчиком (т. Е. Значения, которые отсутствуют). В показанном примере формула в G6:
=IF(COUNTIF(list,F6),"OK","Missing")
где «список» - это именованный диапазон, соответствующий диапазону B6: B11.
Функция ЕСЛИ требует, чтобы логическая проверка вернула ИСТИНА или ЛОЖЬ. В этом случае функция СЧЁТЕСЛИ выполняет логический тест. Если значение найдено в списке , СЧЁТЕСЛИ возвращает число непосредственно в функцию ЕСЛИ. Результатом может быть любое число… 1, 2, 3 и т. Д.
Функция ЕСЛИ оценит любое число как ИСТИНА, в результате чего ЕСЛИ вернет «ОК». Если значение не найдено в списке , COUNTIF возвращает ноль (0), который оценивается как FALSE, а IF возвращает «Missing».
Альтернатива с MATCH
Вы также можете проверить отсутствующие значения с помощью функции ПОИСКПОЗ. ПОИСКПОЗ находит позицию элемента в списке и возвращает ошибку # Н / Д, если значение не найдено. Это поведение можно использовать для построения формулы, возвращающей «Отсутствует» или «ОК», проверив результат ПОИСКПОЗ с функцией ISNA. ISNA возвращает TRUE только при получении ошибки # N / A.
Чтобы использовать ПОИСКПОЗ, как показано в приведенном выше примере, формула:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Обратите внимание, что MATCH должен быть настроен для точного совпадения. Для этого убедитесь, что третий аргумент равен нулю или равен FALSE.
Альтернатива с ВПР
Поскольку функция ВПР также возвращает ошибку # Н / Д, если значение не округлено, вы можете построить формулу с функцией ВПР, которая работает так же, как параметр ПОИСКПОЗ. Как и в случае с ПОИСКПОЗ, вы должны настроить ВПР для использования точного совпадения, а затем проверить результат с помощью ISNA. Также обратите внимание, что мы даем ВПР только один столбец (столбец B) для массива таблицы.
