二級Excel函數(shù)常用函數(shù)

1、常用函數(shù)Excel 一共提供了數(shù)百個內(nèi)部函數(shù)，限于篇幅，此處僅對一些最常用的函數(shù)作一簡單介紹。如有需要，可查閱Excel的聯(lián)機幫助或其它參考資料，以了解更多函數(shù)和更詳細的說明。1、數(shù)學函數(shù)(1) 絕對值函數(shù)ABS格式：ABS(number)功能：返回參數(shù)number的絕對值。 例如：ABS(-7)的返回值為7；ABS(7)的返回值為7。(2) 取整函數(shù)INT格式：INT(number)功能：取一個不大于參數(shù)number的最大整數(shù)。例如：INT(8.9)，INT(-8.9) 其結(jié)果分別是8,-9。(3) 圓周率函數(shù)PI格式：PI()功能：返回圓周率的值。說明：此函數(shù)無需參數(shù)，但函數(shù)名后的括號不能

2、少。(4) 四舍五入函數(shù)ROUND格式：ROUND(number，n)功能：根據(jù)指定位數(shù)，將數(shù)字四舍五入。說明：其中n為整數(shù)，函數(shù)按指定n位數(shù)，將number進行四舍五入。當n>0，數(shù)字將被四舍五入到所指定的小數(shù)位數(shù)；當n0，數(shù)字將被四舍五入成整數(shù)；當n<0，數(shù)字將被四舍五入到小數(shù)點左邊的指定位數(shù)。例如：Round(21.45,1)，Round(21.45,0),Round(21.45,-1)其結(jié)果分別是21.5，21，20。(5) 求余函數(shù)MOD格式：MOD(number,divisor)功能：返回兩數(shù)相除的余數(shù)。結(jié)果的正負號與除數(shù)相同。說明：Number為被除數(shù)，Divisor

3、為除數(shù)。例如：MOD(3,2)等于1，MOD(-3,2)等于1，MOD(3,-2)等于-1，MOD(-3,-2)等于-1。(6) 隨機函數(shù)RAND格式：RAND()功能：返回一個位于0,1)區(qū)間內(nèi)的隨機數(shù)。說明：此函數(shù)無需參數(shù)，但函數(shù)名后的括號不能少。產(chǎn)生a,b區(qū)間內(nèi)的隨機整數(shù)公式：int(rand()*(b-a+1)+a(7) 平方根函數(shù)SQRT格式：SQRT(number)功能：返回給定正數(shù)的平方根。例如：SQRT(9)等于3。(8) 求和函數(shù)SUM格式：SUM(number1，number2，)功能：返回參數(shù)表中所有參數(shù)之和。說明：number1,number2,是1-30個需要求和的參

4、數(shù)。若在參數(shù)中直接輸入數(shù)值、邏輯值或文本型數(shù)字，則邏輯真和假值將轉(zhuǎn)換為數(shù)值1和0，文本型數(shù)字將轉(zhuǎn)換成對應的數(shù)值型數(shù)字參加運算。若引用的單元格中出現(xiàn)空白單元格、邏輯值、文本型數(shù)字，則該參數(shù)將被忽略。(9) 條件求和函數(shù)SUMIF格式：SUMIF(range，criteria，sum_range)功能：根據(jù)指定條件對若干單元格求和。說明：range：用于條件判斷的單元格區(qū)域。criteria：進行累加的單元格應滿足的條件，其形式可以為數(shù)字，表達式或文本。如：條件可以表示為5、"6"、"<60"、"教授"。sum_range:求和的實

5、際單元格。如果省略sum_range，則直接對range中的單元格求和。例如：A1:A4單元區(qū)域中分別存放四個職工的月收入，分別為：2000，2500，3000和5000；B1:B4單元區(qū)域中的內(nèi)容為：教授、講師、教授、副教授。則SUMIF(B1:B4, "教授",A1:A4)的值為5000，表示求B1:B4單元格中職稱為教授人員對應于A1:A4單元格中月收入的和。2、統(tǒng)計函數(shù)(1) 求平均值函數(shù)AVERAGE格式：AVERAGE(number1，number2，)功能：求參數(shù)的平均值。說明：最多可有30個參數(shù)，參數(shù)可以是數(shù)值、區(qū)域或區(qū)域名。若引用參數(shù)中包含文字、邏輯值或空

6、單元格，則將忽略這些參數(shù)。例如：a1:a5區(qū)域中的數(shù)值分別為1，2，3，4，5，則average(a1:a5)為3。(2) COUNT函數(shù)格式：COUNT(value1，value2，)功能：計算所列參數(shù)（最多30個）中數(shù)值型數(shù)據(jù)的個數(shù)。說明：函數(shù)計數(shù)時，會把直接作為參數(shù)輸入的數(shù)字、文本型數(shù)字、空值、邏輯值、日期計算進去；但對于錯誤值或無法轉(zhuǎn)化成數(shù)據(jù)的內(nèi)容則被忽略。如果參數(shù)是數(shù)組或引用，那么只統(tǒng)計數(shù)組或引用中的數(shù)字，數(shù)組或引用中的空白單元格、邏輯值、文本型數(shù)字也將被忽略。這里的“空值”是指函數(shù)的參數(shù)中有一個“空參數(shù)”，和工作表單元格的“空白單元格”是不同的。例如：COUNT(0.1,FALSE

7、, "5","three",4,6.66,70,8,#div/0!)中就有一個空值，計數(shù)時也計算在內(nèi)，該函數(shù)的值為8；而COUNT(A1:D4)是計算區(qū)域A1:D4中非空白的數(shù)字單元格的個數(shù)，注意，空白單元格不計算在內(nèi)。(3) COUNTA函數(shù)格式：COUNTA(value1，value2，)功能：計算所列參數(shù)（最多30個）中數(shù)據(jù)項的個數(shù)。說明：這里，“數(shù)據(jù)”是一個廣義的概念，可以包含任何類型的數(shù)據(jù)。但如果參數(shù)是單元格引用，則引用中的空白單元格將被忽略。例如：COUNTA(5，TRUE，""，"ABC")的計算結(jié)果

8、為5。(4) 條件計數(shù)函數(shù)COUNTIF格式：COUNTIF(range,criteria)功能：計算給定區(qū)域內(nèi)滿足特定條件的單元格數(shù)目。說明：range：希望計算的滿足特定條件的非空單元格目的區(qū)域。criteria：需計數(shù)單元格應滿足的條件，其形式可以為數(shù)字、表達式或文本。例如：設A1:A4中的內(nèi)容分別是“red”，“green”，“red”和“black”，則COUNTIF(A1:A4, "red")為2；若B1:B4中的內(nèi)容分別為25，35，40和60，則COUNTIF(B1:B4, ">=40")為2。例 在工資表中統(tǒng)計職稱是副教授的人數(shù)和

9、40歲以上職工人數(shù)。=countif(c3:c22,”副教授”)=countif(e3:e22,”>=40”)(5) 最大值函數(shù)MAX格式：MAX(number1，number2，)功能：求參數(shù)表（最多30個）中的最大值。說明：參數(shù)可以是數(shù)值、空白單元格、邏輯值或數(shù)字的文本表達式等。錯誤值或不能轉(zhuǎn)化為數(shù)值的文字作為參數(shù)時，會引起錯誤。若參數(shù)中不含數(shù)字，則返回0。例如：MAX(78, "98",TRUE,66)的計算結(jié)果為98。(6) 最小值函數(shù)MIN格式：MIN(number1，number2，)功能：求參數(shù)表（最多30個）中的最小值。說明：參數(shù)說明與MAX相同。3、

10、文本函數(shù)(1) LOWER函數(shù)格式：LOWER(text)功能：將一個字符串中的所有大寫字母轉(zhuǎn)換為小寫字母。說明：text是要轉(zhuǎn)換為小寫形式的字符串。函數(shù)LOWER不改變字符串中的非字母的字符。例如：LOWER("Apt. 2B") 等于 "apt. 2b"。(2) UPPER函數(shù)格式： UPPER(text)功能：將一個字符串中的所有小寫字母轉(zhuǎn)換為大寫字母。說明：text是要轉(zhuǎn)換為大寫形式的字符串。函數(shù)UPPER不改變字符串中的非字母的字符。例如：UPPER("total") 等于 "TOTAL"。(3) LEF

11、T函數(shù)格式：LEFT(text,num_chars)功能：在字符串text中從左邊第一個字符開始截取num_chars個字符。說明：參數(shù)num_chars為截取的字符串的長度，必須大于等于零。如果num_chars大于text的總長度，則返回text全部內(nèi)容。如果省略num_chars，則視為1。例如：LEFT("計算機應用基礎",5)為 “計算機應用”，LEFT("abcd")為 “a”。(4) RIGHT函數(shù)格式：RIGHT(text,num_chars)功能：在字符串text中從右邊第一個字符開始截取num_chars個字符。說明：參數(shù)說明同LEF

12、T函數(shù)。例如：RIGHT("Merry，Chrismas"，8)為 “Chrismas”，RIGHT("abcd")為 “d”。(5) MID函數(shù)格式：MID(text,start_num,num_chars)功能：從字符串text的第start_num個字符開始截取num_chars個字符。說明：start_num是截取字符串的起始位置。如果start_num大于字符串的長度，則函數(shù)mid返回“”（空字符串）；如果start_num小于字符串的長度，但start_num與num_chars的和超過字符串長度，則函數(shù)mid返回從start_num到字符串

13、結(jié)束的所有字符；如果start_num小于1，則函數(shù)Mid將返回錯誤值#VALUE!例如：MID("peking university",1,6)為“peking”(6) LEN函數(shù)格式：LEN(text)功能：返回字符串text中字符的個數(shù)。例如：len("university")為10。4、日期與時間函數(shù)(1) DATE函數(shù)格式：DATE(year,month,day)功能：返回指定日期的序列數(shù)，所謂序列數(shù)是從1900年1月1日到所輸入日期之間的總天數(shù)。說明：year代表年份，是介于1900到9999之間的一個數(shù)字。month代表月份，如果輸入的月份

14、大于12，將從指定年份的一月份開始往上加算。day代表該月份中第幾天，如果day大于該月份的最大天數(shù)，將從指定月份的第一天開始往上加算。例如：DATE(2008,5,1)為39569，返回代表2008年5月1日的序列數(shù)。(2) YEAR函數(shù)格式：YEAR(serial_number)功能：返回于序列數(shù)serial_number相對應的年份數(shù)。例如：YEAR(39569)為2008。(3) MONTH函數(shù)格式：MONTH(serial_number)功能：返回序列數(shù)serial_number相對應的月份數(shù)。例如：MONTH(39569)為5。(4) DAY函數(shù)格式：DAY(serial_numb

15、er)功能：返回序列數(shù)serial_number相對應的天數(shù)。例如：DAY(39569)為1。(5) TODAY函數(shù)格式：TODAY()功能：返回計算機系統(tǒng)內(nèi)部時鐘現(xiàn)在日期的序列數(shù)。例如：TODAY()為39570，表示計算機系統(tǒng)當前日期是2008年5月2日。(6) TIME函數(shù)格式：TIME（hour，minute，second）功能：返回指定時間的序列數(shù)。說明：該序列數(shù)是一個介于0到0.999999999之間的十進制小數(shù)，對應著自0:00:00（12:00:00 AM）到23:59:59（11:59:59 PM）的時間。其中hour介于0到23，代表小時；minute介于0到59，代表分鐘

16、；second介于0到59，代表秒。例如：TIME(12,0,0)為0.5，對應12:00:00 PM；TIME(17,58,10)為0.748726852，對應5:58PM。(7) NOW函數(shù)格式：NOW()功能：返回計算機系統(tǒng)內(nèi)部時鐘的現(xiàn)在日期和時間的序列數(shù)。說明：該序列數(shù)是一個大于1的帶小數(shù)的正數(shù)，其中整數(shù)部分代表當前日期，小數(shù)部分代表當前時間。例如：NOW()為39523.486866667，表示2008年3月16日11:52AM。5、邏輯函數(shù)(1) AND函數(shù)格式：AND(logical1，logical2，logical30)功能：當所有參數(shù)的邏輯值為真（TRUE）時返回TRUE；

17、只要一個參數(shù)的邏輯值為假（FALSE）即返回FALSE。說明：logical1, logical2, .為待檢測的若干個條件值（最多30個），各條件值必須是邏輯值（TRUE或FALSE）、計算結(jié)果為邏輯值的表達式、或者是包含邏輯值的單元格引用。如果引用的參數(shù)包含文字或空單元格，則忽略其值；如果指定的單元格區(qū)域內(nèi)包含非邏輯值，則返回錯誤值#VALUE。例如：AND(TRUE,7>2) 為TRUE；AND(9-5=4,5>8)為FALSE。(2) OR函數(shù)格式：OR(logical1,logical2, ，logical30)功能：在參數(shù)中，任何一個參數(shù)邏輯值為真，即返回邏輯值TRUE

18、；只有全部參數(shù)為假，才返回FALSE。說明：與AND函數(shù)相同。例如：OR(TRUE,3+5=8)為TRUE；OR(1+1=3,3+2=6)為FALSE。(3) NOT函數(shù)格式：NOT(logical)功能：對邏輯參數(shù)logical求相反的值。例如：NOT(FALSE)為TRUE；NOT(1+4=5)為FALSE。(4) IF函數(shù)格式：IF(logical_test,value_if_true,value_if_false)功能：對條件式logical_test進行測試，如果條件為邏輯值TRUE，則取value_if_true的值，否則取value_if_false的值。說明：l 函數(shù)IF最多可

19、以嵌套七層，用value_if_false及value_if_true參數(shù)可以構(gòu)造復雜的檢測條件。l 在計算參數(shù)value_if_true和value_if_false后，函數(shù)IF返回相應語句執(zhí)行后的返回值。圖 4-51 用IF函數(shù)進行等級分類例如：在如圖4-51所示的商品銷售業(yè)績表中，要求用IF函數(shù)完成對每位人員的業(yè)績評價。評價的標準是：全年銷售額>=2000000為“優(yōu)秀”，1500000<=全年銷售額<2000000為“良好”，1000000<=全年銷售額<1500000為“一般”，全年銷售額<1000000為“較差”。其操作步驟如下： 在J3單元格輸

20、入函數(shù)：IF(J3>=2000000,"優(yōu)秀"，IF(J3>=1500000,"良好",IF(J3>=1000000, "一般","較差")） 將J3中的公式復制到J4:J17。6、數(shù)據(jù)庫統(tǒng)計函數(shù)數(shù)據(jù)庫統(tǒng)計函數(shù)的格式為：函數(shù)名(database，field，criteria)。其中database是包含字段的數(shù)據(jù)庫區(qū)域；field指定函數(shù)所要統(tǒng)計的數(shù)據(jù)列，可以是帶引號的字段名，如“級別”，也可以是是字段名所在單元格地址，還可以是代表數(shù)據(jù)庫中數(shù)據(jù)列位置的序號，1表示第一列，2表示第二列等；crite

21、ria為一組包含給定條件的單元格區(qū)域，即條件區(qū)域。條件區(qū)域的寫法同高級篩選。 常用的數(shù)據(jù)庫統(tǒng)計函數(shù)有：DAVERAGE(database,field,criteria)：對數(shù)據(jù)庫中滿足條件記錄的指定字段求平均值。DSUM(database,field,criteria)：對數(shù)據(jù)庫中滿足條件記錄的指定字段求和。DMAX(database,field,criteria)：對數(shù)據(jù)庫中滿足條件記錄的指定字段求最大值。DMIN(database,field,criteria)：對數(shù)據(jù)庫中滿足條件記錄的指定字段求最小值。DCOUNT(database,field,criteria)：計算指定數(shù)據(jù)庫中符合條件且包含有數(shù)字的單元格數(shù)。DCOUNTA(database,field,criteria)：返回數(shù)據(jù)庫中滿足給定條件的非空單元格數(shù)目。例如，在商品銷售業(yè)績表（見圖4-54）中，計算男性中級銷售人員的人數(shù)以及他們業(yè)績獎金的平均值。操作步驟如下：(1) 在空白區(qū)域E19:F20建立條件，方法同“高級篩選”條件區(qū)域的建立。(2) 在E21、E22單元分別輸入各統(tǒng)計函數(shù)：E21：DCOUNT(A2:J17，E19:F20)E22：D