☰

Формула Excel, подсчитывающая количество ячеек с момента последнего ненулевого значения

tag-icon • tag-icon microsoft-excel
Формула Excel, подсчитывающая количество ячеек с момента последнего ненулевого значения

У меня есть несколько столбцов, показывающих продажи за месяц. Например, следующий столбец может означать, что два месяца назад этот клиент совершил продажу на сумму 3555 долларов.

0,0,0,0,0,0,0,0,0,3555,0,0

Мне нужна формула для расчета количества месяцев с момента последней продажи, т.е. результат в данном случае должен быть 2.

решение1

Развивая дальше ответ Джука:

Предположим, что значения продаж за месяцы с 1 по 12 находятся в ячейках A1:L1.

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

=COLUMN(L1)-MAX(IF(A1:L1>0,COLUMN(A1:L1),0))

Введите формулу и введите Ctrl+ Shift+ Enter , чтобы обозначить ее как формулу массива. Excel автоматически отобразит фигурные скобки:

{=COLUMN(L1)-MAX(IF(A1:L1>0,COLUMN(A1:L1),0))}

(Не вводите скобки.)

Это создает виртуальный массив, который содержит номера столбцов для ячеек, где продажи больше нуля, и находит наибольший номер столбца (т.е. ближайший к концу отчетного периода). Затем нам нужно вычесть это значение из номера последнего столбца.

решение2

Вот быстрое решение для этого. Однако вам нужна вспомогательная строка, что означает, что есть место для улучшения. Может быть, кто-то другой сможет взять это отсюда.

Вот эта установка:

month 1 2 3 4 5 6 7 8 9 10 11 12
sale  0 0 0 0 5 0 0 0 8 11 0  0 
help  0 0 0 0 5 0 0 0 9 10 0  0

помощь-формула:

IF(SALE>0,MONTH,0) - IF(A2>0,A1,0)

месяцев с последней продажи:

MAX(A1:L1)-MATCH(MAX(A3:L3);A3:L3;0)

Основная проблема — добраться до максимального числа столбцов со значением продажи больше 0. Сейчас я могу придумать только VBA и Matrix-Function, чтобы решить эту проблему без helper-row. Но у меня такое чувство, что я что-то упускаю.

Связанный контент