Excel常用函數(shù)使用技巧.doc

Excel常用函數(shù)使用技巧目 錄一、EXCEL函數(shù)概述二、邏輯函數(shù)三、日期時(shí)間函數(shù)四、文本函數(shù)五、數(shù)學(xué)函數(shù)六、統(tǒng)計(jì)函數(shù)七、檢視與參照函數(shù)一、EXCEL函數(shù)概述1、函數(shù)結(jié)構(gòu)：=函數(shù)名稱(參數(shù))說明：函數(shù)以公式形式表達(dá)，輸入公式應(yīng)加引導(dǎo)符號(hào) = ;參數(shù)：可以是數(shù)字、文本、邏輯值(TRUE 或FALSE)、數(shù)組、錯(cuò)誤值(#N/A)、單元格引用等;圓括號(hào) “()”盡量在英文狀態(tài)下輸入。2、數(shù)據(jù)格式：通用型文本型（文字文本和數(shù)字文本）數(shù)值型百分比日期型時(shí)間型貨幣型科學(xué)計(jì)數(shù)3、運(yùn)算符（1）算術(shù)運(yùn)算符：+ - * / （2）比較運(yùn)算符： = = = 比較運(yùn)算的結(jié)果為邏輯值TRUE或FALSE（3）文本運(yùn)算符：&（連接多個(gè)文本字符，產(chǎn)生一串新文本）（4）引用運(yùn)算符： 引用單元格，有相對(duì)引用（A5:B7）、絕對(duì)引用（$A$5:$B$7）和混合引用（$A5:B$7）。（5）邏輯運(yùn)算符：NOT AND OR （6）通配符（特殊）：星號(hào)“*”： 可代替任意數(shù)目的字符（一個(gè)或多個(gè)）。問號(hào)“?”： 可代替任何單個(gè)字符。僅對(duì)字符型數(shù)據(jù)進(jìn)行操作的(包括：Match()、Vlookup()、Countif()、Sumif()等函數(shù))，對(duì)數(shù)值型數(shù)據(jù)無效的 。4、函數(shù)嵌套函數(shù)嵌套是指根據(jù)需要，在一個(gè)函數(shù)的使用過程中調(diào)用其他函數(shù)。比如：=WEEKDAY(TODAY(),2); =IF(MAX(A3:A8),IF(),“ ”)5、公式運(yùn)算結(jié)果出錯(cuò)信息（舉例）：（1）#NUM!：數(shù)字不能被運(yùn)算（2）#NAME!：引用了不能被識(shí)別的函數(shù)名或單元格（3）#VALUE!：使用了錯(cuò)誤的數(shù)據(jù)類型（4）#!：?jiǎn)卧耧@示不下內(nèi)容（寬度不夠） 運(yùn)算符和數(shù)字、文本等連接，可構(gòu)成表達(dá)式(如：A5=30;left(A4,1)= “王”)二、邏輯函數(shù)IF主要功能：根據(jù)對(duì)指定條件的邏輯判斷的真假結(jié)果，返回相對(duì)應(yīng)的內(nèi)容。使用格式：=IF(Logical,Value_if_true,Value_if_false)參數(shù)說明：Logical代表邏輯判斷表達(dá)式；Value_if_true表示當(dāng)判斷條件為邏輯“真（TRUE）”時(shí)的顯示內(nèi)容，如果忽略返回“TRUE”；Value_if_false表示當(dāng)判斷條件為邏輯“假（FALSE）”時(shí)的顯示內(nèi)容，如果忽略返回“FALSE”。特別提醒：IF函數(shù)嵌套最多：excel07版64層；excel03版7層。OR主要功能：返回邏輯值，僅當(dāng)所有參數(shù)值均為邏輯“假（FALSE）”時(shí)返回函數(shù)結(jié)果邏輯“假（FALSE）”，否則都返回邏輯“真（TRUE）”。使用格式：=OR(logical1,logical2, .)參數(shù)說明：Logical1,Logical2,Logical3：表示待測(cè)試的條件值或表達(dá)式，最多這30個(gè)。特別提醒：AND和NOT函數(shù)的用法相似三、日期時(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)日期/時(shí)間。使用格式：=TODAY()；=NOW()參數(shù)說明：該函數(shù)不需要參數(shù)。特別提醒：顯示出來的日期/時(shí)間格式，可以通過單元格格式進(jìn)行重新設(shè)置。DATEDIF主要功能：計(jì)算返回兩個(gè)日期參數(shù)的差值。使用格式：=DATEDIF(start_date,end_date,unit)參數(shù)說明： start_date表示開始日期，end_date代表結(jié)束日期，而且結(jié)束日期必須大于開始日期； unit（形式）：（Y 、M、D）要求返回兩個(gè)日期相差的年、月、天數(shù)；YM：忽略年和日，計(jì)算兩個(gè)日期的月份差；YD：忽略年，計(jì)算兩個(gè)日期中的天數(shù)差；MD：忽略年和月，計(jì)算日差。特別提醒：這是Excel中的一個(gè)隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降?，可以直接輸入使用，?duì)于計(jì)算年齡、工齡等非常有效。YEAR/MONTH/DAY主要功能：返回指定日期年份/月份/日。使用格式：=YEAR(date)WEEKDAY主要功能：給出指定日期的對(duì)應(yīng)的星期數(shù)(星期幾)使用格式：=WEEKDAY(serial_number,return_type)參數(shù)說明： serial_number代表指定的日期或引用含有日期的單元格； return_type代表星期的表示方式（參數(shù)）：參數(shù)是2：（星期一）為1，（星期日）為7（符合習(xí)慣）；參數(shù)是1：（星期日）為1，（星期六）為7；參數(shù)是3：（星期一）為0，（星期日）為6。特別提醒：如果是指定的日期，請(qǐng)放在英文狀態(tài)下的雙引號(hào)中，如=WEEKDAY(2003-12-18,2)。HOUR/MINUTE/SECOND主要功能：返回指定時(shí)間的時(shí)/分/秒。使用格式：= HOUR/MINUTE/SECOND (time)四、文本函數(shù)LEN LEFT/RIGHT MIDREPLACE TEXTVALUE TRIMLEN主要功能：統(tǒng)計(jì)文本字符串中字符數(shù)目。使用格式：= LEN (text)參數(shù)說明：LEN要統(tǒng)計(jì)時(shí)，無論中全角字符，還是半角字符，每個(gè)字符均計(jì)為“1”。LEFT/RIGHT主要功能：從一個(gè)文本字符串的最后一個(gè)字符/第一個(gè)字符，截取指定數(shù)目的字符。使用格式：= LEFT/RIGHT (text,num_chars)參數(shù)說明：text代表要截字符的字符串,num_chars代表給定的截取數(shù)目。MID主要功能：從一個(gè)文本字符串的指定位置開始，截取指定數(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_text 中字符的位置。Num_chars是希望 REPLACE 使用 new_text 替換 old_text 中字符的個(gè)數(shù)。New_text是要用于替換 old_text 中字符的文本。區(qū)別:=SUBSTITUE (text,old_text,new_text,instance_num)本函數(shù):用新文本替換原字符串中指定位置的文本(或者)從某位置開始,用新文本替換原字符串中的指定文本(此時(shí)省略instance_num)TEXT主要功能：根據(jù)指定的數(shù)值格式將相應(yīng)的數(shù)字轉(zhuǎn)換為文本形式。使用格式：= TEXT(value,format_text)參數(shù)說明：value代表需要轉(zhuǎn)換的數(shù)值或引用的單元格；format_text為指定文字形式的數(shù)字格式。特別提醒： format_text參數(shù)可以根據(jù)“單元格格式”對(duì)話框“數(shù)字”標(biāo)簽中的類型進(jìn)行確定。VALUE主要功能：將一個(gè)代表數(shù)值的文本型字符串轉(zhuǎn)換為數(shù)值型。使用格式：=VALUE(text)參數(shù)說明：text代表需要轉(zhuǎn)換文本型字符串?dāng)?shù)值。特別提醒：如果文本型數(shù)值不經(jīng)過上述轉(zhuǎn)換，在用函數(shù)處理這些數(shù)值時(shí)，常常返回錯(cuò)誤。TRIM主要功能：刪除字符串中多余的空格，但會(huì)在英文字符串中保留一個(gè)作為詞與詞之間分隔的空格。使用格式：= TRIM(string)五、數(shù)學(xué)函數(shù)INT MOD PRODUCTSUMPRODUCT ROUNDUP SUMIFSUMIFS CEILING FLOORODD ABSINT主要功能：將數(shù)值向下取整為最接近的整數(shù)。使用格式：=INT(number)參數(shù)說明：number表示需要取整的數(shù)值或包含數(shù)值的單元格。特別提醒：number大于0時(shí)，不進(jìn)行四舍五入；number小于0時(shí)，進(jìn)行四舍五入。MOD主要功能：求出兩數(shù)相除的余數(shù)。使用格式：=MOD(number,divisor)參數(shù)說明：number代表被除數(shù)，divisor代表除數(shù)應(yīng)用舉例:從身份證號(hào)碼中提取性別;判斷是否是閏年特別提醒： 如果divisor參數(shù)為零，則顯示錯(cuò)誤值“#DIV/0!”； 值的符號(hào)與divisor（除數(shù)）的符號(hào)相同。PRODUCT主要功能：求若干個(gè)數(shù)字的連乘積使用格式：= PRODUCT（number1,number2.）特別提醒：最多可以使用 255 個(gè)參數(shù)SUMPRODUCT主要功能：計(jì)算多列中對(duì)應(yīng)值相乘之后的和，即乘積之和。使用格式：=SUMPRODUCT(array1,array2,array3, ) 或 =SUMPRODUCT(條件1)* (條件2)*，加總范圍)參數(shù)說明： 數(shù)組為 2 到 30 個(gè)，各個(gè)數(shù)組參數(shù)必須具有相同的維數(shù)，否則將返回錯(cuò)誤值 #VALUE!。 若數(shù)據(jù)區(qū)域中有錯(cuò)誤值時(shí)，計(jì)算出現(xiàn)錯(cuò)誤值#N/A。 若數(shù)據(jù)區(qū)域中有非數(shù)值時(shí),計(jì)算時(shí)視為0ROUNDUP主要功能：對(duì)任意實(shí)數(shù)向上舍入。使用格式：=ROUNDUP(number,num_digits)參數(shù)說明：number表示需要舍入的實(shí)數(shù)，Num_digits 舍入后的數(shù)字的小數(shù)位數(shù)。特別提醒：如果 num_digits 大于 0，則向上舍入到指定的小數(shù)位；如果 num_digits 等于 0，則向上舍入到最接近的整數(shù)；如果 num_digits 小于 0，則在小數(shù)點(diǎn)左側(cè)向上進(jìn)行舍入。SUMIF主要功能：計(jì)算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。使用格式：=SUMIF（Range,Criteria,Sum_Range）參數(shù)說明：Range代表?xiàng)l件判斷所在的區(qū)域(參考區(qū)域)；Criteria為指定條件表達(dá)式(僅一個(gè)條件)；Sum_Range代表需要計(jì)算的數(shù)值區(qū)域。SUMIFS主要功能：根據(jù)多個(gè)指定條件對(duì)若干單元格求和使用格式：= SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, .)參數(shù)說明：sum_range表示實(shí)際求和區(qū)域, criteria_range1表示參考區(qū)域1, criteria1表示條件1。CEILING主要功能：返回大于等于數(shù)值的最小整數(shù)使用格式：= CEILING(number,significance)參數(shù)說明：number 必須為數(shù)值型，significance為該數(shù)值的倍數(shù)。FLOOR主要功能：返回小于等于數(shù)值的最大整數(shù)使用格式：= FLOOR(number,significance)參數(shù)說明：number 必須為數(shù)值型，significance為該數(shù)值的倍數(shù)。ODD主要功能：將一個(gè)不是奇數(shù)的數(shù)值向上舍入為最接近的奇數(shù)。使用格式：= ODD(number)參數(shù)說明：number 必須為數(shù)值型，若number非數(shù)值參數(shù)，函數(shù) ODD 將返回錯(cuò)誤值 #VALUE!。ABS主要功能：求數(shù)值或數(shù)值表達(dá)式的絕對(duì)值。使用格式：= ABS(number/reference)參數(shù)說明：number/reference必須為數(shù)值型。六、統(tǒng)計(jì)函數(shù)COUNTIF COUNTIFS COUNTBLANKRANK FREQUENCY AVERAGEIFMAX/MIN MODE MEDIANLARGE/SMALLCOUNTIF主要功能：對(duì)符合指定條件的單元格區(qū)域計(jì)數(shù)。使用格式：=COUNTIF（Range,Criteria）參數(shù)說明：Range表示需要計(jì)數(shù)的區(qū)域；Criteria為指定條件表達(dá)式(數(shù)字、表達(dá)式或文本)。COUNTIFS主要功能：根據(jù)多個(gè)指定條件對(duì)若干單元格計(jì)數(shù)使用格式：= COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, .)參數(shù)說明：criteria_range1表示參考區(qū)域1, criteria1表示條件1。用法與countif類似，但countif針對(duì)單一條件，而countifs可以實(shí)現(xiàn)多個(gè)條件同時(shí)求結(jié)果。COUNTBLANK主要功能：計(jì)算指定單元格區(qū)域中空白單元格個(gè)數(shù)使用格式：= COUNTBLANK(range)參數(shù)說明：空文本 ” ”也會(huì)計(jì)算在內(nèi)應(yīng)用舉例：查看員工缺勤天數(shù)/次數(shù)RANK主要功能：求某一個(gè)數(shù)值在某一區(qū)域內(nèi)的排名使用格式：= RANK(number,reference,order)參數(shù)說明： Number必須有一個(gè)數(shù)字值,reference必須是一個(gè)數(shù)組或單元格區(qū)域(數(shù)據(jù)值)； order:為 0 或不輸入，結(jié)果按降序排列。為1，結(jié)果按升序排列。特別提醒：reference采用絕對(duì)引用符號(hào)（ “$”）。這樣設(shè)置后，選中 “填充柄”向下拖拉，公式才不會(huì)隨之變動(dòng)。FREQUENCY主要功能：以一列垂直數(shù)組返回某個(gè)區(qū)域中數(shù)據(jù)的頻率分布。使用格式：= FREQUENCY(data_array,bins_array)參數(shù)說明：Data_array表示用來計(jì)算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組進(jìn)行分隔一列數(shù)值（分段標(biāo)準(zhǔn)）。特別提醒：輸入結(jié)果時(shí)，先選定結(jié)果所在區(qū)域。輸入完成后，需要通過按“Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)，確認(rèn)后公式兩端出現(xiàn)一對(duì)大括號(hào)（），此大括號(hào)不能直接輸入。AVERAGEIF主要功能：返回某個(gè)區(qū)域內(nèi)滿足給定條件的的平均值（求非零平均值）。使用格式：= AVERAGEIF(criteria_range, criteria, average_range) 或= AVERAGEIF(average_range, criteria)參數(shù)說明： criteria_Range為必需，指條件所在或代表的區(qū)域，其中包含數(shù)字或包含數(shù)字的名稱、數(shù)組或引用； Criteria是條件表達(dá)式，形式為數(shù)字、表達(dá)式、單元格引用或文本的條件； Average_range可選，計(jì)算平均值的實(shí)際單元格組。如果省略，則使用 range。MAX/MIN主要功能：求一組數(shù)的最大值/最小值使用格式：= MAX/MIN (number1, number2, number3) 參數(shù)說明：參數(shù)含文本或邏輯值，則忽略，且參數(shù)不超過30個(gè)。MODE主要功能：返回在某一數(shù)組或數(shù)據(jù)區(qū)域中出現(xiàn)頻率最多的數(shù)值。使用格式：= MODE (number1, number2, number3) 參數(shù)說明：如果數(shù)組中不含有重復(fù)的數(shù)據(jù)，則返回錯(cuò)誤值N/A。參數(shù)不超過30個(gè)。MEDIAN主要功能：返回在某一數(shù)組或數(shù)據(jù)區(qū)域的值。使用格式：= MEDIAN (number1, number2, number3) 參數(shù)說明：參數(shù)不超過255個(gè)。LARGE/SMALL主要功能：返回在某一數(shù)據(jù)組中的第K個(gè)最大值/最小值。使用格式：= LARGE/SMALL (array,k) 參數(shù)說明： array為需要找到第k個(gè)最大值/最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域。 k為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從大到小) /(從小到大)。七、檢視與參照函數(shù)INDEX主要功能：返回列表或數(shù)組中的元素值，此元素由行序號(hào)和列序號(hào)的索引值進(jìn)行確定。使用格式：=INDEX(array,row_num,column_num)參數(shù)說明：Array代表單元格區(qū)域或數(shù)組常量；Row_num表示指定的行序號(hào)（如果省略row_num，則必須有 column_num）；Column_num表示指定的列序號(hào)（如果省略column_num，則必須有 row_num）。（row_num和column_num必須有一個(gè)）MACTH主要功能：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。使用格式：=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 的第一個(gè)數(shù)值，Lookup_array 可以按任何順序排列；值為1：查找小于等于lookup_value 的最大數(shù)值，Lookup_array 必須按升序排列；如果省略match_type，則默認(rèn)為1。特別提醒：區(qū)域必須是某一行或某一列，即一維數(shù)據(jù)ROW主要功能：返回一個(gè)引用的行號(hào)。使用格式：=ROW(reference)參數(shù)說明：Reference為需要得到其行號(hào)的單元格或單元格區(qū)域。特別提醒：若Reference為一個(gè)區(qū)域, 輸入結(jié)果時(shí)，先選定結(jié)果所在區(qū)域。輸入完成后，需要通過按“Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)。COL