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”。