常用函數(shù)使用技巧

1、Excel常用函數(shù)使用技巧目 錄一、EXCEL函數(shù)概述二、邏輯函數(shù)三、日期時間函數(shù)四、文本函數(shù)五、數(shù)學函數(shù)六、統(tǒng)計函數(shù)七、檢視與參照函數(shù)一、EXCEL函數(shù)概述1、函數(shù)結構：=函數(shù)名稱(參數(shù))說明：§函數(shù)以公式形式表達，輸入公式應加引導符號 = ;§參數(shù)：可以是數(shù)字、文本、邏輯值(TRUE 或FALSE)、數(shù)組、錯誤值(#N/A)、單元格引用等;§圓括號 “()”盡量在英文狀態(tài)下輸入。2、數(shù)據(jù)格式：通用型文本型（文字文本和數(shù)字文本）數(shù)值型百分比日期型時間型貨幣型科學計數(shù)3、運算符（1）算術運算符：+ - * / （2）比較運算符： = > < >=

2、 <= <> 比較運算的結果為邏輯值TRUE或FALSE（3）文本運算符：&（連接多個文本字符，產生一串新文本）（4）引用運算符： 引用單元格，有相對引用（A5:B7）、絕對引用（$A$5:$B$7）和混合引用（$A5:B$7）。（5）邏輯運算符：NOT AND OR （6）通配符（特殊）：§星號“*”： 可代替任意數(shù)目的字符（一個或多個）。§問號“?”： 可代替任何單個字符。§僅對字符型數(shù)據(jù)進行操作的(包括：Match()、Vlookup()、Countif()、Sumif()等函數(shù))，對數(shù)值型數(shù)據(jù)無效的 。4、函數(shù)嵌套函數(shù)嵌套是指根據(jù)

3、需要，在一個函數(shù)的使用過程中調用其他函數(shù)。比如：=WEEKDAY(TODAY(),2); =IF(MAX(A3:A8),IF(),“ ”)5、公式運算結果出錯信息（舉例）：（1）#NUM!：數(shù)字不能被運算（2）#NAME!：引用了不能被識別的函數(shù)名或單元格（3）#VALUE!：使用了錯誤的數(shù)據(jù)類型（4）#!：單元格顯示不下內容（寬度不夠） 運算符和數(shù)字、文本等連接，可構成表達式(如：A5>=30;left(A4,1)= “王”)二、邏輯函數(shù)IF主要功能：根據(jù)對指定條件的邏輯判斷的真假結果，返回相對應的內容。使用格式：=IF(Logical,Value_if_true,Value_if_f

4、alse)參數(shù)說明：Logical代表邏輯判斷表達式；Value_if_true表示當判斷條件為邏輯“真（TRUE）”時的顯示內容，如果忽略返回“TRUE”；Value_if_false表示當判斷條件為邏輯“假（FALSE）”時的顯示內容，如果忽略返回“FALSE”。特別提醒：IF函數(shù)嵌套最多：excel07版64層；excel03版7層。OR主要功能：返回邏輯值，僅當所有參數(shù)值均為邏輯“假（FALSE）”時返回函數(shù)結果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。使用格式：=OR(logical1,logical2, .)參數(shù)說明：Logical1,Logical2,Logic

5、al3：表示待測試的條件值或表達式，最多這30個。特別提醒：AND和NOT函數(shù)的用法相似三、日期時間函數(shù)DATE TODAY/NOW DATEDIFYEAR/MONTH/DAY WEEKDAYHOUR/MINUTE/SECONDDATE主要功能：給出指定數(shù)值的日期。 使用格式：=DATE(year,month,day) 參數(shù)說明：year為指定的年份（小于9999）；month為指定的月份（可以大于12）；day為指定的天數(shù)（可以大于30）。TODAY/NOW主要功能：用于顯示系統(tǒng)日期/時間。使用格式：=TODAY()；=NOW()參數(shù)說明：該函數(shù)不需要參數(shù)。特別提醒：顯示出來的日期/時間格式

6、，可以通過單元格格式進行重新設置。DATEDIF主要功能：計算返回兩個日期參數(shù)的差值。使用格式：=DATEDIF(start_date,end_date,unit)參數(shù)說明： start_date表示開始日期，end_date代表結束日期，而且結束日期必須大于開始日期； unit（形式）：（Y 、M、D）要求返回兩個日期相差的年、月、天數(shù)；YM：忽略年和日，計算兩個日期的月份差；YD：忽略年，計算兩個日期中的天數(shù)差；MD：忽略年和月，計算日差。特別提醒：這是Excel中的一個隱藏函數(shù)，在函數(shù)向導中是找不到的，可以直接輸入使用，對于計算年齡、工齡等非常有效。YEAR/MONTH/DAY主要功能：

7、返回指定日期年份/月份/日。使用格式：=YEAR(date)WEEKDAY主要功能：給出指定日期的對應的星期數(shù)(星期幾)使用格式：=WEEKDAY(serial_number,return_type)參數(shù)說明： serial_number代表指定的日期或引用含有日期的單元格； return_type代表星期的表示方式（參數(shù)）：參數(shù)是2：（星期一）為1，（星期日）為7（符合習慣）；參數(shù)是1：（星期日）為1，（星期六）為7；參數(shù)是3：（星期一）為0，（星期日）為6。特別提醒：如果是指定的日期，請放在英文狀態(tài)下的雙引號中，如=WEEKDAY("2003-12-18",2)。HOU

8、R/MINUTE/SECOND主要功能：返回指定時間的時/分/秒。使用格式：= HOUR/MINUTE/SECOND (time)四、文本函數(shù)LEN LEFT/RIGHT MIDREPLACE TEXTVALUE TRIMLEN主要功能：統(tǒng)計文本字符串中字符數(shù)目。使用格式：= LEN (text)參數(shù)說明：LEN要統(tǒng)計時，無論中全角字符，還是半角字符，每個字符均計為“1”。LEFT/RIGHT主要功能：從一個文本字符串的最后一個字符/第一個字符，截取指定數(shù)目的字符。使用格式：= LEFT/RIGHT (text,num_chars)參數(shù)說明：text代表要截字符的字符串,num_chars代表

9、給定的截取數(shù)目。MID主要功能：從一個文本字符串的指定位置開始，截取指定數(shù)目的字符。使用格式：=MID (text,start_num,num_chars) 參數(shù)說明：text代表要截字符的字符串, start_num表示指定的起始位置，num_chars代表給定的截取數(shù)目。REPLACE主要功能：根據(jù)所指定的字符數(shù)，使用其他文本替換某字符串中的部分文本(連續(xù)一些)。使用格式：=REPLACE(old_text,start_num,num_chars,new_text)參數(shù)說明：Old_text是要替換其部分字符的文本或文本所在單元格。Start_num是要用 new_text 替換的 old

10、_text 中字符的位置。Num_chars是希望 REPLACE 使用 new_text 替換 old_text 中字符的個數(shù)。New_text是要用于替換 old_text 中字符的文本。區(qū)別:=SUBSTITUE (text,old_text,new_text,instance_num)本函數(shù):用新文本替換原字符串中指定位置的文本(或者)從某位置開始,用新文本替換原字符串中的指定文本(此時省略instance_num)TEXT主要功能：根據(jù)指定的數(shù)值格式將相應的數(shù)字轉換為文本形式。使用格式：= TEXT(value,format_text)參數(shù)說明：value代表需要轉換的數(shù)值或引用的單

11、元格；format_text為指定文字形式的數(shù)字格式。特別提醒： format_text參數(shù)可以根據(jù)“單元格格式”對話框“數(shù)字”標簽中的類型進行確定。VALUE主要功能：將一個代表數(shù)值的文本型字符串轉換為數(shù)值型。使用格式：=VALUE(text)參數(shù)說明：text代表需要轉換文本型字符串數(shù)值。特別提醒：如果文本型數(shù)值不經過上述轉換，在用函數(shù)處理這些數(shù)值時，常常返回錯誤。TRIM主要功能：刪除字符串中多余的空格，但會在英文字符串中保留一個作為詞與詞之間分隔的空格。使用格式：= TRIM(string)五、數(shù)學函數(shù)INT MOD PRODUCTSUMPRODUCT ROUNDUP SUMIFSUM

12、IFS CEILING FLOORODD ABSINT主要功能：將數(shù)值向下取整為最接近的整數(shù)。使用格式：=INT(number)參數(shù)說明：number表示需要取整的數(shù)值或包含數(shù)值的單元格。特別提醒：number大于0時，不進行四舍五入；number小于0時，進行四舍五入。MOD主要功能：求出兩數(shù)相除的余數(shù)。使用格式：=MOD(number,divisor)參數(shù)說明：number代表被除數(shù)，divisor代表除數(shù)應用舉例:從身份證號碼中提取性別;判斷是否是閏年特別提醒： 如果divisor參數(shù)為零，則顯示錯誤值“#DIV/0!”； 值的符號與divisor（除數(shù)）的符號相同。PRODUCT主要功

13、能：求若干個數(shù)字的連乘積使用格式：= PRODUCT（number1,number2.）特別提醒：最多可以使用 255 個參數(shù)SUMPRODUCT主要功能：計算多列中對應值相乘之后的和，即乘積之和。使用格式：=SUMPRODUCT(array1,array2,array3, ) 或 =SUMPRODUCT(條件1)* (條件2)*，加總范圍)參數(shù)說明： 數(shù)組為 2 到 30 個，各個數(shù)組參數(shù)必須具有相同的維數(shù)，否則將返回錯誤值 #VALUE!。 若數(shù)據(jù)區(qū)域中有錯誤值時，計算出現(xiàn)錯誤值#N/A。 若數(shù)據(jù)區(qū)域中有非數(shù)值時,計算時視為0ROUNDUP主要功能：對任意實數(shù)向上舍入。使用格式：=ROUN

14、DUP(number,num_digits)參數(shù)說明：number表示需要舍入的實數(shù)，Num_digits 舍入后的數(shù)字的小數(shù)位數(shù)。特別提醒：如果 num_digits 大于 0，則向上舍入到指定的小數(shù)位；如果 num_digits 等于 0，則向上舍入到最接近的整數(shù)；如果 num_digits 小于 0，則在小數(shù)點左側向上進行舍入。SUMIF主要功能：計算符合指定條件的單元格區(qū)域內的數(shù)值和。使用格式：=SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：Range代表條件判斷所在的區(qū)域(參考區(qū)域)；Criteria為指定條件表達式(僅一個條件)；Sum_Range代表需要

15、計算的數(shù)值區(qū)域。SUMIFS主要功能：根據(jù)多個指定條件對若干單元格求和使用格式：= SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)參數(shù)說明：sum_range表示實際求和區(qū)域, criteria_range1表示參考區(qū)域1, criteria1表示條件1。CEILING主要功能：返回大于等于數(shù)值的最小整數(shù)使用格式：= CEILING(number,significance)參數(shù)說明：number 必須為數(shù)值型，significance為該數(shù)值的倍數(shù)。FLOOR主要功能：返回小于等于數(shù)值的最

16、大整數(shù)使用格式：= FLOOR(number,significance)參數(shù)說明：number 必須為數(shù)值型，significance為該數(shù)值的倍數(shù)。ODD主要功能：將一個不是奇數(shù)的數(shù)值向上舍入為最接近的奇數(shù)。使用格式：= ODD(number)參數(shù)說明：number 必須為數(shù)值型，若number非數(shù)值參數(shù)，函數(shù) ODD 將返回錯誤值 #VALUE!。ABS主要功能：求數(shù)值或數(shù)值表達式的絕對值。使用格式：= ABS(number/reference)參數(shù)說明：number/reference必須為數(shù)值型。六、統(tǒng)計函數(shù)COUNTIF COUNTIFS COUNTBLANKRANK FREQUEN

17、CY AVERAGEIFMAX/MIN MODE MEDIANLARGE/SMALLCOUNTIF主要功能：對符合指定條件的單元格區(qū)域計數(shù)。使用格式：=COUNTIF（Range,Criteria）參數(shù)說明：Range表示需要計數(shù)的區(qū)域；Criteria為指定條件表達式(數(shù)字、表達式或文本)。COUNTIFS主要功能：根據(jù)多個指定條件對若干單元格計數(shù)使用格式：= COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, .)參數(shù)說明：criteria_range1表示參考區(qū)域1, criteria1表示條件1。用法與cou

18、ntif類似，但countif針對單一條件，而countifs可以實現(xiàn)多個條件同時求結果。COUNTBLANK主要功能：計算指定單元格區(qū)域中空白單元格個數(shù)使用格式：= COUNTBLANK(range)參數(shù)說明：空文本 ” ”也會計算在內應用舉例：查看員工缺勤天數(shù)/次數(shù)RANK主要功能：求某一個數(shù)值在某一區(qū)域內的排名使用格式：= RANK(number,reference,order)參數(shù)說明： Number必須有一個數(shù)字值,reference必須是一個數(shù)組或單元格區(qū)域(數(shù)據(jù)值)； order:為 0 或不輸入，結果按降序排列。為1，結果按升序排列。特別提醒：reference采用絕對引用符號

19、（ “$”）。這樣設置后，選中 “填充柄”向下拖拉，公式才不會隨之變動。FREQUENCY主要功能：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。使用格式：= FREQUENCY(data_array,bins_array)參數(shù)說明：Data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組進行分隔一列數(shù)值（分段標準）。特別提醒：輸入結果時，先選定結果所在區(qū)域。輸入完成后，需要通過按“Ctrl+Shift+Enter”組合鍵進行確認，確認后公式兩端出現(xiàn)一對大括號（），此大括號不能直接輸入。AVERAGEIF主要功能：返回某個區(qū)域內滿足給定條件的的平均值（求非零

20、平均值）。使用格式：= AVERAGEIF(criteria_range, criteria, average_range) 或= AVERAGEIF(average_range, criteria)參數(shù)說明： criteria_Range為必需，指條件所在或代表的區(qū)域，其中包含數(shù)字或包含數(shù)字的名稱、數(shù)組或引用； Criteria是條件表達式，形式為數(shù)字、表達式、單元格引用或文本的條件； Average_range可選，計算平均值的實際單元格組。如果省略，則使用 range。MAX/MIN主要功能：求一組數(shù)的最大值/最小值使用格式：= MAX/MIN (number1, number2, nu

21、mber3) 參數(shù)說明：參數(shù)含文本或邏輯值，則忽略，且參數(shù)不超過30個。MODE主要功能：返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。使用格式：= MODE (number1, number2, number3) 參數(shù)說明：如果數(shù)組中不含有重復的數(shù)據(jù)，則返回錯誤值N/A。參數(shù)不超過30個。MEDIAN主要功能：返回在某一數(shù)組或數(shù)據(jù)區(qū)域的值。使用格式：= MEDIAN (number1, number2, number3) 參數(shù)說明：參數(shù)不超過255個。LARGE/SMALL主要功能：返回在某一數(shù)據(jù)組中的第K個最大值/最小值。使用格式：= LARGE/SMALL (array,k) 參數(shù)說明：

22、 array為需要找到第k個最大值/最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。 k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從大到小) /(從小到大)。七、檢視與參照函數(shù)INDEX主要功能：返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值進行確定。使用格式：=INDEX(array,row_num,column_num)參數(shù)說明：Array代表單元格區(qū)域或數(shù)組常量；Row_num表示指定的行序號（如果省略row_num，則必須有 column_num）；Column_num表示指定的列序號（如果省略column_num，則必須有 row_num）。（row_num和column_num必須有一個）MA

23、CTH主要功能：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應位置。使用格式：=MATCH(lookup_value,lookup_array,match_type)參數(shù)說明： Lookup_value代表需要在數(shù)據(jù)表中查找的數(shù)值； Lookup_array表示可能包含所要查找的數(shù)值的區(qū)域； Match_type表示查找方式（-1、0或1）：值為-1：查找大于等于 lookup_value的最小數(shù)值，Lookup_array 必須按降序排列；值為0：查找等于lookup_value 的第一個數(shù)值，Lookup_array 可以按任何順序排列；值為1：查找小于等于lookup_value 的最大數(shù)值，Lookup_array 必須按升序排列；如果省略match_type，則默認為1。特別提醒：區(qū)域必須是某一行或某一列，即一維數(shù)據(jù)ROW主要功能：返回一個引用的行號。使用格式：=ROW(reference)參數(shù)說明：Reference為需要得到其行號的單元格或單元格區(qū)域。特別提醒：若Reference為一個區(qū)域, 輸入結果時，先選定結果所在區(qū)域。輸入完成后，需要通過按“Ctrl+Shift+Enter”組合鍵進行確認。COLU