用Excel的內置函數　解決獎金發放問題

平淡

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）。