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

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

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

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

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

• range: список ячеек, которые необходимо подсчитать;
• exclude_values: значения, которые необходимо игнорировать в диапазоне списка.

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

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

Пояснение формулы:

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

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

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

• range: список ячеек, которые необходимо подсчитать;
• exclude_values: значения, которые следует игнорировать в списке диапазона.

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

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

Пояснение формулы:

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

Примечание: обе приведённые выше формулы не учитывают регистр.

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

Другие статьи: