多条件匹配取值计算员工绩效奖金总额的实现方案咨询

方法1：Excel实现（适合普通办公场景） #

操作步骤：

• 把「岗位奖金标准表」「员工周度完成表」分别选中数据区域按Ctrl+T转为超级表，分别命名为奖金标准、完成记录
• 在「完成记录」表新增一列「单条可核算奖金」，输入公式：
  =XLOOKUP(1,(奖金标准[Position]=[@Position])*(奖金标准[Bonus name]=[@[Bonus name]]),奖金标准[Value],0)*[@[Performed or not]]
• 选中完成记录任意单元格插入数据透视表，行区域拖入「Employee ID（员工ID）」，值区域拖入「单条可核算奖金」并设置求和，即可得到你要的结果。如果需要同步展示员工姓名，额外用XLOOKUP关联员工基础信息表匹配ID即可。

方法2：SQL实现（适合数据存储在数据库的场景） #

假设三张表命名分别为：

• 奖金标准表：bonus_rule
• 周度完成表：emp_performance
• 员工基础信息表：emp_info
  查询语句如下：

SELECT 
  emp_performance.`Employee ID` AS 员工ID,
  -- 如需同步展示姓名，取消下面一行注释
  -- emp_info.`员工姓名`,
  CONCAT('$', SUM(bonus_rule.`Value` * emp_performance.`Performed or not`)) AS 总奖金额
FROM emp_performance
LEFT JOIN bonus_rule 
  ON emp_performance.Position = bonus_rule.Position 
  AND emp_performance.`Bonus name` = bonus_rule.`Bonus name`
-- 如需关联员工基础信息，取消下面一行注释
-- LEFT JOIN emp_info ON emp_performance.`Employee ID` = emp_info.`员工ID`
WHERE emp_performance.`Performed or not` = 1
GROUP BY emp_performance.`Employee ID`
-- 若查询了姓名，GROUP BY后面需要同步加 emp_info.`员工姓名`

特殊场景调整 #

如果业务规则要求同个员工同一周期内，同一项奖金不管完成多少次仅发放一次，只需在分组前先对员工ID、岗位、奖金名称字段去重即可。

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