如何修改Excel公式统计每月在职员工数（适配空离职日期）

问题背景 #

我在Staff Details工作表有个StaffDetailsTbl员工数据表，包含员工姓名、入职日期、离职日期字段；另有工作表存储月度日期序列。当前用B4单元格的公式统计每月在职人数，但公式未处理离职日期为空的情况，目前仅统计到了Dave，需要修改公式让离职日期为空的员工在入职后持续被计入统计。

员工数据表具体数据：

原公式：

=LET(set, StaffDetailsTbl, starts, INDEX(set,,5), ends, INDEX(set,,6), SOMs, $B$1#, BYCOL(SOMs, LAMBDA(SOM, LET(EOM, EOMONTH(SOM,0), SUMPRODUCT((starts <= EOM) * (ends >= SOM))))))

修改后的公式 #

=LET(
    set, StaffDetailsTbl,
    starts, INDEX(set,,5),
    ends, INDEX(set,,6),
    adjustedEnds, IF(ISBLANK(ends), DATE(9999,12,31), ends),
    SOMs, $B$1#,
    BYCOL(SOMs, LAMBDA(SOM, 
        LET(
            EOM, EOMONTH(SOM,0),
            SUMPRODUCT((starts <= EOM) * (adjustedEnds >= SOM))
        )
    ))
)

改动说明 #

• 新增adjustedEnds变量，将空的离职日期替换为9999年12月31日——这个足够遥远的日期能确保离职日期为空的员工，只要入职时间早于当月月底，就会被判定为当月在职。
• 原公式中ends >= SOM的判断，空单元格会返回FALSE，导致Bob这类无离职日期的员工未被统计；替换后，空离职日期的员工会一直满足adjustedEnds >= SOM的条件（只要统计月份在其入职日期之后）。
• 如果需要统计到当前日期为止的在职状态，也可以把DATE(9999,12,31)换成TODAY()，不过使用未来日期更适合长期统计需求。

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