如何用Excel动态公式统计员工无考勤无请假的连续违规天数

前提准备 #

• 将数据转为结构化表格（选中数据区域按Ctrl+T），表格需包含：姓名、日期、排班计划、考勤记录、请假代码列
• 新增违规标记列，输入公式：

  =AND([@排班计划]>0, [@考勤记录]=0, [@请假代码]=0)
  

  （若你的Excel区域设置用分号分隔参数，将公式中的逗号替换为分号即可）

动态计算连续违规天数 #

在空白列的首个单元格输入以下动态数组公式，无需手动下拉，公式会自动覆盖所有行：

=SCAN(0, Table1[违规标记], LAMBDA(prev, curr, IF(curr, prev+1, 0)))

SCAN函数会遍历违规标记列，遇到违规（值为TRUE）时累加计数，非违规则重置为0，自动生成每行的连续违规天数。

标记连续2天及以上的违规行 #

若要直接生成符合条件的标记，在另一空白列输入整合后的动态数组公式：

=BYROW(SCAN(0, Table1[违规标记], LAMBDA(prev, curr, IF(curr, prev+1, 0))), LAMBDA(x, IF(x>=2, "连续违规≥2天", "")))

该公式会自动为连续违规≥2天的行添加标记，全程无需手动下拉公式。

注意事项 #

• 仅支持Office 365/2021及以上版本（需动态数组函数支持）；
• 结构化表格会自动适配新增数据，无需调整公式引用范围；若使用普通数据区域，可将Table1[违规标记]替换为动态范围引用（如OFFSET(G2,0,0,COUNTA(G:G)-1,1)），但结构化表格的稳定性更强。

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