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

(浙江大學動物科學學院，浙江 杭州 310058)

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

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

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

圖1 直方圖對話框

圖2 FREQUENCY的對話框

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

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

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

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

圖4 直方圖操作對話框

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

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

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

圖7 頻數(shù)分布表

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

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

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

4 直方圖對話框

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

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

標志表示對數(shù)據(jù)的說明，如體重，體長和胸圍等，如果在輸入?yún)^(qū)域的第一行或第一列中包含標志項，請選中此復選框，如果輸入?yún)^(qū)域沒有標志項，請不要選該復選框。

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

選擇新工作表組(P)表示在當前工作簿中插入新工作表，并將計算結果輸出在新的工作表中，計算結果由新工作表的 A1單元格開始粘貼，如果需要給新工作表命名，請在右側的編輯框中鍵入名稱。

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

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

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

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

5 結束語

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