Подсчёт по нескольким критериям с логикой «НЕ» в Excel

В этой статье показано, как подсчитывать количество ячеек по нескольким критериям с использованием логики «НЕ» в Excel.

Как в Excel выполнить подсчёт по нескольким критериям с использованием логики «НЕ»?

Как показано в приведённой ниже таблице заказов, чтобы подсчитать количество ячеек, содержащих «Ноутбук», за исключением ноутбуков из магазинов B или C, используйте следующую формулу.

Универсальная формула

=SUMPRODUCT(( range1=criteria1)*(ISNA(MATCH(range2,criteria2,0)))

Аргументы

Как использовать эту формулу?

1. Выберите пустую ячейку, чтобы вывести результат.

2. Введите приведённую ниже формулу в выбранную ячейку и нажмите клавишу Enter, чтобы получить результат.

=SUMPRODUCT((D3:D12=G6)*(ISNA(MATCH(E3:E12,H6:H7,0))))

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

=SUMPRODUCT((D3:D12=G6)*(ISNA(MATCH(E3:E12,H6:H7,0))))

• (D3:D12=G6): Здесь проверяется, равно ли каждое значение в диапазоне D3:D12 equals to the specific value (laptop) значению в ячейке G6, и возвращается массив значений ИСТИНА и ЛОЖЬ в виде: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА};
• MATCH(E3:E12,H6:H7,0):Функция ПОИСКПОЗ проверяет, встречаются ли значения B и C в диапазоне E3:E12. Если значение найдено, возвращается его позиция; если нет — ошибка #Н/Д. В результате получается массив вида {#Н/Д;1;2;#Н/Д;1;2;#Н/Д;1;2;#Н/Д}.
• ЕПУСТО({#Н/Д;1;2;#Н/Д;1;2;#Н/Д;1;2;#Н/Д}): Функция ЕПУСТО возвращает ИСТИНА, если встречает ошибку #Н/Д, и ЛОЖЬ — для всех остальных значений. В результате вы получите новый массив: {ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА}.
• Здесь значение ИСТИНА в массиве соответствует условию «не B и не C».
• SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE;TRUE}*({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE})): Здесь ИСТИНА эквивалентна 1, а ЛОЖЬ — 0. Затем значения из двух массивов перемножаются, и вы получите SUMPRODUCT({1;0;0;0;0;0;0;0;0;1}). Наконец, функция СУММПРОИЗВ суммирует все элементы массива и возвращает окончательный результат: 2.

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

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