平淡

1.需求分析

假設公司有2个业务小组，销售数据如图所示（图1），奖金总额为每次1万元。解题思路是先按照每个小组的总销售金额算出小组奖金，然后对各个小组内的销售金额进行降序排列，按照从高到低依次发放奖金。同时需要对累计应发金额和小组获得奖励金额做出判断，当累计应发金额大于小组获得奖励金额时，此时该员工的奖金就是“小组获得奖励金额-截止上一员工已经实发的金额”，后续的员工则没有奖励。具体操作步骤如下：

2.对销售金额排序

在H2单元格中输入公式“=LARGE（C：C，ROW（C1））”，下拉公式即可按降序排列销售金额。再定位到F2单元格并输入公式“=VLOOKUP（H2，IF（{1，0}，$C$1：$C$16，$A$1：$A$16），2，0）”，下拉公式即可完成组别的引用。继续在G2单元格中输入公式“=VLOOKUP（H2，IF（{1，0}，$C$1：$C$16，$B$1：$B$16），2，0）”，下拉公式即可完成对组员的引用（图2）。

3.获取分组销售数据

选中F列，依次点击“数据→筛选”，选择只显示“1组”。复制筛选后的“1组”数据，接着新建工作表2（假设原始数据保存在工作表1中），然后将复制的数据粘贴到工作表2的A5：C10数据区域。操作同上，在工作表1中筛选“2组”数据，复制后将其粘贴到工作表2的G5：I15数据区域。然后分别在C11、I16单元格中统计各小组的总销售金额（图3）。

4.计算小组奖金

在工作表2的B 2单元格中输入公式“=C11/（C11+I16）*C2”、B3单元格中输入公式“=I16/（C11+I16）*C2”，即可算出各小组的奖金数额（图4）。

5.计算组员应发奖金

根据发放规则，在D6单元格中输入公式“=C6*2%”，下拉公式即可计算出“1组”组员的应发奖金（图5）。同上，在J6单元格中输入公式“=I6*2%”，下拉公式即可计算出“2组”组员的应发奖金。

6.计算“1组”组员的实发金额

在E6单元格中输入公式“=I FS（SUM（D$5：D6）<$B$2，C6*2%，SUM（E$5：E5）>=$B$2，0，SUM（D$5：D6）>=$B$2，$B$2-SUM（D$5：D5））”，下拉公式即可计算出“1组”组员的实发金额（图6）。

公式解释：使用I F S 函数执行多条件判断。如果“S U M（D$5：D6）”（即累计的应发金额）小于B2单元格中的数值（即小组获得的总奖金），那么该组员可以按照“销售金额×2%”来足额发放奖金。如果“SUM（E$5：E5）”（即截止到前一个组员已累计实发的金额）大于等于小组获得的总奖金，那么表示截止到前一个组员奖金已经全部发放完毕，剩余组员无法获得奖励。比如员工5，由于之前组员发放的奖金已经达到了4859.58元，因此他及以后的组员将无法获得奖励。

如果累计应发金额大于等于小组获得的总奖金，那就表示奖金已经全部发放完毕，而员工最多只能得到“小组获得的总奖金-截止到上一个组员的累计已发金额”。比如员工3本应获得1062.6元的奖励，但截止到他的应发金额为5538.76元，大于小组获得的总奖金4859.58元，前面员工1、2、4已经发放了4476.16元，所以他只能得到“4859.58-4476.16=383.42”元。

7.计算“2组”组员的实发金额

在K6单元格中输入公式“=IFS（SUM（J$5：J6）<$B$3，I6*2%，SUM（K$5：K5）>=$B$3，0，SUM（J$5：J6）>=$B$3，$B$3-SUM（J$5：J5））”，下拉公式到对应的单元格，即可算出“2组”组员的实发金额，公式解释同上（图7）。

完成上述的操作后，将无关数据隐藏，为实发金额添加颜色标注，并发放奖金即可（图8）。