編者按：

各位小伙伴們好！眾所周知Excel函數(shù)功能非常強(qiáng)大，擅長Excel函數(shù)能夠迅速提升我們的工作效率。但是Excel函數(shù)包含的門類繁多，短時(shí)間精通全部函數(shù)是天方夜譚。我們可以根據(jù)自己的工作需要或者興趣選擇某一部分進(jìn)行學(xué)習(xí)、研究。這一次我們?yōu)榇蠹曳钌狭?span>20個(gè)工作中常用函數(shù)公式，一起去看看吧~

職場(chǎng)人不會(huì)Excel函數(shù)公式怎么行？但是Excel函數(shù)公式那么多，又沒時(shí)間都學(xué)一遍，咋辦？

別急！這不就為你準(zhǔn)備了職場(chǎng)人必須掌握的20個(gè)公式嗎，涉及到8種工作場(chǎng)景！廢話不多說，馬上來干貨。

注：公式都可以直接套用，就不挨個(gè)解釋原理了，相關(guān)的函數(shù)教程公眾號(hào)里以前都講過的。

場(chǎng)景1：帶小計(jì)的數(shù)據(jù)怎么算總計(jì)

以下圖中的數(shù)據(jù)為例，這樣的數(shù)據(jù)非常常見吧。

每個(gè)部門的小計(jì)都是算好的，現(xiàn)在要計(jì)算總計(jì)，給你三個(gè)公式：

【公式1】=SUM(C2:C26)/2

【公式2】=SUMIF(A:A,"小計(jì)",C:C)

【公式3】=SUMIF(B:B,"<>",C:C)

場(chǎng)景2：帶單位的數(shù)字怎么求和

例如，每個(gè)人的獎(jiǎng)金都是帶單位“元”的，如果直接用SUM函數(shù)求和，最終結(jié)果為零。

針對(duì)這類問題，給你兩個(gè)公式：

【公式4】=SUMPRODUCT(--SUBSTITUTE(C2:C21,"元",""))

【公式5】=SUMPRODUCT(--LEFT(C2:C21,LEN(C2:C21)-1))

場(chǎng)景3：帶合并單元格的數(shù)據(jù)怎么統(tǒng)計(jì)

以下圖為例，有單元格被合并的情況下，要統(tǒng)計(jì)部門人數(shù)和獎(jiǎng)金合計(jì)。

統(tǒng)計(jì)部門人數(shù)：

【公式6】 =COUNTA(B2:B21)-SUM(D3:D22)

統(tǒng)計(jì)獎(jiǎng)金合計(jì)：

【公式7】=SUM(C2:C21)-SUM(E3:E22)

注意：這兩個(gè)公式需要先選擇合并單元格區(qū)域，再輸入公式，最后按快捷鍵“Ctrl+Enter”完成輸入。

場(chǎng)景4：按部門和費(fèi)用項(xiàng)目匹配費(fèi)用金額

效果如圖所示：

針對(duì)這類問題，提供三個(gè)公式：

【公式8】=SUMIFS(C:C,A:A,E2,B:B,F2)

【公式9】=SUMPRODUCT((A2:A21=E2)*(B2:B21=F2)*C2:C21)

【公式10】=LOOKUP(1,0/(A2:A21=E2)/(B2:B21=F2),C2:C21)

場(chǎng)景5：重復(fù)單據(jù)號(hào)辨別

對(duì)所有的重復(fù)單據(jù)號(hào)加備注說明。

【公式11】=IF(COUNTIF(A:A,A2)>1,"重復(fù)","")

單據(jù)號(hào)第二次出現(xiàn)才算重復(fù)。

【公式12】=IF(COUNTIF($A$1:A2,A2)>1,"重復(fù)","")

場(chǎng)景6：排名次

對(duì)績效獎(jiǎng)金進(jìn)行排名，所有人都參加。

【公式13】=RANK(C2,$C$2:$C$21)

注意，上面這種排名當(dāng)出現(xiàn)相同名次的時(shí)候，會(huì)占用排名，例如有兩個(gè)5，接下來直接就是7，6就被占用了。

如果想要出現(xiàn)相同名次但是又不占用排名，在F2單元格輸入下面這個(gè)公式：

【公式14】=SUMPRODUCT(($C$2:$C$21>=C2)/COUNTIF($C$2:$C$21,$C$2:$C$21))

有兩個(gè)5的時(shí)候，還是會(huì)有6，名次不會(huì)出現(xiàn)空缺。

另外還有一種比較常用的就是部門內(nèi)的排名。

【公式15】=SUMPRODUCT(($C$2:$C$21>=C2)*($A$2:$A$21=A2))

場(chǎng)景7：編序號(hào)

給員工編序號(hào)，同一個(gè)部門的員工序號(hào)都是從1開始順延。

【公式16=COUNTIF($B$1:B2,B2)

對(duì)部門編序號(hào)，同一個(gè)部門序號(hào)相同，部門不同時(shí)序號(hào)遞增。

【公式17】=N(A1)+(C2<>C1)

場(chǎng)景8：與手機(jī)號(hào)有關(guān)的三個(gè)公式

從個(gè)人信息中提取手機(jī)號(hào)

【公式18】=RIGHT(A2,11)

將手機(jī)號(hào)分段顯示

【公式19】=TEXT(B2,"0 0000 0000")

加密顯示手機(jī)號(hào)

【公式20】=REPLACE(B2,4,4,"****")

好了，相信掌握了這20個(gè)公式，一定會(huì)為你的工作錦上添花。

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

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

７個(gè)Excel小技巧，提高表格查看效率

Excel運(yùn)用規(guī)范1：一個(gè)單元格只記錄一條信息

快速整理不規(guī)范的Excel表格的7個(gè)公式

9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏）

版權(quán)申明：

本文作者郅龍；同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。