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

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

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

單擊右鍵一設(shè)置單元格格式一取消鎖定一選定需要保護(hù)的的單元格，單擊右

鍵一設(shè)置單元格格式一鎖定一工具一保護(hù)一保護(hù)工作表一輸入密碼一確定。

一、電子表格中由身份證號(hào)自動(dòng)導(dǎo)出年月日的公式

=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))轉(zhuǎn)換出生年月如

1986-05-23

說明：

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

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

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

也:表示相加

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

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

數(shù)

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

否則執(zhí)行語(yǔ)句2

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

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

身份證號(hào)碼有兩種，如“352124860213541”或

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

開始取2個(gè)“02”加上-加上從第11個(gè)開始取2個(gè)“13”合起來(lái)為

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

個(gè)開始取2個(gè)“02”加上-加上從第13個(gè)開始取2個(gè)“13”合起來(lái)為

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

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

“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)),”身份證錯(cuò)

"))轉(zhuǎn)換出生年月取如1986.05

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

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

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

不算如24歲2個(gè)月和24歲H個(gè)月都是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「女"男")，"身份

證錯(cuò)〃))轉(zhuǎn)換性別

二、成績(jī)?cè)谀昙?jí)里的排名菜一一RANK()函數(shù)的使用

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

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

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

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

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

=COUNTA(A1:A25)算有數(shù)值的單元格個(gè)數(shù)應(yīng)考人數(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)算標(biāo)準(zhǔn)差

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

在原單元格上轉(zhuǎn)換：在任一空白單元格輸入1—復(fù)制1一選定所有需要

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

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

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

五、也的應(yīng)用

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

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

對(duì)值，再加所要添加的單元格

六、IF的應(yīng)用(滿足其中一個(gè)條件的判斷)

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

不及格〃))))

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

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

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

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

Al〈200),3,")))當(dāng)A列中的數(shù)值大于0小于等于10時(shí)返回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í)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格C1

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

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

示合格字樣，如果C1的值以上條件都不滿足，則執(zhí)行第三個(gè)參數(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ù)(同時(shí)滿足條件判斷)

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

TURE,否則返回FALSE

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

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

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

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

來(lái)使用

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

=CONCATENATE(Al,Bl,Cl,DI)

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

合并。

九、名稱和標(biāo)志

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

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

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

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

使公式變得更加直觀。

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

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

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

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

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

除即可。

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

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

如果單擊“工具一選項(xiàng)”菜單命令，在打開的對(duì)話框中單擊“重新計(jì)算”選

項(xiàng)卡，選中“工作簿選項(xiàng)”選項(xiàng)組中的“接受公式標(biāo)志”選項(xiàng)，公式就可以

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

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

/AVERAGE（物理）”。

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

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

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

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

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

于60

=COLUMN（B11）,確認(rèn)后顯示為“2”（即B列）

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

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

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

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

單中進(jìn)行分類匯總計(jì)算。

Function_Num函數(shù)

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

2COUNT——計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù)

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

4MAX——求最大值

5MIN---求最小值

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

7STDEV——估算樣本的標(biāo)準(zhǔn)偏差，反映相對(duì)于平均值的離散程度

8STDEVP——整個(gè)樣本總體的標(biāo)準(zhǔn)偏差

9SUM——求和

10VAR——計(jì)算基于給定樣本的方差

11VARP——計(jì)算基于整個(gè)樣本總體的方差

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

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

算出的分類匯總（75.75）”

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

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

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

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

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

乘，并返回乘積之和

=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))計(jì)算符合2

個(gè)及以上條件的數(shù)據(jù)個(gè)數(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)

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

姓名性別職稱課時(shí)性別中二中一

男

中55

A男一

女

二

中6

B女一

中4

C女

中3

D男一

中8

E女一

中5

F男二

中6

G女二

中4

H男一

中7

I男二

中8

J女

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

的平方。

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

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

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

〃)，當(dāng)Al,Bl,Cl都大于60時(shí)返回“及格”

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

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

“不及格”，當(dāng)60〈A"70時(shí)返回“及格”，當(dāng)70〈A"85時(shí)返回“良好”,當(dāng)

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

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

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

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

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

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

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

金額區(qū)域，

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

如果訂單總額大于等于50000,則獎(jiǎng)金為15%

十二、字母大小寫轉(zhuǎn)換

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

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

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

成大寫。將其余的字母轉(zhuǎn)換成小寫。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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