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

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

Как подсчитать ячейки, которые не содержат множество значений?

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

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

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

Аргументы

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

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

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

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

=СУММ(1-(МУМНОЖ(--(ЕЧИСЛО(ПОИСКПОЗ(ТРАНСП(D3:D4);B3:B11));СТРОКА(D3:D4)^0)>0))

Как работают эти формулы?

=СУММ(1-(МУМНОЖ(--(ЕЧИСЛО(ПОИСКПОЗ(ТРАНСП(D3:D4);B3:B11));СТРОКА(D3:D4)^0)>0))

1) --(ЕЧИСЛО(ПОИСКПОЗ(ТРАНСП(D3:D4);B3:B11))):

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

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

3) МУМНОЖ({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) СУММ(1-{0;0;1;1;1;0;0;1;2}>0):

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

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

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