Найдите недостающие значения

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

Найдите недостающие значения с помощью MATCH, ISNA и IF
Найдите недостающие значения с помощью ВПР, ISNA и IF
Найдите пропущенные значения с помощью СЧЁТЕСЛИ и ЕСЛИ

---

Найдите недостающие значения с помощью MATCH, ISNA и IF

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

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

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

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

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

= ЕСЛИ (ISNA (ПОИСКПОЗ (30002,6 млрд долларов: 10 млрд долларов, 0)), «Пропал без вести», «Найден»)

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

= ЕСЛИ (ISNA (ПОИСКПОЗ (G6,6 млрд долларов: 10 млрд долларов, 0)), «Пропал без вести», «Найден»)

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

Пояснение формулы

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

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

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

---

Найдите недостающие значения с помощью ВПР, ISNA и IF

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

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

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

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

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

= ЕСЛИ (ISNA (ВПР (30002,6 млрд долларов: 10 млрд долларов, 1, FALSE)), «Отсутствует», «Найдено»)

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

= ЕСЛИ (ISNA (ВПР (G6,6 млрд долларов: 10 млрд долларов, 1, FALSE)), «Отсутствует», «Найдено»)

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

Пояснение формулы

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

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

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

---

Найдите пропущенные значения с помощью СЧЁТЕСЛИ и ЕСЛИ

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

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

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

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

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

= ЕСЛИ (СЧЁТЕСЛИ (6 млрд долларов: 10 млрд долларов,30002), «Найдено», «Пропало»)

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

= ЕСЛИ (СЧЁТЕСЛИ (6 млрд долларов: 10 млрд долларов,G6), «Найдено», «Пропало»)

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

Пояснение формулы

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

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

• СЧЁТЕСЛИ (6 млрд долларов: 10 млрд долларов, G8): Функция СЧЁТЕСЛИ подсчитывает, сколько раз 3004, значение в ячейке G8 появится в массиве 6 млрд долларов: 10 млрд долларов. Очевидно, 3004 не существует в массиве, поэтому результат будет 0.
• ЕСЛИ(СЧЁТЕСЛИ (6 млрд долларов: 10 млрд долларов, G8), «Найдено», «Отсутствует») = ЕСЛИ (0, «Найдено», «Пропало»): Функция ЕСЛИ оценит 0 как ЛОЖЬ. Итак, формула вернет Отсутствующий, значение, возвращаемое, когда первое увеличение оценивается как ЛОЖЬ.

---

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

---

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

---

Лучшие инструменты для работы в офисе