Перейти к основному содержанию

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

Предположим, у вас есть два списка, чтобы подсчитать общее количество значений в одном списке, которые не существуют в другом списке, вы можете использовать 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: тип_соответствия 0 заставляет ПОИСКПОЗ выполнить точное совпадение.

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

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

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

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

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

  • ПОИСКПОЗ(F6:F8,B6:B10,0): тип_соответствия 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: тип_соответствия 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 - поможет вам выделиться из толпы

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


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

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations