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

history

Подсчитаем количество подряд идущих значений в столбце. Рассмотрим неповторяющиеся серии значений (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 .


Комментарии

Только для авторизованных пользователей

Аноним, 21 августа 2020 г.
А можно ли формулу видоизменить на меняющийся диапазон. Допустим, если необходимо считать свежую серию. Или нужно для этого двигать диапазон при обрыве серии
Михаил, 21 августа 2020 г.
Сделал для неповторяющихся серий. См. лист Динамический
Аноним, 22 августа 2020 г.
А вы молодец! Интересное решение! А вообще я думал что вопрос мой был послан в никуда, приятно что оперативно разобрались с моим вопросом
Аноним, 22 августа 2020 г.
Ваше решение конечно круто, но мой сложный случай не помогает решить(((
Михаил, 23 августа 2020 г.
напишите в группу https://vk.com/excel2ru
(только для авторизованных пользователей)

© Copyright 2013 - 2024 Excel2.ru. All Rights Reserved