如何调整Excel公式统计含部分月在职的活跃员工数量

问题说明 #

现有统计每月活跃员工数的Excel公式：

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),($L$1#>=OFFSET($A$20#,0,3,,1))*($L$1#<=OFFSET($A$20#,0,4,,1)))

其中$L$1#由以下公式生成（2020年1-6月的月初日期序列）：

=EDATE(01/01/2020, SEQUENCE(1, 6, 0))

$A$20#指向员工数据集首行，数据集如下：

原公式仅统计月初入职的员工，需求为当月在职哪怕一天也计入统计，预期结果：

尝试修改公式时，因$L$1#在EOMONTH数组运算中配合AND函数失效，修改后的公式无法得到正确结果：

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),(AND((OFFSET($A$20#,0,3,,1)>=$L$1#),(OFFSET($A$20#,0,3,,1)<=EOMONTH($L$1#,0))))*($L$1#<=OFFSET($A$20#,0,4,,1)))

修正方案 #

核心问题分析 #

1. Excel数组运算中不能使用AND/OR函数，需用乘法*替代逻辑与，加法+替代逻辑或；
2. 原判断逻辑错误，正确的活跃员工判断应为员工在职区间与统计月份区间存在重叠，即：
   • 员工入职日期 ≤ 统计月份月末
   • 员工离职日期 ≥ 统计月份月初

修正后的公式 #

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),((OFFSET($A$20#,0,3,,1)<=EOMONTH($L$1#,0))*(OFFSET($A$20#,0,4,,1)>=$L$1#))*1)

公式解析 #

• EOMONTH($L$1#,0)：针对$L$1#中的每个月初日期，生成对应月份的月末日期，数组运算时自动匹配每个月份；
• OFFSET($A$20#,0,3,,1)/OFFSET($A$20#,0,4,,1)：分别提取所有员工的入职日期列和离职日期列；
• (入职日期<=月末)*(离职日期>=月初)：用乘法实现数组层面的逻辑判断，满足重叠条件返回1，否则返回0；
• MMULT：将每行的判断结果求和，得到每个月份的活跃员工总数。

可选优化（高版本Excel） #

若你的Excel支持结构化引用和TOCOL函数，可替换OFFSET为更清晰的引用，提升可读性：

=MMULT(SEQUENCE(1,ROWS($A$20#),1,0),((TOCOL($A$20#[[入职日期]:[入职日期]])<=EOMONTH($L$1#,0))*(TOCOL($A$20#[[离职日期]:[离职日期]])>=$L$1#))*1)

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