Требуемая формула представляет собой немного сложную формулу массива.
Вот ваша таблица с формулой, показывающей ожидаемые результаты:
Эта формула должна быть введена в массив в AE6 а затем заполнена / скопирована вниз и вправо, насколько это необходимо):
{=SUM(IFERROR(INDEX(6:6,N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))/$I$2,0))}
Объяснение:
Предварительно подтвержденный вариант формулы выглядит следующим образом:
{=
SUM(
IFERROR(
INDEX(
(6:6),
N(IF(1,COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1))
+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R))
)/$I$2,
0
)
)}
Формула будет намного легче понять, если учесть, что для продолжительности 12 месяцев и задержки в 1 месяц второй аргумент первого INDEX() примерно эквивалентен:
COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-1
Формула в основном работает, генерируя массив смещений для доступа к порядкам предыдущего числа I2 месяцев с задержкой на I1 месяца относительно текущей ячейки.
Пройдя по формуле в AK6 следует прояснить вышесказанное:
COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))
→ {1,2,3,4,5,6,7,8,9,10,11,12}COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-{1,2,3,4,5,6,7,8,9,10,11,12}-$I$1
→ {37}-({31}-{6})-{1,2,3,4,5,6,7,8,9,10,11,12}-1
→ {10,9,8,7,6,5,4,3,2,1,0,-1}N(IF(1,{10,9,8,7,6,5,4,3,2,1,0,-1}))
→ N({10,9,8,7,6,5,4,3,2,1,0,-1})
→ {10,9,8,7,6,5,4,3,2,1,0,-1}{10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>=COLUMN($R:$R))
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+({10,9,8,7,6,5,4,3,2,1,0,-1}>={18})
→ {10,9,8,7,6,5,4,3,2,1,0,-1}+{0,0,0,0,0,0,0,0,0,0,0,0}
→ {10,9,8,7,6,5,4,3,2,1,0,-1}INDEX((6:6),{10,9,8,7,6,5,4,3,2,1,0,-1})/$I$2
→ INDEX(6:6,{10,9,8,7,6,5,4,3,2,1,0,-1})/12 → {24000,0,20000,0,0,"Opportunity Name1","bWmd1","Col C val","Col B val","Col A val","Col A val",#VALUE!}/12
→ {2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}SUM(IFERROR({2000,0,1666.67,0,0,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!},0))
→ 2666.67
INDEX(6:6,N(IF(1,expression))) является обязательным хаком * , чтобы заставить Excel возвращать массив для expression так как второй аргумент INDEX() оценивается в одно значение по умолчанию. Использование только INDEX(6:6,expression) в AK6 приведет к
INDEX((6:6),COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)))
→ INDEX(6:6,37-(31-6)-COLUMN($A$6:$L$6)-1+(37-(31-6)-COLUMN($A$6:$L$6)-1>=18))
→ INDEX(6:6,12-1-1+(12-1-1>=18))
→ INDEX(6:6,10)
→ 24000
поскольку внутри выражения, возвращающего одно значение, COLUMN(multi-cell-range) возвращает столбец первой ячейки диапазона.
+(COLUMN()-(COLUMN($AE:$AE)-COLUMN($F:$F))-COLUMN(INDEX(6:6,1):INDEX(6:6,$I$2))-$I$1>=COLUMN($R:$R)) корректирует разрыв между таблицами заказов на 2018 и 2019 годы (столбец R). Обратите внимание, что N(IF( hack) в этом случае не требуется, так как ранее использованный хак уже вынудил оценку массива второго аргумента INDEX и, таким образом, функцию COLUMN() оценила массивы).
Функция IFERROR() требуется в том случае, если формула существует в ячейке рядом с левой стороной листа, что приводит либо к доступу к тексту, либо к попытке доступа к ячейке слева от столбца A
Заметки:
- Предварительно подтвержденная формула действительно работает, если введена.
- Квадратные скобки
(6:6) в предварительно проверенной версии должны заставить 6:6 оставаться на своей линии.
Предостережения:
- В ячейках n столбцов слева от ордеров не должно быть чисел (где n определяется значением в
I2). Если там есть какие-либо числа, формула, как есть, будет включать их в расчет выручки.
- Между столбцами «Доход за декабрь 2018 года» и «Доход за январь 2019 года» не может быть разрыва. Формула может быть изменена, чтобы учесть такой разрыв, если таковой необходим.
- Разрыв между двумя таблицами заказов (
R:R) должен составлять ровно один столбец. В противном случае формула, как есть, сломается.
- Разрыв между таблицами заказов не может содержать чисел. В противном случае они будут рассматриваться как дополнительные заказы.
* Объяснение того, почему хак работает, придется подождать, пока я сам не пойму ;-)
