Общая формула
(=MAX(FREQUENCY(IF(rng>0,COLUMN(rng)),IF(rng=0,COLUMN(rng)))))
Резюме
Для подсчета последовательных ежемесячных заказов вы можете использовать формулу массива, основанную на функции ЧАСТОТА, с помощью COLUMN и MAX.
В показанном примере формула I5 имеет следующий вид:
(=MAX(FREQUENCY(IF(C5:H5>0,COLUMN(C5:H5)),IF(C5:H5=0,COLUMN(C5:H5)))))
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter.
Объяснение
Это сложная формула для понимания, так что пристегнитесь!
Ключом к формуле является знание того, что FREQUENCY собирает числа в «ячейки» определенным образом. Каждая ячейка представляет собой верхний предел и генерирует подсчет всех чисел в наборе данных, которые меньше или равны верхнему пределу и больше, чем предыдущий номер ячейки. Уловка состоит в том, чтобы создать data_array с использованием условия, которое вы хотите проверить (в данном случае количество заказов больше нуля), и bins_array с использованием противоположного условия.
Для создания бункера data_array мы используем следующее:
IF(C5:H5>0,COLUMN(C5:H5))
Мы проверяем количество заказов в каждом месяце и, в случае положительного результата, возвращаем номер столбца, где количество заказов> 0. Результирующий массив выглядит следующим образом:
(3; ЛОЖЬ; ЛОЖЬ; 6,7,8)
Обратите внимание, что в этот массив попадают только столбцы, в которых количество порядков> 0.
Массив бинов генерируется следующим образом:
IF(C5:H5=0,COLUMN(C5:H5))
Это помещает номера столбцов для количества заказов = 0 в массив, который заканчивается следующим образом:
(ЛОЖЬ; 4,5; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ)
Только столбцы, в которых количество порядков = 0, попадают в этот массив, где в соответствии со стандартным поведением FREQUENCY они становятся функциональными ячейками, которые подсчитывают ненулевые порядки. Выигрыши переводятся в FALSE и не собирают никаких чисел из массива данных, поскольку значения FALSE игнорируются.
С массивами данных и массивами ячеек выше, частота возвращает массив счетчиков на ячейку в таком массиве:
(1; 0; 3)
Теперь мы просто оборачиваем функцию MAX вокруг массива, возвращаемого FREQUENCY. Затем MAX возвращает наивысшее число в массиве в качестве окончательного результата.
Другие последовательные значения
Чтобы подсчитать последовательные вхождения других значений, просто настройте логику по необходимости, следуя той же схеме: первое условие проверяет то, что вы хотите подсчитать, второе условие проверяет обратное.
