平淡

1.需求分析

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

2.對(duì)銷售金額排序

在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）”，下拉公式即可完成對(duì)組員的引用（圖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)計(jì)各小組的總銷售金額（圖3）。

4.計(jì)算小組獎(jiǎng)金

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

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

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

6.計(jì)算“1組”組員的實(shí)發(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））”，下拉公式即可計(jì)算出“1組”組員的實(shí)發(fā)金額（圖6）。

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

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

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

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