excel - 从续订日期计算 future 成本

Question

我正在尝试创建一个电子表格，以跟踪具有已知保质期的元素的当前和 future 成本。这包括产品是什么、产品成本、产品生命周期(以月为单位)和最后购买日期。

我已经尝试了几种方法来摆弄 Excel 公式以使其正常工作。

我的表如下所示:

| Product | Cost | Life(months) | Purchased |
|---------|------|--------------|-----------|
| SHOES   | 25   | 24           | 1-1-2019  |
| HAT     | 15   | 48           | 1-1-2019  |
| DYE     | 5    | 12           | 1-1-2019  |

| YEAR | TOTAL COST BY YEAR |
|------|--------------------|
| 2019 |                    |
| 2020 |                    |
| 2021 |                    |
| 2022 |                    |
| 2023 |                    |

我目前的公式看起来像这样，虽然它不能正常工作(我总是得到 0):

=SUMIF($D$2:$D$4, MOD(A7-YEAR($D$2:$D$4),($C$2:$C$4/12))= 0, $B$2:$B$4)

基本上我想说的是:

如果我要检查的 future 年份是我必须更换该项目的年份，则将该项目的成本添加到该年份的总额中。

所以在 2019 年，由于我刚刚购买了所有元素，我的总成本应该是 45 美元。
2020 = 5 美元，因为我只需要更换染料。
2021 = 30 美元，因为我需要更换鞋子和染料。

ETC

这些元素需要不断更换。

感谢您的任何帮助!

Answer 1

您应该使用公式 SUM() 和 IF() 的组合作为 ARRAY 公式。

如果数据位于电子表格中的位置:



单元格公式 C6 是:

=SUMPRODUCT(ROUNDUP(ROUNDDOWN((DATE(B6,1,1)+365-$E$2:$E$4)/365*12,0)/$D$2:$D$4,0)-ROUNDUP(ROUNDDOWN((DATE(B6,1,1)-$E$2:$E$4)/365*12,0)/$D$2:$D$4,0),$C$2:$C$4)

确保按 CRTL+SHIFT+ENTER 而不是 ENTER 来确认公式，这样你就可以得到公式上的括号:

{=SUMPRODUCT(ROUNDUP(ROUNDDOWN((DATE(B6,1,1)+365-$E$2:$E$4)/365*12,0)/$D$2:$D$4,0)-ROUNDUP(ROUNDDOWN((DATE(B6,1,1)-$E$2:$E$4)/365*12,0)/$D$2:$D$4,0),$C$2:$C$4)}