EXCEL常用函數(shù)公式大全及舉例

1、EXCEL 常用函數(shù)公式大全及舉例一、相關(guān)概念一函數(shù)語(yǔ)法 由函數(shù)名 + 括號(hào)+ 參數(shù)組成例：求和函數(shù)：SUM A1,B2,)。參數(shù)與參數(shù)之 間用逗號(hào)“，隔開二運(yùn)算符1. 公式運(yùn)算符：加+、減-乘*除/百分號(hào)%、乘幕人2. 比擬運(yùn)算符：大與 >、小于 <、等于 =、小于等于 <= 、大于等 于 >= 、不等于 <> 3. 引用運(yùn)算符：區(qū)域運(yùn)算符： 、聯(lián)合運(yùn)算符 ,三單元格的相對(duì)引用與絕對(duì)引用例：A1$A1A$1$A$1鎖定第 A 列鎖定第 1 行鎖定第 A 列與第 1 行二、常用函數(shù)一數(shù)學(xué)函數(shù)1. 求和=SUM 數(shù)值1,數(shù)值2,2. 條件求和=SUMIF查找的

2、范圍 ,條件(即對(duì)象 ),要求和的范圍例：1 =SUMIF(A1:A4, >=200 ,B1:B4)函數(shù)意思：對(duì)第 A1 欄至 A4 欄中，大于等于 200 的數(shù)值對(duì)應(yīng)的第 B1 列 至 B4 列中數(shù)值求和2 =SUMIF(A1:A4, <300 ,C1:C4)函數(shù)意思：對(duì)第 A1 欄至 A4 欄中，小于 300 的數(shù)值對(duì)應(yīng)的第 C1 欄至 C4欄中數(shù)值求和3. 求個(gè)數(shù)二COUNT 數(shù)值1,數(shù)值2,例：1 二COUNT(A1:A4)函數(shù)意思：第A1欄至A4欄求個(gè)數(shù)2 =COUNT(A1:C4)函數(shù)意思：第A1欄至C4欄求個(gè)數(shù)4. 條件求個(gè)數(shù) =COUNTIF 范圍 ,條件例：1 =

3、COUNTIF(A1:A4 ，<>200 )函數(shù)意思：第 A1 欄至 A4 欄中不等于 200 的欄求個(gè)數(shù)2 =COUNTIF(A1:C4, >=1000 )函數(shù)意思：第 A1 欄至 C4 欄中大于等 1000 的欄求個(gè)數(shù)5. 求算術(shù)平均數(shù)二AVERAGE數(shù)值1,數(shù)值2,例：1 =AVERAGE(A1,B2)2 =AVERAGE(A1:A4)6. 四舍五入函數(shù) =ROUND( 數(shù)值,保存的小數(shù)位數(shù) )7. 排位函數(shù)=RANK 數(shù)值,范圍,序別 1-升序 0-降序例：1=RANK(A1,A1:A4,1)函數(shù)意思：第 A1 欄在 A1 欄至 A4 欄中按升序排序，返回排名值 2=

4、RANK(A1,A1:A4,0)函數(shù)意思：第 A1 欄在 A1 欄至 A4 欄中按降序排序，返回排名值8. 乘積函數(shù)二PRODUCT數(shù)值1,數(shù)值2,9. 取絕對(duì)值=ABS( 數(shù)字 )10. 取整=INT( 數(shù)字)二邏輯函數(shù)條件函數(shù)： =IF 條件 ,執(zhí)行條件 ,執(zhí)行條件假可執(zhí)行七層嵌套 例： A1=5 A2=3 A3=6 A4=7=IF(A1>A2,1,0)=1 真=IF(A1<A2,1,0)=0 假=IF(A1>A2,IF(A3>A4,8,9),1)=9 三文本函數(shù)1. 截取函數(shù)：二LEFT(文本，截取長(zhǎng)度)一從左邊截取=RIGHT( 文本 ,截取長(zhǎng)度 )從右邊截取=

5、MID( 文本,開始位,截取長(zhǎng)度 )從中間截取2計(jì)算字符長(zhǎng)度：LEN(文本)一文本的長(zhǎng)度3. 合并字符函數(shù)：CONCATENATE(文本1，文本2,)4. 在字符串中查找特定字符： FIND( 文本,范圍,數(shù)值)數(shù)值表示查找第幾個(gè)=FIND( “a,“abcadeafga ,2)=4=FIND( “a,“abcadeafga ,3)=7=FIND( “a,“abcadeafga ,4)=105比擬兩個(gè)字符是否完全相符：EXACT(文本1,文本2)=EXACT( “a,“a)=TRUE=EXACT( “a,“ab )=FALSE=EXACT( “a,“A)=FALSE6. 將數(shù)值轉(zhuǎn)化為文本：TE

6、XT(數(shù)值，參數(shù))參數(shù)一般為07. 將數(shù)值型字符轉(zhuǎn)換為數(shù)值：VALUE(數(shù)值文本) 四判斷公式1. 把公式產(chǎn)生的錯(cuò)誤顯示為空公式：C2=IFERROR(A2/B2, )說明：如果是錯(cuò)誤值那么顯示為空，否那么正常顯示K4A =IFERROR (D4/C4+100,ACDEFGHI12022年“開門紅存款完成情況通報(bào)表2數(shù)折載止日期：202232403序號(hào)機(jī)構(gòu)號(hào)機(jī)構(gòu)S稱凈増額本明余額元成率排名415839營(yíng)業(yè)卻2.36?31 £4334.010Q 5.20045.52S12I2. IF多條件判斷返回值公式：C2=IF(AND(A2<500,B2二未到期)，補(bǔ)款，)說明：兩個(gè)條件同時(shí)

7、成立用 AND，任一個(gè)成立用OR函數(shù)。五統(tǒng)計(jì)公式1. 統(tǒng)計(jì)兩個(gè)表格重復(fù)的內(nèi)容公式：B2二COUNTIF(Sheet15!A:A,A2)說明：如果返回值大于0說明在另一個(gè)表中存在，0那么不存在2.統(tǒng)計(jì)不重復(fù)的總?cè)藬?shù)公式：C2二SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)說明：用COUNTIF統(tǒng)計(jì)出每人的出現(xiàn)次數(shù)，用1除的方式把出現(xiàn)次數(shù)變成分母，然后相加六求和公式1.隔列求和公式：H3二SUMIF($A$2: $G$2,H$2,A3:G3)或者：二SUMPRODUCT(MOD(COLUMN(B3:G3),2)=0)*B3:G3)說明：如果標(biāo)題行沒有規(guī)那么用第 2個(gè)公式AB-Q

8、-DEHI1存口1月2月3月口計(jì)2廣口口實(shí)廂方案實(shí)際方案實(shí)際方案實(shí)際方案3A16937i17生E9716523一 75C61010765廠26 / 8公式；器=SUMIF($A$2:$G$2/H$2/A3:G3)I斤朮.昌窯二二刊p二-迅h122. 單條件求和3. 單條件模糊求和4. 多條件模糊求和5. 多表相同位置求和公式：B2二SUM(Sheet1:Sheet19!B2)說明：在表中間刪除或添加表后，公式結(jié)果會(huì)自動(dòng)更新AB捲 !D1產(chǎn)品本月豈計(jì)2A19.3604C01LK酊吧廠卩丄作丟5D06* *、輒1 ；-飛尿庁乜¥廣Mh儲(chǔ)亡迪.汕旳龍陽(yáng)匯總6. 按日期和產(chǎn)品求和七查找與引用

9、公式1.單條件查找公式公式 1 : C1仁VLOOKUP(B11,B3:F7,4,FALSE)說明：查找是VLOOKUP最擅長(zhǎng)的，根本用法A BCDEF134567891011工母性別出生年月張三豐KTD01扌匕京19TU年困月李四光KT0C2女19曲年S月主麻子KTD03里涸北1即5年3月KT004河南19馮年堆月& 1#援1名尋握職孝吉訓(xùn)疋三豐亞京電2. 雙向查找公式公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0) 說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值1 ABcDFGH12L月匚3 月.一.4月5月.SITW

10、I1 5陽(yáng)月3'"劉茗'一 1Q5425457-碼22121 !5mi rI l me win ma XiBnaimiiiawi r20 i 54534S張F(tuán)t'21'"“JI25456452352217,吳丿風(fēng)124521；23L4754S9姓名月份銷售量10張睛3月20 -#用公式返回該值1112L3. 查找最后一條符合條件的記錄。公式：詳見以下圖說明：0/條件可以把不符合條件的變成錯(cuò)誤值，而lookup可以忽略錯(cuò)誤值捌h亙找蟲府昴斬單價(jià)4. 多條件查找公式：詳見以下圖說明：公式原理同上一個(gè)公式B恣 一E!逍；_一；蜜_22例2：多嗓件查找1pr入厚時(shí)閭產(chǎn)品名稱XJ¥單松2022-1-2A101 262022-1-3B34272022