Формулы Excel - Подсчет нескольких критериев с использованием логического НЕ

Подсчет нескольких критериев с использованием логического НЕ

Синтаксис используемых функций

 SUMPRODUCT (массив1, [массив2], [массив3], ...)
ЕНД (значение)
MATCH (lookup_value, lookup_array, [match_type])

Функция SUMPRODUCT используется для умножения соответствующих компонентов в данных массивах и возвращает сумму этих продуктов.
Функция ISNA возвращает True, если Значение относится к значению ошибки # Н / Д (значение недоступно).
Функция MATCH ищет указанный элемент в диапазоне ячеек, а затем возвращает относительную позицию этого элемента в диапазоне.

Что делать?

Для подсчета нескольких критериев с использованием логики НЕ одной из нескольких функций можно использовать функцию SUMPRODUCT вместе с функциями MATCH и ISNA для решения проблемы.

формула

=SUMPRODUCT((C6:C13=F7)*ISNA(MATCH(D6:D13,G7:G8,0)))

Как работает формула

В приведенном выше примере первое выражение C6: C13 = F7 означает, что функция SUMPRODUCT проверяет значение в диапазоне C6: C13 по значению в переменной F7 критерия «Мужской».

Результатом является массив значений TRUE FALSE, таких как -

 
{TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;}

Второе выражение функция MATCH используется для сопоставления каждого значения в диапазоне D6: D13 со значениями в G7: G8, то есть «A» и «B». MATCH возвращает относительную позицию этого lookup_value в lookup_arry, где совпадение завершается успешно. Возвращает номер. Если совпадение не удалось, MATCH возвращает # N / A.

Результатом является массив, как -

{1;2;1;2;#N/A;#N/A;#N/A;2;}

Поскольку значения # N / A соответствуют «not A или B», ISNA «обращает» массив к TRUE и FALSE и выглядит как -

{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;}

Теперь ИСТИНА указывает на «не А или В».

Функция SUMPRODUCT теперь умножает результаты двух массивов и создает один числовой массив внутри SUMPRODUCT, а формула выглядит так:

=SUMPRODUCT({0;0;0;0;1;0;1;0;})

И, наконец, SUMPRODUCT выдает сумму массива. Согласно приведенному выше примеру результат равен 2.

На рисунке выше формула, написанная в G10, представляет собой = SUMPRODUCT ((C6: C13 = F7) * ISNA (MATCH (D6: D13, G7: G8, -1))). В функции MATCH были использованы -1. Это возвращает массив выглядит как -

{1;#N/A;1;#N/A;#N/A;#N/A;#N/A;#N/A;}

ISNA "инвертирует" массив в ИСТИНА и ЛОЖЬ и выглядит как -

{FALSE;TRUE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;}

Функция SUMPRODUCT теперь умножает результаты двух массивов и создает один числовой массив внутри SUMPRODUCT, а формула выглядит так:

=SUMPRODUCT({0;0;0;0;1;0;1;1;})

И, наконец, SUMPRODUCT выдает сумму массива. Согласно приведенному выше примеру результат равен 3.

Предыдущий: Excel Formulas - Подсчет количества ячеек заканчивается строкой
Далее: формулы Excel - Подсчет ячеек с условием ИЛИ