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

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

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

 SUMPRODUCT (array1, [array2], [array3], ...) ISNA (значение) MATCH (lookup_value, lookup_array, [match_type]  ) 

Функция СУММПРОИЗВ используется для умножения соответствующих компонентов в заданных массивах и возвращает сумму этих продуктов.
Функция ISNA возвращает True, если значение относится к # Н/Д (значение недоступно) значение ошибки.
Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек, а затем возвращает относительное положение этого элемента в диапазоне.

Что делать?

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

Формула

  = СУММПРОИЗВ ((C6: C13 = F7) *  ISNA (MATCH (D6: D13, G7: G8,0)))  

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

В приведенном выше примере первое выражение C6: C13 = F7 означает, что функция СУММПРОИЗВ проверяет значение в диапазоне C6: C13 на соответствие значение в переменной критерия F7, «Мужской».

Результатом является массив значений ИСТИНА ЛОЖЬ, например —

  {ИСТИНА; ЛОЖЬ;  ИСТИНА; ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА;}  

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

Результатом является массив вида —

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

Поскольку значения # N/A соответствуют «не A или B», ISNA «меняет местами» массив в ИСТИНА и ЛОЖЬ и выглядит как —

  {ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ;}  

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

Функция СУММПРОИЗВ умножила результаты двух массивов и создала один числовой массив внутри СУММПРОИЗВ, а формула выглядит так —

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

И, наконец, СУММПРОИЗВ произвел сумму массива. Согласно приведенному выше примеру результат равен 2.

На приведенном выше рисунке формула, записанная в G10, имеет вид = СУММПРОИЗВ ((C6: C13 = F7) * ISNA (MATCH (D6: D13, G7: G8, -1))). В функции ПОИСКПОЗ используется -1.. Возвращаемый массив выглядит так:

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

ISNA «меняет» массив на ИСТИНА и ЛОЖЬ и выглядит так —

  {  ЛОЖЬ; ИСТИНА; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА; ИСТИНА;}  

Теперь функция СУММПРОИЗВ умножила результаты двух массивов и создала один числовой массив внутри СУММПРОИЗВ и формула выглядит так —

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

И, наконец, СУММПРОИЗВ произвел сумму массива. Согласно приведенному выше примеру результат равен 3.

Оцените статью
nanomode.ru
Добавить комментарий