请求构建Excel公式计算员工分阶段流动人数

流转规则与数据逻辑 #

各阶段停留周期明确：

• Stage 1：新入职员工连续待3个月（当月入职即进入，满3个月后转Stage2）
• Stage 2：从Stage1转入后连续待6个月，满期转Stage3
• Stage 3：从Stage2转入后连续待6个月，满期转Stage4
• Stage 4：转入后直至离职

离职数据为各月各阶段的离职人数，直接从对应阶段扣除，且离职员工不再参与后续阶段流转。

假设数据单元格布局（可按需调整） #

示例数据位置：

• 初始各阶段人数：A2(Stage1)、B2(Stage2)、C2(Stage3)、D2(Stage4)
• 各月新入职人数：F2(第1月)、F3(第2月)、...、Fn(第N月)
• 各月各阶段离职人数：G2(Stage1第1月)、H2(Stage2第1月)、I2(Stage3第1月)、J2(Stage4第1月)；G3(Stage1第2月)以此类推

逐阶段逐月计算公式 #

1. Stage 1 人数计算 #

Stage1由当月新入职员工、上月Stage1留存员工组成，扣除本月Stage1离职人数。

• 第1个月（单元格K2）：

=MAX(0, A2 + F2 - G2)

• 第N个月（N≥2，单元格Kn）：

=MAX(0, K(n-1) - G(n-1) + Fn - Gn)

MAX(0)用于避免出现负数人数（离职人数超过当前阶段人数的情况）。

2. Stage 2 人数计算 #

Stage2人员来自3个月前入职且在Stage1未离职的员工，加上上月Stage2留存员工，扣除本月Stage2离职人数。

• 第1个月（单元格L2）：

=MAX(0, B2 - H2)

• 第2-3个月（单元格L3/L4）：

=MAX(0, L(n-1) - H(n-1) - Hn)

• 第N个月（N≥4，单元格Ln）：

=MAX(0, L(n-1) - H(n-1) + (F(n-3) - SUM(G(n-3):G(n-1))) - Hn)

F(n-3)-SUM(G(n-3):G(n-1))是3个月前入职员工在Stage1的3个月内未离职的人数，这批人本月转入Stage2。

3. Stage 3 人数计算 #

Stage3人员来自9个月前入职且在Stage1、Stage2期间未离职的员工，加上上月Stage3留存员工，扣除本月Stage3离职人数。

• 第1个月（单元格M2）：

=MAX(0, C2 - I2)

• 第2-9个月（单元格M3-M10）：

=MAX(0, M(n-1) - I(n-1) - In)

• 第N个月（N≥10，单元格Mn）：

=MAX(0, M(n-1) - I(n-1) + ((F(n-9) - SUM(G(n-9):G(n-7))) - SUM(H(n-6):H(n-1))) - In)

先计算9个月前入职员工在Stage1的留存数，再扣除其在Stage2的离职数，得到转入Stage3的人数。

4. Stage 4 人数计算 #

Stage4人员来自15个月前入职且在Stage1/2/3期间未离职的员工，加上上月Stage4留存员工，扣除本月Stage4离职人数。

• 第1个月（单元格N2）：

=MAX(0, D2 - J2)

• 第2-15个月（单元格N3-N16）：

=MAX(0, N(n-1) - J(n-1) - Jn)

• 第N个月（N≥16，单元格Nn）：

=MAX(0, N(n-1) - J(n-1) + (((F(n-15) - SUM(G(n-15):G(n-13))) - SUM(H(n-12):H(n-7))) - SUM(I(n-6):I(n-1))) - Jn)

依次扣除该批员工在三个前置阶段的所有离职人数，得到转入Stage4的人数。

关键注意事项 #

• 所有MAX(0)用于规避负数人数的不合理情况。
• 若实际数据布局与示例不同，直接替换公式中的单元格引用即可。
• 离职数据为当月该阶段的离职人数，扣除后离职员工不再进入后续阶段。

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