excel的一些技巧

Excel函數應用之函數簡介圖1 嵌套函數在學習Excel函數之前，我們需要對於函數的結構做以必要的了解。如圖2所示，函數的結構以函數名稱開始，後面是左圓括弧、以逗號分隔的參數和右圓括弧。如果函數以公式的形式出現，請在函數名稱前面鍵入等號（=）。在創建包含函數的公式時，公式選項板將提供相關的幫助。圖2 函數的結構公式選項板--幫助創建或編輯公式的工具，還可提供有關函數及其參數的信息。單擊編輯欄中的"編輯公式"按鈕，或是單擊"常用"工具欄中的"粘貼函數" 按鈕之後，就會在編輯欄下面出現公式選項板。整個過程如圖3所示。圖3 公式選項板二、使用函數的步驟在Excel中如何使用函數呢？1.單擊需要輸入函數的單元格，如圖4所示，單擊單元格C1，出現編輯欄圖4 單元格編輯2.單擊編輯欄中"編輯公式"按鈕 ，將會在編輯欄下面出現一個"公式選項板"，此時"名稱"框將變成"函數"按鈕，如圖3所示。3.單擊"函數"按鈕右端的箭頭，打開函數列表框，從中選擇所需的函數；圖5 函數列表框4.當選中所需的函數後，Excel 2000將打開"公式選項板"。用戶可以在這個選項板中輸入函數的參數，當輸入完參數後，在"公式選項板"中還將顯示函數計算的結果；5.單擊"確定"按鈕，即可完成函數的輸入；6.如果列表中沒有所需的函數，可以單擊"其它函數"選項，打開"粘貼函數"對話框，用戶可以從中選擇所需的函數，然後單擊"確定"按鈕返回到"公式選項板"對話框。在了解了函數的基本知識及使用方法後，請跟隨筆者一起尋找Excel提供的各種函數。您可以通過單擊插入欄中的"函數"看到所有的函數。圖6 粘貼函數列表三、函數的種類Excel函數一共有11類，分別是資料庫函數、日期與時間函數、工程函數、財務函數、信息函數、邏輯函數、查詢和引用函數、數學和三角函數、統計函數、文本函數以及用戶自定義函數。1.資料庫函數--當需要分析數據清單中的數值是否符合特定條件時，可以使用資料庫工作表函數。例如，在一個包含銷售信息的數據清單中，可以計算出所有銷售數值大於 1,000 且小於 2,500 的行或記錄的總數。Microsoft Excel 共有 12 個工作表函數用於對存儲在數據清單或資料庫中的數據進行分析，這些函數的統一名稱為 Dfunctions，也稱為 D 函數，每個函數均有三個相同的參數：database、field 和 criteria。這些參數指向資料庫函數所使用的工作表區域。其中參數 database 為工作表上包含數據清單的區域。參數 field 為需要匯總的列的標誌。參數 criteria 為工作表上包含指定條件的區域。2.日期與時間函數--通過日期與時間函數，可以在公式中分析和處理日期值和時間值。3.工程函數--工程工作表函數用於工程分析。這類函數中的大多數可分為三種類型：對複數進行處理的函數、在不同的數字系統（如十進位系統、十六進位系統、八進位系統和二進位系統）間進行數值轉換的函數、在不同的度量系統中進行數值轉換的函數。4.財務函數--財務函數可以進行一般的財務計算，如確定貸款的支付額、投資的未來值或凈現值，以及債券或息票的價值。財務函數中常見的參數：未來值 (fv)--在所有付款發生後的投資或貸款的價值。期間數 (nper)--投資的總支付期間數。付款 (pmt)--對於一項投資或貸款的定期支付數額。現值 (pv)--在投資期初的投資或貸款的價值。例如，貸款的現值為所借入的本金數額。利率 (rate)--投資或貸款的利率或貼現率。類型 (type)--付款期間內進行支付的間隔，如在月初或月末。5.信息函數--可以使用信息工作表函數確定存儲在單元格中的數據的類型。信息函數包含一組稱為 IS 的工作表函數，在單元格滿足條件時返回 TRUE。例如，如果單元格包含一個偶數值，ISEVEN 工作表函數返回 TRUE。如果需要確定某個單元格區域中是否存在空白單元格，可以使用 COUNTBLANK 工作表函數對單元格區域中的空白單元格進行計數，或者使用 ISBLANK 工作表函數確定區域中的某個單元格是否為空。6.邏輯函數--使用邏輯函數可以進行真假值判斷，或者進行複合檢驗。例如，可以使用 IF 函數確定條件為真還是假，並由此返回不同的數值。7.查詢和引用函數--當需要在數據清單或表格中查找特定數值，或者需要查找某一單元格的引用時，可以使用查詢和引用工作表函數。例如，如果需要在表格中查找與第一列中的值相匹配的數值，可以使用 VLOOKUP 工作表函數。如果需要確定數據清單中數值的位置，可以使用 MATCH 工作表函數。8.數學和三角函數--通過數學和三角函數，可以處理簡單的計算，例如對數字取整、計算單元格區域中的數值總和或複雜計算。9.統計函數--統計工作表函數用於對數據區域進行統計分析。例如，統計工作表函數可以提供由一組給定值繪製出的直線的相關信息，如直線的斜率和 y 軸截距，或構成直線的實際點數值。10.文本函數--通過文本函數，可以在公式中處理文字串。例如，可以改變大小寫或確定文字串的長度。可以將日期插入文字串或連接在文字串上。下面的公式為一個示例，藉以說明如何使用函數 TODAY 和函數 TEXT 來創建一條信息，該信息包含著當前日期並將日期以"dd-mm-yy"的格式表示。11.用戶自定義函數--如果要在公式或計算中使用特別複雜的計算，而工作表函數又無法滿足需要，則需要創建用戶自定義函數。這些函數，稱為用戶自定義函數，可以通過使用 Visual Basic for Applications 來創建。以上對Excel函數及有關知識做了簡要的介紹，在以後的文章中筆者將逐一介紹每一類函數的使用方法及應用技巧。但是由於Excel的函數相當多，因此也可能僅介紹幾種比較常用的函數使用方法，其他更多的函數您可以從Excel的在線幫助功能中了解更詳細的資訊。Excel進階技巧（一）無可見邊線表格的製作方法對Excel工作簿中的表格線看膩了嗎？別著急，Excel的這些表格線並非向人們想像的那樣是必需的，它也是可以去掉的，我們也可以使整個Excel工作簿變成「白紙」，具體步驟為：1.執行「工具」菜單中的「選項」命令，打開「選項」對話框。2.單擊「視圖」選項卡。3.清除「網格線」選項。4.單擊「確定」按鈕，關閉「選項」對話框。快速輸入大寫中文數字的簡便方法Excel具有將用戶輸入的小寫數字轉換為大寫數字的功能（如它可自動將123.45轉換為「壹佰貳拾叄點肆伍」），這就極大的方便了用戶對表格的處理工作。實現這一功能的具體步驟為：1.將游標移至需要輸入大寫數字的單元格中。2.利用數字小鍵盤在單元格中輸入相應的小寫數字（如123.45）。3.右擊該單元格，並從彈出的快捷菜單中執行「設置單元格格式」命令。4.從彈出的「單元格格式」對話框中選擇「數字」選項卡。5.從「分類」列表框中選擇「特殊」選項；從「類別」列表框中選擇「中文大寫數字」選項。6.單擊「確定」按鈕，用戶輸入的123.45就會自動變為「壹佰貳拾叄點肆伍」，效果非常不錯。在公式和結果之間進行切換的技巧一般來說，當我們在某個單元格中輸入一些計算公式之後，Excel只會採用數據顯示方式，也就是說它會直接將計算結果顯示出來，我們反而無法原始的計算公式。廣大用戶若擬查看原始的計算公式，只需單擊「Ctrl-`"鍵（後撇號，鍵盤上浪線符~的小寫方式），Excel就會在計算公式和最終計算結果之間進行切換。不過此功能僅對當前活動工作簿有效，用戶若擬將所有工作簿都設置為只顯示公式，則應採用如下方法：1.執行「工具」菜單的「選項」命令，打開「選項」對話框。2.擊「視圖」選項卡。3.選「窗口選項」欄中的「公式」選項。4.單擊「確定」按鈕，關閉「選項」對話框。Excel中自動為表格添加序號的技巧Excel具有自動填充功能，它可幫助用戶快速實現諸如「第1欄」、「第2欄」…「第10欄」之類的數據填充工作，具體步驟為：1.在A1、B1單元格中分別輸入「第1欄」、「第2欄」字樣。2.用滑鼠將A1:B1單元格定義為塊。3.為它們設置適當的字體、字型大小及對其方式（如居中、右對齊）等內容。4.將滑鼠移至B1單元格的右下角，當其變成十字形時，拖動滑鼠向右移動，直至J1欄為止。5.放開滑鼠，則A1-J1欄就會出現諸如「第1欄」、「第2欄」…「第10欄」的欄號，且它們的格式、排列位置都完全相同，從而滿足了用戶為表格添加欄號的要求。當然，我們也可採用同樣的辦法在Excel表格中自動設置F1、F2或第1行、第2行之類的行號，操作十分方便。在多個工作表內輸入相同內容的技巧有時，我們會因為某些特殊原因而希望在同一個工作簿的不同工作表中輸入相同的內容，這時我們既不必逐個進行輸入，也不必利用複製、粘貼的辦法，直接利用下述方法即可達到目的：1. 打開相應工作簿。2. 在按下Ctrl鍵的同時採用滑鼠單擊窗口需要輸入相同內容的不同工作表（如Sheet1、Sheet2......），為這些工作表建立聯繫關係。3. 在其中的任意一個工作表中輸入需要所入的內容（如表格的表頭及表格線等），此時這些數據就會自動出現在選中的其它工作表之中。4. 輸入完畢之後，再次按下Ctrl鍵，並使用滑鼠單擊所選擇的多個工作表，解除這些工作表之間的聯繫關係（否則用戶輸入的內容還會出現在其它工作表中）。在Excel 97中插入超級鏈接的技巧與Word等其它Office組件一樣，Excel 97也具有在工作簿中插入超級鏈接的功能，我們可以利用此功能將有關Internet網址、磁碟地址、甚至同一張Excel工作簿的不同的單元格鏈接起來，此後就可以直接利用這些鏈接進行調用，從而極大地方便了用戶的使用。在Excel 97中插入超級鏈接的步驟為：1. 將游標移到需要插入超級鏈接的位置。2. 執行「插入」菜單中的「超級鏈接」命令，打開「插入超級鏈接」對話框。3. 在「鏈接到文件或URL"對話框中指定需要鏈接的文件位置或Internet網址（當用戶需要鏈接同一個工作簿中的不同單元格時，此位置可空出不填）。4. 在「文件中有名稱的位置」對話框中指定需要鏈接文件的具體位置（如Word文檔的某個書籤、Excel工作簿的某個單元格等）。5. 單擊「確定」按鈕，關閉「插入超級鏈接」對話框。這樣，我們就實現了在Excel工作簿中插入超級鏈接的目的，此後用戶只需單擊該超級鏈接按鈕，系統即會自動根據超級鏈接所致顯得內容做出相應的處理（若鏈接的對象為Internet網址則自動激活IE打開該網址；若鏈接的對象為磁碟文件，則自動打開該文件；若鏈接的對象為同一個工作簿的不同單元格則自動將當前單元格跳轉至該單元格），從而滿足了廣大用戶的需要。複製樣式的技巧除可對已有的樣式進行修改及自定義所需的樣式之外，Excel還允許我們將某個工作簿所包含的樣式拷貝到其它工作簿中使用，以進一步擴大樣式的使用範圍，具體步驟為：1. 打開包含有需要複製樣式的源工作簿及目標工作簿。2. 執行「格式」菜單的「樣式」命令，打開「樣式」對話框。3. 單擊「合併」按鈕，打開「合併樣式」對話框。4. 在「合併樣式來源」框中選擇包含有要複製樣式的源工作簿並單擊「確定」按鈕，源工作簿中所包含的一切樣式就會拷貝到目標工作簿中（對於同名的樣式，系統將會要求用戶選擇是否覆蓋），然後我們就可以在目標工作簿中直接加以使用了，從而免去了重複定義之苦。利用粘貼函數功能簡化函數的調用速度Excel一共向用戶提供了日期、統計、財務、文本、查找、信息、邏輯等10多類，總計達數百種函數，不熟悉的用戶可能很難使用這些優秀功能，沒關係，「粘貼函數」功能可在我們需要使用各種函數時提供很大的幫助。「粘貼函數」功能是Excel為了解決部分用戶不了解各種函數的功能及用法、而專門設置的一種一步一步指導用戶正確使用各種函數的嚮導功能，廣大用戶可在它的指導下方便地使用各種自己不熟悉的函數，並可在使用的過程中同時進行學習、實踐，效果非常好。在Excel中使用「粘貼函數」功能插入各種函數的步驟為：1. 在Excel中將游標移至希望插入有關函數的單元格中。2. 執行Excel"插入」菜單的「函數」命令（或單擊快捷工具欄上的「粘貼函數」按鈕），打開「粘貼函數」對話框。3. 若Office嚮導沒有出現，可單擊「粘貼函數」對話框中的「Office嚮導」按鈕，激活Office嚮導，以便在適當的時候獲取它的幫助。4. 在參考「粘貼函數」對話框對各個函數簡介的前提下，從「函數分類」列表框中選擇欲插入函數的分類、從「函數名」列表框中選擇所需插入的函數。5. 單擊「確定」按鈕。6. 此時，系統就會打開一個用於指導用戶插入相應函數的對話框，我們可在它的指導下為所需插入的函數指定各種參數。7. 單擊「確定」按鈕。Excel函數應用之數學和三角函數學習Excel函數，我們還是從「數學與三角函數」開始。畢竟這是我們非常熟悉的函數，這些正弦函數、餘弦函數、取整函數等等從中學開始，就一直陪伴著我們。首先，讓我們一起看看Excel提供了哪些數學和三角函數。筆者在這裡以列表的形式列出Excel提供的所有數學和三角函數，詳細請看附註的表格。從表中我們不難發現，Excel提供的數學和三角函數已基本囊括了我們通常所用得到的各種數學公式與三角函數。這些函數的詳細用法，筆者不在這裡一一贅述，下面從應用的角度為大家演示一下這些函數的使用方法。一、與求和有關的函數的應用SUM函數是Excel中使用最多的函數，利用它進行求和運算可以忽略存有文本、空格等數據的單元格，語法簡單、使用方便。相信這也是大家最先學會使用的Excel函數之一。但是實際上，Excel所提供的求和函數不僅僅只有SUM一種，還包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2幾種函數。這裡筆者將以某單位工資表為例重點介紹SUM（計算一組參數之和）、SUMIF（對滿足某一條件的單元格區域求和）的使用。(說明：為力求簡單，示例中忽略稅金的計算。)圖1 函數求和SUM1、行或列求和以最常見的工資表（如上圖）為例，它的特點是需要對行或列內的若干單元格求和。比如，求該單位2001年5月的實際發放工資總額，就可以在H13中輸入公式：=SUM(H3:H12)2、區域求和區域求和常用於對一張工作表中的所有數據求總計。此時你可以讓單元格指針停留在存放結果的單元格，然後在Excel編輯欄輸入公式"=SUM（）"，用滑鼠在括弧中間單擊，最後拖過需要求和的所有單元格。若這些單元格是不連續的，可以按住Ctrl鍵分別拖過它們。對於需要減去的單元格，則可以按住Ctrl鍵逐個選中它們，然後用手工在公式引用的單元格前加上負號。當然你也可以用公式選項板完成上述工作，不過對於SUM函數來說手工還是來的快一些。比如，H13的公式還可以寫成：=SUM(D3:D12,F3:F12)-SUM(G3:G12)3、注意SUM函數中的參數，即被求和的單元格或單元格區域不能超過30個。換句話說，SUM函數括弧中出現的分隔符（逗號）不能多於29個，否則Excel就會提示參數太多。對需要參與求和的某個常數，可用"=SUM（單元格區域，常數）"的形式直接引用，一般不必絕對引用存放該常數的單元格。SUMIFSUMIF函數可對滿足某一條件的單元格區域求和，該條件可以是數值、文本或表達式，可以應用在人事、工資和成績統計中。仍以上圖為例，在工資表中需要分別計算各個科室的工資發放情況。要計算銷售部2001年5月加班費情況。則在F15種輸入公式為=SUMIF($C$3:$C$12,"銷售部",$F$3:$F$12)其中"$C$3:$C$12"為提供邏輯判斷依據的單元格區域，"銷售部"為判斷條件即只統計$C$3:$C$12區域中部門為"銷售部"的單元格，$F$3:$F$12為實際求和的單元格區域。二、與函數圖像有關的函數應用我想大家一定還記得我們在學中學數學時，常常需要畫各種函數圖像。那個時候是用坐標紙一點點描繪，常常因為計算的疏忽，描不出平滑的函數曲線。現在，我們已經知道Excel幾乎囊括了我們需要的各種數學和三角函數，那是否可以利用Excel函數與Excel圖表功能描繪函數圖像呢？當然可以。這裡，筆者以正弦函數和餘弦函數為例說明函數圖像的描繪方法。圖2 函數圖像繪製1、 錄入數據--如圖所示，首先在表中錄入數據，自B1至N1的單元格以30度遞增的方式錄入從0至360的數字，共13個數字。2、 求函數值--在第2行和第三行分別輸入SIN和COS函數，這裡需要注意的是：由於SIN等三角函數在Excel的定義是要弧度值，因此必須先將角度值轉為弧度值。具體公式寫法為（以D2為例）： =SIN(D1*PI()/180)3、 選擇圖像類型--首先選中製作函數圖像所需要的表中數據，利用Excel工具欄上的圖表嚮導按鈕（也可利用"插入"／"圖表"），在"圖表類型"中選擇"XY散點圖"，再在右側的"子圖表類型"中選擇"無數據點平滑線散點圖"，單擊[下一步]，出現"圖表數據源"窗口，不作任何操作，直接單擊[下一步]。4、 圖表選項操作--圖表選項操作是製作函數曲線圖的重要步驟，在"圖表選項"窗口中進行（如圖3），依次進行操作的項目有：標題--為圖表取標題，本例中取名為"正弦和餘弦函數圖像"；為橫軸和縱軸取標題。坐標軸--可以不做任何操作；網格線--可以做出類似坐標紙上網格，也可以取消網格線；圖例--本例選擇圖例放在圖像右邊，這個可隨具體情況選擇；數據標誌--本例未將數據標誌在圖像上，主要原因是影響美觀。如果有特殊要求例外。5、完成圖像--操作結束後單擊[完成]，一幅圖像就插入Excel的工作區了。6、 編輯圖像--圖像生成後，字體、圖像大小、位置都不一定合適。可選擇相應的選項進行修改。所有這些操作可以先用滑鼠選中相關部分，再單擊右鍵彈出快捷菜單，通過快捷菜單中的有關項目即可進行操作。至此，一幅正弦和餘弦函數圖像製作完成。用同樣的方法，還可以製作二次曲線、對數圖像等等。三、常見數學函數使用技巧--四捨五入在實際工作的數學運算中，特別是財務計算中常常遇到四捨五入的問題。雖然，excel的單元格格式中允許你定義小數位數，但是在實際操作中，我們發現，其實數字本身並沒有真正的四捨五入，只是顯示結果似乎四捨五入了。如果採用這種四捨五入方法的話，在財務運算中常常會出現幾分錢的誤差，而這是財務運算不允許的。那是否有簡單可行的方法來進行真正的四捨五入呢？其實，Excel已經提供這方面的函數了，這就是ROUND函數，它可以返回某個數字按指定位數舍入後的數字。在Excel提供的"數學與三角函數"中提供了一個名為ROUND(number,num_digits)的函數，它的功能就是根據指定的位數，將數字四捨五入。這個函數有兩個參數，分別是number和num_digits。其中number就是將要進行四捨五入的數字；num_digits則是希望得到的數字的小數點後的位數。如圖3所示：單元格B2中為初始數據0.123456，B3的初始數據為0.234567，將要對它們進行四捨五入。在單元格C2中輸入"=ROUND(B2,2)"，小數點後保留兩位有效數字，得到0.12、0.23。在單元格D2中輸入"=ROUND(B2,4)"，則小數點保留四位有效數字，得到0.1235、0.2346。圖3 對數字進行四捨五入對於數字進行四捨五入,還可以使用INT（取整函數），但由於這個函數的定義是返回實數舍入後的整數值。因此，用INT函數進行四捨五入還是需要一些技巧的，也就是要加上0.5，才能達到取整的目的。仍然以圖3為例，如果採用INT函數，則C2公式應寫成："=INT(B2*100+0.5)/100"。最後需要說明的是：本文所有公式均在Excel97和Excel2000中驗證通過，修改其中的單元格引用和邏輯條件值，可用於相似的其他場合。附註：Excel的數學和三角函數一覽表ABS 工作表函數返回參數的絕對值ACOS 工作表函數返回數字的反餘弦值ACOSH 工作表函數返回參數的反雙曲餘弦值ASIN 工作表函數返回參數的反正弦值ASINH 工作表函數返回參數的反雙曲正弦值ATAN 工作表函數返回參數的反正切值ATAN2 工作表函數返回給定的 X 及 Y 坐標值的反正切值ATANH 工作表函數返回參數的反雙曲正切值CEILING 工作表函數將參數 Number 沿絕對值增大的方向，舍入為最接近的整數或基數COMBIN 工作表函數計算從給定數目的對象集合中提取若干對象的組合數COS 工作表函數返回給定角度的餘弦值COSH 工作表函數返回參數的雙曲餘弦值COUNTIF 工作表函數計算給定區域內滿足特定條件的單元格的數目DEGREES 工作表函數將弧度轉換為度EVEN 工作表函數返回沿絕對值增大方向取整後最接近的偶數EXP 工作表函數返回 e 的 n 次冪常數 e 等於 2.71828182845904，是自然對數的底數FACT 工作表函數返回數的階乘，一個數的階乘等於 1*2*3*...*該數FACTDOUBLE 工作表函數返回參數 Number 的半階乘FLOOR 工作表函數將參數 Number 沿絕對值減小的方向去尾舍入，使其等於最接近的 significance 的倍數GCD 工作表函數返回兩個或多個整數的最大公約數INT 工作表函數返回實數舍入後的整數值LCM 工作表函數返回整數的最小公倍數LN 工作表函數返回一個數的自然對數自然對數以常數項 e（2.71828182845904）為底LOG 工作表函數按所指定的底數，返回一個數的對數LOG10 工作表函數返回以 10 為底的對數MDETERM 工作表函數返回一個數組的矩陣行列式的值MINVERSE 工作表函數返回數組矩陣的逆距陣MMULT 工作表函數返回兩數組的矩陣乘積結果MOD 工作表函數返回兩數相除的餘數結果的正負號與除數相同MROUND 工作表函數返回參數按指定基數舍入後的數值MULTINOMIAL 工作表函數返回參數和的階乘與各參數階乘乘積的比值ODD 工作表函數返回對指定數值進行舍入後的奇數PI 工作表函數返回數字 3.14159265358979，即數學常數 pi，精確到小數點後 15 位POWER 工作表函數返回給定數字的乘冪PRODUCT 工作表函數將所有以參數形式給出的數字相乘，並返回乘積值QUOTIENT 工作表函數回商的整數部分，該函數可用於舍掉商的小數部分RADIANS 工作表函數將角度轉換為弧度RAND 工作表函數返回大於等於 0 小於 1 的均勻分布隨機數RANDBETWEEN 工作表函數返回位於兩個指定數之間的一個隨機數ROMAN 工作表函數將阿拉伯數字轉換為文本形式的羅馬數字ROUND 工作表函數返回某個數字按指定位數舍入後的數字ROUNDDOWN 工作表函數靠近零值，向下（絕對值減小的方向）舍入數字ROUNDUP 工作表函數遠離零值，向上（絕對值增大的方向）舍入數字SERIESSUM 工作表函數返回基於以下公式的冪級數之和：SIGN 工作表函數返回數字的符號當數字為正數時返回 1，為零時返回 0，為負數時返回 -1SIN 工作表函數返回給定角度的正弦值SINH 工作表函數返回某一數字的雙曲正弦值SQRT 工作表函數返回正平方根SQRTPI 工作表函數返回某數與 pi 的乘積的平方根SUBTOTAL 工作表函數返回數據清單或資料庫中的分類匯總SUM 工作表函數返回某一單元格區域中所有數字之和SUMIF 工作表函數根據指定條件對若干單元格求和SUMPRODUCT 工作表函數在給定的幾組數組中，將數組間對應的元素相乘，並返回乘積之和SUMSQ 工作表函數返回所有參數的平方和SUMX2MY2 工作表函數返回兩數組中對應數值的平方差之和SUMX2PY2 工作表函數返回兩數組中對應數值的平方和之和，平方和加總在統計計算中經常使用SUMXMY2 工作表函數返回兩數組中對應數值之差的平方和TAN 工作表函數返回給定角度的正切值TANH 工作表函數返回某一數字的雙曲正切值TRUNC 工作表函數將數字的小數部分截去，返回整數Excel函數應用之邏輯函數用來判斷真假值，或者進行複合檢驗的Excel函數，我們稱為邏輯函數。在Excel中提供了六種邏輯函數。即AND、OR、NOT、FALSE、IF、TRUE函數。一、AND、OR、NOT函數這三個函數都用來返回參數邏輯值。詳細介紹見下：（一）AND函數所有參數的邏輯值為真時返回 TRUE；只要一個參數的邏輯值為假即返回 FALSE。簡言之，就是當AND的參數全部滿足某一條件時，返回結果為TRUE，否則為FALSE。語法為AND(logical1,logical2, ...)，其中Logical1, logical2, ... 表示待檢測的 1 到 30 個條件值，各條件值可能為TRUE，可能為 FALSE。 參數必須是邏輯值，或者包含邏輯值的數組或引用。舉例說明：1、 在B2單元格中輸入數字50，在C2中寫公式=AND(B2>30,B2<60)。由於B2等於50的確大於30、小於60。所以兩個條件值（logical）均為真，則返回結果為TRUE。圖1 AND函數示例12、 如果 B1-B3 單元格中的值為 TRUE、FALSE、TRUE，顯然三個參數並不都為真，所以 在B4單元格中的公式=AND(B1:B3) 等於 FALSE圖2 AND函數示例2（二）OR函數OR函數指在其參數組中，任何一個參數邏輯值為 TRUE，即返回 TRUE。它與AND函數的區別在於，AND函數要求所有函數邏輯值均為真，結果方為真。而OR函數僅需其中任何一個為真即可為真。比如，上面的示例2，如果在B4單元格中的公式寫為=OR(B1:B3)則結果等於TRUE圖3 OR函數示例（三）NOT函數NOT函數用於對參數值求反。當要確保一個值不等於某一特定值時，可以使用 NOT 函數。簡言之，就是當參數值為TRUE時，NOT函數返回的結果恰與之相反，結果為FALSE.比如NOT(2+2=4)，由於2+2的結果的確為4，該參數結果為TRUE，由於是NOT函數，因此返回函數結果與之相反，為FALSE。二、TRUE、FALSE函數TRUE、FALSE函數用來返回參數的邏輯值，由於可以直接在單元格或公式中鍵入值TRUE或者FALSE。因此這兩個函數通常可以不使用。三、IF函數（一）IF函數說明IF函數用於執行真假值判斷後，根據邏輯測試的真假值返回不同的結果，因此If函數也稱之為條件函數。它的應用很廣泛，可以使用函數 IF 對數值和公式進行條件檢測。它的語法為IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示計算結果為 TRUE 或 FALSE 的任意值或表達式。本參數可使用任何比較運算符。Value_if_true顯示在logical_test 為 TRUE 時返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 為 FALSE 時返回的值。Value_if_false 也可以是其他公式。簡言之，如果第一個參數logical_test返回的結果為真的話，則執行第二個參數Value_if_true的結果，否則執行第三個參數Value_if_false的結果。IF函數可以嵌套七層，用 value_if_false 及 value_if_true 參數可以構造複雜的檢測條件。Excel 還提供了可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區域中某個文本串或數字出現的次數，則可使用 COUNTIF 工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用 SUMIF 工作表函數。（二）IF函數應用1、 輸出帶有公式的空白表單圖5 人事分析表1以圖中所示的人事狀況分析表為例，由於各部門關於人員的組成情況的數據尚未填寫，在總計欄（以單元格G5為例）公式為：=SUM(C5:F5)我們看到計算為0的結果。如果這樣的表格列印出來就頁面的美觀來看顯示是不令人滿意的。是否有辦法去掉總計欄中的0呢？你可能會說，不寫公式不就行了。當然這是一個辦法，但是，如果我們利用了IF函數的話，也可以在寫公式的情況下，同樣不顯示這些0。如何實現呢？只需將總計欄中的公式（僅以單元格G5為例）改寫成：=IF(SUM(C5:F5),SUM(C5:F5),"")通俗的解釋就是：如果SUM(C5:F5)不等於零，則在單元格中顯示SUM(C5:F5)的結果，否則顯示字元串。幾點說明：（1） SUM(C5:F5)不等於零的正規寫法是SUM(C5:F5)<>0，在EXCEL中可以省略<>0；（2） ""表示字元串的內容為空，因此執行的結果是在單元格中不顯示任何字元。圖42、 不同的條件返回不同的結果如果對上述例子有了很好的理解後，我們就很容易將IF函數應用到更廣泛的領域。比如，在成績表中根據不同的成績區分合格與不合格。現在我們就以某班級的英語成績為例具體說明用法。圖6某班級的成績如圖6所示，為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規則。如果各科平均分超過60分則認為是合格的，否則記作不合格。根據這一規則，我們在綜合評定中寫公式（以單元格B12為例）：=IF(B11>60,"合格","不合格")語法解釋為，如果單元格B11的值大於60，則執行第二個參數即在單元格B12中顯示合格字樣，否則執行第三個參數即在單元格B12中顯示不合格字樣。在綜合評定欄中可以看到由於C列的同學各科平均分為54分，綜合評定為不合格。其餘均為合格。3、 多層嵌套函數的應用在上述的例子中，我們只是將成績簡單區分為合格與不合格，在實際應用中，成績通常是有多個等級的，比如優、良、中、及格、不及格等。有辦法一次性區分嗎？可以使用多層嵌套的辦法來實現。仍以上例為例，我們設定綜合評定的規則為當各科平均分超過90時，評定為優秀。如圖7所示。圖7說明：為了解釋起來比較方便，我們在這裡僅做兩重嵌套的示例，您可以按照實際情況進行更多重的嵌套，但請注意Excel的IF函數最多允許七重嵌套。根據這一規則，我們在綜合評定中寫公式（以單元格F12為例）：=IF(F11>60,IF(AND(F11>90),"優秀","合格"),"不合格")語法解釋為，如果單元格F11的值大於60，則執行第二個參數，在這裡為嵌套函數，繼續判斷單元格F11的值是否大於90（為了讓大家體會一下AND函數的應用，寫成AND(F11>90)，實際上可以僅寫F11>90），如果滿足在單元格F12中顯示優秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執行第三個參數即在單元格F12中顯示不合格字樣。在綜合評定欄中可以看到由於F列的同學各科平均分為92分，綜合評定為優秀。（三）根據條件計算值在了解了IF函數的使用方法後，我們再來看看與之類似的Excel提供的可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區域中某個文本串或數字出現的次數，則可使用 COUNTIF 工作表函數。如果要根據單元格區域中的某一文本串或數字求和，則可使用 SUMIF 工作表函數。關於SUMIF函數在數學與三角函數中以做了較為詳細的介紹。這裡重點介紹COUNTIF的應用。COUNTIF可以用來計算給定區域內滿足特定條件的單元格的數目。比如在成績表中計算每位學生取得優秀成績的課程數。在工資表中求出所有基本工資在2000元以上的員工數。語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數目的單元格區域。Criteria確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。例如，條件可以表示為 32、"32"、">32"、"apples"。1、成績表這裡仍以上述成績表的例子說明一些應用方法。我們需要計算的是：每位學生取得優秀成績的課程數。規則為成績大於90分記做優秀。如圖8所示圖8根據這一規則，我們在優秀門數中寫公式（以單元格B13為例）：=COUNTIF(B4:B10,">90")語法解釋為，計算B4到B10這個範圍，即jarry的各科成績中有多少個數值大於90的單元格。在優秀門數欄中可以看到jarry的優秀門數為兩門。其他人也可以依次看到。2、 銷售業績表銷售業績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計算銷售人員的訂單數，然後匯總每個銷售人員的銷售額，並且根據總發貨量決定每次銷售應獲得的獎金。原始數據表如圖9所示（原始數據是以流水單形式列出的，即按訂單號排列）圖9 原始數據表按銷售人員匯總表如圖10所示圖10 銷售人員匯總表如圖10所示的表完全是利用函數計算的方法自動匯總的數據。首先建立一個按照銷售人員匯總的表單樣式，如圖所示。然後分別計算訂單數、訂單總額、銷售獎金。（1） 訂單數 --用COUNTIF計算銷售人員的訂單數。以銷售人員ANNIE的訂單數公式為例。公式：=COUNTIF($C$2:$C$13,A17)語法解釋為計算單元格A17(即銷售人員ANNIE)在"銷售人員"清單$C$2:$C$13的範圍內（即圖9所示的原始數據表）出現的次數。這個出現的次數即可認為是該銷售人員ANNIE的訂單數。（2） 訂單總額--用SUMIF匯總每個銷售人員的銷售額。以銷售人員ANNIE的訂單總額公式為例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在"銷售人員"清單$C$2:$C$13中檢查單元格A17 中的文本（即銷售人員ANNIE），然後計算"訂單金額"列（$B$2:$B$13）中相應量的和。這個相應量的和就是銷售人員ANNIE的訂單總額。（3） 銷售獎金--用IF根據訂單總額決定每次銷售應獲得的獎金。假定公司的銷售獎金規則為當訂單總額超過5萬元時，獎勵幅度為百分之十五，否則為百分之十。根據這一規則仍以銷售人員ANNIE為例說明。公式為：=IF(C17<50000,10%,15%)*C17如果訂單總額小於 50000則獎金為 10%；如果訂單總額大於等於 50000，則獎金為 15%。至此，我們已完全了解了EXCEL函數的邏輯函數，相信大家在實際工作中會想出更多更有用的運用。Excel函數應用之文本/日期/時間函數所謂文本函數，就是可以在公式中處理文字串的函數。例如，可以改變大小寫或確定文字串的長度；可以替換某些字元或者去除某些字元等。而日期和時間函數則可以在公式中分析和處理日期值和時間值。關於這兩類函數的列表參看附表，這裡僅對一些常用的函數做簡要介紹。一、文本函數（一）大小寫轉換LOWER--將一個文字串中的所有大寫字母轉換為小寫字母。UPPER--將文本轉換成大寫形式。PROPER--將文字串的首字母及任何非字母字元之後的首字母轉換成大寫。將其餘的字母轉換成小寫。這三種函數的基本語法形式均為 函數名（text）。示例說明：已有字元串為：pLease ComE Here! 可以看到由於輸入的不規範，這句話大小寫亂用了。通過以上三個函數可以將文本轉換顯示樣式，使得文本變得規範。參見圖1Lower（pLease ComE Here!）= please come here!upper（pLease ComE Here!）= PLEASE COME HERE!proper（pLease ComE Here!）= Please Come Here!圖1您可以使用Mid、Left、Right等函數從長字元串內獲取一部分字元。具體語法格式為LEFT函數：LEFT(text,num_chars)其中Text是包含要提取字元的文本串。Num_chars指定要由 LEFT 所提取的字元數。MID函數：MID(text,start_num,num_chars)其中Text是包含要提取字元的文本串。Start_num是文本中要提取的第一個字元的位置。RIGHT函數：RIGHT(text,num_chars)其中Text是包含要提取字元的文本串。Num_chars指定希望 RIGHT 提取的字元數。比如，從字元串"This is an apple."分別取出字元"This"、"apple"、"is"的具體函數寫法為。LEFT("This is an apple",4)=ThisRIGHT("This is an apple",5)=appleMID("This is an apple",6,2)=is圖2（三）去除字元串的空白在字元串形態中，空白也是一個有效的字元，但是如果字元串中出現空白字元時，容易在判斷或對比數據是發生錯誤，在Excel中您可以使用Trim函數清除字元串中的空白。語法形式為：TRIM(text)其中Text為需要清除其中空格的文本。需要注意的是，Trim函數不會清除單詞之間的單個空格，如果連這部分空格都需清除的話，建議使用替換功能。比如，從字元串"My name is Mary"中清除空格的函數寫法為：TRIM("My name is Mary")=My name is Mary 參見圖3圖3（四）字元串的比較在數據表中經常會比對不同的字元串，此時您可以使用EXACT函數來比較兩個字元串是否相同。該函數測試兩個字元串是否完全相同。如果它們完全相同，則返回 TRUE；否則，返回 FALSE。函數 EXACT 能區分大小寫，但忽略格式上的差異。利用函數 EXACT 可以測試輸入文檔內的文字。語法形式為：EXACT(text1,text2)Text1為待比較的第一個字元串。Text2為待比較的第二個字元串。舉例說明：參見圖4EXACT("China","china")=False圖4二、日期與時間函數在數據表的處理過程中，日期與時間的函數是相當重要的處理依據。而Excel在這方面也提供了相當豐富的函數供大家使用。（一）取出當前系統時間/日期信息用於取出當前系統時間/日期信息的函數主要有NOW、TODAY。語法形式均為 函數名（）。（二）取得日期/時間的部分欄位值如果需要單獨的年份、月份、日數或小時的數據時，可以使用HOUR、DAY、MONTH、YEAR函數直接從日期/時間中取出需要的數據。具體示例參看圖5。比如，需要返回2001-5-30 12:30 PM的年份、月份、日數及小時數，可以分別採用相應函數實現。YEAR(E5)=2001MONTH(E5)=5DAY(E5)=30HOUR(E5)=12圖5此外還有更多有用的日期/時間函數，可以查閱附表。下面我們將以一個具體的示例來說明Excel的文本函數與日期函數的用途。三、示例：做一個美觀簡潔的人事資料分析表1、 示例說明在如圖6所示的某公司人事資料表中，除了編號、員工姓名、身份證號碼以及參加工作時間為手工添入外，其餘各項均為用函數計算所得。圖6在此例中我們將詳細說明如何通過函數求出：（1）自動從身份證號碼中提取出生年月、性別信息。（2）自動從參加工作時間中提取工齡信息。2、身份證號碼相關知識在了解如何實現自動從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當今的身份證號碼有15/18位之分。早期簽發的身份證號碼是15位的，現在簽發的身份證由於年份的擴展（由兩位變為四位）和末尾加了效驗碼，就成了18位。這兩種身份證號碼將在相當長的一段時期內共存。兩種身份證號碼的含義如下：（1）15位的身份證號碼：1~6位為地區代碼，7~8位為出生年份(2位)，9~10位為出生月份，11~12位為出生日期，第13~15位為順序號，並能夠判斷性別，奇數為男，偶數為女。（2）18位的身份證號碼：1~6位為地區代碼，7~10位為出生年份(4位)，11~12位為出生月份，13~14位為出生日期，第15~17位為順序號，並能夠判斷性別，奇數為男，偶數為女。18位為效驗位。3、 應用函數在此例中為了實現數據的自動提取，應用了如下幾個Excel函數。（1）IF函數：根據邏輯表達式測試的結果，返回相應的值。IF函數允許嵌套。語法形式為：IF（logical_test, value_if_true,value_if_false）（2）CONCATENATE：將若干個文字項合併至一個文字項中。語法形式為：CONCATENATE(text1,text2……)（3）MID：從文本字元串中指定的起始位置起，返回指定長度的字元。語法形式為：MID(text,start_num,num_chars)（4）TODAY：返回計算機系統內部的當前日期。語法形式為：TODAY（）（5）DATEDIF：計算兩個日期之間的天數、月數或年數。語法形式為：DATEDIF(start_date,end_date,unit)（6）VALUE：將代表數字的文字串轉換成數字。語法形式為：VALUE(text)（7）RIGHT：根據所指定的字元數返迴文本串中最後一個或多個字元。語法形式為：RIGHT(text,num_chars)（8）INT：返回實數舍入後的整數值。語法形式為：INT(number)4、 公式寫法及解釋（以員工Andy為例說明）說明：為避免公式中過多的嵌套，這裡的身份證號碼限定為15位的。如果您看懂了公式的話，可以進行簡單的修改即可適用於18位的身份證號碼，甚至可適用於15、18兩者並存的情況。（1）根據身份證號碼求性別=IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")公式解釋：a. RIGHT(E4,3)用於求出身份證號碼中代表性別的數字，實際求得的為代表數字的字元串b. VALUE(RIGHT(E4,3)用於將上一步所得的代表數字的字元串轉換為數字c. VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2用於判斷這個身份證號碼是奇數還是偶數，當然你也可以用Mod函數來做出判斷。d. =IF(VALUE(RIGHT(E4,3))/2=INT(VALUE(RIGHT(E4,3))/2),"女","男")及如果上述公式判斷出這個號碼是偶數時，顯示"女"，否則，這個號碼是奇數的話，則返回"男"。（2）根據身份證號碼求出生日期=CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))公式解釋：a. MID(E4,7,2)為在身份證號碼中獲取表示年份的數字的字元串b. MID(E4,9,2) 為在身份證號碼中獲取表示月份的數字的字元串c. MID(E4,11,2) 為在身份證號碼中獲取表示日期的數字的字元串d. CONCATENATE("19",MID(E4,7,2),"/",MID(E4,9,2),"/",MID(E4,11,2))目的就是將多個字元串合併在一起顯示。（3）根據參加工作時間求年資（即工齡）=CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"個月")公式解釋：a. TODAY()用於求出系統當前的時間b. DATEDIF(F4,TODAY(),"y")用於計算當前系統時間與參加工作時間相差的年份c. DATEDIF(F4,TODAY(),"ym")用於計算當前系統時間與參加工作時間相差的月份，忽略日期中的日和年。d. =CONCATENATE(DATEDIF(F4,TODAY(),"y"),"年",DATEDIF(F4,TODAY(),"ym"),"個月")目的就是將多個字元串合併在一起顯示。5. 其他說明在這張人事資料表中我們還發現，創建日期：31-05-2001時顯示在同一個單元格中的。這是如何實現的呢？難道是手工添加的嗎？不是，實際上這個日期還是變化的，它顯示的是系統當前時間。這裡是利用函數 TODAY 和函數 TEXT 一起來創建一條信息，該信息包含著當前日期並將日期以"dd-mm-yyyy"的格式表示。具體公式寫法為：="創建日期："&TEXT(TODAY(),"dd-mm-yyyy")至此，我們對於文本函數、日期與時間函數已經有了大致的了解，同時也設想了一些應用領域。相信隨著大家在這方面的不斷研究，會有更廣泛的應用。附一：文本函數函數名函數說明語法ASC將字元串中的全形（雙位元組）英文字母更改為半形（單位元組）字元。ASC(text)CHAR返回對應於數字代碼的字元，函數 CHAR 可將其他類型計算機文件中的代碼轉換為字元。CHAR(number)CLEAN刪除文本中不能列印的字元。對從其他應用程序中輸入的字元串使用 CLEAN 函數，將刪除其中含有的當前操作系統無法列印的字元。例如，可以刪除通常出現在數據文件頭部或尾部、無法列印的低級計算機代碼。CLEAN(text)CODE返迴文字串中第一個字元的數字代碼。返回的代碼對應於計算機當前使用的字符集。CODE(text)CONCATENATE將若干文字串合併到一個文字串中。CONCATENATE (text1,text2,...)DOLLAR依照貨幣格式將小數四捨五入到指定的位數並轉換成文字。DOLLAR 或 RMB(number,decimals)EXACT該函數測試兩個字元串是否完全相同。如果它們完全相同，則返回 TRUE；否則，返回 FALSE。函數 EXACT 能區分大小寫，但忽略格式上的差異。利用函數 EXACT 可以測試輸入文檔內的文字。EXACT(text1,text2)FINDFIND 用於查找其他文本串 (within_text) 內的文本串 (find_text)，並從 within_text 的首字元開始返回 find_text 的起始位置編號。FIND(find_text,within_text,start_num)FIXED按指定的小數位數進行四捨五入，利用句點和逗號，以小數格式對該數設置格式，並以文字串形式返回結果。FIXED(number,decimals,no_commas)JIS將字元串中的半形（單位元組）英文字母或片假名更改為全形（雙位元組）字元。JIS(text)LEFTLEFT 基於所指定的字元數返迴文本串中的第一個或前幾個字元。LEFTB 基於所指定的位元組數返迴文本串中的第一個或前幾個字元。此函數用於雙位元組字元。LEFT(text,num_chars)LEFTB(text,num_bytes)LENLEN 返迴文本串中的字元數。LENB 返迴文本串中用於代表字元的位元組數。此函數用於雙位元組字元。LEN(text)LENB(text)LOWER將一個文字串中的所有大寫字母轉換為小寫字母。LOWER(text)MIDMID 返迴文本串中從指定位置開始的特定數目的字元，該數目由用戶指定。MIDB 返迴文本串中從指定位置開始的特定數目的字元，該數目由用戶指定。此函數用於雙位元組字元。MID(text,start_num,num_chars)MIDB(text,start_num,num_bytes)PHONETIC提取文本串中的拼音 (furigana) 字元。PHONETIC(reference)PROPER將文字串的首字母及任何非字母字元之後的首字母轉換成大寫。將其餘的字母轉換成小寫。PROPER(text)REPLACEREPLACE 使用其他文本串並根據所指定的字元數替換某文本串中的部分文本。REPLACEB 使用其他文本串並根據所指定的字元數替換某文本串中的部分文本。此函數專為雙位元組字元使用。REPLACE(old_text,start_num,num_chars,new_text)REPLACEB(old_text,start_num,num_bytes,new_text)REPT按照給定的次數重複顯示文本。可以通過函數 REPT 來不斷地重複顯示某一文字串，對單元格進行填充。REPT(text,number_times)RIGHTRIGHT 根據所指定的字元數返迴文本串中最後一個或多個字元。RIGHTB 根據所指定的字元數返迴文本串中最後一個或多個字元。此函數用於雙位元組字元。RIGHT(text,num_chars)RIGHTB(text,num_bytes)SEARCHSEARCH 返回從 start_num 開始首次找到特定字元或文本串的位置上特定字元的編號。使用 SEARCH 可確定字元或文本串在其他文本串中的位置，這樣就可使用 MID 或 REPLACE 函數更改文本。SEARCHB 也可在其他文本串 (within_text) 中查找文本串 (find_text)，並返回 find_text 的起始位置編號。此結果是基於每個字元所使用的位元組數，並從 start_num 開始的。此函數用於雙位元組字元。此外，也可使用 FINDB 在其他文本串中查找文本串。SEARCH(find_text,within_text,start_num)SEARCHB(find_text,within_text,start_num)SUBSTITUTE在文字串中用 new_text 替代 old_text。如果需要在某一文字串中替換指定的文本，請使用函數 SUBSTITUTE；如果需要在某一文字串中替換指定位置處的任意文本，請使用函數 REPLACE。SUBSTITUTE(text,old_text,new_text,instance_num)T將數值轉換成文本。T(value)TEXT將一數值轉換為按指定數字格式表示的文本。TEXT(value,format_text)TRIM除了單詞之間的單個空格外，清除文本中所有的空格。在從其他應用程序中獲取帶有不規則空格的文本時，可以使用函數 TRIM。TRIM(text)UPPER將文本轉換成大寫形式。UPPER(text)VALUE將代表數字的文字串轉換成數字。VALUE(text)WIDECHAR將單位元組字元轉換為雙位元組字元。WIDECHAR(text)YEN使用 ￥（日圓）貨幣格式將數字轉換成文本，並對指定位置後的數字四捨五入。YEN(number,decimals)附二、日期與時間函數函數名函數說明語法DATE返回代表特定日期的系列數。DATE(year,month,day)DATEDIF計算兩個日期之間的天數、月數或年數。DATEDIF(start_date,end_date,unit)DATEVALUE函數 DATEVALUE 的主要功能是將以文字表示的日期轉換成一個系列數。DATEVALUE(date_text)DAY返回以系列數表示的某日期的天數，用整數 1 到 31 表示。DAY(serial_number)DAYS360按照一年360天的演算法（每個月以 30 天計，一年共計 12 個月），返回兩日期間相差的天數。DAYS360(start_date,end_date,method)EDATE返回指定日期 (start_date) 之前或之後指定月份數的日期系列數。使用函數 EDATE 可以計算與發行日處於一月中同一天的到期日的日期。EDATE(start_date,months)EOMONTH返回 start-date 之前或之後指定月份中最後一天的系列數。用函數 EOMONTH 可計算特定月份中最後一天的時間系列數，用於證券的到期日等計算。EOMONTH(start_date,months)HOUR返回時間值的小時數。即一個介於 0 (12:00 A.M.) 到 23 (11:00 P.M.) 之間的整數。HOUR(serial_number)MINUTE返回時間值中的分鐘。即一個介於 0 到 59 之間的整數。MINUTE(serial_number)MONTH返回以系列數表示的日期中的月份。月份是介於 1（一月）和 12（十二月）之間的整數。MONTH(serial_number)NETWORKDAYS返回參數 start-data 和 end-data 之間完整的工作日數值。工作日不包括周末和專門指定的假期NETWORKDAYS(start_date,end_date,holidays)NOW返回當前日期和時間所對應的系列數。NOW( )SECOND返回時間值的秒數。返回的秒數為 0 至 59 之間的整數。SECOND(serial_number)TIME返回某一特定時間的小數值，函數 TIME 返回的小數值為從 0 到 0.99999999 之間的數值，代表從 0:00:00 (12:00:00 A.M) 到 23:59:59 (11:59:59 P.M) 之間的時間。TIME(hour,minute,second)TIMEVALUE返回由文本串所代表的時間的小數值。該小數值為從 0 到 0.999999999 的數值，代表從 0:00:00 (12:00:00 AM) 到 23:59:59 (11:59:59 PM) 之間的時間。TIMEVALUE(time_text)TODAY返回當前日期的系列數，系列數是 Microsoft Excel 用於日期和時間計算的日期-時間代碼。TODAY( )WEEKDAY返回某日期為星期幾。默認情況下，其值為 1（星期天）到 7（星期六）之間的整數。WEEKDAY(serial_number,return_type)WEEKNUM返回一個數字，該數字代表一年中的第幾周。WEEKNUM(serial_num,return_type)WORKDAY返回某日期（起始日期）之前或之後相隔指定工作日的某一日期的日期值。工作日不包括周末和專門指定的假日。WORKDAY(start_date,days,holidays)YEAR返回某日期的年份。返回值為 1900 到 9999 之間的整數。YEAR(serial_number)YEARFRAC返回 start_date 和 end_date 之間的天數佔全年天數的百分比。YEARFRAC(start_date,end_date,basis)Excel函數應用之查詢與引用函數1、引用的作用在Excel中引用的作用在於標識工作表上的單元格或單元格區域，並指明公式中所使用的數據的位置。通過引用，可以在公式中使用工作表不同部分的數據，或者在多個公式中使用同一單元格的數值。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至其它應用程序中的數據。2、引用的含義關於引用需要了解如下幾種情況的含義：外部引用--不同工作簿中的單元格的引用稱為外部引用。遠程引用--引用其它程序中的數據稱為遠程引用。相對引用--在創建公式時，單元格或單元格區域的引用通常是相對於包含公式的單元格的相對位置。絕對引用--如果在複製公式時不希望 Excel 調整引用，那麼請使用絕對引用。即加入美元符號，如$C$1。3、引用的表示方法關於引用有兩種表示的方法，即A1 和 R1C1 引用樣式。（1）引用樣式一（默認）--A1A1的引用樣式是Excel的默認引用類型。這種類型引用字母標誌列（從 A 到 IV ，共 256 列）和數字標誌行（從 1 到 65536）。這些字母和數字被稱為行和列標題。如果要引用單元格，請順序輸入列字母和行數字。例如，C25 引用了列 C 和行 25 交叉處的單元格。如果要引用單元格區域，請輸入區域左上角單元格的引用、冒號（：）和區域右下角單元格的引用，如A20:C35。（2）引用樣式二--R1C1在 R1C1 引用樣式中，Excel 使用"R"加行數字和"C"加列數字來指示單元格的位置。例如，單元格絕對引用 R1C1 與 A1 引用樣式中的絕對引用 $A$1 等價。如果活動單元格是 A1，則單元格相對引用 R[1]C[1] 將引用下面一行和右邊一列的單元格，或是 B2。在了解了引用的概念後，我們來看看Excel提供的查詢與引用函數。查詢與引用函數可以用來在數據清單或表格中查找特定數值，或者需要查找某一單元格的引用。Excel中一共提供了ADDRESS、AREAS、CHOOSE、COLUMN、COLUMNS、HLOOKUP、HYPERLINK、INDEX、INDIRECT、LOOKUP、MATCH、OFFSET、ROW、ROWS、TRANSPOSE、VLOOKUP 16個查詢與引用函數。下面，筆者將分組介紹一下這些函數的使用方法及簡單應用。一、ADDRESS、COLUMN、ROW1、 ADDRESS用於按照給定的行號和列標，建立文本類型的單元格地址。其語法形式為：ADDRESS(row_num,column_num,abs_num,a1,sheet_text)Row_num指在單元格引用中使用的行號。Column_num指在單元格引用中使用的列標。Abs_num 指明返回的引用類型，1代表絕對引用，2代表絕對行號，相對列標，3代表相對行號，絕對列標，4為相對引用。A1用以指明 A1 或 R1C1 引用樣式的邏輯值。如果 A1 為 TRUE 或省略，函數 ADDRESS 返回 A1 樣式的引用；如果 A1 為 FALSE，函數 ADDRESS 返回 R1C1 樣式的引用。Sheet_text為一文本，指明作為外部引用的工作表的名稱，如果省略 sheet_text，則不使用任何工作表名。簡單說，即ADDRESS（行號，列標，引用類型，引用樣式，工作表名稱）比如，ADDRESS(4,5,1,FALSE,"[Book1]Sheet1") 等於 "[Book1]Sheet1!R4C5"參見圖1圖12、 COLUMN用於返回給定引用的列標。語法形式為：COLUMN(reference)Reference為需要得到其列標的單元格或單元格區域。如果省略 reference，則假定為是對函數 COLUMN 所在單元格的引用。如果 reference 為一個單元格區域，並且函數 COLUMN 作為水平數組輸入，則函數 COLUMN 將 reference 中的列標以水平數組的形式返回。但是Reference 不能引用多個區域。3、 ROW用於返回給定引用的行號。語法形式為：ROW(reference)Reference為需要得到其行號的單元格或單元格區域。 如果省略 reference，則假定是對函數 ROW 所在單元格的引用。如果 reference 為一個單元格區域，並且函數 ROW 作為垂直數組輸入，則函數 ROW 將 reference 的行號以垂直數組的形式返回。但是Reference 不能對多個區域進行引用。二、AREAS、COLUMNS、INDEX、ROWS1、 AREAS用於返回引用中包含的區域個數。其中區域表示連續的單元格組或某個單元格。其語法形式為AREAS(reference)Reference為對某一單元格或單元格區域的引用，也可以引用多個區域。如果需要將幾個引用指定為一個參數，則必須用括弧括起來。2、 COLUMNS用於返回數組或引用的列數。其語法形式為COLUMNS(array)Array為需要得到其列數的數組、數組公式或對單元格區域的引用。3、 ROWS用於返回引用或數組的行數。其語法形式為ROWS(array)Array為需要得到其行數的數組、數組公式或對單元格區域的引用。以上各函數示例見圖2圖24、 INDEX用於返回表格或區域中的數值或對數值的引用。函數 INDEX() 有兩種形式：數組和引用。數組形式通常返回數值或數值數組；引用形式通常返回引用。（1）INDEX(array,row_num,column_num) 返回數組中指定單元格或單元格數組的數值。Array為單元格區域或數組常數。Row_num為數組中某行的行序號，函數從該行返回數值。Column_num為數組中某列的列序號，函數從該列返回數值。需注意的是Row_num 和 column_num 必須指向 array 中的某一單元格，否則，函數 INDEX 返回錯誤值 #REF!。（2）INDEX(reference,row_num,column_num,area_num) 返回引用中指定單元格或單元格區域的引用。Reference為對一個或多個單元格區域的引用。Row_num為引用中某行的行序號，函數從該行返回一個引用。Column_num為引用中某列的列序號，函數從該列返回一個引用。需注意的是Row_num、column_num 和 area_num 必須指向 reference 中的單元格；否則，函數 INDEX 返回錯誤值 #REF!。如果省略 row_num 和 column_num，函數 INDEX 返回由 area_num 所指定的區域。三、INDIRECT、OFFSET1、 INDIRECT用於返回由文字串指定的引用。當需要更改公式中單元格的引用，而不更改公式本身，使用函數 INDIRECT。其語法形式為：INDIRECT(ref_text,a1)其中Ref_text為對單元格的引用，此單元格可以包含 A1-樣式的引用、R1C1-樣式的引用、定義為引用的名稱或對文字串單元格的引用。如果 ref_text 不是合法的單元格的引用，函數 INDIRECT 返回錯誤值 #REF!。A1為一邏輯值，指明包含在單元格 ref_text 中的引用的類型。如果 a1 為 TRUE 或省略，ref_text 被解釋為 A1-樣式的引用。如果 a1 為 FALSE，ref_text 被解釋為 R1C1-樣式的引用。需要注意的是：如果 ref_text 是對另一個工作簿的引用（外部引用），則那個工作簿必須被打開。如果源工作簿沒有打開，函數 INDIRECT 返回錯誤值 #REF!。2、 OFFSET函數用於以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以是一個單元格或者單元格區域，並可以指定返回的行數或者列數。其基本語法形式為：OFFSET(reference, rows, cols, height, width)。其中，reference變數作為偏移量參照系的引用區域(reference必須為對單元格或相連單元格區域的引用，否則，OFFSET函數返回錯誤值＃VALUE!)。rows變數表示相對於偏移量參照系的左上角單元格向上(向下)偏移的行數(例如rows使用2作為參數，表示目標引用區域的左上角單元格比reference低2行)，行數可為正數(代表在起始引用單元格的下方)或者負數(代表在起始引用單元格的上方)或者0(代表起始引用單元格)。cols表示相對於偏移量參照系的左上角單元格向左(向右)偏移的列數(例如cols使用4作為參數，表示目標引用區域的左上角單元格比reference右移4列)，列數可為正數(代表在起始引用單元格的右邊)或者負數(代表在起始引用單元格的左邊)。如果行數或者列數偏移量超出工作表邊緣，OFFSET函數將返回錯誤值＃REF！。height變數表示高度，即所要返回的引用區域的行數(height必須為正數)。width變數表示寬度，即所要返回的引用區域的列數(width必須為正數)。如果省略height或者width，則假設其高度或者寬度與reference相同。例如，公式OFFSET(A1,2,3,4,5)表示比單元格A1靠下2行並靠右3列的4行5列的區域(即D3:H7區域)。由此可見，OFFSET函數實際上並不移動任何單元格或者更改選定區域，它只是返回一個引用。四、HLOOKUP、LOOKUP、MATCH、VLOOKUP1、 LOOKUP函數與MATCH函數LOOKUP函數可以返迴向量（單行區域或單列區域）或數組中的數值。此系列函數用於在表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。當比較值位於數據表的首行，並且要查找下面給定行中的數據時，使用函數 HLOOKUP。當比較值位於要進行數據查找的左邊一列時，使用函數 VLOOKUP。如果需要找出匹配元素的位置而不是匹配元素本身，則應該使用函數 MATCH 而不是函數 LOOKUP。MATCH函數用來返回在指定方式下與指定數值匹配的數組中元素的相應位置。從以上分析可知，查找函數的功能，一是按搜索條件，返回被搜索區域內數據的一個數據值；二是按搜索條件，返回被搜索區域內某一數據所在的位置值。利用這兩大功能，不僅能實現數據的查詢，而且也能解決如"定級"之類的實際問題。2、 LOOKUP用於返迴向量（單行區域或單列區域）或數組中的數值。函數 LOOKUP 有兩種語法形式：向量和數組。（1） 向量形式函數 LOOKUP 的向量形式是在單行區域或單列區域（向量）中查找數值，然後返回第二個單行區域或單列區域中相同位置的數值。其基本語法形式為LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_value為函數 LOOKUP 在第一個向量中所要查找的數值。Lookup_value 可以為數字、文本、邏輯值或包含數值的名稱或引用。Lookup_vector為只包含一行或一列的區域。Lookup_vector 的數值可以為文本、數字或邏輯值。需要注意的是Lookup_vector 的數值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否則，函數 LOOKUP 不能返回正確的結果。文本不區分大小寫。Result_vector 只包含一行或一列的區域，其大小必須與 lookup_vector 相同。如果函數 LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小於或等於 lookup_value 的最大數值。如果 lookup_value 小於 lookup_vector 中的最小值，函數 LOOKUP 返回錯誤值 #N/A。示例詳見圖3圖3（2） 數組形式函數 LOOKUP 的數組形式在數組的第一行或第一列查找指定的數值，然後返回數組的最後一行或最後一列中相同位置的數值。通常情況下，最好使用函數 HLOOKUP 或函數 VLOOKUP 來替代函數 LOOKUP 的數組形式。函數 LOOKUP 的這種形式主要用於與其他電子表格兼容。關於LOOKUP的數組形式的用法在此不再贅述，感興趣的可以參看Excel的幫助。3、 HLOOKUP與VLOOKUPHLOOKUP用於在表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。VLOOKUP用於在表格或數值數組的首列查找指定的數值，並由此返回表格或數組當前行中指定列處的數值。當比較值位於數據表的首行，並且要查找下面給定行中的數據時，請使用函數 HLOOKUP。當比較值位於要進行數據查找的左邊一列時，請使用函數 VLOOKUP。語法形式為：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，Lookup_value表示要查找的值，它必須位於自定義查找區域的最左列。Lookup_value 可以為數值、引用或文字串。Table_array查找的區域，用於查找數據的區域，上面的查找值必須位於這個區域的最左列。可以使用對區域或區域名稱的引用。Row_index_num為 table_array 中待返回的匹配值的行序號。Row_index_num 為 1 時，返回 table_array 第一行的數值，row_index_num 為 2 時，返回 table_array 第二行的數值，以此類推。Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推.Range_lookup為一邏輯值，指明函數 HLOOKUP 查找時是精確匹配，還是近似匹配。下面詳細介紹一下VLOOKUP函數的應用。簡言之，VLOOKUP函數可以根據搜索區域內最左列的值，去查找區域內其它列的數據，並返回該列的數據，對於字母來說，搜索時不分大小寫。所以，函數VLOOKUP的查找可以達到兩種目的：一是精確的查找。二是近似的查找。下面分別說明。（1） 精確查找--根據區域最左列的值，對其它列的數據進行精確的查找示例：創建工資表與工資條首先建立員工工資表圖4然後，根據工資表創建各個員工的工資條，此工資條為應用Vlookup函數建立。以員工Sandy（編號A001）的工資條創建為例說明。第一步，拷貝標題欄第二步，在編號處（A21）寫入A001第三步，在姓名（B21）創建公式=VLOOKUP($A21,$A$3:$H$12,2,FALSE)語法解釋：在$A$3:$H$12範圍內（即工資表中）精確找出與A21單元格相符的行，並將該行中第二列的內容計入單元格中。第四步，以此類推，在隨後的單元格中寫入相應的公式。圖5（2） 近似的查找--根據定義區域最左列的值，對其它列數據進行不精確值的查找示例：按照項目總額不同提取相應比例的獎金第一步，建立一個項目總額與獎金比例的對照表，如圖6所示。項目總額的數字均為大於情況。即項目總額在0~5000元時，獎金比例為1%，以此類推。圖6第二步 假定某項目的項目總額為13000元，在B11格中輸入公式=VLOOKUP(A11,$A$4:$B$8,2,TRUE)即可求得具體的獎金比例為5%，如圖7。圖74、 MATCH函數MATCH函數有兩方面的功能，兩種操作都返回一個位置值。一是確定區域中的一個值在一列中的準確位置，這種精確的查詢與列表是否排序無關。二是確定一個給定值位於已排序列表中的位置，這不需要準確的匹配.語法結構為：MATCH(lookup_value,lookup_array,match_type)lookup_value為要搜索的值。lookup_array：要查找的區域(必須是一行或一列)。match_type：匹配形式，有0、1和－1三種選擇："0"表示一個準確的搜索。"1"表示搜索小於或等於查換值的最大值，查找區域必須為升序排列。"－1"表示搜索大於或等於查找值的最小值，查找區域必須降序排開。以上的搜索，如果沒有匹配值，則返回＃N/A。五、HYPERLINK所謂HYPERLINK，也就是創建快捷方式，以打開文檔或網路驅動器，甚至INTERNET地址。通俗地講，就是在某個單元格中輸入此函數之後，可以到您想去的任何位置。在某個Excel文檔中，也許您需要引用別的Excel文檔或Word文檔等等，其步驟和方法是這樣的：（1）選中您要輸入此函數的單元格，比如B6。（2）單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇"常用"，在"函數名"框中選擇HYPERLINK，此時在對話框的底部將出現該函數的簡短解釋。（3）單擊"確定"後將彈出HYPERLINK函數參數設置對話框。（4）在"Link_location"中鍵入要鏈接的文件或INTERNET地址，比如："c:my documentsExcel函數.doc"；在"Friendly_name"中鍵入"Excel函數"（這裡是假設我們要打開的文檔位於c:my documents下的文件"Excel函數.doc"）。（5）單擊"確定"回到您正編輯的Excel文檔，此時再單擊B6單元格就可立即打開用Word編輯的會議紀要文檔。HYPERLINK函數用於創建各種快捷方式，比如打開文檔或網路驅動器，跳轉到某個網址等。說得誇大一點，在某個單元格中輸入此函數之後，可以跳到我們想去的任何位置。六、其他（CHOOSE、TRANSPOSE）1、CHOOSE函數函數CHOOSE可以使用 index_num 返回數值參數清單中的數值。使用函數 CHOOSE 可以基於索引號返回多達 29 個待選數值中的任一數值。語法形式為：CHOOSE(index_num,value1,value2,...)Index_num用以指明待選參數序號的參數值。Index_num 必須為 1 到 29 之間的數字、或者是包含數字 1 到 29 的公式或單元格引用。Value1,value2,... 為 1 到 29 個數值參數，函數 CHOOSE 基於 index_num，從中選擇一個數值或執行相應的操作。參數可以為數字、單元格引用，已定義的名稱、公式、函數或文本。2、TRANSPOSE函數TRANSPOSE用於返回區域的轉置。函數 TRANSPOSE 必須在某個區域中以數組公式的形式輸入，該區域的行數和列數分別與 array 的列數和行數相同。使用函數 TRANSPOSE 可以改變工作表或宏表中數組的垂直或水平走向。語法形式為TRANSPOSE(array)Array為需要進行轉置的數組或工作表中的單元格區域。所謂數組的轉置就是，將數組的第一行作為新數組的第一列，數組的第二行作為新數組的第二列，以此類推。示例，將原來為橫向排列的業績錶轉置為縱向排列。圖8第一步，由於需要轉置的為多個單元格形式，因此需要以數組公式的方法輸入公式。故首先選定需轉置的範圍。此處我們設定轉置後存放的範圍為A9.B14.第二步，單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇"查找與引用函數"框中選擇TRANSPOSE，此時在對話框的底部將出現該函數的簡短解釋。單擊"確定"後將彈出TRANSPOSE函數參數設置對話框。圖9第三步，選擇數組的範圍即A2.F3第四步，由於此處是以數組公式輸入，因此需要按 CRTL+SHIFT+ENTER 組合鍵來確定為數組公式，此時會在公式中顯示"{}"。隨即轉置成功，如圖10所示。圖10以上我們介紹了Excel的查找與引用函數，此類函數的靈活應用對於減少重複數據的錄入是大有裨益的。此處只做了些拋磚引玉的示例，相信大家會在實際運用中想出更具實用性的應用方法。Excel函數應用之統計函數Excel的統計工作表函數用於對數據區域進行統計分析。例如，統計工作表函數可以用來統計樣本的方差、數據區間的頻率分布等。是不是覺得好像是很專業範疇的東西？是的，統計工作表函數中提供了很多屬於統計學範疇的函數，但也有些函數其實在你我的日常生活中是很常用的，比如求班級平均成績，排名等。在本文中，主要介紹一些常見的統計函數，而屬於統計學範疇的函數不在此贅述，詳細的使用方法可以參考Excel幫助及相關的書籍。在介紹統計函數之前，請大家先看一下附表中的函數名稱。是不是發現有些函數是很類似的，只是在名稱中多了一個字母A？比如，AVERAGE與AVERAGEA；COUNT與COUNTA。基本上，名稱中帶A的函數在統計時不僅統計數字，而且文本和邏輯值（如TRUE 和 FALSE）也將計算在內。在下文中筆者將主要介紹不帶A的幾種常見函數的用法。一、用於求平均值的統計函數AVERAGE、TRIMMEAN1、求參數的算術平均值函數AVERAGE語法形式為AVERAGE(number1,number2, ...)其中Number1, number2, ...為要計算平均值的 1～30 個參數。這些參數可以是數字，或者是涉及數字的名稱、數組或引用。如果數組或單元格引用參數中有文字、邏輯值或空單元格，則忽略其值。但是，如果單元格包含零值則計算在內。2、求數據集的內部平均值TRIMMEAN函數TRIMMEAN先從數據集的頭部和尾部除去一定百分比的數據點，然後再求平均值。當希望在分析中剔除一部分數據的計算時，可以使用此函數。比如，我們在計算選手平均分數中常用去掉一個最高分，去掉一個最低分，XX號選手的最後得分，就可以使用該函數來計算。語法形式為TRIMMEAN(array,percent)其中Array為需要進行篩選並求平均值的數組或數據區域。Percent為計算時所要除去的數據點的比例，例如，如果 percent = 0.2，在 20 個數據點的集合中，就要除去 4 個數據點（20 x 0.2），頭部除去 2 個，尾部除去 2 個。函數 TRIMMEAN 將除去的數據點數目向下舍為最接近的 2 的倍數。3、舉例說明：示例中也列舉了帶A的函數AVERAGEA的求解方法。求選手Annie的參賽分數。在這裡，我們先假定已經將該選手的分數進行了從高到底的排序，在後面的介紹中我們將詳細了解排序的方法。圖1二、用於求單元格個數的統計函數COUNT語法形式為COUNT(value1,value2, ...)其中Value1, value2, ...為包含或引用各種類型數據的參數（1～30個），但只有數字類型的數據才被計數。函數 COUNT 在計數時，將把數字、空值、邏輯值、日期或以文字代表的數計算進去；但是錯誤值或其他無法轉化成數字的文字則被忽略。如果參數是一個數組或引用，那麼只統計數組或引用中的數字；數組中或引用的空單元格、邏輯值、文字或錯誤值都將忽略。如果要統計邏輯值、文字或錯誤值，應當使用函數 COUNTA。舉例說明COUNT函數的用途，示例中也列舉了帶A的函數COUNTA的用途。仍以上例為例，要計算一共有多少評委參與評分（用函數COUNTA），以及有幾個評委給出了有效分數（用函數COUNT）。圖2三、求區域中數據的頻率分布FREQUENCY由於函數 FREQUENCY 返回一個數組，必須以數組公式的形式輸入。語法形式為FREQUENCY(data_array,bins_array)其中Data_array為一數組或對一組數值的引用，用來計算頻率。如果 data_array 中不包含任何數值，函數 FREQUENCY 返回零數組。Bins_array為一數組或對數組區域的引用，設定對 data_array 進行頻率計算的分段點。如果 bins_array 中不包含任何數值，函數 FREQUENCY 返回 data_array 元素的數目。看起來FREQUENCY的用法蠻複雜的，但其用處很大。比如可以計算不同工資段的人員分布，公司員工的年齡分布，學生成績的分布情況等。這裡以具體示例說明其基本的用法。以計算某公司的員工年齡分布情況為例說明。在工作表裡列出了員工的年齡。這些年齡為 28、25、31、21、44、33、22 和 35，並分別輸入到單元格 C4:C11。這一列年齡就是 data_array。Bins_array 是另一列用來對年齡分組的區間值。在本例中，bins_array 是指 C13:C16 單元格，分別含有值 25、30、35、和 40。以數組形式輸入函數 FREQUENCY，就可以計算出年齡在 25歲以下、26~30歲、31~35歲、36~40歲和40歲以上各區間中的數目。本例中選擇了5個垂直相鄰的單元格後，即以數組公式輸入下面的公式。返回的數組中的元素個數比 bins_array（數組）中的元素個數多 1。第五個數字1表示大於最高間隔 (40) 的數值（44）的個數。函數 FREQUENCY 忽略空白單元格和文本值。{=FREQUENCY(C4:C11,C13:C16)}等於 {2;2;2;1;1}圖3四、一組用於求數據集的滿足不同要求的數值的函數1、求數據集的最大值MAX與最小值MIN這兩個函數MAX、MIN就是用來求解數據集的極值（即最大值、最小值）。函數的用法非常簡單。語法形式為函數（number1,number2,...），其中Number1,number2,... 為需要找出最大數值的 1 到 30 個數值。如果要計算數組或引用中的空白單元格、邏輯值或文本將被忽略。因此如果邏輯值和文本不能忽略，請使用帶A的函數MAXA或者MINA 來代替。2、求數據集中第K個最大值LARGE與第k個最小值SMALL這兩個函數LARGE、SMALL與MAX、MIN非常想像，區別在於它們返回的不是極值，而是第K個值。語法形式為：函數(array,k)，其中Array為需要找到第 k 個最小值的數組或數字型數據區域。K為返回的數據在數組或數據區域里的位置（如果是LARGE為從大到小排，若為SMALL函數則從小到大排）。說到這，大家可以想得到吧。如果K=1或者K=n（假定數據集中有n個數據）的時候，是不是就可以返回數據集的最大值或者最小值了呢。3、 求數據集中的中位數MEDIANMEDIAN函數返回給定數值集合的中位數。所謂中位數是指在一組數據中居於中間的數，換句話說，在這組數據中，有一半的數據比它大，有一半的數據比它小。語法形式為MEDIAN(number1,number2, ...)其中Number1, number2,...是需要找出中位數的 1 到 30 個數字參數。如果數組或引用參數中包含有文字、邏輯值或空白單元格，則忽略這些值，但是其值為零的單元格會計算在內。需要注意的是，如果參數集合中包含有偶數個數字，函數 MEDIAN 將返回位於中間的兩個數的平均值。4、 求數據集中出現頻率最多的數MODEMODE函數用來返回在某一數組或數據區域中出現頻率最多的數值。跟 MEDIAN 一樣，MODE 也是一個位置測量函數。語法形式為MODE(number1,number2, ...)其中Number1, number2, ... 是用於眾數（眾數指在一組數值中出現頻率最高的數值）計算的 1 到 30 個參數，也可以使用單一數組（即對數組區域的引用）來代替由逗號分隔的參數。5、 以上函數的示例以某單位年終獎金分配表為例說明。在示例中，我們將利用這些函數求解該單位年終獎金分配中的最高金額、最低金額、平均金額、中間金額、眾數金額以及第二高金額等。詳細的公式寫法可從圖中清楚的看出，在此不再贅述。圖4五、用來排位的函數RANK、PERCENTRANK1、一個數值在一組數值中的排位的函數RANK數值的排位是與數據清單中其他數值的相對大小，當然如果數據清單已經排過序了，則數值的排位就是它當前的位置。數據清單的排序可以使用Excel提供的排序功能完成。語法形式為RANK(number,ref,order) 其中Number為需要找到排位的數字；Ref 為包含一組數字的數組或引用。Order為一數字用來指明排位的方式。如果 order 為 0 或省略，則Excel 將 ref 當作按降序排列的數據清單進行排位。如果 order 不為零，Microsoft Excel 將 ref 當作按升序排列的數據清單進行排位。需要說明的是，函數 RANK 對重複數的排位相同。但重複數的存在將影響後續數值的排位。嗯，這就好像並列第幾的概念啊。例如，在一列整數里，如果整數 10 出現兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數值）。2、求特定數值在一個數據集中的百分比排位的函數PERCENTRANK此PERCENTRANK函數可用於查看特定數據在數據集中所處的位置。例如，可以使用函數 PERCENTRANK 計算某個特定的能力測試得分在所有的能力測試得分中的位置。語法形式為PERCENTRANK(array,x,significance) 其中Array為彼此間相對位置確定的數字數組或數字區域。X為數組中需要得到其排位的值。Significance為可選項，表示返回的百分數值的有效位數。如果省略，函數 PERCENTRANK 保留 3 位小數。3、與排名有關的示例仍以某單位的年終獎金分配為例說明，這裡以員工Annie的排名為例說明公式的寫法。獎金排名的公式寫法為：=RANK(C3,$C$3:$C$12)百分比排名的公式寫法為：=PERCENTRANK($C$3:$C$12,C3)圖5以上我們介紹了Excel統計函數中比較常用的幾種函數，更多的涉及專業領域的統計函數可以參看附表以及各種相關的統計學書籍。附表：函數名稱函數說明語法形式AVEDEV返回一組數據與其均值的絕對偏差的平均值，即離散度。AVEDEV(number1,number2, ...)AVERAGE返回參數算術平均值。AVERAGE(number1,number2, ...)AVERAGEA計算參數清單中數值的平均值（算數平均值）。不僅數字，而且文本和邏輯值（如TRUE 和 FALSE）也將計算在內。AVERAGEA(value1,value2,...)BETADIST返回 Beta 分布累積函數的函數值。Beta 分布累積函數通常用於研究樣本集合中某些事物的發生和變化情況。BETADIST(x,alpha,beta,A,B)BETAINV返回 beta 分布累積函數的逆函數值。即，如果 probability = BETADIST(x,...)，則 BETAINV(probability,...) = x。beta 分布累積函數可用於項目設計，在給定期望的完成時間和變化參數後，模擬可能的完成時間。BETAINV(probability,alpha,beta,A,B)BINOMDIST返回一元二項式分布的概率值。BINOMDIST(number_s,trials,probability_s,cumulative)CHIDIST返回 γ2 分布的單尾概率。γ2 分布與 γ2 檢驗相關。使用 γ2 檢驗可以比較觀察值和期望值。CHIDIST(x,degrees_freedom)CHIINV返回 γ2 分布單尾概率的逆函數。CHIINV(probability,degrees_freedom)CHITEST返回獨立性檢驗值。函數 CHITEST 返回 γ2 分布的統計值及相應的自由度。CHITEST(actual_range,expected_range)CONFIDENCE返回總體平均值的置信區間。置信區間是樣本平均值任意一側的區域。CONFIDENCE(alpha,standard_dev,size)CORREL返回單元格區域 array1 和 array2 之間的相關係數。使用相關係數可以確定兩種屬性之間的關係。CORREL(array1,array2)COUNT返回參數的個數。利用函數 COUNT 可以計算數組或單元格區域中數字項的個數。COUNT(value1,value2, ...)COUNTA返回參數組中非空值的數目。利用函數COUNTA 可以計算數組或單元格區域中數據項的個數。COUNTA(value1,value2, ...)COVAR返回協方差，即每對數據點的偏差乘積的平均數，利用協方差可以決定兩個數據集之間的關係。COVAR(array1,array2)CRITBINOM返回使累積二項式分布大於等於臨界值的最小值。此函數可以用於質量檢驗。CRITBINOM(trials,probability_s,alpha)DEVSQ返回數據點與各自樣本均值偏差的平方和。DEVSQ(number1,number2,...)EXPONDIST返回指數分布。使用函數 EXPONDIST 可以建立事件之間的時間間隔模型。EXPONDIST(x,lambda,cumulative)FDIST返回 F 概率分布。使用此函數可以確定兩個數據系列是否存在變化程度上的不同。FDIST(x,degrees_freedom1,degrees_freedom2)FINV返回 F 概率分布的逆函數值。FINV(probability,degrees_freedom1,degrees_freedom2)FISHER返回點 x 的 Fisher 變換。該變換生成一個近似正態分布而非偏斜的函數。FISHER(x)FISHERINV返回 Fisher 變換的逆函數值。使用此變換可以分析數據區域或數組之間的相關性。FISHERINV(y)FORECAST根據給定的數據計算或預測未來值。FORECAST(x,known_y"s,known_x"s)FREQUENCY以一列垂直數組返回某個區域中數據的頻率分布。FREQUENCY(data_array,bins_array)FTEST返回 F 檢驗的結果。F 檢驗返回的是當數組 1 和數組 2 的方差無明顯差異時的單尾概率。可以使用此函數來判斷兩個樣本的方差是否不同。FTEST(array1,array2)GAMMADIST返回伽瑪分布。可以使用此函數來研究具有偏態分布的變數。伽瑪分布通常用於排隊分析。GAMMADIST(x,alpha,beta,cumulative)GAMMAINV返回伽瑪分布的累積函數的逆函數。GAMMAINV(probability,alpha,beta)GAMMALN返回伽瑪函數的自然對數，Γ(x)。GAMMALN(x)GEOMEAN返回正數數組或數據區域的幾何平均值。GEOMEAN(number1,number2, ...)GROWTH根據給定的數據預測指數增長值。GROWTH(known_y"s,known_x"s,new_x"s,const)HARMEAN返回數據集合的調和平均值。調和平均值與倒數的算術平均值互為倒數。HARMEAN(number1,number2, ...)HYPGEOMDIST返回超幾何分布。HYPGEOMDIST(sample_s,number_sample,population_s,number_population)INTERCEPT利用已知的 x 值與 y 值計算直線與 y 軸的截距。INTERCEPT(known_y"s,known_x"s)KURT返回數據集的峰值。KURT(number1,number2, ...)LARGE返回數據集里第 k 個最大值。使用此函數可以根據相對標準來選擇數值。LARGE(array,k)LINEST使用最小二乘法計算對已知數據進行最佳直線擬合，並返回描述此直線的數組。LINEST(known_y"s,known_x"s,const,stats)LOGEST在回歸分析中，計算最符合觀測數據組的指數回歸擬合曲線，並返回描述該曲線的數組。LOGEST(known_y"s,known_x"s,const,stats)LOGINV返回 x 的對數正態分布累積函數的逆函數。LOGINV(probability,mean,standard_dev)LOGNORMDIST返回 x 的對數正態分布的累積函數。LOGNORMDIST(x,mean,standard_dev)MAX返回數據集中的最大數值。MAX(number1,number2,...)MAXA返回參數清單中的最大數值。MAXA(value1,value2,...)MEDIAN返回給定數值集合的中位數。中位數是在一組數據中居於中間的數。MEDIAN(number1,number2, ...)MIN返回給定參數表中的最小值。MIN(number1,number2, ...)MINA返回參數清單中的最小數值。MINA(value1,value2,...)MODE返回在某一數組或數據區域中出現頻率最多的數值。MODE(number1,number2, ...)NEGBINOMDIST返回負二項式分布。NEGBINOMDIST(number_f,number_s,probability_s)NORMDIST返回給定平均值和標準偏差的正態分布的累積函數。NORMDIST(x,mean,standard_dev,cumulative)NORMINV返回給定平均值和標準偏差的正態分布的累積函數的逆函數。NORMINV(probability,mean,standard_dev)NORMSDIST返回標準正態分布的累積函數，該分布的平均值為 0，標準偏差為 1。NORMSDIST(z)NORMSINV返回標準正態分布累積函數的逆函數。該分布的平均值為 0，標準偏差為 1。NORMSINV(probability)PEARSON返回 Pearson（皮爾生）乘積矩相關係數，r，這是一個範圍在 -1.0 到 1.0 之間（包括 -1.0 和 1.0 在內）的無量綱指數，反映了兩個數據集合之間的線性相關程度。PEARSON(array1,array2)PERCENTILE返回數值區域的 K 百分比數值點。可以使用此函數來建立接受閥值。例如，可以確定得分排名在 90 個百分點以上的檢測侯選人。PERCENTILE(array,k)PERCENTRANK返回特定數值在一個數據集中的百分比排位。此函數可用於查看特定數據在數據集中所處的位置。例如，可以使用函數 PERCENTRANK 計算某個特定的能力測試得分在所有的能力測試得分中的位置。PERCENTRANK(array,x,significance)PERMUT返回從給定數目的對象集合中選取的若干對象的排列數。排列可以為有內部順序的對象或為事件的任意集合或子集。排列與組合不同，組合的內部順序無意義。此函數可用於彩票計算中的概率。PERMUT(number,number_chosen)POISSON返回泊松分布。泊松分布通常用於預測一段時間內事件發生的次數，比如一分鐘內通過收費站的轎車的數量。POISSON(x,mean,cumulative)PROB返回一概率事件組中落在指定區域內的事件所對應的概率之和。如果沒有給出 upper_limit，則返回 x _range 內值等於 lower_limit 的概率。PROB(x_range,prob_range,lower_limit,upper_limit)QUARTILE返回數據集的四分位數。四分位數通常用於在銷售額和測量值數據集中對總體進行分組。例如，可以使用函數 QUARTILE 求得總體中前 25% 的收入值。QUARTILE(array,quart)RANK返回一個數值在一組數值中的排位。數值的排位是與數據清單中其他數值的相對大小（如果數據清單已經排過序了，則數值的排位就是它當前的位置）。RANK(number,ref,order)RSQ返回根據 known_y"s 和 known_x"s 中數據點計算得出的 Pearson 乘積矩相關係數的平方。有關詳細信息，請參閱函數 REARSON。R 平方值可以解釋為 y 方差與 x 方差的比例。RSQ(known_y"s,known_x"s)SKEW返回分布的偏斜度。偏斜度反映以平均值為中心的分布的不對稱程度。正偏斜度表示不對稱邊的分布更趨向正值。負偏斜度表示不對稱邊的分布更趨向負值。SKEW(number1,number2,...)SLOPE返回根據 known_y"s 和 known_x"s 中的數據點擬合的線性回歸直線的斜率。斜率為直線上任意兩點的重直距離與水平距離的比值，也就是回歸直線的變化率。SLOPE(known_y"s,known_x"s)SMALL返回數據集中第 k 個最小值。使用此函數可以返回數據集中特定位置上的數值。SMALL(array,k)STANDARDIZE返回以 mean 為平均值，以 standard-dev 為標準偏差的分布的正態化數值。STANDARDIZE(x,mean,standard_dev)STDEV估算樣本的標準偏差。標準偏差反映相對於平均值（mean）的離散程度。STDEV(number1,number2,...)STDEVA估算基於給定樣本的標準偏差。標準偏差反映數值相對於平均值（mean）的離散程度。文本值和邏輯值（如 TRUE 或 FALSE）也將計算在內。STDEVA(value1,value2,...)STDEVP返回以參數形式給出的整個樣本總體的標準偏差。標準偏差反映相對於平均值（mean）的離散程度。STDEVP(number1,number2,...)STDEVPA計算樣本總體的標準偏差。標準偏差反映數值相對於平均值（mean）的離散程度。STDEVPA(value1,value2,...)STEYX返回通過線性回歸法計算 y 預測值時所產生的標準誤差。標準誤差用來度量根據單個 x 變數計算出的 y 預測值的誤差量。STEYX(known_y"s,known_x"s)TDIST返回學生 t- 分布的百分點（概率），t 分布中數值 (x) 是 t 的計算值（將計算其百分點）。t 分布用於小樣本數據集合的假設檢驗。使用此函數可以代替 t 分布的臨界值表。TDIST(x,degrees_freedom,tails)TINV返回作為概率和自由度函數的學生 t 分布的 t 值。TINV(probability,degrees_freedom)TREND返回一條線性回歸擬合線的一組縱坐標值（y 值）。即找到適合給定的數組 known_y"s 和 known_x"s 的直線（用最小二乘法），並返回指定數組 new_x"s 值在直線上對應的 y 值。TREND(known_y"s,known_x"s,new_x"s,const)TRIMMEAN返回數據集的內部平均值。函數 TRIMMEAN 先從數據集的頭部和尾部除去一定百分比的數據點，然後再求平均值。當希望在分析中剔除一部分數據的計算時，可以使用此函數。TRIMMEAN(array,percent)TTEST返回與學生氏- t 檢驗相關的概率。可以使用函數 TTEST 判斷兩個樣本是否可能來自兩個具有相同均值的總體。TTEST(array1,array2,tails,type)VAR估算樣本方差。VAR(number1,number2,...)VARA估算基於給定樣本的方差。不僅數字，文本值和邏輯值（如 TRUE 和 FALSE）也將計算在內。VARA(value1,value2,...)VARP計算樣本總體的方差。VARP(number1,number2,...)VARPA計算樣本總體的方差。不僅數字，文本值和邏輯值（如 TRUE 和 FALSE）也將計算在內。VARPA(value1,value2,...)WEIBULL返回韋伯分布。使用此函數可以進行可靠性分析，比如計算設備的平均故障時間。WEIBULL(x,alpha,beta,cumulative)ZTEST返回 z 檢驗的雙尾 P 值。Z 檢驗根據數據集或數組生成 x 的標準得分，並返回正態分布的雙尾概率。可以使用此函數返回從某總體中抽取特定觀測值的似然估計。ZTEST(array,x,sigma)Excel函數應用之工程函數Excel的工程函數與統計函數類似，都是屬於比較專業範疇的函數。因此，在文中筆者也僅介紹幾種比較常用的工程函數，更多的請參考Excel幫助和專業的書籍。顧名思義，工程工作表函數就是用於工程分析的函數。Excel中一共提供了近40個工程函數。工程工作表函數由"分析工具庫"提供。如果您找不到此類函數的話，可能需要安裝"分析工具庫"。一、"分析工具庫"的安裝如圖所示圖11） 在"工具"菜單中，單擊"載入宏"命令。（2）如果"載入宏"對話框中沒有"分析工具庫"，請單擊"瀏覽"按鈕，定位到"分析工具庫"載入宏文件"Analys32.xll"所在的驅動器和文件夾（通常位於"Microsoft OfficeOfficeLibraryAnalysis"文件夾中）；如果沒有找到該文件，應運行"安裝"程序。（3） 選中"分析工具庫"複選框。二、工程函數的分類在Excel幫助系統中將工程函數大體可分為三種類型，即：（1）對複數進行處理的函數（2）在不同的數字系統（如十進位系統、十六進位系統、八進位系統和二進位系統）間進行數值轉換的函數（3）在不同的度量系統中進行數值轉換的函數在文中為了對函數的解釋更清晰，筆者把工程函數分為如下的六種類型，即：（1）貝賽爾（Bessel）函數（2）在不同的數字系統間進行數值轉換的函數（3）用於篩選數據的函數（4）度量衡轉換函數（5）與積分運算有關的函數（6）對複數進行處理的函數下面逐一的對於這些工程函數進行介紹。1、貝賽爾（Bessel）函數貝賽爾（Bessel）函數是特殊函數中應用最廣泛的一種函數，在理論物理研究、應用數學、大氣科學以及無線電等工程領域都有廣泛的應用。在Excel中一共提供了四個函數，即：BESSELI、BESSELJ 、BESSELK、BESSELY。語法形式為：函數(x,n) 其中，X為參數值，N為函數的階數。如果 n非整數，則截尾取整。需說明的是，如果 x 為非數值型，則貝賽爾（Bessel）函數返回錯誤值 #VALUE!。如果 n 為非數值型，則貝賽爾（Bessel）函數返回錯誤值 #VALUE!。如果 n ＜0，則貝賽爾（Bessel）函數返回錯誤值 #NUM!。2、在不同的數字系統間進行數值轉換的函數Excel工程函數中提供二進位、八進位、十進位與十六進位之間的數值轉換函數。這類工程函數名稱非常容易記憶，只要記住二進位為BIN，八進位為OCT，十進位為DEC，十六進位為HEX。再記住函數名稱中間有個數字2就可以容易的記住這些數值轉換函數了。比如，如果需要將二進位數轉換為十進位，應用的函數為前面BIN，中間加個2，後面為DEC，合起來這個函數就是BIN2DEC。簡單列表為：圖2此類數值轉換函數的語法形式也很容易記憶。比如，將不同進位的數值轉為十進位的語法形式為：函數（number），其中Number為待轉換的某種進位數。又如，將不同進位轉換為其他進位的數值的語法形式為：函數（number,places）其中Number為待轉換的數。Places為所要使用的字元數。當需要在返回的數值前置零時 places 尤其有用。3、用於篩選數據的函數DELTA與GESTEP（1）用以測試兩個數值是否相等的函數DELTADELTA用以測試兩個數值是否相等。如果 number1=number2，則返回 1，否則返回 0。可用此函數篩選一組數據，例如，通過對幾個 DELTA 函數求和，可以計算相等數據對的數目。該函數也稱為 Kronecker Delta 函數。語法形式為DELTA(number1,number2) 其中Number1為第一個參數，Number2為第二個參數。如果省略，假設 Number2 值為零。如果number1或者number2為非數值型，則函數 DELTA 返回錯誤值 #VALUE!。（2）可篩選數據的函數GESTEP使用GESTEP函數可篩選數據。如果 Number 大於等於 step，返回 1，否則返回 0。例如，通過計算多個函數 GESTEP 的返回值，可以檢測出數據集中超過某個臨界值的數據個數。語法形式為：GESTEP(number,step) 其中Number為待測試的數值。Step稱閥值。如果省略 step，則函數 GESTEP 假設其為零。需注意的是，如果任一參數非數值，則函數 GESTEP 返回錯誤值 #VALUE!（3）以考試成績統計為例說明函數的用法例：某院校舉行數學模擬考試，正在進行成績排定。提出的評定方案為求出成績超過90分的考生人數有哪些人。在這裡我們採用GEStep函數來完成統計，首先會為每位考生的成績做標記。超過90分的標記為1，否則為0，然後對所有考生的標記進行匯總，即可求出有多少人超過90分。圖3以1號Annie的成績為例，成績為98分，超90分。具體公式為：=GESTEP(C4,90)4、度量衡轉換函數CONVERTCONVERT函數可以將數字從一個度量系統轉換到另一個度量系統中。語法形式為CONVERT(number,from_unit,to_unit) 其中Number為以 from_units 為單位的需要進行轉換的數值。From_unit為數值 number 的單位。To_unit為結果的單位。函數 CONVERT 中from_unit 和 to_unit的參數接受的附表的文本值。重量和質量From_unit 或 to_unit能量From_unit 或 to_unit克"g"焦耳"J"斯勒格"sg"爾格"e"磅（常衡制）"lbm"熱力學卡"c"U（原子質量單位）"u"IT 卡"cal"盎司（常衡制）"ozm"電子伏"eV"距離From_unit 或 to_unit馬力-小時"HPh"米"m"瓦特-小時"Wh"法定哩"mi"英尺磅"flb"海里"Nmi"BTU"BTU"英寸"in"功率From_unit 或 to_unit英尺"ft"馬力"HP"碼`瓦特"W"埃"ang"磁From_unit 或 to_unit皮卡（1/72 英寸）"Pica"特斯拉"T"時間From_unit 或 to_unit高斯"ga"年"yr"溫度From_unit 或 to_unit日"day"攝氏度"C"小時"hr"華氏度"F"分鐘"mn"開爾文度"K"秒"sec"液體度量From_unit 或 to_unit壓強From_unit 或 to_unit茶匙"tsp"帕斯卡"Pa"湯匙"tbs"大氣壓"atm"液量盎司"oz"毫米汞柱"mmHg"杯"cup"力From_unit 或 to_unitU.S. 品脫"pt"牛頓"N"U.K. 品脫"uk_pt"達因"dyn"夸脫"qt"磅力"lbf"加侖"gal"升"l"5、與積分運算有關的函數ERF與ERFCERF為返回誤差函數在上下限之間的積分。其語法形式為：ERF(lower_limit,upper_limit) 其中，Lower_limit為ERF函數的積分下限。Upper_limit為ERF函數的積分上限。如果省略，默認為零。ERFC為返回從 x 到 ∞（無窮）積分的 ERF 函數的余誤差函數。其語法形式為：ERFC(x) 其中X為ERF函數積分的下限。6、與複數運算有關的函數還記得中學時代學過的複數嗎？是不是還記得當時求複數的模等計算的繁複？Excel的工程函數中提供的多種與複數運算有關的函數，你可以用它來驗證自己的運算結果的正確性啊。關於有哪些函數與複數運算有關，可以察看所附的表格。這裡將以簡單的事例說明函數的使用方法。注意到在工程函數中有一些前綴為im的函數了嗎？這些就是與複數運算有關的函數。舉例，已知複數5+12i，請用函數求解該複數的共軛複數、實係數、虛係數、模等。圖4附表：Excel的工程函數函數名函數說明語法形式BESSELI返回修正 Bessel 函數值，它與用純虛數參數運算時的 Bessel 函數值相等。BESSELI(x,n)BESSELJ返回 Bessel 函數值。BESSELJ(x,n)BESSELK返回修正 Bessel 函數值，它與用純虛數參數運算時的 Bessel 函數值相等。BESSELK(x,n)BESSELY返回 Bessel 函數值，也稱為 Weber 函數或 Neumann 函數。BESSELY(x,n)BIN2DEC將二進位數轉換為十進位數。BIN2DEC(number)BIN2HEX將二進位數轉換為十六進位數。BIN2HEX(number,places)BIN2OCT將二進位數轉換為八進位數。BIN2OCT(number,places)COMPLEX將實係數及虛係數轉換為 x+yi 或 x+yj 形式的複數。COMPLEX(real_num,i_num,suffix)CONVERT將數字從一個度量系統轉換到另一個度量系統中。CONVERT(number,from_unit,to_unit)DEC2BIN將十進位數轉換為二進位數。DEC2BIN(number,places)DEC2HEX將十進位數轉換為十六進位數。DEC2HEX(number,places)DEC2OCT將十進位數轉換為八進位數。DEC2OCT(number,places)DELTA測試兩個數值是否相等。如果 number1=number2，則返回 1，否則返回 0。DELTA(number1,number2)ERF返回誤差函數在上下限之間的積分。ERF(lower_limit,upper_limit)ERFC返回從 x 到 ∞（無窮）積分的 ERF 函數的余誤差函數ERFC(x)GESTEP如果 Number 大於等於 step，返回 1，否則返回 0。使用該函數可篩選數據。GESTEP(number,step)HEX2BIN將十六進位數轉換為二進位數。HEX2BIN(number,places)HEX2DEC將十六進位數轉換為十進位數。HEX2DEC(number)HEX2OCT將十六進位數轉換為八進位數。HEX2OCT(number,places)IMABS返回以 x+yi 或 x+yj 文本格式表示的複數的絕對值（模）。IMABS(inumber)IMAGINARY返回以 x+yi 或 x+yj 文本格式表示的複數的虛係數。IMAGINARY(inumber)IMARGUMENT返回以弧度表示的角IMARGUMENT(inumber)IMCONJUGATE返回以 x+yi 或 x+yj 文本格式表示的複數的共軛複數。IMCONJUGATE(inumber)IMCOS返回以 x+yi 或 x+yj 文本格式表示的複數的餘弦。IMCOS(inumber)IMDIV返回以 x+yi 或 x+yj 文本格式表示的兩個複數的商。IMDIV(inumber1,inumber2)IMEXP返回以 x+yi 或 x+yj 文本格式表示的複數的指數。IMEXP(inumber)IMLN返回以 x+yi 或 x+yj 文本格式表示的複數的自然對數。IMLN(inumber)IMLOG10返回以 x+yi 或 x+yj 文本格式表示的複數的常用對數（以 10 為底數）。IMLOG10(inumber)IMLOG2返回以 x+yi 或 x+yj 文本格式表示的複數的以 2 為底數的對數。IMLOG2(inumber)IMPOWER返回以 x+yi 或 x+yj 文本格式表示的複數的 n 次冪。IMPOWER(inumber,number)IMPRODUCT返回以 x+yi 或 x+yj 文本格式表示的 2 至 29 個複數的乘積。IMPRODUCT(inumber1,inumber2,...)IMREAL返回以 x+yi 或 x+yj 文本格式表示的複數的實係數。IMREAL(inumber)IMSIN返回以 x+yi 或 x+yj 文本格式表示的複數的正弦值。IMSIN(inumber)IMSQRT返回以 x+yi 或 x+yj 文本格式表示的複數的平方根。IMSQRT(inumber)IMSUB返回以 x+yi 或 x+yj 文本格式表示的兩個複數的差。IMSUB(inumber1,inumber2)IMSUM返回以 x+yi 或 x+yj 文本格式表示的兩個或多個複數的和。IMSUM(inumber1,inumber2,...)OCT2BIN將八進位數轉換為二進位數。OCT2BIN(number,places)OCT2DEC將八進位數轉換為十進位數。OCT2DEC(number)OCT2HEX將八進位數轉換為十六進位數。OCT2HEX(number,places)Excel函數應用之財務函數像統計函數、工程函數一樣，在Excel中還提供了許多財務函數。財務函數可以進行一般的財務計算，如確定貸款的支付額、投資的未來值或凈現值，以及債券或息票的價值。這些財務函數大體上可分為四類：投資計算函數、折舊計算函數、償還率計算函數、債券及其他金融函數。它們為財務分析提供了極大的便利。使用這些函數不必理解高級財務知識，只要填寫變數值就可以了。在下文中，凡是投資的金額都以負數形式表示，收益以正數形式表示。在介紹具體的財務函數之前，我們首先來了解一下財務函數中常見的參數：未來值 (fv)--在所有付款發生後的投資或貸款的價值。期間數 (nper)--為總投資（或貸款）期，即該項投資（或貸款）的付款期總數。付款 (pmt)--對於一項投資或貸款的定期支付數額。其數值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。現值 (pv)--在投資期初的投資或貸款的價值。例如，貸款的現值為所借入的本金數額。利率 (rate)--投資或貸款的利率或貼現率。類型 (type)--付款期間內進行支付的間隔，如在月初或月末，用0或1表示。日計數基準類型（basis）--為日計數基準類型。Basis為0 或省略代表US (NASD) 30/360，為1代表實際天數/實際天數，為2代表實際天數/360，為3代表實際天數/365 ，為4代表歐洲30/360。接下來，我們將分別舉例說明各種不同的財務函數的應用。在本文中主要介紹各類型的典型財務函數，更多的財務函數請參看附表及相關書籍。如果下文中所介紹的函數不可用，返回錯誤值 #NAME?，請安裝並載入"分析工具庫"載入宏。操作方法為：1、在"工具"菜單上，單擊"載入宏"。2、在"可用載入宏"列表中，選中"分析工具庫"框，再單擊"確定"。一、投資計算函數投資計算函數可分為與未來值fv有關，與付款pmt有關，與現值pv有關，與複利計算有關及與期間數有關幾類函數。1、與未來值fv有關的函數--FV、FVSCHEDULE2、與付款pmt有關的函數--IPMT、ISPMT、PMT、PPMT3、與現值pv有關的函數--NPV、PV、XNPV4、與複利計算有關的函數--EFFECT、NOMINAL5、與期間數有關的函數--NPER在投資計算函數中，筆者將重點介紹FV、NPV、PMT、PV函數。（一） 求某項投資的未來值FV在日常工作與生活中，我們經常會遇到要計算某項投資的未來值的情況，此時利用Excel函數FV進行計算後，可以幫助我們進行一些有計劃、有目的、有效益的投資。FV函數基於固定利率及等額分期付款方式，返回某項投資的未來值。語法形式為FV(rate,nper,pmt,pv,type)。其中rate為各期利率，是一固定值，nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數，pv為各期所應付給（或得到）的金額，其數值在整個年金期間（或投資期內）保持不變，通常Pv包括本金和利息，但不包括其它費用及稅款，pv為現值，或一系列未來付款當前值的累積和，也稱為本金，如果省略pv，則假設其值為零，type為數字0或1，用以指定各期的付款時間是在期初還是期末，如果省略t，則假設其值為零。例如：假如某人兩年後需要一筆比較大的學習費用支出，計劃從現在起每月初存入2000元，如果按年利2.25%，按月計息（月利為2.25%/12），那麼兩年以後該賬戶的存款額會是多少呢？公式寫為：FV(2.25%/12, 24,-2000,0,1)圖1（二） 求投資的凈現值NPVNPV函數基於一系列現金流和固定的各期貼現率，返回一項投資的凈現值。投資的凈現值是指未來各期支出（負值）和收入（正值）的當前值的總和。語法形式為：NPV(rate,value1,value2, ...) 其中，rate為各期貼現率，是一固定值；value1,value2,...代表1到29筆支出及收入的參數值，value1,value2,...所屬各期間的長度必須相等，而且支付及收入的時間都發生在期末。需要注意的是：NPV按次序使用value1,value2，來注釋現金流的次序。所以一定要保證支出和收入的數額按正確的順序輸入。如果參數是數值、空白單元格、邏輯值或表示數值的文字表示式，則都會計算在內；如果參數是錯誤值或不能轉化為數值的文字，則被忽略，如果參數是一個數組或引用，只有其中的數值部分計算在內。忽略數組或引用中的空白單元格、邏輯值、文字及錯誤值。例如，假設開一家電器經銷店。初期投資￥200,000，而希望未來五年中各年的收入分別為￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的貼現率是8%（相當於通貸膨脹率或競爭投資的利率），則投資的凈現值的公式是：=NPV(A2, A4:A8)+A3在該例中，一開始投資的￥200,000並不包含在v參數中，因為此項付款發生在第一期的期初。假設該電器店的營業到第六年時，要重新裝修門面，估計要付出￥40,000，則六年後書店投資的凈現值為：=NPV(A2, A4:A8, A9)+A3如果期初投資的付款發生在期末，則 投資的凈現值的公式是：=NPV(A2, A3:A8)圖2（三） 求貸款分期償還額PMTPMT函數基於固定利率及等額分期付款方式，返回投資或貸款的每期付款額。PMT函數可以計算為償還一筆貸款，要求在一定周期內支付完時，每次需要支付的償還額，也就是我們平時所說的"分期付款"。比如借購房貸款或其它貸款時，可以計算每期的償還額。其語法形式為：PMT(rate,nper,pv,fv,type) 其中，rate為各期利率，是一固定值，nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數，pv為現值，或一系列未來付款當前值的累積和，也稱為本金，fv為未來值，或在最後一次付款後希望得到的現金餘額，如果省略fv，則假設其值為零（例如，一筆貸款的未來值即為零），type為0或1，用以指定各期的付款時間是在期初還是期末。如果省略type，則假設其值為零。例如，需要10個月付清的年利率為8%的￥10,000貸款的月支額為：PMT（8%/12,10,10000） 計算結果為：-￥1,037.03。（四） 求某項投資的現值PVPV函數用來計算某項投資的現值。年金現值就是未來各期年金現在的價值的總和。如果投資回收的當前價值大於投資的價值，則這項投資是有收益的。其語法形式為：PV(rate,nper,pmt,fv,type) 其中Rate為各期利率。Nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數。Pmt為各期所應支付的金額，其數值在整個年金期間保持不變。通常 pmt 包括本金和利息，但不包括其他費用及稅款。Fv 為未來值，或在最後一次支付後希望得到的現金餘額，如果省略 fv，則假設其值為零（一筆貸款的未來值即為零）。Type用以指定各期的付款時間是在期初還是期末。例如，假設要購買一項保險年金，該保險可以在今後二十年內於每月末回報￥600。此項年金的購買成本為80,000，假定投資回報率為8%。那麼該項年金的現值為：PV(0.08/12, 12*20,600,0) 計算結果為：￥-71,732.58。負值表示這是一筆付款，也就是支出現金流。年金（￥-71,732.58）的現值小於實際支付的（￥80,000）。因此，這不是一項合算的投資。圖3二、 折舊計算函數折舊計算函數主要包括AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。這些函數都是用來計算資產折舊的，只是採用了不同的計算方法。這裡，對於具體的計算公式不再贅述，具體選用哪種折舊方法，則須視各單位情況而定。三、償還率計算函數償還率計算函數主要用以計算內部收益率，包括IRR、MIRR、RATE和XIRR幾個函數。（一） 返回內部收益率的函數--IRRIRR函數返回由數值代表的一組現金流的內部收益率。這些現金流不一定必須為均衡的，但作為年金，它們必須按固定的間隔發生，如按月或按年。內部收益率為投資的回收利率，其中包含定期支付（負值）和收入（正值）。其語法形式為IRR(values,guess) 其中values為數組或單元格的引用，包含用來計算內部收益率的數字，values必須包含至少一個正值和一個負值，以計算內部收益率，函數IRR根據數值的順序來解釋現金流的順序，故應確定按需要的順序輸入了支付和收入的數值，如果數組或引用包含文本、邏輯值或空白單元格，這些數值將被忽略；guess為對函數IRR計算結果的估計值，excel使用迭代法計算函數IRR從guess開始，函數IRR不斷修正收益率，直至結果的精度達到0.00001%，如果函數IRR經過20次迭代，仍未找到結果，則返回錯誤值#NUM！，在大多數情況下，並不需要為函數IRR的計算提供guess值，如果省略guess，假設它為0.1（10%）。如果函數IRR返回錯誤值#NUM！，或結果沒有靠近期望值，可以給guess換一個值再試一下。例如，如果要開辦一家服裝商店，預計投資為￥110,000，並預期為今後五年的凈收益為：￥15,000、￥21,000、￥28,000、￥36,000和￥45,000。分別求出投資兩年、四年以及五年後的內部收益率。圖4在工作表的B1：B6輸入數據"函數.xls"所示，計算此項投資四年後的內部收益率IRR（B1：B5）為-3.27%；計算此項投資五年後的內部收益率IRR（B1：B6）為8.35%;計算兩年後的內部收益率時必須在函數中包含guess，即IRR（B1：B3，-10%）為-48.96%。（二） 用RATE函數計算某項投資的實際贏利在經濟生活中，經常要評估當前某項投資的運作情況，或某個新企業的現狀。例如某承包人建議你貸給他30000元，用作公共工程建設資金，並同意每年付給你9000元，共付五年，以此作為這筆貸款的最低回報。那麼你如何去決策這筆投資？如何知道這項投資的回報率呢？對於這種周期性償付或是一次償付完的投資，用RATE函數可以很快地計算出實際的贏利。其語法形式為RATE(nper,pmt,pv,fv,type,guess)。具體操作步驟如下：1、選取存放數據的單元格，並按上述相似的方法把此單元格指定為"百分數"的格式。2、插入函數RATE，打開"粘貼函數"對話框。3、在"粘貼函數"對話框中，在"Nper"中輸入償還周期5(年),在"Pmt"中輸入7000(每年的回報額)，在"Pv"中輸入－30000(投資金額)。即公式為=RATE(5,9000,-30000)4、確定後計算結果為15.24％。這就是本項投資的每年實際贏利，你可以根據這個值判斷這個贏利是否滿意，或是決定投資其它項目，或是重新談判每年的回報。四、債券及其他金融函數債券及其他金融函數又可分為計算本金、利息的函數，與利息支付時間有關的函數、與利率收益率有關的函數、與修正期限有關的函數、與有價證券有關的函數以及與證券價格表示有關的函數。1、計算本金、利息的函數--CUMPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM2、與利息支付時間有關的函數--COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD3、 與利率收益率有關的函數--INTRATE、ODDFYIELD、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT4、與修正期限有關的函數--DURATION、MDURATION5、與有價證券有關的函數--DISC、ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT、RECEIVED6、與證券價格表示有關的函數--DOLLARDE、DOLLARFR在債券及其他金融函數中，筆者將重點介紹函數ACCRINT、CUMPRINC、DISC。（一）求定期付息有價證券的應計利息的函數ACCRINTACCRINT函數可以返回定期付息有價證券的應計利息。其語法形式為ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)其中issue為有價證券的發行日，first_interest為有價證券的起息日，settlement為有價證券的成交日，即在發行日之後，有價證券賣給購買者的日期，rate為有價證券的年息票利率，par為有價證券的票麵價值，如果省略par，函數ACCRINT就會自動將par設置為￥1000，frequency為年付息次數，basis為日計數基準類型。例如，某國庫券的交易情況為：發行日為2008年3月1日；起息日為2008年8月31日；成交日為2008年5月1日，息票利率為10.0%；票麵價值為￥1,000；按半年期付息；日計數基準為30/360，那麼應計利息為：圖5（二）求本金數額CUMPRINCCUMPRINC函數用於返回一筆貨款在給定的st到en期間累計償還的本金數額。其語法形式為CUMPRINC(rate,nper,pv,start_period,end_period,type) 其中rate為利率，nper為總付款期數，pv為現值，start_period為計算中的首期，付款期數從1開始計數，end_period為計算中的末期，type為付款時間類型。例如，一筆住房抵押貸款的交易情況如下：年利率為9.00%；期限為30年；現值為￥125，000。由上述已知條件可以計算出：r=9.00%/12=0.0075，np=30*12=360。圖6那麼該筆貸款在第下半年償還的全部本金之中（第7期到第12期）為： =CUMPRINC(A2/12,A3*12,A4,7,12,0)計算結果為：-436.568194。該筆貸款在第一個月償還的本金為：=CUMPRINC(A2/12,A3*12,A4,1,1,0)計算結果為：-68.27827118。（三） 求有價證券的貼現率DISCDISC函數返回有價證券的貼現率。其語法形式為DISC(settlement,maturity,pr,redemption,basis) 其中settlement為有價證券的成交日，即在發行日之後，有價證券賣給購買者的日期，maturity為有價證券的到日期，到期日是有價證券有效期截止時的日期，pr為面值為"￥100"的有價證券的價格，redemption為面值為"￥100"的有價證券的清償價格，basis為日計數基準類型。例如：某債券的交易情況如下：成交日為99年3月18日，到期日為99年8月7日，價格為￥48.834，清償價格為￥52，日計數基準為實際天數/360。那麼該債券的貼現率為： DISC("99/3/18","99/8/7",48.834,52,2) 計算結果為：0.154355363。函數名稱函數說明語法形式ACCRINT返回定期付息有價證券的應計利息。ACCRINT(issue,first_interest, settlement,rate,par,frequency, basis)ACCRINTM返回到期一次性付息有價證券的應計利息。ACCRINTM(issue,maturity,rate, par,basis)AMORDEGRC返回每個會計期間的折舊值。此函數是為法國會計系統提供的。AMORDEGRC(cost,date_purchased, first_period,salvage,period, rate,basis)AMORLINC返回每個會計期間的折舊值，該函數為法國會計系統提供。AMORLINC(cost,date_purchased, first_period,salvage,period, rate,basis)COUPDAYBS返回當前付息期內截止到成交日的天數。COUPDAYBS(settlement,maturity, frequency, basis)COUPDAYS返回成交日所在的付息期的天數。COUPDAYS(settlement,maturity, frequency, basis)COUPDAYSNC返回從成交日到下一付息日之間的天數。COUPDAYSNC(settlement,maturity, frequency, basis)COUPNCD返回成交日過後的下一付息日的日期。COUPNCD(settlement,maturity, frequency, basis)COUPNUM返回成交日和到期日之間的利息應付次數，向上取整到最近的整數。COUPNUM(settlement,maturity, frequency, basis)COUPPCD返回成交日之前的上一付息日的日期。COUPPCD(settlement,maturity, frequency, basis)CUMIPMT返回一筆貸款在給定的 start-period 到 end-period 期間累計償還的利息數額。CUMIPMT(rate,nper,pv,start_period, end_period,type)CUMPRINC返回一筆貸款在給定的 start-period 到 end-period 期間累計償還的本金數額。CUMPRINC(rate,nper,pv,start_period, end_period,type)DB使用固定餘額遞減法，計算一筆資產在給定期間內的折舊值。DB(cost,salvage,life,period,month)DDB使用雙倍餘額遞減法或其他指定方法，計算一筆資產在給定期間內的折舊值。DDB(cost,salvage,life,period,factor)DISC返回有價證券的貼現率。DISC(settlement,maturity,pr, redemption,basis)DOLLARDE將按分數表示的價格轉換為按小數表示的價格，如證券價格，轉換為小數表示的數字。DOLLARDE(fractional_dollar, fraction)DOLLARFR將按小數表示的價格轉換為按分數表示的價格。如證券價格，轉換為分數型數字。DOLLARFR(decimal_dollar, fraction)DURATION返回假設面值 $100 的定期付息有價證券的修正期限。期限定義為一系列現金流現值的加權平均值，用於計量債券價格對於收益率變化的敏感程度。DURATION(settlement,maturity, coupon yld,frequency,basis)EFFECT利用給定的名義年利率和一年中的複利期次，計算實際年利率。EFFECT(nominal_rate,npery)FV基於固定利率及等額分期付款方式，返回某項投資的未來值。FV(rate,nper,pmt,pv,type)FVSCHEDULE基於一系列複利返回本金的未來值。函數 FVSCHDULE 用於計算某項投資在變動或可調利率下的未來值。FVSCHEDULE(principal,schedule)INTRATE返回一次性付息證券的利率。INTRATE(settlement,maturity, investment,redemption,basis)IPMT基於固定利率及等額分期付款方式，返回投資或貸款在某一給定期次內的利息償還額。IPMT(rate,per,nper,pv,fv,type)IRR返回由數值代表的一組現金流的內部收益率。IRR(values,guess)ISPMT計算特定投資期內要支付的利息。ISPMT(rate,per,nper,pv)MDURATION返回假設面值 $100 的有價證券的 Macauley 修正期限。MDURATION(settlement,maturity, coupon,yld,frequency,basis)MIRR返回某一連續期間內現金流的修正內部收益率。MIRR(values,finance_rate, reinvest_rate)NOMINAL基於給定的實際利率和年複利期數，返回名義年利率。NOMINAL(effect_rate,npery)NPER基於固定利率及等額分期付款方式，返回某項投資（或貸款）的總期數。NPER(rate, pmt, pv, fv, type)NPV通過使用貼現率以及一系列未來支出（負值）和收入（正值），返回一項投資的凈現值。NPV(rate,value1,value2, ...)ODDFPRICE返回首期付息日不固定的面值 $100 的有價證券的價格ODDFPRICE(settlement,maturity, issue,first_coupon,rate,yld, redemption, frequency,basis)ODDFYIELD返回首期付息日不固定的有價證券（長期或短期）的收益率。ODDFYIELD(settlement,maturity, issue,first_coupon,rate,pr, redemption, frequency,basis)ODDLPRICE返回末期付息日不固定的面值 $100 的有價證券（長期或短期）的價格。ODDLPRICE(settlement,maturity, last_interest,rate,yld,redemption, frequency,basis)ODDLYIELD返回末期付息日不固定的有價證券（長期或短期）的收益率。ODDLYIELD(settlement,maturity, last_interest,rate,pr,redemption, frequency,basis)PMT基於固定利率及等額分期付款方式，返回貸款的每期付款額。PMT(rate,nper,pv,fv,type)PPMT基於固定利率及等額分期付款方式，返回投資在某一給定期間內的本金償還額。PPMT(rate,per,nper,pv,fv,type)PRICE返回定期付息的面值 $100 的有價證券的價格。PRICE(settlement,maturity, rate,yld,redemption,frequency, basis)PRICEDISC返回折價發行的面值 $100 的有價證券的價格。PRICEDISC(settlement,maturity, discount,redemption,basis)PRICEMAT返回到期付息的面值 $100 的有價證券的價格。PRICEMAT(settlement,maturity, issue,rate,yld,basis)PV返回投資的現值。現值為一系列未來付款的當前值的累積和。例如，借入方的借入款即為貸出方貸款的現值。PV(rate,nper,pmt,fv,type)RATE返回年金的各期利率。函數 RATE 通過迭代法計算得出，並且可能無解或有多個解。RATE(nper,pmt,pv,fv,type,guess)RECEIVED返回一次性付息的有價證券到期收回的金額。RECEIVED(settlement,maturity, investment,discount,basis)SLN返回某項資產在一個期間中的線性折舊值。SLN(cost,salvage,life)SYD返回某項資產按年限總和折舊法計算的指定期間的折舊值。SYD(cost,salvage,life,per)TBILLEQ返回國庫券的等效收益率。TBILLEQ(settlement,maturity, discount)TBILLPRICE返回面值 $100 的國庫券的價格。TBILLPRICE(settlement,maturity, discount)TBILLYIELD返回國庫券的收益率。TBILLYIELD(settlement,maturity,pr)VDB使用雙倍餘額遞減法或其他指定的方法，返回指定的任何期間內（包括部分期間）的資產折舊值。函數 VDB 代表可變餘額遞減法。VDB(cost,salvage,life,start_period, end_period,factor,no_switch)XIRR返回一組現金流的內部收益率，這些現金流不一定定期發生。若要計算一組定期現金流的內部收益率，請使用函數 IRR。XIRR(values,dates,guess)XNPV返回一組現金流的凈現值，這些現金流不一定定期發生。若要計算一組定期現金流的凈現值，請使用函數 NPV。XNPV(rate,values,dates)YIELD返回定期付息有價證券的收益率，函數 YIELD 用於計算債券收益率。YIELD(settlement,maturity,rate, pr,redemption,frequency,basis)YIELDDISC返回折價發行的有價證券的年收益率。YIELDDISC(settlement,maturity, pr,redemption,basis)YIELDMAT返回到期付息的有價證券的年收益率。YIELDMAT(settlement,maturity, issue,rate,pr,basis)Excel函數應用之資料庫函數在Microsoft Excel 中包含了一些工作表函數，它們用於對存儲在數據清單或資料庫中的數據進行分析，這些函數統稱為資料庫函數Dfunctions。一、函數的共同特點這一類函數具有一些共同特點：（1）每個函數均有三個參數：database、field 和 criteria。這些參數指向函數所使用的工作表區域。（2）除了GETPIVOTDATA函數之外，其餘十二個函數都以字母D開頭。（3）如果將字母D去掉，可以發現其實大多數資料庫函數已經在Excel的其他類型函數中出現過了。比如，DAVERAGE將D去掉的話，就是求平均值的函數AVERAGE。二、資料庫函數列表在Excel包含的資料庫函數及其應用為：圖1三、資料庫函數的參數含義由於資料庫函數具有相同的三個參數，因此筆者將首先介紹一下該類函數的幾個參數。然後再以具體示例來說明資料庫函數的應用方法。該類函數的語法形式為 函數名稱(database,field,criteria)。Database為構成數據清單或資料庫的單元格區域。資料庫是包含一組相關數據的數據清單，其中包含相關信息的行為記錄，而包含數據的列為欄位。數據清單的第一行包含著每一列的標誌項。Field為指定函數所使用的數據列。數據清單中的數據列必須在第一行具有標誌項。Field 可以是文本，即兩端帶引號的標誌項，如「使用年數」或「產量」；此外，Field 也可以是代表數據清單中數據列位置的數字：1 表示第一列，2 表示第二列，等等。Criteria為一組包含給定條件的單元格區域。可以為參數 criteria 指定任意區域，只要它至少包含一個列標誌和列標誌下方用於設定條件的單元格。四、舉例說明1、例：某果園的果樹的高度、使用年數、產量與利潤的統計數據表如圖所示，圖2希望求出（1） 有多少種蘋果樹的樹高在10~16英尺之間（2） 蘋果樹與梨樹的最大利潤值是多少（3） 高度大於 10 英尺的蘋果樹的最小利潤是多少（4） 蘋果樹的總利潤（5） 高度大於 10 英尺的蘋果樹的平均產量（6） 果園中所有樹種的平均使用年數（7） 求蘋果樹和梨樹產量的估算標準偏差、真實標準偏差、估算方差、真實方差。2、求解步驟（1） 創建空白工作簿或工作表，將數據錄入Excel中圖3（2） 由於第一問需要求解有多少種蘋果樹的樹高在10~16英尺之間，因此建立查詢條件高度在10與16之間圖4（3） 利用函數DCOUNT求解滿足條件的單元格數來計算高度在10~16英尺的蘋果樹的種類。公式為：DCOUNT(A4:E10,"使用年數",B1:G2)，求出有一種蘋果樹滿足條件。（4） 由於第二問為求蘋果樹與梨樹的最大利潤值，因此再建立一個查詢條件——梨樹，如圖。圖5（5） 利用函數DMAX求出蘋果樹與梨樹的最大利潤，同理可以利用函數DMIN求出高度大於10英尺蘋果樹的最小利潤。具體公式為：=DMAX(A4:E10,"利潤",A1:A3) 求出蘋果樹與梨樹的最大利潤為105=DMIN(A4:E10,"利潤",A1:B2) 求出高度大於10英尺蘋果樹的最小利潤為75（6） 利用函數DSUM可以求出所有蘋果樹的總利潤。公式為：=DSUM(A4:E10,"利潤",A1:A2) 總利潤為225（7） 其他各問均可採用類似的函數求解，詳細的公式如圖所示。圖6五、需要注意的地方1、可以為參數 criteria 指定任意區域，只要它至少包含一個列標誌和列標誌下方用於設定條件的單元格。例如，如果區域 A1:A2 在 A1 中包含列標誌「樹種」，在A2中包含名稱蘋果樹，可將此區域命名為蘋果樹樹種，那麼在資料庫函數中就可使用該名稱作為參數 criteria。2、雖然條件區域可以在工作表的任意位置，但不要將條件區域置於數據清單的下方。因為如果使用「數據」菜單中的「記錄單」命令在數據清單中添加信息，新的信息將被添加在數據清單下方的第一行上。如果數據清單下方的行非空，Microsoft Excel 將無法添加新的信息。3、確定條件區域沒有與數據清單相重疊。4、若要對資料庫的整個列進行操作，需要在條件區域中的列標誌下方輸入一個空白行。六、關於條件的建立在上面的示例中，我們簡單介紹了條件區域的建立，在這裡詳細介紹有關在Excel中利用高級條件進行數據篩選的方法。1、 有關概念條件是指所指定的限制查詢或篩選的結果集中包含哪些記錄的條件。例如，上面示例中條件選擇「高度」欄位的值大於10的記錄：高度>10。清單是指包含相關數據的一系列工作錶行，例如，發票資料庫或一組客戶名稱和電話號碼。清單的第一行具有列標誌。2、 建立條件區域的基本要求（1）在可用作條件區域的數據清單上插入至少三個空白行。（2）條件區域必須具有列標誌。（3）請確保在條件值與數據清單之間至少留了一個空白行。如在上面的示例中A1:F3就是一個條件區域，其中第一行為列標誌，如樹種、高度。3、 篩選條件的建立在列標誌下面的一行中，鍵入所要匹配的條件。所有以該文本開始的項都將被篩選。例如，如果您鍵入文本「Dav」作為條件，Microsoft Excel 將查找「Davolio」、「David」和「Davis」。如果只匹配指定的文本，可鍵入公式=""=text""，其中「text」是需要查找的文本。如果要查找某些字元相同但其他字元不一定相同的文本值，則可使用通配符。Excel中支持的通配符為：圖74、 幾種不同條件的建立（1）單列上具有多個條件如果對於某一列具有兩個或多個篩選條件，那麼可直接在各行中從上到下依次鍵入各個條件。例如，上面示例的條件區域顯示「樹種」列中包含「蘋果樹」或「梨樹」的行。（2）多列上具有單個條件若要在兩列或多列中查找滿足單個條件的數據，請在條件區域的同一行中輸入所有條件。例如，下面示例的條件區域顯示所有在「高度」列中大於10且「產量」大於10的數據行。圖8（3）某一列或另一列上具有單個條件若要找到滿足一列條件或另一列條件的數據，請在條件區域的不同行中輸入條件。例如，上面示例的條件區域顯示所有在「高度」列中大於10的數據行。（4）兩列上具有兩組條件之一若要找到滿足兩組條件（每一組條件都包含針對多列的條件）之一的數據行，請在各行中鍵入條件。例如，下面的條件區域將顯示所有在「樹種」列中包含「蘋果樹」且「高度」大於10的數據行，同時也顯示「櫻桃樹」的「使用年數」大於10年的行。圖9（5）一列有兩組以上條件若要找到滿足兩組以上條件的行，請用相同的列標包括多列。例如，上面示例的條件區域顯示介於10和16之間的高度。（6）將公式結果用作條件Excel中可以將公式（公式：單元格中的一系列值、單元格引用、名稱或運算符的組合，可生成新的值。公式總是以等號 (=) 開始。）的計算結果作為條件使用。用公式創建條件時，不要將列標誌作為條件標記使用，應該將條件標記置空，或者使用清單中非列標誌的標記。例如，下面的條件區域顯示在列 C 中，其值大於單元格區域 C7:C10 平均值的行。=C7>AVERAGE($C$7:$C$10)需要注意的是用作條件的公式必須使用相對引用來引用列標誌（例如，「高度」），或者引用第一個記錄的對應欄位。公式中的所有其他引用都必須是絕對引用並且公式必須計算出結果 TRUE 或 FALSE。在本公式示例中，C7 引用了數據清單中第一個記錄（行 7）的欄位（列 C）。當然也可以在公式中使用列標誌來代替相對的單元格引用或區域名稱。當 Microsoft Excel 在包含條件的單元格中顯示錯誤值 #NAME? 或 #VALUE! 時，您可以忽略這些錯誤，因為它們不影響列表的篩選。此外Microsoft Excel 在計算數據時不區分大小寫。
推薦閱讀：