Подсчёт отсутствующих значений

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

Подсчёт отсутствующих значений с помощью СУММПРОИЗВ, ПОИСКПОЗ и ЕНД
Подсчёт отсутствующих значений с помощью СУММПРОИЗВ и СЧЁТЕСЛИ

Подсчёт отсутствующих значений с помощью СУММПРОИЗВ, ПОИСКПОЗ и ЕНД

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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