Подсчет максимального количества подряд идущих значений в MS 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 или A8:A16<=0 или A8:A16<>1). Т.е. при желании можно настроить формулу для своих потребностей.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)

Комментарии

Павел Польс (не проверено)

Добрый день. Подскажите, как адаптировать эту формулу для определения количества ячеек, идущих подряд, с определенным значением в СТРОКЕ?

Creator

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

В ячейку D1 введите Ваше значение. Например, если в диапазоне А8:А15 содержится массив: {3:1:2:9:6:3:3:7}, а в D1 введено значение 3, то вышеуказанная вернет 2, т.к. в указанном массиве две тройки подряд - это максимальное количество идущих подряд троек. Если в ячейку D1 ввести 99, то формула вернет 0 (нет значений 99), а если 9, то 1, т.к. в массиве только одно значение 9.

tiran12 (не проверено)

Супер, очень помогло. Спасибо!!!!!!

zlobkinavv

Добрый день, подскажите пожалуйста, как адаптировать формулу "=МАКС(ЧАСТОТА(СТРОКА(A8:A16);(A8:A16<=0)*СТРОКА(A8:A16)))-1", если необходимо посчитать количество идущих подряд положительных значений для конкретного товара. Перечень товаров указан в столбце А:А, при этом все товары по столбцу расположены в случайной последовательности, интересующая динамика располагается в столбце D:D.

Creator

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