小編有話說：真是沒想到呀，日常工作中那么復(fù)雜的難題，用sum函數(shù)3秒就解決了。很多時(shí)候，決定我們升職加薪的并不是什么大事情，往往就是這些小工作，其他人做很久，而你幾秒就解決了，老板也忍不住給你點(diǎn)贊呀！

對(duì)于經(jīng)常使用Excel的辦公職員來說，SUM是一個(gè)簡(jiǎn)單的不能再簡(jiǎn)單的函數(shù)了，公司幾乎所有的人都知道用這個(gè)函數(shù)可以求和，但是有一個(gè)文員小姐姐一連用SUM完成了三個(gè)并非求和的任務(wù)，就被經(jīng)理看中了，直接提拔為自己的助理。到底發(fā)生了什么事呢？還得從頭說起……

某日經(jīng)理召集部門內(nèi)的表哥表姐們做一次內(nèi)部選拔，打算物色一名助理，為此準(zhǔn)備了三個(gè)任務(wù)讓大家逐一完成，小姐姐也在候選人當(dāng)中。

任務(wù)1：使用SUM函數(shù)批量制作標(biāo)簽

這里說的標(biāo)簽其實(shí)是一種直接在Excel里錄入后打印出來使用的小標(biāo)簽，如圖所示：

需要按照B列的數(shù)字，做出對(duì)應(yīng)數(shù)量的標(biāo)簽，要求很簡(jiǎn)單，不怕麻煩的話可以復(fù)制粘貼，當(dāng)然如果是復(fù)制粘貼的話，大家都會(huì)，小姐姐當(dāng)然不會(huì)這樣做，請(qǐng)看：

在后面一列使用公式=SUM(B$2:B2)-ROW(A1)下拉，一直到出現(xiàn)0時(shí)停止，接下來就是一連串的操作：對(duì)C列排序，然后選擇A列區(qū)域，F5定位后輸入=A3，Ctrl 回車完成填充

旁邊還在復(fù)制粘貼的同事瞬間被雷到……

公式很簡(jiǎn)單，就一個(gè)SUM函數(shù)和一個(gè)ROW函數(shù)，操作也很簡(jiǎn)單，排序定位加上批量填充的操作，但是誰讓你就想不到呢？

想問公式的原理？簡(jiǎn)單的數(shù)學(xué)問題，實(shí)在想不通的話就把公式記下吧，我們趕快看第二個(gè)任務(wù)是什么。

任務(wù)2：快速按部門編寫序號(hào)

在這個(gè)表格中需要對(duì)A列進(jìn)行編號(hào)，規(guī)則是部門發(fā)生變化時(shí)序號(hào)才遞增。

接到這個(gè)任務(wù)之后，大家又開始各自琢磨，有人開始嘗試各種公式，有人開始琢磨用操作技巧完成，小姐姐直接用SUM秒殺：

公式夠簡(jiǎn)單吧：=SUM(A1,B1<>B2)，利用了SUM忽略文本和可對(duì)邏輯值計(jì)算的特性，第二招出手，驚嘆聲一片，經(jīng)理也無法保持淡定，直接發(fā)出了第三個(gè)任務(wù)。

任務(wù)3：計(jì)算階梯返利額

按照公司的規(guī)定，要按照各經(jīng)銷商的年銷售額進(jìn)行返利，具體返利規(guī)則為：年回款200萬以內(nèi)返點(diǎn)5%， 超過200到350萬的部分返7%，超過350萬到500萬的部分返10%，超過500萬到700萬的部分返13%，超過700萬的部分返17%。一共分為五個(gè)階梯，舉個(gè)簡(jiǎn)單的例子：

以經(jīng)銷商A來說，銷售額是225.02萬元，返還金額就是200*0.05+（225.02-200）*0.07，換個(gè)思路，還可以這樣算：225.02*0.05+（225.02-200）*（0.07-0.05）

這還只是涉及到兩級(jí)的算法，如果是五個(gè)級(jí)別都考慮的話……

在弄明白了計(jì)算方法以后，大家又開始埋頭苦干，有一級(jí)一級(jí)往上疊加的，有開始嵌套if的，不管是什么方法，四級(jí)以后都有了眩暈感，此時(shí)經(jīng)理又說了句，明年考慮把返利等級(jí)從五級(jí)調(diào)整到八級(jí)，以便計(jì)算時(shí)更加細(xì)化，一時(shí)間眾人皆倒……

小姐姐不緊不慢的提交了自己寫的公式，充滿了套路的一個(gè)公式：

=SUM(TEXT((B2-{0,2,3.5,5,7}*100)*{5,2,3,3,4},"0.00;!0")%)

對(duì)于這個(gè)公式，經(jīng)理也有點(diǎn)發(fā)懵，考慮到大家看到這個(gè)公式后的不同反應(yīng)，對(duì)公式的要點(diǎn)進(jìn)行解析：

 1、B2-{0,2,3.5,5,7}*100，用客戶年銷售額分別減去0萬，200萬，350萬，500萬，700萬；

2、TEXT((B3-{0,2,3.5,5,7}*100)*{5,2,3,3,4},"0.00;!0")%，是將第1步相減結(jié)果分別乘以5，2，3，3，4（這是相鄰兩個(gè)級(jí)別之間提成比例從差值），用TEXT將結(jié)果為負(fù)數(shù)的直接轉(zhuǎn)化為0，再縮小100倍（%的作用）。常量數(shù)組{5,2,3,3,4}的由來：200萬內(nèi)返5個(gè)點(diǎn)，超過200萬到350萬的部分返7個(gè)點(diǎn)，比200萬內(nèi)的返點(diǎn)多2個(gè)點(diǎn)，后來以此類推。TEXT函數(shù)第二參數(shù)"0.00;!0"，意指正數(shù)保留兩位小數(shù)，負(fù)數(shù)直接轉(zhuǎn)化為0。

3、SUM(TEXT((B3-{0,2,3.5,5,7}*10^6)*{5,2,3,3,4},"0.00;!0")%)，將第2步計(jì)算的各段返點(diǎn)金額加總，得到累計(jì)返點(diǎn)金額。

好吧，肯定還是有一大波人無法領(lǐng)會(huì)其中的奧妙，但不管怎么樣，小姐姐是毫無懸念的脫穎而出了。

通過今天分享的這個(gè)故事，可以得到一個(gè)結(jié)論，Excel用的溜真的有錢途哦！

小結(jié)

1.案例一其實(shí)還有很多其他的解法，比如使用復(fù)雜的數(shù)組公式，還有使用REPT函數(shù)結(jié)合換行符后再用Word去完成，本文提到的SUM解法，相對(duì)比較玄妙，思路過于奇巧，有用到這種問題的話可以直接套路搬走。

2.案例二也并不復(fù)雜，其實(shí)就是對(duì)部門進(jìn)行不重復(fù)計(jì)數(shù)的公式，常見的是

=SUMPRODUCT(1/COUNTIF($B$2:B2,$B$2:B2))這個(gè)公式，本例中是對(duì)部門進(jìn)行了排序，才能取巧的。

3.案例三就非常有用了，雖然公式比較難，好處是擴(kuò)展性強(qiáng)，在遇到計(jì)算各種階梯價(jià)格的時(shí)候?qū)街械膬蓚€(gè)常量數(shù)組進(jìn)行調(diào)整就可以直接套用。

Excel真的是博大精深，妙趣無窮。

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

如果您因工作所需使用到Excel，不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。

如果您想要隨時(shí)隨地學(xué)習(xí)excel，掃下方二維碼，可關(guān)注公眾號(hào)，每日為您推送優(yōu)質(zhì)excel教程：

 Excel教程相關(guān)推薦：