【EXCEL】按天计算，分摊到每年的费用金额，只用内置函数 无需编写VB

先上说明：（为了便于区分嵌套的循环，外层使用 是-否，内层使用 yes-no）

1、判断表头年份大于开始时间或者表头时间小于结束时间，直接填0
2、判断表头时间和开始时间是同一年：
是：判断结束时间和开始时间是否是同一年？
　　yes--》(结束时间-开始时间+1天)*每天费用
　　no--》(年末-开始时间计算+1天)*每天费用
否：判断表头时间和结束时间是否同一年？
　　yes--》(结束时间-当年的第一天+1天)*每天费用
　　no--》表头当年天数*每天费用

再上代码：

第一种方法，使用内置DAYS()计算天数  对于WPS不友好，不兼容WPS

=

IF(
　　OR(VALUE(LEFT(G$2,4))<YEAR($B3),VALUE(LEFT(G$2,4))>YEAR($C3)),
　　0,
　　IF(
　　　　YEAR($B3)=VALUE(LEFT(G$2,4)),
　　　　IF(
　　　　　　YEAR($B3)=YEAR($C3),
　　　　　　(DAYS($C3,$B3)+1)*$F3,
　　　　　　(DAYS(TEXT(LEFT(G$2,4)&1231,"0-00-00"),$B3)+1)*$F3
　　　　),
　　　　IF(
　　　　YEAR($C3)=VALUE(LEFT(G$2,4)),
　　　　(DAYS($C3,TEXT(LEFT(G$2,4)&"0101","0-00-00"))+1)*$F3,
　　　　(DAYS(TEXT(VALUE(LEFT(G$2,4))&1231,"0-00-00"),TEXT(LEFT(G$2,4)&"0101","0-00-00"))+1)*$F3

　　　　)
　　)
)

 

第二种方法：日期直接相减，支持WPS

=IF(

　　OR(VALUE(LEFT(G$2,4))<YEAR($B3),VALUE(LEFT(G$2,4))>YEAR($C3)),
　　0,
　　
　　IF(
　　　　YEAR($B3)=VALUE(LEFT(G$2,4)),
　　　　IF(
　　　　　　YEAR($B3)=YEAR($C3),
　　　　　　($C3-$B3+1)*$F3,
　　　　　　(TEXT(LEFT(G$2,4)&1231,"0-00-00")-$B3+1)*$F3
　　　　),
　　　　IF(
　　　　　　YEAR($C3)=VALUE(LEFT(G$2,4)),
　　　　　　($C3-TEXT(LEFT(G$2,4)&"0101","0-00-00")+1)*$F3,
　　　　　　(TEXT(VALUE(LEFT(G$2,4))&1231,"0-00-00")-TEXT(LEFT(G$2,4)&"0101","0-00-00")+1)*$F3)
　　　　)
)

注意几点：

1、注意单元格部分根据自己需求调整 主要是开始时间、结束时间、日均费用以及标题时间。

2、表格中的表头是带有“年”字的所以需要进行截取。

3、注意修改单元格格式，小数点位数根据需要修改。

 

表格内容：