Excel Conditional Formatting 按周求和时长并按阈值高亮单元格

1. 提取时长数值并计算每周总和 #

由于B、C列的时长为带hrs后缀的文本格式，无法直接计算，需先提取数值再求和：
假设你需要在D列对应行存储每周总时长，选中D列首个要存放结果的单元格（如D2），输入如下公式后下拉填充到所有对应行即可：

=SUM(VALUE(LEFT(B2,LEN(B2)-3)),VALUE(LEFT(C2,LEN(C2)-3)))

公式说明：

• LEFT(B2,LEN(B2)-3)：提取B2单元格内容中除最后3位（即hrs后缀）的文本内容
• VALUE()：将提取到的数字文本转为可计算的数值格式
• SUM()：对B、C两列的数值求和得到周总时长

如果你的数据是按周分组多行存储，需要按周维度汇总总和，可搭配SUMIF或SUMIFS函数匹配周标识后再求和即可。

2. 设置条件格式规则 #

• 选中所有已计算完成的周总时长单元格区域
• 依次点击顶部菜单栏「开始」→「条件格式」→「新建规则」
• 新建第一条规则（大于20hrs标绿色）：
  1. 规则类型选择「使用公式确定要设置格式的单元格」
  2. 公式输入框填入=D2>20（请将D2替换为你选中区域的首个单元格地址，不要加$绝对引用符号，确保规则可批量适配所有选中单元格）
  3. 点击「格式」→「填充」，选择目标绿色后确认保存规则
• 新建第二条规则（小于20hrs标红色）：
  1. 重复上述新建规则操作，公式输入=D2<20
  2. 格式设置填充色为红色后保存规则即可

如果需要处理总时长刚好等于20hrs的场景，可自行新增第三条规则自定义格式。

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