时间戳

首页 > 解决方案 > 自定义 SUMPRODUCT()

excel - 自定义 SUMPRODUCT()

问题描述

我有下表：

在此处输入图像描述

考虑到每个月的权重，我的目标是在 5 个月内分散 10,000 个单位。与 10,000 相邻的行是我最好的尝试（我在这上面花了几个小时）。黄色是我所追求的。

我试图用来计算的逻辑如下：计算跨月的平均分布（在本例中为 10,000 / 5 = 2,000 = X）。将每个月的权重相乘以获得加权金额（在此示例中获得 M2 = 1,600 和 M4 = 3,000：X +（X * M2 权重）等）。然后，您可以将总 10,000 减去这些总和，得到 5,400，然后您可以在没有权重的月份中分配。必须有一个简单的方法来做到这一点，SUMPRODUCT但我似乎无法弄清楚。

到目前为止，我最好的尝试是：

=IF(B3=0,SUMPRODUCT(ABS((10000/5)*$B3:$F3)),(10000/5)+((10000/5)*B3))

标签： excelexcel-formulaformulaweightedsumproduct

解决方案

尝试以下操作：

在此处输入图像描述

中的公式B3：

=IF(B$2=0,($A3-SUMPRODUCT(($B2:$F2<>0)*(($A3/5)*(1+$B2:$F2))))/COUNTIF($B2:$F2,0),($A3/5)*(1+B$2))

向右拖动。

逻辑：

  • =IF(B$2=0,<true>,<false>)- 检查上面的单元格是否等于零
  • 如果上面是假的（所以不是 0）然后：($A3/5)*(1+B$2)- 首先(10000/5)将该结果除以并乘以(1-<percentage>)
  • 如果IF结果为真 - 检查范围内的哪些单元格不为零，并乘以与步骤 2 相同的逻辑。SUMPRODUCT然后可以从 10000 中减去获得的总和，然后除以实际保持为零的单元格计数（因此COUNTIF） .

推荐阅读