Excel電子表格編制頻數(shù)分布表的方法

(浙江大學(xué)動(dòng)物科學(xué)學(xué)院，浙江 杭州 310058)

隨著畜牧業(yè)生產(chǎn)的發(fā)展，人們必須進(jìn)行科學(xué)試驗(yàn)和研究，從而得到大量的試驗(yàn)數(shù)據(jù)，這些原始數(shù)據(jù)雜亂無(wú)章，看不出分布趨勢(shì)，也不便于統(tǒng)計(jì)分析，只有通過(guò)分組整理成頻數(shù)分布表，才能得知分布趨勢(shì)，制成比較直觀的統(tǒng)計(jì)分布圖，對(duì)于頻數(shù)分布表的制作，眾所周知，是一項(xiàng)繁瑣的工作，本文旨在告知讀者，利用Excel電子表格就能夠輕松地完成頻數(shù)分布表的制作，從而提高工作效率。

1 頻數(shù)分布表的編制方法

Excel電子表格提供了二種編制頻數(shù)分布表的方法，第一種是在加載了“分析工具庫(kù)”宏的情況下，采用“分析工具庫(kù)”中的“直方圖”，見圖1；第二種是直接采用粘貼函數(shù)“frequency”，見圖2。

圖1 直方圖對(duì)話框

圖2 FREQUENCY的對(duì)話框

2 頻數(shù)分布表的編制過(guò)程

2.1數(shù)據(jù)的預(yù)處理 演示數(shù)據(jù)采用200頭大白母豬仔豬一月齡窩重資料，見圖3。由圖3可見，200頭母豬一月齡窩重?cái)?shù)據(jù)排列在Excel表格的1至20行，A至J列，K列數(shù)據(jù)為各組的上限值。圖中的數(shù)據(jù)為連續(xù)型數(shù)據(jù)，應(yīng)采用組距式分組法分組，在分組前需要確定全距、組數(shù)、組距、組中值和組限，然后將各個(gè)變數(shù)分別納入相應(yīng)的組內(nèi)。現(xiàn)以圖3數(shù)據(jù)為例，說(shuō)明其方法與步驟。(1)求全距，全距是資料中變數(shù)的最大值與最小值之差。由圖3可見，具有下劃線的數(shù)據(jù)為最大值和最小值。因此全距為106.9 kg。關(guān)于原始資料中的最大值和最小值可以利用電子表格中MAX(最大值)和MIN(最小值)粘貼函數(shù)來(lái)尋找；(2)確定組數(shù)，組數(shù)通常是人為確定的，對(duì)于本例可初步確定為14組；(3)確定組距，每一組的范圍稱為組距，通常用i表示組距，組距是根據(jù)全距等于組數(shù)與組距之積的關(guān)系計(jì)算所得，為方便進(jìn)一步的分組可以將計(jì)算得到的小數(shù)化為整數(shù)。本例i=106.9/14=7.6，取整數(shù)8作為組距；(4)求組中值與組限，每組中的最小值及最大值分別稱為組下限及組上限，它們的平均數(shù)就是組中值，因而，組下限=組中值-0.5×組距。為避免分組時(shí)第一組中變數(shù)過(guò)多，一般第一組的組中值最好接近或等于資料中的最小值，本例的最小值為11.6，因此可確定第一組的組中值為12，根據(jù)已確定的組距為8可以得出第一組的組下限同樣為8，然后加上一個(gè)組距就為第二組的組下限，依次類推，直至最后一組的組上限包括資料中的最大值為止。于是分組如下：8 -16，16-24，24-32，……，112-120。為避免前一組的組上限等于后一組的組下限所引起該數(shù)值分組時(shí)的歸屬問(wèn)題，通常采用將該數(shù)值歸后一組的原則，這樣分組就更改如下：8-15.9，16-23.9，24-31.9，……，112-119.9。在對(duì)數(shù)據(jù)經(jīng)過(guò)上述處理后就可以利用電子表格來(lái)進(jìn)行頻數(shù)分布表的制作。

圖3 200頭大白母豬仔豬一月齡窩重資料

2.2用直方圖編制頻數(shù)分布表 打開直方圖對(duì)話框，如圖1，在輸入?yún)^(qū)域(I)中輸入$A$1：$J$21，在接收區(qū)域(B)中輸入$K$1：$K$14。在輸出選項(xiàng)中根據(jù)需要進(jìn)行選擇，如選定輸出區(qū)域?yàn)?A$23，那么頻數(shù)分布表就以該單元格為左上角輸出，如同時(shí)選定柏拉圖和累積百分率，見圖4，再點(diǎn)擊確定按鈕，得頻數(shù)分布表如圖5。

圖4 直方圖操作對(duì)話框

圖5 頻數(shù)分布表和柏拉圖表及其累積百分率

由圖5可見前3列為頻數(shù)分布表及累積百分率，第4列至第6列為柏拉圖表及累積百分率，即頻數(shù)以降序排列的形式。

2.3用粘貼函數(shù)Frequency編制頻數(shù)分布表 首先在Excel表格中選擇存放結(jié)果的區(qū)域，為方便起見通常放在分組列的右側(cè)，如$L$1：$L$14，然后打開粘貼函數(shù)frequency對(duì)話框，如圖2，在Data-array中輸入$A$1：$J$21，在Bins-array中輸入$K$1：$K$14，見圖6，再按組合鍵“Ctrl+Shift+Enter”，即同時(shí)按下“Ctrl+Shift+Enter”三個(gè)鍵，得頻數(shù)分布表如圖7。

圖7 頻數(shù)分布表

3 離散型數(shù)據(jù)的處理

離散型數(shù)據(jù)的分組比連續(xù)型數(shù)據(jù)分組簡(jiǎn)單，可以直接按照用數(shù)據(jù)表示的變量值進(jìn)行分組，通常收集得到的數(shù)據(jù)就是已經(jīng)按變量值分組的數(shù)據(jù)，如果原始數(shù)據(jù)沒(méi)有分組，同樣可以采用直方圖或粘貼函數(shù)Frequency來(lái)制作頻數(shù)分布表。在制作頻數(shù)分布表時(shí)，只要在接收區(qū)域中輸入與原始數(shù)據(jù)相同的變量值就可以了，其它過(guò)程同連續(xù)型數(shù)據(jù)。

對(duì)于用文字描述的變量，必須先進(jìn)行變量轉(zhuǎn)換，將文字轉(zhuǎn)換成數(shù)字即可，在完成頻數(shù)分布表制作后，可以再將數(shù)字反轉(zhuǎn)換成原變量。例如，在研究豬毛色遺傳時(shí)，得到的原始記錄為“黑，白和花”毛色，這時(shí)可以將它們分別轉(zhuǎn)換成1，2和3，然后利用直方圖或粘貼函數(shù)Frequency來(lái)制作頻數(shù)分布表，最后再將1，2和3轉(zhuǎn)換成黑，白和花即可。

4 直方圖對(duì)話框

由圖1可見，在直方圖對(duì)話框中，左側(cè)有2個(gè)方框，上面是輸入方框，下面是輸出方框，右側(cè)有3個(gè)按鈕，由上至下依次是確定、取消和幫助。

在輸入方框中包括2個(gè)區(qū)域和1個(gè)正方形復(fù)選框，標(biāo)志(L)，輸入?yún)^(qū)域(I)和接收區(qū)域(B)，后2個(gè)區(qū)域中不能空缺，在輸入?yún)^(qū)域中輸入待分析數(shù)據(jù)區(qū)域的單元格引用，在接收區(qū)域中輸入接收區(qū)域的單元格引用，即各組的組上限值，這些值應(yīng)當(dāng)按升序排列。

標(biāo)志表示對(duì)數(shù)據(jù)的說(shuō)明，如體重，體長(zhǎng)和胸圍等，如果在輸入?yún)^(qū)域的第一行或第一列中包含標(biāo)志項(xiàng)，請(qǐng)選中此復(fù)選框，如果輸入?yún)^(qū)域沒(méi)有標(biāo)志項(xiàng)，請(qǐng)不要選該復(fù)選框。

在輸出選項(xiàng)方框中有6個(gè)選項(xiàng)框，3個(gè)圓形選擇框和3個(gè)正方形選擇框。選擇輸出區(qū)域(O)表示頻數(shù)分布表輸出在同一工作表中，需在此右側(cè)編輯框中鍵入輸出頻數(shù)分布表左上角的單元格引用。

選擇新工作表組(P)表示在當(dāng)前工作簿中插入新工作表，并將計(jì)算結(jié)果輸出在新的工作表中，計(jì)算結(jié)果由新工作表的 A1單元格開始粘貼，如果需要給新工作表命名，請(qǐng)?jiān)谟覀?cè)的編輯框中鍵入名稱。

選擇新工作簿(W)表示計(jì)算結(jié)果將輸出在新的工作簿中，單擊新工作簿選項(xiàng)，可創(chuàng)建一新工作簿，并在新工作簿的新工作表中粘貼計(jì)算結(jié)果。

柏拉圖也稱為排序直方圖，選擇柏拉圖(A)復(fù)選框，可以在輸出表中同時(shí)按頻率數(shù)據(jù)降序排列，如果不選擇該復(fù)選框，Micrnsoft ExceL將按組距值升序來(lái)排列數(shù)據(jù)，即只輸出頻數(shù)分布表。

選擇累積百分率(M)復(fù)選框，可以在輸出表中增加一列累積百分率，并同時(shí)在直方圖中添加累積百分率線，如果不選擇，則不輸出累積百分率。

選擇圖表輸出(C)復(fù)選框，可以在輸出表的同時(shí)生成一個(gè)嵌入式直方圖。

5 結(jié)束語(yǔ)

采用直方圖編制頻數(shù)分布表，可以得到頻數(shù)分布表以外的多個(gè)結(jié)果，如柏拉圖，累積百分率，以及直方圖等，但需要加載分析工具庫(kù)宏。這一點(diǎn)往往在許多電腦上無(wú)法實(shí)現(xiàn)。如采用粘貼函數(shù)Frequency編制頻數(shù)分布表，對(duì)所有裝有Microsoft Excel軟件的電腦都能實(shí)現(xiàn)，因?yàn)樵贓xcel軟件中自帶粘貼函數(shù)Frequency，但是在使用時(shí)，一定要記住二點(diǎn)，首先，打開粘貼函數(shù)Frequency前，先要選擇結(jié)果存放區(qū)域，其次，在輸出結(jié)果時(shí)，必須使用組合鍵“Ctrl+Shift+Enter”。