Excel函數(shù)應(yīng)用案例完全手冊模板

Excel函數(shù)應(yīng)用案例完全手冊模板資料內(nèi)容僅供您學(xué)習(xí)參考，如有不當(dāng)或者侵權(quán)，請聯(lián)系改正或者刪除。Excel函數(shù)應(yīng)用完全手冊 張迎新 附錄文章一、函數(shù)應(yīng)用基礎(chǔ)1.函數(shù)和公式(1)什么是函數(shù)Excel函數(shù)即是預(yù)先定義,執(zhí)行計算、分析等處理數(shù)據(jù)任務(wù)的特殊公式。以常見的求和函數(shù)SUM為例,它的語法是”SUM(number1,number2,)”。其中”SUM”稱為函數(shù)名稱,一個函數(shù)只有唯一的一個名稱,它決定了函數(shù)的功能和用途。函數(shù)名稱后緊跟左括號,接著是用逗號分隔的稱為參數(shù)的內(nèi)容,最后用一個右括號表示函數(shù)結(jié)束。參數(shù)是函數(shù)中最復(fù)雜的組成部分,它規(guī)定了函數(shù)的運算對象、順序或結(jié)構(gòu)等。使得用戶能夠?qū)δ硞€單元格或區(qū)域進行處理,如分析存款利息、確定成績名次、計算三角函數(shù)值等。按照函數(shù)的來源,Excel函數(shù)能夠分為內(nèi)置函數(shù)和擴展函數(shù)兩大類。前者只要啟動了Excel,用戶就能夠使用它們;而后者必須經(jīng)過單擊”工具→加載宏”菜單命令加載,然后才能像內(nèi)置函數(shù)那樣使用。(2)什么是公式函數(shù)與公式既有區(qū)別又互相聯(lián)系。如果說前者是Excel預(yù)先定義好的特殊公式,后者就是由用戶自行設(shè)計對工作表進行計算和處理的公式。以公式”=SUM(E1:H1)*A1+26”為例,它要以等號”=”開始,其內(nèi)部能夠包括函數(shù)、引用、運算符和常量。上式中的”SUM(E1:H1)”是函數(shù),”A1”則是對單元格A1的引用(使用其中存儲的數(shù)據(jù)),”26”則是常量,”*”和”+”則是算術(shù)運算符(另外還有比較運算符、文本運算符和引用運算符)。如果函數(shù)要以公式的形式出現(xiàn),它必須有兩個組成部分,一個是函數(shù)名稱前面的等號,另一個則是函數(shù)本身。2.函數(shù)的參數(shù)函數(shù)右邊括號中的部分稱為參數(shù),假如一個函數(shù)能夠使用多個參數(shù),那么參數(shù)與參數(shù)之間使用半角逗號進行分隔。參數(shù)能夠是常量(數(shù)字和文本)、邏輯值(例如TRUE或FALSE)、數(shù)組、錯誤值(例如#N/A)或單元格引用(例如E1:H1),甚至能夠是另一個或幾個函數(shù)等。參數(shù)的類型和位置必須滿足函數(shù)語法的要求,否則將返回錯誤信息。(1)常量常量是直接輸入到單元格或公式中的數(shù)字或文本,或由名稱所代表的數(shù)字或文本值,例如數(shù)字”2890.56”、日期”-8-19”和文本”黎明”都是常量?？墒枪交蛴晒接嬎愠龅慕Y(jié)果都不是常量,因為只要公式的參數(shù)發(fā)生了變化,它自身或計算出來的結(jié)果就會發(fā)生變化。(2)邏輯值邏輯值是比較特殊的一類參數(shù),它只有TRUE(真)或FALSE(假)兩種類型。例如在公式”=IF(A3=0,"",A2/A3)”中,”A3=0”就是一個能夠返回TRUE(真)或FALSE(假)兩種結(jié)果的參數(shù)。當(dāng)”A3=0”為TRUE(真)時在公式所在單元格中填入”0”,否則在單元格中填入”A2/A3”的計算結(jié)果。(3)數(shù)組數(shù)組用于可產(chǎn)生多個結(jié)果,或能夠?qū)Υ娣旁谛泻土兄械囊唤M參數(shù)進行計算的公式。Excel中有常量和區(qū)域兩類數(shù)組。前者放在”{}”(按下Ctrl+Shift+Enter組合鍵自動生成)內(nèi)部,而且內(nèi)部各列的數(shù)值要用逗號”,”隔開,各行的數(shù)值要用分號”;”隔開。假如你要表示第1行中的56、78、89和第2行中的90、76、80,就應(yīng)該建立一個2行3列的常量數(shù)組”{56,78,89;90,76,80}。區(qū)域數(shù)組是一個矩形的單元格區(qū)域,該區(qū)域中的單元格共用一個公式。例如公式”=TREND(B1:B3,A1:A3)”作為數(shù)組公式使用時,它所引用的矩形單元格區(qū)域”B1:B3,A1:A3”就是一個區(qū)域數(shù)組。(4)錯誤值使用錯誤值作為參數(shù)的主要是信息函數(shù),例如”ERROR.TYPE”函數(shù)就是以錯誤值作為參數(shù)。它的語法為”ERROR.TYPE(error_val)”,如果其中的參數(shù)是#NUM!,則返回數(shù)值”6”。(5)單元格引用單元格引用是函數(shù)中最常見的參數(shù),引用的目的在于標(biāo)識工作表單元格或單元格區(qū)域,并指明公式或函數(shù)所使用的數(shù)據(jù)的位置,便于它們使用工作表各處的數(shù)據(jù),或者在多個函數(shù)中使用同一個單元格的數(shù)據(jù)。還能夠引用同一工作簿不同工作表的單元格,甚至引用其它工作簿中的數(shù)據(jù)。根據(jù)公式所在單元格的位置發(fā)生變化時,單元格引用的變化情況,我們能夠引用分為相對引用、絕對引用和混合引用三種類型。以存放在F2單元格中的公式”=SUM(A2:E2)”為例,當(dāng)公式由F2單元格復(fù)制到F3單元格以后,公式中的引用也會變化為”=SUM(A3:E3)”。若公式自F列向下繼續(xù)復(fù)制,”行標(biāo)”每增加1行,公式中的行標(biāo)也自動加1。如果上述公式改為”=SUM($A$3:$E＄3)”,則無論公式復(fù)制到何處,其引用的位置始終是”A3:E3”區(qū)域?；旌弦糜小苯^對列和相對行”,或是”絕對行和相對列”兩種形式。前者如”=SUM($A3:$E3)”,后者如”=SUM(A$3:E$3)”。上面的幾個實例引用的都是同一工作表中的數(shù)據(jù),如果要分析同一工作簿中多張工作表上的數(shù)據(jù),就要使用三維引用。假如公式放在工作表Sheet1的C6單元格,要引用工作表Sheet2的”A1:A6”和Sheet3的”B2:B9”區(qū)域進行求和運算,則公式中的引用形式為”=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是說三維引用中不但包含單元格或區(qū)域引用,還要在前面加上帶”!”的工作表名稱。假如你要引用的數(shù)據(jù)來自另一個工作簿,如工作簿Book1中的SUM函數(shù)要絕對引用工作簿Book2中的數(shù)據(jù),其公式為”=SUM([Book2]Sheet1!SAS1:SAS8,[Book2]Sheet2!SBS1:SBS9)”,也就是在原來單元格引用的前面加上”[Book2]Sheet1!”。放在中括號里面的是工作簿名稱,帶”!”的則是其中的工作表名稱。即是跨工作簿引用單元格或區(qū)域時,引用對象的前面必須用”!”作為工作表分隔符,再用中括號作為工作簿分隔符。不過三維引用的要受到較多的限制,例如不能使用數(shù)組公式等。提示:上面介紹的是Excel默認(rèn)的引用方式,稱為”A1引用樣式”。如果你要計算處在”宏”內(nèi)的行和列,必須使用”R1C1引用樣式”。在這種引用樣式中,Excel使用”R”加”行標(biāo)”和”C”加”列標(biāo)”的方法指示單元格位置。啟用或關(guān)閉R1C1引用樣式必須單擊”工具→選項”菜單命令,打開對話框的”常規(guī)”選項卡,選中或清除”設(shè)置”下的”R1C1引用樣式”選項。由于這種引用樣式很少使用,限于篇幅本文不做進一步介紹。(6)嵌套函數(shù)除了上面介紹的情況外,函數(shù)也能夠是嵌套的,即一個函數(shù)是另一個函數(shù)的參數(shù),例如”=IF(OR(RIGHTB(E2,1)="1",RIGHTB(E2,1)="3",RIGHTB(E2,1)="5",RIGHTB(E2,1)="7",RIGHTB(E2,1)="9"),"男","女")”。其中公式中的IF函數(shù)使用了嵌套的RIGHTB函數(shù),并將后者返回的結(jié)果作為IF的邏輯判斷依據(jù)。(7)名稱和標(biāo)志為了更加直觀地標(biāo)識單元格或單元格區(qū)域,我們能夠給它們賦予一個名稱,從而在公式或函數(shù)中直接引用。例如”B2:B46”區(qū)域存放著學(xué)生的物理成績,求解平均分的公式一般是”=AVERAGE(B2:B46)”。在給B2:B46區(qū)域命名為”物理分?jǐn)?shù)”以后,該公式就能夠變?yōu)椤?AVERAGE(物理分?jǐn)?shù))”,從而使公式變得更加直觀。給一個單元格或區(qū)域命名的方法是:選中要命名的單元格或單元格區(qū)域,鼠標(biāo)單擊編輯欄頂端的”名稱框”,在其中輸入名稱后回車。也能夠選中要命名的單元格或單元格區(qū)域,單擊”插入→名稱→定義”菜單命令,在打開的”定義名稱”對話框中輸入名稱后確定即可。如果你要刪除已經(jīng)命名的區(qū)域,能夠按相同方法打開”定義名稱”對話框,選中你要刪除的名稱刪除即可。由于Excel工作表多數(shù)帶有”列標(biāo)志”。例如一張成績統(tǒng)計表的首行一般帶有”序號”、”姓名”、”數(shù)學(xué)”、”物理”等”列標(biāo)志”(也能夠稱為字段),如果單擊”工具→選項”菜單命令,在打開的對話框中單擊”重新計算”選項卡,選中”工作簿選項”選項組中的”接受公式標(biāo)志”選項,公式就能夠直接引用”列標(biāo)志”了。例如”B2:B46”區(qū)域存放著學(xué)生的物理成績,而B1單元格已經(jīng)輸入了”物理”字樣,則求物理平均分的公式能夠?qū)懗伞?AVERAGE(物理)”。需要特別說明的是,創(chuàng)立好的名稱能夠被所有工作表引用,而且引用時不需要在名稱前面添加工作表名(這就是使用名稱的主要優(yōu)點),因此名稱引用實際上是一種絕對引用?？墒枪揭谩绷袠?biāo)志”時的限制較多,它只能在當(dāng)前數(shù)據(jù)列的下方引用,不能跨越工作表引用,可是引用”列標(biāo)志”的公式在一定條件下能夠復(fù)制。從本質(zhì)上講,名稱和標(biāo)志都是單元格引用的一種方式。因為它們不是文本,使用時名稱和標(biāo)志都不能添加引號。3.函數(shù)輸入方法對Excel公式而言,函數(shù)是其中的主要組成部分,因此公式輸入能夠歸結(jié)為函數(shù)輸入的問題。(1)”插入函數(shù)”對話框”插入函數(shù)”對話框是Excel輸入公式的重要工具,以公式”=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”為例,Excel輸入該公式的具體過程是:首先選中存放計算結(jié)果(即需要應(yīng)用公式)的單元格,單擊編輯欄(或工具欄)中的”fx”按鈕,則表示公式開始的”=”出現(xiàn)在單元格和編輯欄,然后在打開的”插入函數(shù)”對話框中的”選擇函數(shù)”列表找到”SUM”函數(shù)。如果你需要的函數(shù)不在里面,能夠打開”或選擇類別”下拉列表進行選擇。最后單擊”確定”按鈕,打開”函數(shù)參數(shù)”對話框。對SUM函數(shù)而言,它能夠使用從number1開始直到number30共30個參數(shù)。對上面的公式來說,首先應(yīng)當(dāng)把光標(biāo)放在對話框的”number1”框中,單擊工作簿中的”Sheet2!”工作表標(biāo)簽,”Sheet2!”即可自動進入其中,接著鼠標(biāo)拖動選中你要引用的區(qū)域即可。接著用鼠標(biāo)單擊對話框的”number2”框,單擊工作簿中的”Sheet3!”工作表標(biāo)簽,其名稱”Sheet3!”即可自動進入其中,再按相同方法選擇要引用的單元格區(qū)域即可。上述方法的最大優(yōu)點就是引用的區(qū)域很準(zhǔn)確,特別是三維引用時不容易發(fā)生工作表或工作簿名稱輸入錯誤的問題。(2)編輯欄輸入如果你要套用某個現(xiàn)成公式,或者輸入一些嵌套關(guān)系復(fù)雜的公式,利用編輯欄輸入更加快捷。首先選中存放計算結(jié)果的單元格;鼠標(biāo)單擊Excel編輯欄,按照公式的組成順序依次輸入各個部分,公式輸入完畢后,單擊編輯欄中的”輸入”(即”√”)按鈕(或回車)即可。手工輸入時同樣能夠采取上面介紹的方法引用區(qū)域,以公式”=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”為例,你能夠先在編輯欄中輸入”=SUM()”,然后將光標(biāo)插入括號中間,再按上面介紹的方法操作就能夠引用輸入公式了。可是分隔引用之間的逗號必須用手工輸入,而不能像”插入函數(shù)”對話框那樣自動添加。在《電腦報合訂本附贈光盤》相應(yīng)文章中,我們列出了所有Excel函數(shù),供讀者速查。(二)日期與時間函數(shù)1.DATE用途:返回代表特定日期的序列號。語法:DATE(year,month,day)參數(shù):year為一到四位,根據(jù)使用的日期系統(tǒng)解釋該參數(shù)。默認(rèn)情況下,ExcelforWindows使用1900日期系統(tǒng),而ExcelforMacintosh使用1904日期系統(tǒng)。Month代表每年中月份的數(shù)字。如果所輸入的月份大于12,將從指定年份的一月份執(zhí)行加法運算。Day代表在該月份中第幾天的數(shù)字。如果day大于該月份的最大天數(shù)時,將從指定月份的第一天開始往上累加。注意:Excel按順序的序列號保存日期,這樣就能夠?qū)ζ溥M行計算。如果工作簿使用的是1900日期系統(tǒng),則Excel會將191月1日保存為序列號1。同理,會將1998年1月1日保存為序列號35796,因為該日期距離191月1日為35795天。實例:如果采用1900日期系統(tǒng)(Excel默認(rèn)),則公式”=DATE(,1,1)”返回36892。2.DATEVALUE用途:返回date_text所表示的日期的序列號。該函數(shù)的主要用途是將文字表示的日期轉(zhuǎn)換成一個序列號。語法:DATEVALUE(date_text)參數(shù):Date_text是用Excel日期格式表示日期的文本。在使用1900日期系統(tǒng)中,date_text必須是191月1日到9999年12月31日之間的一個日期;而在1904日期系統(tǒng)中,date_text必須是191月1日到9999年12月31日之間的一個日期。如果date_text超出上述范圍,則函數(shù)DATEVALUE返回錯誤值#VALUE!。如果省略參數(shù)date_text中的年代,則函數(shù)DATEVALUE使用電腦系統(tǒng)內(nèi)部時鐘的當(dāng)前年代,且date_text中的時間信息將被忽略。實例:公式”=DATEVALUE("/3/5")”返回36955,DATEVALUE("2-26")返回36948。3.DAY用途:返回用序列號(整數(shù)1到31)表示的某日期的天數(shù),用整數(shù)1到31表示。語法:DAY(serial_number)參數(shù):Serial_number是要查找的天數(shù)日期,它有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如1900日期系統(tǒng)的35825表示的1998年1月30日),以及其它公式或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))。實例:公式”=DAY("/1/27")”返回27,=DAY(35825)返回30,=DAY(DATEVALUE("/1/25"))返回25。4.DAYS360用途:按照一年360天的算法(每個月30天,一年共計12個月),返回兩日期間相差的天數(shù)。語法:DAYS360(start_date,end_date,method)參數(shù):Start_date和end_date是用于計算期間天數(shù)的起止日期。如果start_date在end_date之后,則DAYS360將返回一個負(fù)數(shù)。日期能夠有多種輸入方式:帶引號的文本串(例如:"1998/01/30")、序列號(例如:如果使用1900日期系統(tǒng),則35825表示1998年1月30日)或其它公式或函數(shù)的結(jié)果(例如,DATEVALUE("1998/1/30"))。Method是一個邏輯值,它指定了在計算中是采用歐洲方法還是美國方法。若為FALSE或忽略,則采用美國方法(如果起始日期是一個月的31日,則等于同月的30日。如果終止日期是一個月的31日,而且起始日期早于30日,則終止日期等于下一個月的1日,否則,終止日期等于本月的30日)。若為TRUE則采用歐洲方法(無論是起始日期還是終止日期為一個月的31號,都將等于本月的30號)。實例:公式”=DAYS360("1998/2/1","/2-1")”返回1080。5.EDATE用途:返回指定日期(start_date)之前或之后指定月份的日期序列號。語法:EDATE(start_date,months)參數(shù):Start_date參數(shù)代表開始日期,它有多種輸入方式:帶引號的文本串(例如:"1998/01/30")、序列號(如35825表示1998年1月30日)或其它公式或函數(shù)的結(jié)果(例如:DATEVALUE("1998/1/30"))。Months為在start_date之前或之后的月份數(shù),未來日期用正數(shù)表示,過去日期用負(fù)數(shù)表示。實例:公式”=EDATE("/3/5",2)”返回37016即5月5日,=EDATE("/3/5",-6)返回36774即9月5日。6.EOMONTH用途:返回start-date之前或之后指定月份中最后一天的序列號。語法:EOMONTH(start_date,months)參數(shù):Start_date參數(shù)代表開始日期,它有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如1900日期系統(tǒng)中的35825)或其它公式或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))。Month為start_date之前或之后的月份數(shù),正數(shù)表示未來日期,負(fù)數(shù)表示過去日期。實例:公式”=EOMONTH("/01/01",2)”返回36981即3月31日,=EOMONTH("/01/01",-6)返回36738即7月31日。7.HOUR用途:返回時間值的小時數(shù)。即介于0(12:00A.M.)到23(11:00P.M.)之間的一個整數(shù)。語法:HOUR(serial_number)參數(shù):Serial_number表示一個時間值,其中包含著要返回的小時數(shù)。它有多種輸入方式:帶引號的文本串(如"6:45PM")、十進制數(shù)(如0.78125表示6:45PM)或其它公式或函數(shù)的結(jié)果(如TIMEVALUE("6:45PM"))。實例:公式”=HOUR("3:30:30PM")”返回15,=HOUR(0.5)返回12即12:00:00AM,=HOUR(29747.7)返回16。8.MINUTE用途:返回時間值中的分鐘,即介于0到59之間的一個整數(shù)。語法:MINUTE(serial_number)參數(shù):Serial_number是一個時間值,其中包含著要查找的分鐘數(shù)。時間有多種輸入方式:帶引號的文本串(如"6:45PM")、十進制數(shù)(如0.78125表示6:45PM)或其它公式或函數(shù)的結(jié)果(如TIMEVALUE("6:45PM"))。實例:公式”=MINUTE("15:30:00")”返回30,=MINUTE(0.06)返回26,=MINUTE(TIMEVALUE("9:45PM"))返回45。9.MONTH用途:返回以序列號表示的日期中的月份,它是介于1(一月)和12(十二月)之間的整數(shù)。語法:MONTH(serial_number)參數(shù):Serial_number表示一個日期值,其中包含著要查找的月份。日期有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如表示1998年1月30日的35825)或其它公式或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))等。實例:公式”=MONTH("/02/24")”返回2,=MONTH(35825)返回1,=MONTH(DATEVALUE("/6/30"))返回6。10.NETWORKDAYS用途:返回參數(shù)start-data和end-data之間完整的工作日(不包括周末和專門指定的假期)數(shù)值。語法:NETWORKDAYS(start_date,end_date,holidays)參數(shù):Start_date代表開始日期,End_date代表終止日;Holidays是表示不在工作日歷中的一個或多個日期所構(gòu)成的可選區(qū)域,法定假日以及其它非法定假日。此數(shù)據(jù)清單能夠是包含日期的單元格區(qū)域,也能夠是由代表日期的序列號所構(gòu)成的數(shù)組常量。函數(shù)中的日期有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如使用1900日期系統(tǒng)的35825)或其它公式或函數(shù)的結(jié)果(如DATEVALUE("1998/1/30"))。注意:該函數(shù)只有加載”分析工具庫”以后方能使用。11.NOW用途:返回當(dāng)前日期和時間所對應(yīng)的序列號。語法:NOW()參數(shù):無實例:如果正在使用的是1900日期系統(tǒng),而且計算機的內(nèi)部時鐘為-1-2812:53,則公式”=NOW()”返回36919.54。12.SECOND用途:返回時間值的秒數(shù)(為0至59之間的一個整數(shù))。語法:SECOND(serial_number)參數(shù):Serial_number表示一個時間值,其中包含要查找的秒數(shù)。關(guān)于時間的輸入方式見上文的有關(guān)內(nèi)容。實例:公式”=SECOND("3:30:26PM")”返回26,=SECOND(0.016)返回2。13.TIME用途:返回某一特定時間的小數(shù)值,它返回的小數(shù)值從0到0.99999999之間,代表0:00:00(12:00:00A.M)到23:59:59(11:59:59P.M)之間的時間。語法:TIME(hour,minute,second)參數(shù):Hour是0到23之間的數(shù),代表小時;Minute是0到59之間的數(shù),代表分;Second是0到59之間的數(shù),代表秒。實例:公式”=TIME(12,10,30)”返回序列號0.51,等價于12:10:30PM。=TIME(9,30,10)返回序列號0.40,等價于9:30:10AM。=TEXT(TIME(23,18,14),"h:mm:ssAM/PM")返回”11:18:14PM”。14.TIMEVALUE用途:返回用文本串表示的時間小數(shù)值。該小數(shù)值為從0到0.的數(shù)值,代表從0:00:00(12:00:00AM)到23:59:59(11:59:59PM)之間的時間。語法:TIMEVALUE(time_text)參數(shù):Time_text是一個用Excel時間格式表示時間的文本串(如"6:45PM"和"18:45"等)。實例:公式”=TIMEVALUE("3:30AM")”返回0.,=TIMEVALUE("/1/266:35AM")返回0.。15.TODAY用途:返回系統(tǒng)當(dāng)前日期的序列號。參數(shù):無語法:TODAY()實例:公式”=TODAY()”返回-8-28(執(zhí)行公式時的系統(tǒng)時間)。16.WEEKDAY用途:返回某日期的星期數(shù)。在默認(rèn)情況下,它的值為1(星期天)到7(星期六)之間的一個整數(shù)。語法:WEEKDAY(serial_number,return_type)參數(shù):Serial_number是要返回日期數(shù)的日期,它有多種輸入方式:帶引號的文本串(如"/02/26")、序列號(如35825表示1998年1月30日)或其它公式或函數(shù)的結(jié)果(如DATEVALUE("/1/30"))。Return_type為確定返回值類型的數(shù)字,數(shù)字1或省略則1至7代表星期天到數(shù)星期六,數(shù)字2則1至7代表星期一到星期天,數(shù)字3則0至6代表星期一到星期天。實例:公式”=WEEKDAY("/8/28",2)”返回2(星期二),=WEEKDAY("/02/23",3)返回6(星期日)。17.WEEKNUM用途:返回一個數(shù)字,該數(shù)字代表一年中的第幾周。語法:WEEKNUM(serial_num,return_type)參數(shù):Serial_num代表一周中的日期。應(yīng)使用DATE函數(shù)輸入日期,或者將日期作為其它公式或函數(shù)的結(jié)果輸入。Return_type為一數(shù)字,確定星期計算從哪一天開始。默認(rèn)值為1。18.WORKDAY用途:返回某日期(起始日期)之前或之后相隔指定工作日(不包括周末和專門指定的假日)的某一日期的值,并扣除周末或假日。語法:WORKDAY(start_date,days,holidays)參數(shù):Start_date為開始日期;Days為Start_date之前或之后不含周末及節(jié)假日的天數(shù);Days是正值將產(chǎn)生未來日期、負(fù)值產(chǎn)生過去日期;Holidays為可選的數(shù)據(jù)清單,表示需要從工作日歷中排除的日期值(如法定假日或非法定假日)。此清單能夠是包含日期的單元格區(qū)域,也能夠是由代表日期的序列號所構(gòu)成的數(shù)組常量。日期有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如1900日期系統(tǒng)時的35825表示1998年1月30日)或其它公式或函數(shù)的結(jié)果(例如DATEVALUE("1998/1/30"))。19.YEAR用途:返回某日期的年份。其結(jié)果為1900到9999之間的一個整數(shù)。語法:YEAR(serial_number)參數(shù):Serial_number是一個日期值,其中包含要查找的年份。日期有多種輸入方式:帶引號的文本串(例如"1998/01/30")、序列號(例如,如果使用1900日期系統(tǒng)則35825表示1998年1月30日)或其它公式或函數(shù)的結(jié)果(例如DATEVALUE("1998/1/30"))。實例:公式”=YEAR("/8/6")返回”,=YEAR("/05/01")返回,=YEAR(35825)返回1998。20.YEARFRAC用途:返回start_date和end_date之間的天數(shù)占全年天數(shù)的百分比。語法:YEARFRAC(start_date,end_date,basis)參數(shù):Start_date表示開始日期,End_date代表結(jié)束日期。函數(shù)中的日期有多種輸入方式:帶引號的文本串(如"1998/01/30")、序列號(如35829表示1900日期系統(tǒng)中的1998年1月30日),或其它公式或函數(shù)的結(jié)果(例如DATEVALUE("1998/1/30"))。Basis表示日計數(shù)基準(zhǔn)類型,其中0或省略為US(NASD)30/360,1實際天數(shù)/實際天數(shù),2實際天數(shù)/360,3實際天數(shù)/365,4歐洲30/360。實例:公式”=YEARFRAC("/01/31","/06/30",0)”返回0.,YEARFRAC("/01/25","/09/27")返回0.67222。13.DELTA用途:測試兩個數(shù)值是否相等。如果number1=number2,則返回1,否則返回0。語法:DELTA(number1,number2)參數(shù):Number1為第一個參數(shù)。Number2為第二個參數(shù)。如果省略,假設(shè)Number2的值為零。(六)信息函數(shù)1.CELL用途:返回某一引用區(qū)域的左上角單元格的格式、位置或內(nèi)容等信息,該函數(shù)主要用于保持與其它電子表格程序的兼容性。語法:CELL(info_type,reference)參數(shù):Info_type為一個文本值,指明所需要的單元格信息的類型。Reference表示要獲取其有關(guān)信息的單元格。如果忽略,則在info_type中所指定的信息將返回給最后更改的單元格。實例:公式”=CELL("row",A20)”返回20,如果A3單元格包含TOTAL,則CELL("contents",A3)返回"TOTAL"。2.ERROR.TYPE用途:返回對應(yīng)于某一錯誤類型的數(shù)字,如果沒有錯誤則返回#N/A。在IF函數(shù)中能夠使用ERROR.TYPE檢測錯誤值,并返回文字串(如”發(fā)生錯誤”)來取代錯誤值。語法:ERROR.TYPE(error_val)參數(shù):Error_val為需要得到其數(shù)字代碼的一個錯誤類型。盡管error_val能夠是實際的錯誤值,但它一般為一個單元格引用,而此單元格中包含需要檢測的公式。注意:ERROR.TYPE函數(shù)返回的錯誤代碼是:#NULL!返回1,#DIV/0!返回2,#VALUE!返回3,#REF!返回4,#NAME?返回5,#NUM!返回6,#N/A返回7,其它錯誤則返回#N/A。實例:如果A5=36/0,則公式”=ERROR.TYPE(A5)”返回2。3.INFO用途:返回有關(guān)當(dāng)前操作環(huán)境的的信息。語法:INFO(type_text)參數(shù):Type_text是一文本,指明所要返回的信息類型。其中"directory"返回當(dāng)前目錄或文件夾的路徑,"memavail"返回可用的內(nèi)存空間(以字節(jié)為單位),"memused"返回數(shù)據(jù)占用的內(nèi)存空間,"numfile"返回當(dāng)前所有打開的工作表的數(shù)目,"osversion"返回當(dāng)前操作系統(tǒng)的版本號(文本),"recalc"返回當(dāng)前的重計算方式(”自動”或”人工”),"release"返回Excel的版本號(文本),"system"返回操作系統(tǒng)名稱(Macintosh="mac",Windows="pcdos"),"totmem"返回全部內(nèi)存空間,包括已經(jīng)占用的內(nèi)存空間(以字節(jié)為單位)。注意:INFO函數(shù)中的文本參數(shù)必須用西文引號引起來。實例:公式”=INFO("osversion")”返回”Windows(32-bit)4.90”,=INFO("memavail")返回1048576。4.IS類函數(shù)用途:其中包括用來檢驗數(shù)值或引用類型的九個工作表函數(shù)。它們能夠檢驗數(shù)值的類型并根據(jù)參數(shù)的值返回TRUE或FALSE。例如,數(shù)值為空白單元格引用時,ISBLANK函數(shù)返回邏輯值TRUE,否則返回FALSE。語法:ISBLANK(value)、ISERR(value)、ISERROR(value)、ISLOGICAL(value)、ISNA(value)、ISNONTEXT(value)、ISNUMBER(value)、ISREF(value)、ISTEXT(value)。參數(shù):Value是需要進行檢驗的參數(shù)。分別為空白(空白單元格)、錯誤值、邏輯值、文本、數(shù)字、引用值或?qū)τ谝陨先我鈪?shù)的名稱引用。如果函數(shù)中的參數(shù)為下面的內(nèi)容,則返回TRUE:ISBLANK的參數(shù)是空白單元格,ISERR的參數(shù)是任意錯誤值(除去#N/A),ISERROR的參數(shù)是任意錯誤值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!),ISLOGICAL的參數(shù)是邏輯值,ISNA的參數(shù)是錯誤值#N/A,ISNONTEXT的參數(shù)是任意不是文本的內(nèi)容(此函數(shù)在值為空白單元格時返回TRUE),ISNUMBER的參數(shù)是數(shù)字,ISREF的參數(shù)是引用,ISTEXT的參數(shù)是文本。注意:IS類函數(shù)的參數(shù)value是不可轉(zhuǎn)換的。在其它大多數(shù)需要數(shù)字的函數(shù)中,文本"19"會被轉(zhuǎn)換成數(shù)字19。然而在公式ISNUMBER("19")中,"19"并由文本值轉(zhuǎn)換成其它類型的值,而是返回FALSE。IS類函數(shù)用于檢驗公式計算結(jié)果十分有用,它與函數(shù)IF結(jié)合在一起能夠提供一種在公式中查出錯誤值的方法。實例:公式”=ISBLANK("")”返回FALSE,=ISREF(A5)返回TRUE(其中a5為空白單元格)。如果需要計算A1:A4區(qū)域的平均值,但不能確定單元格內(nèi)是否包含數(shù)字,則公式AVERAGE(A1:A4)返回錯誤值#DIV/0!。為了應(yīng)付這種情況,能夠使用公式”=IF(ISERROR(AVERAGE(A1:A4)),"引用包含空白單元格",AVERAGE(A1:A4))”查出可能存在的錯誤。5.ISEVEN用途:測試參數(shù)的奇偶性,如果參數(shù)為偶數(shù)返回TRUE,否則返回FALSE。語法:ISEVEN(number)Number待測試的數(shù)值。如果參數(shù)值不是整數(shù),則自動截去小數(shù)部分取整。注意:該函數(shù)必須加載”分析工具庫”方能使用。如果參數(shù)number不是數(shù)值,ISEVEN函數(shù)返回錯誤值#VALUE!。實例:公式”=ISEVEN(11)返回FALSE”,=ISEVEN(6)返回TRUE。6.ISODD用途:測試參數(shù)的奇偶性,如果參數(shù)為奇數(shù)返回TRUE,否則返回FALSE。語法:ISODD(number)參數(shù):Number待測試的數(shù)值。如果參數(shù)不是整數(shù),則自動截去小數(shù)部分取整。注意:該函數(shù)必須加載”分析工具庫”方能使用。實例:公式”=ISODD(19)”返回TRUE,=ISODD(14.5)返回FALSE。7.N用途:返回轉(zhuǎn)化為數(shù)值后的值。語法:N(value)參數(shù):Value為要轉(zhuǎn)化的值。函數(shù)N能夠轉(zhuǎn)化下表列出的值:數(shù)字返回該數(shù)字,日期返回該日期的序列號,TRUE返回1,FALSE返回0,錯誤值(如#DIV/0!)返回該錯誤值,其它值返回0。實例:如果A1包含"7",A2包含"文件",A3包含"TRUE",則公式”=N(A1)”返回7,=N(A2)返回0(因為A2含有文字),=N(A3)返回1(因為A3含有TRUE)。8.NA用途:返回錯誤值#N/A。#N/A表示”無法得到有效值”。如果使用#N/A標(biāo)志空白單元格,能夠避免不小心將空白單元格計算在內(nèi)而產(chǎn)生的問題,因為公式引用含有#N/A的單元格時返回錯誤值#N/A。語法:NA()參數(shù):空白單元格實例:公式”=NA("")”返回#N/A。9.TYPE用途:返回數(shù)值的類型。當(dāng)某一個函數(shù)的計算結(jié)果取決于特定單元格中數(shù)值的類型時,可使用函數(shù)TYPE。語法:TYPE(value)參數(shù):Value能夠是Excel中的數(shù)據(jù),如數(shù)字、文本、邏輯值等等。如果value為數(shù)字返回1,是文本返回2,邏輯值返回4,錯誤值返回16,數(shù)組返回64。實例:如果A1包含文本"金額",則公式”=TYPE(A1)”返回2。=TYPE(2+A1)返回16。(七)邏輯運算符1.AND用途:所有參數(shù)的邏輯值為真時返回TRUE(真);只要有一個參數(shù)的邏輯值為假,則返回FALSE(假)。語法:AND(logical1,logical2,…)。參數(shù):Logical1,logical2,…為待檢驗的1～30個邏輯表示式,它們的結(jié)論或為TRUE(真)或為FALSE(假)。參數(shù)必須是邏輯值或者包含邏輯值的數(shù)組或引用,如果數(shù)組或引用內(nèi)含有文字或空白單元格,則忽略它的值。如果指定的單元格區(qū)域內(nèi)包括非邏輯值,AND將返回錯誤值#VALUE!。實例:如果A1=2、A=6,那么公式”=AND(A1A2)”返回FALSE。如果B4=104,那么公式”=IF(AND(1<B4,B4<p>2.FALSE用途:返回邏輯值FALSE。語法:FALSE()參數(shù):該函數(shù)不需要參數(shù)實例:如果在A1單元格內(nèi)輸入公式”=FALSE()”,回車后即可返回FALSE。若在單元格或公式中輸入文字FALSE,Excel會自動將它解釋成邏輯值FALSE。3.IF用途:執(zhí)行邏輯判斷,它能夠根據(jù)邏輯表示式的真假,返回不同的結(jié)果,從而執(zhí)行數(shù)值或公式的條件檢測任務(wù)。語法:IF(logical_test,value_if_true,value_if_false)。參數(shù):Logical_test計算結(jié)果為TRUE或FALSE的任何數(shù)值或表示式;Value_if_true是Logical_test為TRUE時函數(shù)的返回值,如果logical_test為TRUE而且省略了value_if_true,則返回TRUE。而且Value_if_true能夠是一個表示式;Value_if_false是Logical_test為FALSE時函數(shù)的返回值。如果logical_test為FALSE而且省略value_if_false,則返回FALSE。Value_if_false也能夠是一個表示式。實例:公式”=IF(C2>=85,"A",IF(C2>=70,"B",IF(C2>=60,"C",IF(C2<60,"D"))))”,其中第二個IF語句同時也是第一個IF語句的參數(shù)。同樣,第三個IF語句是第二個IF語句的參數(shù),以此類推。例如,若第一個邏輯判斷表示式C2>=85成立,則D2單元格被賦值”A”;如果第一個邏輯判斷表示式C2>=85不成立,則計算第二個IF語句”IF(C2>=70”;以此類推直至計算結(jié)束,該函數(shù)廣泛用于需要進行邏輯判斷的場合。4.NOT用途:求出一個邏輯值或邏輯表示式的相反值。如果您要確保一個邏輯值等于其相反值,就應(yīng)該使用NOT函數(shù)。語法:NOT(logical)參數(shù):Logical是一個能夠得出TRUE或FALSE結(jié)論的邏輯值或邏輯表示式。如果邏輯值或表示式的結(jié)果為FALSE,則NOT函數(shù)返回TRUE;如果邏輯值或表示式的結(jié)果為TRUE,那么NOT函數(shù)返回的結(jié)果為FALSE。實例:如果A1=6、A2=8,那么公式”=NOT(A1<p>5.OR用途:所有參數(shù)中的任意一個邏輯值為真時即返回TRUE(真)。語法:OR(logical1,logical2,...)參數(shù):Logical1,logical2,...是需要進行檢驗的1至30個邏輯表示式,其結(jié)論分別為TRUE或FALSE。如果數(shù)組或引用的參數(shù)包含文本、數(shù)字或空白單元格,它們將被忽略。如果指定的區(qū)域中不包含邏輯值,OR函數(shù)將返回錯誤#VALUE!。實例:如果A1=6、A2=8,則公式”=OR(A1+A2>A2,A1=A2)”返回TRUE;而公式”=OR(A1>A2,A1=A2)”返回FALSE。6.TRUE用途:返回邏輯值TRUE。語法:TRUE()參數(shù):該函數(shù)不需要參數(shù)實例:如果在A1單元格內(nèi)輸入公式”=TRUE()”,回車后即可返回TRUE。若在單元格或公式中輸入文字TRUE,Excel會自動將它解釋成邏輯值TRUE。函數(shù)TRUE主要用于與其它電子表格程序兼容。(八)查找和引用函數(shù)1.ADDRESS用途:以文字形式返回對工作簿中某一單元格的引用。語法:ADDRESS(row_num,column_num,abs_num,a1,sheet_text)參數(shù):Row_num是單元格引用中使用的行號;Column_num是單元格引用中使用的列標(biāo);Abs_num指明返回的引用類型(1或省略為絕對引用,2絕對行號、相對列標(biāo),3相對行號、絕對列標(biāo),4是相對引用);A1是一個邏輯值,它用來指明是以A1或R1C1返回引用樣式。如果A1為TRUE或省略,函數(shù)ADDRESS返回A1樣式的引用;如果A1為FALSE,函數(shù)ADDRESS返回R1C1樣式的引用。Sheet_text為一文本,指明作為外部引用的工作表的名稱,如果省略sheet_text,則不使用任何工作表的名稱。實例:公式”=ADDRESS(1,4,4,1)”返回D1。2.AREAS用途:返回引用中包含的區(qū)域個數(shù)。語法:AREAS(reference)。參數(shù):Reference是對某一單元格或單元格區(qū)域的引用,也能夠引用多個區(qū)域。注意:如果需要將幾個引用指定為一個參數(shù),則必須用括號括起來,以免Excel將逗號作為參數(shù)間的分隔符。實例:公式”=AREAS(a2:b4)”返回1,=AREAS((A1:A3,A4:A6,B4:B7,A16:A18))返回4。3.CHOOSE用途:能夠根據(jù)給定的索引值,從多達29個待選參數(shù)中選出相應(yīng)的值或操作。語法:CHOOSE(index_num,value1,value2,...)。參數(shù):Index_num是用來指明待選參數(shù)序號的值,它必須是1到29之間的數(shù)字、或者是包含數(shù)字1到29的公式或單元格引用;Value1,value2,...為1到29個數(shù)值參數(shù),能夠是數(shù)字、單元格,已定義的名稱、公式、函數(shù)或文本。實例:公式”=CHOOSE(2,"電腦","愛好者")返回”愛好者”。公式”=SUM(A1:CHOOSE(3,A10,A20,A30))”與公式”=SUM(A1:A30)”等價(因為CHOOSE(3,A10,A20,A30)返回A30)。4.COLUMN用途:返回給定引用的列標(biāo)。語法:COLUMN(reference)。參數(shù):Reference為需要得到其列標(biāo)的單元格或單元格區(qū)域。如果省略reference,則假定函數(shù)COLUMN是對所在單元格的引用。如果reference為一個單元格區(qū)域,而且函數(shù)COLUMN作為水平數(shù)組輸入,則COLUMN函數(shù)將reference中的列標(biāo)以水平數(shù)組的形式返回。實例:公式”=COLUMN(A3)”返回1,=COLUMN(B3:C5)返回2。5.COLUMNS用途:返回數(shù)組或引用的列數(shù)。語法:COLUMNS(array)。參數(shù):Array為需要得到其列數(shù)的數(shù)組、數(shù)組公式或?qū)卧駞^(qū)域的引用。實例:公式”=COLUMNS(B1:C4)”返回2,=COLUMNS({5,4;4,5})返回2。6.HLOOKUP用途:在表格或數(shù)值數(shù)組的首行查找指定的數(shù)值,并由此返回表格或數(shù)組當(dāng)前列中指定行處的數(shù)值。語法:HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)參數(shù):Lookup_value是需要在數(shù)據(jù)表第一行中查找的數(shù)值,它能夠是數(shù)值、引用或文字串;Table_array是需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,能夠使用對區(qū)域或區(qū)域名稱的引用,Table_array的第一行的數(shù)值能夠是文本、數(shù)字或邏輯值。Row_index_num為table_array中待返回的匹配值的行序號。Range_lookup為一邏輯值,指明函數(shù)HLOOKUP查找時是精確匹配,還是近似匹配。實例:如果A1:B3區(qū)域存放的數(shù)據(jù)為34、23、68、69、92、36,則公式”=HLOOKUP(34,A1:B3,1,FALSE)返回34;=HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE)返回”c”。7.HYPERLINK用途:創(chuàng)立一個快捷方式,用以打開存儲在網(wǎng)絡(luò)服務(wù)器、Intranet(Internet)或本地硬盤的其它文件。語法:HYPERLINK(link_location,friendly_name)參數(shù):Link_location是文件的路徑和文件名,它還能夠指向文檔中的某個更為具體的位置,如Execl工作表或工作簿中特定的單元格或命名區(qū)域,或是指向Word文檔中的書簽。路徑能夠是存儲在硬盤驅(qū)動器上的文件,或是Internet或Intranet上的URL路徑;Friendly_name為單元格中顯示的鏈接文字或數(shù)字,它用藍色顯示并帶有下劃線。如果省略了Friendly_name,單元格就將link_location顯示為鏈接。實例:HYPERLINK("","驅(qū)動之家")會在工作表中顯示文本”驅(qū)動之家”,單擊它即可連接到””。公式”=HYPERLINK("D:\README.TXT","說明文件")”在工作表中建立一個的藍色”說明文件”鏈接,單擊它能夠打開D盤上的README.TXT文件。8.INDEX用途:返回表格或區(qū)域中的數(shù)值或?qū)?shù)值的引用。函數(shù)INDEX()有兩種形式:數(shù)組和引用。數(shù)組形式一般返回數(shù)值或數(shù)值數(shù)組;引用形式一般返回引用。語法:INDEX(array,row_num,column_num)返回數(shù)組中指定的單元格或單元格數(shù)組的數(shù)值。INDEX(reference,row_num,column_num,area_num)返回引用中指定單元格或單元格區(qū)域的引用。參數(shù):Array為單元格區(qū)域或數(shù)組常數(shù);Row_num為數(shù)組中某行的行序號,函數(shù)從該行返回數(shù)值。如果省略row_num,則必須有column_num;Column_num是數(shù)組中某列的列序號,函數(shù)從該列返回數(shù)值。如果省略column_num,則必須有row_num。Reference是對一個或多個單元格區(qū)域的引用,如果為引用輸入一個不連續(xù)的選定區(qū)域,必須用括號括起來。Area_num是選擇引用中的一個區(qū)域,并返回該區(qū)域中row_num和column_num的交叉區(qū)域。選中或輸入的第一個區(qū)域序號為1,第二個為2,以此類推。如果省略area_num,則INDEX函數(shù)使用區(qū)域1。實例:如果A1=68、A2=96、A3=90,則公式”=INDEX(A1:A3,1,1)”返回68,=INDEX(A1:A3,1,1,1)返回68。9.INDIRECT用途:返回由文字串指定的引用。此函數(shù)立即對引用進行計算,并顯示其內(nèi)容。當(dāng)需要更改公式中單元格的引用,而不更改公式本身,即可使用INDIRECT函數(shù)。語法:INDIRECT(ref_text,a1)。參數(shù):Ref_text是對單元格的引用,此單元格能夠包含A1樣式的引用、R1C1樣式的引用、定義為引用的名稱或?qū)ξ淖执畣卧竦囊?A1為一邏輯值,指明包含在單元格ref_text中的引用的類型。如果a1為TRUE或省略,ref_text被解釋為A1-樣式的引用。如果a1為FALSE,ref_text被解釋為R1C1-樣式的引用。實例:如果單元格A1存放有文本B1,而B1單元格中存放了數(shù)值68.75,則公式”=INDIRECT($A$1)”返回68.75。10.LOOKUP用途:返回向量(單行區(qū)域或單列區(qū)域)或數(shù)組中的數(shù)值。該函數(shù)有兩種語法形式:向量和數(shù)組,其向量形式是在單行區(qū)域或單列區(qū)域(向量)中查找數(shù)值,然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的數(shù)值;其數(shù)組形式在數(shù)組的第一行或第一列查找指定的數(shù)值,然后返回數(shù)組的最后一行或最后一列中相同位置的數(shù)值。語法1(向量形式):LOOKUP(lookup_value,lookup_vector,result_vector)語法2(數(shù)組形式):LOOKUP(lookup_value,array)。參數(shù)1(向量形式):Lookup_value為函數(shù)LOOKUP在第一個向量中所要查找的數(shù)值。Lookup_value能夠為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。Lookup_vector為只包含一行或一列的區(qū)域。Lookup_vector的數(shù)值能夠為文本、數(shù)字或邏輯值。參數(shù)2(數(shù)組形式):Lookup_value為函數(shù)LOOKUP在數(shù)組中所要查找的數(shù)值。Lookup_value能夠為數(shù)字、文本、邏輯值或包含數(shù)值的名稱或引用。如果函數(shù)LOOKUP找不到lookup_value,則使用數(shù)組中小于或等于lookup_value的最大數(shù)值。Array為包含文本、數(shù)字或邏輯值的單元格區(qū)域,它的值用于與lookup_value進行比較。注意:Lookup_vector的數(shù)值必須按升序排列,否則LOOKUP函數(shù)不能返回正確的結(jié)果,參數(shù)中的文本不區(qū)分大小寫。實例:如果A1=68、A2=76、A3=85、A4=90,則公式”=LOOKUP(76,A1:A4)”返回2,=LOOKUP("bump",{"a",1;"b",2;"c",3})返回2。11.MATCH用途:返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置。如果需要找出匹配元素的位置而不是匹配元素本身,則應(yīng)該使用MATCH函數(shù)。語法:MATCH(lookup_value,lookup_array,match_type)。參數(shù):Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值,它能夠是數(shù)值(或數(shù)字、文本或邏輯值)、對數(shù)字、文本或邏輯值的單元格引用。Lookup_array是可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域,Lookup_array能夠是數(shù)組或數(shù)組引用;Match_type為數(shù)字-1、0或1,它說明Excel如何在lookup_array中查找lookup_value。如果match_type為1,函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值。如果match_type為0,函數(shù)MATCH查找等于lookup_value的第一個數(shù)值。如果match_type為-1,函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值。注意:MATCH函數(shù)返回lookup_array中目標(biāo)值的位置,而不是數(shù)值本身。如果match_type為0且lookup_value為文本,lookup_value能夠包含通配符(”*”和”?”)。星號能夠匹配任何字符序列,問號能夠匹配單個字符。實例:如果A1=68、A2=76、A3=85、A4=90,則公式”=MATCH(90,A1:A5,0)”返回3。12.OFFSET用途:以指定的引用為參照系,經(jīng)過給定偏移量得到新的引用。返回的引用能夠是一個單元格或單元格區(qū)域,并能夠指定返回的行數(shù)或列數(shù)。語法:OFFSET(reference,rows,cols,height,width)。參數(shù):Reference是作為偏移量參照系的引用區(qū)域,它必須是單元格或相連單元格區(qū)域的引用;Rows是相對于偏移量參照系的左上角單元格,上(下)偏移的行數(shù)。如果使用5作為參數(shù)Rows,則說明目標(biāo)引用區(qū)域的左上角單元格比reference低5行。行數(shù)可為正數(shù)(代表在起始引用的下方)或負(fù)數(shù)(代表在起始引用的上方);Cols是相對于偏移量參照系的左上角單元格,左(右)偏移的列數(shù)。如果使用5作為參數(shù)Cols,則說明目標(biāo)引用區(qū)域的左上角的單元格比reference靠右5列。列數(shù)可為正數(shù)(代表在起始引用的右邊)或負(fù)數(shù)(代表在起始引用的左邊);Height是要返回的引用區(qū)域的行數(shù),Height必須為正數(shù);Width是要返回的引用區(qū)域的列數(shù),Width必須為正數(shù)。實例:如果A1=68、A2=76、A3=85、A4=90,則公式”=SUM(OFFSET(A1:A2,2,0,2,1))”返回177。13.ROW用途:返回給定引用的行號。語法:ROW(reference)。Reference為需要得到其行號的單元格或單元格區(qū)域。實例:公式”=ROW(A6)”返回6,如果在C5單元格中輸入公式”=ROW()”,其計算結(jié)果為5。14.ROWS用途:返回引用或數(shù)組的行數(shù)。語法:ROWS(array)。參數(shù):Array是需要得到其行數(shù)的數(shù)組、數(shù)組公式或?qū)卧駞^(qū)域的引用。實例:公式”=ROWS(A1:A9)”返回9,=ROWS({1,2,3;4,5,6;1,2,3})返回3。15.RTD用途:從支持COM自動化的程序中返回實時數(shù)據(jù)。語法:RTD(ProgID,server,topic1,[topic2],...)參數(shù):ProgID已安裝在本地計算機中,經(jīng)過注冊的COM自動化加載宏的ProgID名稱,該名稱用引號引起來。Server是運行加載宏的服務(wù)器的名稱。如果沒有服務(wù)器,程序是在本地計算機上運行,那么該參數(shù)為空白。topic1,topic2,...為1到28個參數(shù),這些參數(shù)放在一起代表一個唯一的實時數(shù)據(jù)。16.TRANSPOSE用途:返回區(qū)域的轉(zhuǎn)置(所謂轉(zhuǎn)置就是將數(shù)組的第一行作為新數(shù)組的第一列,數(shù)組的第二行作為新數(shù)組的第二列,以此類推)。語法:TRANSPOSE(array)。參數(shù):Array是需要轉(zhuǎn)置的數(shù)組或工作表中的單元格區(qū)域。實例:如果A1=68、A2=76、B1=85、B2=90,那么公式”{=TRANSPOSE(A1:B1)}”返回C1=56、D1=98、C2=90、D2=87。17.VLOOKUP用途:在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值,并由此返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。當(dāng)比較值位于數(shù)據(jù)表首列時,能夠使用函數(shù)VLOOKUP代替函數(shù)HLOOKUP。語法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)參數(shù):Lookup_value為需要在數(shù)據(jù)表第一列中查找的數(shù)值,它能夠是數(shù)值、引用或文字串。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,能夠使用對區(qū)域或區(qū)域名稱的引用。Col_index_num為table_array中待返回的匹配值的列序號。Col_index_num為1時,返回table_array第一列中的數(shù)值;col_index_num為2,返回table_array第二列中的數(shù)值,以此類推。Range_lookup為一邏輯值,指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略,則返回近似匹配值,也就是說,如果找不到精確匹配值,則返回小于lookup_value的最大數(shù)值;如果range_value為FALSE,函數(shù)VLOOKUP將返回精確匹配值。如果找不到,則返回錯誤值#N/A。實例:如果A1=23、A2=45、A3=50、A4=65,則公式”=VLOOKUP(50,A1:A4,1,TRUE)”返回50。13.COUNTIF用途:統(tǒng)計某一區(qū)域中符合條件的單元格數(shù)目。語法:COUNTIF(range,criteria)參數(shù):range為需要統(tǒng)計的符合條件的單元格數(shù)目的區(qū)域;Criteria為參與計算的單元格條件,其形式能夠為數(shù)字、表示式或文本(如36、">160"和"男"等)。其中數(shù)字能夠直接寫入,表示式和文本必須加引號。實例:假設(shè)A1:A5區(qū)域內(nèi)存放的文本分別為女、男、女、男、女,則公式”=COUNTIF(A1:A5,"女")”返回3。(十)統(tǒng)計函數(shù)1.AVEDEV用途:返回一組數(shù)據(jù)與其平均值的絕對偏差的平均值,該函數(shù)能夠評測數(shù)據(jù)(例如學(xué)生的某科考試成績)的離散度。語法:AVEDEV(number1,number2,...)參數(shù):Number1、number2、...是用來計算絕對偏差平均值的一組參數(shù),其個數(shù)能夠在1～30個之間。實例:如果A1=79、A2=62、A3=45、A4=90、A5=25,則公式”=AVEDEV(A1:A5)”返回20.16。2.AVERAGE用途:計算所有參數(shù)的算術(shù)平均值。語法:AVERAGE(number1,number2,...)。參數(shù):Number1、number2、...是要計算平均值的1～30個參數(shù)。實例:如果A1:A5區(qū)域命名為分?jǐn)?shù),其中的數(shù)值分別為100、70、92、47和82,則公式”=AVERAGE(分?jǐn)?shù))”返回78.2。3.AVERAGEA用途:計算參數(shù)清單中數(shù)值的平均值。它與AVERAGE函數(shù)的區(qū)別在于不但數(shù)字,而且文本和邏輯值(如TRUE和FALSE)也參與計算。語法:AVERAGEA(value1,value2,...)參數(shù):Value1、value2、...為需要計算平均值的1至30個單元格、單元格區(qū)域或數(shù)值。實例:如果A1=76、A2=85、A3=TRUE,則公式”=AVERAGEA(A1:A3)”返回54(即76+85+1/3=54)。12.COUNT用途:返回數(shù)字參數(shù)的個數(shù)。它能夠統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。語法:COUNT(value1,value2,...)。參數(shù):Value1,value2,...是包含或引用各種類型數(shù)據(jù)的參數(shù)(1～30個),其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。實例:如果A1=90、A2=人數(shù)、A3=〞〞、A4=54、A5=36,則公式”=COUNT(A1:A5)”返回3。13.COUNTA用途:返回參數(shù)組中非空值的數(shù)目。利用函數(shù)COUNTA能夠計算數(shù)組或單元格區(qū)域中數(shù)據(jù)項的個數(shù)。語法:COUNTA(value1,value2,...)說明:Value1,value2,...所要計數(shù)的值,參數(shù)個數(shù)為1～30個。在這種情況下的參數(shù)能夠是任何類型,它們包括空格但不包括空白單元格。如果參數(shù)是數(shù)組或單元格引用,則數(shù)組或引用中的空白單元格將被忽略。如果不需要統(tǒng)計邏輯值、文字或錯誤值,則應(yīng)該使用COUNT函數(shù)。實例:如果A1=6.28、A2=3.74,其余單元格為空,則公式”=COUNTA(A1:A7)”的計算結(jié)果等于2。14.COUNTBLANK用途:計算某個單元格區(qū)域中空白單元格的數(shù)目。語法:COUNTBLANK(range)參數(shù):Range為需要計算其中空白單元格數(shù)目的區(qū)域。實例:如果A1=88、A2=55、A3=""、A4=72、A5="",則公式”=COUNTBLANK(A1:A5)”返回2。15.COUNTIF用途:計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法:COUNTIF(range,criteria)參數(shù):Range為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計算在內(nèi)的條件,其形式能夠為數(shù)字、表示式或文本。36.LARGE用途:返回某一數(shù)據(jù)集中的某個最大值。能夠使用LARGE函數(shù)查詢考試分?jǐn)?shù)集中第一、第二、第三等的得分。語法:LARGE(array,k)參數(shù):Array為需要從中查詢第k個最大值的數(shù)組或數(shù)據(jù)區(qū)域,K為返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域里的位置(即名次)。實例:如果B1=59、B2=70、B3=80、B4=90、B5=89、B6=84、B7=92,,則公式”=LARGE(B1,B7,2)”返回90。41.MAX用途:返回數(shù)據(jù)集中的最大數(shù)值。語法:MAX(number1,number2,...)參數(shù):Number1,number2,...是需要找出最大數(shù)值的1至30個數(shù)值。實例:如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96,則公式”=MAX(A1:A7)”返回96。42.MAXA用途:返回數(shù)據(jù)集中的最大數(shù)值。它與MAX的區(qū)別在于文本值和邏輯值(如TRUE和FALSE)作為數(shù)字參與計算。語法:MAXA(value1,value2,...)參數(shù):Value1,Value2,...為需要從中查找最大數(shù)值的1到30個參數(shù)。實例:如果A1:A5包含0、0.2、0.5、0.4和TRUE,則:MAXA(A1:A5)返回1。43.MEDIAN用途:返回給定數(shù)值集合的中位數(shù)(它是在一組數(shù)據(jù)中居于中間的數(shù)。換句話說,在這組數(shù)據(jù)中,有一半的數(shù)據(jù)比它大,有一半的數(shù)據(jù)比它小)。語法:MEDIAN(number1,number2,...)參數(shù):Number1,number2,...是需要找出中位數(shù)的1到30個數(shù)字參數(shù)。實例:MEDIAN(11,12,13,14,15)返回13;MEDIAN(1,2,3,4,5,6)返回3.5,即3與4的平均值。44.MIN用途:返回給定參數(shù)表中的最小值。語法:MIN(number1,number2,...)。參數(shù):Number1,number2,...是要從中找出最小值的1到30個數(shù)字參數(shù)。實例:如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=96,則公式”=MIN(A1:A7)”返回49;而=MIN(A1:A5,0,-8)返回-8。45.MINA用途:返回參數(shù)清單中的最小數(shù)值。它與MIN函數(shù)的區(qū)別在于文本值和邏輯值(如TRUE和FALSE)也作為數(shù)字參與計算。語法:MINA(value1,value2,...)參數(shù):Value1,value2,...為需要從中查找最小數(shù)值的1到30個參數(shù)。實例:如果A1=71、A2=83、A3=76、A4=49、A5=92、A6=88、A7=FALSE,則公式”=MINA(A1:A7)”返回0。59.RANK用途:返回一個數(shù)值在一組數(shù)值中的排位(如果數(shù)據(jù)清單已經(jīng)排過序了,則數(shù)值的排位就是它當(dāng)前的位置)。語法:RANK(number,ref,order)參數(shù):Number是需要計算其排位的一個數(shù)字;Ref是包含一組數(shù)字的數(shù)組或引用(其中的非數(shù)值型參數(shù)將被忽略);Order為一數(shù)字,指明排位的方式。如果order為0或省略,則按降序排列的數(shù)據(jù)清單進行排位。如果order不為零,ref當(dāng)作按升序排列的數(shù)據(jù)清單進行排位。注意:函數(shù)RANK對重復(fù)數(shù)值的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。如在一列整數(shù)中,若整數(shù)60出現(xiàn)兩次,其排位為5,則61的排位為7(沒有排位為6的數(shù)值)。實例:如果A1=78、A2=45、A3=90、A4=12、A5=85,則公式”=RANK(A1,$A$1:$A$5)”返回5、8、2、10、4。63.SMALL用途:返回數(shù)據(jù)集中第k個最小值,從而得到數(shù)據(jù)集中特定位置上的數(shù)值。語法:SMALL(array,k)參數(shù):Array是需要找到第k個最小值的數(shù)組或數(shù)字型數(shù)據(jù)區(qū)域,K為返回的數(shù)據(jù)在數(shù)組或數(shù)據(jù)區(qū)域里的位置(從小到大)。實例:如果如果A1=78、A2=45、A3=90、A4=12、A5=85,則公式”=SMALL(A1:A5,3)”返回78。(十一)文本和數(shù)據(jù)函數(shù)1.ASC用途:將字符串中的全角(雙字節(jié))英文字母更改為半角(單字節(jié))字符。語法:ASC(text)參數(shù):Text為文本或包含文本的單元格引用。如果文本中不包含任何全角英文字母,則文本不會被更改。實例:如果A1=ｅｘｃｅｌ,則公式”=ASC(A1)”返回excel。2.CHAR用途:返回對應(yīng)于數(shù)字代碼的字符,該函數(shù)可將其它類型的電腦文件中的代碼轉(zhuǎn)換為字符(操作環(huán)境為MacintoshMacintosh字符集和WindowsANSI字符集)。語法:CHAR(number)。參數(shù):Number是用于轉(zhuǎn)換的字符代碼,介于1～255之間(使用當(dāng)前計算機字符集中的字符)。實例:公式”=CHAR(56)”返回8,=CHAR(36)返回$。3.CLEAN用途:刪除文本中不能打印的字符。對從其它應(yīng)用程序中輸入的字符串使用CLEAN函數(shù),將刪除其中含有的當(dāng)前操作系統(tǒng)無法打印的字符。語法:CLEAN(text)。參數(shù):Text為要從中刪除不能打印字符的任何字符串。實例:由于CHAR(7)返回一個不能打印的字符,因此公式”=CLEAN(CHAR(7)&"text"&CHAR(7))”返回text。4.CODE用途:返回文字串中第一個字符的數(shù)字代碼(