Note: The other languages of the website are Google-translated. Back to English
English English

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

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

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

Найдите недостающие значения с помощью 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)), «Пропал без вести», «Найден»)

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

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

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

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

=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)), «Отсутствует», «Найдено»)

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

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

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

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

=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), «Найдено», «Пропало»)

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

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

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

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

=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, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (<>, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.


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

Найдите значение, содержащее определенный текст с подстановочными знаками

Чтобы найти первое совпадение, содержащее определенную текстовую строку в диапазоне в Excel, вы можете использовать формулы ИНДЕКС и ПОИСКПОЗ с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).

Частичное совпадение с ВПР

Бывают случаи, когда вам нужен Excel для извлечения данных на основе частичной информации. Чтобы решить эту проблему, вы можете использовать формулу ВПР вместе с подстановочными знаками - звездочкой (*) и вопросительным знаком (?).

Примерное совпадение с ИНДЕКСОМ и ПОИСКПОЗ

Бывают случаи, когда нам нужно найти приблизительные совпадения в Excel, чтобы оценить производительность сотрудников, выставить оценки учащихся, рассчитать почтовые расходы на основе веса и т. Д. В этом руководстве мы поговорим о том, как использовать функции ИНДЕКС и ПОИСКПОЗ для получения результаты нам нужны.

Найти значение ближайшего соответствия по нескольким критериям

В некоторых случаях может потребоваться поиск ближайшего или приблизительного значения соответствия на основе нескольких критериев. С помощью комбинации функций ИНДЕКС, ПОИСКПОЗ и ЕСЛИ вы можете быстро сделать это в Excel.


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

Kutools for Excel - поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и т. Д.) И экономия 80% времени для вас.

  • Рассчитан на 1500 сценариев работы, помогает решить 80% задач Excel.
  • Уменьшите количество нажатий на клавиатуру и мышь каждый день, избавьтесь от усталости глаз и рук.
  • Станьте экспертом по Excel за 3 минуты. Больше не нужно запоминать какие-либо болезненные формулы и коды VBA.
  • 30-дневная неограниченная бесплатная пробная версия. 60-дневная гарантия возврата денег. Бесплатное обновление и поддержка 2 года.
Лента Excel (с установленным Kutools for Excel)

Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), точно так же, как Chrome, Firefox и новый Internet Explorer.
Снимок экрана Excel (с установленной вкладкой Office)
Сортировать комментарии по
Комментарии (0)
Оценок пока нет. Оцените первым!
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места