☰

使用范围将公式从行扩展到表？

tag-icon • tag-icon microsoft-excel macos
使用范围将公式从行扩展到表？

不确定该如何命名。

我有一张资本支出（CapEx）表（开始和结束 = 年，频率 = 每 n 年），如下所示：

Row/Col    A       B      C      D
1        Amount  Start   Freq   Ends
2          1200   2022     2    2045
3         35000   2024     5    2050
4         15000   2023     3    2045
5         20000   2025     8    2056

另一个表包含“年份”和该年份的“总资本支出”列。

Row/Col    G      H
11       Year   Total_CapEx
12       2022   <formula>
13       2023   <formula>
14       2024   <formula>
and so on

确定单个项目、单个年份（例如 H12 和第 2 行）的资本支出的公式为

=IF(G12>=D2,0,IF(MOD(G12-B2,C2)=0,1,0)*A2)

需要的是 G 列中每年所有适用资本支出的总和。最复杂的方法是在 SUM 函数中对 CapEx 表中的每一行重复上述公式。这不是一个好的解决方案！

因此需要做两件事：

  1. 一种简写公式，可能使用范围，来得到总和。
  2. 无需更改公式即可扩展资本支出表。

这可能吗 ？

答案1

如果您是 0365 客户，这相当容易，因为您可以访问动态数组功能集。我们将创建两个不可见的“辅助列”，它们仅存在于内存中：

end = Start + Freq -1.
AnnualAmt = Amount / Freq

因此，我们过滤资本表中 G12 中的日期介于 start 和 endYr 之间的任何行，并对这些行中的年金额求和。

此函数位于 G12 中并将其复制下来：

=LET(
end, Start + Freq -1,
annualAmt, Amount / Freq,
include, (G12 >= Start) * (G12 <= end),
SUM( FILTER( annualAmt, include, 0) )
)

您可以自行选择将显示的名称分配给源数据中的范围，也可以替换绝对范围（如 $A$2:$A$5 等）放入公式中，而不是命名范围。

編輯我假设您希望按年份分配资本金额，但我突然意识到您设置资本表的方式已经这样做了，并且 Freq 表示“重复频率年份的金额”而不是“将金额除以频率年份”。如果是这样，那么就更简单了：

=LET(
end, Start + Freq -1,
include, (G12 >= Start) * (G12 <= end),
SUM( FILTER( Amount, include, 0) )
)

您可能会发现它变得如此简单，以至于为了清晰起见，它实际上没有必要使用 LET 函数，您只需执行以下操作即可：

SUM( FILTER( Amount, (G12 >= Start) * (G12 <= (Start + Freq -1) ), 0) )

注意：SUM 和 Filter 在某些情况下有效，而 SUMIF 则无效，并且after * before对于数组来说比 更可靠and(after,before)。有时过滤器找不到符合条件的年份，因此如果结果为空，它会返回 0 值。

答案2

我搞明白了。我为第一个表中的每一列的数据命名（不是必需的，但可以使公式更具可读性）。我使用了 SUMPRODUCT 函数。

=SUMPRODUCT(Capex_Amount*IF($G12>=Capex_Start,1,0)*IF($G12<=Capex_Ends,1,0)*IF(MOD($G12-Capex_Start,Capex_Every)=0,1,0))

我尝试对开始和结束 IF 语句进行 AND 运算，但结果总是为 0 ？ 所示的公式有效。

相关内容