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

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

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

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

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

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

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

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

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

=МАКС(МУМНОЖ({-1;1};НАИМЕНЬШИЙ((НЕЧЁТ(A8:A16)<>A8:A16)*(СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(A8:A16))));ТРАНСП(СТРОКА(ДВССЫЛ("A2:A"&ЧСТРОК(A8:A16))))-{1:0}))-1)

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

= МАКС(ЧАСТОТА(СТРОКА(A8:A16);(ЧЁТН(A8:A16)<>A8:A16)*СТРОКА(A8:A16)))-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 ). Т.е. при желании можно настроить формулу для своих потребностей.