Excel中统计日期范围内选中月份的天数（复选框控制）

核心公式（兼容旧版Excel，仅用基础函数） #

=SUMPRODUCT(
    --('Sheet2'!$W$9:$W$20=TRUE),
    MAX(0, 
        MIN(
            EOMONTH(IF(ROW($1:$12)>=MONTH(D3), DATE(YEAR(D3),ROW($1:$12),1), DATE(YEAR(D3)+1,ROW($1:$12),1)), 0),
            E3
        ) - MAX(
            IF(ROW($1:$12)>=MONTH(D3), DATE(YEAR(D3),ROW($1:$12),1), DATE(YEAR(D3)+1,ROW($1:$12),1)),
            D3
        ) + 1
    ) * (IF(ROW($1:$12)>=MONTH(D3), DATE(YEAR(D3),ROW($1:$12),1), DATE(YEAR(D3)+1,ROW($1:$12),1)) <= E3)
)

公式拆解 #

1. 选中月份标记：--('Sheet2'!$W$9:$W$20=TRUE)
   将复选框的TRUE/FALSE转换为1/0，仅选中的月份会参与后续计算。

2. 月份对应日期生成：IF(ROW($1:$12)>=MONTH(D3), DATE(YEAR(D3),ROW($1:$12),1), DATE(YEAR(D3)+1,ROW($1:$12),1))
   生成1-12月对应的当月第一天：

   • 若月份≥起始日期（D3）的月份，使用D3的年份；
   • 若月份<D3的月份，使用D3年份+1（处理跨年度场景）。

3. 有效日期范围计算：

   • EOMONTH(..., 0)：获取对应月份的最后一天；
   • MIN(..., E3)：取月份最后一天与结束日期（E3）的较小值，得到该月份在统计范围内的实际结束日；
   • MAX(..., D3)：取月份第一天与D3的较大值，得到该月份在统计范围内的实际起始日。

4. 天数计算：MAX(0, ... +1)
   计算该月份的有效天数，若实际结束日早于起始日则计0，避免负数。

5. 跨年度过滤：*(IF(...) <= E3)
   过滤掉下一年中超过E3的月份，避免无效计算。

示例验证 #

针对你给出的测试场景，公式均能返回预期结果：

备注 #

• 公式兼容跨年度场景（如D3=2023-12-25，E3=2024-01-05，选中12月和1月时，会正确计算7+5=12天）；
• 所有函数均为Excel基础函数，无需VBA或宏支持。

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