Как посчитать количество положительных чисел в excel

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

Пусть имеется столбец со значениями, среди которых есть нечетные значения. Необходимо подсчитать максимальное количество идущих подряд нечетных значений (выделены светло коричневой заливкой). В данном примере максимальное количество идущих подряд нечетных значений = 3.

Если диапазон значений находится в ячейках A8:A15 , то формула будет выглядеть так (см. файл примера ):

Диапазон в формуле должен быть на 1 ячейку больше, это не опечатка. Иначе, в случае, если максимальное количество подряд идущих нечетных значений будет в самом конце списка, то формула вернет неправильный результат (на 1 меньше). Формулу нужно вводить как формулу массива .

Разберем работу формулы подробнее. Вместо формулы массива используем дополнительные столбцы:

В качестве первого аргумента функции ЧАСТОТА() — массива данных, используем номера строк, в ячейки которых введены исходные значения (столбец G). В качестве второго аргумента — массива интервалов, используем номера тех же строк, но если значение нечетное, то вместо номера строк выведем 0 (см. столбец Н). Т.к. функция ЧАСТОТА() перед вычислением сортирует по возрастанию массив интервалов, то для наглядности сделаем тоже в столбце I. В столбце J для удобства приведен перечень диапазонов, в которых функция ЧАСТОТА() подсчитывает количество попавших в них значений из массива данных. Т.к. в столбце I нет номеров строк, в которых содержатся нечетные значения, то разницы между границами интервалов будут нам давать количества подряд идущих нечетных значений. Именно это и делает функция ЧАСТОТА() , когда подсчитывает количества значений (номеров строк), попавших в эти интервалы. Далее необходимо вычислить максимальное значение и вычесть из него 1. Этот пример показывает нестандартное применение функции ЧАСТОТА() — поэтому, алгоритм работы всей формулы не прозрачен и разобраться в нем сложновато.

Другая формула, еще более сложная для понимания, но также возвращающая верный результат:

Для нахождения максимального количества идущих подряд ЧЁТНых значений используйте формулу:

Для нахождения максимального количества идущих подряд положительных значений используйте формулу:

= МАКС(ЧАСТОТА(СТРОКА( A8:A16 );( A8:A16 <=0)*СТРОКА( A8:A16 )))-1

Для нахождения максимального количества идущих подряд значений =1 используйте формулу:

= МАКС(ЧАСТОТА(СТРОКА( A8:A16 );( A8:A16 <>1)*СТРОКА( A8:A16 )))-1

Как видно из приведенных выше примеров, структура формулы остается без изменения, изменяется лишь выражение, ответственное за нахождение значений НЕудовлетворяющих заданному условию ( НЕЧЁТ(A8:A16)<>A8:A16 или ЧЁТН(A8:A16)<>A8:A16 или A 8:A16 <=0 или A 8:A16 <>1 ). Т.е. при желании можно настроить формулу для своих потребностей.

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

В этом учебном материале по Excel мы рассмотрим примеры того, как подсчитать ячейки, которые содержат числа больше нуля.

Основная формула

Описание

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

Пояснение

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

СЧЕТЕСЛИ в Excel — примеры функции с одним и несколькими условиями

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

По сути,они одинаковы во всех версиях, поэтому вы можете использовать примеры в MS Excel 2016, 2013, 2010 и 2007.

Функция Excel СЧЕТЕСЛИ применяется для подсчета количества ячеек в указанном диапазоне, которые соответствуют определенному условию.

Например, вы можете воспользоваться ею, чтобы узнать, сколько ячеек в вашей рабочей таблице содержит число, больше или меньше указанной вами величины. Другое стандартное использование — для подсчета ячеек с определенным словом или с определенной буквой (буквами).

СЧЕТЕСЛИ(диапазон; критерий)

Как видите, здесь только 2 аргумента, оба из которых являются обязательными:

диапазон — определяет одну или несколько клеток для подсчета. Вы помещаете диапазон в формулу, как обычно, например, A1: A20.
критерий — определяет условие, которое определяет, что именно считать. Это может быть число, текстовая строка, ссылка или выражение. Например, вы можете употребить следующие критерии: «10», A2, «> = 10», «какой-то текст».

Что нужно обязательно запомнить?

В аргументе «критерий» условие всегда нужно записывать в кавычках, кроме случая, когда используется ссылка либо какая-то функция.
Любой из аргументов ссылается на диапазон из другой книги Excel, то эта книга должна быть открыта.
Регистр букв не учитывается.
Также можно применить знаки подстановки * и ? (о них далее – подробнее).
Чтобы избежать ошибок, в тексте не должно быть непечатаемых знаков.

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

Примеры работы функции СЧЕТЕСЛИ.

Для подсчета текста.

Давайте разбираться, как это работает. На рисунке ниже вы видите список заказов, выполненных менеджерами. Выражение =СЧЕТЕСЛИ(В2:В22,»Никитенко») подсчитывает, сколько раз этот работник присутствует в списке:

применение СЧЕТЕСЛИ

Замечание. Критерий не чувствителен к регистру букв, поэтому можно вводить как прописные, так и строчные буквы.

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

К примеру, в нашей таблице есть несколько заказчиков «Корона» из разных городов. Нам необходимо подсчитать общее количество заказов «Корона» независимо от города.

Мы подсчитали количество заказов, где в наименовании заказчика встречается «коро» в любом регистре. Звездочка (*) используется для поиска ячеек с любой последовательностью начальных и конечных символов, как показано в приведенном выше примере. Если вам нужно заменить какой-либо один символ, введите вместо него знак вопроса (?).

Кроме того, указывать условие прямо в формуле не совсем рационально, так как при необходимости подсчитать какие-то другие значения вам придется корректировать её. А это не слишком удобно.

Рекомендуется условие записывать в какую-либо ячейку и затем ссылаться на нее. Так мы сделали в H9. Также можно употребить подстановочные знаки со ссылками с помощью оператора конкатенации (&). Например, вместо того, чтобы указывать «* Коро *» непосредственно в формуле, вы можете записать его куда-нибудь, и использовать следующую конструкцию для подсчета ячеек, содержащих «Коро»:

Подсчет ячеек, начинающихся или заканчивающихся определенными символами

Вы можете употребить подстановочный знак звездочку (*) или знак вопроса (?) в зависимости от того, какого именно результата вы хотите достичь.

Если вы хотите узнать количество ячеек, которые начинаются или заканчиваются определенным текстом, независимо от того, сколько имеется других символов, используйте:

=СЧЁТЕСЛИ(A2:A22;»К*») — считать значения, которые начинаются с « К» .

=СЧЁТЕСЛИ(A2:A22;»*р») — считать заканчивающиеся буквой «р».

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

=СЧЁТЕСЛИ(С2:С22;». д») — находит количество буквой «д» в конце и текст в которых состоит из 5 букв, включая пробелы.

= СЧЁТЕСЛИ(С2:С22,»??») — считает количество состоящих из 2 символов, включая пробелы.

Примечание. Чтобы узнать количество клеток, содержащих в тексте знак вопроса или звездочку, введите тильду (

) перед символом ? или *.

?*») будут подсчитаны все позиции, содержащие знак вопроса в диапазоне С2:С22.

Подсчет чисел по условию.

В отношении чисел редко случается, что нужно подсчитать количество их, равных какому-то определённому числу. Тем не менее, укажем, что записать нужно примерно следующее:

Гораздо чаще нужно высчитать количество значений, больших либо меньших определенной величины.

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

Обратите внимание, что математический оператор вместе с числом всегда заключен в кавычки .

критерии

Описание

Если больше, чем

Подсчитайте, где значение больше 5.

Если меньше чем

Подсчет со числами менее 5.

Определите, сколько раз значение равно 5.

Подсчитайте, сколько раз не равно 5.

Если больше или равно

Подсчет, когда больше или равно 5.

Если меньше или равно

Подсчет, где меньше или равно 5.

В нашем примере

Считаем количество крупных заказов на сумму более 10 000. Обратите внимание, что условие подсчета мы записываем здесь в виде текстовой строки и поэтому заключаем его в двойные кавычки.

Вы также можете использовать все вышеприведенные варианты для подсчета ячеек на основе значения другой ячейки. Вам просто нужно заменить число ссылкой.

Замечание. В случае использования ссылки, вы должны заключить математический оператор в кавычки и добавить амперсанд (&) перед ним. Например, чтобы подсчитать числа в диапазоне D2: D9, превышающие D3, используйте =СЧЕТЕСЛИ(D2:D9,»>»&D3)

Если вы хотите сосчитать записи, которые содержат математический оператор, как часть их содержимого, то есть символ «>», «<» или «=», то употребите в условиях подстановочный знак с оператором. Такие критерии будут рассматриваться как текстовая строка, а не числовое выражение.

Например, =СЧЕТЕСЛИ(D2:D9,»*>5*») будет подсчитывать все позиции в диапазоне D2: D9 с таким содержимым, как «Доставка >5 дней» или «>5 единиц в наличии».

Примеры с датами.

Если вы хотите сосчитать клетки с датами, которые больше, меньше или равны указанной вами дате, вы можете воспользоваться уже знакомым способом, используя формулы, аналогичные тем, которые мы обсуждали чуть выше. Все вышеприведенное работает как для дат, так и для чисел.