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

Подсчитаем количество подряд идущих значений в столбце. Рассмотрим неповторяющиеся серии значений (111222234444...) и повторяющиеся (110111000010 и 110221010112).

Пусть имеется столбец с повторяющимися значениями .

Неповторяющиеся серии значений

Предположим, что имеется столбец дат. См. файл примера . Даты отсортированы по возрастанию (для удобства одинаковые даты выделены условным форматированием ).

Сформируем в столбце С список уникальных значений (даты без повторов). С помощью формулы =СЧЁТЕСЛИ($A$8:$A$23;C8) найдем количество подряд идущих значений дат.

Задача усложняется, если серии значений могут повторяться.

Серии повторяются (2 разных значения)

Рассмотрим серию вида 00010110111... в диапазоне А29:А48 .

К такому виду серий можно свести следующие задачи (серии положительных значений, серии значений >5%, даты попадающие в определенный интервал и т.п.).

Подсчитаем количество серий, а также найдем длину серии (количество подряд идущих значений).

С помощью формулы = ЕСЛИ(A29<>A30;СЧЁТЕСЛИ($A$29:A30;A29)-СУММЕСЛИ($A$28:A28;A29;$B$28:B28);"") подсчитаем длину каждой серии. Формулу разместим в столбце В .

Для работоспособности формулы необходимо, чтобы была заполнена желтая ячейка А49 (иначе, если последняя серия состоит из 1 значения, то формула не произведет подсчет ее длины).

Чтобы подсчитать длины серий нулей используем формулу массива :

=ЕСЛИОШИБКА(ИНДЕКС(Длины_серий;НАИМЕНЬШИЙ(ЕСЛИ((Значения=0)*(Длины_серий<>"");СТРОКА(Длины_серий);"");СТРОКА()-СТРОКА($E$28))-СТРОКА($E$28));"")

Аналогичную формулу запишем для подсчету серий единиц:

= ЕСЛИОШИБКА(ИНДЕКС(Длины_серий;НАИМЕНЬШИЙ(ЕСЛИ((Значения=1)*(Длины_серий<>"");СТРОКА(Длины_серий);"");СТРОКА()-СТРОКА($G$28))-СТРОКА($G$28));"")

Для удобства созданы именованные диапазоны Значения и Длины_серий .

Серии повторяются (3 разных значения)

Рассмотрим серию вида 110221010112 ... в диапазоне А55:А74 .

Аналогично предыдущей задаче найдем длины серий:

С помощью формулы = ЕСЛИ(A55<>A56;СЧЁТЕСЛИ($A$29:A56;A55)-СУММЕСЛИ($A$28:A54;A55;$B$28:B54);"") подсчитаем длину каждой серии. Формулу разместим в столбце В .

Чтобы подсчитать длины серий нулей используем формулу массива :

= ЕСЛИОШИБКА(ИНДЕКС(Длины_серий3;НАИМЕНЬШИЙ(ЕСЛИ((Значения3=0)*(Длины_серий3<>"");СТРОКА(Длины_серий3);"");СТРОКА()-СТРОКА($E$54))-СТРОКА($E$54));"")

Для удобства созданы именованные диапазоны Значения3 и Длины_серий3 .