Перейти к основному содержанию

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

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

Популярные опции: Найдите, выделите или определите дубликаты  |  Удалить пустые строки  |  Объедините столбцы или ячейки без потери данных  |  Раунд без формулы ...
Супер ВПросмотр: Несколько критериев  |  Множественное значение  |  На нескольких листах  |  Нечеткий поиск...
Адв. Выпадающий список: Простой раскрывающийся список  |  Зависимый раскрывающийся список  |  Выпадающий список с множественным выбором...
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  Сравнить столбцы с Выберите одинаковые и разные ячейки ...
Рекомендуемые функции: Сетка Фокус  |  Просмотр дизайна  |  Большой Формулный Бар  |  Менеджер книг и листов | Библиотека ресурсов (Авто текст)  |  Выбор даты  |  Комбинировать листы  |  Шифровать/дешифровать ячейки  |  Отправлять электронные письма по списку  |  Суперфильтр  |  Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы ...)  |  50+ График Тип (Диаграмма Ганта ...)  |  40+ Практических Формулы (Рассчитать возраст по дню рождения ...)  |  19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути ...)  |  12 Конверсия Инструменты (Числа в слова, Конверсия валюты ...)  |  7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить ячейки Excel ...)  |  ... и более

Kutools для Excel может похвастаться более чем 300 функциями, Гарантия того, что то, что вам нужно, находится на расстоянии одного клика...


Вкладка Office - включение чтения и редактирования с вкладками в Microsoft Office (включая Excel)

  • Одна секунда для переключения между десятками открытых документов!
  • Уменьшите количество щелчков мышью на сотни каждый день, попрощайтесь с рукой мыши.
  • Повышает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
  • Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations