Загальна формула
=IF(COUNTIF(list,value),"OK","Missing")
Резюме
Щоб визначити значення в одному списку, яких немає в іншому списку, ви можете використовувати просту формулу на основі функції COUNTIF з функцією IF. У наведеному прикладі формула в G6 має вигляд:
=IF(COUNTIF(list,F6),"OK","Missing")
де "список" - іменований діапазон B6: B11.
Пояснення
Функція COUNTIF підраховує клітинки, які відповідають критеріям, повертаючи кількість знайдених випадків. Якщо жодна комірка не відповідає критеріям, COUNTIF повертає нуль. Ви можете використовувати поведінку безпосередньо всередині оператора IF для позначення значень, які мають нульовий рахунок (тобто значень, які відсутні). У наведеному прикладі формула в G6 має вигляд:
=IF(COUNTIF(list,F6),"OK","Missing")
де "список" - іменований діапазон, який відповідає діапазону B6: B11.
Функція IF вимагає логічного тесту для повернення TRUE або FALSE. У цьому випадку функція COUNTIF виконує логічний тест. Якщо значення знайдено у списку , COUNTIF повертає число безпосередньо до функції IF. Цей результат може бути будь-яким числом … 1, 2, 3 тощо.
Функція IF буде оцінювати будь-яке число як TRUE, змушуючи IF повертати "OK". Якщо значення не знайдено у списку , COUNTIF повертає нуль (0), що вважається FALSE, а IF повертає "Відсутнє".
Альтернатива з MATCH
Ви також можете перевірити відсутність значень за допомогою функції MATCH. MATCH знаходить позицію елемента у списку та повертає помилку # N / A, коли значення не знайдено. Ви можете використовувати цю поведінку для побудови формули, яка повертає "Відсутній" або "ОК", перевіряючи результат MATCH за допомогою функції ISNA. ISNA повертає TRUE лише тоді, коли отримує помилку # N / A.
Для використання MATCH, як показано у прикладі вище, формула:
=IF(ISNA(MATCH(F6,list,0)),"Missing","OK")
Зверніть увагу, що MATCH має бути налаштований на точну відповідність. Для цього переконайтеся, що третій аргумент дорівнює нулю або FALSE.
Альтернатива VLOOKUP
Оскільки VLOOKUP також повертає помилку # N / A, коли значення не є круглим, ви можете створити формулу за допомогою VLOOKUP, яка працює так само, як опція MATCH. Як і у випадку з MATCH, ви повинні налаштувати VLOOKUP для використання точної відповідності, а потім перевірити результат за допомогою ISNA. Також зверніть увагу, що ми надаємо VLOOKUP лише один стовпець (стовпець B) для масиву таблиці.
