Excel函數(shù)應(yīng)用教程 (2).doc

Excel函數(shù)應(yīng)用教程：信息函數(shù)信息函數(shù)主要用于顯示Excel內(nèi)部的一些提示信息，如數(shù)據(jù)錯誤信息、操作環(huán)境參數(shù)、數(shù)據(jù)類型等。共有9條。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是需要進(jìn)行檢驗的參數(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，F(xiàn)ALSE返回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。Excel函數(shù) 快速完成學(xué)生成績等級評定2007-11-26 PC 類型: 轉(zhuǎn)載 來源: 天極網(wǎng) 作者: 王道才 責(zé)編: 潘翔城 Excel函數(shù)可以幫助各位老師用字母A、B、C給學(xué)生打成績。我們定的標(biāo)準(zhǔn)是80分以上為A，60分至79分為B，60分以下為C。下面我們看如何實現(xiàn)這一效果。一、利用公式計算等級：在Excel中把成績錄入完成，然后在表頭行的最后邊再添加上一個“等級”列。在F2單元格中輸入：=CONCATENATE(IF (C2=80,A,IF(C2=60,B,C),IF(D2=80,A,IF(D2=60,B,C), IF(E2=80,A,IF(E2=60,B,C)，然后把鼠標(biāo)指針指向F2單元格的右下角，等鼠標(biāo)指針變成黑色十字加號時，按住左鍵向右拖動到這列單元格的最后放手。(如圖1)也可以在F2單元格中輸入：=IF(C2=80,A,IF(C2=60,B,C)&IF(D2 =80,A,IF(D2=60,B,C)&IF(E2=80,A,IF(E2=60,B,C)，然后把鼠標(biāo)指針指向F2單元格的右下角，等鼠標(biāo)指針變成黑色十字加號時，按住左鍵向右拖動到這列單元格的最后放手。(如圖2和圖3)二、函數(shù)和公式說明：上面的公式中我們主要是利用IF條件函數(shù)的嵌套。IF函數(shù)是根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容的條件函數(shù)。利用此函數(shù)邏輯計算出真假值，會返回不同的結(jié)果，從而可達(dá)到篩選數(shù)據(jù)的目的。其語法格式是：IF(logical_test,value_if_true, value_if_false)， logical_test：表示計算結(jié)果為TRUE或者FALSE的任意值或表達(dá)式，該參數(shù)可使用任何比較運算符;value_if_true：表示 logical_test為TRUE(也就是真或正確)時返回的值;value_if_false：表示logical_test為FALSE(也就是假或錯誤)時返回的值。而本例中的公式IF(C2=80,A,IF(C2=60,B,C)意思是如果C2單元格中的語文分?jǐn)?shù)大于或等于 80分，則在F2單元格中顯示字母A，而如果小于80分則再重新判斷是否大于等于60分，如果大于或等于60分則在F2單元格中顯示字母B，而如果小于 60分則顯示字母C。CONCATENATE是將若干個文字項合并至一個文字項中的函數(shù)。其語法格式是：CONCATENATE(text1,text2)。而 &是將多個單元格字符串連接成一個字符串的函數(shù)。其語法格式是：numb1&numb2&。這里text1和numb1是指 IF(C2=80,A,IF(C2=60,B,C)，即語文這列成績的等級;text2和numb2是指IF (D2=80,A,IF(D2=60,B,C)，即數(shù)學(xué)這列成績的等級;text3和numb3是指IF(E2= 80,A,IF(E2=60,B,C)，即英語這列成績的等級。前兩天一位班主任復(fù)制給我兩個EXCEL表，分別是他所帶班級的語文及數(shù)學(xué)成績各在前30名的學(xué)生（如圖1），需要把兩個表合二為一，同時還要顯示出語文及數(shù)學(xué)成績均在前30名的學(xué)生，這可是個難題！經(jīng)過我的冥思苦想和不斷嘗試，終于找到了解決之道，那就是使用IF函數(shù)，讓數(shù)據(jù)各就各位。下面我們詳細(xì)講講如何使用IF函數(shù)解決這個問題。文章末尾提供.xls文件供大家下載參考。操作步驟：第一步、合二為一第二步、表格排序第三步、用IF函數(shù)，讓成績各就各位第四步、表格篩選第五步、刪除重復(fù)的數(shù)據(jù) （圖片較大，請拉動滾動條觀看）第一步、合二為一分別打開“語文”及“數(shù)學(xué)”這兩個EXCEL表格，大家從圖1中可以看出，兩個表格的列數(shù)是一樣的，A列是名次，B列是學(xué)生編號，C列是姓名，D列是成績。如果直接將“數(shù)學(xué)”表格中的數(shù)據(jù)復(fù)制到“語文”表格中，因為D列同是成績，那么就無法區(qū)分出語文及數(shù)學(xué)成績了，所以在復(fù)制“數(shù)學(xué)”表格的數(shù)據(jù)前，在“數(shù)學(xué)”表格的D列頂端單擊鼠標(biāo)左鍵，選中整列，然后單擊鼠標(biāo)右鍵，選擇其中的“插入”，在D列前插入一列。這樣，“數(shù)學(xué)”表格中的成績就由D列變?yōu)镋列了。接下來從表格的第2行起，拖動鼠標(biāo)選到第31行，然后在選中的列表上點右鍵，選擇“復(fù)制”，再將表格窗口切換到“語文”表格窗口中，在A列下的空白位置，第32行的第一個格上單擊鼠標(biāo)右鍵，選擇菜單中的“粘貼”，這樣就把兩個表格的數(shù)據(jù)合二為一了，如圖2。巧用IF函數(shù)合并Excel數(shù)據(jù)表第二步、表格排序從圖1中可以看出，表格的A列是名次，存有并列重復(fù)現(xiàn)象，C列是學(xué)生名稱，也有可能存在重名，而B列的學(xué)生編號是唯一的，不會發(fā)生一個學(xué)生多個編號的現(xiàn)象。所以表格的排序就以B列(學(xué)生編號)為關(guān)鍵字。選中B列，點擊EXCEL菜單中“數(shù)據(jù)”下的“排序”，在彈出的“排序警告”窗口中，按其默認(rèn)設(shè)置“擴展選定區(qū)域”，點“排序”按鍵打開“排序”窗口，如圖3。注意：在“主要關(guān)鍵字”中是否選擇的是B列（學(xué)生編號），如果不是，可手動選擇，排列順序按默認(rèn)的“遞增”，因為這個表格有標(biāo)題行，所以在“當(dāng)前數(shù)據(jù)清單”項中，選擇“有標(biāo)題行”這一項。設(shè)置完成后，點“確定”就完成了排序工作，如圖4，可以看出，編號從小到大遞增排列，編號相同的也是依次排列。 第三步：用IF函數(shù)，讓成績各就各位先來熟悉一下IF函數(shù)的語法結(jié)構(gòu)：“IF(條件，成立的結(jié)果，不成立的結(jié)果）”。意思是，對函數(shù)中的“條件”進(jìn)行判斷，如果“條件”成立，也就為TRUE，那就輸出“成立的結(jié)果”；如果“條件”不成立，也就是為FALSE，則輸出“不成立的結(jié)果”。在“條件”中，還可以使用“、=、=”等關(guān)系運算符作為“條件”的表達(dá)式，然后按照運算的結(jié)果不同，輸出不同的結(jié)果！好，先簡要說這么多，下面切換到我們的主題中！在F列的第2單元格中輸入下面引號內(nèi)的公式“=IF(B2=B3，E3，IF(E2=，E2)”，如圖5。下面我把這段公式的執(zhí)行過程說一遍。首先根據(jù)“條件（B2B3）”（單元格B2與B3中的編號）判斷是否相等，如果相等，條件為TRUE，就輸出“成立的結(jié)果（E3）”（在F2單元格內(nèi)顯示E3單元格的內(nèi)容）；如果不相等，條件為FALSE，那就執(zhí)行“不成立的結(jié)果”（嵌套IF中的命令），因為表格只是部分學(xué)生的成績，所以還要再加上一個嵌套IF過程，在這個過程中，先判斷E2單元格是否為空，如果為空，說明這一單元格是“語文”表格的單元格，就在F2單元格內(nèi)顯示一個空值；而如果E2單元格不為空時，則說明該行的這一單元格是“數(shù)學(xué)”表格中的成績，則在F2單元格中輸出E2單元格的內(nèi)容。公式輸完后，按回車鍵結(jié)束，然后選中F2單元格，移動鼠標(biāo)至單元格右下方的“+”（填充柄）上，按下左鍵拖動“填充柄”向下復(fù)制公式至表格底部?，F(xiàn)在再來觀察一下表格，如圖6，是不是已把編號相同的數(shù)學(xué)成績和語文成績排在一行了，對于沒有重復(fù)編號的，該公式也復(fù)制了E列中的數(shù)學(xué)成績到F列中。從圖6中可以看出，表格中存有兩行數(shù)學(xué)成績，如何把F列的數(shù)據(jù)轉(zhuǎn)移到E列中呢？首先選中并復(fù)制F列，然后在E列上單擊右鍵，注意這次在菜單中選擇的是“選擇性粘貼”，打開“選擇性粘貼”窗口，在“粘貼”項中選擇“數(shù)值”，如圖7，點“確定”鍵便完成數(shù)據(jù)轉(zhuǎn)移，當(dāng)然，不要忘了刪除F列。第四步、表格篩選下面就可以在表格中篩選語文及數(shù)學(xué)成績均在前30名的學(xué)生了！這里還是要用IF函數(shù)，對重復(fù)編號的作一個標(biāo)記。先來分析一下如何寫IF函數(shù)，編號重復(fù)，則說明該編號的學(xué)生語文及數(shù)學(xué)均在前30名，所以才會出現(xiàn)重復(fù)編號。明白了這個道理，就可以寫IF語句了，在F列的第2單元格內(nèi)輸入公式“=IF(B2=B3，1，)”，如圖8。這段公式的意思是：如果單元格B2（學(xué)生編號）與B3相同，那就在F2單元格內(nèi)顯示“1”，如果B2與B3單元格的內(nèi)容不同，則在F2單元格內(nèi)顯示空。這里所輸出的“1”只是用來作篩選的標(biāo)記，也可以把它寫為“2”或者“a”等之類的字符。然后再拖動單元格的“填充柄”至復(fù)制公式至表格底部，這樣，凡是有重復(fù)編號的，會在第一個編號行的F列中對應(yīng)的單元格內(nèi)顯示“1”。其實也可以通過判斷某一列內(nèi)語文與數(shù)學(xué)成績是否同時存在作為條件，來作標(biāo)記，相應(yīng)的IF函數(shù)語句是：“=IF(AND(D2，E2)，1，)”，這里加入了AND函數(shù)來做判斷，有興趣的朋友可自已測試一下。羅哩羅嗦說了這么多，終于該說篩選了，選中F列，點擊菜單中的“篩選”“自動篩選”，然后再點F頂端的篩選箭頭，選擇其中的“1”或“非空白”均可，如圖9。圖10顯示的就是篩選出的語文及數(shù)學(xué)成績均在前30名的學(xué)生，呵呵！還真不少呢！第五步、刪除重復(fù)的數(shù)據(jù)既然已完成了篩選，為什么又出現(xiàn)了第五步？俗語說：“幫人幫到底”，表格中還有重復(fù)的數(shù)據(jù)沒有清除，怎么能算完成呢？點擊菜單中的“篩選”“自動篩選”，去掉“自動篩選”前的對勾，顯示全部的數(shù)據(jù)。在這里我們要刪除重復(fù)的編號，而這些編號也有一個規(guī)律，第一個編號所在行含有語文及數(shù)學(xué)成績，而第二個編號所在行只有數(shù)學(xué)成績，所以只要給第二個編號所在行打上標(biāo)記就可以了，同樣還是使用IF函數(shù)，在G列的第二個單元格內(nèi)輸入公式：“=IF(B1=B2，1，)”，如圖11，注意和上面的那個作標(biāo)記的有一點不同，這個是把標(biāo)記作在只有數(shù)學(xué)成績的重復(fù)編號行中。再拖動G2的“填充柄”復(fù)制公式到表格底部，然后使用“篩選”菜單中的“自動篩選”，篩選出標(biāo)記為“1”的行，這些顯示的就是數(shù)據(jù)重復(fù)行，接著選中它們，在右鍵菜單中點“刪除行”，如圖12，就從表格中清除了這些重復(fù)的數(shù)據(jù)。OK，任務(wù)完成了，有沒有感覺出IF函數(shù)的神奇，在EXCEL中，不僅是IF函數(shù)，還有其他的函數(shù)，都有很強大的功能，相信有興趣的朋友一定能研究出更多、更好的使用技巧！ 1.AVEDEV用途：返回一組數(shù)據(jù)與其平均值的絕對偏差的平均值，該函數(shù)可以評測數(shù)據(jù)(例如學(xué)生的某科考試成績)的離散度。語法：AVEDEV(number1，number2，.)參數(shù)：Number1、number2、.是用來計算絕對偏差平均值的一組參數(shù)，其個數(shù)可以在130個之間。實例：如果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、.是要計算平均值的130個參數(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)。4.BETADIST用途：返回Beta分布累積函數(shù)的函數(shù)值。Beta分布累積函數(shù)通常用于研究樣本集合中某些事物的發(fā)生和變化情況。例如，人們一天中看電視的時間比率。語法：BETADIST(x，alpha，beta，A，B)參數(shù)：X用來進(jìn)行函數(shù)計算的值，須居于可選性上下界(A和B)之間。Alpha分布的參數(shù)。Beta分布的參數(shù)。A是數(shù)值x所屬區(qū)間的可選下界，B是數(shù)值x所屬區(qū)間的可選上界。實例：公式“=BETADIST(2，8，10，1，3)”返回0.685470581。5.BETAINV用途：返回beta分布累積函數(shù)的逆函數(shù)值。即，如果probability=BETADIST(x，.)，則BETAINV(probability，.)=x。beta分布累積函數(shù)可用于項目設(shè)計，在給出期望的完成時間和變化參數(shù)后，模擬可能的完成時間。語法：BETAINV(probability，alpha，beta，A，B)參數(shù)：Probability為Beta分布的概率值，Alpha分布的參數(shù)，Beta分布的參數(shù)，A數(shù)值x所屬區(qū)間的可選下界，B數(shù)值x所屬區(qū)間的可選上界。實例：公式“=BETAINV(0.685470581，8，10，1，3)”返回2。6.BINOMDIST用途：返回一元二項式分布的概率值。BINOMDIST函數(shù)適用于固定次數(shù)的獨立實驗，實驗的結(jié)果只包含成功或失敗二種情況，且成功的概率在實驗期間固定不變。例如，它可以計算擲10次硬幣時正面朝上6次的概率。語法：BINOMDIST(number_s，trials，probability_s，cumulative)參數(shù)：Number_s為實驗成功的次數(shù)，Trials為獨立實驗的次數(shù)，Probability_s為一次實驗中成功的概率，Cumulative是一個邏輯值，用于確定函數(shù)的形式。如果cumulative為TRUE，則BINOMDIST函數(shù)返回累積分布函數(shù)，即至多number_s次成功的概率;如果為FALSE，返回概率密度函數(shù)，即number_s次成功的概率。實例：拋硬幣的結(jié)果不是正面就是反面，第一次拋硬幣為正面的概率是0.5。則擲硬幣10次中6次的計算公式為“=BINOMDIST(6，10，0.5，F(xiàn)ALSE)”，計算的結(jié)果等于0.2050787.CHIDIST用途：返回c2分布的單尾概率。c2分布與c2檢驗相關(guān)。使用c2檢驗可以比較觀察值和期望值。例如，某項遺傳學(xué)實驗假設(shè)下一代植物將呈現(xiàn)出某一組顏色。使用此函數(shù)比較觀測結(jié)果和期望值，可以確定初始假設(shè)是否有效。語法：CHIDIST(x，degrees_freedom)參數(shù)：X是用來計算c2分布單尾概率的數(shù)值，Degrees_freedom是自由度。實例：公式“=CHIDIST(1，2)”的計算結(jié)果等于0.606530663。8.CHIINV用途：返回c2分布單尾概率的逆函數(shù)。如果probability=CHIDIST(x，?)，則CHIINV(probability，?)=x。使用此函數(shù)比較觀測結(jié)果和期望值，可以確定初始假設(shè)是否有效。語法：CHIINV(probability，degrees_freedom)參數(shù)：Probability為c2分布的單尾概率，Degrees_freedom為自由度。實例：公式“=CHIINV(0.5，2)”返回1.386293564。9.CHITEST用途：返回相關(guān)性檢驗值，即返回c2分布的統(tǒng)計值和相應(yīng)的自由度，可使用c2檢驗確定假設(shè)值是否被實驗所證實。語法：CHITEST(actual_range，expected_range)參數(shù)：Actual_range是包含觀察值的數(shù)據(jù)區(qū)域，Expected_range是包含行列匯總的乘積與總計值之比的數(shù)據(jù)區(qū)域。實例：如果A1=1、A2=2、A3=3、B1=4、B2=5、B3=6，則公式“=CHITEST(A1:A3，B1:B3)”返回0.062349477。10.CONFIDENCE用途：返回總體平均值的置信區(qū)間，它是樣本平均值任意一側(cè)的區(qū)域。例如，某班學(xué)生參加考試，依照給定的置信度，可以確定該次考試的最低和最高分?jǐn)?shù)。語法：CONFIDENCE(alpha，standard_dev，size)。參數(shù)：Alpha是用于計算置信度(它等于100*(1-alpha)%，如果alpha為0.05，則置信度為95%)的顯著水平參數(shù)，Standard_dev是數(shù)據(jù)區(qū)域的總體標(biāo)準(zhǔn)偏差，Size為樣本容量。實例：假設(shè)樣本取自46名學(xué)生的考試成績，他們的平均分為60，總體標(biāo)準(zhǔn)偏差為5分，則平均分在下列區(qū)域內(nèi)的置信度為95%。公式“=CONFIDENCE(0.05，5，46)”返回1.44，即考試成績?yōu)?01.44分。11.CORREL用途：返回單元格區(qū)域array1和array2之間的相關(guān)系數(shù)。它可以確定兩個不同事物之間的關(guān)系，例如檢測學(xué)生的物理與數(shù)學(xué)學(xué)習(xí)成績之間是否關(guān)聯(lián)。語法：CORREL(array1，array2)參數(shù)：Array1第一組數(shù)值單元格區(qū)域。Array2第二組數(shù)值單元格區(qū)域。實例：如果A1=90、A2=86、A3=65、A4=54、A5=36、B1=89、B2=83、B3=60、B4=50、B5=32，則公式“=CORREL(A1:A5，B1:B5)”返回0.998876229，可以看出A、B兩列數(shù)據(jù)具有很高的相關(guān)性。12.COUNT用途：返回數(shù)字參數(shù)的個數(shù)。它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。語法：COUNT(value1，value2，.)。參數(shù)：value1，value2，.是包含或引用各種類型數(shù)據(jù)的參數(shù)(130個)，其中只有數(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ù)為130個。在這種情況下的參數(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ù)字、表達(dá)式或文本。16.COVAR用途：返回協(xié)方差，即每對數(shù)據(jù)點的偏差乘積的平均數(shù)。利用協(xié)方差可以研究兩個數(shù)據(jù)集合之間的關(guān)系。語法：COVAR(array1，array2)參數(shù)：Array1是第一個所含數(shù)據(jù)為整數(shù)的單元格區(qū)域，Array2是第二個所含數(shù)據(jù)為整數(shù)的單元格區(qū)域。實例：如果A1=3、A2=2、A3=1、B1=3600、B2=1500、B3=800，則公式“=COVAR(A1:A3，B1:B3)”返回933.3333333。17.CRITBINOM用途：返回使累積二項式分布大于等于臨界值的最小值，其結(jié)果可以用于質(zhì)量檢驗。例如決定最多允許出現(xiàn)多少個有缺陷的部件，才可以保證當(dāng)整個產(chǎn)品在離開裝配線時檢驗合格。語法：CRITBINOM(trials，probability_s，alpha)參數(shù)：Trials是伯努利實驗的次數(shù)，Probability_s是一次試驗中成功的概率，Alpha是臨界值。實例：公式“=CRITBINOM(10，0.9，0.75)”返回10。18.DEVSQ用途：返回數(shù)據(jù)點與各自樣本平均值的偏差的平方和。語法：DEVSQ(number1，number2，.)參數(shù)：Number1、number2、.是用于計算偏差平方和的1到30個參數(shù)。它們可以是用逗號分隔的數(shù)值，也可以是數(shù)組引用。實例：如果A1=90、A2=86、A3=65、A4=54、A5=36，則公式“=DEVSQ(A1:A5)”返回2020.8。19.EXPONDIST用途：返回指數(shù)分布。該函數(shù)可以建立事件之間的時間間隔模型，如估計銀行的自動取款機支付一次現(xiàn)金所花費的時間，從而確定此過程最長持續(xù)一分鐘的發(fā)生概率。語法：EXPONDIST(x，lambda，cumulative)。參數(shù)：X函數(shù)的數(shù)值，Lambda參數(shù)值，Cumulative為確定指數(shù)函數(shù)形式的邏輯值。如果cumulative為TRUE，EXPONDIST返回累積分布函數(shù);如果cumulative為FALSE，則返回概率密度函數(shù)。實例：公式“=EXPONDIST(0.2，10，TRUE)”返回0.864665，=EXPONDIST(0.2，10，F(xiàn)ALSE)返回1.353353。20.FDIST用途：返回F概率分布，它可以確定兩個數(shù)據(jù)系列是否存在變化程度上的不同。例如，通過分析某一班級男、女生的考試分?jǐn)?shù)，確定女生分?jǐn)?shù)的變化程度是否與男生不同。語法：FDIST(x，degrees_freedom1，degrees_freedom2)參數(shù)：X是用來計算概率分布的區(qū)間點，Degrees_freedom1是分子自由度，Degrees_freedom2是分母自由度。實例：公式“=FDIST(1，90，89)”返回0.500157305。函數(shù)右邊括號中的部分稱為參數(shù)，假如一個函數(shù)可以使用多個參數(shù)，那么參數(shù)與參數(shù)之間使用半角逗號進(jìn)行分隔。參數(shù)可以是常量(數(shù)字和文本)、邏輯值(例如TRUE或FALSE)、數(shù)組、錯誤值(例如#N/A)或單元格引用(例如E1:H1)，甚至可以是另一個或幾個函數(shù)等。參數(shù)的類型和位置必須滿足函數(shù)語法的要求，否則將返回錯誤信息。(1)常量常量是直接輸入到單元格或公式中的數(shù)字或文本，或由名稱所代表的數(shù)字或文本值，例如數(shù)字“2890.56”、日期“2003-8-19”和文本“黎明”都是常量。但是公式或由公式計算出的結(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é)果，或可以對存放在行和列中的一組參數(shù)進(jìn)行計算的公式。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ū)域進(jìn)行求和運算，則公式中的引用形式為“=SUM(Sheet2!A1:A6,Sheet3!B2:B9)”。也就是說三維引用中不僅包含單元格或區(qū)域引用，還要在前面加上帶“!”的工作表名稱。假如你要引用的數(shù)據(jù)來自另一個工作簿，如工作簿Book1中的SUM函數(shù)要絕對引用工作簿Book2中的數(shù)據(jù)，其公式為“=SUM(Book2Sheet1! SA S1: SA S8,Book2Sheet2! SB S1: SB S9)”，也就是在原來單元格引用的前面加上“Book2Sheet1!”。放在中括號里面的是工作簿名稱，帶“!”的則是其中的工作表名稱。即是跨工作簿引用單元格或區(qū)域時，引用對象的前面必須用“!”作為工作表分隔符，再用中括號作為工作簿分隔符。不過三維引用的要受到較多的限制，例如不能使用數(shù)組公式等。提示:上面介紹的是Excel默認(rèn)的引用方式，稱為“A1引用樣式”。如果你要計算處在“宏”內(nèi)的行和列，必須使用“R1C1引用樣式”。在這種引用樣式中，Excel使用“R”加“行標(biāo)”和“C”加“列標(biāo)”的方法指示單元格位置。啟用或關(guān)閉R1C1引用樣式必須單擊“工具選項”菜單命令，打開對話框的“常規(guī)”選項卡，選中或清除“設(shè)置”下的“R1C1引用樣式”選項。由于這種引用樣式很少使用，限于篇幅本文不做進(jìn)一步介紹。(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)輸入了“物理”字樣，則求物理平均分的公式可以寫成“=AVERAGE(物理)”。需要特別說明的是，創(chuàng)建好的名稱可以被所有工作表引用，而且引用時不需要在名稱前面添加工作表名(這就是使用名稱的主要優(yōu)點)，因此名稱引用實際上是一種絕對引用。但是公式引用“列標(biāo)志”時的限制較多，它只能在當(dāng)前數(shù)據(jù)列的下方引用，不能跨越工作表引用，但是引用“列標(biāo)志”的公式在一定條件下可以復(fù)制。從本質(zhì)上講，名稱和標(biāo)志都是單元格引用的一種方式。因為它們不是文本，使用時名稱和標(biāo)志都不能添加引號。在excel中計算日期差工齡生日等2009-03-13 PC 類型: 轉(zhuǎn)載 來源: 新快網(wǎng) 責(zé)編: 吳宏 方法1：在A1單元格輸入前面的日期，比如“2004-10-10”，在A2單元格輸入后面的日期，如“2005-6-7”。接著單擊A3單元格，輸入公式“=DATEDIF(A1,A2,d)”。然后按下回車鍵，那么立刻就會得到兩者的天數(shù)差“240”。提示：公式中的A1和A2分別代表前后兩個日期，順序是不可以顛倒的。此外，DATEDIF函數(shù)是Excel中一個隱藏函數(shù)，在函數(shù)向?qū)е锌床坏剿?，但這并不影響我們的使用。方法2：任意選擇一個單元格，輸入公式“=2004-10-10-2005-6-7”，然后按下回車鍵，我們可以立即計算出結(jié)果。計算工作時間工齡假如日期數(shù)據(jù)在D2單元格。=DATEDIF(D2,TODAY(),y)+1注意：工齡兩頭算，所以加“1”。如果精確到“天”=DATEDIF(D2,TODAY(),y)&年&DATEDIF(D2,TODAY(),ym)&月&DATEDIF(D2,TODAY(),md)&日二、計算2003-7-617:05到2006-7-713:50分之間相差了多少天、多少個小時多少分鐘假定原數(shù)據(jù)分別在A1和B1單元格,將計算結(jié)果分別放在C1、D1和E1單元格。C1單元格公式如下：=ROUND(B1-A1,0)D1單元格公式如下：=(B1-A1)*24E1單元格公式如下：=(B1-A1)*24*60注意:A1和B1單元格格式要設(shè)為日期,C1、D1和E1單元格格式要設(shè)為常規(guī).三、計算生日，假設(shè)b2為生日=datedif(B2,today(),y)DATEDIF函數(shù)，除Excel2000中在幫助文檔有描述外，其他版本的Excel在幫助文檔中都沒有說明，并且在所有版本的函數(shù)向?qū)е幸捕颊也坏酱撕瘮?shù)。但該函數(shù)在電子表格中確實存在，并且用來計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)很方便。微軟稱，提供此函數(shù)是為了與Lotus1-2-3兼容。該函數(shù)的用法為“DATEDIF(Start_date,End_date,Unit)”，其中Start_date為一個日期，它代表時間段內(nèi)的第一個日期或起始日期。End_date為一個日期，它代表時間段內(nèi)的最后一個日期或結(jié)束日期。Unit為所需信息的返回類型。“Y”為時間段中的整年數(shù)，“M”為時間段中的整月數(shù)，“D”時間段中的天數(shù)。“MD”為Start_date與End_date日期中天數(shù)的差，可忽略日期中的月和年?！癥M”為Start_date與End_date日期中月數(shù)的差，可忽略日期中的日和年?！癥D”為Start_date與End_date日期中天數(shù)的差，可忽略日期中的年。比如，B2單元格中存放的是出生日期（輸入年月日時，用斜線或短橫線隔開），在C2單元格中輸入“=datedif(B2,today(),y)”（C2單元格的格式為常規(guī)），按回車鍵后，C2單元格中的數(shù)值就是計算后的年齡。此函數(shù)在計算時，只有在兩日期相差滿12個月，才算為一年，假如生日是2004年2月27日，今天是2005年2月28日，用此函數(shù)計算的年齡則為0歲，這樣算出的年齡其實是最公平的。Excel 宏表函數(shù) DATEDIF 參考DateDiff 函數(shù) Excel 中有個宏表函數(shù) DATEDIF，用它可以計算兩日期相關(guān)多少天、多少月等值，但 Excel 中并沒有直接的關(guān)于它的幫助文檔。下面是 VBA 中 DateDiff 函數(shù)的參考，同樣適用，請參考。返回 Variant (Long) 的值，表示兩個指定日期間的時間間隔數(shù)目。 語法 DateDiff(interval, date1, date2, firstdayofweek, firstweekofyear) DateDiff 函數(shù)語法中有下列命名參數(shù)： 部分 描述 interval 必要。字符串表達(dá)式，表示用來計算date1 和 date2 的時間差的時間間隔 Date1，date2 必要；Variant (Date)。計算中要用到的兩個日期。 Firstdayofweek 可選。指定一個星期的第一天的常數(shù)。如果未予指定，則以星期日為第一天。 firstweekofyear 可選。指定一年的第一周的常數(shù)。如果未予指定，則以包含 1 月 1 日的星期為第一周。設(shè)置 interval 參數(shù)的設(shè)定值如下： 設(shè)置 描述 yyyy 年 q 季 m 月 y 一年的日數(shù) d 日 w 一周的日數(shù) ww 周 h 時 n 分鐘 s 秒 firstdayofweek 參數(shù)的設(shè)定值如下： 常數(shù) 值 描述 vbUseSystem 0 使用 NLS API 設(shè)置。 vbSunday 1 星期日（缺省值） vbMonday 2 星期一 vbTuesday 3 星期二 vbWednesday 4 星期三 vbThursday 5 星期四 vbFriday 6 星期五 vbSaturday 7 星期六常數(shù) 值 描述