使用Excel繪制t分布概率密度函數(shù)

關(guān)于t分布應(yīng)用廣泛，主要用于假設(shè)檢驗。關(guān)于使用Excel畫出t分布的概率密度函數(shù)圖表的問題，試答如下：

使用excel繪制t分布的概率密度函數(shù)，需要兩列：1）自變量X，2）計算自變量X對應(yīng)的t分布的概率密度函數(shù)。由于Excel中TDIST函數(shù)計算的是概率累積密度，不能計算概率密度值，所以借用伽馬函數(shù)的自然對數(shù)。先從t分布的公式著手。

其中：ν 為自由度=n-1

Γ為伽馬函數(shù)的的符號

t分布的平均數(shù)和標(biāo)準(zhǔn)正態(tài)分布一樣均等于0

t分布的標(biāo)準(zhǔn)差=ν/(ν-2)

我們以隨機變量t值為x軸（即視t為x），如何將自由度帶入方程式求y值？因為t分布中涉及到GAMMALN()函數(shù)，而excel是提供GAMMALN()函數(shù)的，所以我們可以使用excel中的GAMMALN()函數(shù)來計算得到t分布的概率密度函數(shù)（參見【附錄】）。經(jīng)轉(zhuǎn)換后其公式為：

t(X,df)=EXP(GAMMALN((df+1)/2))/（SQRT(PI()*df)*EXP(GAMMALN(df/2))）*(1+X^2/df)^(-1/2*(df+1))……………………………………公式（1）

由于對公式書寫格式的順序的理解不同，上述公式可能也會寫成以下形式：

t(X,df)=EXP(GAMMALN((df+1)/2))*(1+X^2/df)^(-(df+1)/2)/SQRT(df*PI())/EXP(GAMMALN(df/2))  ……………………………………公式（2）

現(xiàn)以自由度（ν）=4為例，求t分布的圖表，可由以下幾步進行：

第1步 確定自變量取值范圍

自由度=4時，t分布的方差為ν/(ν-2)=2，標(biāo)準(zhǔn)差= SQRT (2)=1.414

t分布的平均數(shù)和標(biāo)準(zhǔn)正態(tài)分布一樣均等于0,同樣與正態(tài)分布一樣，幾乎99%的t值會落在平均數(shù)`x±3個標(biāo)準(zhǔn)差之內(nèi)，即落在區(qū)間（`x-3σ，`x+3σ）之間，所以橫軸的取值范圍在-4.2~4.2之間。

第2步 在Excel單元格中輸入自變量

在A列中，在單元格A2中輸入-4.2，在單元格A3中輸入-4，遞增0.2，選中單元格A2與A3，按住右下角的填充控制點一直拖到單元格A44是4.2為止，A列的這些數(shù)據(jù)就作為隨機變量t的取值。如表-1所示：

表-1

第3步 在單元格B2中輸入計算t分布的概率密度函數(shù)的公式

對于公式（1），由于自由度（ν）=4 ，則由df=4代入；自變量X就是單元格A2的值，所以按Excel相對引用的規(guī)則，X由A2代入即可，于是單元格B2內(nèi)容是

=EXP(GAMMALN((4+1)/2))/（SQRT(PI()*4)*EXP(GAMMALN(4/2))）*(1+A2^2/4)^(-1/2*(4+1))，如表-2所示：

 表-2

上述公式如按公式（1）的理解順序，單元格B2內(nèi)容可以寫成：

=EXP(GAMMALN((4+1)/2))*(1+A2^2/4)^(-(4+1)/2)/SQRT(4*PI())/EXP(GAMMALN(4/2))

結(jié)果是一樣的。

第4步 復(fù)制公式

按住單元格B2右下角的填充控制點，向下一直拖曳到B44，將B2的公式填充復(fù)制到B列的相應(yīng)的單元格，如表-3所示：

表-3

第5步 由于相對引用的規(guī)則，A列的自變量會自動被公式相對引用計算，結(jié)果如表-4所示：

表-4

上述表-3是為了說明公式的復(fù)制，而特意在“工具”-“選項”-“視圖”中將“公式”勾選，從而使公示內(nèi)容全部顯示出來。實際操作中，如表-4一樣，公式的表達式不會顯露，只有計算的結(jié)果會出現(xiàn)。至此已完成自由度為4的t分布概率密度函數(shù)表。

第6步 作t分布概率密度函數(shù)圖

選擇A1：B44，選“圖表向?qū)А?“標(biāo)準(zhǔn)類型’-“XY散點圖”（平滑線），如圖-1所示：

圖-1

第7步 輸入標(biāo)題，調(diào)整字號、線型等格式，完成t分布概率密度函數(shù)圖，如圖-2所示：

圖-2

如將上圖的圖表類型換成二維面積圖，則如圖-3-1(2003版)和圖-3-2（2010版）所示：

圖-3-1

圖-3-2

在Excel 2003版中面積圖數(shù)據(jù)系列格式的圖案的內(nèi)部填充格式?jīng)]有透明的設(shè)置，也不能使用柱形圖那樣用預(yù)先制作的透明圖片填充，此類效果可以在2007版與2010版中輕易實現(xiàn)。如為了在2003版中突出視覺效果，可以嘗試使用三維面積圖。如將上圖的圖表類型換成三維面積圖，則如圖-4-1（2003版）和圖-4-2（2010版）所示：

圖-4-1

圖-4-2

為了方便調(diào)整不同的自由度參數(shù)值觀察圖形變化，在Excel數(shù)據(jù)表中可在第一行的某幾個單元格如E1、F1、G1輸入不同參數(shù)，然后在公式引用這幾個參數(shù)時使用不同的方式：列數(shù)據(jù)為相對引用，而行數(shù)據(jù)為絕對引用，如E$1、F$1、G$1。而A列自變量值則使用：列數(shù)據(jù)為絕對引用，而行數(shù)據(jù)為相對引用，如$A2、$A3、$A4等。

數(shù)據(jù)表輸入截圖如圖-5：

圖-5

在公式輸入后，選擇單元格區(qū)間A1：D44，在同一圖表作出三種不同自由度的平滑曲線的散點圖，可見隨著自由度的變大，t分布越向Y軸集中如圖-6所示：

圖-6

【附錄：關(guān)于GAMMALN（）函數(shù)和EXP（）函數(shù)】

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

伽馬函數(shù)Γ（x）是個定積分，無法直接繪圖，可由GAMMALN（）函數(shù)和EXP（）函數(shù)，并利用對數(shù)恒等式：

間接求得，下面對以上內(nèi)容使用Excel中的相關(guān)文字加以說明。

GAMMALN函數(shù)的作用： 返回伽瑪函數(shù)Γ(x)的自然對數(shù)。

語法：

GAMMALN(x)

X    為需要計算函數(shù) GAMMALN 的數(shù)值。

GAMMALN(x)=LN(Γ（x）)

說明：

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

如果 x ≤ 0，函數(shù) GAMMAIN 返回錯誤值 #NUM!。

數(shù)字 e 的 GAMMALN(i) 次冪等于 (i-1)!，其中 i 為整數(shù),常數(shù) e 等于 2.71828182845904，是自然對數(shù)的底數(shù)。

GAMMALN(8)=8.525161

EXP(GAMMALN(8))=5040=(8-1)!=FACT(7)

FACT(N)為返回N-1的階乘（N-1）！=1×2×3×4×…×(N-2)×(N-1)的函數(shù)（其中N為自然數(shù)）

關(guān)于EXP（）函數(shù)： EXP（）返回 e 的 n 次冪。常數(shù) e 等于 2.71828182845904，是自然對數(shù)的底數(shù)。

語法

EXP(number)

Number 為底數(shù) e 的指數(shù)。

說明

若要計算以其他常數(shù)為底的冪，請使用指數(shù)操作符 (^)。

EXP 函數(shù)是計算自然對數(shù)的 LN 函數(shù)的反函數(shù)。

EXP(1)=2.718282（e的近似值）

EXP(2)=7.389056

EXP(1)=20.08554

EXP(LN(3))=3

于是為求伽馬函數(shù)Γ（x）首先要回憶一個最基本的恒等式：

即可得：

把該恒等式用于伽馬函數(shù)的取得，可以由以下兩步進行：

先用GAMMALN(x)，取得自然對數(shù)；

再用EXP(GAMMALN(x)),取得伽馬函數(shù)的值。