excel函數(shù)應(yīng)用:SUM函數(shù)并不僅僅能用于求和

對(duì)于經(jīng)常使用Excel的辦公職員來(lái)說(shuō),SUM是一個(gè)簡(jiǎn)單的不能再簡(jiǎn)單的函數(shù)了,公司幾乎所有的人都知道用這個(gè)函數(shù)可以求和,但是有一個(gè)文員小姐姐一連用SUM完成了三個(gè)并非求和的任務(wù),就被經(jīng)理看中了,直接提拔為自己的助理。到底發(fā)生了什么事呢?還得從頭說(shuō)起……
某日經(jīng)理召集部門內(nèi)的表哥表姐們做一次內(nèi)部選拔,打算物色一名助理,為此準(zhǔn)備了三個(gè)任務(wù)讓大家逐一完成,小姐姐也在候選人當(dāng)中。
任務(wù)1:使用SUM函數(shù)批量制作標(biāo)簽
這里說(shuō)的標(biāo)簽其實(shí)是一種直接在Excel里錄入后打印出來(lái)使用的小標(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í)停止,接下來(lái)就是一連串的操作:對(duì)C列排序,然后選擇A列區(qū)域,F(xiàn)5定位后輸入=A3,Ctrl?回車完成填充旁邊還在復(fù)制粘貼的同事瞬間被雷到……

公式很簡(jiǎn)單,就一個(gè)SUM函數(shù)和一個(gè)ROW函數(shù),操作也很簡(jiǎn)單,排序定位加上批量填充的操作,但是誰(shuí)讓你就想不到呢?
想問(wèn)公式的原理?簡(jiǎn)單的數(shù)學(xué)問(wèn)題,實(shí)在想不通的話就把公式記下吧,我們趕快看第二個(gè)任務(wù)是什么。
任務(wù)2:快速按部門編寫序號(hào)

在這個(gè)表格中需要對(duì)A列進(jìn)行編號(hào),規(guī)則是部門發(fā)生變化時(shí)序號(hào)才遞增。
接到這個(gè)任務(wù)之后,大家又開(kāi)始各自琢磨,有人開(kāi)始嘗試各種公式,有人開(kāi)始琢磨用操作技巧完成,小姐姐直接用SUM秒殺:

公式夠簡(jiǎn)單吧:=SUM(A1,B1B2),利用了SUM忽略文本和可對(duì)邏輯值計(jì)算的特性,第二招出手,驚嘆聲一片,經(jīng)理也無(wú)法保持淡定,直接發(fā)出了第三個(gè)任務(wù)。
任務(wù)3:計(jì)算階梯返利額
按照公司的規(guī)定,要按照各經(jīng)銷商的年銷售額進(jìn)行返利,具體返利規(guī)則為:年回款200萬(wàn)以內(nèi)返點(diǎn)5%,?超過(guò)200到350萬(wàn)的部分返7%,超過(guò)350萬(wàn)到500萬(wàn)的部分返10%,超過(guò)500萬(wàn)到700萬(wàn)的部分返13%,超過(guò)700萬(wàn)的部分返17%。一共分為五個(gè)階梯,舉個(gè)簡(jiǎn)單的例子:

以經(jīng)銷商A來(lái)說(shuō),銷售額是225.02萬(wàn)元,返還金額就是200*0.05+(225.02-200)*0.07,換個(gè)思路,還可以這樣算:225.02*0.05+(225.02-200)*(0.07-0.05)

這還只是涉及到兩級(jí)的算法,如果是五個(gè)級(jí)別都考慮的話……
在弄明白了計(jì)算方法以后,大家又開(kāi)始埋頭苦干,有一級(jí)一級(jí)往上疊加的,有開(kāi)始嵌套if的,不管是什么方法,四級(jí)以后都有了眩暈感,此時(shí)經(jīng)理又說(shuō)了句,明年考慮把返利等級(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萬(wàn),200萬(wàn),350萬(wàn),500萬(wàn),700萬(wàn);
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}的由來(lái):200萬(wàn)內(nèi)返5個(gè)點(diǎn),超過(guò)200萬(wàn)到350萬(wàn)的部分返7個(gè)點(diǎn),比200萬(wàn)內(nèi)的返點(diǎn)多2個(gè)點(diǎn),后來(lái)以此類推。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)金額。
好吧,肯定還是有一大波人無(wú)法領(lǐng)會(huì)其中的奧妙,但不管怎么樣,小姐姐是毫無(wú)懸念的脫穎而出了。
通過(guò)今天分享的這個(gè)故事,可以得到一個(gè)結(jié)論,Excel用的溜真的有錢途哦!
小結(jié)
1.案例一其實(shí)還有很多其他的解法,比如使用復(fù)雜的數(shù)組公式,還有使用REPT函數(shù)結(jié)合換行符后再用Word去完成,本文提到的SUM解法,相對(duì)比較玄妙,思路過(guò)于奇巧,有用到這種問(wèn)題的話可以直接套路搬走。
2.案例二也并不復(fù)雜,其實(shí)就是對(duì)部門進(jìn)行不重復(fù)計(jì)數(shù)的公式,常見(jiàn)的是
=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真的是博大精深,妙趣無(wú)窮。
****部落窩教育-excel巧用SUM函數(shù)****
原創(chuàng):老菜鳥(niǎo)/部落窩教育(未經(jīng)同意,請(qǐng)勿轉(zhuǎn)載)
更多教程:部落窩教育(www.itblw.com)
微信公眾號(hào):exceljiaocheng