В этой статье мы узнаем, как найти недостающие значения в таблице с помощью формулы в Excel.

Например, у нас есть список значений, и нам нужно подтверждение местонахождения значения в списке. Для этого мы рассмотрим ситуацию и предложим несколько формул, позволяющих сделать то же самое. Мы будем использовать разные операции с функциями в зависимости от простоты table_array.

Следующая функция будет использоваться. Так что узнайте немного о них, прежде чем использовать.

  1. link: / tips-if-condition-in-excel [IF function]

  2. link: / tips-countif-in-microsoft-excel [функция СЧЁТЕСЛИ]

  3. link: / logic-formulas-excel-isna-function [Функция ISNA]

  4. link: / формулы-и-функции-введение-функции-vlookup [функция ВПР]

  5. link: / lookup-formulas-excel-match-function [MATCH function]

Мы построим из него формулу. Во-первых, значение поиска ищется в конкретном столбце массива таблицы. Тогда согласованные значения дадут нам подтверждение с помощью функции ЕСЛИ. Функция ЕСЛИ возвращает подтверждение с использованием значений «Есть» и «Отсутствует».

Первый метод *: использование функций СЧЁТЕСЛИ и ЕСЛИ Используйте общую формулу

= IF (

COUNTIF (

list , cell_value), «Is there» , «Missing» )

Пояснение:

  • Функция СЧЁТЕСЛИ сохраняет количество значений cell_value в списке и возвращает число в функцию ЕСЛИ.

  • Функция ЕСЛИ рассматривает 0 как ЛОЖЬ, а любое другое целое число, кроме 0, как ИСТИНА.

  • Функция ЕСЛИ возвращает «Есть» как Значение, если истинно, и «Отсутствует» как значение, если Ложь.

Пример:

Давайте рассмотрим это с помощью формулы в примере.

Вот у нас есть таблица, и нам нужно извлечь из нее информацию.

image

Здесь нам нужно подтверждение списком ID. Поэтому мы используем именованный диапазон для списка идентификаторов. ID * именованный диапазон используется для C2: C14.

Итак, мы воспользуемся формулой, чтобы получить общую сумму

= IF (

COUNTIF (

ID , G4 ) , «Is there» , «Missing» )

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

  1. Функция СЧЁТЕСЛИ сохраняет количество идентификаторов 900 в списке и возвращает их в функцию ЕСЛИ.

  2. Функция ЕСЛИ рассматривает 0 как ЛОЖЬ, а любое другое целое число, кроме 0, как ИСТИНА. Функция ЕСЛИ возвращает «Есть» как Значение, если истинно, и «Отсутствует» как значение, если Ложь.

Здесь массивы аргументов функции указаны как ссылка на ячейку.

image

Как вы можете видеть, формула возвращает значения для ID-номера 807 и 953. Но возвращает Missing для ID-номера 900. Второй метод: использование функции ISNA и VLOOKUP.

Синтаксис формулы:

= IF (

ISNA

(

VLOOKUP

( cell_value , list , 1 , 0 )), «Missing» , «Is There» )

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

  1. Функция ВПР ищет значение ячейки в 1-м столбце списка table_array. Функция возвращает значение, если оно найдено, или возвращает ошибку # Н / Д.

  2. Функция ISNA перехватывает ошибку # Н / Д и возвращает ИСТИНА, если ошибка # Н / Д существует, или возвращает ЛОЖЬ.

  3. Функция ЕСЛИ возвращает «Есть» в качестве значения, если ЛОЖНО, и «Отсутствует» в качестве значения, если ИСТИНА.

image

Как видно из приведенного выше снимка. Формула возвращает «Есть там»

для совпадающего идентификатора 807 и 953. Но возвращает «Отсутствует» для несовпадающего идентификатора 900. Третий метод: использование функции ISNA & MATCH.

Синтаксис формулы:

= IF (

ISNA

(

MATCH

( cell_value , list , 0 )), «Missing» , «Is There» )

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

  1. Функция MATCH ищет значение ячейки в списке table_array.

Функция возвращает значение, если оно найдено, или возвращает ошибку # Н / Д.

  1. Функция ISNA перехватывает ошибку # Н / Д и возвращает ИСТИНА, если ошибка # Н / Д существует, или возвращает ЛОЖЬ.

  2. Функция ЕСЛИ возвращает «Есть» в качестве значения, если ЛОЖНО, и «Отсутствует» в качестве значения, если ИСТИНА.

image

Как видно из приведенного выше снимка. Формула возвращает «Есть там»

для совпадающего идентификатора 807 и 953. Но возвращает «Отсутствует» для несовпадающего идентификатора 900. Выше объяснялись 3 примера для поиска недостающих значений в списке в Excel. Все три формулы работают нормально, но есть некоторые моменты, на которые стоит обратить внимание.

Заметки: . Функция VLOOKUP не смотрит влево в table_array.

  1. Функция СЧЁТЕСЛИ поддерживает подстановочные знаки (,? **), которые помогают извлекать значения, содержащие фразы.

  2. Нечисловые значения должны быть заключены в двойные кавычки («значение» *)

или используйте cell_reference ..

  1. Просматривайте весь список, поскольку функция возвращает значения везде, где они совпадают.

  2. Аргумент массива функции может быть указан как ссылка на ячейку или именованные диапазоны.

  3. Вы можете настроить эти формулы по мере необходимости, используя другую функцию Excel.

  4. Функция возвращает сумму значений, удовлетворяющих всем условиям.

Надеюсь, вы поняли, как найти недостающие значения в Excel. Дополнительные статьи о формулах функций Excel можно найти здесь. Не стесняйтесь высказывать свой запрос или отзыв по указанной выше статье. Мы поможем вам.

Похожие статьи

link: / lookup-formulas-vlookup-top-5-values-with-duplicate-values-using-index-match-in-excel [INDEX-MATCH in Excel]

link: / lookup-formulas-vlookup-multiple-values ​​[VLOOKUP Multiple Values]

link: / lookup-formulas-vlookup-with-dynamic-col-index [ВПР с динамическим индексом столбцов]

link: / lookup-formulas-partial-match-with-vlookup-function [Частичное совпадение с функцией VLOOKUP]

link: / lookup-formulas-vlookup-by-date-in-excel [Просмотр-поиск по дате в Excel]

link: / lookup-formulas-17-things-about-excel-vlookup [17 вещей о ВПР в Excel]

Популярные статьи

link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-Повышение-продуктивность [50 ярлыков Excel для повышения вашей производительности]

link: / excel-generals-how-to-edit-a-dropdown-list-in-microsoft-excel [Редактировать раскрывающийся список]

link: / excel-range-name-absolute-reference-in-excel [Абсолютная ссылка в Excel]

link: / tips-conditional-formatting-with-if-statement [Если с условным форматированием]

link: / logic-formulas-if-function-with-wildcards [Если с wildcards]

link: / lookup-formulas-vlookup-by-date-in-excel [Vlookup-by-date]