☰

Формула для расчета фиксированного дохода за 12 месяцев после месячной задержки

tag-icon • tag-icon microsoft-excel worksheet-function
Формула для расчета фиксированного дохода за 12 месяцев после месячной задержки

У меня возникли проблемы с разработкой формулы, которая позволила бы мне рассчитать доход, отложив его на 1 месяц, а затем сохранив его на прежнем уровне в течение 12 месяцев.

Вот мой рабочий лист:

Скриншот рабочего листа

Базовые данные по заказам 2018 года находятся в ячейках F6:Q9.

Базовые данные по заказам 2019 года находятся в ячейках S6:AD9.

В настоящее время имеется заказ на март 2018 г. (ячейка H6), и профиль доходов должен показывать задержку в 1 месяц, поэтому доходов за апрель не будет, но с мая 2018 г. заказ на март в размере 20 000 фунтов стерлингов должен начать отражать 1 667 фунтов стерлингов (до апреля 2019 г.).

Формула также должна учитывать заказы как из F6:Q9(заказы 2018 года), так и из S6:AD9(заказы 2019 года).

В ячейке I1я ввел 1(чтобы отразить задержку в один месяц).

В ячейке I2я ввел 12(чтобы отразить количество месяцев, в течение которых доход должен оставаться неизменным).

Профиль доходов начинается с ячейки, AE6и именно здесь у меня возникают трудности с определением того, какую формулу использовать.

Я пытался использовать формулу смещения, но она не заработала. Я не знаю, как подойти к решению.

решение1

Требуемая формула — этонемногосложная формула массива.

Вот ваша таблица с формулой, показывающей ожидаемые результаты:

Скриншот рабочего листа

Эту формулу необходимо ввести в массив, 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()по умолчанию оценивается как одно значение. Использование just in приведет к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(в этом случае хак не требуется, поскольку ранее использованный хак уже принудительно выполнил оценку массива второго аргумента, INDEXи, таким образом, COLUMN()функция вычисляет массивы.

Функция IFERROR()необходима в случае, если формула находится в ячейке, расположенной близко к левой стороне листа, что приводит либо к доступу к тексту, либо к попытке доступа к ячейке слева от столбца A.

Примечания:

  • Упрощенная формула действительно работает, если ее ввести.
  • Скобки вокруг (6:6)в усовершенствованной версии необходимы для того, чтобы заставить 6:6оставаться на своей строке.

Предостережения:

  • В ячейках n столбцов слева от заказов не может быть цифр.(где n определяется значением в I2). Если там есть какие-либо числа, формула, как есть, включит их в расчет выручки.
  • Разрыва между столбцами «Доходы» за декабрь 2018 г. и «Доходы» за январь 2019 г. быть не может.При желании формулу можно изменить, чтобы учесть такой зазор.
  • Промежуток между двумя таблицами заказов ( R:R) должен оставаться ровно в один столбец. В противном случае формула, как есть, сломается.
  • Пробелы в таблицах между заказами не могут содержать никаких чисел. В противном случае они будут рассматриваться как дополнительные заказы.

* Объяснение того, почему именно этот хак работает, придется подождать, пока я сам в этом не разберусь ;-)

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