Excel電子表格編制頻數分布表的方法

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

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

1 頻數分布表的編制方法

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

圖1 直方圖對話框

圖2 FREQUENCY的對話框

2 頻數分布表的編制過程

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

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

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

圖4 直方圖操作對話框

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

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

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

圖7 頻數分布表

3 離散型數據的處理

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

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

4 直方圖對話框

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

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

標志表示對數據的說明，如體重，體長和胸圍等，如果在輸入區域的第一行或第一列中包含標志項，請選中此復選框，如果輸入區域沒有標志項，請不要選該復選框。

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

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

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

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

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

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

5 結束語

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