Ecel 2003電子表格函數(shù)公式大全

電子表格函數(shù)公式使用集錦

怎么讓excel表格公式只能看不能被改：選定不需要保護的的單元格，

單擊右鍵一設置單元格格式一取消鎖定一選定需要保護的的單元格，單擊右

鍵一設置單元格格式一鎖定一工具一保護一保護工作表一輸入密碼一確定。

一、電子表格中由身份證號自動導出年月日的公式

=IF(LEN(E1)=15,“19"&MID(E1,7,2)&"-"&MID(El,9,2)&"-"&MID(El,H

,2),MID(El,7,4)(El,H,2)(El,13,2))轉換出生年月如

1986-05-23

說明：

E：列數(shù)E1890：第E列第1890行。輸入身份證號碼

LEN(text):返回本字符串的個數(shù)。LEN(“123”)=3

LEN(E1)=15表示如果身份證號碼為15個數(shù)字

也:表示相加

MID(字符串，M,N)：從該字符串第M位開始，取N位字符。

MID(Al,3,4)=3456,從“Al”單元格中的第“3”位起截取“4”個

數(shù)

IF(條件表達式，語句1,語句2)：如果條件成立，那么就執(zhí)行語句1,

否則執(zhí)行語句2

LEFT(Al,14)截取Al單元格前14位數(shù)

RIGHT(Al,14)截取Al單元格后14位數(shù)

身份證號碼有兩種，如“352124860213541”或

如果E1是15個，為19加上從第7個開始取2個“86”加上-加上從第9個

開始取2個“02”加上-加上從第11個開始取2個“13”合起來為

“1986-02-13”，否則為從第7個開始取4個“1986”加上-加上從第11

個開始取2個“02”加上-加上從第13個開始取2個“13”合起來為

"1986-02-13"o〃19〃、〃-“為直接寫入的數(shù)。

=IF(LEN(A1)=15,“19"&MID(A1,7,4),MID(Al,7,6))轉換出生年月取如

“198606”

=IF(LEN(Al)=15,CONCATENATEn9z/,MID(Al,7,2),MID(Al,9,2)),

IF(LEN(Al)=18,CONCATENATE(MID(Al,7,4),MID(A1,H,2)),”身份證錯

"))轉換出生年月取如1986.05

=2010-MID(Bl,1,4)-IF((MID(Bl,5,2)-0)>8,1,0)計算年齡

=IF(LEN(Al)=15,YEAR(N0W())-1900-VALUE(MID(Al,7,2)),IF(LEN(Al)

=18,YEAR(NOW())-VALUE(MID(Al,7,4)),"身份證錯〃))計算年齡,月數(shù)全部

不算如24歲2個月和24歲H個月都是24歲

=IF(LEN(Al)=15,IF(MOD(VALUE(RIGHT(Al,3)),2)=0,"女"J男

"),IF(LEN(Al)=18,IF(MOD(VALUE(MID(Al,15,3)),2)=0「女"男")，"身份

證錯〃))轉換性別

二、成績在年級里的排名菜一一RANK()函數(shù)的使用

=RANK(N2,$N$2:$N$1501,0)

N2為所要排名的單元格，$N$2:$N$1501為從N2列到N1501歹U，。表示

為按照降序排列的列表，不為零為按照升序排列的列表

=RANK(C1,$C$1:$C$1O)為10個學生中的第一個的排名

三、利用函數(shù)統(tǒng)計考試成績

=COUNTA(A1:A25)算有數(shù)值的單元格個數(shù)應考人數(shù)

=COUNT(B1:B25)和上面的一樣用處算出考試人數(shù)

=COUNTBLANK(B1:B25)算出缺考人數(shù)

=COUNTIF(B1:B25,"〉=90”)算90分以上人數(shù)=COUNTIF(B1:B25,">=80")-

COUNTIF(B1:B25,">=90")算80到90分人數(shù)

=MAX(C1:C25)算最高分

工HN(C1:C25)算最低分

=AVERAGE(C1:C25)算平均分

=COUNTIF(Cl:C25,/，>=90/，)/COUNT(Cl:C25)90分以上占百分比

=MEDIAN(B1:B25)算中位數(shù)

=MODE(B1:B25)算眾數(shù)

=STDEVP(B1:B25)算標準差

四、文本格式轉換成數(shù)值格式

在原單元格上轉換：在任一空白單元格輸入1—復制1一選定所有需要

改變的單元格一右鍵一選擇性粘貼一選〃乘〃，用0加計算也行

在新單元格上轉換：選定新單元格，"=所要轉換的文本單元格-0”

在新單元格上轉換：復制一選擇性粘貼一數(shù)值一點擊下拉一轉換成數(shù)值

五、也的應用

=B2&C2表示把兩個單元格的數(shù)值合為一個如“45”、“67”合為“4567”

=$F$17&C1表示在一個數(shù)值前加一個數(shù)，在任意單元格輸入123設為絕

對值，再加所要添加的單元格

六、IF的應用(滿足其中一個條件的判斷)

=IF(A2〉89,“優(yōu)+”,IF(A2>79,“優(yōu)”,IF(A2〉69,“良+”,IF(A2〉59,“及格

不及格〃))))

=IF(A2>B2,〃超預算〃,〃預算內(nèi)〃)是對預算執(zhí)行結果的判斷

=IF(A2=100,SUM(B5:B15),n如果A2數(shù)字為100,則計算單元格區(qū)域

B5:B15,否則返回空文本(")

=IF(AND(Al>0,Al<=10),1,IF(AND(Al>10,Al<100),2,IF(AND(Al>=100,

Al〈200),3,")))當A列中的數(shù)值大于0小于等于10時返回1,大于10小

于100返回2,大于等于100小于200返回3

=IF(AND(A1=B1,A1=C1),1,0)如果A1=B1=C1,則在DI顯示1,若不相等

則返回0

=IF(Cl>60,IF(AND(C1〉9O),〃優(yōu)秀〃，〃合格〃)，〃不合格〃)如果單元格C1

的值大于60,則執(zhí)行第二個參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格C1

的值是否大于90(為了讓大家體會一下AND函數(shù)的應用，寫成AND(C1〉9O),

實際上可以僅寫Cl〉90),如果滿足在單元格C2中顯示優(yōu)秀字樣，不滿足顯

示合格字樣，如果C1的值以上條件都不滿足，則執(zhí)行第三個參數(shù)即在單元

格C2中顯示不合格字樣。

=IF(A1=1,"A”,IF(A1=2,"B”,IF(A1=3,"C”,IF(A1=4,"D",IF(A1=5,"E",

IF(Al=6,"F",IF(Al=7,"G",IF(Al=8,""))))))))&IF(Al=9,"Q",IF(Al=

10,勺"，IF(A1=H,"k”,IF(A1=12,"y”,IF(A1=13,"x”,IF(A1=14,IF(A1=

15,"m”,IF(A1=16,〃o〃，〃〃))))))))&……為多層嵌套

七、AND函數(shù)(同時滿足條件判斷)

AND(條件一，條件二.條件三…)，條件一二三…都成立時返回

TURE,否則返回FALSE

=IF(AND(A1〉6O,B1〉6O,C1〉6O),“及格”，〃不及格“)，當AND(Al>60,

Bl>60,Cl>60)為TURE時返回“及格”，為FALSE時返回“不及格”

=IF(Al<60,"不及格”，IF(AND(Al〉60,A"70),〃及格

”,IF(AND(A1>7O,A1<85),"良好”，IF(A1>85,“優(yōu)秀”))))可以和IF函數(shù)合起

來使用

八、CONCATENATE函數(shù)(將幾個文本字符串合并為一個文本字符串)

=CONCATENATE(Al,Bl,Cl,DI)

也可以用&(和號)運算符代替函數(shù)CONCATENATE實現(xiàn)文本項的

合并。

九、名稱和標志

為了更加直觀地標識單元格或單元格區(qū)域，我們可以給它們賦予一個名

稱，從而在公式或函數(shù)中直接引用。例如"B2:B46”區(qū)域存放著學生的物理

成績，求解平均分的公式一般是“=AVERAGE（B2:B46）”。在給B2:B46區(qū)域命

名為“物理分數(shù)”以后，該公式就可以變?yōu)椤?AVERAGE（物理分數(shù)）”，從而

使公式變得更加直觀。

給一個單元格或區(qū)域命名的方法是：選中要命名的單元格或單元格區(qū)

域，鼠標單擊編輯欄頂端的“名稱框”，在其中輸入名稱后回車。也可以選

中要命名的單元格或單元格區(qū)域，單擊“插入一名稱一定義"菜單命令，在

打開的“定義名稱”對話框中輸入名稱后確定即可。如果你要刪除已經(jīng)命名

的區(qū)域，可以按相同方法打開“定義名稱”對話框，選中你要刪除的名稱刪

除即可。

由于Excel工作表多數(shù)帶有“列標志”。例如一張成績統(tǒng)計表的首行通

常帶有“序號”、“姓名”、“數(shù)學”、“物理”等“列標志”（也可以稱為字段），

如果單擊“工具一選項”菜單命令，在打開的對話框中單擊“重新計算”選

項卡，選中“工作簿選項”選項組中的“接受公式標志”選項，公式就可以

直接引用“列標志”To例如“B2:B46”區(qū)域存放著學生的物理成績,而B1

單元格已經(jīng)輸入了“物理”字樣，則求物理平均分的公式可以寫成

/AVERAGE（物理）”。

十、幾個常用函數(shù)

=ABS（A2）,則在A2單元格中無論輸入正數(shù)（如100）還是負數(shù)（如-100）,

B2中均顯示出正數(shù)（如100）

=AND（A5>=60,B5>=60）,確認。如果C5中返回TRUE,說明A5和B5中的

數(shù)值均大于等于60,如果返回FALSE,說明A5和B5中的數(shù)值至少有一個小

于60

=COLUMN（B11）,確認后顯示為“2”（即B列）

十一、與求和有關的函數(shù)

1、=SUM（H3:H12）求H3至H12的和

2、SUBTOTAL（function_num,refl,ref2,,,,）分類匯總

Functionnum為1到H之間的數(shù)字，指定使用何種函數(shù)在數(shù)據(jù)清

單中進行分類匯總計算。

Function_Num函數(shù)

1AVERAGE——求算術平均數(shù)

2COUNT——計算參數(shù)列表中的數(shù)字項的個數(shù)

3COUNTA——計算單元格區(qū)域或數(shù)組中包含數(shù)據(jù)的單元格個數(shù)。

4MAX——求最大值

5MIN---求最小值

6PRODUCT——單元格內(nèi)的乘積

7STDEV——估算樣本的標準偏差，反映相對于平均值的離散程度

8STDEVP——整個樣本總體的標準偏差

9SUM——求和

10VAR——計算基于給定樣本的方差

11VARP——計算基于整個樣本總體的方差

例：“=SUBTOTAL（9,A2:A5）對A2至A5列使用SUM函數(shù)計算出的分類

匯總（303）”，"-SUBTOTAL。,A2:A5）對A2至A5列使用AVERAGE函數(shù)計

算出的分類匯總（75.75）”

3、SUMIF——根據(jù)指定條件對若干單元格求和

例：=SUMIF（$C$3:$C$12,“銷售部”,$F$3:$F$12）,“$C$3:$C$12"指部

門名稱單元格，〃銷售部〃指計算其中的“銷售部”部門，“$F$3:$F元格指

部門名稱相應的數(shù)值單元格。

4、SUMPRODUCT——在給定的幾組數(shù)組中，將數(shù)組間對應的元素相

乘，并返回乘積之和

=SUMPR0DUCT(A15:A16,B15:B16)表示“A15*B15+A16*B16”

=SUMPR0DUCT(B2:C4*D2:E4)表示“B2*D2+C2*E2+...+B4*D4+C4*E4”

=SUMPRODUCT(($B$2:$B$11=$E2)*($C$2:$C$11=F$1))計算符合2

個及以上條件的數(shù)據(jù)個數(shù)(4人的單元格輸入公式)

姓名性別職稱性別中一中二

A男中一男4

B女中二女

C女中一

D男中一

E女中一

F男中二

G女中二

H男中一

I男中一

J女中一

=SUMPRODUCT(($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)

計算男、女分別是中一或中二的總課時數(shù)(在15節(jié)單元格中輸入公式)

姓名性別職稱課時性別中二中一

男

中55

A男一

女

二

中6

B女一

中4

C女

中3

D男一

中8

E女一

中5

F男二

中6

G女二

中4

H男一

中7

I男二

中8

J女

SUMSQ函數(shù)：計算多個數(shù)值的平方和。如SUMSQ(B2,C2)=B2的平方+C2

的平方。

ROUND函數(shù)：如ROUND(B2,2)就是對B2進行四舍五入保留2位小數(shù)。

INT(將數(shù)字向下舍入到最接近的取整函數(shù))

IF和AND嵌套使用：=IF(AND(Al>60,Bl>60,Cl>60)，"及格”，〃不及格

〃)，當Al,Bl,Cl都大于60時返回“及格”

=IF(Al<60,"不及格”，IF(AND(Al〉60,A"70),〃及格

”,IF(AND(A1〉7O,A"85),"良好",IF(A1>85,〃優(yōu)秀")))),當Al<60時返回

“不及格”，當60〈A"70時返回“及格”，當70〈A"85時返回“良好”,當

Al>85時返回“優(yōu)秀”

COUNTIF函數(shù)：計算其中滿足條件的單元格數(shù)目，如

C0UNTIF(B4:B10,"〉90"),計算B4到B10這個范圍各科成績中有多少個數(shù)值

大于90的單元格。如COUNTIF($C$2:$C$13,A17),計算$C$2:$C$13這個范

圍有多少個A17(A17存放的是姓名)

SUMIF($C$2:$C$13,A17,$B$2:$B$13)計算其中(A17)的銷售獎金，

$C$2:$C$13是銷售人員的姓名,A17是其中的一個姓名,$B$2:$B$13是銷售

金額區(qū)域，

IF(C17<50000,10%,15%)*C17如果訂單總額小于50000則獎金為10%；

如果訂單總額大于等于50000,則獎金為15%

十二、字母大小寫轉換

LOWER(Al)將Al文字串中的所有字母轉換為小寫字母。

UPPER(Al)將Al文本轉換成大寫形式。

PROPER(Al)將Al文字串的首字母及任何非字母字符之后的首字母轉換

成大寫。將其余的字母轉換成小寫。

十三、取出字符串中的部分字符

LEFT("Thisisanapple",4)=This從前面取

RIGHT(Z/Thisisanapple",5)=apple從后面取

MID(Z/Thisisanapple”,6,2)=is從中間取

十四、取出當前系統(tǒng)時間/日期信息

NOW()取當前系統(tǒng)“年月日時分”

TODAY。取當前系統(tǒng)“年月日”

YEAR(E5)=2001取單元格的“年”

MONTH(E5)=5取單元格的“月”

DAY(E5)=30取單元格的“日”

HOUR(E5)=12取單元格的“時”

DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)：其中計算年數(shù)為

DATEDIF(A24,T0DAY(),"y"),勺〃時間段中的整年數(shù)，"M"時間段中的整月

數(shù)，〃D〃時間段中的天數(shù)，〃MD〃為日期中天數(shù)的差，忽略日期中的月和年(直

接天數(shù)相減，不夠減要向上月借一)，“YF為日期中月數(shù)的差，忽略日期中

的日和年(直接月數(shù)相減，不夠減要向上月借一)，〃YD〃為日期中天數(shù)的

差。忽略日期中的年(月日合計相減，不夠減要向上月借一)。

VALUE：將代表數(shù)字的文字串轉換成數(shù)字，語法形式