计算续订日期的未来费用

Question

我试图创建一个电子表格，将跟踪当前和未来的成本项目已知的保质期。这包括产品是什么，产品的成本，产品的寿命(月)和最后一次购买的日期。

我尝试了几种方法来修改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)

基本上，我想说的是：

如果我正在检查的下一年是我必须替换该项目的一年，那么将该项目的成本加到该年的总数中。

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

等

这些物品需要不断更换。

谢谢你的帮助！

Answer 1

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

如果数据位于电子表格中，则如下所示：

​

​

计算cell 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)}

Answer 2

您可以使用名为DATEDIF的某种“秘密”函数。

​

​

B8中的公式：

=SUMPRODUCT((INT(DATEDIF($D$2,DATE(A8,1,1),"m")/$C$2:$C$4)=DATEDIF($D$2,DATE(A8,1,1),"m")/$C$2:$C$4)*$B$2:$B$4)

“DATEDIF (日期+ Dif)函数是一个来自Lotus1-2-3的”兼容性“函数。出于未知的原因，它只在Excel 2000中被记录下来，但从那时起，它在所有Excel版本中都有效。正如奇普·皮尔森所说: DATEDIF被视为公式家族的醉酒表亲。Excel知道它过着幸福而有用的生活，但不会在礼貌的交谈中提及它。”(https://exceljet.net/excel-functions/excel-datedif-function)