電子表格Excel函數教程

2023年12月25日發(fā)(作者：誠實的)

Excel函數教程

[日期：2005-07-02] 來源： 作者：陸元婕

[字體：大 中

小]

[dvnews_page=excel函數應用之函數簡介]excel的數據處理功能在現有的文字處理軟件中可以說是獨占鰲頭，幾乎沒有什么軟件能夠與它匹敵。在您學會了excel的基本操作后，是不是覺得自己一直局限在excel的操作界面中，而對于excel的函數功能卻始終停留在求和、求平均值等簡單的函數應用上呢？難道excel只能做這些簡單的工作嗎？其實不然，函數作為excel處理數據的一個最重要手段，功能是十分強大的，在生活和工作實踐中可以有多種應用，您甚至可以用excel來設計復雜的統計管理表格或者小型的數據庫系統。

請跟隨筆者開始excel的函數之旅。這里，筆者先假設您已經對于excel的基本操作有了一定的認識。首先我們先來了解一些與函數有關的知識。

一、什么是函數

excel中所提的函數其實是一些預定義的公式，它們使用一些稱為參數的特定數值按特定的順序或結構進行計算。用戶可以直接用它們對某個區(qū)域內的數值進行一系列運算，如分析和處理日期值和時間值、確定貸款的支付額、確定單元格中的數據類型、計算平均值、排序顯示和運算文本數據等等。例如，SUM 函數對單元格或單元格區(qū)域進行加法運算。

函數是否可以是多重的呢？也就是說一個函數是否可以是另一個函數的參數呢？當然可以，這就是嵌套函數的含義。所謂嵌套函數，就是指在某些情況下，您可能需要將某函數作為另一函數的參數使用。例如圖1中所示的公式使用了嵌套的 AVERAGE 函數，并將結果與 50 相比較。這個公式的含義是：如果單元格F2到F5的平均值大于50，則求F2到F5的和，否則顯示數值0。

圖1 嵌套函數

在學習excel函數之前，我們需要對于函數的結構做以必要的了解。如圖2所示，函數的結構以函數名稱開始，后面是左圓括號、以逗號分隔的參數和右圓括號。如果函數以公式的形式出現，請在函數名稱前面鍵入等號（=）。在創(chuàng)建包含函數的公式時，公式選項板將提供相關的幫助。

圖2 函數的結構

公式選項板--幫助創(chuàng)建或編輯公式的工具，還可提供有關函數及其參數的信息。單擊編輯欄中的"編輯公式"按鈕，或是單擊"常用"工具欄中的"粘貼函數" 按鈕之后，就會在編輯欄下面出現公式選項板。整個過程如圖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 函數，每個函數均有三個相同的參數：databa、field 和

criteria。這些參數指向數據庫函數所使用的工作表區(qū)域。其中參數 databa 為工作表上包含數據清單的區(qū)域。參數 field 為需要匯總的列的標志。參數 criteria 為工作表上包含指定條件的區(qū)域。

2.日期與時間函數--通過日期與時間函數，可以在公式中分析和處理日期值和時間值。

3.工程函數--工程工作表函數用于工程分析。這類函數中的大多數可分為三種類型：對復數進行處理的函數、在不同的數字系統（如十進制系統、十六進制系統、八進制系統和二進制系統）間進行數值轉換的函數、在不同的度量系統中進行數值轉換的函數。

4.財務函數--財務函數可以進行一般的財務計算，如確定貸款的支付額、投資的未來值或凈現值，以及債券或息票的價值。財務函數中常見的參數：

未來值 (fv)--在所有付款發(fā)生后的投資或貸款的價值。

期間數 (nper)--投資的總支付期間數。

付款 (pmt)--對于一項投資或貸款的定期支付數額。

現值 (pv)--在投資期初的投資或貸款的價值。例如，貸款的現值為所借入的本金數額。

利率 (rate)--投資或貸款的利率或貼現率。

類型 (type)--付款期間內進行支付的間隔，如在月初或月末。

5.信息函數--可以使用信息工作表函數確定存儲在單元格中的數據的類型。信息函數包含一組稱為 IS 的工作表函數，在單元格滿足條件時返回 TRUE。例如，如果單元格包含一個偶數值，ISEVEN 工作表函數返回 TRUE。如果需要確定某個單元格區(qū)域中是否存在空白單元格，可以使用 COUNTBLANK 工作表函數對單元格區(qū)域中的空白單元格進行計數，或者使用 ISBLANK 工作表函數確定區(qū)域中的某個單元格是否為空。

6.邏輯函數--使用邏輯函數可以進行真假值判斷，或者進行復合檢驗。例如，可以使用 IF 函數確定條件為真還是假，并由此返回不同的數值。

7.查詢和引用函數--當需要在數據清單或表格中查找特定數值，或者需要查找某一單元格的引用時，可以使用查詢和引用工作表函數。例如，如果需要在表格中查找與第一列中的值相匹配的數值，可以使用

VLOOKUP 工作表函數。如果需要確定數據清單中數值的位置，可以使用 MATCH 工作表函數。

8.數學和三角函數--通過數學和三角函數，可以處理簡單的計算，例如對數字取整、計算單元格區(qū)域中的數值總和或復雜計算。

9.統計函數--統計工作表函數用于對數據區(qū)域進行統計分析。例如，統計工作表函數可以提供由一組給定值繪制出的直線的相關信息，如直線的斜率和 y 軸截距，或構成直線的實際點數值。

10.文本函數--通過文本函數，可以在公式中處理文字串。例如，可以改變大小寫或確定文字串的長度。可以將日期插入文字串或連接在文字串上。下面的公式為一個示例，借以說明如何使用函數 TODAY 和函數

TEXT 來創(chuàng)建一條信息，該信息包含著當前日期并將日期以"dd-mm-yy"的格式表示。

11.用戶自定義函數--如果要在公式或計算中使用特別復雜的計算，而工作表函數又無法滿足需要，則需要創(chuàng)建用戶自定義函數。這些函數，稱為用戶自定義函數，可以通過使用 Visual Basic for Applications 來創(chuàng)建。

以上對excel函數及有關知識做了簡要的介紹，在以后的文章中筆者將逐一介紹每一類函數的使用方法及應用技巧。但是由于excel的函數相當多，因此也可能僅介紹幾種比較常用的函數使用方法，其他更多的函數您可以從excel的在線幫助功能中了解更詳細的資訊。[dvnews_page=excel函數應用之數學和三角函數]學習excel函數，我們還是從“數學與三角函數”開始。畢竟這是我們非常熟悉的函數，這些正弦函數、余弦函數、取整函數等等從中學開始，就一直陪伴著我們。

首先，讓我們一起看看excel提供了哪些數學和三角函數。筆者在這里以列表的形式列出excel提供的所有數學和三角函數，詳細請看附注的表格。

從表中我們不難發(fā)現，excel提供的數學和三角函數已基本囊括了我們通常所用得到的各種數學公式與三角函數。這些函數的詳細用法，筆者不在這里一一贅述，下面從應用的角度為大家演示一下這些函數的使用方法。

一、與求和有關的函數的應用

SUM函數是excel中使用最多的函數，利用它進行求和運算可以忽略存有文本、空格等數據的單元格，語法簡單、使用方便。相信這也是大家最先學會使用的excel函數之一。但是實際上，excel所提供的求和函數不僅僅只有SUM一種，還包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2幾種函數。

這里筆者將以某單位工資表為例重點介紹SUM（計算一組參數之和）、SUMIF（對滿足某一條件的單元格區(qū)域求和）的使用。(說明：為力求簡單，示例中忽略稅金的計算。)

圖1 函數求和

SUM

1、行或列求和

以最常見的工資表（如上圖）為例，它的特點是需要對行或列內的若干單元格求和。

比如，求該單位2001年5月的實際發(fā)放工資總額，就可以在H13中輸入公式：

=SUM(H3:H12)

2、區(qū)域求和

區(qū)域求和常用于對一張工作表中的所有數據求總計。此時你可以讓單元格指針停留在存放結果的單元格，然后在excel編輯欄輸入公式"=SUM（）"，用鼠標在括號中間單擊，最后拖過需要求和的所有單元格。若這些單元格是不連續(xù)的，可以按住Ctrl鍵分別拖過它們。對于需要減去的單元格，則可以按住Ctrl鍵逐個選中它們，然后用手工在公式引用的單元格前加上負號。當然你也可以用公式選項板完成上述工作，不過對于SUM函數來說手工還是來的快一些。比如，H13的公式還可以寫成：

=SUM(D3:D12,F3:F12)-SUM(G3:G12)

3、注意

SUM函數中的參數，即被求和的單元格或單元格區(qū)域不能超過30個。換句話說，SUM函數括號中出現的分隔

符（逗號）不能多于29個，否則excel就會提示參數太多。對需要參與求和的某個常數，可用"=SUM（單元格區(qū)域，常數）"的形式直接引用，一般不必絕對引用存放該常數的單元格。

SUMIF

SUMIF函數可對滿足某一條件的單元格區(qū)域求和，該條件可以是數值、文本或表達式，可以應用在人事、工資和成績統計中。

仍以上圖為例，在工資表中需要分別計算各個科室的工資發(fā)放情況。

要計算銷售部2001年5月加班費情況。則在F15種輸入公式為

=SUMIF($C$3:$C$12,"銷售部",$F$3:$F$12)

其中"$C$3:$C$12"為提供邏輯判斷依據的單元格區(qū)域，"銷售部"為判斷條件即只統計$C$3:$C$12區(qū)域中部門為"銷售部"的單元格，$F$3:$F$12為實際求和的單元格區(qū)域。

二、與函數圖像有關的函數應用

我想大家一定還記得我們在學中學數學時，常常需要畫各種函數圖像。那個時候是用坐標紙一點點描繪，常常因為計算的疏忽，描不出平滑的函數曲線。現在，我們已經知道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的工作區(qū)了。

6、 編輯圖像--圖像生成后，字體、圖像大小、位置都不一定合適。可選擇相應的選項進行修改。所有這些操作可以先用鼠標選中相關部分，再單擊右鍵彈出快捷菜單，通過快捷菜單中的有關項目即可進行操作。

至此，一幅正弦和余弦函數圖像制作完成。用同樣的方法，還可以制作二次曲線、對數圖像等等。

三、常見數學函數使用技巧--四舍五入

在實際工作的數學運算中，特別是財務計算中常常遇到四舍五入的問題。雖然，excel的單元格格式中允許你定義小數位數，但是在實際操作中，我們發(fā)現，其實數字本身并沒有真正的四舍五入，只是顯示結果似乎四舍五入了。如果采用這種四舍五入方法的話，在財務運算中常常會出現幾分錢的誤差，而這是財務運算不允許的。那是否有簡單可行的方法來進行真正的四舍五入呢？其實，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 工作表函數

ATANH 工作表函數

CEILING 工作表函數

COMBIN 工作表函數

COS 工作表函數

COSH 工作表函數

COUNTIF 工作表函數

DEGREES 工作表函數

EVEN 工作表函數

EXP 工作表函數

FACT 工作表函數

FACTDOUBLE 工作表函數

FLOOR 工作表函數

GCD 工作表函數

INT 工作表函數

LCM 工作表函數

LN 工作表函數

LOG 工作表函數

LOG10 工作表函數

MDETERM 工作表函數

MINVERSE 工作表函數

MMULT 工作表函數

MOD 工作表函數

MROUND 工作表函數

MULTINOMIAL 工作表函數

ODD 工作表函數

PI 工作表函數

返回參數的絕對值

返回數字的反余弦值

返回參數的反雙曲余弦值

返回參數的反正弦值

返回參數的反雙曲正弦值

返回參數的反正切值

返回給定的 X 及 Y 坐標值的反正切值

返回參數的反雙曲正切值

將參數 Number 沿絕對值增大的方向，舍入為最接近的整數或基數

計算從給定數目的對象集合中提取若干對象的組合數

返回給定角度的余弦值

返回參數的雙曲余弦值

計算給定區(qū)域內滿足特定條件的單元格的數目

將弧度轉換為度

返回沿絕對值增大方向取整后最接近的偶數

返回 e 的 n 次冪常數 e 等于 2.704，是自然對數的底數

返回數的階乘，一個數的階乘等于 1*2*3*...*該數

返回參數 Number 的半階乘

將參數 Number 沿絕對值減小的方向去尾舍入，使其等于最接近的

significance 的倍數

返回兩個或多個整數的最大公約數

返回實數舍入后的整數值

返回整數的最小公倍數

返回一個數的自然對數自然對數以常數項 e（2.704）為底

按所指定的底數，返回一個數的對數

返回以 10 為底的對數

返回一個數組的矩陣行列式的值

返回數組矩陣的逆距陣

返回兩數組的矩陣乘積結果

返回兩數相除的余數結果的正負號與除數相同

返回參數按指定基數舍入后的數值

返回參數和的階乘與各參數階乘乘積的比值

返回對指定數值進行舍入后的奇數

返回數字 3.979，即數學常數 pi，精確到小數點后 15 位

POWER 工作表函數

PRODUCT 工作表函數

QUOTIENT 工作表函數

RADIANS 工作表函數

RAND 工作表函數

RANDBETWEEN 工作表函數

ROMAN 工作表函數

ROUND 工作表函數

ROUNDDOWN 工作表函數

ROUNDUP 工作表函數

SERIESSUM 工作表函數

SIGN 工作表函數

SIN 工作表函數

SINH 工作表函數

SQRT 工作表函數

SQRTPI 工作表函數

SUBTOTAL 工作表函數

SUM 工作表函數

SUMIF 工作表函數

SUMPRODUCT 工作表函數

SUMSQ 工作表函數

SUMX2MY2 工作表函數

SUMX2PY2 工作表函數

SUMXMY2 工作表函數

TAN 工作表函數

TANH 工作表函數

TRUNC 工作表函數

返回給定數字的乘冪

將所有以參數形式給出的數字相乘，并返回乘積值

回商的整數部分，該函數可用于舍掉商的小數部分

將角度轉換為弧度

返回大于等于 0 小于 1 的均勻分布隨機數

返回位于兩個指定數之間的一個隨機數

將阿拉伯數字轉換為文本形式的羅馬數字

返回某個數字按指定位數舍入后的數字

靠近零值，向下（絕對值減小的方向）舍入數字

遠離零值，向上（絕對值增大的方向）舍入數字

返回基于以下公式的冪級數之和：

返回數字的符號當數字為正數時返回 1，為零時返回 0，為負數時返回 -1

返回給定角度的正弦值

返回某一數字的雙曲正弦值

返回正平方根

返回某數與 pi 的乘積的平方根

返回數據清單或數據庫中的分類匯總

返回某一單元格區(qū)域中所有數字之和

根據指定條件對若干單元格求和

在給定的幾組數組中，將數組間對應的元素相乘，并返回乘積之和

返回所有參數的平方和

返回兩數組中對應數值的平方差之和

返回兩數組中對應數值的平方和之和，平方和加總在統計計算中經常使用

返回兩數組中對應數值之差的平方和

返回給定角度的正切值

返回某一數字的雙曲正切值

將數字的小數部分截去，返回整數

[dvnews_page=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函數示例1

2、 如果 B1-B3 單元格中的值為 TRUE、FALSE、TRUE，顯然三個參數并不都為真，所以 在B4單元格中的公式=AND(B1:B3) 等于 FALSE

圖2 AND函數示例2

（二）OR函數

OR函數指在其參數組中，任何一個參數邏輯值為 TRUE，即返回 TRUE。它與AND函數的區(qū)別在于，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函數用于執(zhí)行真假值判斷后，根據邏輯測試的真假值返回不同的結果，因此If函數也稱之為條件函數。它的應用很廣泛，可以使用函數 IF 對數值和公式進行條件檢測。

它的語法為IF(logical_test,value_if_true,value_if_fal)。其中Logical_test表示計算結果為 TRUE

或 FALSE 的任意值或表達式。本參數可使用任何比較運算符。

Value_if_true顯示在logical_test 為 TRUE 時返回的值，Value_if_true 也可以是其他公式。Value_if_fal logical_test 為 FALSE 時返回的值。Value_if_fal 也可以是其他公式。

簡言之，如果第一個參數logical_test返回的結果為真的話，則執(zhí)行第二個參數Value_if_true的結果，否則執(zhí)行第三個參數Value_if_fal的結果。IF函數可以嵌套七層，用 value_if_fal 及

value_if_true 參數可以構造復雜的檢測條件。

excel還提供了可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區(qū)域中某個文本串或數字出現的次數，則可使用 COUNTIF 工作表函數。如果要根據單元格區(qū)域中的某一文本串或數字求和，則可使用 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)不等于零的正規(guī)寫法是SUM(C5:F5)<>0，在excel中可以省略<>0；

（2） ""表示字符串的內容為空，因此執(zhí)行的結果是在單元格中不顯示任何字符。

圖4

2、 不同的條件返回不同的結果

如果對上述例子有了很好的理解后，我們就很容易將IF函數應用到更廣泛的領域。比如，在成績表中根據不同的成績區(qū)分合格與不合格。現在我們就以某班級的英語成績?yōu)槔唧w說明用法。

圖6

某班級的成績如圖6所示，為了做出最終的綜合評定，我們設定按照平均分判斷該學生成績是否合格的規(guī)則。如果各科平均分超過60分則認為是合格的，否則記作不合格。

根據這一規(guī)則，我們在綜合評定中寫公式（以單元格B12為例）：

=IF(B11>60,"合格","不合格")

語法解釋為，如果單元格B11的值大于60，則執(zhí)行第二個參數即在單元格B12中顯示合格字樣，否則執(zhí)行第三個參數即在單元格B12中顯示不合格字樣。

在綜合評定欄中可以看到由于C列的同學各科平均分為54分，綜合評定為不合格。其余均為合格。

3、 多層嵌套函數的應用

在上述的例子中，我們只是將成績簡單區(qū)分為合格與不合格，在實際應用中，成績通常是有多個等級的，比如優(yōu)、良、中、及格、不及格等。有辦法一次性區(qū)分嗎？可以使用多層嵌套的辦法來實現。仍以上例為例，我們設定綜合評定的規(guī)則為當各科平均分超過90時，評定為優(yōu)秀。如圖7所示。

圖7

說明：為了解釋起來比較方便，我們在這里僅做兩重嵌套的示例，您可以按照實際情況進行更多重的嵌套，但請注意excel的IF函數最多允許七重嵌套。

根據這一規(guī)則，我們在綜合評定中寫公式（以單元格F12為例）：

=IF(F11>60,IF(AND(F11>90),"優(yōu)秀","合格"),"不合格")

語法解釋為，如果單元格F11的值大于60，則執(zhí)行第二個參數，在這里為嵌套函數，繼續(xù)判斷單元格F11的值是否大于90（為了讓大家體會一下AND函數的應用，寫成AND(F11>90)，實際上可以僅寫F11>90），如果滿足在單元格F12中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果F11的值以上條件都不滿足，則執(zhí)行第三個參數即在單元格F12中顯示不合格字樣。

在綜合評定欄中可以看到由于F列的同學各科平均分為92分，綜合評定為優(yōu)秀。

（三）根據條件計算值

在了解了IF函數的使用方法后，我們再來看看與之類似的excel提供的可根據某一條件來分析數據的其他函數。例如，如果要計算單元格區(qū)域中某個文本串或數字出現的次數，則可使用 COUNTIF 工作表函數。如果要根據單元格區(qū)域中的某一文本串或數字求和，則可使用 SUMIF 工作表函數。關于SUMIF函數在數學與三角函數中以做了較為詳細的介紹。這里重點介紹COUNTIF的應用。

COUNTIF可以用來計算給定區(qū)域內滿足特定條件的單元格的數目。比如在成績表中計算每位學生取得優(yōu)秀成績的課程數。在工資表中求出所有基本工資在2000元以上的員工數。

語法形式為COUNTIF(range,criteria)。其中Range為需要計算其中滿足條件的單元格數目的單元格區(qū)域。Criteria確定哪些單元格將被計算在內的條件，其形式可以為數字、表達式或文本。例如，條件可以表示為 32、"32"、">32"、"apples"。

1、成績表

這里仍以上述成績表的例子說明一些應用方法。我們需要計算的是：每位學生取得優(yōu)秀成績的課程數。規(guī)則為成績大于90分記做優(yōu)秀。如圖8所示

圖8

根據這一規(guī)則，我們在優(yōu)秀門數中寫公式（以單元格B13為例）：

=COUNTIF(B4:B10,">90")

語法解釋為，計算B4到B10這個范圍，即jarry的各科成績中有多少個數值大于90的單元格。

在優(yōu)秀門數欄中可以看到jarry的優(yōu)秀門數為兩門。其他人也可以依次看到。

2、 銷售業(yè)績表

銷售業(yè)績表可能是綜合運用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計算銷售人員的訂單數，然后匯總每個銷售人員的銷售額，并且根據總發(fā)貨量決定每次銷售應獲得的獎金。

原始數據表如圖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根據訂單總額決定每次銷售應獲得的獎金。

假定公司的銷售獎金規(guī)則為當訂單總額超過5萬元時，獎勵幅度為百分之十五，否則為百分之十。根據這一規(guī)則仍以銷售人員ANNIE為例說明。公式為：

=IF(C17<50000,10%,15%)*C17

如果訂單總額小于 50000則獎金為 10%；如果訂單總額大于等于 50000，則獎金為 15%。

至此，我們已完全了解了excel函數的邏輯函數，相信大家在實際工作中會想出更多更有用的運用。

[dvnews_page=excel函數應用之文本/日期/時間函數]所謂文本函數，就是可以在公式中處理文字串的函數。例如，可以改變大小寫或確定文字串的長度；可以替換某些字符或者去除某些字符等。而日期和時間函數則可以在公式中分析和處理日期值和時間值。關于這兩類函數的列表參看附表，這里僅對一些常用的函數做簡要介紹。

一、文本函數

（一）大小寫轉換

LOWER--將一個文字串中的所有大寫字母轉換為小寫字母。

UPPER--將文本轉換成大寫形式。

PROPER--將文字串的首字母及任何非字母字符之后的首字母轉換成大寫。將其余的字母轉換成小寫。

這三種函數的基本語法形式均為 函數名（text）。示例說明：

已有字符串為：pLea ComE Here! 可以看到由于輸入的不規(guī)范，這句話大小寫亂用了。

通過以上三個函數可以將文本轉換顯示樣式，使得文本變得規(guī)范。參見圖1

Lower（pLea ComE Here!）= plea come here!

upper（pLea ComE Here!）= PLEASE COME HERE!

proper（pLea ComE Here!）= Plea 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)=This

RIGHT("This is an apple",5)=apple

MID("This is an apple",6,2)=is

圖2

（三）去除字符串的空白

在字符串形態(tài)中，空白也是一個有效的字符，但是如果字符串中出現空白字符時，容易在判斷或對比數據是發(fā)生錯誤，在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 能區(qū)分大小寫，但忽略格式上的差異。利用函數 EXACT 可以測試輸入文檔內的文字。語法形式為：EXACT(text1,text2)Text1為待比較的第一個字符串。Text2為待比較的第二個字符串。舉例說明：參見圖4

EXACT("China","china")=Fal

圖4

二、日期與時間函數

在數據表的處理過程中，日期與時間的函數是相當重要的處理依據。而excel在這方面也提供了相當豐富的函數供大家使用。

（一）取出當前系統時間/日期信息

用于取出當前系統時間/日期信息的函數主要有NOW、TODAY。

語法形式均為 函數名（）。

（二）取得日期/時間的部分字段值

如果需要單獨的年份、月份、日數或小時的數據時，可以使用HOUR、DAY、MONTH、YEAR函數直接從日期/時間中取出需要的數據。具體示例參看圖5。

比如，需要返回2001-5-30 12:30 PM的年份、月份、日數及小時數，可以分別采用相應函數實現。

YEAR(E5)=2001

MONTH(E5)=5

DAY(E5)=30

HOUR(E5)=12

圖5

此外還有更多有用的日期/時間函數，可以查閱附表。下面我們將以一個具體的示例來說明excel的文本函數與日期函數的用途。

三、示例：做一個美觀簡潔的人事資料分析表

1、 示例說明

在如圖6所示的某公司人事資料表中，除了編號、員工姓名、身份證號碼以及參加工作時間為手工添入外，其余各項均為用函數計算所得。

圖6

在此例中我們將詳細說明如何通過函數求出：

（1）自動從身份證號碼中提取出生年月、性別信息。

（2）自動從參加工作時間中提取工齡信息。

2、身份證號碼相關知識

在了解如何實現自動從身份證號碼中提取出生年月、性別信息之前，首先需要了解身份證號碼所代表的含義。我們知道，當今的身份證號碼有15/18位之分。早期簽發(fā)的身份證號碼是15位的，現在簽發(fā)的身份證由于年份的擴展（由兩位變?yōu)樗奈唬┖湍┪布恿诵灤a，就成了18位。這兩種身份證號碼將在相當長的一段時期內共存。兩種身份證號碼的含義如下：

（1）15位的身份證號碼：1~6位為地區(qū)代碼，7~8位為出生年份(2位)，9~10位為出生月份，11~12位為出生日期，第13~15位為順序號，并能夠判斷性別，奇數為男，偶數為女。

（2）18位的身份證號碼：1~6位為地區(qū)代碼，7~10位為出生年份(4位)，11~12位為出生月份，13~14位為出生日期，第15~17位為順序號，并能夠判斷性別，奇數為男，偶數為女。18位為效驗位。

3、 應用函數

在此例中為了實現數據的自動提取，應用了如下幾個excel函數。

（1）IF函數：根據邏輯表達式測試的結果，返回相應的值。IF函數允許嵌套。

語法形式為：IF（logical_test, value_if_true,value_if_fal）

（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. 其他說明

在這張人事資料表中我們還發(fā)現，創(chuàng)建日期：31-05-2001時顯示在同一個單元格中的。這是如何實現的呢？難道是手工添加的嗎？不是，實際上這個日期還是變化的，它顯示的是系統當前時間。這里是利用函數

TODAY 和函數 TEXT 一起來創(chuàng)建一條信息，該信息包含著當前日期并將日期以"dd-mm-yyyy"的格式表示。

具體公式寫法為：="創(chuàng)建日期："&TEXT(TODAY(),"dd-mm-yyyy")

至此，我們對于文本函數、日期與時間函數已經有了大致的了解，同時也設想了一些應用領域。相信隨著大家在這方面的不斷研究，會有更廣泛的應用。

附一：文本函數

函數名

ASC

CHAR

節(jié)）字符。

返回對應于數字代碼的字符，函數 CHAR 可將其他類型計算機文件中的代碼轉換為字符。

刪除文本中不能打印的字符。對從其他應用程序中輸入的CLEAN

字符串使用 CLEAN 函數，將刪除其中含有的當前操作系統無法打印的字符。例如，可以刪除通常出現在數據文件頭部或尾部、無法打印的低級計算機代碼。

CODE

CONCATENATE

DOLLAR

返回文字串中第一個字符的數字代碼。返回的代碼對應于計算機當前使用的字符集。

將若干文字串合并到一個文字串中。

依照貨幣格式將小數四舍五入到指定的位數并轉換成文字。

該函數測試兩個字符串是否完全相同。如果它們完全相EXACT

同，則返回 TRUE；否則，返回 FALSE。函數 EXACT 能區(qū)分大小寫，但忽略格式上的差異。利用函數 EXACT 可以測試輸入文檔內的文字。

FIND FIND 用于查找其他文本串 (within_text) 內的文本串 FIND(find_text,within_texEXACT(text1,text2)

CODE(text)

CONCATENATE

(text1,text2,...)

DOLLAR 或

RMB(number,decimals)

CLEAN(text)

函數說明

將字符串中的全角（雙字節(jié)）英文字母更改為半角（單字ASC(text)

CHAR(number)

語法

(find_text)，并從 within_text 的首字符開始返回

find_text 的起始位置編號。

FIXED

按指定的小數位數進行四舍五入，利用句點和逗號，以小數格式對該數設置格式，并以文字串形式返回結果。

JIS

將字符串中的半角（單字節(jié)）英文字母或片假名更改為全角（雙字節(jié)）字符。

LEFT 基于所指定的字符數返回文本串中的第一個或前幾LEFT

個字符。

LEFTB 基于所指定的字節(jié)數返回文本串中的第一個或前幾個字符。此函數用于雙字節(jié)字符。

LEN 返回文本串中的字符數。

LEN LENB 返回文本串中用于代表字符的字節(jié)數。此函數用于雙字節(jié)字符。

LOWER 將一個文字串中的所有大寫字母轉換為小寫字母。

MID 返回文本串中從指定位置開始的特定數目的字符，該MID

數目由用戶指定。

MIDB 返回文本串中從指定位置開始的特定數目的字符，該數目由用戶指定。此函數用于雙字節(jié)字符。

PHONETIC 提取文本串中的拼音 (furigana) 字符。

PROPER

將文字串的首字母及任何非字母字符之后的首字母轉換成大寫。將其余的字母轉換成小寫。

REPLACE 使用其他文本串并根據所指定的字符數替換某REPLACE

文本串中的部分文本。

REPLACEB 使用其他文本串并根據所指定的字符數替換某文本串中的部分文本。此函數專為雙字節(jié)字符使用。

REPT

按照給定的次數重復顯示文本。可以通過函數 REPT 來不斷地重復顯示某一文字串，對單元格進行填充。

RIGHT 根據所指定的字符數返回文本串中最后一個或多RIGHT

個字符。

RIGHTB 根據所指定的字符數返回文本串中最后一個或多個字符。此函數用于雙字節(jié)字符。

SEARCH 返回從 start_num 開始首次找到特定字符或文本串的位置上特定字符的編號。使用 SEARCH 可確定字符或文本串在其他文本串中的位置，這樣就可使用 MID 或

REPLACE 函數更改文本。

SEARCH SEARCHB 也可在其他文本串 (within_text) 中查找文本串 (find_text)，并返回 find_text 的起始位置編號。此結果是基于每個字符所使用的字節(jié)數，并從 start_num

開始的。此函數用于雙字節(jié)字符。此外，也可使用 FINDB

在其他文本串中查找文本串。

在文字串中用 new_text 替代 old_text。如果需要在某SUBSTITUTE

一文字串中替換指定的文本，請使用函數 SUBSTITUTE；如果需要在某一文字串中替換指定位置處的任意文本，請使用函數 REPLACE。

t,start_num)

FIXED(number,decimals,no_commas)

JIS(text)

LEFT(text,num_chars)

LEFTB(text,num_bytes)

LEN(text)

LENB(text)

LOWER(text)

MID(text,start_num,num_chars)

MIDB(text,start_num,num_bytes)

PHONETIC(reference)

PROPER(text)

REPLACE(old_text,start_num,num_chars,new_text)

REPLACEB(old_text,start_num,num_bytes,new_text)

REPT(text,number_times)

RIGHT(text,num_chars)

RIGHTB(text,num_bytes)

SEARCH(find_text,within_text,start_num)

SEARCHB(find_text,within_text,start_num)

SUBSTITUTE(text,old_text,new_text,instance_num)

T

TEXT

TRIM

UPPER

VALUE

WIDECHAR

YEN

將數值轉換成文本。

將一數值轉換為按指定數字格式表示的文本。

除了單詞之間的單個空格外，清除文本中所有的空格。在從其他應用程序中獲取帶有不規(guī)則空格的文本時，可以使用函數 TRIM。

將文本轉換成大寫形式。

將代表數字的文字串轉換成數字。

將單字節(jié)字符轉換為雙字節(jié)字符。

使用 ￥（日圓）貨幣格式將數字轉換成文本，并對指定位置后的數字四舍五入。

T(value)

TEXT(value,format_text)

TRIM(text)

UPPER(text)

VALUE(text)

WIDECHAR(text)

YEN(number,decimals)

附二、日期與時間函數

函數名

DATE

DATEDIF

DATEVALUE

DAY

DAYS360

函數說明

返回代表特定日期的系列數。

計算兩個日期之間的天數、月數或年數。

函數 DATEVALUE 的主要功能是將以文字表示的日期轉換成一個系列數。

按照一年 360 天的算法（每個月以 30 天計，一年共計 12 個月），返回兩日期間相差的天數。

返回指定日期 (start_date) 之前或之后指定月份數的日期EDATE 系列數。使用函數 EDATE 可以計算與發(fā)行日處于一月中同一天的到期日的日期。

返回 start-date 之前或之后指定月份中最后一天的系列數。EOMONTH 用函數 EOMONTH 可計算特定月份中最后一天的時間系列數，用于證券的到期日等計算。

HOUR

MINUTE

MONTH

NETWORKDAYS

NOW

SECOND

TIME

返回時間值的小時數。即一個介于 0 (12:00 A.M.) 到 23

(11:00 P.M.) 之間的整數。

返回時間值中的分鐘。即一個介于 0 到 59 之間的整數。

返回以系列數表示的日期中的月份。月份是介于 1（一月）和

12（十二月）之間的整數。

工作日不包括周末和專門指定的假期

返回當前日期和時間所對應的系列數。

返回時間值的秒數。返回的秒數為 0 至 59 之間的整數。

返回某一特定時間的小數值，函數 TIME 返回的小數值為從 0

到 0.99999999 之間的數值，代表從 0:00:00 (12:00:00 A.M)

到 23:59:59 (11:59:59 P.M) 之間的時間。

TIMEVALUE

返回由文本串所代表的時間的小數值。該小數值為從 0 到

0.999999999 的數值，代表從 0:00:00 (12:00:00 AM) 到

語法

DATE(year,month,day)

DATEDIF(start_date,end_date,unit)

DATEVALUE(date_text)

返回以系列數表示的某日期的天數，用整數 1 到 31 表示。 DAY(rial_number)

DAYS360(start_date,end_date,method)

EDATE(start_date,months)

EOMONTH(start_date,months)

HOUR(rial_number)

MINUTE(rial_number)

MONTH(rial_number)

返回參數 start-data 和 end-data 之間完整的工作日數值。NETWORKDAYS(start_date,end_date,holidays)

NOW( )

SECOND(rial_number)

TIME(hour,minute,cond)

TIMEVALUE(time_text)

23:59:59 (11:59:59 PM) 之間的時間。

TODAY

WEEKDAY

WEEKNUM

WORKDAY

YEAR

YEARFRAC

excel

1

excel

2

excel

3

1

excel 256

1 65536 25

返回當前日期的系列數，系列數是 Microsoft excel 用于日期和時間計算的日期-時間代碼。

返回某日期為星期幾。默認情況下，其值為 1（星期天）到 7（星期六）之間的整數。

返回一個數字，該數字代表一年中的第幾周。

返回某日期（起始日期）之前或之后相隔指定工作日的某一日期的日期值。工作日不包括周末和專門指定的假日。

返回某日期的年份。返回值為 1900 到 9999 之間的整數。

返回 start_date 和 end_date 之間的天數占全年天數的百分比。

TODAY( )

WEEKDAY(rial_number,return_type)

WEEKNUM(rial_num,return_type)

WORKDAY(start_date,days,holidays)

YEAR(rial_number)

YEARFRAC(start_date,end_date,basis)

2

excel

excelexcel

1

1234

1

圖1

2、 COLUMN用于返回給定引用的列標。

語法形式為：COLUMN(reference)

Reference為需要得到其列標的單元格或單元格區(qū)域。如果省略 reference，則假定為是對函數 COLUMN 所在單元格的引用。如果 reference 為一個單元格區(qū)域，并且函數 COLUMN 作為水平數組輸入，則函數

COLUMN 將 reference 中的列標以水平數組的形式返回。但是Reference 不能引用多個區(qū)域。

3、 ROW用于返回給定引用的行號。

語法形式為：ROW(reference)

Reference為需要得到其行號的單元格或單元格區(qū)域。 如果省略 reference，則假定是對函數 ROW 所在單元格的引用。如果 reference 為一個單元格區(qū)域，并且函數 ROW 作為垂直數組輸入，則函數 ROW 將

reference 的行號以垂直數組的形式返回。但是Reference 不能對多個區(qū)域進行引用。

二、AREAS、COLUMNS、INDEX、ROWS

1、 AREAS用于返回引用中包含的區(qū)域個數。其中區(qū)域表示連續(xù)的單元格組或某個單元格。

其語法形式為AREAS(reference)

Reference為對某一單元格或單元格區(qū)域的引用，也可以引用多個區(qū)域。如果需要將幾個引用指定為一個參數，則必須用括號括起來。

2、 COLUMNS用于返回數組或引用的列數。

其語法形式為COLUMNS(array)

Array為需要得到其列數的數組、數組公式或對單元格區(qū)域的引用。

3、 ROWS用于返回引用或數組的行數。

其語法形式為ROWS(array)

Array為需要得到其行數的數組、數組公式或對單元格區(qū)域的引用。

以上各函數示例見圖2

圖2

4、 INDEX用于返回表格或區(qū)域中的數值或對數值的引用。

函數 INDEX() 有兩種形式：數組和引用。數組形式通常返回數值或數值數組；引用形式通常返回引用。

（1）INDEX(array,row_num,column_num) 返回數組中指定單元格或單元格數組的數值。

Array為單元格區(qū)域或數組常數。Row_num為數組中某行的行序號，函數從該行返回數值。Column_num為數組中某列的列序號，函數從該列返回數值。需注意的是Row_num 和 column_num 必須指向 array 中的某一單元格，否則，函數 INDEX 返回錯誤值 #REF!。

（2）INDEX(reference,row_num,column_num,area_num) 返回引用中指定單元格或單元格區(qū)域的引用。

Reference為對一個或多個單元格區(qū)域的引用。

Row_num為引用中某行的行序號，函數從該行返回一個引用。

Column_num為引用中某列的列序號，函數從該列返回一個引用。

需注意的是Row_num、column_num 和 area_num 必須指向 reference 中的單元格；否則，函數 INDEX 返回錯誤值 #REF!。如果省略 row_num 和 column_num，函數 INDEX 返回由 area_num 所指定的區(qū)域。

三、INDIRECT、OFFSET

1

2

22

44

2345

1

2

1

-2-1012

3

圖3

（2） 數組形式

函數 LOOKUP 的數組形式在數組的第一行或第一列查找指定的數值，然后返回數組的最后一行或最后一列中相同位置的數值。通常情況下，最好使用函數 HLOOKUP 或函數 VLOOKUP 來替代函數 LOOKUP 的數組形式。函數 LOOKUP 的這種形式主要用于與其他電子表格兼容。關于LOOKUP的數組形式的用法在此不再贅述，感興趣的可以參看excel的幫助。

3、 HLOOKUP與VLOOKUP

HLOOKUP用于在表格或數值數組的首行查找指定的數值，并由此返回表格或數組當前列中指定行處的數值。

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表示要查找的值，它必須位于自定義查找區(qū)域的最左列。Lookup_value 可以為數值、引用或文字串。

Table_array查找的區(qū)域，用于查找數據的區(qū)域，上面的查找值必須位于這個區(qū)域的最左列。可以使用對區(qū)域或區(qū)域名稱的引用。

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函數可以根據搜索區(qū)域內最左列的值，去查找區(qū)域內其它列的數據，并返回該列的數據，對于字母來說，搜索時不分大小寫。所以，函數VLOOKUP的查找可以達到兩種目的：一是精確的查找。二是近似的查找。下面分別說明。

（1） 精確查找--根據區(qū)域最左列的值，對其它列的數據進行精確的查找

示例：創(chuàng)建工資表與工資條

首先建立員工工資表

圖4

然后，根據工資表創(chuàng)建各個員工的工資條，此工資條為應用Vlookup函數建立。以員工Sandy（編號A001）的工資條創(chuàng)建為例說明。

第一步，拷貝標題欄

第二步，在編號處（A21）寫入A001

第三步，在姓名（B21）創(chuàng)建公式

=VLOOKUP($A21,$A$3:$H$12,2,FALSE)

語法解釋：在$A$3:$H$12范圍內（即工資表中）精確找出與A21單元格相符的行，并將該行中第二列的內容計入單元格中。

第四步，以此類推，在隨后的單元格中寫入相應的公式。

圖5

（2） 近似的查找--根據定義區(qū)域最左列的值，對其它列數據進行不精確值的查找

示例：按照項目總額不同提取相應比例的獎金

第一步，建立一個項目總額與獎金比例的對照表，如圖6所示。項目總額的數字均為大于情況。即項目總額

在0~5000元時，獎金比例為1%，以此類推。

圖6

第二步 假定某項目的項目總額為13000元，在B11格中輸入公式

=VLOOKUP(A11,$A$4:$B$8,2,TRUE)

即可求得具體的獎金比例為5%，如圖7。

圖7

4、 MATCH函數

MATCH函數有兩方面的功能，兩種操作都返回一個位置值。

一是確定區(qū)域中的一個值在一列中的準確位置，這種精確的查詢與列表是否排序無關。

二是確定一個給定值位于已排序列表中的位置，這不需要準確的匹配.

語法結構為：MATCH(lookup_value,lookup_array,match_type)

lookup_value為要搜索的值。

011

excelexcelword

1

2

3

4excelexcelexcel

5excelword

1

29

1 29 1

29

1 29

2、TRANSPOSE函數

TRANSPOSE用于返回區(qū)域的轉置。函數 TRANSPOSE 必須在某個區(qū)域中以數組公式的形式輸入，該區(qū)域的行數和列數分別與 array 的列數和行數相同。使用函數 TRANSPOSE 可以改變工作表或宏表中數組的垂直或水平走向。

語法形式為TRANSPOSE(array)

Array為需要進行轉置的數組或工作表中的單元格區(qū)域。所謂數組的轉置就是，將數組的第一行作為新數組的第一列，數組的第二行作為新數組的第二列，以此類推。

示例，將原來為橫向排列的業(yè)績表轉置為縱向排列。

圖8

第一步，由于需要轉置的為多個單元格形式，因此需要以數組公式的方法輸入公式。故首先選定需轉置的范圍。此處我們設定轉置后存放的范圍為A9.B14.

第二步，單擊常用工具欄中的"粘貼函數"圖標，將出現"粘貼函數"對話框，在"函數分類"框中選擇"查找與引用函數"框中選擇TRANSPOSE，此時在對話框的底部將出現該函數的簡短解釋。 單擊"確定"后將彈出TRANSPOSE函數參數設置對話框。

圖9

第三步，選擇數組的范圍即A2.F3

第四步，由于此處是以數組公式輸入，因此需要按 CRTL+SHIFT+ENTER 組合鍵來確定為數組公式，此時會在公式中顯示"{}"。隨即轉置成功，如圖10所示。

圖10

以上我們介紹了excel的查找與引用函數，此類函數的靈活應用對于減少重復數據的錄入是大有裨益的。此處只做了些拋磚引玉的示例，相信大家會在實際運用中想出更具實用性的應用方法。[dvnews_page=excel函數應用之統計函數]excel的統計工作表函數用于對數據區(qū)域進行統計分析。例如，統計工作表函數可以

excel

1 130

2

0.2 20 4 20 0.2 2

2 2

3

圖1

二、用于求單元格個數的統計函數COUNT

語法形式為COUNT(value1,value2, ...)

其中Value1, value2, ...為包含或引用各種類型數據的參數（1～30個），但只有數字類型的數據才被計數。函數 COUNT 在計數時，將把數字、空值、邏輯值、日期或以文字代表的數計算進去；但是錯誤值或其他無法轉化成數字的文字則被忽略。

如果參數是一個數組或引用，那么只統計數組或引用中的數字；數組中或引用的空單元格、邏輯值、文字或錯誤值都將忽略。如果要統計邏輯值、文字或錯誤值，應當使用函數 COUNTA。

舉例說明COUNT函數的用途，示例中也列舉了帶A的函數COUNTA的用途。仍以上例為例，要計算一共有多少評委參與評分（用函數COUNTA），以及有幾個評委給出了有效分數（用函數COUNT）。

圖2

三、求區(qū)域中數據的頻率分布FREQUENCY

由于函數 FREQUENCY 返回一個數組，必須以數組公式的形式輸入。

語法形式為FREQUENCY(data_array,bins_array)

其中Data_array為一數組或對一組數值的引用，用來計算頻率。如果 data_array 中不包含任何數值，函數 FREQUENCY 返回零數組。Bins_array為一數組或對數組區(qū)域的引用，設定對 data_array 進行頻率計

算的分段點。如果 bins_array 中不包含任何數值，函數 FREQUENCY 返回 data_array 元素的數目。

看起來FREQUENCY的用法蠻復雜的，但其用處很大。比如可以計算不同工資段的人員分布，公司員工的年齡分布，學生成績的分布情況等。這里以具體示例說明其基本的用法。

以計算某公司的員工年齡分布情況為例說明。在工作表里列出了員工的年齡。這些年齡為 28、25、31、21、44、33、22 和 35，并分別輸入到單元格 C4:C11。這一列年齡就是 data_array。Bins_array 是另一列用來對年齡分組的區(qū)間值。在本例中，bins_array 是指 C13:C16 單元格，分別含有值 25、30、35、和 40。以數組形式輸入函數 FREQUENCY，就可以計算出年齡在 25歲以下、26~30歲、31~35歲、36~40歲和40歲以上各區(qū)間中的數目。本例中選擇了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非常想像，區(qū)別在于它們返回的不是極值，而是第K個值。語法形式為：函數(array,k)，其中Array為需要找到第 k 個最小值的數組或數字型數據區(qū)域。K為返回的數據在數組或數據區(qū)域里的位置（如果是LARGE為從大到小排，若為SMALL函數則從小到大排）。

說到這，大家可以想得到吧。如果K=1或者K=n（假定數據集中有n個數據）的時候，是不是就可以返回數據集的最大值或者最小值了呢。

3、 求數據集中的中位數MEDIAN

MEDIAN函數返回給定數值集合的中位數。所謂中位數是指在一組數據中居于中間的數，換句話說，在這組數據中，有一半的數據比它大，有一半的數據比它小。

語法形式為MEDIAN(number1,number2, ...)其中Number1, number2,...是需要找出中位數的 1 到 30 個數字參數。如果數組或引用參數中包含有文字、邏輯值或空白單元格，則忽略這些值，但是其值為零的單元格會計算在內。

需要注意的是，如果參數集合中包含有偶數個數字，函數 MEDIAN 將返回位于中間的兩個數的平均值。

4、 求數據集中出現頻率最多的數MODE

MODE函數用來返回在某一數組或數據區(qū)域中出現頻率最多的數值。跟 MEDIAN 一樣，MODE 也是一個位置測量函數。

語法形式為MODE(number1,number2, ...)其中Number1, number2, ... 是用于眾數（眾數指在一組數值中出現頻率最高的數值）計算的 1 到 30 個參數，也可以使用單一數組（即對數組區(qū)域的引用）來代替由逗號分隔的參數。

5、 以上函數的示例

以某單位年終獎金分配表為例說明。在示例中，我們將利用這些函數求解該單位年終獎金分配中的最高金額、最低金額、平均金額、中間金額、眾數金額以及第二高金額等。

詳細的公式寫法可從圖中清楚的看出，在此不再贅述。

圖4

五、用來排位的函數RANK、PERCENTRANK

1、一個數值在一組數值中的排位的函數RANK

數值的排位是與數據清單中其他數值的相對大小，當然如果數據清單已經排過序了，則數值的排位就是它

當前的位置。數據清單的排序可以使用excel提供的排序功能完成。

語法形式為RANK(number,ref,order) 其中Number為需要找到排位的數字；Ref 為包含一組數字的數組或引用。Order為一數字用來指明排位的方式。

如果 order 為 0 或省略，則excel 將 ref 當作按降序排列的數據清單進行排位。

如果 order 不為零，Microsoft excel 將 ref 當作按升序排列的數據清單進行排位。

需要說明的是，函數 RANK 對重復數的排位相同。但重復數的存在將影響后續(xù)數值的排位。嗯，這就好像并列第幾的概念啊。例如，在一列整數里，如果整數 10 出現兩次，其排位為 5，則 11 的排位為 7（沒有排位為 6 的數值）。

2、求特定數值在一個數據集中的百分比排位的函數PERCENTRANK

此PERCENTRANK函數可用于查看特定數據在數據集中所處的位置。例如，可以使用函數 PERCENTRANK 計算某個特定的能力測試得分在所有的能力測試得分中的位置。

語法形式為PERCENTRANK(array,x,significance) 其中Array為彼此間相對位置確定的數字數組或數字區(qū)域。X為數組中需要得到其排位的值。Significance為可選項，表示返回的百分數值的有效位數。如果省略，函數 PERCENTRANK 保留 3 位小數。

3、與排名有關的示例

仍以某單位的年終獎金分配為例說明，這里以員工Annie的排名為例說明公式的寫法。

獎金排名的公式寫法為：

=RANK(C3,$C$3:$C$12)

百分比排名的公式寫法為：

=PERCENTRANK($C$3:$C$12,C3)

圖5

以上我們介紹了excel統計函數中比較常用的幾種函數，更多的涉及專業(yè)領域的統計函數可以參看附表以及各種相關的統計學書籍。

附表：

函數名稱

AVEDEV

AVERAG度。

函數說明

返回一組數據與其均值的絕對偏差的平均值，即離散返回參數算術平均值。

語法形式

AVEDEV(number1,number2, ...)

AVERAGE(number1,number2,

E

AVERAG計算參數清單中數值的平均值（算數平均值）。不僅數EA

字，而且文本和邏輯值（如TRUE 和 FALSE）也將計算在內。

BETADI返回 Beta 分布累積函數的函數值。Beta 分布累積函ST

數通常用于研究樣本集合中某些事物的發(fā)生和變化情況。

返回 beta 分布累積函數的逆函數值。即，如果

BETAINprobability = BETADIST(x,...)，則

V

BETAINV(probability,...) = x。beta 分布累積函數可用于項目設計，在給定期望的完成時間和變化參數后，模擬可能的完成時間。

BINOMDIST

返回一元二項式分布的概率值。

CHIDIS返回 γ2 分布的單尾概率。γ2 分布與 γ2 檢驗相關。T

使用 γ2 檢驗可以比較觀察值和期望值。

CHIINV

返回 γ2 分布單尾概率的逆函數。

CHITES返回獨立性檢驗值。函數 CHITEST 返回 γ2 分布的統T

計值及相應的自由度。

CONFID返回總體平均值的置信區(qū)間。置信區(qū)間是樣本平均值任ENCE

意一側的區(qū)域。

CORREL

返回單元格區(qū)域 array1 和 array2 之間的相關系數。使用相關系數可以確定兩種屬性之間的關系。

COUNT

返回參數的個數。利用函數 COUNT 可以計算數組或單元格區(qū)域中數字項的個數。

COUNTA

返回參數組中非空值的數目。利用函數COUNTA 可以計算數組或單元格區(qū)域中數據項的個數。

COVAR

返回協方差，即每對數據點的偏差乘積的平均數，利用協方差可以決定兩個數據集之間的關系。

CRITBI返回使累積二項式分布大于等于臨界值的最小值。此函NOM

數可以用于質量檢驗。

DEVSQ

返回數據點與各自樣本均值偏差的平方和。

EXPOND返回指數分布。使用函數 EXPONDIST 可以建立事件之IST

間的時間間隔模型。

FDIST

返回 F 概率分布。使用此函數可以確定兩個數據系列是否存在變化程度上的不同。

FINV

返回 F 概率分布的逆函數值。

FISHER

返回點 x 的 Fisher 變換。該變換生成一個近似正態(tài)分布而非偏斜的函數。

...)

AVERAGEA(value1,value2,...)

BETADIST(x,alpha,beta,A,B)

BETAINV(probability,alpha,beta,A,B)

BINOMDIST(number_s,trials,probability_s,cumulative)

CHIDIST(x,degrees_freedom)

CHIINV(probability,degrees_freedom)

CHITEST(actual_range,expected_range)

CONFIDENCE(alpha,standard_dev,size)

CORREL(array1,array2)

COUNT(value1,value2, ...)

COUNTA(value1,value2, ...)COVAR(array1,array2)

CRITBINOM(trials,probability_s,alpha)

DEVSQ(number1,number2,...)

EXPONDIST(x,lambda,cumulative)

FDIST(x,degrees_freedom1,degrees_freedom2)

FINV(probability,degrees_freedom1,degrees_freedom2)

FISHER(x)

FISHERINV

FORECAST

FREQUENCY

返回 Fisher 變換的逆函數值。使用此變換可以分析數據區(qū)域或數組之間的相關性。

根據給定的數據計算或預測未來值。

以一列垂直數組返回某個區(qū)域中數據的頻率分布。

返回 F 檢驗的結果。F 檢驗返回的是當數組 1 和數組

FISHERINV(y)

FORECAST(x,known_y's,known_x's)

FREQUENCY(data_array,bins_array)

FTEST

2 的方差無明顯差異時的單尾概率。可以使用此函數來判斷兩個樣本的方差是否不同。

GAMMAD返回伽瑪分布。可以使用此函數來研究具有偏態(tài)分布的IST

變量。伽瑪分布通常用于排隊分析。

GAMMAINV

返回伽瑪分布的累積函數的逆函數。

GAMMALN

返回伽瑪函數的自然對數，Γ(x)。

GEOMEAN

返回正數數組或數據區(qū)域的幾何平均值。

GROWTH

根據給定的數據預測指數增長值。

HARMEA返回數據集合的調和平均值。調和平均值與倒數的算術N

平均值互為倒數。

HYPGEOMDIST

返回超幾何分布。

INTERCEPT

利用已知的 x 值與 y 值計算直線與 y 軸的截距。

KURT

返回數據集的峰值。

LARGE

返回數據集里第 k 個最大值。使用此函數可以根據相對標準來選擇數值。

LINEST

使用最小二乘法計算對已知數據進行最佳直線擬合，并返回描述此直線的數組。

LOGEST

在回歸分析中，計算最符合觀測數據組的指數回歸擬合曲線，并返回描述該曲線的數組。

LOGINV

返回 x 的對數正態(tài)分布累積函數的逆函數。

LOGNORMDIST

返回 x 的對數正態(tài)分布的累積函數。

MAX

返回數據集中的最大數值。

MAXA

返回參數清單中的最大數值。

MEDIAN

返回給定數值集合的中位數。中位數是在一組數據中居于中間的數。

MIN

返回給定參數表中的最小值。

MINA

返回參數清單中的最小數值。

FTEST(array1,array2)

GAMMADIST(x,alpha,beta,cumulative)

GAMMAINV(probability,alpha,beta)

GAMMALN(x)

GEOMEAN(number1,number2,

...)

GROWTH(known_y's,known_x's,new_x's,const)

HARMEAN(number1,number2,

...)

HYPGEOMDIST(sample_s,number_sample,

population_s,number_population)

INTERCEPT(known_y's,known_x's)

KURT(number1,number2, ...)LARGE(array,k)

LINEST(known_y's,known_x's,const,stats)

LOGEST(known_y's,known_x's,const,stats)

LOGINV(probability,mean,standard_dev)

LOGNORMDIST(x,mean,standard_dev)

MAX(number1,number2,...)

MAXA(value1,value2,...)

MEDIAN(number1,number2, ...)

MIN(number1,number2, ...)

MINA(value1,value2,...)

MODE

NEGBINOMDIST

NORMDIST

NORMINV

NORMSD返回在某一數組或數據區(qū)域中出現頻率最多的數值。

返回負二項式分布。

返回給定平均值和標準偏差的正態(tài)分布的累積函數。

返回給定平均值和標準偏差的正態(tài)分布的累積函數的逆函數。

返回標準正態(tài)分布的累積函數，該分布的平均值為 0，MODE(number1,number2, ...)

NEGBINOMDIST(number_f,number_s,probability_s)

NORMDIST(x,mean,standard_dev,cumulative)

NORMINV(probability,mean,standard_dev)

IST

標準偏差為 1。

NORMSI返回標準正態(tài)分布累積函數的逆函數。該分布的平均值NV

為 0，標準偏差為 1。

返回 Pearson（皮爾生）乘積矩相關系數，r，這是一PEARSO個范圍在 -1.0 到 1.0 之間（包括 -1.0 和 1.0 在N

內）的無量綱指數，反映了兩個數據集合之間的線性相關程度。

PERCEN返回數值區(qū)域的 K 百分比數值點。可以使用此函數來TILE

建立接受閥值。例如，可以確定得分排名在 90 個百分點以上的檢測侯選人。

返回特定數值在一個數據集中的百分比排位。此函數可PERCEN用于查看特定數據在數據集中所處的位置。例如，可以TRANK

使用函數 PERCENTRANK 計算某個特定的能力測試得分在所有的能力測試得分中的位置。

返回從給定數目的對象集合中選取的若干對象的排列PERMUT

數。排列可以為有內部順序的對象或為事件的任意集合或子集。排列與組合不同，組合的內部順序無意義。此函數可用于彩票計算中的概率。

POISSO返回泊松分布。泊松分布通常用于預測一段時間內事件N

發(fā)生的次數，比如一分鐘內通過收費站的轎車的數量。返回一概率事件組中落在指定區(qū)域內的事件所對應的PROB

概率之和。如果沒有給出 upper_limit，則返回 x

_range 內值等于 lower_limit 的概率。

QUARTI返回數據集的四分位數。四分位數通常用于在銷售額和LE

測量值數據集中對總體進行分組。例如，可以使用函數QUARTILE 求得總體中前 25% 的收入值。

返回一個數值在一組數值中的排位。數值的排位是與數RANK

據清單中其他數值的相對大小（如果數據清單已經排過序了，則數值的排位就是它當前的位置）。

返回根據 known_y's 和 known_x's 中數據點計算得RSQ

出的 Pearson 乘積矩相關系數的平方。有關詳細信息，請參閱函數 REARSON。R 平方值可以解釋為 y 方差與

x 方差的比例。

返回分布的偏斜度。偏斜度反映以平均值為中心的分布SKEW

的不對稱程度。正偏斜度表示不對稱邊的分布更趨向正值。負偏斜度表示不對稱邊的分布更趨向負值。

NORMSDIST(z)

NORMSINV(probability)

PEARSON(array1,array2)

PERCENTILE(array,k)

PERCENTRANK(array,x,significance)

PERMUT(number,number_chon)

POISSON(x,mean,cumulative)

PROB(x_range,prob_range,lower_limit,upper_limit)

QUARTILE(array,quart)

RANK(number,ref,order)

RSQ(known_y's,known_x's)

SKEW(number1,number2,...)

返回根據 known_y's 和 known_x's 中的數據點擬合SLOPE

的線性回歸直線的斜率。斜率為直線上任意兩點的重直距離與水平距離的比值，也就是回歸直線的變化率。

SMALL

返回數據集中第 k 個最小值。使用此函數可以返回數據集中特定位置上的數值。

STANDA返回以 mean 為平均值，以 standard-dev 為標準偏差RDIZE

的分布的正態(tài)化數值。

STDEV

估算樣本的標準偏差。標準偏差反映相對于平均值（mean）的離散程度。

估算基于給定樣本的標準偏差。標準偏差反映數值相對STDEVA

于平均值（mean）的離散程度。文本值和邏輯值（如 TRUE

或 FALSE）也將計算在內。

STDEVP

返回以參數形式給出的整個樣本總體的標準偏差。標準偏差反映相對于平均值（mean）的離散程度。

STDEVP計算樣本總體的標準偏差。標準偏差反映數值相對于平A

均值（mean）的離散程度。

返回通過線性回歸法計算 y 預測值時所產生的標準誤STEYX

差。標準誤差用來度量根據單個 x 變量計算出的 y 預測值的誤差量。

返回學生 t- 分布的百分點（概率），t 分布中數值 (x)

TDIST

是 t 的計算值（將計算其百分點）。t 分布用于小樣本數據集合的假設檢驗。使用此函數可以代替 t 分布的臨界值表。

TINV

返回作為概率和自由度函數的學生 t 分布的 t 值。

返回一條線性回歸擬合線的一組縱坐標值（y 值）。即TREND

找到適合給定的數組 known_y's 和 known_x's 的直線（用最小二乘法），并返回指定數組 new_x's 值在直線上對應的 y 值。

返回數據集的內部平均值。函數 TRIMMEAN 先從數據集TRIMME的頭部和尾部除去一定百分比的數據點，然后再求平均AN

值。當希望在分析中剔除一部分數據的計算時，可以使用此函數。

返回與學生氏- t 檢驗相關的概率。可以使用函數

TTEST

TTEST 判斷兩個樣本是否可能來自兩個具有相同均值的總體。

VAR

估算樣本方差。

VARA

估算基于給定樣本的方差。不僅數字，文本值和邏輯值（如 TRUE 和 FALSE）也將計算在內。

VARP

計算樣本總體的方差。

VARPA

計算樣本總體的方差。不僅數字，文本值和邏輯值（如TRUE 和 FALSE）也將計算在內。

WEIBUL返回韋伯分布。使用此函數可以進行可靠性分析，比如L

計算設備的平均故障時間。

SLOPE(known_y's,known_x's)

SMALL(array,k)

STANDARDIZE(x,mean,standard_dev)

STDEV(number1,number2,...)

STDEVA(value1,value2,...)STDEVP(number1,number2,...)

STDEVPA(value1,value2,...)

STEYX(known_y's,known_x's)

TDIST(x,degrees_freedom,tails)

TINV(probability,degrees_freedom)

TREND(known_y's,known_x's,new_x's,const)

TRIMMEAN(array,percent)

TTEST(array1,array2,tails,type)

VAR(number1,number2,...)

VARA(value1,value2,...)

VARP(number1,number2,...)VARPA(value1,value2,...)

WEIBULL(x,alpha,beta,cumulative)

返回 z 檢驗的雙尾 P 值。Z 檢驗根據數據集或數組生ZTEST

成 x 的標準得分，并返回正態(tài)分布的雙尾概率。可以使用此函數返回從某總體中抽取特定觀測值的似然估計。

[dvnews_page=excel函數應用之工程函數]excel的工程函數與統計函數類似，都是屬于比較專業(yè)范疇的函數。因此，在文中筆者也僅介紹幾種比較常用的工程函數，更多的請參考excel幫助和專業(yè)的書籍。顧名思義，工程工作表函數就是用于工程分析的函數。excel中一共提供了近40個工程函數。工程工作表函數由"分析工具庫"提供。如果您找不到此類函數的話，可能需要安裝"分析工具庫"。

一、"分析工具庫"的安裝

如圖所示

ZTEST(array,x,sigma)

圖1

（1）在"工具"菜單中，單擊"加載宏"命令。

（2）如果"加載宏"對話框中沒有"分析工具庫"，請單擊"瀏覽"按鈕，定位到"分析工具庫"加載宏文件""所在的驅動器和文件夾（通常位于"Microsoft officeofficeLibraryAnalysis"文件夾中）；如果沒有找到該文件，應運行"安裝"程序。

（3） 選中"分析工具庫"復選框。

二、工程函數的分類

在excel幫助系統中將工程函數大體可分為三種類型，即：

（1）對復數進行處理的函數

（2）在不同的數字系統（如十進制系統、十六進制系統、八進制系統和二進制系統）間進行數值轉換的函數

（3）在不同的度量系統中進行數值轉換的函數

在文中為了對函數的解釋更清晰，筆者把工程函數分為如下的六種類型，即：

（1）貝賽爾（Besl）函數

（2）在不同的數字系統間進行數值轉換的函數

（3）用于篩選數據的函數

（4）度量衡轉換函數

（5）與積分運算有關的函數

（6）對復數進行處理的函數