☰

计算 1 个月延迟后 12 个月内持平收入的公式

tag-icon • tag-icon microsoft-excel worksheet-function
计算 1 个月延迟后 12 个月内持平收入的公式

我无法设计一个公式来计算基于延迟 1 个月的收入然后在 12 个月内保持收入平稳的收入。

这是我的工作表：

工作表截图

2018 年订单的基础数据在单元格中F6:Q9。

2019 年订单的基础数据在单元格中S6:AD9。

目前有一份 2018 年 3 月的订单（手机H6），收入概况应显示 1 个月的延迟，因此 4 月份不会有收入，但从 2018 年 5 月开始，3 月份的 20,000 英镑订单应开始显示 1,667 英镑（直到 2019 年 4 月）。

该公式还需要考虑来自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 返回数组的必需 hack *expression ，因为默认情况下，第二个参数的INDEX()计算结果为单个值。仅使用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(在这种情况下不需要 hack，因为之前使用的 hack 已经强制对第二个参数进行数组评估，INDEX因此COLUMN()函数评估为数组。

IFERROR()如果公式存在于靠近工作表左侧的单元格中，则需要此函数，从而访问文本或尝试访问列左侧的单元格A。

笔记：

  • 美化的公式确实可以起作用。
  • 美化版本中的括号(6:6)用于强制6:6将其保持在自己的行上。

注意事项：

  • 订单左侧 n 列的单元格中不能有数字（其中 n 由 中的值指定I2）。如果其中有任何数字，则公式将按原样将其包括在收入计算中。
  • 2018 年 12 月收入和 2019 年 1 月收入列之间不能有差距。如果需要的话，可以修改公式来允许这样的差距。
  • 两个订单表 ( ) 之间的间隙R:R必须保持恰好一列宽。否则，公式将会中断。
  • 订单表之间的间隙不能包含任何数字。否则，它们将被视为附加订单。

*我必须先自己弄清楚，才能确切解释这种黑客攻击为何有效 ;-)

相关内容