基于部门在Excel工作表中计算计划工时的问题求助

需求说明 #

• 计算逻辑：按员工所属部门，对「下班」列减去「上班」列的结果求和
• 汇总目标：将VS和Cleaning两个部门的总计划工时，按日期列对应汇总到C22:O22区域

之前尝试的错误公式 #

1. =IF(OR($B$4:$B$20="VS";$B$4:$B$20="Cleaning");TEXT((SUM(F4:F20)-SUM(E4:E20));"t");""))
2. =TEXT(SUM((IF(B4:B20="VS";SUM(D4:D20);0))-(IF(B4:B20="Cleaning";SUM(C4:C20);0)));"t")

错误原因分析 #

• 第一个公式：OR($B$4:$B$20="VS";$B$4:$B$20="Cleaning")是对整个区域做判断，而非逐行筛选部门；且SUM(F4:F20)-SUM(E4:E20)直接求和整列，未过滤指定部门的行，结果完全不符合需求
• 第二个公式：嵌套的SUM(D4:D20)/SUM(C4:C20)直接求和整列，没有对应到符合部门条件的行；同时错误地将两个部门的计算做减法，逻辑混乱

正确公式方案 #

方案1：SUMPRODUCT（兼容所有Excel版本） #

在C22单元格输入以下公式，然后向右拖拽填充至O22：

=TEXT(SUMPRODUCT((($B$4:$B$20="VS")+($B$4:$B$20="Cleaning"))*(C$4:C$20-B$4:B$20)),"t")

公式说明：

• ($B$4:$B$20="VS")+($B$4:$B$20="Cleaning")：逐行判断是否为VS或Cleaning部门，符合条件返回1，否则返回0
• *(C$4:C$20-B$4:B$20)：仅保留符合部门条件的行，计算「下班-上班」的工时差
• SUMPRODUCT对所有符合条件的工时差求和，TEXT(...)将结果格式化为时间格式

方案2：SUM+FILTER（适用于支持动态数组的Excel版本） #

在C22单元格输入以下公式，公式会自动向右填充：

=TEXT(SUM(FILTER(C$4:C$20-B$4:B$20,($B$4:$B$20="VS")+($B$4:$B$20="Cleaning"))),"t")

公式说明：

• FILTER(...)筛选出VS和Cleaning部门的行，计算对应的工时差
• SUM对筛选后的工时差求和，TEXT(...)格式化时间格式

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