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

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

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

一、相關(guān)概念

（一）函數(shù)語法

由函數(shù)名+括號+參數(shù)組成

例：求和函數(shù)：SUM（A1,B2,…)。參數(shù)與參數(shù)之間用逗號“,”隔開

（二）運算符

1.公式運算符：加（+）、減（-）、乘（*）、除（/）、百分號（%）、乘冪（＾）

2.比較運算符：大與（>）、小于（<）、等于（=）、小于等于（<=）、大于等

于（>=）、不等于（<>）

3.引用運算符：區(qū)域運算符（：）、聯(lián)合運算符（,）

（三）單元格的相對引用與絕對引用

例：A1

$A1鎖定第A列

A$1鎖定第1行

$A$1鎖定第A列與第1行

二、常用函數(shù)

（一）數(shù)學(xué)函數(shù)

1.求和=SUM（數(shù)值1,數(shù)值2,……）

2.條件求和=SUMIF（查找的范圍,條件(即對象),要求和的范圍）

例：（1）=SUMIF(A1:A4,”>=200”,B1:B4)

函數(shù)意思：對第A1欄至A4欄中，大于等于200的數(shù)值對應(yīng)的第B1列

至B4列中數(shù)值求和

—1—

（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ù)意思：第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-降序

例：（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ù)字)

—2—

（二）邏輯函數(shù)

條件函數(shù)：=IF（條件,執(zhí)行條件,執(zhí)行條件假）—可執(zhí)行七層嵌套

例：A1=5A2=3A3=6A4=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(文本,截取長度)—從左邊截取

=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)=10

5.比較兩個字符是否完全相符：EXACT(文本1,文本2)

=EXACT(“a”,“a”)=TRUE

=EXACT(“a”,“ab”)=FALSE

=EXACT(“a”,“A”)=FALSE

6.將數(shù)值轉(zhuǎn)化為文本：TEXT(數(shù)值,參數(shù)))—參數(shù)一般為0

7.將數(shù)值型字符轉(zhuǎn)換為數(shù)值：VALUE(數(shù)值文本)

（四）判斷公式

—3—

1.把公式產(chǎn)生的錯誤顯示為空

公式：C2=IFERROR(A2/B2,””)

說明：如果是錯誤值則顯示為空，否則正常顯示。

2.IF多條件判斷返回值

公式：C2=IF(AND(A2<500,B2=”未到期”),”補款”,””)

說明：兩個條件同時成立用AND，任一個成立用OR函數(shù)。

（五）統(tǒng)計公式

1.統(tǒng)計兩個表格重復(fù)的內(nèi)容

公式：B2=COUNTIF(Sheet15!A:A,A2)

說明：如果返回值大于0說明在另一個表中存在，0則不存在。

—4—

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)

說明：如果標(biāo)題行沒有規(guī)則用第2個公式

—5—

2.單條件求和

3.單條件模糊求和

4.多條件模糊求和

5.多表相同位置求和

公式：B2=SUM(Sheet1:Sheet19!B2)

說明：在表中間刪除或添加表后，公式結(jié)果會自動更新。

6.按日期和產(chǎn)品求和

（七）查找與引用公式

1.單條件查找公式

公式1：C11=VLOOKUP(B11,B3:F7,4,FALSE)

說明：查找是VLOOKUP最擅長的，基本用法

—6—

2.雙向查找公式

公式=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

說明：利用MATCH函數(shù)查找位置，用INDEX函數(shù)取值

3.查找最后一條符合條件的記