Загальна формула
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Резюме
Для підрахунку послідовних щомісячних замовлень ви можете використовувати формулу масиву, засновану на функції FREQUENCY, за допомогою COLUMN та MAX.
У наведеному прикладі формула в I5 має вигляд:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Примітка: це формула масиву, яку потрібно вводити за допомогою Control + Shift + Enter.
Пояснення
Це хитра формула для розуміння, тож пристебнись!
Ключовим для формули є знання того, що FREQUENCY збирає числа у "сміттєві ящики" певним чином. Кожен бункер представляє верхню межу і генерує підрахунок усіх чисел у наборі даних, які менше або дорівнюють верхній межі, і більше, ніж попереднє число бункера. Тоді фокус полягає у створенні масиву даних з використанням умови, для якої ви хочете перевірити (у цьому випадку кількість замовлень перевищує нуль), а масиву bins_array - протилежна умова.
Для створення контейнера data_array ми використовуємо наступне:
IF(C5:H5>0,COLUMN(C5:H5))
Ми перевіряємо кількість замовлень кожного місяця і, якщо це позитивне, повертаємо номер стовпця, де кількість замовлень> 0. Отриманий масив виглядає так:
(3, FALSE, FALSE, 6,7,8)
Зверніть увагу, що в цей масив потрапляють лише стовпці, кількість замовлення яких перевищує 0.
Масив bins генерується таким чином:
IF(C5:H5=0,COLUMN(C5:H5))
Це ставить номери стовпців для підрахунку замовлень = 0 у масив, який закінчується так:
(FALSE, 4,5, FALSE, FALSE, FALSE)
Тільки стовпці, де кількість замовлень = 0, потрапляють у цей масив, де відповідно до стандартної поведінки FREQUENCY вони стають функціональними бінами, що складають ненульові замовлення. Виграші перекладаються на FALSE і не збирають жодних чисел із масиву даних, оскільки значення FALSE ігноруються.
З наведеними вище масивами даних та масивами bin, частота повертає масив підрахунків на кожний бін у масиві, як це:
(1; 0; 3)
Тепер ми просто обертаємо функцію MAX навколо масиву, що повертається FREQUENCY. Потім MAX повертає найбільше число в масиві як кінцевий результат.
Інші послідовні значення
Щоб підрахувати послідовні випадки появи інших значень, просто відрегулюйте логіку за необхідності за тією ж схемою: перша умова перевіряє те, що ви хочете порахувати, друга умова перевіряє протилежне.
