用Excel的內(nèi)置函數(shù)　解決獎金發(fā)放問題

平淡

1.需求分析

假設(shè)公司有2個業(yè)務(wù)小組，銷售數(shù)據(jù)如圖所示（圖1），獎金總額為每次1萬元。解題思路是先按照每個小組的總銷售金額算出小組獎金，然后對各個小組內(nèi)的銷售金額進(jìn)行降序排列，按照從高到低依次發(fā)放獎金。同時需要對累計應(yīng)發(fā)金額和小組獲得獎勵金額做出判斷，當(dāng)累計應(yīng)發(fā)金額大于小組獲得獎勵金額時，此時該員工的獎金就是“小組獲得獎勵金額-截止上一員工已經(jīng)實發(fā)的金額”，后續(xù)的員工則沒有獎勵。具體操作步驟如下：

2.對銷售金額排序

在H2單元格中輸入公式“=LARGE（C：C，ROW（C1））”，下拉公式即可按降序排列銷售金額。再定位到F2單元格并輸入公式“=VLOOKUP（H2，IF（{1，0}，$C$1：$C$16，$A$1：$A$16），2，0）”，下拉公式即可完成組別的引用。繼續(xù)在G2單元格中輸入公式“=VLOOKUP（H2，IF（{1，0}，$C$1：$C$16，$B$1：$B$16），2，0）”，下拉公式即可完成對組員的引用（圖2）。

3.獲取分組銷售數(shù)據(jù)

選中F列，依次點(diǎn)擊“數(shù)據(jù)→篩選”，選擇只顯示“1組”。復(fù)制篩選后的“1組”數(shù)據(jù)，接著新建工作表2（假設(shè)原始數(shù)據(jù)保存在工作表1中），然后將復(fù)制的數(shù)據(jù)粘貼到工作表2的A5：C10數(shù)據(jù)區(qū)域。操作同上，在工作表1中篩選“2組”數(shù)據(jù)，復(fù)制后將其粘貼到工作表2的G5：I15數(shù)據(jù)區(qū)域。然后分別在C11、I16單元格中統(tǒng)計各小組的總銷售金額（圖3）。

4.計算小組獎金

在工作表2的B 2單元格中輸入公式“=C11/（C11+I16）*C2”、B3單元格中輸入公式“=I16/（C11+I16）*C2”，即可算出各小組的獎金數(shù)額（圖4）。

5.計算組員應(yīng)發(fā)獎金

根據(jù)發(fā)放規(guī)則，在D6單元格中輸入公式“=C6*2%”，下拉公式即可計算出“1組”組員的應(yīng)發(fā)獎金（圖5）。同上，在J6單元格中輸入公式“=I6*2%”，下拉公式即可計算出“2組”組員的應(yīng)發(fā)獎金。

6.計算“1組”組員的實發(fā)金額

在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組”組員的實發(fā)金額（圖6）。

公式解釋：使用I F S 函數(shù)執(zhí)行多條件判斷。如果“S U M（D$5：D6）”（即累計的應(yīng)發(fā)金額）小于B2單元格中的數(shù)值（即小組獲得的總獎金），那么該組員可以按照“銷售金額×2%”來足額發(fā)放獎金。如果“SUM（E$5：E5）”（即截止到前一個組員已累計實發(fā)的金額）大于等于小組獲得的總獎金，那么表示截止到前一個組員獎金已經(jīng)全部發(fā)放完畢，剩余組員無法獲得獎勵。比如員工5，由于之前組員發(fā)放的獎金已經(jīng)達(dá)到了4859.58元，因此他及以后的組員將無法獲得獎勵。

如果累計應(yīng)發(fā)金額大于等于小組獲得的總獎金，那就表示獎金已經(jīng)全部發(fā)放完畢，而員工最多只能得到“小組獲得的總獎金-截止到上一個組員的累計已發(fā)金額”。比如員工3本應(yīng)獲得1062.6元的獎勵，但截止到他的應(yīng)發(fā)金額為5538.76元，大于小組獲得的總獎金4859.58元，前面員工1、2、4已經(jīng)發(fā)放了4476.16元，所以他只能得到“4859.58-4476.16=383.42”元。

7.計算“2組”組員的實發(fā)金額

在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））”，下拉公式到對應(yīng)的單元格，即可算出“2組”組員的實發(fā)金額，公式解釋同上（圖7）。

完成上述的操作后，將無關(guān)數(shù)據(jù)隱藏，為實發(fā)金額添加顏色標(biāo)注，并發(fā)放獎金即可（圖8）。