Excel中两日期列区间YEARFRAC值按年跨行汇总的实现方法

解决方案 #

你可以通过数组运算或者逐行遍历函数实现无中间列直接汇总，不需要额外填充每行的年度占比单元格。以下是两种适用不同版本的方案：

方案1：支持LAMBDA函数的版本（Excel 365/2021、Google Sheets） #

用BYROW函数逐行读取任职时间段，逐行计算年度占比后再统一求和即可。
假设你的表格结构如下：

• 开始日期列为B列，人员的开始日期范围为B2:B3
• 结束日期列为C列，人员的结束日期范围为C2:C3
• 首行的年份存放在D1（2021）、E1（2022）、F1（2023）单元格
  在汇总行的2021对应单元格（D4）输入以下公式，向右拖动即可自动计算2022、2023的汇总值：

=SUM(BYROW(B2:C3,LAMBDA(x,MAX(0,YEARFRAC(MAX(INDEX(x,1),DATE(D1,1,1)),MIN(INDEX(x,2),DATE(D1,12,31)))))))

公式说明：

• BYROW会逐行遍历B2:C3的每一行数据，传入LAMBDA的参数x
• INDEX(x,1)取当前行的开始日期，INDEX(x,2)取当前行的结束日期
• 外层加MAX(0,xxx)是为了过滤完全不在当前统计年份的任职记录，避免出现负的占比影响结果

方案2：旧版Excel（不支持LAMBDA） #

使用数组公式，输入完成后按Ctrl+Shift+Enter触发数组运算即可生效：

=SUM(IF(C2:C3<DATE(D1,1,1),0,IF(B2:B3>DATE(D1,12,31),0,YEARFRAC(IF(B2:B3>DATE(D1,1,1),B2:B3,DATE(D1,1,1)),IF(C2:C3<DATE(D1,12,31),C2:C3,DATE(D1,12,31))))))

数组公式会自动逐行计算每对开始、结束日期的年度占比，再直接累加得到最终汇总值。

两种方案计算出来的结果都完全匹配你给出的示例：2021年1.5、2022年2.0、2023年0.5。

内容的提问来源于stack exchange，提问作者Chris Conta