1. 1、IF函數(shù)條件判斷
2. 2、多條件判斷
3. 3、條件求和
4. 4、多條件求和
5. 5、條件計(jì)數(shù)
6. 6、多條件計(jì)數(shù)
7. 7、條件查找
8. 8、多條件查找
9. 9、計(jì)算文本算式
10. 10、合并多個(gè)單元格內(nèi)容
11. 11、合并帶格式的單元格內(nèi)容
12. 12、比較大小寫(xiě)的單詞是否相同
13. 13、提取混合內(nèi)容中的姓名
14. 14、根據(jù)身份證號(hào)碼提取出生年月
15. 15、替換部分電話(huà)號(hào)碼
16. 16、屏蔽函數(shù)公式返回的錯(cuò)誤值
17. 17、四舍五入函數(shù)
18. 18、取整的間隔小時(shí)數(shù)
19. 19、提取日期時(shí)間中的日期值
20. 20、生成隨機(jī)數(shù)

excel函數(shù)公式大全及圖解

1、IF函數(shù)條件判斷
IF函數(shù)是最常用的判斷類(lèi)函數(shù)之一，能完成非此即彼的判斷。
如下圖，考核得分的標(biāo)準(zhǔn)為9分，要判斷B列的考核成績(jī)是否合格。
=IF(B4>=9,”合格”,”不合格”)

IF，相當(dāng)于普通話(huà)的“如果”，常規(guī)用法是：
IF(判斷的條件,符合條件時(shí)的結(jié)果,不符合條件時(shí)的結(jié)果)

2、多條件判斷
如下圖所示，如果部門(mén)為生產(chǎn)、崗位為主操 有高溫補(bǔ)助。在D列使用公式：
=IF(AND(B2=”生產(chǎn)”,C2=”主操”),”有”,”無(wú)”)

AND函數(shù)對(duì)兩個(gè)條件判斷，如果同時(shí)符合，IF函數(shù)返回“有”，否則為無(wú)。

3、條件求和
如下圖所示，使用SUMIF函數(shù)計(jì)算一班的總成績(jī)：
=SUMIF(D2:D5,F2,C2:C5)

SUMIF用法是：
=SUMIF(條件區(qū)域,指定的求和條件,求和的區(qū)域)
用通俗的話(huà)描述就是：
如果D2:D5區(qū)域的班級(jí)等于F2單元格的“一班”，就對(duì)C2:C5單元格對(duì)應(yīng)的區(qū)域求和。

4、多條件求和
如下圖所示，要統(tǒng)計(jì)部門(mén)為生產(chǎn),并且崗位為主操的補(bǔ)助總額。
公式為：
=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)

SUMIFS用法是：
=SUMIFS(求和的區(qū)域，條件區(qū)域1,指定的求和條件1,條件區(qū)域2,指定的求和條件2,……)

5、條件計(jì)數(shù)
如下圖，要統(tǒng)計(jì)指定店鋪的業(yè)務(wù)筆數(shù)。也就是統(tǒng)計(jì)B列中有多少個(gè)指定的店鋪名稱(chēng)。
=COUNTIF(B2:B12,E3)

COUNTIF函數(shù)統(tǒng)計(jì)條件區(qū)域中，符合指定條件的單元格個(gè)數(shù)。常規(guī)用法為：
=COUNTIF(條件區(qū)域,指定條件)

6、多條件計(jì)數(shù)
要求：統(tǒng)計(jì)統(tǒng)計(jì)部門(mén)為生產(chǎn),并且崗位為主操的人數(shù)
公式為：
=COUNTIFS(B2:B9,F2,C2:C9,G2)

COUNTIFS函數(shù)統(tǒng)計(jì)條件區(qū)域中，符合多個(gè)指定條件的單元格個(gè)數(shù)。常規(guī)用法為：
=COUNTIFS(條件區(qū)域1,指定條件1,條件區(qū)域2,指定條件2……)

7、條件查找
VLOOKUP函數(shù)一直是大眾情人般的存在，函數(shù)的語(yǔ)法為：
VLOOKUP(要找誰(shuí),在哪兒找,返回第幾列的內(nèi)容,精確找還是近似找)
如下圖，要查詢(xún)F5單元格中的員工姓名是什么職務(wù)。
=VLOOKUP($F$5,$B$1:$D$10,2,0)

使用該函數(shù)時(shí)，需要注意以下幾點(diǎn)：
1、第4參數(shù)一般用0（或FASLE）以精確匹配方式進(jìn)行查找。
2、第3參數(shù)中的列號(hào)，不能理解為工作表中實(shí)際的列號(hào)，而是指定返回值在查找范圍中的第幾列。
3、如果查找值與數(shù)據(jù)區(qū)域關(guān)鍵字的數(shù)據(jù)類(lèi)型不一致，會(huì)返回錯(cuò)誤值#N/A。
4、查找值必須位于查詢(xún)區(qū)域中的第一列。

8、多條件查找
如下圖所示，要求查詢(xún)部門(mén)為生產(chǎn),并且崗位為部長(zhǎng)的姓名。
公式為：
=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)

LOOKUP函數(shù)多條件查詢(xún)寫(xiě)法為：
=LOOKUP(1,0/((條件區(qū)域1=條件1)*(條件區(qū)域2=條件2)),查詢(xún)區(qū)域)

9、計(jì)算文本算式
如下圖，要計(jì)算單元格中的文本算式，先單擊第一個(gè)要輸入公式的單元格，定義名稱(chēng) ：
計(jì)算 = EVALUATE(C2)

然后在單元格中輸入公式：
=計(jì)算

10、合并多個(gè)單元格內(nèi)容
連接合并多個(gè)單元格中的內(nèi)容，可以使用&符號(hào)完成。如下圖，要合并A列的姓名和B列的電話(huà)號(hào)碼，可以使用公式：
=A2&B$1&B2

11、合并帶格式的單元格內(nèi)容
合并帶有格式的內(nèi)容時(shí)，Excel默認(rèn)按常規(guī)格式進(jìn)行合并，但是如果是日期、時(shí)間或是其他有格式的數(shù)值，結(jié)果就會(huì)讓人大失所望了：

如何才能正確連接出需要的字符串呢？其實(shí)很簡(jiǎn)單，C2公式為：
=A2&TEXT(B2,” y年m月d日”)

首先使用TEXT函數(shù)，把B列的日期變成具有特定樣式的字符串，然后再與A列的姓名連接，就變成了最終需要的樣式。

12、比較大小寫(xiě)的單詞是否相同
如果在A(yíng)1和A2單元格中分別輸入大小寫(xiě)的單詞，使用以下公式判斷時(shí)，Excel會(huì)默認(rèn)二者是相同的：
=A2=B2

如需區(qū)別大小寫(xiě)，可以使用公式：
=EXACT(A2,B2)
EXACT函數(shù) 區(qū)分大小寫(xiě)，但忽略格式上的差異。

13、提取混合內(nèi)容中的姓名
如下圖，要從A列姓名電話(huà)中提取出姓名，除了使用高版本的自動(dòng)填充功能，還可以使用公式完成：
=LEFT(A2,LENB(A2)-LEN(A2))

LENB函數(shù)將每個(gè)漢字（雙字節(jié)字符）的字符數(shù)按2計(jì)數(shù)，LEN函數(shù)則對(duì)所有的字符都按1計(jì)數(shù)。因此“LENB(A2)-LEN(A2)”返回的結(jié)果就是文本字符串中的漢字個(gè)數(shù)。
LEFT函數(shù)從文本字符串的第一個(gè)字符開(kāi)始，返回指定個(gè)數(shù)的字符，最終提取出員工姓名。

14、根據(jù)身份證號(hào)碼提取出生年月
計(jì)算公式為：
=1*TEXT(MID(B2,7,8),”0-00-00″)

首先使用MID函數(shù)從B2單元格的第7位開(kāi)始，提取出表示出生年月的8個(gè)字符，結(jié)果為：
“19780215”
再使用TEXT函數(shù)將字符串轉(zhuǎn)換為日期樣式：
“1978-02-15”
然后通過(guò)*1計(jì)算，將其轉(zhuǎn)換為真正的日期。最后設(shè)置為日期格式即可。

15、替換部分電話(huà)號(hào)碼
如下圖所示，要將手機(jī)號(hào)碼的中間四位換成星號(hào)，公式為：
=SUBSTITUTE(B2,MID(B2,4,4),”****”,1)

SUBSTITUTE函數(shù)的用法是：
SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個(gè)])
先使用MID函數(shù)取得B列號(hào)碼中的中間4位，再用“*****”替換掉這部分內(nèi)容。
最后一個(gè)參數(shù)使用1，表示只替換第一次出現(xiàn)的內(nèi)容。比如第九行的電話(huà)號(hào)碼是13801010101，最后四位和中間四位相同，如果不指定1，就會(huì)全部替換掉了。

16、屏蔽函數(shù)公式返回的錯(cuò)誤值
在使用函數(shù)公式過(guò)程中，經(jīng)常會(huì)返回一些諸如#N/A、#NAME?之類(lèi)的錯(cuò)誤值，要屏蔽這些錯(cuò)誤值其實(shí)很簡(jiǎn)單，只需在原公式外側(cè)加上一個(gè)IFERROR函數(shù)就好。
IFERROR函數(shù)的用法為：
=IFERROR(原公式,出現(xiàn)錯(cuò)誤時(shí)要返回的內(nèi)容)
如果公式正確，就返回原有計(jì)算結(jié)果，如果公式返回的是錯(cuò)誤值，就返回用戶(hù)指定的顯示內(nèi)容。

17、四舍五入函數(shù)
ROUND函數(shù)這個(gè)想必大家經(jīng)常用到吧，就是對(duì)數(shù)值按指定的位數(shù)四舍五入。比如：
=ROUND(8/9,3)
就是將8/9的計(jì)算結(jié)果四舍五入到三位小數(shù)，結(jié)果為0.889。

18、取整的間隔小時(shí)數(shù)
計(jì)算兩個(gè)時(shí)間的間隔小時(shí)數(shù)，不足一小時(shí)部分舍去，計(jì)算加班時(shí)經(jīng)常會(huì)用到，說(shuō)多了都是淚……
=TEXT(B2-B1,”[h]”)

19、提取日期時(shí)間中的日期值
要從日期時(shí)間數(shù)據(jù)中提取出日期，可以使用以下公式：
=INT(A2)
要繼續(xù)提取時(shí)間，只需要做個(gè)減法，就歐了：

20、生成隨機(jī)數(shù)
RANDBETWEEN能夠在指定范圍內(nèi)生成一組隨機(jī)數(shù)據(jù)，對(duì)于廣大質(zhì)檢、監(jiān)理、統(tǒng)計(jì)人員來(lái)說(shuō)，這的確是一個(gè)偉大的函數(shù)。
函數(shù)的用法是：
=RANDBETWEEN(數(shù)字下限,數(shù)字上限)
比如以下公式，就是生成60到100之間的隨機(jī)數(shù)：
=RANDBETWEEN(60,100)