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

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

Как подсчитать ячейки, не содержащие нескольких значений?

Как показано на рисунке ниже, чтобы подсчитать ячейки в диапазоне B3:B11, не содержащие значений из диапазона D3:D4, выполните следующие действия.

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

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>,0))}

Аргументы

Примечание: Эту формулу необходимо вводить как формулу массива. Если после ввода она автоматически заключается в фигурные скобки — значит, формула массива создана успешно.

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

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

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

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>,0))

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

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>,0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4):Функция ТРАНСП изменяет ориентацию диапазона D3:D4 и возвращает {«count»,«blank»};
• SEARCH({“count”,”blank”},B3:B11): В данном случае функция ПОИСК находит позиции подстрок «count» и «blank» в диапазоне B3:B11 и возвращает массив вида {#ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 1; #ЗНАЧ!; #ЗНАЧ!; 8; 1; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; #ЗНАЧ!; 1;
  #ЗНАЧ!; 1,7}.
• В данном случае каждая ячейка диапазона B3:B11 проверяется дважды — по одному разу для каждого из двух значений, исключаемых при подсчёте. В результате массив содержит 18 значений, где каждое число указывает позицию первого символа подстроки «count» или «blank» в соответствующей ячейке диапазона B3:B11.
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}:Функция ЕЧИСЛО возвращает ИСТИНА для чисел в массиве и ЛОЖЬ для ошибок. В данном случае результат будет следующим:{ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ИСТИНА;
  ИСТИНА;ИСТИНА}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,
  FALSE;TRUE,TRUE}):Эти два знака минуса преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. В результате вы получите новый массив:{0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0: Функция СТРОКА возвращает номера строк для ссылки на диапазон ячеек: {3;4}. Затем оператор возведения в степень (^) возводит числа 3 и 4 в степень 0, в результате чего получается: {1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}): Функция МУМНОЖ возвращает матричное произведение этих двух массивов: {0;0;1;1;1;0;0;1;2}, что позволяет сопоставить результат с исходными данными. Любое ненулевое значение в массиве означает, что была найдена хотя бы одна из исключаемых строк, а ноль — что ни одна из них не обнаружена.

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0: Здесь проверяется, больше ли каждое число в массиве нуля. Если число больше 0 — возвращается ИСТИНА, в противном случае — ЛОЖЬ. В результате вы получите новый массив: {ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА;ИСТИНА;ЛОЖЬ;ЛОЖЬ;ИСТИНА;ИСТИНА}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Поскольку нужно подсчитать только те ячейки, которые не содержат указанных значений, значения в массиве следует инвертировать — для этого их вычитают из 1. Арифметический оператор автоматически преобразует логические значения ИСТИНА и ЛОЖЬ в 1 и 0 соответственно, в результате чего получается следующий массив: {1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}Функция СУММ суммирует все числа в массиве и возвращает итоговый результат — 4.

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

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