實用Excel技巧分享:10個職場人士最常用的公式

本篇文章給大家整理出了10個職場人士最常用的excel公式,希望能幫大家排憂解難,趕緊來看看吧!

實用Excel技巧分享:10個職場人士最常用的公式

不會使用公式函數(shù)干活真的很沒有效率,但是公式函數(shù)那么多,一下子又學不完,這是很多職場人士面臨的窘境。

今天總結了十個使用頻率很高的公式分享給大家,相信學會這十個公式,你也可以在職場上縱橫一番了。

公式1:條件計數(shù)

條件計數(shù)在Excel的應用中十分常見,例如統(tǒng)計人員名單中的女性人數(shù),就是條件計數(shù)的典型代表。

實用Excel技巧分享:10個職場人士最常用的公式

條件計數(shù)需要用到COUNTif函數(shù),函數(shù)結構為=COUNTIF(統(tǒng)計區(qū)域,條件),在本例第一個公式=COUNTIF(B:B,G2)中,B:B就是統(tǒng)計區(qū)域,G2是條件,公式結果表示B列中為“女”的數(shù)據(jù)有14個。

第二參數(shù)條件可以不使用單元格引用,直接用具體內(nèi)容作為條件,當條件為文本時,需要在條件兩邊添加英文狀態(tài)的雙引號,比如第二個公式=COUNTIF(B:B,”女”)就是如此。?

公式2:快速標注重復數(shù)據(jù)

平時的工作中也經(jīng)常會遇到標注重復值的問題,例如在一份銷售明細表中,對重復的銷售人員姓名進行標注。

實用Excel技巧分享:10個職場人士最常用的公式

首先使用公式=COUNTIF(A:A,A2)計算出每個姓名出現(xiàn)的次數(shù),當結果大于1就表示姓名重復,進而使用IF函數(shù)得到最終的結果。

公式為:=IF(COUNTIF(A:A,A2)=1,””,”重復”),結果如圖所示。

實用Excel技巧分享:10個職場人士最常用的公式

判斷姓名是否重復還有一種情況:第一次出現(xiàn)不算重復,從第二次起才算重復。

遇到這種情況,只需要修改COUNTIF函數(shù)的條件區(qū)域即可,公式為:=IF(COUNTIF($A$1:A2,A2)=1,””,”重復”),結果如圖所示。

實用Excel技巧分享:10個職場人士最常用的公式

公式3:多條件計數(shù)

如果要對多個條件進行統(tǒng)計,就要用到COUNTIFS函數(shù),例如需要統(tǒng)計出男性中學歷為本科的人數(shù)。

實用Excel技巧分享:10個職場人士最常用的公式

COUNTIFS的函數(shù)結構為=COUNTIFS(數(shù)據(jù)區(qū)域1,條件1,數(shù)據(jù)區(qū)域2,條件2,……),最多可以有127組條件。

本例的第一個公式=COUNTIFS(B:B,G2,C:C,G3)中,共有兩組條件,B列是對性別進行判斷,C列是對學歷進行判斷。

同樣,條件可以引用單元格,也可以直接使用具體內(nèi)容,這與COUNTIF函數(shù)完全一致。

公式4:條件求和

除了條件計數(shù),條件求和的應用也很廣泛,例如在銷售明細表中統(tǒng)計出電視銷量的合計。

實用Excel技巧分享:10個職場人士最常用的公式

按條件求和時需要用到SUMIF函數(shù),函數(shù)的結構為=SUMIF(條件區(qū)域,條件,求和區(qū)域),在本例中,條件區(qū)域是B列,求和區(qū)域是C列,條件可以使用單元格也可以直接使用具體內(nèi)容,要統(tǒng)計出電視銷量的合計就有兩種寫法:=SUMIF(B:B,”電視”,C:C)和=SUMIF(B:B,B2,C:C)。

公式5:多條件求和

有條件求和就會有多條件求和,例如要按照銷售人員和商品名稱兩個條件進行求和時,就要用到多條件求和函數(shù)SUMIFS了。

實用Excel技巧分享:10個職場人士最常用的公式

SUMIFS函數(shù)的結構為=SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2,……),在本例中,求和區(qū)域是D列的銷售數(shù)量,第一個條件區(qū)域是B列的銷售人員,第二個條件區(qū)域是C列的商品名稱,因此最終的公式為:

=SUMIFS(D:D,B:B,”沈伊杰”,C:C,”壁掛空調(diào)”)

需要提醒一點的是SUMIFS求和區(qū)域的位置與SUMIF不同,SUMIFS的求和區(qū)域在第一參數(shù),而SUMIF的求和區(qū)域在第三個參數(shù),千萬不要搞混了??!

公式6:根據(jù)身份證號碼計算出生日期

要從身份證號碼中得到出生日期,這種問題對于從事人資行政崗位的小伙伴一定不陌生,公式也比較簡單:

=TEXT(MID(A2,7,8),”0-00-00″)就能得到所需結果,如圖所示:

實用Excel技巧分享:10個職場人士最常用的公式

要明白這個公式的原理,首先要知道身份證號碼中的一些規(guī)則,目前使用的身份證基本都是18位的,從第七位數(shù)字開始的八個數(shù)字就表示出生日期。

這個公式中涉及到兩個函數(shù),首先來看MID函數(shù),MID函數(shù)有三個參數(shù),格式為:=MID(在哪提取,從第幾個字開始取,取幾個字)。

MID(A2,7,8)表示從A2單元格的第七個數(shù)字開始截取八位,效果如圖所示:

實用Excel技巧分享:10個職場人士最常用的公式

出生日期提取出來后卻不是我們需要的效果,這時候就該函數(shù)魔術師TEXT出馬了,TEXT函數(shù)只有兩個參數(shù),格式為=TEXT(要處理的內(nèi)容,“以什么格式顯示”),本例中要處理的內(nèi)容就是MID函數(shù)這部分,顯示格式為”0-00-00″,當然你要用”0年00月00日”這個格式顯示也沒問題,公式改為=TEXT(MID(A2,7,8),”0年00月00日”)就可以了:

實用Excel技巧分享:10個職場人士最常用的公式

公式7:根據(jù)身份證號碼計算年齡

有了出生日期,當然就會想到計算年齡,公式為:=DATEDIF(B2,TODAY(),”Y”)

實用Excel技巧分享:10個職場人士最常用的公式

這里用到了一個Excel的隱藏函數(shù)DATEDIF,函數(shù)需要三個參數(shù),基本結構為=DATEDIF(起始日期,截止日期,計算方式)。

本例中的起始日期就是出生日期,用B2作為第一參數(shù);截止日期是今天,用TODAY()函數(shù)作為第二參數(shù);計算方式為按年計算,用”Y” 作為第三參數(shù)。

如果需要直接從身份證號碼計算年齡的話,公式可以寫為:

=DATEDIF(TEXT(MID(A2,7,8),”0-00-00″),TODAY(),”Y”)

實用Excel技巧分享:10個職場人士最常用的公式

公式8:按照區(qū)間得到不同結果

這類問題多見于績效考核,例如公司對員工進行了績效考核,需要按照考核成績確定獎勵級別,定級規(guī)則為:50分以下為E,50-65(含)為D,65-75(含)為C,75-90(含)為B,90以上為A。

可以使用公式=LOOKUP(E2,{0;50;65;75;90},{“E”;”D”;”C”;”B”;”A”})得到每個員工的獎勵級別,結果如圖所示:

實用Excel技巧分享:10個職場人士最常用的公式

要解釋這個公式的原理就費勁了,可以參考之前的LOOKUP函數(shù)相關教程。

其實要解決這類問題記住套路就夠了:LOOKUP按區(qū)間返回對應結果的套路為=LOOKUP(成績,{下限值列表},{獎勵級別列表}),下限值之間用分號隔開,獎勵級別之間同樣用分號隔開。

也可以將成績下限與獎勵級別的對應關系錄入在表格里,公式可以修改為=LOOKUP(E2,$I$2:$J$6),結果如圖所示。

實用Excel技巧分享:10個職場人士最常用的公式

公式9:單條件匹配數(shù)據(jù)

要想縱橫職場,不會匹配怎么行?要做單條件匹配不會VLOOKUP怎么行?

VLOOKUP函數(shù)的基本結構為=VLOOKUP(找什么,在哪找,第幾列,怎么找),例如按照姓名找最高學歷,可以使用公式=VLOOKUP(G2,B:E,4,0)得到所需結果,如圖所示:

實用Excel技巧分享:10個職場人士最常用的公式

使用這個函數(shù)有兩個要點一定要知道:

①要找的內(nèi)容必須在查找范圍的首列,例如按姓名查找時,查找范圍是從B列開始而不是A列。

②第幾列指的是查找范圍的列而不是表格中的列,例如要找最高學歷,在查找范圍的第4列,而不是表格中的列數(shù)5。

公式10:多條件匹配數(shù)據(jù)

學會多條件匹配數(shù)據(jù)就真的無敵了!

舉一個按姓名和商品名稱兩個條件匹配銷售數(shù)量的例子,如圖所示:

實用Excel技巧分享:10個職場人士最常用的公式

公式為=LOOKUP(1,0/(($A$2:$A$10=E2)*($B$2:$B$10=F2)),$C$2:$C$10)

使用LOOKUP函數(shù)進行多條件匹配的套路為:=LOOKUP(1,0/((查找范圍1=查找值1)*(查找范圍2=查找值2)*……*(查找范圍n=查找值n)),結果范圍),需要注意的是多個查找條件之間是相乘的關系,同時它們需要放在同一組括號中作為0/的分母。

好了,最常用的十類公式就分享到這里,用好了真的可以縱橫職場哦!

相關學習推薦:excel教程

? 版權聲明
THE END
喜歡就支持一下吧
點贊9 分享