如何在Excel单个单元格计算日期区间各月天数及按比例分摊金额？

核心需求 #

给定起始日期（A1）、结束日期（B1）和每月固定金额（如$300），计算区间内各月份的分摊金额并求和，同时可计算总天数。要求单个单元格完成计算。

方案一：适用于Excel 365/2021（支持动态数组） #

直接在单个单元格输入以下公式（可将300替换为单元格引用如C1，方便灵活修改金额）：

=SUM(300*(MIN(EOMONTH(SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1),0),B1)-MAX(A1,EOMONTH(SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1),-1)+1)+1)/DAY(EOMONTH(SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1),0)))

公式拆解

• SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1)：生成区间覆盖的所有月份的第一天序列（示例中为2024/2/1、2024/3/1...2024/6/1）
• EOMONTH(...,0)：获取每个月份的最后一天，结合DAY()得到当月总天数
• MIN(EOMONTH(...),B1)：确定每个月在区间内的结束日（取当月最后一天或区间结束日的较小值）
• MAX(A1, EOMONTH(...,-1)+1)：确定每个月在区间内的起始日（取当月第一天或区间起始日的较大值）
• (结束日-起始日+1)：计算当月实际包含的天数
• 300*(实际天数/当月总天数)：计算单月分摊金额，最后用SUM()求和得到总金额

方案二：适用于旧版Excel（无动态数组支持） #

需按Ctrl+Shift+Enter作为数组公式输入：

=SUM(300*(MIN(EOMONTH(DATE(YEAR(A1),MONTH(A1)+ROW(INDIRECT("1:"&DATEDIF(A1,B1,"m")+1))-1,1),0),B1)-MAX(A1,DATE(YEAR(A1),MONTH(A1)+ROW(INDIRECT("1:"&DATEDIF(A1,B1,"m")+1))-1,1))+1)/DAY(EOMONTH(DATE(YEAR(A1),MONTH(A1)+ROW(INDIRECT("1:"&DATEDIF(A1,B1,"m")+1))-1,1),0)))

总天数计算（单个单元格） #

基于同样逻辑，总天数公式为：

=SUM(MIN(EOMONTH(SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1),0),B1)-MAX(A1,EOMONTH(SEQUENCE(DATEDIF(A1,B1,"m")+1,1,EOMONTH(A1,-1)+1),-1)+1)+1)

验证示例 #

当A1=19/02/2024，B1=16/06/2024，金额为300时：

• 总天数计算结果为119
• 总金额计算结果为$1,173.80（保留两位小数）

内容的提问来源于stack exchange，提问作者Asifa.K