Excel跨多数据集自动统计分布式团队成员所在地区节假日

适用场景前置说明 #

假设你的表格结构如下：

• 团队成员按列排布：每列对应一名成员，第1行是成员姓名，第2行是该成员所属地区（例如B1=成员1、B2=北京；C1=成员2、C2=上海）
• 公共假日列表存于单独工作表（比如Sheet2）：A列是假日日期（DATE），B列是对应地区（LOCATION）
• 指定的起止日期固定在某两个单元格（比如$F$1=开始日期，$F$2=结束日期）

单公式实现方案 #

在成员姓名下方的单元格（比如B3）输入以下公式，直接横向填充即可覆盖所有成员列：

=IF(COUNTIFS(Sheet2!$B:$B, B$2, Sheet2!$A:$A, ">="&$F$1, Sheet2!$A:$A, "<="&$F$2)=0, "无公共假日", COUNTIFS(Sheet2!$B:$B, B$2, Sheet2!$A:$A, ">="&$F$1, Sheet2!$A:$A, "<="&$F$2)&"个公共假日")

公式逻辑拆解 #

1. COUNTIFS核心统计：通过多条件匹配完成假日计数
   • Sheet2!$B:$B, B$2：匹配当前成员所属地区（B$2固定引用第2行的地区，横向填充时列会自动切换）
   • Sheet2!$A:$A, ">="&$F$1：筛选出不早于指定开始日期的假日
   • Sheet2!$A:$A, "<="&$F$2：筛选出不晚于指定结束日期的假日
2. IF判断优化显示：如果计数为0，直接显示“无公共假日”；否则显示具体数量+说明文字

极简数字版（仅统计数量） #

若不需要文字说明，只显示假日数量，可用简化公式：

=COUNTIFS(Sheet2!$B:$B, B$2, Sheet2!$A:$A, ">="&$F$1, Sheet2!$A:$A, "<="&$F$2)

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