計(jì)算機(jī)Excel函數(shù)整編

4.8.3常用函數(shù)

Excel一共提供了數(shù)百個(gè)內(nèi)部函數(shù)，限于篇幅，此處僅對(duì)一些最常用的函數(shù)作一簡(jiǎn)單介

紹。如有需要，可行閱Excel的聯(lián)機(jī)幫助或其它參考資料，以了解更多函數(shù)和更詳細(xì)的說明。

1、數(shù)學(xué)函數(shù)

(1)絕對(duì)值函數(shù)ABS

格式:ABS(number)

功能：返回參數(shù)number的絕對(duì)值。

例如：ABS(-7)的返回值為7：ABS(7)的返回值為7。

⑵取整函數(shù)INT

格式：INT(number)

功能：取一個(gè)不大于參數(shù)number的最大整數(shù)。

例如：INT(8.9),INT(-8.9)其結(jié)果分別是8,-9。

(3)圓周率函數(shù)PI

格式：PK)

功能：返回圓周率兀的值，

說明：此函數(shù)無需參數(shù)，但函數(shù)名后的括號(hào)不能少。

(4)四舍五入函數(shù)ROUND

格式:ROUND(number,n)

功能：根據(jù)指定位數(shù)，將數(shù)字四舍五入。

說明：其中n為整數(shù)，函數(shù)按指定n位數(shù)，將number進(jìn)行四舍五入。當(dāng)n>0,數(shù)字將被四

舍五人到所指定的小數(shù)位數(shù)；當(dāng)n=(),數(shù)字將被四舍五入成整數(shù)；當(dāng)n<0,數(shù)字將被

四舍五入到小數(shù)點(diǎn)左邊的指定位數(shù)。

例如：Round(21.45,1),Round(21.45,0),Round(21.45,7)其結(jié)果分別是21.5,21,20。

(5)求余函數(shù)MOD

格式：MOD(number,divisor)

功能：返回兩數(shù)相除的余數(shù)。結(jié)果的正負(fù)號(hào)與除數(shù)相同。

說明：Number為被除數(shù)，Divisor為除數(shù)。

例如：例如3,2)等于1,M0D(-3,2)等于1,M0D(3,-2)等于-1,MOD(-3,-2)等于T。

(6)隨機(jī)函數(shù)RAND

格式:RANDO

功能：返回一個(gè)位于［0,1)區(qū)間內(nèi)的隨機(jī)數(shù)。

說明：此函數(shù)無需參數(shù)，但函數(shù)名后的括號(hào)不能少。

產(chǎn)生［a,b］區(qū)間內(nèi)的隨機(jī)整數(shù)公式:int(rand()*(b-a+l))+a

(7)平方根函數(shù)SQRT

格式：SQRT(number)

功能：返回給定正數(shù)的平方根。

例如：SQRT(9)等于3。

(8)求和函數(shù)SUM

格式：SUM(number1,number2,,?,)

功能：返回參數(shù)表中所有參數(shù)之和。

說明：numberl,numbcr2....是1-30個(gè)需要求和的參數(shù)。若在參數(shù)中直接輸入數(shù)值、邏輯

值或文本型數(shù)字，則邏輯真和假值將轉(zhuǎn)換為數(shù)值1和0,文本型數(shù)字將轉(zhuǎn)換成對(duì)應(yīng)的

數(shù)值型數(shù)字參加運(yùn)算。若引用的單元格中出現(xiàn)空白單元格、邏輯值、文本型數(shù)字，則

該參數(shù)將被忽略。

(9)條件求和函數(shù)SUMIF

格式：SUMIF(range,criteria,sumrange)

功能：根據(jù)指定條件對(duì)若干單元格求和。

說明：

range：用于條件判斷的單元格區(qū)域。

criteria：進(jìn)行累加的單元格應(yīng)滿足的條件，其形式可以為數(shù)字，表達(dá)式或文本。如：條件

可以表示為5、〃6"、為60"、"教授〃。

$um_range:求和的實(shí)際單元格。如果省略sum_range,則直接對(duì)range中的單元格求和。

例如：A1:A4單元區(qū)域中分別存放四個(gè)職工的月收入,分別為:2000,2500,3000和5000；

B1:B4單元區(qū)域中的內(nèi)容為:教授、講師、教授、副教授。則SUMIF(B1:B4,"教授",A1:A4)

的值為5000,表示求B1:B4單元格中職稱為教授人員對(duì)應(yīng)于A1:A4單元格中月收入的

和。

2、統(tǒng)計(jì)函數(shù)

(1)求平均值函數(shù)AVERAGE

格式：AVERAGE(number1,number2,???)

功能：求參數(shù)的平均值。

說明：最多可有30個(gè)參數(shù)，參數(shù)可以是數(shù)值、區(qū)域或區(qū)域名。若引用參數(shù)中包含文字、邏

輯值或空單元格，則將忽略這些參數(shù)。

例如：al:a5區(qū)域中的數(shù)值分別為1,2,3,4,5,則average(al:a5)為3。

(2)COUNT函數(shù)

格式:COUNT(valuel,value2,??,)

功能：計(jì)算所列參數(shù)(最多30個(gè))中數(shù)值型數(shù)據(jù)的個(gè)數(shù)。

說明：函數(shù)計(jì)數(shù)時(shí)，會(huì)把直接作為參數(shù)輸入的數(shù)字、文在型數(shù)字、空值、邏輯值、FI期計(jì)算

進(jìn)去；但對(duì)于錯(cuò)誤值或無法轉(zhuǎn)化成數(shù)據(jù)的內(nèi)容則被忽略,如果參數(shù)是數(shù)組或引用，那

么只統(tǒng)計(jì)數(shù)組或引用中的數(shù)字，數(shù)組或引用中的空白單元格、邏輯值、文本型數(shù)字也

將被忽略。這里的“空值”是指函數(shù)的參數(shù)中有一個(gè)“空參數(shù)”，和工作表單元格的

“空白單元格”是不同的。

例如：COUNTS.1,FALSE,〃5","three”,4,6.66,70,,8,#div/0!)中就有一個(gè)空值，計(jì)數(shù)時(shí)也

計(jì)算在內(nèi)，該函數(shù)的值為8；而COUNT(A1:D4)是計(jì)算區(qū)域A1:D4中非空白的數(shù)字單元

格的個(gè)數(shù)，注意，空白單元格不計(jì)算在內(nèi)。

(3)COUNTA函數(shù)

2

格式：COUNTA(valuel,value2,???)

功能：計(jì)算所列參數(shù)(最多30個(gè))中數(shù)據(jù)項(xiàng)的個(gè)數(shù)。

說明：這里，“數(shù)據(jù)”是一個(gè)廣義的概念，可以包含任何類型的數(shù)據(jù)。但如果參數(shù)是單元格

引用，則引用中的空白單元格將被忽略。

例如：COUNTA(5,TRUE,,〃"，"ABC")的計(jì)算結(jié)果為5。

(4)條件計(jì)數(shù)函數(shù)COUNTIF

格式:COUNTIF(range,criteria)

功能：計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格數(shù)目。

說明：

range：希望計(jì)算的滿足特定條件的非空單元格目的區(qū)域。

criteria：需計(jì)數(shù)單元格應(yīng)滿足的條件，其形式可以為數(shù)字、表達(dá)式或文本。

例如：設(shè)A1:A4中的內(nèi)容分別是“red”,“green”，“red”和“black”,則COUNTIF(Al:A4,

"red")為2；若Bl:B4中的內(nèi)容分別為25,35,40和60,則COUNTIF(B1:B4,”>=40〃)為2。

例在工資表中統(tǒng)計(jì)職稱是副教授的人數(shù)和40歲以上職工人數(shù)。

=countif(c3:c22,“副教授”)

=countif(e3:e22,,,>=40^

(5)最大值函數(shù)MAX

格式：MAX(number1,number2,???)

功能：求參數(shù)表(最多3c個(gè))中的最大值。

說明：參數(shù)可以是數(shù)值、空白單元格、邏輯值或數(shù)字的文本表達(dá)式等。錯(cuò)誤值或不能轉(zhuǎn)化為

數(shù)值的文字作為參數(shù)時(shí)，會(huì)引起錯(cuò)誤。若參數(shù)中不含數(shù)字，則返回0。

例如：MAX(78,"98”,TRUE,,66)的計(jì)算結(jié)果為98。

(6)最小值函數(shù)MIN

格式：MIN(number1,number2,,?,)

功能：求參數(shù)表(最多3c個(gè))中的最小值。

說明：參數(shù)說明與MAX相同。

3、文本函數(shù)

(1)LOWER函數(shù)

格式：LOWER(text)

功能：將一個(gè)字符串中的所有大寫字母轉(zhuǎn)換為小寫字母。

說明：text是要轉(zhuǎn)換為小寫形式的字符串。函數(shù)LOWER不改變字符串中的非字母的字符。

例如：LOWER("Apt.2B")等于"apt.2b”。

(2)UPPER函數(shù)

格式：UPPER(text)

功能：將一個(gè)字符串中的所有小寫字母轉(zhuǎn)換為大寫字母。

說明：text是要轉(zhuǎn)換為大寫形式的字符串。函數(shù)UPPER不改變字符串中的非字母的字符。

例如:UPPER("total")等于"TOTAL"。

3

(3)LEFT函數(shù)

格式：LEFT(lexl,num_chars)

功能：在字符串text中從左邊第一個(gè)字符開始截取num/hars個(gè)字符。

說明：參數(shù)num_chars為我取的字符串的長(zhǎng)度，必須大于等于零。如果num_chars大于text

的總長(zhǎng)度,則返回text全部?jī)?nèi)容。如果省略num_chars,則視為1。

例如：LEFT(“計(jì)算機(jī)應(yīng)用基礎(chǔ)”,5)為“計(jì)算機(jī)應(yīng)用"，LEFT("abed")為“a"。

(4)RIGHT函數(shù)

格式：RIGHT(text,num_chars)

功能：在字符串text中從右邊第一個(gè)字符開始截取num_chars個(gè)字符。

說明：參數(shù)說明同LEFT函數(shù)。

例如：RIGHTCMerry,Chrismas",8)為"Chrismas"，RIGHT("abed")為“d"。

(5)MID函數(shù)

格式：MID(text,startnum,numchars)

功能：從字符串text的第start_num個(gè)字符開始截取num_chars個(gè)字符。

說明：startjum是截取字符串的起始位置。如果startjum大于字符串的長(zhǎng)度，則函數(shù)mid

返回""(空字符串)；如果start_num小于字符串的長(zhǎng)度,但start_num與num_chars

的和超過字符串長(zhǎng)度，則函數(shù)mid返回從startnum到字符串結(jié)束的所有字符；如果

startnum小于1,則函數(shù)Mid將返回錯(cuò)誤值#VALUE!

例如：MIDCpekinguniversity”,1,6)為"peking”

(6)LEN函數(shù)

格式：LEN(text)

功能：返回字符串text中字符的個(gè)數(shù)。

例如：len("university")為10。

4、日期與時(shí)間函數(shù)

(1)DATE函數(shù)

格式:DATE(year,month,day)

功能：返回指定日期的序列數(shù)，所謂序列數(shù)是從1900年1月1日到所輸入日期之間的總天

數(shù)。

說明：year代表年份，是介于1900到9999之間的一個(gè)數(shù)字。month代表月份，如果輸入的

月份大于12,將從指定年份的一月份開始往上加算。day代表該月份中第幾天，如果

day大于該月份的最大天數(shù)，將從指定月份的第一天開始往上加算。

例如：DATE(2008,5,1)為39569,返回代表2008年5月1日的序列數(shù)。

(2)YEAR函數(shù)

格式：YEAR(serialnumber)

功能：返回于序列數(shù)serial_number相對(duì)應(yīng)的年份數(shù)。

例如：YEAR(39569)為20c8。

(3)MONTH函數(shù)

4

格式：MONTH(serialnumber)

功能：返回序列數(shù)serial_number相對(duì)應(yīng)的月份數(shù)。

例如：MONTH(39569)為5。

(4)DAY函數(shù)

格式：DAY(seria1number)

功能：返回序列數(shù)serial_number相對(duì)應(yīng)的天數(shù)。

例如：DAY(39569)為1。

(5)TODAY函數(shù)

格式：TODAY()

功能：返回計(jì)算機(jī)系統(tǒng)內(nèi)部時(shí)鐘現(xiàn)在日期的序列數(shù)。

例如：TODAY()為39570,表示計(jì)算機(jī)系統(tǒng)當(dāng)前日期是2008年5月2日。

(6)TIME函數(shù)

格式：TIME(hour,minute,second)

功能：返回指定時(shí)間的序列數(shù)。

說明:該序列數(shù)是一個(gè)介于0到于之間的十進(jìn)制小數(shù),對(duì)應(yīng)著自0:00:00(12:00:00AM)

到23:59:59(11:59:59PM)的時(shí)間。其中hour介于0到23,代表小時(shí);minute介

于0到59,代表分鐘；second介于。到59,代表秒。

例如：TIME(12,0,0)為0.5,對(duì)應(yīng)12:00:00PM：TIME(17,58,10)為0.,對(duì)應(yīng)5:58PM。

⑺NOW函數(shù)

格式：NOWO

功能：返回計(jì)算機(jī)系統(tǒng)內(nèi)部時(shí)鐘的現(xiàn)在日期和時(shí)間的序列數(shù)。

說明：該序列數(shù)是?個(gè)大于1的帶小數(shù)的正數(shù)，其中整數(shù)部分代表當(dāng)前日期，小數(shù)部分代表

當(dāng)前時(shí)間。

例如：N0W()為39523.,表示2008年3月16日11:52AM。

5、邏輯函數(shù)

(1)AND函數(shù)

格式：AND(logical1,logical2,…，logical30)

功能：當(dāng)所有參數(shù)的邏輯值為真(TRUE)時(shí)返回TRUE；只要一個(gè)參數(shù)的邏輯值為假(FALSE)

即返回FALSE。

說明：logical】，logical,...為待檢測(cè)的若干個(gè)條件值(最多30個(gè))，各條件值必須是

邏輯值(TRUE或FALSE)、計(jì)算結(jié)果為邏輯值的表達(dá)式、或者是包含邏輯值的單元格

引用。如果引用的參數(shù)包含文字或空單元格，則忽略其值；如果指定的單元格區(qū)域內(nèi)

包含非邏輯值，則返回錯(cuò)誤值#VALUE。

例如：AND(TRUE,7>2)為TRUE；AND(9-5=4,5>8)為FALSE。

(2)OR函數(shù)

格式:OR(logical1,logical2,…，logical30)

功能：在參數(shù)中，任何一個(gè)參數(shù)邏輯值為真，即返回邏輯值TRUE；只有全部參數(shù)為假，才

返回FALSEo

5

說明：與AND函數(shù)相同。

例如：例如RUE,3+5=8)為TRUE；OR(1+1=3,3+2=6)為FALSE。

⑶NOT函數(shù)

格式：NOT(logical)

功能：對(duì)邏輯參數(shù)logical求相反的值。

例如：例如FALSE)為TRUE：NOT(1+4=5)為FALSE。

(4)IF函數(shù)

格式：IF(logical_test,valueiftrue,valueiffalse)

功能：對(duì)條件式logical_test進(jìn)行測(cè)試,如果條件為邏輯值TRUE,則取value_if_true的

值，否則取value」取false的值。

說明：

?函數(shù)IF最多可以嵌套七層，用value_if_false及value_if_true參數(shù)可以構(gòu)造復(fù)雜

的檢測(cè)條件。

?在計(jì)算參數(shù)value_if_true和value_if_false后，函數(shù)IF返回相應(yīng)語句執(zhí)行后的返

回值。

例如：在如圖4-51所示的商品銷售業(yè)績(jī)表中，要求用IF函數(shù)完成對(duì)每位人員的業(yè)績(jī)?cè)u(píng)價(jià)。

評(píng)價(jià)的標(biāo)準(zhǔn)是：仝年銷仕:額”為“優(yōu)秀”，仁仝年綃售額＜為“良好"，＜=仝年銷位額＜

為“一般”，全年銷售額＜為“較差”。其操作步驟如下：

①在J3單元格輸入函數(shù)：

=IF(J3＞=,〃優(yōu)秀〃，IF(J3＞=,〃良好〃，IF(J3＞=,"一般〃，"較差")))

②將J3中的公式復(fù)制到J4:J17。

6、數(shù)據(jù)庫統(tǒng)計(jì)函數(shù)

數(shù)據(jù)庫統(tǒng)計(jì)函數(shù)的格式為：函數(shù)名(database,field,criteria)0其中database是包

含字段的數(shù)據(jù)庫區(qū)域；field指定函數(shù)所要統(tǒng)計(jì)的數(shù)據(jù)列，可以是帶引號(hào)的字段名，如“級(jí)

別”，也可以是是宇段名所在單元格地址，還可以是代表數(shù)據(jù)庫中數(shù)據(jù)列位置的序號(hào)，1表

示第一列，2表示第二列等；criteria為一組包含給定袋件的單元格區(qū)域，即條件區(qū)域。條

件區(qū)域的寫法同高級(jí)篩選，

常用的數(shù)據(jù)庫統(tǒng)計(jì)函數(shù)有：

DAVERAGE(database,field,criteria)：對(duì)數(shù)據(jù)庫中滿足條件記錄的指定字段求平均值。

DSUM(database,field,criteria)：對(duì)數(shù)據(jù)庫中滿足條件記錄的指定字段求和。

DMAX(database,lield,criteria)：對(duì)數(shù)據(jù)庫中滿足條件汜錄的指定字段求最大值。

DMIN(database,field,criteria)：對(duì)數(shù)據(jù)庫中滿足條件記錄的指定字段求最小值。

DCOUNT(database,field,criteria)：計(jì)算指定數(shù)據(jù)庫中符合條件且包含有數(shù)字的單元格數(shù)。

DCOUNl-A(database,field,criteria)：返回?cái)?shù)據(jù)庫中滿足給定條件的非空單元格數(shù)目。

6

例如，在商品銷售業(yè)績(jī)表(見圖4-54)中，計(jì)算男性中級(jí)銷售人員的人數(shù)以及他們業(yè)績(jī)

獎(jiǎng)金的平均值。操作步驟如下：

(1)在空白區(qū)域E19:F20建立條件，方法同“高級(jí)篩選”條件區(qū)域的建立。

(2)在E21、E22單元分別輸入各統(tǒng)計(jì)函數(shù)：

E2I：=DCOUNT(A2:J17,,E19:F20)

E22：=DAVERAGE(A2:J17,8,E19:F20)

統(tǒng)計(jì)結(jié)果如圖4-65所示。

8、其他函數(shù)

(1)頻率分析函數(shù)FREQUENCY

格式：FREQUENCY(range1.rangc2