Найдите недостающие значения
Бывают случаи, когда вам нужно сравнить два списка, чтобы проверить, существует ли значение списка 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")
√ Примечание: вы можете изменить «Отсутствует», «Найдено» на любые значения по своему усмотрению.
- искомое_значение: Значение MATCH, используемое для получения его позиции, если оно существует в искомый_диапазон или # N / A error, если нет. Здесь относится к продуктам в вашем списке.
- искомый_диапазон: Диапазон ячеек для сравнения с искомое_значение. Здесь имеется в виду список продуктов поставщика.
Выяснить если все продукты из вашего списка есть в списке вашего поставщика, скопируйте или введите формулу ниже в ячейку 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")
√ Примечание: вы можете изменить «Отсутствует», «Найдено» на любые значения по своему усмотрению.
- искомое_значение: Значение VLOOKUP, используемое для получения его положения, если оно существует в искомый_диапазон или # N / A error, если нет. Здесь относится к продуктам в вашем списке.
- искомый_диапазон: Диапазон ячеек для сравнения с искомое_значение. Здесь имеется в виду список продуктов поставщика.
Чтобы узнать, все ли продукты из вашего списка присутствуют в списке вашего поставщика, скопируйте или введите формулу ниже в ячейку 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 как ЛОЖЬ. Итак, формула вернет Отсутствующий, значение, возвращаемое, когда первое увеличение оценивается как ЛОЖЬ.
Связанные функции
Функция ЕСЛИ - одна из самых простых и полезных функций в книге Excel. Он выполняет простой логический тест, который зависит от результата сравнения, и возвращает одно значение, если результат ИСТИНА, или другое значение, если результат ЛОЖЬ.
Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.
Функция ВПР в Excel выполняет поиск значения по первому столбцу таблицы и возвращает соответствующее значение из определенного столбца в той же строке.
Функция СЧЁТЕСЛИ - это статистическая функция в Excel, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (<>, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.
Связанные формулы
Найдите значение, содержащее определенный текст с подстановочными знаками
Чтобы найти первое совпадение, содержащее определенную текстовую строку в диапазоне в Excel, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).
Бывают случаи, когда вам нужен Excel для извлечения данных на основе частичной информации. Чтобы решить эту проблему, вы можете использовать формулу ВПР вместе с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).
Примерное совпадение с ИНДЕКСОМ и ПОИСКПОЗ
Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel, чтобы оценить производительность сотрудников, выставить оценки учащихся, рассчитать почтовые расходы на основе веса и т. Д. В этом руководстве мы поговорим о том, как использовать функции ИНДЕКС и ПОИСКПОЗ для получения результаты нам нужны.
Найти значение ближайшего соответствия по нескольким критериям
В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
