使用Excel函数按月份统计事项发生量的技术求助

核心逻辑拆解 #

• 空结束日期自动替换为TODAY()，标记未完结事项
• 重开事项按独立行统计（只要数据中重开事项是单独行，公式自动识别）
• 通过单元格参数实现年份快速切换，方便多年份数量对比

函数公式 #

假设：

• B列为事项开始日期
• C列为事项结束日期（空值=未完结）
• E1单元格为目标统计年份（如输入2023）

统计「与目标年份有时间交集」的事项数量 #

（包括跨年度事项，只要事项在目标年份内有发生时段）

=SUMPRODUCT(--(B:B<=DATE(E1,12,31)),--(IF(C:C="",TODAY(),C:C)>=DATE(E1,1,1)))

统计「完全在目标年份内」的事项数量 #

（事项开始和结束均在目标年份区间内，未完结事项需当前日期仍在目标年内）

=SUMPRODUCT(--(B:B>=DATE(E1,1,1)),--(IF(C:C="",TODAY(),C:C)<=DATE(E1,12,31)))

公式说明 #

1. DATE(E1,1,1) / DATE(E1,12,31)：自动生成目标年份的1月1日和12月31日，实现年份切换
2. IF(C:C="",TODAY(),C:C)：将空结束日期替换为当前日期，统一判断逻辑
3. --：将布尔判断结果（TRUE/FALSE）转换为1/0，SUMPRODUCT自动求和得到符合条件的事项总数

注意事项 #

• 确保B、C列是日期格式，若为文本需先转换（选中列→开始选项卡→格式→短日期）
• 大数据量时，替换整列引用（如B:B）为具体数据范围（如B2:B1000），提升计算效率
• 未完结事项会随当前日期自动更新状态，若需固定统计截止日期，将TODAY()替换为指定日期（如DATE(2024,5,1)）

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