解鎖人事、財(cái)務(wù)常用的20個(gè)函數(shù)公式,求和、排名、統(tǒng)計(jì)全都很簡單
?
作者:郅龍來源:部落窩教育發(fā)布時(shí)間:2021-07-22 16:31:57點(diǎn)擊:3361
編者按:
各位小伙伴們好!眾所周知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)推薦:
Excel運(yùn)用規(guī)范1:一個(gè)單元格只記錄一條信息
9條最實(shí)用的計(jì)算excel中關(guān)于日期的公式?。ńㄗh收藏)
版權(quán)申明:
本文作者郅龍;同時(shí)部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請(qǐng)聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會(huì)這2個(gè)公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報(bào)表……
- 如何計(jì)算Excel單元格中的算式,四種求和方法請(qǐng)收好!