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

Для подсчета количества ячеек, содержащих определенный текст, вы можете использовать функцию СЧЁТЕСЛИ. В общей форме формулы (выше), RNG является диапазон ячеек, TXT представляет собой текст, который должны содержать ячейки, и "*" является подстановочным символом, соответствующим любому количеству символов.

В примере, активная ячейка содержит следующую формулу:

СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые содержат "а" путем сопоставления содержимого каждой ячейки с шаблоном "*a*", который поставляется в качестве критериев. Символ "*" (звездочка) является подстановочным в Excel, что означает "совпадают с любым количеством символов", так что эта модель будет считать любую ячейку, которая содержит "а" в любом положении. Количество ячеек, которые соответствуют этому шаблону рассчитывается как число.

Вы можете легко настроить эту формулу, чтобы использовать содержимое другой ячейки для критериев. Например, если A1 содержит текст, который соответствует тому, что вы хотите, используйте следующую формулу:

Когда вы подсчитывать ячейки с критерием "или", вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат "abc" или "def", вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и "abc" и "def".

Когда вы подсчитывать ячейки с критерием "или", вы должны быть осторожны, чтобы не удвоить счет. Например, если вы подсчитываете ячейки, которые содержат "abc" или "def", вы не можете просто сложить вместе две функции СЧЁТЕСЛИ, потому что вы можете удвоить подсчет ячеек, которые содержат и "abc" и "def".

Эта формула основана на формуле, которая находит текст внутри ячейки:

При заданном диапазоне ячеек, этот фрагмент будет возвращать массив значений Истина или Ложь, одно значение для каждой ячейки диапазона. Поскольку мы используем это дважды (один раз для "abc" и еще для "def"), мы получим два массива.

Далее мы складываем эти массивы вместе (+), сложение создаст новый единый массив чисел. Каждое число в этом массиве является результатом сложения истинных и ложных значений в исходных двух массивах вместе. В показанном примере, массив выглядит следующим образом:

Нам нужно сложить эти цифры, но мы не хотим, чтобы удвоился счет. Таким образом, мы должны убедиться, что любое значение больше нуля. Чтобы сделать это, мы вернем все значения, которые больше 0, в Истина или Ложь, а затем с помощью двойного отрицания (--) переведем массив в формат 1 и 0.
И, наконец, СУММПРОИЗВ суммирует полученные числа.

Со вспомогательным столбцом для проверки каждой ячейки в отдельности, проблема менее сложная. Мы можем использовать СЧЁТЕСЛИ с двумя значениями (при условии, как "бесконечное множество"). Формула:

СЧЁТЕСЛИ возвращает массив, который содержит два пункта: подсчет для "abc" и подсчет на "def". Чтобы избежать двойного счета, мы складываем элементы, а потом возвращаем результат "истина/ложь" с ">0". Наконец, мы преобразуем значения Истина или Ложь в 1 и 0 с двойным минусом (--).

Итоговый результат равен 1 или 0 для каждой ячейки. Чтобы получить в общей сложности для всех ячеек в диапазоне, вам нужно просуммировать вспомогательный столбец.

Для подсчета количества ячеек, содержащих ошибки, вы можете использовать функцию ЕОШ, завернутую в функцию СУММПРОИЗВ. В общей форме формулы (выше) rng представляет собой диапазон ячеек, в которых вы хотели бы рассчитывать ошибки.

В примере, активная ячейка содержит следующую формулу:

СУММПРОИЗВ принимает один или несколько массивов и вычисляет сумму произведений соответствующих чисел. Если только один массив , он просто суммирует элементы в массиве.

Функция ЕОШ вычисляется для каждой ячейки в rng. Результатом является массив со значениями истина / ложь:

СУММПРОИЗВ затем суммирует элементы в этом массиве и возвращает общую сумму, которая, в данном примере, это число 2.
Примечание: ЕОШ подсчитывает все ошибки, кроме # N / A. Если вы хотите, чтобы также рассчитывалось # N / A, используйте функцию ЕОШИБКА вместо ЕОШ.

Вы можете также использовать функцию СУММ для подсчета ошибок. Структура формулы такая же, но она должна быть введена как формула массива (нажмите Ctrl + Shift + Enter, а не просто Enter). После ввода формула будет выглядеть следующим образом:

В примере, активная ячейка содержит следующую формулу:

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

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

В примере, активная ячейка содержит следующую формулу:

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

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

В примере, активная ячейка содержит следующую формулу:

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

Если вы хотите использовать значение в другой ячейке как часть критериев, используйте амперсанд (&) символ конъюнкции следующим образом:

Если в ячейке А1 находится значение "-5", критерии будут "<-5" после конъюнкции.

Если в ячейке А1 находится значение "-5", критерии будут "<-5" после конъюнкции.

В примере, активная ячейка содержит следующую формулу:

Для подсчета ячеек, которые содержат только нечетные числа, вы можете использовать формулу, основанную на функции СУММПРОИЗВ вместе с функцией ОСТАТ.

В примере, формула в ячейке E4 является:

В примере, форм

Эта формула рассчитала 4, так как есть 4 нечетных числа в диапазоне В5: В10 (который назван "rng" в формуле).

Функция СУММПРОИЗВ непосредственно работает с массивами.

Одна вещь, которую вы можете сделать довольно легко с СУММПРОИЗВ это выполнить тест на массив, используя один или несколько критериев, затем подсчитать результаты.

В этом случае, мы проводим тест на нечетное число, который использует функцию ОСТАТ:

ула в ячейке E4 является:

ОСТАТ рассчитывает остаток от деления. В этом случае делитель равен 2, поэтому ОСТАТ рассчитывает остаток 1 для любого нечетного числа, а остаток 0 для четных чисел.
В функции СУММПРОИЗВ, этот тест выполняется в каждой ячейке B5: B10, результат представляет собой массив значений истина / ложь:

После того, как мы присвоили значения истина / ложь числам с помощью двойного отрицания, мы получили:

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

СУММПРОИЗВ затем просто суммирует эти числа и рассчитывает 4.

СЧЁТЕСЛИ подсчитывает количество ячеек, которые соответствуют критериям. В этом случае критерий поставляется в качестве шаблонного символа "*", который совпадает с любым количеством символов текста.

Несколько замечаний:
- Логические значения истина и ложь не учитываются, как текст
- Числа не подсчитываются "*", если они не будут введены в виде текста
- Пустая клетка, которая начинается с апострофа ( ') будут учитываться.

Вы можете также использовать СУММПРОИЗВ для подсчета текстовых значений наряду с функцией ЕТЕКСТ так:

Чувствительная к регистру версия

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

НАЙТИ чувствительна к регистру, и вы должны дать ему диапазон ячеек, а затем использовать СУММПРОИЗВ для подсчета результатов. Формула выглядит следующим образом:

Там, где текст является текстом, который вы ищете, и rng диапазон ячеек, которые вы хотите подсчитать. Там нет необходимости использовать групповые символы, так как НАЙТИ возвратит число, если текст найден в любом месте в ячейке.