Excel統(tǒng)計(jì)函數(shù)查詢手冊(cè)（二）

Excel統(tǒng)計(jì)函數(shù)查詢手冊(cè)

FREQUENCY函數(shù)

本文介紹MicrosoftExcel中FREQUENCY函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或

多個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)

或復(fù)雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

計(jì)算數(shù)值在某個(gè)區(qū)域內(nèi)的出現(xiàn)頻率，然后返回一個(gè)垂直數(shù)組。例如，使用函數(shù)FREQUENCY可以

在分?jǐn)?shù)區(qū)域內(nèi)計(jì)算測(cè)驗(yàn)分?jǐn)?shù)的個(gè)數(shù)。由于函數(shù)FREQUENCY返回一個(gè)數(shù)組，所以它必須以數(shù)組公

式的形式輸入。

語(yǔ)法

FREQUENCY（data_array,bins_array）

FREQUENCY函數(shù)語(yǔ)法具有下列參數(shù)（參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的

值）：

Data_array必需。一個(gè)值數(shù)組或?qū)σ唤M數(shù)值的引用，您要為它計(jì)算頻率。如果data_array

中不包含任何數(shù)值，函數(shù)FREQUENCY將返回一個(gè)零數(shù)組。

Bins_array必需。一個(gè)區(qū)間數(shù)組或?qū)^(qū)間的引用，該區(qū)間用于對(duì)data_array中的數(shù)值進(jìn)

行分組。如果bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回的值與data_array中

的元素個(gè)數(shù)相等。

說(shuō)明

在選擇了用于顯示返回的分布結(jié)果的相鄰單元格區(qū)域后，函數(shù)FREQUENC丫應(yīng)以數(shù)組公

式的形式輸入。

返回的數(shù)組中的元素個(gè)數(shù)比bins_array中的元素個(gè)數(shù)多1個(gè)。多出來(lái)的元素表示最高區(qū)

間之上的數(shù)值個(gè)數(shù)。例如，如果要為三個(gè)單元格中輸入的三個(gè)數(shù)值區(qū)間計(jì)數(shù)，請(qǐng)務(wù)必在四個(gè)單

元格中輸入FREQUENC丫函數(shù)獲得計(jì)算結(jié)果。多出來(lái)的單元格將返回data_array中第三個(gè)

區(qū)間值以上的數(shù)值個(gè)數(shù)。

函數(shù)FREQUENC丫將忽略空白單元格和文本。

對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1分?jǐn)?shù)區(qū)間分割點(diǎn)

27970

38579

47889

585

650

781

895

988

1097

11公式說(shuō)明(結(jié)果)

小于或等于70的分?jǐn)?shù)個(gè)數(shù)

=FREQUENCY(A2:A10,B2:B4)(1)

12

71-79區(qū)間內(nèi)的分?jǐn)?shù)個(gè)數(shù)(2)

13

80-89區(qū)間內(nèi)的分?jǐn)?shù)個(gè)數(shù)(4)

162

14大于或等于90的分?jǐn)?shù)的個(gè)數(shù)(2)

15

注釋示例中的公式必須以數(shù)組公式輸入。將示例復(fù)制到空白工作表之后，請(qǐng)選擇區(qū)域A12:A15,

按F2,再按Ctrl+Shift+Entero如果公式未以數(shù)組公式的形式輸入，則單元格A12中只有一個(gè)結(jié)

果⑴。

GAMMA.DIST函數(shù)

返回伽瑪分布?？梢允褂么撕瘮?shù)來(lái)研究具有偏態(tài)分布的變量。伽瑪分布通常用于排隊(duì)分析。

語(yǔ)法

GAMMA.DIST(x,alpha,beta,cumulative)

GAMMA.DIST函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息

m)：

X必需。用來(lái)計(jì)算分布的值。

Alpha必需。分布參數(shù)。

Beta必需。分布參數(shù)。如果beta=1,GAMMA.DIST返回標(biāo)準(zhǔn)伽瑪分布。

Cumulative必需。決定函數(shù)形式的邏輯值。如果Cumulative為T(mén)RUE,函數(shù)

GAMMA.DIST返回累積分布函數(shù)；如果為FALSE,則返回概率密度函數(shù)。

說(shuō)明

如果x、alpha或beta為非數(shù)值型，則GAMMA.DIST返回錯(cuò)誤值#VALUE!。

如果x<0,則GAMMA.DIST返回錯(cuò)誤值#NUM!?

如果alphaV0或beta<0,則GAMMA.DIST返回錯(cuò)誤值#NUM!?

163

伽瑪概率密度函數(shù)的計(jì)算公式如下:

1

/（七？/）="kt-＜產(chǎn)久萬(wàn)

八，，/"「（而

標(biāo)準(zhǔn)伽瑪概率密度函數(shù)為：

一?

/（z'^=TuT

當(dāng)alpha=1時(shí)，GAMMA.DIST返回如下的指數(shù)分布：

-,-7

對(duì)于正整數(shù)n,當(dāng)alpha=n/2,beta=2且cumulative=TRUE時(shí)，GAMMA.DIST以

自由度n返回（1-CHISQ.DIST.RT（x））?

當(dāng)alpha為正整數(shù)時(shí)，GAMMA.DIST也稱為愛(ài)爾朗（Erlang）分布。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)說(shuō)明

10.00001131用來(lái)計(jì)算分布的值

2

9Alpha分布參數(shù)

3

2Beta分布參數(shù)

4

公式說(shuō)明（結(jié)果）

5

=GAMMA.DIST(A2,A3,A4,FALSE)在上述條件下的概率伽瑪分布（.03263913）

164

6=GAMMA.DIST(A2,A3,A4,TRUE)在上述條件下的累積伽瑪分布(0.068094)

7

GAMMA.INV函數(shù)

返回伽瑪累積分布的反函數(shù)。如果p=GAMMA.DIST(x,...)，貝UGAMMA.INV(p....)=x?

使用此函數(shù)可研究可能出現(xiàn)偏態(tài)分布的變量。

語(yǔ)法

GAMMA.INV(probability,alpha,beta)

GAMMA.INV函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的

值。)：

jProbability必需。與伽瑪分布相關(guān)的概率。

宓A(chǔ)lpha必需。分布參數(shù)。

3Beta必需。分布參數(shù)。如果beta=1,GAMMA.INV返回標(biāo)準(zhǔn)伽瑪分布。

說(shuō)明

己如果任意參數(shù)為文本型，則GAMMA.INV返回錯(cuò)誤值#VALLE!。

&如果probability<0或probability>1,則GAMMA.INV返回錯(cuò)誤值#NUML

3如果alphaV0或beta<0,則GAMMA.INV返回錯(cuò)誤值#NUM!。

如果已給定概率值，則GAMMA.INV使用GAMMA.DIST(x,alpha,beta,TRUE)=probability求解

數(shù)值X。因此，GAMMA.INV的精度取決于GAMMA.DIST的精度。GAMMA.INV使用迭代搜索技

術(shù)。如果搜索在64次迭代之后沒(méi)有收斂，則函數(shù)返回錯(cuò)誤值#N/A。

165

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)說(shuō)明

20.068094伽瑪分布的概率值

39Alpha分布參數(shù)

42Beta分布參數(shù)

5公式說(shuō)明（結(jié)果）

=GAMMA.INV(A2,A3,A4)在上述條件下伽瑪累積分布函數(shù)的反函數(shù)（10.00001131）

6

GAMMALN函數(shù)

本文介紹MicrosoftExcel中GAMMALN函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多

個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或

復(fù)雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

返回伽瑪函數(shù)的自然對(duì)數(shù)，「（X）。

語(yǔ)法

GAMMALN(x)

166

GAMMALN函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。)：

X必需。用于進(jìn)行GAMMALN函數(shù)計(jì)算的數(shù)值。

說(shuō)明

如果x為非數(shù)值型，函數(shù)GAMMALN返回錯(cuò)誤值#VALUE!。

如果x<0,函數(shù)GAMMALN返回錯(cuò)誤值#NUM!.

數(shù)字e的GAMMALN(i)次嘉等于(i-1)!,其中i為整數(shù)。

函數(shù)GAMMALN的計(jì)算公式如下：

GAMMALN=U7(r(x))

式中：

00

r(x)=je^u^du

n

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1公式說(shuō)明(結(jié)果)

2=GAMMALN(4)4的伽瑪函數(shù)的自然對(duì)數(shù)(1.791759)

167

GAMMALN.PRECISE函數(shù)

本文介紹MicrosoftExcel中GAMMALN.PRECISE函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)

一個(gè)或多個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行

很長(zhǎng)或復(fù)雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

返回y函數(shù)的自然對(duì)數(shù)「（X）。

語(yǔ)法

GAMMALN.PREClSE（x）

GAMMALN.PRECISE函數(shù)語(yǔ)法具有下列參數(shù)（參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提

供信息的值。）：

X必需。用于進(jìn)行GAMMALN.PRECISE函數(shù)計(jì)算的數(shù)值。

說(shuō)明

如果x為非數(shù)值型，則GAMMALN.PRECISE返回錯(cuò)誤值#VALU日。

如果x<0,則GAMMALN.PRECISE返回錯(cuò)誤值#NUM!?

數(shù)字e的GAMMALN.PRECISE（i）次暴返回與（i-1）!相同的結(jié)果，其中i為整數(shù)。

GAMMALN.PRECISE計(jì)算公式如下：

GAMMALN.PRECISE=LN（「（x））

式中：

CO

r（x）=Je~uux-ldu

n

168

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

說(shuō)明（結(jié)果）

=GAMMALN.PRECISE(4)4的Y函數(shù)的自然對(duì)數(shù)（1.791759）

GEOMEAN函數(shù)

本文介紹MicrosoftExcel+GEOMEAN函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多

個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或

復(fù)雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

返回正數(shù)數(shù)組或區(qū)域的兒何平均值。例如，可以使用函數(shù)GEOMEAN計(jì)算可變復(fù)利的平均增長(zhǎng)率。

語(yǔ)法

GEOMEAN(numberl,[number2],...)

GEOMEAN函數(shù)語(yǔ)法具有下列參數(shù)（參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。）：

Numberl,number2,...Numbed是必需的，后續(xù)數(shù)值是可選的。這是用于計(jì)算平均值的

一組參數(shù)，參數(shù)的個(gè)數(shù)可以為1到255個(gè)。也可以用單一數(shù)組或?qū)δ硞€(gè)數(shù)組的引用來(lái)代替用逗

號(hào)分隔的參數(shù)。

169

說(shuō)明

參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。

邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。

如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的

單元格將計(jì)算在內(nèi)。

如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。

如果任何數(shù)據(jù)點(diǎn)小于0,函數(shù)GEOMEAN返回錯(cuò)誤值#NUM!。

幾何平均值的計(jì)算公式如下：

G%7yly2y3…y”

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)

24

5

48

57

611

74

171

170

83

9公式說(shuō)明(結(jié)果)

10=GEOMEAN(A2:A8)上述數(shù)據(jù)集的幾何平均值(5.476987)

GROWTH函數(shù)

本文介紹MicrosoftExcel中GROWTH函數(shù)(函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多個(gè)

值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或復(fù)雜

的計(jì)算時(shí)。)的公式語(yǔ)法和用法。

說(shuō)明

根據(jù)現(xiàn)有的數(shù)據(jù)預(yù)測(cè)指數(shù)增長(zhǎng)值。根據(jù)現(xiàn)有的x值和y值，GROWTH函數(shù)返回一組新的x值對(duì)

應(yīng)的y值?？梢允褂肎ROWTH工作表函數(shù)來(lái)擬合滿足現(xiàn)有x值和y值的指數(shù)曲線。

語(yǔ)法

GROWTH(known_y,s,[known_x's],[new_x*s],[const])

GROWTH函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。)：

Known_『s必需。滿足指數(shù)回歸擬合曲線y=b*mAx的一組已知的y值。

如果數(shù)組known_y's在單獨(dú)一列中，則known_x's的每一列被視為一個(gè)獨(dú)立的變量。

如果數(shù)組known_y's在單獨(dú)一行中，則known_x's的每一行被視為一個(gè)獨(dú)立的變量。

如果known_y*s中的任何數(shù)為零或?yàn)樨?fù)數(shù)，GROWTH函數(shù)將返回錯(cuò)誤值#NUM!。

Known_x*s可選。滿足指數(shù)回歸擬合曲線y=b*mAx的一組已知的可選x值。

171

171

數(shù)組known_x's可以包含一組或多組變量。如果僅使用一個(gè)變量，那么只要known_x's

和known_y,s具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果用到多個(gè)變量，則

known_y，s必須為向量（即必須為一行或一列）。

如果省略known_x,s,則假設(shè)該數(shù)組為{1,2,3,...},其大小與known_y's相同。

New_x*s可選。需要通過(guò)GROWTH函數(shù)為其返回對(duì)應(yīng)y值的一組新x值。

New_x's與known_x's一樣，對(duì)每個(gè)自變量必須包括單獨(dú)的一列（或一行）。因此，如

果known_y*s是單列的，known_x*s和new_x*s應(yīng)該有同樣的列數(shù)。如果known_y's

是單行的，known_x'sfnnew_x，s應(yīng)該有同樣的行數(shù)。

如果省略new_x's,則假設(shè)它和known_x，s相同。

如果known_x，s與new_x，s都被省略，則假設(shè)它們?yōu)閿?shù)組{1,2,3,…},其大小與

known_y*s相同。

Const可選。一邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為1。

如果const為T(mén)RUE或省略，b將按正常計(jì)算。

A

如果const為FALSE,b將設(shè)為1,m值將被調(diào)整以滿足y=mx0

說(shuō)明

對(duì)于返回結(jié)果為數(shù)組的公式，在選定正確的單元格個(gè)數(shù)后，必須以數(shù)組公式的形式輸入。

當(dāng)為參數(shù)（如known_x's）輸入數(shù)組常量時(shí)，應(yīng)當(dāng)使用逗號(hào)分隔同一行中的數(shù)據(jù)，用分號(hào)

分隔不同行中的數(shù)據(jù)。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

本示例采用與LOGEST函數(shù)示例相同的數(shù)據(jù)。第一個(gè)公式顯示與已知值對(duì)應(yīng)的值。如果指數(shù)趨勢(shì)繼續(xù)

存在，則第二個(gè)公式將預(yù)測(cè)下個(gè)月的值。

172

ABC

月份單位數(shù)公式（對(duì)應(yīng)的值）

1

1133,100=GROWTH(B2:B7,A2:A7)

2

1247,300

3

1369,000

4

14102,000

5

15150,000

6

16220,000

7

月份公式（預(yù)測(cè)的值）

8

17=GROWTH(B2:B7,A2:A7,A9:A10)

918

10

注釋示例中的公式必須以數(shù)組公式輸入。將示例復(fù)制到空白工作表后，請(qǐng)選中以公式單元格開(kāi)始

的區(qū)域C2:C7或B9:B10?按F2,再按Ctrl+Shift+Enter.如果公式不是以數(shù)組公式的形式輸入，

則單個(gè)結(jié)果為32618.20377和320196.7184.

HARMEAN函數(shù)

本文介紹MicrosoftExcelHARMEAN函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多

個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或

復(fù)雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

173

返回?cái)?shù)據(jù)集合的調(diào)和平均值。調(diào)和平均值與倒數(shù)的算術(shù)平均值互為倒數(shù)。

語(yǔ)法

HARMEAN(numberl,[number2J,...)

HARMEAN函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為臊作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。)：

Numberl,number2,...Numberl是必需的，后續(xù)數(shù)值是可選的。這是用于計(jì)算平均值的

一組參數(shù)，參數(shù)的個(gè)數(shù)可以為1到255個(gè)。也可以用單一數(shù)組或?qū)δ硞€(gè)數(shù)組的引用來(lái)代替用逗

號(hào)分隔的參數(shù)。

說(shuō)明

調(diào)和平均值總小于幾何平均值，而幾何平均值總小于算術(shù)平均值。

參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。

邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。

如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的

單元格將計(jì)算在內(nèi)。

如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。

如果任何數(shù)據(jù)點(diǎn)小于等于0,函數(shù)HARMEAN返回錯(cuò)誤值#NUM!。

調(diào)和平均值的計(jì)算公式如下：

-L=ly!

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

174

B

1數(shù)據(jù)

24

35

48

57

611

7?

83

9公式說(shuō)明(結(jié)果)

10=HARMEAN(A2:A8)上面數(shù)據(jù)集的調(diào)和平均值(5.028376)

HYPGEOM.DIST函數(shù)

返回超幾何分布。

給定樣本容量、樣本總體容量和樣本總體中成功的次數(shù)，函數(shù)HYPGEOM.DIST返回樣本取得給定

成功次數(shù)的概率。使用函數(shù)HYPGEOM.DIST可以解決有限總體的問(wèn)題，其中每個(gè)觀察值或者為成

功或者為失敗，且給定樣本容量的每一個(gè)子集有相等的發(fā)生概率。

語(yǔ)法

HYPGEOM.DIST(sample_s,number_sample,population_s,number_pop,cumulative)

HYPGEOM.DIST函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信

息的值。)：

175

Sample_s必需。樣本中成功的次數(shù)。

Numbersample必需。樣本容量。

Population_s必需。樣本總體中成功的次數(shù)。

Number_pop必需。樣本總體的容量。

Cumulative必需。決定函數(shù)形式的邏輯值。如果cumulative為T(mén)RUE,函數(shù)

HYPGEOM.DIST返回累積分布函數(shù)；如果為FALSE,則返回概率密度函數(shù)。

說(shuō)明

所有參數(shù)將被截尾取整。

如果任意參數(shù)為非數(shù)值型，貝IHYPGEOM.DIST返回錯(cuò)誤值#VALUE!。

如果sample_s<0MSsample_s大于number_sample和population_s中的較小值，

則HYPGEOM.DIST返回錯(cuò)誤值#NUM!。

如果sample_s小于0和(number_sample-numberjDopulation+population_s)

較大值，則HYPGEOM.DIST返回錯(cuò)誤值#NUM!。

如果number_sample<0number_sample>number_population,貝U

HYPGEOM.DIST返回錯(cuò)誤值#NUM!?

如果populationsw0或population_s>numberjDopulation,則HYPGEOM.DIST返回

錯(cuò)誤值#NUM!o

如果number_pop<0,則HYPGEOM.DIST返回錯(cuò)誤值#NUM!。

超幾何分布的計(jì)算公式如下：

P[X=x)=汶x,黑,M

式中:

x=sample_s

176

n=number_sample

M=population_s

N=numberjDop

函數(shù)HYPGEOM.DIST用于在有限樣本總體中進(jìn)行不退回抽樣的概率計(jì)算。

示例

抽樣器里有20塊巧克力。8塊是焦糖的，其余12塊是果仁的。如果隨機(jī)選出4塊，下面函數(shù)返

回恰好有一塊是焦糖的概率。

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)說(shuō)明

21樣本中成功的次數(shù)

34樣本容量

48樣本總體中成功的次數(shù)

520樣本總體的容量

6公式說(shuō)明(結(jié)果)

=HYPGEOM.DIST(A2,A3,A4,A5,TRUE)上述樣本和樣本總體的累積超幾何分布函數(shù)(0.4654828)

7=HYPGEOM.D￡T(A2,A3,A4,A5,FALSE)上述樣本和樣本總體的概率超幾何分布函數(shù)(0.36326109)

8

177

INTERCEPT函數(shù)

本文介紹MicrosoftExcel中INTERCEPT函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多

個(gè)值執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或復(fù)

雜的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

利用現(xiàn)有的x值與y值計(jì)算直線與v軸的截距。截距為穿過(guò)已知的known_x's和known_y's數(shù)

據(jù)點(diǎn)的線性回歸線與V軸的交點(diǎn)。當(dāng)自變量為0（零）時(shí)，使用INTERCEPT函數(shù)可以決定因變

量的值。例如，當(dāng)所有的數(shù)據(jù)點(diǎn)都是在室溫或更高的溫度下取得的，可以用INTERCEPT函數(shù)預(yù)測(cè)

在0℃時(shí)金屬的電阻。

語(yǔ)法

INTERCEPT（known_y's,known_x's）

INTERCEPT函數(shù)語(yǔ)法具有下列參數(shù)（參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的

僖）：

Known_y's必需。因變的觀察值或數(shù)據(jù)的集合。

Known_x's必需。自變的觀察值或數(shù)據(jù)的集合。

說(shuō)明

參數(shù)可以是數(shù)字，或者是包含數(shù)字的名稱、數(shù)組或引用。

如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略：但包含零值的

單元格將計(jì)算在內(nèi)。

如果known_y's和known_x's所包含的數(shù)據(jù)點(diǎn)個(gè)數(shù)不相等或不包含任何數(shù)據(jù)點(diǎn)，則函數(shù)

INTERCEPT返回錯(cuò)誤值#N/A。

回歸線a的截距公式為:

178

a=Y-hX

公式中斜率b計(jì)算如下：

；%Wp-(Wx)(功)

一立產(chǎn)-區(qū))2

其中x和y是樣本平均值A(chǔ)VERAGE(known_x，s)和AVERAGE(known_y's)o

函數(shù)SLOPE和INTERCEPT中使用的下層算法與函數(shù)LINEST中使用的下層算法不

同。當(dāng)數(shù)據(jù)未定且共線時(shí)，這些算法之間的差異會(huì)導(dǎo)致不同的結(jié)果。例如，如果參數(shù)known_y's的

數(shù)據(jù)點(diǎn)為0,參數(shù)known_x's的數(shù)據(jù)點(diǎn)為1：

SLOPE和INTERCEPT返回錯(cuò)誤#DIV/0!。INTERCEPT和SLOPE算法用來(lái)查找

一個(gè)且僅一個(gè)答案，在這種情況下可能有多個(gè)答案。

LINEST返回值0?LINEST算法用來(lái)返回共線數(shù)據(jù)的合理結(jié)果，在這種情況下至少可

找到一個(gè)答案。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1已知y已知x

226

335

4911

517

685

179

7公式說(shuō)明(結(jié)果)

=INTERCEPT(A2:A6,B2:B6)利用上面已知的x值與y值計(jì)算直線與y軸的截距(0.0483871)

8

KURT函數(shù)

本文介紹MicrosoftExcel中KURT函數(shù)(函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多個(gè)值執(zhí)行

運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或復(fù)雜的計(jì)算

時(shí)。)的公式語(yǔ)法和用法。

說(shuō)明

返回?cái)?shù)據(jù)集的峰值。峰值反映與正態(tài)分布相比某一分布的尖銳度或平坦度。正峰值表示相對(duì)尖銳的分布。

負(fù)峰值表示相對(duì)平坦的分布。

語(yǔ)法

KURT(numberl,[number2J,...)

KURT函數(shù)語(yǔ)法具有下列參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。)：

Number1,number2,...Numberl是必需的，后續(xù)數(shù)值是可選的。這是用于計(jì)算峰值的一

組參數(shù)，參數(shù)的個(gè)數(shù)可以為1到255個(gè)。也可以用單一數(shù)組或?qū)δ硞€(gè)數(shù)組的引用來(lái)代替用逗

號(hào)分隔的參數(shù)。

說(shuō)明

參數(shù)可以是數(shù)字或者是包含數(shù)字的名稱、數(shù)組或引用。

邏輯值和直接鍵入到參數(shù)列表中代表數(shù)字的文本被計(jì)算在內(nèi)。

181

180

如果數(shù)組或引用參數(shù)包含文本、邏輯值或空白單元格，則這些值將被忽略；但包含零值的

單元格將計(jì)算在內(nèi)。

如果參數(shù)為錯(cuò)誤值或?yàn)椴荒苻D(zhuǎn)換為數(shù)字的文本，將會(huì)導(dǎo)致錯(cuò)誤。

如果數(shù)據(jù)點(diǎn)少于4個(gè)，或樣本標(biāo)準(zhǔn)偏差等于0,函數(shù)KURT返回錯(cuò)誤值#DIV/0!?

峰值的計(jì)算公式如下:

-

(?1)(?-2)(附-3)

(?-2)(?-3)

S為樣本的標(biāo)準(zhǔn)偏差。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)

23

34

45

2

63

74

85

181

181

96

104

117

12公式說(shuō)明（結(jié)果）

13=KURT（A2:A11）上述數(shù)據(jù)集的峰值（-0.1518）

LARGE函數(shù)

本文介紹MicrosoftExcel中LARGE函數(shù)（函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多個(gè)值

執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或復(fù)雜

的計(jì)算時(shí)。）的公式語(yǔ)法和用法。

說(shuō)明

返回?cái)?shù)據(jù)集中第k個(gè)最大值。使用此函數(shù)可以根據(jù)相對(duì)標(biāo)準(zhǔn)來(lái)選擇數(shù)值。例如，可以使用函數(shù)

LARGE得到第一名、第二名或第三名的得分。

語(yǔ)法

LARGE（array,k）

LARGE函數(shù)語(yǔ)法具有下列參數(shù)（參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。）：

Array必需。需要確定第k個(gè)最大值的數(shù)組或數(shù)據(jù)區(qū)域。

K必需。返回值在數(shù)組或數(shù)據(jù)單元格區(qū)域中的位置（從大到小排）。

說(shuō)明

如果數(shù)組為空，函數(shù)LARGE返回錯(cuò)誤值#NUM!。

182

如果kvO或k大于數(shù)據(jù)點(diǎn)的個(gè)數(shù)，函數(shù)LARGE返回錯(cuò)誤值#NUM!。

如果區(qū)域中數(shù)據(jù)點(diǎn)的個(gè)數(shù)為n,則函數(shù)LARGE(array,1)返回最大值，函數(shù)LARGE(array,n)返回

最小值。

示例

如果將示例復(fù)制到一個(gè)空白工作表中，可能會(huì)更容易理解該示例。將示例復(fù)制到一個(gè)空白工作表中后，可

以按照需要改編示例。

AB

1數(shù)據(jù)數(shù)據(jù)

234

352

434

556

647

7公式說(shuō)明(結(jié)果)

8=LARGE(A2:B6,3)上面數(shù)據(jù)中第三個(gè)最大值(5)

9=LARGE(A2:B6,7)上面數(shù)據(jù)中第七個(gè)最大值(4)

LINEST函數(shù)

本文介紹MicrosoftExcel中LINEST函數(shù)(函數(shù)：函數(shù)是預(yù)先編寫(xiě)的公式，可以對(duì)一個(gè)或多個(gè)值

執(zhí)行運(yùn)算，并返回一個(gè)或多個(gè)值。函數(shù)可以簡(jiǎn)化和縮短工作表中的公式，尤其在用公式執(zhí)行很長(zhǎng)或復(fù)雜的

計(jì)算時(shí)。)的公式語(yǔ)法和用法。在“另請(qǐng)參閱”部分中可找到一些鏈接，這些鏈接指向有關(guān)繪制圖

表和執(zhí)行回歸分析的詳細(xì)信息o

183

說(shuō)明

LINEST函數(shù)可通過(guò)使用最小二乘法計(jì)算與現(xiàn)有數(shù)據(jù)最佳擬合的直線，來(lái)計(jì)算某直線的統(tǒng)計(jì)值，然

后返回描述此直線的數(shù)組。也可以將LINEST與其他函數(shù)結(jié)合使用來(lái)計(jì)算未知參數(shù)中其他類(lèi)型的線

性模型的統(tǒng)計(jì)值，包括多項(xiàng)式、對(duì)數(shù)、指數(shù)和幕級(jí)數(shù)。因?yàn)榇撕瘮?shù)返回?cái)?shù)值數(shù)組，所以必須以數(shù)組公式

的形式輸入。請(qǐng)按照本文中的示例使用此函數(shù)。

直線的公式為：

y=mx+b

-或-

y=mlxl+m2x2+...+b

如果有多個(gè)區(qū)域的x值，其中因變量y值是自變量x值的函數(shù)。m值是與每個(gè)x值相對(duì)應(yīng)的系數(shù),

b為常量。注意，y、x和m可以是向量。LINEST函數(shù)返回的數(shù)組為｛mn,mn-1,...,m1,b｝。

LINEST函數(shù)還可返回附加回歸統(tǒng)計(jì)值。

語(yǔ)法

LINEST(known_y*s,[knowri-X^J,[const],[stats])

LINEST函數(shù)語(yǔ)法具有以下參數(shù)(參數(shù)：為操作、事件、方法、屬性、函數(shù)或過(guò)程提供信息的值。)：

語(yǔ)法

Known_y's必需。關(guān)系表達(dá)式y(tǒng)=mx+b中已知的y值集合。

如果known_y*s對(duì)應(yīng)的單元格區(qū)域在單獨(dú)一列中，則known_xfs的每一列被視為一

個(gè)獨(dú)立的變量。

如果known_y*s對(duì)應(yīng)的單元格區(qū)域在單獨(dú)一行中，則known_x,s的每一行被視為一

個(gè)獨(dú)立的變量。

184

Excel函數(shù)查詢手冊(cè)

Known_x's可選。關(guān)系表達(dá)式y(tǒng)=mx+b中已知的x值集合。

known_x's對(duì)應(yīng)的單元格區(qū)域可以包含一組或多組變量.如果僅使用一個(gè)變量，那么只

要known_y'sfUknown_x's具有相同的維數(shù)，則它們可以是任何形狀的區(qū)域。如果

使用多個(gè)變量，則known_y's必須為向量（即必須為一行或一列）。

如果省略known_x's,則假設(shè)該數(shù)組為{1,2,3,…},其大小與known_y's相同。

const可選。一個(gè)邏輯值，用于指定是否將常量b強(qiáng)制設(shè)為0.

如果const為T(mén)RUE或被省略，b將按通常方式計(jì)算。

如果const為FALSE,b將被設(shè)為0,并同時(shí)調(diào)整m值使y=mx。

stats可選。一個(gè)邏輯值，用于指定是否返回附加回歸統(tǒng)計(jì)值。

如果stats為T(mén)RUE,則LINEST函數(shù)返回附加回歸統(tǒng)計(jì)值，這時(shí)返回的數(shù)組為

,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid）,

如果stats為FALSE或被省略，LINEST函數(shù)只返回系數(shù)m和常量b。

附加回歸統(tǒng)計(jì)值如下：

統(tǒng)計(jì)值說(shuō)明

系數(shù)的標(biāo)準(zhǔn)誤差值。

sel/se2,.../senml,m2z...,mn

seb常量b的標(biāo)準(zhǔn)誤差值（當(dāng)const為FALSE時(shí)，seb=#N/A）.

r2判定系數(shù)。y的估計(jì)值與實(shí)際值之比，范圍在0到1之間。如果為1,則樣本有很好的相關(guān)

性，y的估計(jì)值與實(shí)際值之間沒(méi)有差別。相反，如果判定系數(shù)為0,則回歸公式不能用來(lái)預(yù)測(cè)

y值。有關(guān)如何計(jì)算r2的信息，請(qǐng)參閱本主題下文中的一說(shuō)明

seyY估計(jì)值的標(biāo)準(zhǔn)誤差。

FF統(tǒng)計(jì)或F觀察值。使用F統(tǒng)計(jì)可以判斷因變量和自變量之間是否偶爾發(fā)生過(guò)可觀察到的

關(guān)系。

df自由度。用于在統(tǒng)計(jì)表上查找F臨界值。將從表中查得的值與LINEST函數(shù)返回的F統(tǒng)計(jì)

值進(jìn)行比較可確定模型的置信區(qū)間。有關(guān)如何計(jì)算df的信息，請(qǐng)參閱本主題下文中的一說(shuō)

明|卜示例4說(shuō)明了F和df的用法。

185

Excel函數(shù)查詢手冊(cè)

ssreg回歸平方和。

ssresid殘差平方和。有關(guān)如何計(jì)算ssreg和ssresid的信息，請(qǐng)參閱本主題下文中的一說(shuō)明

下面的圖示顯示了附加回歸統(tǒng)計(jì)值返回的順序。

ABCDEF

1mnmn-1rr)2m*|b

2sensen-1s&2seiseb

3r2sev

4Fdf

5ssreqssresid

說(shuō)明

可以使用斜率和y軸截距描述任何直線：

斜率(m)：

通常記為m,如果需要計(jì)算斜率，則選取直線上的兩點(diǎn)，(x1,y1)和(x2,y2)；斜率等于(y2-

y1)/(x2-x1).

Y軸截距(b)：

通常記為b,直線的y軸的截距為直線通過(guò)y軸時(shí)與y軸交點(diǎn)的數(shù)值。

直線的公式為y=mx+b.如果知道了m和b的值，將y或x的值代入公式就可計(jì)算出直

線上的任意一點(diǎn)。還可以使用TREND函數(shù)。

當(dāng)只有一個(gè)自變量x時(shí)，可直接利用下面公式得到斜率和y軸截距值：

斜率：

=INDEX(LINEST(known_y's,known_x's),1)

Y軸截距：

=INDEX(LINEST(known_y's,known_x's),2)

186

數(shù)據(jù)的離散程度決定了LINEST函數(shù)計(jì)算的直線的精確度。數(shù)據(jù)越接近線性，LINEST模

型就越精確。LINEST函數(shù)使用最小二乘法來(lái)判定數(shù)據(jù)的最佳擬合。當(dāng)只有一個(gè)自變量x時(shí)，

m和b是根據(jù)下面的公式計(jì)算出的：

心＞)-(2m)

^2=---------------

閥安父))-(￡/

0=-----------------o--------------------

附(ZC))-(￡x)2

其中，x和y是樣本平均值；即,x=AVERAGE(knownx's),y=AVERAGE(known_y's)o

直線和曲線擬合函數(shù)LINEST和LOGEST可用來(lái)計(jì)算與給定數(shù)據(jù)擬合程度最高的直線或

指數(shù)曲線，但需要判斷兩者中哪一個(gè)與數(shù)據(jù)擬合程度最高?？梢杂煤瘮?shù)

TREND(known_y's,known_x's)來(lái)計(jì)算直線，或用函數(shù)GROWTH(known_y's,known_x's)

來(lái)計(jì)算指數(shù)曲線。這些不帶new_x's參數(shù)的函數(shù)可在實(shí)際數(shù)據(jù)點(diǎn)上根據(jù)直線或曲線來(lái)返回y

預(yù)測(cè)值的數(shù)組，然后可以將預(yù)測(cè)值與實(shí)際值進(jìn)行比較?？赡苄枰脠D表方式來(lái)直觀地比較二者。

回歸分析時(shí)，Excel會(huì)計(jì)算每一點(diǎn)的y的估計(jì)值和實(shí)際值的平方差。這些平方差之和稱為

殘差平方和(ssresid)。然后Excel會(huì)計(jì)算總平方和(sstotal)o當(dāng)參數(shù)const=TRUE或被省

略時(shí)，總平方和是y的實(shí)際值和平均值的平方差之和。當(dāng)參數(shù)const=FALSE時(shí)，總平方和

是y的實(shí)際值的平方和(不需要從每個(gè)y值中減去平均值)?；貧w平方和(ssreg)可通過(guò)公式

ssreg=sstotal-ssresid計(jì)算出來(lái)。ssreg=sstotal-ssresid?殘差平方和與總平方和的比值

越小，判定系數(shù)r2的值就越大，r2是用來(lái)判斷從回歸分析求得的公式是否足以說(shuō)明變量之間

關(guān)系的指示器。r2=ssreg/sstotal。

在某些情況下，一個(gè)或多個(gè)X列可能沒(méi)有出現(xiàn)在其他X列中的附加預(yù)測(cè)值(假設(shè)Vs和

X，s位于列中)。換句話說(shuō)，刪除一個(gè)或多個(gè)X列可能會(huì)得到同樣精度的y預(yù)測(cè)值。在這種情

況下，應(yīng)從回歸模型中省略這些多余的X歹!1。這種現(xiàn)象被稱為“共線”，因?yàn)槿魏味嘤嗟腦列都可

被表示為多個(gè)非多余X列的和。LINEST函數(shù)會(huì)檢查是否存在共線，并在識(shí)別出多余的X列之

后從回歸模型中刪除所有這些列。由于包含0系數(shù)以及Ose數(shù)值，因此已刪除的X列能在

LINEST輸出中被識(shí)別出來(lái)。如果一個(gè)或多個(gè)多余的列被刪除，則將影響df,原因是df取決

于實(shí)際用于預(yù)測(cè)目的的X列的數(shù)量。有關(guān)計(jì)算df的詳細(xì)信息，請(qǐng)參閱示例4。如果由

于刪除多余的X列而更改了df,則也會(huì)影響sey和F的值。實(shí)際上，出現(xiàn)共線的情況應(yīng)該

187

相對(duì)很少。但是，如果某些X列僅包含0和1數(shù)值作為實(shí)驗(yàn)中的對(duì)象是否屬于特定組成員的

指示器，則很可能引起共線。如果const=TRUE或被省略，則LINEST函數(shù)可有效地插入

所有1數(shù)值的其他X列以便為截距建立模型。如果在一列中，1對(duì)應(yīng)于每個(gè)男性對(duì)象，0對(duì)應(yīng)于

女性對(duì)象；而在另一列中，1對(duì)應(yīng)于每個(gè)女性對(duì)象，0對(duì)應(yīng)于男性對(duì)象，那么后一列就是多余

的，因?yàn)槠渲械捻?xiàng)可通過(guò)從所有1值的另一列（通過(guò)LINEST函數(shù)添加）中減去“男性指示器”

列中的項(xiàng)來(lái)獲得。

在沒(méi)有X列因共線而被從模型中刪除時(shí)，請(qǐng)用以下方法計(jì)算df的值：如果known_x's

有k列且const=TRUE或被省略，那么df=n-k-1。如果const=FALSE,那么df=n

-k。在這兩種情況下，每次由于共線而刪除一個(gè)X列都會(huì)使df的值加1。

對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。

當(dāng)輸入一個(gè)數(shù)組常量（如known_x's）作為參數(shù)時(shí)，請(qǐng)使用逗號(hào)分隔同一行中的各值，使

用分號(hào)分隔各行。分隔符可能會(huì)因“控制面板”的“區(qū)域和語(yǔ)言選項(xiàng)”中區(qū)域設(shè)置的不同而有所不同。

注意，如果y的回歸分析預(yù)測(cè)值超出了用來(lái)計(jì)算公式的y值的范圍，它們可能是無(wú)效的。

LINEST函數(shù)中使用的下層算法與SLOPE和INTERCEPT函數(shù)中使用的下層算法不同。

當(dāng)數(shù)據(jù)未定且共線時(shí)，這些算法之間的差異會(huì)導(dǎo)致不同的結(jié)果。例如，如果參數(shù)known_y's的數(shù)據(jù)

點(diǎn)為0,參數(shù)known_x's的數(shù)據(jù)點(diǎn)為1：

LINEST會(huì)返回值0。LINEST函數(shù)的算法用來(lái)返回共線數(shù)據(jù)的合理結(jié)果，在這種情況

下至少可找到一個(gè)答案。

SLOPE和INTERCEPT會(huì)返回錯(cuò)誤#DIV/0!?SLOPE和INTERCEPT函數(shù)的算法

只用來(lái)查找一個(gè)答案，在這種情況下可能有多個(gè)答案。

除了使用LOGEST計(jì)算其他回歸分析類(lèi)型的統(tǒng)計(jì)值外，還可以使用LINEST計(jì)算其他回

歸分析類(lèi)型的范圍，方法是將x和y變量的函數(shù)作為L(zhǎng)INEST的x和y系列輸入。例如，

下面的公式：

=LINEST（yvalues,xvaluesACOLUMN（$A:$C））

將在您使用y值的單個(gè)列和x值的單個(gè)列計(jì)算下面的方程式的近似立方（多項(xiàng)式次數(shù)3）值

時(shí)運(yùn)行：

y=m1*x+m2*xA2+m3*xA3+b