image

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

Сценарий:

Например, у нас есть список значений, и нам нужно подтверждение о местонахождении требуемых имен в списке. Допустим, мы хотим подсчитать имена пропавших без вести VIP (VIP_names) в списке гостей.

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

Используемые функции и формула Синтаксис:

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

  1. link: / summing-excel-sumproduct-function [функция СУММПРОИЗВ]

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

Мы построим из него формулу. Во-первых, функция СЧЁТЕСЛИ принимает каждое значение одно за другим в диапазоне критериев и проверяет их в указанном диапазоне. Функция СУММПРОИЗВ возвращает сумму ИСТИННЫХ значений (как 1) и ЛОЖНЫХ значений (как 0) в массиве.

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

=SUMPRODUCT(--(COUNTIF(long_list,required)=0))

-: оператор, используемый для преобразования значений ИСТИНА в значения ЛОЖЬ. Можно сказать, что функция НЕ используется для массива.

long_list: требуемый список поиска: обязательный список

Пример:

Все это может быть непонятным для понимания. Итак, давайте протестируем эту формулу, запустив ее на примере, показанном ниже. Здесь у нас есть данные о ценах на продукты, полученные на даты. Здесь нам нужно найти количество предметов для всех предметов первой необходимости. Для этого мы назначили 2 списка в качестве полученного списка и необходимые элементы в столбце формулы. Теперь мы воспользуемся приведенной ниже формулой, чтобы получить количество продуктов.

Используйте формулу:

=SUMPRODUCT(--(COUNTIF(C3:C16,E3:E12)=0))

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

  1. Функция СЧЁТЕСЛИ считает каждый элемент или ячейку в E3: E12 (предметы первой необходимости)

в полученном списке (C3: C16).

  1. = 0 извлекает список необходимых вещей в массив. . — оператор возвращает отрицание массива, т.е. значения ИСТИНА в значения ЛОЖЬ.

  2. SUMPRODUCT находит сумму, которая будет подсчитывать предметы первой необходимости, которых нет в полученном списке.

image

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

image

Как видите, формула возвращает 3 в качестве количества непринятых предметов первой необходимости. В списке из 14 полученных предметов еще 3 нет.

Если — (оператор отрицания не используется)

Вам должно быть интересно, что делает этот оператор отрицания (-). Воспользуемся приведенной выше формулой без символа отрицания (-).

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

=SUMPRODUCT((COUNTIF(C3:C16,E3:E12)=0))

Эта формула возвращает 7 — количество предметов первой необходимости в полученных предметах. Эта формула работает, только если в обоих списках нет повторяющихся элементов. Перед использованием формулы проверьте дубликаты.

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

Примечания:

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

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

  3. Оператор (-) отменяет знаки 1 на 0 и 0 на 1.

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

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

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

Надеюсь, эта статья о том, как подсчитать пропущенные значения в списке в Excel, носит пояснительный характер. Дополнительные статьи о формулах COUNTIF здесь. Если вам понравились наши блоги, поделитесь ими со своими друзьями на Facebook. А также вы можете подписаться на нас в Twitter и Facebook. Мы будем рады услышать от вас, дайте нам знать, как мы можем улучшить, дополнить или усовершенствовать нашу работу и сделать ее лучше для вас. Напишите нам на [email protected].

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

link: / logic-formulas-if-with-and-function-in-excel [ЕСЛИ с функцией И]: реализация логической функции ЕСЛИ с функцией И для извлечения результатов, имеющих критерии в Excel.

link: / logic-formulas-if-with-or-function-in-excel [ЕСЛИ с функцией ИЛИ]: Реализация логической функции ЕСЛИ с функцией ИЛИ для извлечения результатов, имеющих критерии в данных Excel.

link: / logic-formulas-excel-nested-if-function [Как использовать вложенную функцию ЕСЛИ]: вложенная функция ЕСЛИ работает с данными, имеющими несколько критериев. Использование повторяющейся функции ЕСЛИ — это вложенная формула ЕСЛИ в Excel.

link: / summing-sumifs-using-and-or-logic [СУММЕСЛИМН с использованием логики И-ИЛИ]: Получить сумму чисел, имеющих несколько критериев, применяемых с помощью логической функции И-ИЛИ в excel.

link: / logic-formulas-minimum-value-using-if [Минимальное значение с использованием функции ЕСЛИ]: Получить минимальное значение с помощью функции excel IF и функции MIN для данных массива.

link: / tips-excel-wildcards [Как использовать подстановочные знаки в excel]: Подсчет ячеек, соответствующих фразам, используя подстановочные знаки в excel

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

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

link: / formulas-and-functions-Introduction-of-vlookup-function [Функция ВПР в Excel]: Это одна из наиболее часто используемых и популярных функций Excel, которая используется для поиска значений из разных диапазонов и листов. link: / tips-countif-in-microsoft-excel [COUNTIF в Excel 2016]: Подсчет значений с условиями с помощью этой удивительной функции. Вам не нужно фильтровать данные для подсчета определенных значений.

Функция Countif важна для подготовки вашей приборной панели.

link: / excel-formula-and-function-excel-sumif-function [Как использовать функцию СУММЕСЛИ в Excel]: Это еще одна важная функция панели инструментов. Это поможет вам суммировать значения для конкретных условий.