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

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

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

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

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

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

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

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

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

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

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

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

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

公式夠簡單吧：=SUM(A1,B1<>B2)，利用了SUM忽略文本和可對邏輯值計(jì)算的特性，第二招出手，驚嘆聲一片，經(jīng)理也無法保持淡定，直接發(fā)出了第三個任務(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%。一共分為五個階梯，舉個簡單的例子：

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

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

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

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

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

對于這個公式，經(jīng)理也有點(diǎn)發(fā)懵，考慮到大家看到這個公式后的不同反應(yīng)，對公式的要點(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（這是相鄰兩個級別之間提成比例從差值），用TEXT將結(jié)果為負(fù)數(shù)的直接轉(zhuǎn)化為0，再縮小100倍（%的作用）。常量數(shù)組{5,2,3,3,4}的由來：200萬內(nèi)返5個點(diǎn)，超過200萬到350萬的部分返7個點(diǎn)，比200萬內(nèi)的返點(diǎn)多2個點(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)會其中的奧妙，但不管怎么樣，小姐姐是毫無懸念的脫穎而出了。

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

小結(jié)

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

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

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

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

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

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

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

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

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