F 分布的Excel 構(gòu)建與模擬

F 分布（F-distribution）是一種常見(jiàn)的概率分布，在方差分析的統(tǒng)計(jì)推斷中占有重要位置.它的定義是這樣的：設(shè)從某正態(tài)分布總體N(μ, σ2)中隨機(jī)抽取樣本容量為n1和n2的兩個(gè)獨(dú)立樣本，其樣本方差分別則定義這兩個(gè)方差的比值為F，即：

在具體的教學(xué)過(guò)程中，如果對(duì)上述內(nèi)容，尤其是對(duì)F 分布曲線的特征簡(jiǎn)單地進(jìn)行敘述和羅列，一方面難以激發(fā)學(xué)生的學(xué)習(xí)興趣，另外一方面也不利于他們對(duì)F 分布的深入理解.近年來(lái)，筆者與同事們?cè)诮虒W(xué)過(guò)程中，利用Excel 軟件中的函數(shù)公式、圖表繪制以及VBA 編程功能（Visual Basic for Application），對(duì)生物統(tǒng)計(jì)學(xué)當(dāng)中的一些內(nèi)容進(jìn)行模擬或構(gòu)建，取得了不錯(cuò)的教學(xué)效果［2-5］.本文繼續(xù)以Excel 2010 為例，采用2 種方法對(duì)F 分布曲線分別進(jìn)行構(gòu)建和模擬，以供參考.

1 利用函數(shù)進(jìn)行構(gòu)建

Excel 2010 中的F.DIST（F, df1, df2, FALSE）函數(shù)可以用來(lái)計(jì)算某F 值以及df1和df2條件下的概率密度.利用該函數(shù)對(duì)F 分布曲線進(jìn)行構(gòu)建時(shí)，其數(shù)據(jù)輸入格式如圖1 所示.在A 列中自A4 單元格以下輸入≥0的數(shù)字以表示F 值，本文中以0.1 為間隔，即0.0、0.1、0.2、…；在第1 行中自B 列開(kāi)始輸入正整數(shù)以表示df1，本文中以2、3、10、…為例；在第2 行中自B 列開(kāi)始輸入正整數(shù)以表示df2，本文中以2、10、20、…為例；在B4 單元格中輸入“=F.DIST($A4,B$1,B$2,FALSE)”，并將此函數(shù)公式向右、向下復(fù)制填充，一張?zhí)囟‵值以及df1和df2條件下的概率密度表格便生成了；然后對(duì)所得概率密度與A 列的F 值進(jìn)行繪圖，便可得到相應(yīng)的F 分布曲線（見(jiàn)圖2）.該文件生成后，只需要任意修改表格第1、2 行中的自由度取值，便可以在課堂上動(dòng)態(tài)地展示F 分布曲線隨自由度df1和df2變化而變化的規(guī)律特點(diǎn)， 從而達(dá)到激發(fā)學(xué)生學(xué)習(xí)興趣、提高教學(xué)效果之目的.

圖1 Excel 函數(shù)輸入示例

圖2 不同自由度的F 分布曲線

2 利用VBA 編程進(jìn)行模擬

2.1 模擬步驟

Excel 中的NormInv(Rnd(),0,1)函數(shù)可以生成一系列服從標(biāo)準(zhǔn)正態(tài)分布N(0,1)的隨機(jī)數(shù)字，正好可以用來(lái)模擬F 分布的抽樣過(guò)程.

第1 步：打開(kāi)Excel 2010，新建一個(gè)電子表格，在當(dāng)前工作表的A1 至A4 單元格中分別輸入“自由度df1=”“自由度df2=”“抽樣次數(shù)=”以及“組距=”；在B1 至B4 單元格中輸入合適的數(shù)字以表示相應(yīng)的自由度、抽樣次數(shù)和組距（本文分別以10、20、10 000、0.2 為例）；在C1 至I1 單元格中分別輸入“F 值”“組限”以及“頻率”等（見(jiàn)圖3）.

第2 步：按Alt+F11 進(jìn)入Visual Basic 編寫(xiě)界面，雙擊左側(cè)的Sheet1 圖標(biāo)，進(jìn)入代碼編寫(xiě)界面，把本文附錄中的VBA 代碼拷貝進(jìn)去（見(jiàn)圖4）.然后關(guān)閉代碼編寫(xiě)界面進(jìn)入工作表界面，將文件另存為啟用宏的文件類(lèi)型（*.xlsm）即可.

圖3 模擬步驟第1 步的輸入結(jié)果

圖4 Excel 中VBA 代碼的輸入

第3 步：為宏運(yùn)行設(shè)置快捷鍵.按Alt+F8 鍵或點(diǎn)擊菜單欄的“視圖→宏→查看宏”后選定“Sheet1.sql”，再點(diǎn)擊“選項(xiàng)(O)…”，在快捷鍵對(duì)話框里填寫(xiě)字母“q”后確定即可.

2.2 模擬結(jié)果

當(dāng)按下快捷鍵Ctrl+q 時(shí)，Excel 便開(kāi)始執(zhí)行模擬程序.首先是在C 列和D 列自第2 行往下分別生成n1(= df1+1)和n2(=df2+1)個(gè)服從標(biāo)準(zhǔn)正態(tài)總體N(0, 1)的隨機(jī)變量.本文中以df1=10、df2=20 為例，故在C2 至C12 單元格中生成11 個(gè)隨機(jī)變量表示n1、在D2 至D22 中生成21 個(gè)隨機(jī)變量表示n2.然后利用VAR 函數(shù)計(jì)算n1的方差和n2的方差分別存放在單元格E2 和F2 當(dāng)中；再將便可得到一個(gè)F值，存放在G2 單元格中.若將上述過(guò)程重復(fù)10 000 次（B3 單元格中的數(shù)字，本文中以10 000 為例），便可生成10 000 個(gè)和F 值，被相應(yīng)地依次存放在E、F、G 這3 列的第2 行至第10 001 行當(dāng)中.

在VBA 代碼中，利用ROUNDUP(MAX())和ROUNDDOWN(MIN())這兩個(gè)復(fù)合函數(shù)對(duì)G 列中的F 值的最大值與最小值分別向上向下取整數(shù)，以統(tǒng)計(jì)F 值的分布范圍，并據(jù)此設(shè)置組限，數(shù)據(jù)存放在H 列中；再利用FREQUENCY 函數(shù)進(jìn)行頻次統(tǒng)計(jì)，所得的各組的頻率存放在I 列中；最后對(duì)I 列中的結(jié)果繪制柱狀圖，其結(jié)果如圖5 所示.任意修改B1、B2 單元格中的自由度取值，該柱狀圖便會(huì)相應(yīng)地發(fā)生改變，從而可以動(dòng)態(tài)地展示F 分布隨df1和df2變化而變化的基本規(guī)律.從圖5 中可以看出，F(xiàn) 值的頻率分布變化趨勢(shì)與圖2 中的概率密度曲線的變化趨勢(shì)是基本一致的.

圖5 不同自由度的F 頻率分布

3 結(jié)語(yǔ)

頻率與概率之間的關(guān)系，實(shí)際上就是樣本統(tǒng)計(jì)數(shù)與總體參數(shù)的關(guān)系.上述的第1 種方法，即利用F.DIST 函數(shù)對(duì)F 分布曲線進(jìn)行構(gòu)建，恰恰就是對(duì)F 分布總體的描述.該方法的優(yōu)點(diǎn)是分布曲線圓滑美觀，運(yùn)算速度快，不影響正常的教學(xué)節(jié)奏，便于課堂上展示；其不足之處在于未能體現(xiàn)抽樣過(guò)程，也就不便于學(xué)生深刻理解F 分布的內(nèi)涵.利用VBA 編程對(duì)F 分布進(jìn)行模擬則可以很好地彌補(bǔ)這一不足之處.在VBA 模擬中，學(xué)生可以根據(jù)運(yùn)算程序去梳理F 分布的由來(lái).通過(guò)任意修改模擬參數(shù)，可以查看不同自由度、不同抽樣次數(shù)以及不同組距下的F 頻率分布，這種交互式體驗(yàn)必定有助于加深學(xué)生對(duì)該分布的理解.理論上，當(dāng)圖3 中B3 單元格中的抽樣次數(shù)無(wú)限大、B4 單元格中的組距無(wú)限接近于0 時(shí)，圖5 就與圖2 相一致.在實(shí)際應(yīng)用時(shí)，一方面Excel 無(wú)法滿(mǎn)足抽樣次數(shù)無(wú)限大的要求；另一方面，抽樣次數(shù)過(guò)大也會(huì)使得運(yùn)算過(guò)程耗時(shí)過(guò)長(zhǎng).VBA 代碼中，最大抽樣次數(shù)設(shè)置為65 535，已然可以滿(mǎn)足模擬的基本要求，取得很好的模擬效果.所以，建議在課堂上采用第1 種方法輔助教學(xué)，將第2 種方法留給學(xué)生課下自行模擬，這種課上課下相結(jié)合的教學(xué)方式，必能激發(fā)學(xué)生的學(xué)習(xí)興趣，提高教學(xué)效果.

4 附錄（VBA 代碼）