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

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

2、值 2,）2. 條件求和=SUMIF（查找的范圍 ,條件（即對象 ）,要求和的范圍）例：（1） =SUMIF（A1:A4, ”=200 ”,B1:B4）函數(shù)意思：對第 A1 欄至 A4 欄中，大于等于 200 的數(shù)值對應(yīng)的第 B1 列 至 B4 列中數(shù)值求和（2） =SUMIF（A1:A4, ”300 ”,C1:C4） 函數(shù)意思：對第 A1 欄至 A4 欄中，小于 300 的數(shù)值對應(yīng)的第 C1 欄至 C4 欄中數(shù)值求和3. 求個數(shù)=COUNT （數(shù)值 1,數(shù)值 2, ）例：（1） =COUNT（A1:A4） 函數(shù)意思：第 A1 欄至 A4 欄求個數(shù)（2） =COUNT（A1:C4） 函數(shù)意思：

3、第 A1 欄至 C4 欄求個數(shù)4. 條件求個數(shù)=COUNTIF （范圍 ,條件）例：（1） =COUNTIF（A1:A4 ，” 200 ”）函數(shù)意思：第 A1 欄至 A4 欄中不等于 200 的欄求個數(shù)（2 ） =COUNTIF（A1:C4, ”=1000 ”） 函數(shù)意思：第 A1 欄至 C4 欄中大于等 1000 的欄求個數(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- 降

4、序例：（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. 取絕對值=ABS（ 數(shù)字 ）10. 取整=INT（ 數(shù)字 ）（二）邏輯函數(shù)條件函數(shù)： =IF （條件 ,執(zhí)行條件 ,執(zhí)行條件假）可執(zhí)行七層嵌套 例： A1=5 A2=3 A3=6 A4=7=IF（A1A2,1,0）=1 （真）=IF（A1A2,IF（A3A4,8,9）,1）=9（三）文本函數(shù)1. 截

5、取函數(shù)： =LEFT（文本,截取長度 ）從左邊截取 =RIGHT（ 文本 ,截取長度 ）從右邊截取 =MID（ 文本,開始位,截取長度 ）從中間截取2. 計算字符長度： LEN（ 文本）文本的長度3. 合并字符函數(shù)： CONCATENATE（ 文本 1,文本 2,）4. 在字符串中查找特定字符： FIND（ 文本,范圍 ,數(shù)值）數(shù)值表示查找第幾個 =FIND（ “a”,“abcadeafga ”,2）=4=FIND（ “a”,“abcadeafga ”,3）=7=FIND（ “a”,“abcadeafga ”,4）=105. 比較兩個字符是否完全相符： EXACT（ 文本 1,文本 2）=EX

6、ACT（ “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ù)值文本 ）（四）判斷公式3 1. 把公式產(chǎn)生的錯誤顯示為空公式： C2=IFERROR(A2/B2, ”)說明：如果是錯誤值則顯示為空，否則正常顯示2. IF 多條件判斷返回值公式： C2=IF(AND(A2500,B2= ”未到期” ),”補款” ,”) 說明：兩個條件同時成立用 AND ，任一個成立用 OR 函數(shù)。(五) 統(tǒng)計公式1. 統(tǒng)計兩個表格重

7、復(fù)的內(nèi)容公式： B2=COUNTIF(Sheet15!A:A,A2)說明：如果返回值大于 0 說明在另一個表中存在， 0 則不存在2. 統(tǒng)計不重復(fù)的總?cè)藬?shù)公式： C2=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8)說明：用 COUNTIF 統(tǒng)計出每人的出現(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)說明：如果標題行沒有規(guī)則用第 2 個公式2. 單條件求和3. 單條件模糊求和4. 多條件模糊求和5. 多表相同位置求和公式： B2=SUM(Sheet1:Sheet19!B2)說明：在表中間刪除或添加表后，公式結(jié)果會自動更新6. 按日期和產(chǎn)品求和(七) 查找與引用公式1. 單條件查找公式公式 1： C11=VLOOKUP(B11,B3:F7,4,FALSE) 說明：查找是 VLOOKUP 最擅長的，基本用法2. 雙向查找公式 公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0)說明：利用 MATCH 函數(shù)查找位置，用 INDEX 函數(shù)取值3