EXCEL常用函數(shù)使用方法

基礎(chǔ)-EXCEL中區(qū)域表示D4由左上A2和右下D2所組成的長方格區(qū)域

C2:C4

A2:D4第4行，第D列C2至C4所組成的區(qū)域第一頁第二頁，共21頁。基礎(chǔ)-特殊符號&：excel用于合并文本內(nèi)容!：用于引用其他文件表格單元格A123=A1&"@139.com;";A1張三2stock.xlsSheet1如果要在上面表格引用stock.xls文件中的A1，這時應(yīng)使用"!"='[stock.xls]Sheet1'!A1若文件名為中文或有括號，單引號不能省略張三第二頁2023/10/10第三頁，共21頁?；A(chǔ)-單元格引用ABCD112234356在C1輸入=A1+B1時將顯示什么？若復(fù)制C1單元格到C2將顯示3還是7？復(fù)制到D1呢？357相對引用復(fù)制公式時地址跟著發(fā)生變化絕對引用復(fù)制公式時地址不會跟著發(fā)生變化使用$標(biāo)識符表示絕對引用C1輸入=$A$1+$B$1第三頁第四頁，共21頁。單元格信息提取A1abc黃hjklc23456顯示A1單元格從左開始前5個字符顯示A1單元格從右開始前4個字符顯示A1單元格從第2格字符從左開始前3個字符=LEFT(A1,11)=RIGHT(A1,4)=MID(A1,2,3)=LEFT(A1,5)=LEFT(A1,-1)abc黃hjklcbc黃asd黃hjkc#VALUE!LEFT(字符串,字符個數(shù))RIGHT(字符串,字符個數(shù))MID(字符串,開始位置,字符個數(shù))語法超過總字符數(shù)值錯誤，使用錯誤參數(shù)第四頁2023/10/10第五頁，共21頁。FIND函數(shù)A1abc黃hjklc2345語法FIND(要查找的字符串，被查找的字符串，開始位置）返回值為要查找的字符串的位置FIND函數(shù)查找A1單元格中字符串"bc"的位置=FIND("bc",A1,1)2查找A1單元格中字符"c"的位置=FIND("c",A1,1)39查找A1單元格中字符"c"的位置=FIND("c",A1,4)查找A1單元格中字符"g"的位置=FIND("g",A1,3)#VALUE!值錯誤，查找不到字符"g"，返回值錯誤第五頁2023/10/10第六頁，共21頁。FIND函數(shù)AB1100M24M320M目標(biāo)提取A列中的數(shù)字并將其轉(zhuǎn)換為數(shù)值步驟：查找數(shù)字位置→根據(jù)位置提取→轉(zhuǎn)換為數(shù)值LEFT與FIND組合使用=VALUE(LEFT(A1,FIND("M",A1,1)-1))=VALUE(LEFT(A2,FIND("M",A1,1)-1))=VALUE(LEFT(A3,FIND("M",A1,1)-1))VALUE(字符串)作用：將字符串轉(zhuǎn)換為數(shù)值注意：字符串必須只包含數(shù)字100420第六頁2023/10/10第七頁，共21頁。FIND函數(shù)邏輯判斷ABC11-120030=AND(A1,B1)=OR(A2,B2)TRUEFALSE這兒"0"是文本非數(shù)字=NOT(A3)#VALUE!EXCEL邏輯判斷只有AND、OR、NOT三種EXCEL只將0判定為FALSE邏輯判斷不能使用文本第七頁2023/10/10第八頁，共21頁。條件判斷IF函數(shù)判斷是否滿足條件，如果滿足返回一個值，不滿足返回另外個值IF(測試條件,真值,假值)語法AB110026034=IF(A1>=50,"A1>=50","A1<50")=IF(A2>60,"A2>60",)=IF(A2>60,"A2>60")A1>=500FALSE若沒有設(shè)置參數(shù)值內(nèi)容將返回0若沒有設(shè)置假值參數(shù)將返回FALSE=IF(A1<50,"A",IF(A2>=70,"B",IF(A2>=60,"C","D")))CIF嵌套使用，但嵌套次數(shù)過多建議多單元格分開使用第八頁2023/10/10第九頁，共21頁。條件判斷IFERROR函數(shù)IFERROR(計算值,錯誤值)如果公式計算結(jié)果錯誤，則返回錯誤值，否則返回公式計算的值。結(jié)果錯誤類型#N/A#VALUE!#NUM!#NAME?#REF!#NULL!#DIV/0使用錯誤的參數(shù)或類型不對公式被0除公式中沒有可用值單元格引用無效函數(shù)中的單元格引用不正確（語法）無法識別函數(shù)中的文本函數(shù)沒有返回數(shù)值第九頁2023/10/10第十頁，共21頁。數(shù)據(jù)類型判斷如果判斷特定的錯誤類型，或者更進一步，判斷單元格內(nèi)是否為數(shù)字、文本或空單元格IS函數(shù)集ISNUMBER(值)值為數(shù)字ISTEXT(值)值為文本ISBLANK(值)值為空白單元格ISERROR(值)值為任意錯誤值ISERR(值)值為任意錯誤值（除#N/A)ISNA(值)值為#N/A如果為右列的內(nèi)容，則返回TRUE，否則返回FALSE第十頁2023/10/10第十一頁，共21頁。查找與引用查找也是一種邏輯判斷——判斷單元格內(nèi)容是否與查找的內(nèi)容相符引用——根據(jù)查找的結(jié)果，引用某個單元格信息多條件的判斷(軟件中的switch)IF多條件嵌套使用報表文檔間匯總第十一頁2023/10/10第十二頁，共21頁。查找與引用LOOKUP函數(shù)LOOKUP(查找值,查找向量,返回向量)在查找向量近似查找相應(yīng)的值，返回相應(yīng)的向量ABC1優(yōu)秀15%02優(yōu)良35%15%3良好40%50%4稱職9%90%5欠佳1%99%6100%假設(shè)某人績效排名40%=LOOKUP(D1,C1:C6,A1:A6)D0.40優(yōu)良如果使用IF函數(shù)應(yīng)如何做？=IF(D1>=C5,A1,IF(D1>=C4,A2,IF(D1>=C3,A3,IF(D1>=C2,A4,IF(D1>=C1,A5,"輸入數(shù)據(jù)有誤")))));必須升序第十二頁2023/10/10第十三頁，共21頁。查找與引用VLOOKUP函數(shù)查找某個單元格區(qū)域的第一列，然后返回該區(qū)域相同行上任何單元格中的值。VLOOKUP(查找值,數(shù)據(jù)表,列序數(shù),[匹配條件])語法查找值：要在數(shù)據(jù)表第一列中搜索的值數(shù)據(jù)表：包含數(shù)據(jù)的單元格區(qū)域列序數(shù)：查找成功時，返回匹配值所在行相應(yīng)列號的單元格區(qū)域的值匹配條件TRUE:表示精確或近似匹配，要求數(shù)據(jù)表為升序排列FALSE:表示精確匹配，對數(shù)據(jù)表排列沒有要求第十三頁2023/10/10第十四頁，共21頁。=VLOOKUP(A1,'[stock.xls]Sheet1'!A:C,3,false)說明：在stock.xls中Sheet1的A列中精確搜尋A2(洪瀨)，返回其所在行第3列的值A(chǔ)BC1省新422詩山333侖倉1044洪瀨76stock.xlsSheet1AB1省新2溪美3洪瀨=VLOOKUP(A3,'[stock.xls]Sheet1'!A:C,3,false)26若輸入=VLOOKUP(A3,'[stock.xls]Sheet1'!A:B,3,false)將返回什么？將返回#REF!，因為查找數(shù)據(jù)表只有A列和B列，而公式需要引用C列，產(chǎn)生引用錯誤查找與引用=VLOOKUP(A2,'[stock.xls]Sheet1'!A:C,3,false)#NAME?第十四頁2023/10/10第十五頁，共21頁。匯總函數(shù)求和，求平均，計數(shù)這些都是匯總函數(shù)，主要應(yīng)用于統(tǒng)計評估AB1500M219231004285286447異常8#N/A954=SUM(A2:A5)=AVERAGE(A2:A6)=COUNT(A1:A9)=COUNTA(A1:A9)69統(tǒng)計A1到A9含有數(shù)字的單元格個數(shù)統(tǒng)計A1到A9非空單元格個數(shù)A2到A5單元格區(qū)域求和A2到A5單元格區(qū)域求平均34874.8=SUM(A1:A9)#N/A=COUNTIF(A1:A9,28)統(tǒng)計A1到A9數(shù)字為28的單元格個數(shù)2第十五頁2023/10/10第十六頁，共21頁。分類匯總有時候需要篩選后的數(shù)據(jù)進行計數(shù)求和或平均，但例如使用前面的COUNTA之類的函數(shù)，被篩選的數(shù)據(jù)也被計入，這時就要使用SUBTOTAL函數(shù)SUBTOTAL函數(shù)返回列表或數(shù)據(jù)庫中的分類匯總，只針對篩選后的數(shù)據(jù)計算SUBTOTAL(函數(shù)序號,引用1,...)語法函數(shù)序號決定SUBTOTAL函數(shù)功能，指定使用何種函數(shù)進行分類匯總計算1-AVERAGE2-COUNT3-COUNTA4-MAX5-MIN6-PRODUCT7-STDEV8-STDEVB9-SUM10-VAR11-VARP第十六頁2023/10/10第十七頁，共21頁。AB21923100428528644954篩選后的表格分類匯總=SUBTOTAL(3,A1:A9)=COUNTA(A1:A9)函數(shù)序號3代表使用COUNTA函數(shù)功能96只統(tǒng)計了篩選后的表格AB1500M219231004285286447異常8異常954篩選前的表格函數(shù)序號9代表使用SUM函數(shù)功能=SUBTOTAL(9,A1:A9)446第十七頁2023/10/10第十八頁，共21頁。其他函數(shù)HLOOKUPVLOOKUP的行查找版本MATCH可基于升序或降序近似查找，返回查找值的位置OFFSET通過引用為參考，通過偏移量得到新的引用PRODUCT所有乘積，返回乘積結(jié)果SUMIF基于條件求和T判斷單元格是否為文本，非文本返回空單元格CELLVLOOKUP的行查找版返回某