☰

如何在 Excel 中从合同开始日期开始将合同总价值分成相等的月价值？

tag-icon • tag-icon microsoft-excel worksheet-function
如何在 Excel 中从合同开始日期开始将合同总价值分成相等的月价值？

我需要创建一个公式，根据合同总价值在合同期限内平均分配的金额（从合同签署之日起计算），用月收入填充月度栏。我目前在报告中拥有的信息是开始日期、合同总价值和期限（以月为单位）。我知道我可以轻松计算月度价值，然后手动将该金额输入到每个月中，但我的报告有数百个条目，因此需要一个可以应用于每个条目的公式。

我正在考虑的格式（但我愿意接受其他建议！）如下...

在此处输入图片描述

我希望通过魔法公式得到的结果是这样的（我手动输入了这个）...

在此处输入图片描述

然后我应该能够创建一个数据透视表来总结每月的收入预测。

我不需要公式来计算部分月份，例如，如果合同从月中开始，那么可以将其算作整月收入。如果有更简单的方法将其分成月度列，那就没问题了——我的报告中只会显示摘要信息。

如果您对我如何做到这一点有任何想法，请告诉我，如果您需要任何澄清，请告诉我。

谢谢！

答案1

在单元格中D2，复制/粘贴此公式：（您可能需要根据您的区域设置将逗号更改为分号。）

=IF(AND(D$1>=DATE(YEAR($A2),MONTH($A2),1),D$1<=DATE(YEAR($A2),MONTH($A2)+$B2,1)),$C2/$B2,"")

分解一下，逻辑如下：

IF
   (the date in row 1 is >= the first day of the contract start date month)
   AND
   (the date in row 1 is <= the first day of the month of the contract start date plus the duration in months)
THEN
   calculate the total value divided by the number of months
ELSE
   return blank (change "" to 0 or "-" or whatever fits your need)

关键点在于第一行（列标题）中的值是实际日期而不是文本。按照公式的编写方式，这些日期必须是每月的第一天。如果您输入前两个或三个日期，然后将其余日期拖过来，这并不难；Excel 将自动递增。如果这是个问题，请告诉我，我们可以找到其他方法。

相关内容