Подсчет пропущенных значений
Предположим, у вас есть два списка, чтобы подсчитать общее количество значений в одном списке, которые не существуют в другом списке, вы можете использовать SUMPRODUCT формула с помощью МАТЧ и функции ISNA, или COUNTIF функции.
Подсчитайте пропущенные значения с помощью СУММПРОИЗВ, ПОИСКПОЗ и 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)))
Пояснение формулы
=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))
Пояснение формулы
=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, которая используется для подсчета количества ячеек, соответствующих критерию. Он поддерживает логические операторы (<>, =,> и <), а также символы подстановки (? И *) для частичного сопоставления.
Связанные формулы
Бывают случаи, когда вам нужно сравнить два списка, чтобы проверить, существует ли значение списка A в списке B в Excel. Например, у вас есть список продуктов, и вы хотите проверить, существуют ли продукты из вашего списка в списке продуктов, предоставленном вашим поставщиком. Чтобы выполнить эту задачу, мы перечислили три способа ниже, не стесняйтесь выбирать тот, который вам нравится.
В этой статье основное внимание уделяется формулам Excel для подсчета ячеек, которые в точности равны указанной вами текстовой строке или частично равны заданной текстовой строке, как показано на скриншотах ниже. Во-первых, он объяснит синтаксис формулы и аргумент, а также предоставляет примеры для лучшего понимания.
Подсчитайте количество ячеек не между двумя заданными числами
Подсчет количества ячеек между двумя числами - обычная задача для нас в Excel, но в некоторых случаях вы можете захотеть подсчитать ячейки не между двумя заданными числами. Например, у меня есть список продуктов с продажами с понедельника по воскресенье, теперь мне нужно получить количество ячеек, которое не находится между конкретными минимальными и высокими числами, как показано ниже. В этой статье я представлю некоторые формулы для решения этой задачи в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
