实验一二三指导书09.9.28.doc

统计学原理实验指导Excel在统计学中的应用中北大学经济与管理学院实验一 EXCEL的基本统计功能 Microsoft EXCEL是一个设计精良、功能齐全的办公软件。它除了具有我们常用的办公功能，如通过电子表格的形式对数字数据进行组织和计算；将数字数据转化为可视化的图表和数据库管理功能外。它还是一个十分强大而且非常易用于使用数据统计和预测工具。EXCEL的统计功能分为基本统计和预测两部分。我们将按照统计顺序逐一介绍。这一节将结合实例，重点介绍EXCEL2000的基本统计功能。EXCEL2000提供的基本统计主要包括描述性统计、频数统计、等级和百分数等方法。这些统计方法主要利用EXCEL统计函数或数据分析中的描述统计过程来实现。一、数据的筛选与排序图1是一份学生成绩登记表，我们就以此为例，看看excel是如何筛选出我们需要的数据。 一、自动筛选 目的：将语文成绩“大于等于90分”的学生和语文成绩“小于60分”的学生筛选出来。 实现过程如下。 1、选中数据表中任意一个单元格，执行“数据筛选自动筛选”命令，此时，每一项列标题右侧均出现一个下拉按钮（参见图1）。2、单击“语文”右侧的下拉按钮，在随后弹出的快捷菜单中（参见图1），选“自定义”选项，打开“自定义自动筛选方式”对话框（如图2）。 3、单击左上方框右侧的下拉按钮，在随后出现的对话框中，选“大于或等于”选项，然后在右上方框中输入数值“90”；选中“或”选项，再单击左下方框右侧的下拉按钮，在随后出现的快捷菜单中，选“小于”选项，然后在右下方框中输入数值“60”（参见图2）。 4、设置好以后，单击“确定”按钮，符合条件的数据即刻被筛选出来（筛选的结果如图3所示）。 提示： 在上述筛选结果的基础上，对“数学”列继续进行筛选操作，将筛选条件设置为“大于或等于”“90”，就可将“语文”和“数学”成绩都“大于或等于90”分的学生筛选出来。 二、高级筛选 目的：将“语文”或“数学”成绩“大于或等于”“90”分的学生筛选出来。 实现过程如下。 1、先执行“数据筛选自动筛选”命令，撤销“自动筛选”功能。 如果前面没有进行“自动筛选”操作，则此步可以省略。 2、分别选中D66、D67、E66、E68单元格，输入内容：语文、=90、数学、=90（如图4）。 3、选中成绩表中任意单元格，执行“数据筛选高级筛选”命令，打开“高级筛选”对话框（如图5）。 4、选中“将筛选结果复制到其他位置”选项。 提示： 也可以不选择此选项，但为了保留原有数据，建议最好将筛选结果复制保存到其他位置上。 5、单击“条件区域”右侧的红色按钮，此时，对话框缩小成一个浮动状态条（如图6），用鼠标选中D66至E68条件区域，然后按浮动状态条右侧的红色按钮，返回“高级筛选”对话框。 6、单击“复制到”右侧的红色按钮，此时，对话框缩小成一个浮动状态条（如图7），用鼠标选中保存筛选结果的第1个单元格（如A69），然后按浮动状态条右侧的红色按钮，返回“高级筛选”对话框。 提示：“数据区域”系统通常按默认区域自动添加，如果需要更改，仿照上面操作就行了。 7、全部设置完成后，单击“确定”按钮，符合条件的数据就被筛选出来了。 提示： 如果把“条件区域”改成如图8所示的样式（即，条件数值位于同一行中），然后执行上述筛选制度操作，则只将“语文”和“数学”成绩都“大于或等于”“90”分的学生筛选了出来。 三、排序通过排序，可以根据某特定列的内容来重排数据清单中的行。例如，假设一包含销售数据的数据清单有销售员、单位数量和订单日期等列。我们可以用“排序”命令按销售员重排数据清单、从高到低组织单位数量或从早到晚排好订单日期。当选择“排序”命令后，Microsoft Excel 会利用该列和我们指定的排序次序，或利用自定义排序次序来重新排列行、列或单个的单元格。除非另有指定，否则Microsoft Excel 会根据我们选择的“主要关键字”列的内容以升序顺序（最低到最高）对行作排序。如果我们按一列以上作排序，主要列中有完全相同项的行会根据我们指定的第二列作排序。第二列中有完全相同项的行会根据我们指定的第三列作排序。提示：排序操作不仅仅适用于数据库，对于我们在工作表中的任意选定范围，我们都可以执行排序操作。12.3.1 按列排序按照某一选定列排序的操作步骤如下： (1) 执行“数据”菜单上的“排序”命令，出现一个如图12-6所示的对话框。 (3) 在“主要关键字”列表框中，选定重排数据清单的主要列，在本例中我们选定“地区”。选定“递增”或“递减”选项按钮以指定该列值的排序次序，在本例中选定“递增”。若要由一列以上来作排序，在“次要关键字”和“第三关键字”框中，选定您想用作排序的附加列。对于每一列再选定“递增”或“递减”选项按钮。如果在数据清单中的第一行包含列标记，在“当前数据清单”框中选定“有标题行”选项按钮，以使该行排除在排序之外，或选定“没有标题行”使该行也被排序。小技巧：按行排列数据相信大家对Excel的排序功能都不陌生，通常数据只是按列排列。有时我们需要按行排列数据，这时只需从“数据”菜单中选择“排序”，在排序对话框中左下角选中“选项”按钮，在弹出的对话框中选择“按行排序”，并设置相应关键字即可，而且还可以更改排序的方法是按拼音还是按笔画排序或是选择自定义序列中的排序方法。二、统计图表（一）利用 Excel 对原始数据进行分组并得到频数分布和直方图 例3-1：某车间工人进行技能测试成绩分别为下表： （单位：分）57894984868775737268758297816781547987957671609065767270868589896457838178877261要注意的是： Excel 在统计各组频数时，是按“各组上限在本组内”的原则处理的（如本例中， 59 分及 59 分以下都归入第一组，其余数据中 69 分以及 69 分以下都归入第二组，这样得到的频数才相当于统计习惯上所指的“ 60 以下”、“ 60 一 70 ”的频数，依此类推）。具体步骤如下：第一步，选择“工具”菜单栏中的“数据分析”第二步，在“数据分析”菜单选择“直方图”命令。第三步， 在“直方图”对话框 “输入区域”数值框中输入原始数据所在区域（例中为 Al : J4 ，这里的单元格引用使用绝对引用或相对引用均可。相对引用：当把公式复制到其它单元格中时，行或列的引用会改变。所谓行或列的引用会改变，即指代表行的数字和代表列的字母会根据实际的偏移量相应改变。 绝对引用：当把公式复制到其它单元格中时，行和列的引用不会改变。 混合引用：行或列中有一个是相对引用，另一个是绝对引用。），在 “接收区域”数值框中输入指定的分组上限值所在区域（本例为 A8 : A12 。如果将数据名称所在单元格也包括在输入和接受区域中，就应2选中标志复选框。（本例未包括，此处不需要选中单元格。在“输出区域”数值框中制定频数分布输出区域的起点单元格（本例中为 C7 ) ；选中“图表输出”（否则只输出频数分布而不显示直方图）复选框，如图 3 一 7 所示；单击“确定”按钮即可得到次数分布表和直方图，如图3-8所示。 在频数分布表基础上，也可以进一步计算出各组频率（比重）及向上累计频数和频率等。如本例中，可先在单元格 D13 中单击“自动求和”按钮或输入公式“ = SUM ( DS : D12 ) ”得到工人总数，然后在单元格 E7 中输入“比重（ % ) ，在单元格 E8 中输入公式“ = D8 / $ D $ 13 * 100 ，按 Enter键后利用鼠标拖动的方法将单元格 E8复制到单元格 E9 至 E13 中，即可得到各组比重。在单元格 F7 中输入“向上累计人数”，在单元格 F8中输入公式“ = D8 ，在单元格 F9 中输入公式“ = D9 + F8 ，按 Enter 键后将单元格 F9 复制到单元格 F10 至 F12 中，即可得到各组向上累计人数。在单元格 G7 中输入“向上累计频率夕，在单元格 G8 中输入公式“ = F8 / $ DS13 * 100 ，按 Enter键后将单元格 G9 复制到单元格 G12 至 G12 中，可得到各组的向上累计频率。此外，直方图中的曲线代表各组累积的百分比。（二)、利用 Excel 绘制其他统计图在统计整理中常用的统计图有柱形图或条形图、折线图、饼图、雷达图等。利用 Excel 的图表向导可以很方便地绘制所需统计图。限于篇幅，下面仅说明折线图和饼状图的绘制方法。1、 利用 Excel 绘制折线图以例 3 一 3 中我国 1991 一 2005 年国内生产总值的数据来说明折线图（如图 3 一 4 所示）的制作过程。首先输入数据。在工作表的 Al 中输入“年份”，在 Bl 中输入“国内生产总值”，在 A2 一 A16 中分别输入 1991 一 2005 （也可只输入前两个数，再用自动填充功能产生年份序列），在 B2 一 B16 中分别输入各年国内生产总值的数值。例 3 一 3 我国 1991 一 2005 年国内生产总值表年份国内生产总值年份国内生产总值19912178119998967719922692320009921519933533420011096551994481982002120333199560794200313582319967117720041598781997789732005183085199884402然后选择“插入”一“图表”命令（或单击工具栏中的“图表向导”按钮），在“标准类型”中选择“折线图”（再选择其中一种子图表类型，本例中选择带点的折线图）命令。 单击“下一步”按钮，在“数据区域”栏中输入纵坐标数据所在单元格区域（本例中输入“ Bl : B16 ，也可以单击输入栏尾端的小图表，再用鼠标选定该数据区域。这里把数据名称所在单元格包括在内，可自动生成名为“国内生产总值”的图表标题和图例，若指定数据区域为 B2 : B16 ，则自动生成的图例名为“系列 1 ) ；选择“系列”选项卡，在“分类（ X ）轴标志”栏中输入年份数据的区域“ AZ : A16 （若不指定此选项，横坐标采用自然数自动编号）。单击“下一步”按钮，在“标题”、“数据标识”、“网格线”等选项卡中按需要作出相应选择（也可以忽略这些选择，直接单击“完成”按钮）。单击“下一步”按钮指定图表的输出位置（默认位置是当前工作表，所以通常也可以忽略该项选择），最后单击“完成”按钮即可。2、 利用 Excel 绘制饼状图饼状图也简称饼图，可以根据各组比重来绘制，也可以直接根据各组总量指标来绘制（ Excel 会自动计算出比重来绘图，并在图中显示各组比重）。下面以表 3 一 6 中 2006 年 1 一 3 季度我国国内生产总值的按三次产业划分的数据来说明饼状图的制作。首先输入数据。在工作表的 A2 一 A4 中分别输入“第一产业”、“第二产业”和“第三产业”字样，在 Bl 中输入“绝对额”字样，在 B2 一 A4 中分别输入相应的指标数值。然后选择“插入” ? “图表”命令，在“标准类型”中选择“饼图”（再任选一个子图表类型）命令；单击“下一步”按钮，在“数据区域”栏中输入数值所在单元格区域（本例中输入 BZ : B4 ，也可以用鼠标选定该数据区域） ; 选择“系列”选项卡，在“分类（ X ）轴标志”栏中输入各组名称所在区域，以便在饼图中显示各组（类别）名称，本例中输入“ AZ : A4 ”。若不指定此选项，则各组名称采用自然数自动编号代替。单击“下一步”按钮，可在“数据标志”选项卡中选择希望在饼图中显示的内容。本例选择了“类别名称”、“值”和“百分比”，若“图例”选项卡中选择了显示图例，就不必选“类别名称”。单击“完成”按钮即可得出所需的饼图，如图 3 一 6 所示。22实验二 集中指标以下利用Excel提供的函数，计算算术平均数、几何、调和、中位数、众数等集中指标。学会使用Excel计算各种数字特征，要求独立完成相关作业。一、由未分组数据计算分布特征的有关指标（一）用EXCEL统计函数进行特征值计算 EXCEL描述性统计函数主要包括一般统计函数，集中趋势函数和变异统计函数：如图35所示，单元格区域B4:B53是50名工人某日加工零件个数资料（例32）。C4:C19是一些描述性统计量的说明。D4:D19是一般统计结果。其做法有如下两种。1在单元格D3中输入公式“=COUNT （B4:B53）”并回车，得到B4:B53区域中非空数值型数据的个数统计；在单元格D4中输入公式“=SUM（B3:B53）”并回车，得到50名工人日加工零件数的总和；同样，在D5:D15单元格中中分别输入MAX、MIN、AVERAGE、MEDIAN、GEOMEAN、HARMEAN、AVEDEV、STDEV、VAR、KURT和SKEW 函数，分别得到50个数据中的最大值、最小值、平均值、中位数、几何平均数和调和平均数及变异统计的平均差、标准差、方差峰度和偏度。图35 统计函数2首先在EXCEL2000的系统工具栏中选择“插入”中的函数，其次在函数对话框中选择所计算的函数，然后根据函数向导提示一步步的完成。（二）宏程序进行特征值计算除了利用上述统计函数完成统计数据分析外，EXCEL还在数据分析宏程序中提供了一个描述性统计过程。对于例32，我们也可以利用这个“描述性统计”宏过程来计算，其方法更为简单。我们点击图中的“描述性统计”过程，“描述性统计”过程的菜单如图36A，36B，36C 所示。我们在“输入区域”中输入数据所在单元格区域“B3:B53”，选择输出“汇总统计”和“平均数值信度”，在“K个最大值”和“K个最小值”选择中，选择系统默认值“1”，表示选择输出第1个最大值和第1个最小值。输入“输出区域”为E1单元格，然后按“确定”，即得到图所示特征值计算结果，该结果与图中利用统计函数计算的结果是一致的。图36A 数据分析宏程序 图36B 描述性统计过程对话框图3-6C描述性统计结果二、由分组数据计算算术平均数指标对于分组数据的有关指标，只能用 Excel 的公式与复制功能来实现。下面就以图 5 一 4 的数据来说明有关指标的具体计算操作。成绩（分）学生（人数）f 组中值xxf60 分以下65533060-702265143070-802675195080-902185178590-100495380合计79-5875算术平均数74.36708861 ( l ）输入数据，如图 5 一 4 的 A 、 B 、 C 列所示。 ( 2 ）算术平均数的计算：在单元格 DZ 中输入公式“ = C2* B2 ，按 Enter 键后将单元格 D2 的公式向下复制到 D6 ，在单元格 D7 中输入公式“ = SUM ( D 2 : D6 ) （或单击自动求和图标即可），按 Enter 键后计算算术平均数所需的分子的数值（本例中为 5875 ) 就显示在单元格 D7 中。在单元格 A9 中输入“算术平均数”，在单元格 B9 中输入公式 = D7 /B 7 ，按 Enier 键后单元格 B9 中显示的数值（ 74 . 36709 ）就是所求的算术平均数。对于众数、中位数等统计指标的计算，同样只需要将相应的计算公式在 Exeel 中实现即可。补存：分组数据中位数的计算例：某地区农民家庭按人均收入分组的分组数据资料如下：收入（元）频率（%）01001002002003003004004005005006006007002.313.719.715.215.12014计算家庭人均收入的中位数，均值，标准差。2、操作步骤（如图2-1所示）（1）绘制计算表框架，且输入分组数据：分组，频率，组限。（2）用各种常用公式或函数在计算表中计算其他各栏。公式如下：累积频率：（略）；组中值：(D4D10+E4E10)/2组距：= E4E10 - D4D10xifi：=F4F10*B4B10xi2fi：=POWER（F4F10，2）*B4B103.计算中位数，均值，标准差。公式如下：中位数：D7+(B11/2-C6)/B7*G7均值：SUM（H4H10）标准差：SQRT（I11-POWER（H11,2）图2-1 分组资料数字特征计算描述统计结果中： “中值”即“中位数”；“模式”即“众数”；“区域”即“极差”；“样本方差”即；“标准偏差”即；“峰值”，EXCEL工作表函数为KURT，其计算公式为：“偏斜度”，EXCEL工作表函数为SKEW，其计算公式为：“置信度”，其计算公式为：图2-4 “描述统计”计算结果实验三 变异指标分布与形状变异指标和集中指标是描述统计的基本指标。例一：仍用上面例子说明变异指标的计算。接上面：以图 5 一 4 的数据来说明有关变异指标的计算操作。成绩（分）学生（人数）f 组中值xxf60 分以下65533060-702265143070-802675195080-902185178590-100495380合计79-5875算术平均数74.36708861 ( 1 ）平均差的计算：在单元格 El 中输入“加权的离差绝对值”，在单元格 E2 中输入公式“ = AB S ( C2 一 B $ 9 ) * B2 ，按 Enter 键后将单元格 E2 Enter 键后在计算平均差所需的分子的数值 ( 644 . 557 ）就显示在单元格 E7 中。在单元格 A10 中输入“平均差”，在单元格 B10 中输入公式“ = E7 / B7 ，按 Enter 键后单元格 B10 中显示的数值（ 8 . 1 58 949 ）就是所求的平均差。 ( 2 ）方差的计算：在单元格 Fl 中输入“加权的离差平方”，在单元格 F2中输入公式“=（ c2 一 B $ 9 ）2 * B2 ，按 Enter 键后将单元格 F2 的公式向下复制到 F6 ，在单元格 F7 , 中输入公式“ = SuM ( F2 : F6 ) ，按 Enter 键后在计算方差所需的分子的数值（ 8 268 . 3544 ) 显示在单元格 F7 中。在单元格 All 中输入“方差”，在单元格 Bll 中输入公式“ = F7 /B 7 , 按 Enter 键即可在单元格 B11 中得到的所求方差的数值（本例中为 1 04 . 6627 ）。 ( 3 ）标准差的计算：由于标准差就等于方差的平方根，所以在单元格 A12 中输入“标准差”，在单元格 B12 中输入公式“ = Bll 0 . 5 ”或“ = SQRT ( Bll ) ，按 Enter 键即可在单元格 B12 中得到的所求标准差的数值（本例中为 10 . 23048 ）。 例2为了了解小学生每天花在看电视的时间大概有多久，某老师抽样了20位小学四年级的学生，请他们记录每天看电视的时间。得到如图3-3所示的数据（单位：分钟）。请问（1）这七天的平均数、区域、四分位数、平均绝对离差、标准差、方差各为多少？（2）从这七天的平均数和标准差，你得到什么结论？（3）平均来说（不分假日和非假日），每位学生花多少时间看电视？作法1如图3-3所示，将所有的数据置于“电视”的工作表内。2Excel提供了函数AVERAGE以计算平均数；MAX计算最大值，MIN计算最小值，两者相减为区域；QUARTILE计算四分位数；STDEV计算样本标准差；VAR计算样本方差。计算第一位学生的各种指标，该学生的数据放于B2:H2中，在适当地址如I2中键入“=AVERAGE（B2:H2）”，即可得到第一位学生收视时间的平均数。在J2中键入“=MAX（B2:H2）-MIN(B2:H2)”，即可得到该学生的区域。在K2中键入“=QUARTI