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

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

某日經理召集部門內的表哥表姐們做一次內部選拔，打算物色一名助理，為此準備了三個任務讓大家逐一完成，小姐姐也在候選人當中。

任務1：使用SUM函數批量制作標簽

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

需要按照B列的數字，做出對應數量的標簽，要求很簡單，不怕麻煩的話可以復制粘貼，當然如果是復制粘貼的話，大家都會，小姐姐當然不會這樣做，請看：

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

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

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

想問公式的原理？簡單的數學問題，實在想不通的話就把公式記下吧，我們趕快看第二個任務是什么。

任務2：快速按部門編寫序號

在這個表格中需要對A列進行編號，規(guī)則是部門發(fā)生變化時序號才遞增。

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

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

任務3：計算階梯返利額

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

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

這還只是涉及到兩級的算法，如果是五個級別都考慮的話……

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

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

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

對于這個公式，經理也有點發(fā)懵，考慮到大家看到這個公式后的不同反應，對公式的要點進行解析：

 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步相減結果分別乘以5，2，3，3，4（這是相鄰兩個級別之間提成比例從差值），用TEXT將結果為負數的直接轉化為0，再縮小100倍（%的作用）。常量數組{5,2,3,3,4}的由來：200萬內返5個點，超過200萬到350萬的部分返7個點，比200萬內的返點多2個點，后來以此類推。TEXT函數第二參數"0.00;!0"，意指正數保留兩位小數，負數直接轉化為0。

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

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

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

小結

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

2.案例二也并不復雜，其實就是對部門進行不重復計數的公式，常見的是

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

3.案例三就非常有用了，雖然公式比較難，好處是擴展性強，在遇到計算各種階梯價格的時候對公式中的兩個常量數組進行調整就可以直接套用。

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

本文配套的練習課件請加入QQ群：264539405下載。

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

如果您想要隨時隨地學習excel，掃下方二維碼，可關注公眾號，每日為您推送優(yōu)質excel教程：

 Excel教程相關推薦：