Excel - 在表格中自动填充特定月份数

Question

我正在制作一个租赁表，我希望能够输入开始日期和期限的月份数。我希望表格以这样的格式返回数据：以期限开始日期为开始，以 12 个月为增量，例如 120 个月（10 年），然后停止。例如，如果我输入了 60 个月，我希望表格以 5 年结束。我该如何格式化它？TIA！

我希望表格看起来的样子

Answer 1

您可以输入以下公式，然后从 K3、L3 和 N3 向下复制，直到与您想要提供的最大年限相匹配的行数。因此，对于最大租赁期限为 40 年（可能是土地租赁），向下复制到第 42 行。

K 列：

=IF((ROW()-2)*12<=$N$1,"Year "&(ROW()-2),"")

L 列：

=IF((ROW()-3)*12<$N$1,EDATE($L$1,(ROW()-3)*12),"")

第 N 列：

=IFERROR(EDATE(L3,12)-1,"")

日期的关键在于EDATE函数。由于您从月份的开始开始，因此只需获取一年的“第 n 个”月份开始日期即可。要获取月份结束日期的“第 n 个减一天”，只需从其结果中减去一天即可。

需要注意的是，您的图片（而不是一般问题）的年终日期是上个月的最后一天。（当然是下一年。）您使用的“10/31/2023”实际上是 13 个月的“年”，而不是 12 个月。如果这是故意的，您应该更改问题的文本。这还需要对公式进行轻微更改。

您可以通过为开始日期和期限创建命名范围来使公式更加用户友好。或者使用LET本质上相同的方法，但保持所有组件在每个公式中立即可用。

如果您有SPILL可用的功能，则可以改用以下三个公式：

K 列：

=IF((ROW(L3:L42)-2)*12<=$N$1,"Year "&(ROW(L3:L42)-2),"")

L 列：

=IF((ROW(L3:L42)-3)*12<$N$1,EDATE($L$1,(ROW(L3:L42)-3)*12),"")

第 N 列：

=IFERROR(EDATE(L3,12*(ROW(L3:L42)-2))-1,"")

这样就避免了复制和粘贴的需要，或者在公式更新和升级后记得这样做。

Answer 2

进一步回答上一个问题，如果您是可以使用数组的 O365 用户，则 K、L 和 N 列的这三个公式可以完成所有操作并根据需要进行溢出。

=MAKEARRAY(O1,1,LAMBDA(r,c,"Year "&r))
=MAKEARRAY(O1,1,LAMBDA(r,c,EDATE(L1,(r-1)*12)))
=MAKEARRAY(O1,1,LAMBDA(r,c,EOMONTH(EDATE(L1,(r-1)*12),11)))

如果您想炫耀一下，可以使用一个公式而不是 3 个公式来创建整个表格，方法是使用根据第 1-3 列变化的选择：

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,"Year "&r,EDATE(L1,(r-1)*12),EOMONTH(EDATE(L1,(r-1)*12),11))))

如果是这样，您可能需要使用 Alt-Enters 将其分解为稍微更易读的内容，例如：

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,
"Year "&r,
EDATE(L1,(r-1)*12),
EOMONTH(EDATE(L1,(r-1)*12),11)
)))

此外，有时在溢出的情况下，在公式中强制使用日期格式会更容易，以防数组溢出到新的区域，因此你可以将第二和第三个公式的结果放入text( result, "mm/dd/yyyy")函数中：

=MAKEARRAY(O1,3,LAMBDA(r,c,CHOOSE(c,
"Year "&r,
TEXT(EDATE(L1,(r-1)*12),"mm/dd/yyy"),
TEXT(EOMONTH(EDATE(L1,(r-1)*12),11),"mm/dd/yyyy")
)))

好的，真的，我现在已经完成了，哈哈。