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

1、常用函數(shù)Excel 一共提供了數(shù)百個內部函數(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) 其結果分別是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進行四舍五入。當n0，數(shù)字將被四舍五入到所指定的小數(shù)位數(shù)；當n0，數(shù)字將被四舍五入成整數(shù)；當n0，數(shù)字將被四舍五入到小數(shù)點左邊的指定位數(shù)。例如：Round(21.45,1)，Round(21.45,0),Round(21.45,-1)其結果分別是21.5，21，20。(5) 求余函數(shù)MOD格式：MOD(number,divisor)功能：返回兩數(shù)相除的余數(shù)。結果的正負號與除數(shù)相同。說明：Number為被除數(shù)，Divisor為除數(shù)。例如：M

3、OD(3,2)等于1，MOD(-3,2)等于1，MOD(3,-2)等于-1，MOD(-3,-2)等于-1。(6) 隨機函數(shù)RAND格式：RAND()功能：返回一個位于0,1)區(qū)間內的隨機數(shù)。說明：此函數(shù)無需參數(shù)，但函數(shù)名后的括號不能少。產(chǎn)生a,b區(qū)間內的隨機整數(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個需要求和的參數(shù)。若在參數(shù)中直

4、接輸入數(shù)值、邏輯值或文本型數(shù)字，則邏輯真和假值將轉換為數(shù)值1和0，文本型數(shù)字將轉換成對應的數(shù)值型數(shù)字參加運算。若引用的單元格中出現(xiàn)空白單元格、邏輯值、文本型數(shù)字，則該參數(shù)將被忽略。(9) 條件求和函數(shù)SUMIF格式：SUMIF(range，criteria，sum_range)功能：根據(jù)指定條件對若干單元格求和。說明：range：用于條件判斷的單元格區(qū)域。criteria：進行累加的單元格應滿足的條件，其形式可以為數(shù)字，表達式或文本。如：條件可以表示為5、6、=40)為2。例 在工資表中統(tǒng)計職稱是副教授的人數(shù)和40歲以上職工人數(shù)。=countif(c3:c22,”副教授”)=countif(e

5、3:e22,”=40”)(5) 最大值函數(shù)MAX格式：MAX(number1，number2，)功能：求參數(shù)表（最多30個）中的最大值。說明：參數(shù)可以是數(shù)值、空白單元格、邏輯值或數(shù)字的文本表達式等。錯誤值或不能轉化為數(shù)值的文字作為參數(shù)時，會引起錯誤。若參數(shù)中不含數(shù)字，則返回0。例如：MAX(78, 98,TRUE,66)的計算結果為98。(6) 最小值函數(shù)MIN格式：MIN(number1，number2，)功能：求參數(shù)表（最多30個）中的最小值。說明：參數(shù)說明與MAX相同。3、文本函數(shù)(1) LOWER函數(shù)格式：LOWER(text)功能：將一個字符串中的所有大寫字母轉換為小寫字母。說明：t

6、ext是要轉換為小寫形式的字符串。函數(shù)LOWER不改變字符串中的非字母的字符。例如：LOWER(Apt. 2B) 等于 apt. 2b。(2) UPPER函數(shù)格式： UPPER(text)功能：將一個字符串中的所有小寫字母轉換為大寫字母。說明：text是要轉換為大寫形式的字符串。函數(shù)UPPER不改變字符串中的非字母的字符。例如：UPPER(total) 等于 TOTAL。(3) LEFT函數(shù)格式：LEFT(text,num_chars)功能：在字符串text中從左邊第一個字符開始截取num_chars個字符。說明：參數(shù)num_chars為截取的字符串的長度，必須大于等于零。如果num_char

7、s大于text的總長度，則返回text全部內容。如果省略num_chars，則視為1。例如：LEFT(計算機應用基礎,5)為 “計算機應用”，LEFT(abcd)為 “a”。(4) RIGHT函數(shù)格式：RIGHT(text,num_chars)功能：在字符串text中從右邊第一個字符開始截取num_chars個字符。說明：參數(shù)說明同LEFT函數(shù)。例如：RIGHT(Merry，Chrismas，8)為 “Chrismas”，RIGHT(abcd)為 “d”。(5) MID函數(shù)格式：MID(text,start_num,num_chars)功能：從字符串text的第start_num個字符開始截取

8、num_chars個字符。說明：start_num是截取字符串的起始位置。如果start_num大于字符串的長度，則函數(shù)mid返回“”（空字符串）；如果start_num小于字符串的長度，但start_num與num_chars的和超過字符串長度，則函數(shù)mid返回從start_num到字符串結束的所有字符；如果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)

9、 DATE函數(shù)格式：DATE(year,month,day)功能：返回指定日期的序列數(shù)，所謂序列數(shù)是從1900年1月1日到所輸入日期之間的總天數(shù)。說明：year代表年份，是介于1900到9999之間的一個數(shù)字。month代表月份，如果輸入的月份大于12，將從指定年份的一月份開始往上加算。day代表該月份中第幾天，如果day大于該月份的最大天數(shù)，將從指定月份的第一天開始往上加算。例如：DATE(2008,5,1)為39569，返回代表2008年5月1日的序列數(shù)。(2) YEAR函數(shù)格式：YEAR(serial_number)功能：返回于序列數(shù)serial_number相對應的年份數(shù)。例如：YEA

10、R(39569)為2008。(3) MONTH函數(shù)格式：MONTH(serial_number)功能：返回序列數(shù)serial_number相對應的月份數(shù)。例如：MONTH(39569)為5。(4) DAY函數(shù)格式：DAY(serial_number)功能：返回序列數(shù)serial_number相對應的天數(shù)。例如：DAY(39569)為1。(5) TODAY函數(shù)格式：TODAY()功能：返回計算機系統(tǒng)內部時鐘現(xiàn)在日期的序列數(shù)。例如：TODAY()為39570，表示計算機系統(tǒng)當前日期是2008年5月2日。(6) TIME函數(shù)格式：TIME（hour，minute，second）功能：返回指定時間的序

11、列數(shù)。說明：該序列數(shù)是一個介于0到0.之間的十進制小數(shù)，對應著自0:00:00（12:00:00 AM）到23:59:59（11:59:59 PM）的時間。其中hour介于0到23，代表小時；minute介于0到59，代表分鐘；second介于0到59，代表秒。例如：TIME(12,0,0)為0.5，對應12:00:00 PM；TIME(17,58,10)為0.，對應5:58PM。(7) NOW函數(shù)格式：NOW()功能：返回計算機系統(tǒng)內部時鐘的現(xiàn)在日期和時間的序列數(shù)。說明：該序列數(shù)是一個大于1的帶小數(shù)的正數(shù)，其中整數(shù)部分代表當前日期，小數(shù)部分代表當前時間。例如：NOW()為39523.，表示2

12、008年3月16日11:52AM。5、邏輯函數(shù)(1) AND函數(shù)格式：AND(logical1，logical2，logical30)功能：當所有參數(shù)的邏輯值為真（TRUE）時返回TRUE；只要一個參數(shù)的邏輯值為假（FALSE）即返回FALSE。說明：logical1, logical2, .為待檢測的若干個條件值（最多30個），各條件值必須是邏輯值（TRUE或FALSE）、計算結果為邏輯值的表達式、或者是包含邏輯值的單元格引用。如果引用的參數(shù)包含文字或空單元格，則忽略其值；如果指定的單元格區(qū)域內包含非邏輯值，則返回錯誤值#VALUE。例如：AND(TRUE,72) 為TRUE；AND(9-5

13、=4,58)為FALSE。(2) OR函數(shù)格式：OR(logical1,logical2, ，logical30)功能：在參數(shù)中，任何一個參數(shù)邏輯值為真，即返回邏輯值TRUE；只有全部參數(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)

14、功能：對條件式logical_test進行測試，如果條件為邏輯值TRUE，則取value_if_true的值，否則取value_if_false的值。說明：l 函數(shù)IF最多可以嵌套七層，用value_if_false及value_if_true參數(shù)可以構造復雜的檢測條件。l 在計算參數(shù)value_if_true和value_if_false后，函數(shù)IF返回相應語句執(zhí)行后的返回值。圖 4-51 用IF函數(shù)進行等級分類例如：在如圖4-51所示的商品銷售業(yè)績表中，要求用IF函數(shù)完成對每位人員的業(yè)績評價。評價的標準是：全年銷售額=為“優(yōu)秀”，=全年銷售額為“良好”，=全年銷售額為“一般”，全年銷售額=

15、,優(yōu)秀，IF(J3=,良好,IF(J3=, 一般,較差)） 將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表示第二列等；criteria為一組包含給定條件的單元格區(qū)域，即條件區(qū)域。條件區(qū)域的寫法同高級篩選。 常用的數(shù)據(jù)庫統(tǒng)計函數(shù)有：DAVERAGE(database,field,criteria)：對數(shù)據(jù)庫中滿

16、足條件記錄的指定字段求平均值。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）中，計算男

17、性中級銷售人員的人數(shù)以及他們業(yè)績獎金的平均值。操作步驟如下：(1) 在空白區(qū)域E19:F20建立條件，方法同“高級篩選”條件區(qū)域的建立。(2) 在E21、E22單元分別輸入各統(tǒng)計函數(shù)：E21：DCOUNT(A2:J17，E19:F20)E22：DAVERAGE(A2:J17,8, E19:F20)統(tǒng)計結果如圖4-65所示。圖4-65 數(shù)據(jù)庫統(tǒng)計函數(shù)結果8、其他函數(shù)(1) 頻率分析函數(shù)FREQUENCY格式：FREQUENCY(range1,range2)功能：將區(qū)域range1中的數(shù)據(jù)按垂直區(qū)域range2(分段點)進行頻率分布的統(tǒng)計，統(tǒng)計結果放在range2右邊列的對應位置。說明：輸入公式前要選定顯示結果的區(qū)域，返回數(shù)組中的元素個數(shù)比range2中的元素數(shù)目多一個，輸入公式完畢要按“Ctrl+Shift+Enter”組合鍵，不能按Enter鍵。例如：若工作表中已輸入某班學生成績，如圖4-52所示。統(tǒng)計成績在059、6069、7079、8089和90100各區(qū)間中的人數(shù)。圖4-53使用頻度函數(shù)圖4-52 學生成績操作步驟如下： 在區(qū)域C2:C5中輸入分段點的分數(shù)59、69、79、89。 選定顯示結果的區(qū)域D2:D6。 用鼠標單擊編輯欄，在編輯欄中輸入公式“=FREQUENCY(A1:A10,C2:C5)”。