Самая длинная победная серия

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

Самая длинная выйгрышная серия

Другими словами, определить максимальное количество идущих подряд значений, удовлетворяющих заданному условию. Иногда такую задачу еще называют нахождением "самой длинной победной серии", приводя в пример непрерывную цепочку последовательных побед в спортивных соревнованиях. В английском языке есть даже специальный термин - "winning streak" для такого случая.

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

Как работает функция ЧАСТОТА FREQUENCY

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

  1. сначала мы выделяем диапазон пустых ячеек для результатов (зеленые в примере выше) - на одну больше, чем границ интервалов (желтые)
  2. затем вводим нашу функцию, указывая два ее аргумента - диапазон данных (A1:A20) и диапазон интервалов (E4:E6)
  3. и жмем сочетание Ctrl+Shift+Enter, чтобы ввести функцию как формулу массива
Как же эта функция может помочь нам в решении задачи о нахождении самой длинной победной серии?

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

Числовая последовательность

Что изменилось:

  • Диапазон исходных данных расположен по горизонтали, а не по вертикали.
  • Теперь это не случайным образом перемешанные числа, а возрастающая последовательность 1,2,3... полученная с помощью функции СТОЛБЕЦ (COLUMN), выдающей номер столбца для текущей ячейки.
  • Допустим, для примера, что числа 4,9,16 - это границы интересующих нас диапазонов. Я "выдернул" их из исходной последовательности в отдельный ряд.
Если нам нужно подсчитать количество чисел из исходной последовательности 1..20, попадающих в каждый из интервалов (1-4, 4-9, 9-16, 16-20), то функцию ЧАСТОТА можно ввести как:

Функция ЧАСТОТА для возрастающей последовательности

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

Самая длинная победная серия

В английском варианте это будет =MAX(FREQUENCY(IF(B3:H3>0;COLUMN(B3:H3));IF(B3:H3=0;COLUMN(B3:H3))))

Разберем эту формулу по кусочкам:

  • ЕСЛИ(B3:H3>0;СТОЛБЕЦ(B3:H3)) - проверяет, чтобы ячейка была >0 и выводит номер её столбца. Для первого сотрудника на выходе получим массив {2;ЛОЖЬ;4;5;6;ЛОЖЬ;8}, который потом используем как диапазон исходных данных для функции ЧАСТОТА.
  • ЕСЛИ(B3:H3=0;СТОЛБЕЦ(B3:H3)) - то же самое, но наборот - выводим номера столбцов для нерабочих дней. На выходе получим массив {ЛОЖЬ;3;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;7;ЛОЖЬ}, который потом подставим в функцию ЧАСТОТА как диапазон интервалов.
  • И поскольку нам нужна самая длинная последовательность, то функцию ЧАСТОТА мы вкладываем внутрь функции МАКС.

И не забудьте после ввода формулы нажать сочетание Ctrl+Shift+Enter, чтобы ввести ее как формулу массива, конечно же.

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

Вот и все. Не так уж и сложно, не правда ли? ;)

Ссылки по теме




03.11.2017 02:14:31
Николай, спасибо за очередной доходчивый урок!
Подскажите пожалуйста, а как применять данную функцию если данные будут сформированы в столбцы и будут браться из умной таблицы? ведь простой заменой диапазона на =Таблица1[#Данные]  не обойдется?
и в продолжение вашей темы: как подсчитать сумму отработанных часов в самой продолжительной серии? ведь в теории может быть 2 наибольших одинаковых по величине интервала и разница между ними как раз может быть в количестве часов.
02.12.2017 01:28:31
Есть похожая задача. В требованиях трудового законодательства существует ограничение по продолжительности рабочей недели. Нужно найти в табеле отработанного времени максимальную сумму отработанных часов за любые 7 дней подряд.
05.12.2017 23:25:12
Николай, спасибо!
А можете дать наводку как посчитать длинную победную серию по условию, например для конкретного работника?
Формулу я уже адаптировал для подсчета по строкам, а вот условие что для конкретного работника - никак не могу "запилить".
Спасибо!
22.04.2018 12:12:49
Эврика!
Премного благодарен! Как раз мой случай! ;)
22.04.2018 14:59:48
У меня возникла проблема...
Есть диапазон, в нем отмечается "промах" - 0, и "попадание" - 1.
Всё отлично, я изменил формулу и получился подсчет промахов.
Но пустые ячейки в диапазоне так же считаются за "0".
Пробовал вставить и СЧЁТ и СЧЁТЕСЛИ... но видимо вставляю их куда-то не в то место или они здесь не применимы.
Факт - не получается исключить из диапазона пустые ячейки.
Как реализовать, что бы подсчитывать только самую длинную серию "0", но исключить пустые ячейки.
Пока я могу только заполнять наоборот. А это не совсем то, что надо, ибо потом я считаю "1" в других формулах.

{=МАКС(ЧАСТОТА(
ЕСЛИ(
СЧЁТ(A1:BH1);
ЕСЛИ(
A1:BH1=0;
СТОЛБЕЦ(A1:BH1)
));
ЕСЛИ(
СЧЁТ(A1:BH1);
ЕСЛИ(
A1:BH1=1;
СТОЛБЕЦ(A1:BH1)
))
))
}
Наверх