如何基于两列值条件计算员工工作量总和？sumifs实现方案

问题背景 #

现有一张记录员工任务的表格（示例数据位于Sheet1的A1:D5区域）：

需要按以下规则计算每位员工的总工作量：

• 若员工参与的任务无搭档（员工2列空），全额计入该任务工作量
• 若员工参与的任务有搭档，按任务工作量的50%计入

预期输出：

实现方法 #

方法1：SUMPRODUCT函数（兼容全版本Excel） #

在结果工作表（如Sheet2）的A2单元格输入员工姓名，B2单元格使用以下公式计算总工作量，下拉公式即可批量计算：

=SUMPRODUCT(
    ((Sheet1!$B$2:$B$5=A2)+(Sheet1!$C$2:$C$5=A2)),
    Sheet1!$D$2:$D$5 / (1 + (Sheet1!$B$2:$B$5<>"")*(Sheet1!$C$2:$C$5<>""))
)

公式逻辑说明：

1. ((Sheet1!$B$2:$B$5=A2)+(Sheet1!$C$2:$C$5=A2))：判断当前员工是否在任务的员工1或员工2列，匹配返回1，不匹配返回0
2. 1 + (Sheet1!$B$2:$B$5<>"")*(Sheet1!$C$2:$C$5<>"")：有搭档时返回2，无搭档时返回1，用于工作量的除数
3. 两组数组相乘后求和，得到符合规则的总工作量

方法2：Excel 365动态数组解法（自动生成所有结果） #

若使用Excel 365或新版WPS，可一次性生成所有员工的姓名和对应工作量：

1. 提取所有不重复员工姓名：

=UNIQUE(VSTACK(Sheet1!$B$2:$B$5,Sheet1!$C$2:$C$5))

2. 批量计算每位员工的总工作量：

=BYROW(UNIQUE(VSTACK(Sheet1!$B$2:$B$5,Sheet1!$C$2:$C$5)),LAMBDA(name,
    SUM(
        FILTER(
            Sheet1!$D$2:$D$5 / IF((Sheet1!$B$2:$B$5<>"")*(Sheet1!$C$2:$C$5<>""),2,1),
            (Sheet1!$B$2:$B$5=name)+(Sheet1!$C$2:$C$5=name)
        )
    )
))

该公式会自动筛选出当前员工参与的所有任务，按规则计算后求和，无需手动下拉。

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