Подсчет количества ячеек, не равных множеству значений в Excel

В Excel вы можете легко получить количество ячеек, не равных определенному значению, используя функцию СЧЁТЕСЛИ. Но пробовали ли вы подсчитать ячейки, которые не равны многим значениям? Например, я хочу получить общее количество продуктов в столбце A, но исключить определенные позиции из диапазона C4:C6, как показано на скриншоте ниже. В этой статье я представлю несколько формул для решения этой задачи в Excel.

• Подсчет количества ячеек, не равных множеству значений с помощью формулы1
• Подсчет количества ячеек, не равных множеству значений с помощью формулы2

Подсчет количества ячеек, не равных множеству значений с помощью формулы1

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

• диапазон: Список ячеек, которые вы хотите подсчитать;
• значения_для_исключения: Значения, которые нужно игнорировать из списка диапазона.

1. Введите или скопируйте приведенную ниже формулу в пустую ячейку, где вы хотите вывести результат:

2. Затем нажмите клавишу Enter, чтобы получить правильный результат, как показано на скриншоте:

Объяснение формулы:

• ПОИСКПОЗ(A2:A13;C2:C4;0): Эта функция ПОИСКПОЗ возвращает позицию каждого совпадающего значения в виде числа и возвращает ошибку #Н/Д там, где совпадений нет. Это означает, что если значения в A2:A13 равны значениям в C2:C4, она вернет число, в противном случае вернет ошибку #Н/Д. Результат здесь будет таким: {1;#Н/Д;#Н/Д;1;3;2;#Н/Д;#Н/Д;3;#Н/Д;#Н/Д;#Н/Д}.
• ЕНД(ПОИСКПОЗ(A2:A13;C2:C4;0))=ЕНД({1;#Н/Д;#Н/Д;1;3;2;#Н/Д;#Н/Д;3;#Н/Д;#Н/Д;#Н/Д;}): Эта функция ЕНД преобразует значения ошибок в ИСТИНА, а числа — в ЛОЖЬ, поэтому вы получите следующее: {ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА}.
• --(ЕНД(ПОИСКПОЗ(A2:A13;C2:C4;0))))=--({ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА}): Двойной отрицательный оператор -- преобразует все ИСТИНЫ в 1, а ЛОЖЬ в 0, как это: СУММПРОИЗВ(--(ЕНД(ПОИСКПОЗ(A2:A13;C2:C4;0))))=СУММПРОИЗВ({0;1;1;0;0;0;1;1;0;1;1;1}).
• Затем функция СУММПРОИЗВ используется для суммирования чисел в массиве, и результат будет: 7.

Подсчет количества ячеек, не равных множеству значений с помощью формулы2

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

• диапазон: Список ячеек, которые вы хотите подсчитать;
• значения_для_исключения: Значения, которые нужно игнорировать из списка диапазона.

1. Пожалуйста, введите или скопируйте приведенную ниже формулу в пустую ячейку для вывода результата:

2. Затем нажмите клавишу Enter, чтобы получить результат, как показано на следующем скриншоте:

Объяснение формулы:

• СЧЁТЗ(A2:A13): Эта функция СЧЁТЗ возвращает количество всех непустых ячеек в диапазоне A2:A13. Результат будет: 12.
• СЧЁТЕСЛИ(A2:A13;C2:C4): Эта функция СЧЁТЕСЛИ подсчитывает ячейки в диапазоне A2:A13, которые равны критерию в C2:C4, поэтому вы получите такой результат: {2;1;2}, что означает, что Apple встречается дважды, Grape один раз и Lemon дважды.
• СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A13;C2:C4))=СУММПРОИЗВ({2;1;2}): Эта функция СУММПРОИЗВ суммирует все элементы в массиве и возвращает окончательный результат: 5.
• СЧЁТЗ(A2:A13)-СУММПРОИЗВ(СЧЁТЕСЛИ(A2:A13;C2:C4))=12-5 :Результат (5) функции СУММПРОИЗВ вычитается из количества всех непустых ячеек (12), чтобы получить итоговый результат.

Примечание: Обе вышеупомянутые формулы не чувствительны к регистру.

Используемые связанные функции:

Больше статей: