EXCEL在统计描述中的运用

第五节EXCEL在统计描述中的运用一、编制分布数列【例3-19】某地区进行农产量抽样调查，抽取50个单位构成样本。样本资料如下：亩产量数据单位：公斤240440400375500528412425320400520600485600588412444430342364450455465500580345340320300403445360517390410360422428520400425385440380548304448605325500要求（1）编制次数分布数列（组距为100，进行等距分组），并绘制次数分布图；（2）分别向上累计和向下累计并绘制累计分布图。解：本例可用[直方图]分析工具或FREQUENCY等函数来完成。但[直方图]工具不能直接进行向下累计，需要借助Excel公式才可进行。（一）用[直方图]工具进行分析。直方图工具，用于在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累积频率，可以统计有限集中某个数值元素的出现次数。主要步骤如下：1．输入数据。如图3-15所示，A列输入亩产量，A1为列标志“亩产量”，C列为分组标志，B2:B7为分组组限，B1为列标志“组限”。在确定组限时，需要注意：（1）组限指的是某一组的上限，如299.9是300以下（也即200-300）这一组的上限，399.9是300-400这一组的上限。（2）对未排序的亩产量数据，可先用MAX与MIN函数确定亩产量的最大与最小值，以方便确定组限。（3）[直方图]工具分组时采用的是“上限在内”原则，为符合统计分组的“上限不在内”原的则，每个组限均不应与亩产量相同。如本例中组限399.9表示将统计出亩产量大于299.9而小于等于399.9的数据点个数，如果亩产量中有399.9这个数，则可将300-400这一组的上限上为399.99。2．调出[直方图]对话框，其主要选项的含义如下。输入区域：在此输入待分析数据区域的单元格范围。本例输入区域为“$A$1:$A$51”。接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在的话，Excel将统计在当前边界点和相邻的高值边界点之间的数据点个数。如果某个数值等于或小于某个边界值，则该值将被归到以该边界值为上限的区间中（注意：与统计分组“上限不在内”原则有所区别）。如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。本例接收区域为“$B$1:$B$6”。图3-14标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel将在输出表中生成适宜的数据标志。柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例[直方图]对话框的填写如图3-14所示。3．单击[确定]按钮即可。完整的结果通常包括三列和一个频率分布图，第一列是数值的区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比。在输出的结果中，“频率”指的是统计中所指的“频数”，“累积%”指的是“向上累计频率”。结果中原本还有一“其它”行的数据，该行是亩产量大于699.9的频数与累计频率（位于单元格区域D7:F7），因本例中没有大于699.9的数据，所以可将该行删除，即选定D7:F7，然后执行菜单命令[编辑][删除]命令。由于该工具总会自动统计出大于最大组限的数据的频数，所以本例中组限也可以不使用699.9，即在第2步中接收区域为“$B$1:$B$6”，在输出的结果中，将“其它”更改为我们所需要的标志性文字，如改为“699.9”。经过修改的结果如图3-15所示。图3-154．计算向下累计频率。在G1单元格输入列标志“向下累计”。在G6单元格输入“=E6/SUM($E$2:$E$6)”，计算出最后一组的频率，在G5单元格中输入“=E5/SUM($E$2:$E$6)+G6”得到最后一组和倒数第二组的累计频率，将G5单元格的公式复制到G2:G4，计算出向下累计这一列。5．在图表中加入向下累计曲线。选定图表区，此时菜单上出现[数据]按钮变为[图表]按钮，执行菜单命令[图表][数据源]，调出[源数据]对话框。在[源数据]对话框的[系列]页框中，单击[添加]按钮，输入所添加系列的名称与值。要输入系列名称，请单击[名称]文本框，再用鼠标单击工作表中的G1单元格，此时[名称]文本框显示“=分布数列!$G$1”（“分布数列”为工作表标签名）；要输入系列值，请单击[值]文本框，再用鼠标在工作表上选定G2:G6单元格，此时[值]文本框中显示“=分布数列!$G$2:$G$6”。最后单击[确定]按钮，结果见图3-16。图3-16（二）用FREQUENCY函数编制分布数列的主要步骤如下。1．输入数据。如图3-17所示，A、B、C列输入的数据同第一种方法，在D1:F1中依次输入列标“频数”、“向上累计频数”和“向下累计频数”。其中“组限”的含义同第一种方法。2．选定D2:D6，输入公式“=FREQUENCY(A2:A51,B2:B6)”，然后按按CRTL+SHIFT+ENTER组合键，即可计算出各组的频数。3．计算向上累计频数。在E2单元格中输入“=D2”，在E3单元格中输入公式“=D3+E2”，再将公式复制到E4:E6。4．计算向下累计频数。在F6单元格中输入“=D6”，在F5单元格中输入公式“=D5+F6”，再将公式复制到F2:F4。计算结果如图3-17所示。5．根据得到的计算结果，可用Excel的图表功能绘制出亩产量分布的直方图、折线图等。图3-17二、计算有关的描述统计指标（一）利用[描述统计]分析工具【例3-20】下面是50名工人完成某一装配工序所需时间：单位：分钟3538443344434840453045324239493745373642354132463430433744493646453637374536464238433438473529414041要求对装配工时进行描述统计分析。解：主要操作步骤如下：1．输入数据。如图3-19所示，在A2:A51输入时间数据，在A1输入列标志“工时”。2．调出[描述统计]对话框，其主要选项的含义如下。“输入区域”：在此输入待分析数据区域的单元格范围。本例输入区域为“$A$1:$A$51”。“分组方式”：如果需要指出输入区域中的数据是按行还是按列排列，则单击[逐行]或[逐列]。本例分组方式为“逐列”。标志位于第一行/列：如果输入区域的第一行中包含标志项(变量名)，则选中[标志位于第一行]复选框；如果输入区域的第一列中包含标志项，则选中[标志位于第一列]复选框，本例要选中该复选框；如果输入区域没有标志项，则不选任何复选框，Excel将在输出表中生成适宜的数据标志。“输出区域”：在此框中可填写输出结果表左上角单元格地址，用于控制输出结果的存放位置。本例输出区域填“$C$1”，整个输出结果分为两列，左边一列包含统计标志项，右边一列包含统计值。根据所选择的[分组方式]选项的不同，Excel将为输入表中的每一行或每一列生成一个两列的统计表。“新工作表”：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始存放计算结果。如果需要给新工作表命名，则在右侧编辑框中键入名称。“新工作簿”：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果。“汇总统计”：指定输出表生成下列统计结果，则选中此复选框。这些统计结果有：样本的平均值（X），抽样平均误差（nS/），组中值（Median），众数（Mode），样本标准差（S），样本方差（S2），峰度值，偏度值，极差（Max-Min），最小值（Min），最大值（Max），样本总和，样本容量（n）和一定显著水平下总体均值的置信区间。本例选中该复选框。“平均数置信度”：若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，置信度95%可计算出的总体样本均值置信区间为10，则表示：在5%的显著水平下总体均值的置信区间为(X-10,X+10)。本例平均数置信度为95%。第k个大/小值：如果需要在输出表的某一行中包含每个区域的数据的第k个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入k的数值。本例[描述统计]对话框的填写如图3-18所示。图3-183．[描述统计]对话框填完后，单击[确定]按钮，结果如图3-19所示。图3-19有两点需要注意。（1）各种分析工具对话框输出选项中“输出区域”、“新工作表组”和“新工作簿”的含义基本相同，以下不再赘述。（2）见图3-19中指标的名称与统计中的习惯叫法不大一致，确切的指标名称如本例操作步骤第2步中所述。（二）利用统计函数完成对于分组数据，不能直接用[描述统计]分析工具来计算描述统计有关指标。应综合应用Excel的公式与函数来实现。【例3-21】某粮食作物的产量和播种面积资料如下。亩产量x400～500500～600600～700700～800800～900900～1000合计播种面积f63050604014200根据上表资料计算加权平均亩产量、标准差以及偏度和峰度等。解：主要操作步骤如下。1．输入数据。如图3-20所示，A2:A6输入亩产量的分组，这些数据在Excel中被认为是文本，不能直接参加数值运算，A1输入本列标志“亩产量”。B2:B6输入各组的组中值，B1输入本列标志“组中值”。C2:C6输入播种面积数据，C1为本列标志“播种面积”。B9:B15存放的是最终结果与一些中间变量值，对应的A9:A15则是它们的名称。2．定义变量名。先定义样本数据组中值及播种面积的变量名。选定B1:C7，执行菜单命令[插入][名称][指定]，单击“首行”选项，最后单击[确定]按钮。再定义最终结果与中间变量的名称，选定A9:B15，执行菜单命令[插入][名称][指定]，单击“最左列”选项，最后单击[确定]按钮。图3-202．计算加权平均亩产量。在B9单元格输入如下公式：=SUMPRODUCT(组中值*播种面积)/SUM(播种面积)当然，如果没有定义变量名，则要使用单元格引用，公式如下：=SUMPRODUCT(B2:B7*C2:C7)/SUM(C2:C7)3．计算其它指标。在B10:B14中，依次输入以下公式：=SQRT(SUMPRODUCT((组中值-加权平均亩产量)^2*播种面积)/SUM(播种面积))=SUMPRODUCT((组中值-加权平均亩产量)^3*播种面积)/SUM(播种面积)=SUMPRODUCT((组中值-加权平均亩产量)^4*播种面积)/SUM(播种面积)=三阶动差/标准差^3=四阶动差/标准差^4-34．计算结果如图3-20所示。这里有几点需要说明。（1）可参照本例的方法计算分组情况下的其它有关描述统计的指标，如方差、标准差等。（2）对于众数及中位数的计算，不需要特别的技巧，只要将统计中的计算公式在Excel中实现即可。这时Excel更像一个普通的计算器，但Excel可记下公式，便于检查与修改，同时在数据量很大，分组很多时，借助Excel的相关函数可很快地找出众数组和中位数所在组。如本例众数的计算，首先确定众数组，为“700～800”这一组。然后根据等距分组的上限公式，在B15单元格输入公式“=700+(C5-C4)*100/((C5-C4)+(C5-C6))”即可，其中700为众数组下限，100为组距，结果如图3-20所示。