Поиск отсутствующих значений

Иногда возникает необходимость сравнить два списка в Excel, чтобы проверить, содержится ли значение из списка A в списке B. Например, у вас есть перечень товаров, и вы хотите убедиться, что они присутствуют в списке, предоставленном поставщиком. Ниже представлены три способа решения этой задачи — выберите наиболее удобный для вас.

Поиск отсутствующих значений с помощью ПОИСКПОЗ, ЕОШ и ЕСЛИ
Поиск отсутствующих значений с помощью ВПР, ЕОШ и ЕСЛИ
Поиск отсутствующих значений с помощью СЧЁТЕСЛИ и ЕСЛИ

Поиск отсутствующих значений с помощью ПОИСКПОЗ, ЕОШ и ЕСЛИ

Чтобы определить, присутствуют ли все товары из вашего списка в списке поставщика, как показано на скриншоте выше, сначала используйте функцию ПОИСКПОЗ для поиска позиции товара из вашего списка (значение из столбца A) в списке поставщика (столбец B). Если товар не найден, функция ПОИСКПОЗ вернёт ошибку #N/A. Затем передайте этот результат функции ЕОШ, чтобы преобразовать ошибки #N/A в значение ИСТИНА — это будет означать, что такие товары отсутствуют. После этого функция ЕСЛИ выдаст ожидаемый результат.

Общий синтаксис

=IF(ISNA(MATCH("lookup_value",lookup_range,0)),"Missing","Found")

√ Примечание: вы можете заменить значения «Отсутствует» и «Найдено» на любые другие по своему усмотрению.

Чтобы определить,присутствуют ли все товары из вашего списка в списке поставщика, скопируйте или введите приведённую ниже формулу в ячейку H6 и нажмите клавишу Enter, чтобы получить результат:

=ЕСЛИ(ЕОШ(ПОИСКПОЗ()))30002,$B$6:$B$10,0)),«Missing»,«Found»)

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ЕСЛИ(ЕНД(ПОИСКПОЗ()))G6;$B$6:$B$10;0);«Отсутствует»;«Найдено»)

√ Примечание. Знаки доллара ($) выше обозначают абсолютные ссылки, то есть диапазон_поискав формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавлены к значению_поиска, поскольку вы хотите, чтобы оно было динамическим. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.

Объяснение формулы

В качестве примера используем приведённую ниже формулу:

=IF(ISNA(MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

• MATCH(G8,$B$6:$B$10,0): Параметр match_type 0 заставляет функцию MATCH вернуть число, указывающее позицию первого вхождения значения 3004 (из ячейки G8) в массиве $B$6:$B$10. Однако в данном случае это значение не найдено в указанном диапазоне, поэтому функция возвращает ошибку #N/A.
• ISNA()MATCH(G8,$B$6:$B$10,0))=ISNA()#N/A):Функция ISNA проверяет, является ли значение ошибкой «#N/A». Если да — она возвращает ИСТИНА; если же значение отличается от ошибки «#N/A», функция возвращает ЛОЖЬ. Следовательно, эта формула ISNA вернёт ИСТИНА.
• IF()ISNA()MATCH(G8,$B$6:$B$10,0)),«Missing»,«Found») = IF(ИСТИНА,«Missing»,«Found»):Функция IF вернёт «Missing» («Отсутствует»), если результат проверки с помощью ISNA и MATCH равен ИСТИНА; в противном случае — «Found» («Найдено»). Таким образом, формула вернёт Missing.

Поиск отсутствующих значений с помощью ВПР, ЕНД и ЕСЛИ

Чтобы проверить, присутствуют ли все товары из вашего списка в прайс-листе поставщика, замените функцию ПОИСКПОЗ на ВПР — она работает аналогично: если значение отсутствует в другом списке (то есть считается пропущенным), функция вернёт ошибку #Н/Д.

Общий синтаксис

=IF(ISNA(VLOOKUP("lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ Примечание: вы можете заменить значения «Отсутствует» и «Найдено» на любые другие по своему усмотрению.

Чтобы определить, присутствуют ли все товары из вашего списка в списке поставщика, скопируйте или введите приведённую ниже формулу в ячейку H6 и нажмите Enter, чтобы получить результат:

=ЕСЛИ(ЕНД(ВПР()))30002;$B$6:$B$10;1;ЛОЖЬ));«Отсутствует»;«Найдено»)

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ЕСЛИ(ЕНД(ВПР()))G6;$B$6:$B$10;1;ЛОЖЬ));«Отсутствует»;«Найдено»)

√ Примечание. Знаки доллара ($) выше обозначают абсолютные ссылки, то есть диапазон_поиска в формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавлены к значению_поиска, поскольку вы хотите, чтобы оно оставалось динамическим. После ввода формулы перетащите маркер заполнения вниз, чтобы применить её к ячейкам ниже.

Объяснение формулы

В качестве примера используем приведённую ниже формулу:

=IF(ISNA(VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

• VLOOKUP(G8,$B$6:$B$10,1,FALSE): Параметр range_lookup ЛОЖЬ заставляет функцию VLOOKUP искать и возвращать значение, точно соответствующее значению 3004, находящемуся в ячейке G8. Если значение lookup_value 3004 содержится в первом столбце массива $B$6:$B$10, функция VLOOKUP вернёт это значение; в противном случае она вернёт ошибку #N/A. В данном случае значение 3004 отсутствует в массиве, поэтому результатом будет #N/A.
• ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE))=ISNA()#N/A):Функция ISNA проверяет, является ли значение ошибкой «#N/A». Если да — она возвращает ИСТИНА; если же значение не является ошибкой «#N/A», функция возвращает ЛОЖЬ. Следовательно, эта формула ISNA вернёт ИСТИНА.
• IF()ISNA()VLOOKUP(G8,$B$6:$B$10,1,FALSE)),«Missing»,«Found») = IF(ИСТИНА,«Missing»,«Found»):Функция IF вернёт «Missing» («Отсутствует»), если результат сравнения с помощью ISNA и VLOOKUP равен ИСТИНА; в противном случае будет возвращено «Found» («Найдено»). Таким образом, формула вернёт Missing.

Поиск отсутствующих значений с помощью СЧЁТЕСЛИ и ЕСЛИ

Чтобы определить, присутствуют ли все товары из вашего списка в списке поставщика, вы можете использовать более простую формулу с функциями СЧЁТЕСЛИ и ЕСЛИ. Эта формула основана на том, что Excel интерпретирует любое число, кроме нуля (0), как ИСТИНА. Таким образом, если значение присутствует в другом списке, функция СЧЁТЕСЛИ вернёт количество его вхождений в этот список, и ЕСЛИ воспримет это число как ИСТИНА; если значение отсутствует в списке, СЧЁТЕСЛИ вернёт 0, и ЕСЛИ воспримет это как ЛОЖЬ.

Общий синтаксис

=IF(COUNTIF("lookup_range",lookup_value),"Found","Missing")

√ Примечание. Вы можете заменить значения «Найдено» и «Отсутствует» на любые другие по своему усмотрению.

Чтобы определить, присутствуют ли все товары из вашего списка в списке поставщика, скопируйте или введите приведённую ниже формулу в ячейку H6 и нажмите Enter, чтобы получить результат:

=ЕСЛИ(СЧЁТЕСЛИ())$B$6:$B$10;30002);«Найдено»;«Отсутствует»)

Или используйте Ссылка на ячейку, чтобы сделать формулу динамической:

=ЕСЛИ(СЧЁТЕСЛИ())$B$6:$B$10;G6);«Найдено»;«Отсутствует»)

√ Примечание. Знаки доллара ($) выше обозначают абсолютные ссылки, то есть диапазон_поискав формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавлены к значению_поискапоскольку вы хотите, чтобы оно было динамическим. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.

Объяснение формулы

В качестве примера используем приведённую ниже формулу:

=IF(COUNTIF($B$6:$B$10,G8),"Found","Missing")

• COUNTIF($B$6:$B$10,G8):Функция COUNTIF подсчитывает, сколько раз значение 3004, находящееся в ячейке G8, встречается в диапазоне $B$6:$B$10. Поскольку значение 3004 в этом диапазоне отсутствует, результатом будет 0.
• IF()COUNTIF($B$6:$B$10,G8),«Found»,«Missing») = IF(0,«Found»,«Missing»):Функция IF воспринимает значение 0 как ЛОЖЬ. Поэтому формула вернёт Missing — то есть результат, указанный для случая, когда первый аргумент ложен.

Связанные функции

Связанные формулы