【原】Excel基礎(chǔ)款：職場必備的20條Excel函數(shù)公式【附示例和解析】

盛夏職場工具箱 2019-08-22

展開全文

提到Excel，估計職場人都不會陌生，畢竟很大一票人都會在簡歷上寫著“熟練使用Excel”。職場必備技能排行榜上，Excel絕對地位顯赫。不過有多少人只是把Excel當(dāng)做簡單的數(shù)據(jù)錄入工具呢？這里不妄加評論。文章梳理了20個Excel入門級卻很常用、實用的函數(shù)公式，供初入職場或者想提升工作效率的職場人士，做參考。

1、條件判斷：if函數(shù)

比如，判斷業(yè)績所屬等級。

=IF(E3>=150,"A級",IF(E3>=50,"B級",IF(E3>=20,"C級","D級")))

2、單條件求和：SUMIF函數(shù)

比如，求男性員工業(yè)績總和。如上圖。

=SUMIF(D3:D13,"男",E3:E13)，即SUMIF（條件范圍，條件，求和范圍）

3、多條件求和：SUMIFS函數(shù)

比如，求男性員工、業(yè)績大于等于100萬的業(yè)績總和。如上圖。

=SUMIFS(E3:E13,E3:E13,">=100",D3:D13,"男")，即SUMIFS（求和范圍，條件1范圍，條件1，條件2范圍，條件2,……條件N范圍，條件N）

4、單條件計數(shù)：COUNTIF函數(shù)

比如，計算男性員工人數(shù)。如上圖。

=COUNTIF(D3:D13,"男")，即COUNTIF(條件范圍，條件)。

5、多條件計數(shù)：COUNTIFS函數(shù)

比如，計算男性員工、業(yè)績大于等于100萬的人數(shù)。如上圖。

=COUNTIFS(D3:D13,"男",E3:E13,">=100")，即COUNTIFS（條件范圍1，條件1，條件范圍2，條件2……條件范圍N，條件N）。

6、數(shù)據(jù)查詢：Vlookup

比如，查詢杜老大的業(yè)績是多少。

=VLOOKUP(H3,B3:E13,4,0)，即，VLOOKUP（查找的值，查找范圍，找查找范圍中的第幾列，精準(zhǔn)匹配還是模糊匹配）。

7、逆向查詢：LOOKUP函數(shù)

比如，查詢軒轅三光的員工編號。

=LOOKUP(1,0/(B3:B13=H5),A3:A13)，即LOOKUP（查找的值，查找的條件，返回值的范圍）。本示例中使用的位變異用法。查找的值為1，條件為0。

8、數(shù)據(jù)查詢：INDEX+MATCH 函數(shù)

比如，查詢李大嘴的業(yè)績等級。

=INDEX(F3:F13,MATCH(H7,B3:B13,0))，INDEX函數(shù)：返回給定范圍內(nèi)行列交叉處的值。MATCH函數(shù)：給出指定值在指定范圍內(nèi)的所在位置。此方法查出的結(jié)果，同上述數(shù)據(jù)查詢vlookup查出的結(jié)果。

9、從身份證中，提取出生年月：TEXT+MID函數(shù)

=TEXT(MID(C3,7,8),"00-00-00")。即，利用MID函數(shù)從C3單元格中提取從第7個開始，長度為8的字符串；然后，再利用TEXT函數(shù)將字符的格式轉(zhuǎn)換為“00-00-00”的格式，即1986-01-19。

10、用身份證號，計算年齡：DATEDIF函數(shù)

=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")&"周歲"，在上一個提取出生年月日步驟的基礎(chǔ)上，再利用DATEDIF函數(shù)計算出和當(dāng)前日期（TODAY（））的相差年份（y）。

11、合并多個單元格內(nèi)容

比如，合并姓名和手機(jī)號。

=B3&L$2&L3，如果合并后的結(jié)果想去掉手機(jī)號三個字，則公式為=B3&L3

12、合并帶格式的單元格

比如，合并姓名和出生日期。

=B3&TEXT(J3," y年m月d日")，即，使用TEXT函數(shù)，把J列的日期變成具有特定樣式的字符串，然后再與B列的姓名連接，就變成了最終需要的樣式。

13、提取文本數(shù)字混合內(nèi)容的姓名

提取姓名=LEFT(M3,LEN(M3)-11)

提取手機(jī)號：=RIGHT(M3,11)

注：此公式適用于數(shù)字長度固定的文本數(shù)字混合內(nèi)容，例如手機(jī)號、身份證號等。使用Left函數(shù)實現(xiàn)從左向右提取指定位數(shù)的字符串，使用Right函數(shù)實現(xiàn)從右向左提取固定位數(shù)的字符串。

14、替換部分電話號碼：SUBSTITUTE函數(shù)

=SUBSTITUTE(L3,MID(L3,4,4),"****",1),即，SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個])。最后一個參數(shù)使用1，表示只替換第一次出現(xiàn)的內(nèi)容。比如，如果電話號碼是13801010101，最后四位和中間四位相同，如果不指定1，就會全部替換掉了。

15、數(shù)字四舍五入

比如，將8/9計算結(jié)果四舍五入到兩位小數(shù)。

=ROUND(8/9,2)，結(jié)果為0.89，公式結(jié)構(gòu)=ROUND(數(shù)字,小數(shù)位數(shù))。

16、取絕對值

=ABS(A2),即=ABS(數(shù)字)

17、數(shù)字取整

=INT(A2)，=INT(數(shù)字)，如上圖。

18、計算兩個時間的間隔小時數(shù)

比如，計算加班時長，不足一小時部分舍掉。

=TEXT(G3-G2,"[h]")

19、生成隨機(jī)數(shù)：RANDBETWEEN函數(shù)

比如，生成60到100之間的隨機(jī)數(shù)，質(zhì)檢、監(jiān)理、統(tǒng)計人員常用。