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

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

2、學(xué)函數(shù)1 .求和=SUM （數(shù)值1,數(shù)值2,）2 . 條件求和 =SUMIF （查找的范圍,條件 （即對(duì)象）,要求和的范圍）例： (1 ) =SUMIF(A1:A4, ” >=200 ”,B1:B4)函數(shù)意思：對(duì)第 A1 欄至 A4 欄中，大于等于200 的數(shù)值對(duì)應(yīng)的第 B1 列至 B4 列中數(shù)值求和2 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 欄求

3、個(gè)數(shù)（2）=COUNT（A1:C4）函數(shù)意思：第A1 欄至 C4 欄求個(gè)數(shù)4. 條件求個(gè)數(shù)=COUNTIF （范圍,條件）例： （1） =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. 排位函

4、數(shù)=RANK （數(shù)值,范圍 ,序別） 1-升序 0- 降序例： （1 ）=RANK（A1,A1:A4,1）函數(shù)意思：第A1 欄在A1欄至A4 欄中按升序排序，返回排名值。（2）=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=5A2=3A3=6A4=7=IF（A1>A2,1,0）=1 （真）=IF（A1<A2,1,0）=0 （假）=

5、IF（A1>A2,IF（A3>A4,8,9）,1）=9（三）文本函數(shù)1 .截取函數(shù)：=LEFT（文本，截取長度）一從左邊截取=RIGHT（ 文本 ,截取長度）從右邊截取=MID（ 文本 ,開始位 ,截取長度）從中間截取2 .計(jì)算字符長度：LEN（文本）一文本的長度3 .合并字符函數(shù)：CONCATENATE（文本1，文本2,）4 . 在字符串中查找特定字符： FIND（ 文本 ,范圍 ,數(shù)值 ）數(shù)值表示查找第幾個(gè)=FIND（ “a”, “abcadeafga ”,2）=4=FIND（ “a”, “abcadeafga ”,3）=7=FIND（ “a”, “abcadeafga ”,4

6、）=105 .比較兩個(gè)字符是否完全相符：EXACT（文本1,文本2）=EXACT（ “a ” , “a ”）=TRUE=EXACT（ “a”, “ab ”）=FALSE=EXACT（ “a ”, “A ”）=FALSE6 .將數(shù)值轉(zhuǎn)化為文本：TEXT（數(shù)值，參數(shù)）一參數(shù)一般為07 .將數(shù)值型字符轉(zhuǎn)換為數(shù)值：VALUE（數(shù)值文本）（四）判斷公式1 .把公式產(chǎn)生的錯(cuò)誤顯示為空公式：C2=IFERROR(A2/B2, ”)說明：如果是錯(cuò)誤值則顯示為空，否則正常顯示E4jt| =imR0R (D4/G4+1004*)ABCDEFGHI 112016年“開門紅”存款完成情況通報(bào)表2數(shù)據(jù)截止日期：201睥

7、3月24日3序號(hào)機(jī)構(gòu)號(hào)機(jī)構(gòu)名稱年V徐穎本期余領(lǐng)完成率排名41583G營業(yè)部2.36731,643a4mo5,20045.52D2 . IF多條件判斷返回值公式：C2=IF(AND(A2<500,B2=" 未到期"),”補(bǔ)款“，"”)說明：兩個(gè)條件同時(shí)成立用 AND,任一個(gè)成立用OR函數(shù)。(五)統(tǒng)計(jì)公式1.統(tǒng)計(jì)兩個(gè)表格重復(fù)的內(nèi)容公式：B2=COUNTIF(Sheet15!A:A,A2)說明：如果返回值大于0說明在另一個(gè)表中存在，0則不存在姓名2JA IOl3 .B14 C1口D16 .G07 1Y0C.S31修 4 + *l ：5ht14/SiBt152.統(tǒng)計(jì)

8、不重復(fù)的總?cè)藬?shù)公式：C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)C2=3UHIFR0DUCT (1/COUNTIF(A2; AZ; &B)A1姓名2A3B4C5D6A7E eD說明：用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è)公式AEC口EFG HT11 口口L目2月3月合計(jì)2實(shí)際計(jì)劃實(shí)際計(jì)劃其際計(jì)劃實(shí)際計(jì)

9、劃3A169374174E976523,75C6101076528 r89101112公式；=SUMIF($A$2:$G$2/I-I$2JA3;G3)2 .單條件求和3 .單條件模糊求和4 .多條件模糊求和5 .多表相同位置求和公式：B2=SUM(Sheet1:Sheet19!B2)6.按日期和產(chǎn)品求和(七)查找與引用公式1.單條件查找公式公式 1: C11=VLOOKUP(B11,B3:F7,4,FALSE)說明：查找是VLOOKUP最擅長的，基本用法說明：在表中間刪除或添加表后，公式結(jié)果會(huì)自動(dòng)更新134567891011片吉工務(wù)性思著貴出生年月張三豐KT001fi法1白70年3月李四光KT

10、002女天津1沏年9月王麻子KT003弱河北1磬5年3月這六兒KTQ04女河南1985年12月AB C D E F姓名篇貫張三豐北京,-根鼠,乞/搏展培川|2 .雙向查找公式公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0) 說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值A(chǔ)BC,DEFGH1姓名3月，L 2月i 3月4.E,5月.6月劉考1054254 ! 557吳號(hào)碼衛(wèi)一21t 215L 3 j485張睛21544205455545.李棟4512L 3522-1吳鳳1245212354754姓名月份銷售量10張睛3月20 -用公式返回該值11李棟4月:123 .查找最后一條符合條件的記錄。公式：詳見下圖說明：0/ （條件）可以把不符合條件的變成錯(cuò)誤值，而lookup可以忽略錯(cuò)誤值4673g1011 囪 困15入庫時(shí)間產(chǎn)品名稱久庫單價(jià)2012-1-2A10201