Подсчитать количество ячеек, не содержащих много значений
Обычно легко подсчитать ячейки, которые не содержат одно конкретное значение, с помощью функции СЧЁТЕСЛИ. В этом посте будет рассказано, как подсчитать количество ячеек, которые не содержат много значений в указанном диапазоне в 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): Функция TRANSPOSE поворачивает ориентацию D3: D4 и возвращает {"Count", "blank"};
- SEARCH({“count”,”blank”},B3:B11): Функция ПОИСК здесь находит позицию подстроки «count» и «blank» из диапазона B3: B11 и возвращает массив как {#VALUE!, # VALUE!; # VALUE!, # VALUE!; 1, # VALUE!; # VALUE!, 8; 1, # VALUE!; # VALUE!, # VALUE!; # VALUE!, # VALUE! ; 1,
#VALUE!; 1,7}. - В этом случае каждая ячейка в B3: B11 будет просматриваться дважды, поскольку есть два значения, которые вы хотите исключить при подсчете ячеек, поэтому вы получите 18 значений в массиве. Каждое число в массиве указывает позицию первого символа «счетчик» или «пробел» в каждой ячейке 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}):Функция MMULT возвращает матричное произведение этих двух массивов: {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. Если число больше 0, возвращает ИСТИНА, если нет, возвращает ЛОЖЬ. Тогда вы получите новый массив как {ЛОЖЬ; ЛОЖЬ; ИСТИНА; ИСТИНА; ИСТИНА; ЛОЖЬ, ЛОЖЬ, ИСТИНА; ИСТИНА}.
- 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}: Поскольку нам нужно подсчитывать только ячейки, которые не содержат указанных значений, нам нужно обратить эти значения в массиве, вычитая их из 1. Здесь математический оператор автоматически преобразует значения ИСТИНА и ЛОЖЬ в единицы и нули и, наконец, возвращает результат как {1;1;0;0;0;1;1;0;0}.
- SUM{1;1;0;0;0;1;1;0;0}: Функция СУММ суммирует все числа в массиве и возвращает окончательный результат как 4.
Связанные функции
Функция СУММ в Excel
Функция СУММ в Excel складывает значения.
Функция ММУМНОГ в Excel
Функция Excel MMULT возвращает матричное произведение двух массивов.
ЕЧИСЛО в Excel
Функция Excel ЕЧИСЛО возвращает ИСТИНА, если ячейка содержит число, и ЛОЖЬ, если нет.
Функция ТРАНСПОРТ в Excel
Функция Excel TRANSPOSE изменяет ориентацию диапазона или массива.
Функция СТРОКА в Excel
Функция Excel ROW возвращает номер строки ссылки.
Родственные формулы
Подсчитайте ячейки, которые не содержат ошибок
В этом руководстве представлены подробные инструкции, которые помогут вам подсчитать количество ячеек, не содержащих ошибок, в указанном диапазоне в Excel.
Подсчет ячеек, не содержащих определенного текста
Вы можете использовать функцию СЧЁТЕСЛИ с подстановочным знаком, чтобы подсчитать количество ячеек, содержащих определенный текст в диапазоне. Напротив, также легко использовать функцию СЧЁТЕСЛИ для подсчета ячеек, не содержащих определенного текста. В этом руководстве подробно описаны шаги, которые помогут решить проблему.
Подсчитать количество ячеек даты в диапазоне по дням недели
Excel предоставляет комбинацию функций СУММПРОИЗВ и ДЕНЬ НЕДЕЛИ, чтобы помочь нам легко подсчитать количество указанных дней недели в пределах диапазона. Это руководство представляет собой пошаговое руководство, которое поможет вам разобраться в этом.
Подсчитайте количество текстовых ячеек
Чтобы подсчитать количество ячеек, содержащих текст в указанном диапазоне, функция СЧЁТЕСЛИ может помочь легко это сделать. В этой статье мы подробно расскажем о формуле, которая поможет вам решить проблему.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...
Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)
- Одна секунда для переключения между десятками открытых документов!
- Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
- Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
