基于Week Number动态计算Excel员工每周加班工时技术问询

核心规则梳理 #

• 标准日工时为8小时，默认周标准工时40小时
• 加班统计需同时满足两个条件：当日工时超过8小时 且 周总工时超过调整后的周标准工时，仅符合双条件的部分计入加班
• 周标准工时调整规则：若当周存在事假（Personal Holiday）、病假（Sick Day）或公共假期（Public Holiday），每缺勤/放假1天，周标准工时扣除8小时（例：1天公共假期+1天事假，周标准工时=40-8×2=24小时）
• 所有计算需基于「周数（Week Number）」列实现跨行动态匹配

分步公式实现（假设表格列名已翻译为中文） #

假设表格列布局如下：
| 员工ID | 周数 | 日期 | 星期 | 当日工时 | 假期类型 | 周总工时 | 调整后周标准工时 | 当日加班工时 | 周总加班工时 |

1. 计算调整后周标准工时 #

在「调整后周标准工时」列（以第2行为例，对应单元格I2）输入公式：

=40 - COUNTIFS($B:$B, $B2, $G:$G, "Personal Holiday")*8 - COUNTIFS($B:$B, $B2, $G:$G, "Sick Day")*8 - COUNTIFS($B:$B, $B2, $G:$G, "Public Holiday")*8

逻辑：通过COUNTIFS按周数匹配，统计当周各类假期的天数，每类假期每天扣除8小时，从默认40小时基准中扣减。

2. 计算周总工时 #

在「周总工时」列（第2行对应单元格H2）输入公式：

=SUMIFS($F:$F, $B:$B, $B2)

逻辑：SUMIFS自动汇总当前周数下所有行的当日工时总和。

3. 计算当日加班工时 #

在「当日加班工时」列（第2行对应单元格J2）输入公式：

=IF(AND($F2>8, $H2>$I2), MAX(0, $F2-8), 0)

逻辑拆解：

• 先判断「当日工时>8」且「周总工时>调整后周标准工时」双条件是否成立
• 若成立，取当日工时超出8小时的部分；若不成立，返回0

4. 计算周总加班工时 #

在「周总加班工时」列（第2行对应单元格K2）输入公式：

=SUMIFS($J:$J, $B:$B, $B2)

逻辑：自动汇总当前周数下所有符合条件的当日加班工时总和。

周六出勤特殊处理 #

若周六为正常出勤（非假期），上述公式自动适配：

• 若周六当日工时超8小时，且当周总工时超调整后标准，超出部分计入加班
• 若周六工时未超8小时，或当周总工时未达标，则不计入加班

动态效果说明 #

所有公式均以「周数」作为匹配维度，新增行、修改周数或调整假期信息时，公式会自动重新计算对应周的所有统计值，无需手动调整数据范围。

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