Excel按岗位统计月度招聘人数及动态溢出公式优化问询

核心实现思路 #

通过嵌套数组遍历+条件统计，实现岗位（纵向）和月度（横向）的双向动态溢出，无需手动拖拽复制公式。

推荐公式（支持Excel 365/2021） #

在K3单元格输入以下公式，会自动根据J列的岗位列表和K行的月度序列双向溢出统计结果：

=LET(
    _roles, J3#,
    _months, K2#,
    _hire_dates, StaffDetailsTbl[Employment Start Date],
    _disciplines, StaffDetailsTbl[Discipline],
    BYROW(_roles, LAMBDA(role,
        BYCOL(_months, LAMBDA(month,
            COUNTIFS(_disciplines, role, _hire_dates, ">="&month, _hire_dates, "<"&EDATE(month,1))
        ))
    ))
)

公式细节说明 #

• LET：定义变量简化公式结构，避免重复引用，提升可读性和计算效率
• J3#/K2#：引用动态溢出的岗位列表和月度序列，自动适配数据源的行数/列数变化
• BYROW+BYCOL：嵌套遍历每个岗位和月度，对每一组组合执行精准条件统计
• COUNTIFS：匹配对应岗位的同时，筛选入职日期落在当前月度范围内的员工数（>=当月第一天且<下月第一天）

旧版Excel兼容方案 #

如果使用不支持BYROW/BYCOL的Excel版本，可使用矩阵乘法实现：

=MMULT(--(TRANSPOSE(StaffDetailsTbl[Discipline])=J3#), --(StaffDetailsTbl[Employment Start Date]>=K2#)*(StaffDetailsTbl[Employment Start Date]<EDATE(K2#,1)))

• 原理：将岗位匹配的布尔数组转置后，与月度匹配的布尔数组进行矩阵乘法，直接得到每个岗位-月度的统计总数

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