Google Sheets多员工排班按人员重置的连续工作天数公式实现
Google Sheets多员工连续工作天数统计纯公式实现方案
统计规则对齐
- 按员工维度独立计算,不受其他员工排班行干扰
- 当日调班后状态为休息(F列值为0)时,连续计数直接重置为0
- 当日调班后状态为工作(F列值为1)时,计数为该员工最近一次休息日期到当前日期间的有效工作天数,中间无其他休息即为连续工作长度
逐行填充兼容方案
适合所有Google Sheets版本,稳定性高。在计算列首个单元格(如H2,第1行为表头)输入以下公式,下拉填充至最后一行数据即可:=IF(F2=0,0,COUNTIFS(B:B,B2,A:A,">"&IFERROR(MAXIFS(A:A,B:B,B2,F:F,0),0),A:A,"<="&A2))
公式逻辑拆解:
- 首先判断当前行F列状态,若为0(休息)直接返回0
- 用
MAXIFS提取同员工下所有历史排班中,F列=0(休息)的最大日期,即该员工最近一次休息的日期;若该员工从未有过休息记录,IFERROR会返回默认值0,对应日期远早于所有正常排班日期 - 用
COUNTIFS统计同员工下,日期大于最近一次休息日期、且小于等于当前行日期的所有排班行数量,即为当前的连续工作天数
批量溢出高效方案
适合300+员工、数千行排班的大数量场景,无需下拉填充,仅需在H2单元格输入一次即可自动覆盖所有有效数据行:=ARRAYFORMULA(LET(dt_range, A2:A, emp_range, B2:B, status_range, F2:F, IF(status_range="","",IF(status_range=0,0,BYROW(emp_range&"¶"&dt_range&"¶"&status_range,LAMBDA(r,LET(emp_s,INDEX(SPLIT(r,"¶"),1),dt_s,INDEX(SPLIT(r,"¶"),2),st_s,INDEX(SPLIT(r,"¶"),3),COUNTIFS(B:B,emp_s,A:A,">"&IFERROR(MAXIFS(A:A,B:B,emp_s,F:F,0),0),A:A,"<="&dt_s))))))))
大表提速提示:
可将公式中的整列引用(如B:B、A:A、F:F)替换为实际数据的固定范围(如B2:B10000),避免Google Sheets扫描大量空行,可将计算速度提升60%以上。
前置校验要求
- A列日期必须设置为标准日期格式,不可为文本格式,否则日期比较逻辑会失效
- 同员工不可存在同一天的重复排班行,否则会导致连续计数偏高
排班表结构优化建议
- 增加辅助列预计算最近休息日期:在原G列输入
=IFERROR(MAXIFS(A:A,B:B,B2,F:F,0),0),后续连续计数公式直接引用G列的最近休息日期,避免重复执行MAXIFS运算,进一步降低大表计算压力 - 建议排班表按「员工升序 + 日期升序」排序,同员工的排班行形成连续块,后续若需通过条件格式标记连续工作超阈值(如连续7天上班触发提醒),配置效率会大幅提升
- 调班列E列的数据验证建议设置为可选值「空/调休/补班」,而非直接输入二值,F列的状态转换公式可设置为
=IF(E2="调休",0,IF(E2="补班",1,D2)),降低手动输入的错误率
内容的提问来源于stack exchange,提问作者Petar Atanasov
相关产品推荐
相关产品推荐

