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

Подсчет пропущенных значений

Предположим, у вас есть два списка, чтобы подсчитать общее количество значений в одном списке, которые не существуют в другом списке, вы можете использовать SUMPRODUCT формула с помощью МАТЧ и функции ISNA, или COUNTIF функции.

подсчитать пропущенные значения 1

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


Подсчитайте пропущенные значения с помощью СУММПРОИЗВ, ПОИСКПОЗ и ISNA

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

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

=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))

  • диапазон_до_счета: Диапазон, из которого подсчитываются пропущенные значения. Здесь имеется в виду список Б.
  • искомый_диапазон: Диапазон для сравнения с диапазон_до_счета. Здесь имеется в виду список А.
  • 0: Компания match_type 0 заставляет ПОИСКПОЗ выполнить точное совпадение.

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

=СУММПРОИЗВ(--ИСНА(ПОИСКПОЗ(F6: F8,B6: B10, 0)))

подсчитать пропущенные значения 2

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

=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))

  • ПОИСКПОЗ(F6:F8,B6:B10,0): Компания match_type 0 заставляет функцию ПОИСКПОЗ возвращать числовые значения, которые указывают относительное положение значений в ячейках F6 в F8 В диапазоне B6: B10. Если значение не существует в списке A, будет возвращена ошибка #N/A. Итак, результаты будут в виде такого массива: {2;3;#Н/Д}.
  • ISNA (ПОИСКПОЗ(F6:F8,B6:B10,0)) = ISNA ({2;3;#Н/Д}): ISNA работает, чтобы выяснить, является ли значение ошибкой «#Н/Д» или нет. Если да, функция вернет TURE; Если нет, он вернет FALSE. Итак, формула ИСНА вернет {ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
  • СУММПРОИЗВ(--ISNA (ПОИСКПОЗ(F6:F8,B6:B10,0))) = СУММПРОИЗВ(--{ЛОЖЬ;ЛОЖЬ;ИСТИНА}): Двойной отрицательный знак преобразует TRUE в 1, а FALSE в 0: {0; 1; 0}. Затем функция СУММПРОИЗВ возвращает сумму: 1.

Подсчитайте пропущенные значения с помощью СУММПРОИЗВ и СЧЁТЕСЛИ

Для подсчета общее количество значений в списке B, которые отсутствуют в списке A, вы также можете использовать функцию СЧЁТЕСЛИ, чтобы определить, существует ли значение в списке A или нет с условием «= 0», поскольку 0 будет генерироваться, если значение отсутствует. Затем СУММПРОИЗВ подсчитает общее количество пропущенных значений.

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

=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))

  • искомый_диапазон: Диапазон для сравнения с диапазон_до_счета. Здесь имеется в виду список А.
  • диапазон_до_счета: Диапазон, из которого подсчитываются пропущенные значения. Здесь имеется в виду список Б.
  • 0: Компания match_type 0 заставляет ПОИСКПОЗ выполнить точное совпадение.

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

=СУММПРОИЗВ(--(СЧЁТЕСЛИ(B6: B10,F6: F8)=0))

подсчитать пропущенные значения 3

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

=SUMPRODUCT(--(COUNTIF(B6:B10,F6:F8)=0))

  • СЧЁТЕСЛИ(B6:B10,F6:F8): Функция COUNTIF подсчитывает количество вхождений ячеек через F6 в F8 В диапазоне B6: B10. Результат будет в виде такого массива: {1; 1; 0}.
  • - (СЧЁТЕСЛИ(B6:B10,F6:F8)= 0) = - ({1; 1; 0}= 0): Фрагмент {1;1;0}=0 дает массив TRUE и FALSE. {ЛОЖЬ;ЛОЖЬ;ИСТИНА}. Затем двойной отрицательный знак превращает TRUE в 1, а FALSE в 0. Окончательный массив выглядит так: {0; 0; 1}.
  • СУММПРОИЗВ (- (СЧЁТЕСЛИ(B6:B10,F6:F8)= 0)) = СУММПРОИЗВ ({0; 0; 1}): Затем СУММПРОИЗВ возвращает сумму: 1.

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

Функция СУММПРОИЗВ в Excel

В Excel функцию СУММПРОИЗВ можно использовать для умножения двух или более столбцов или массивов вместе, а затем получения суммы произведений. Фактически, СУММПРОИЗВ - это полезная функция, которая может помочь подсчитать или суммировать значения ячеек с несколькими критериями, такими как функция СЧЁТЕСЛИ или СУММЕСЛИМН. В этой статье будет представлен синтаксис функции и несколько примеров для этой функции СУММПРОИЗВ.

Функция ПОИСКПОЗ в Excel

Функция ПОИСКПОЗ в Excel ищет определенное значение в диапазоне ячеек и возвращает относительное положение значения.

Функция СЧЁТЕСЛИ в Excel

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


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

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

Бывают случаи, когда вам нужно сравнить два списка, чтобы проверить, существует ли значение списка A в списке B в 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   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL