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

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

Как подсчитать несколько критериев с логикой НЕ в Excel?

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

Общая формула

=СУММПРОИЗВ(( диапазон1=критерий1)*(ЕОШ(ПОИСКПОЗ(диапазон2,критерий2,0)))

Аргументы

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

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

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

=СУММПРОИЗВ((D3:D12=G6)*(ЕОШ(ПОИСКПОЗ(E3:E12,H6:H7,0))))

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

=СУММПРОИЗВ((D3:D12=G6)*(ЕОШ(ПОИСКПОЗ(E3:E12,H6:H7,0))))

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

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

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