统计学实验讲义EXCEL.doc

1、目 录统计学实验指导 2实验一 用 EXCEL 搜集与整理数据 21. 用 EXCEL 搜集数据 22. 用 EXCEL 进行统计分组 43. 用 EXCEL 作统计图 6实验二 用 EXCEL 计算描述统计量 71. 用函数计算描述统计量 .72. 描述统计工具量的使用 .8实验三 用 EXCEL 进行参数估计和假设检验 91. 用 EXCEL 进行区间估计 92. 用 EXCEL 进行假设检验 10实验四 用 EXCEL 进行相关与回归分析 111. 用 EXCEL 进行相关分析 122. 用 EXCEL 进行回归分析 13实验五 用 EXCEL 进行时间序列和指数分析 151. 用 EX

2、CEL 计算季节指数 152. 用 EXCEL 计算总指数 163. 用 EXCEL 计算平均指数 172统计学实验指导实验一 用 EXCEL 搜集与整理数据实验目的：掌握用 EXCEL 进行数据的搜集整理和显示。1. 用 EXCEL 搜集数据搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查。我国的统计调查将以抽样为主，在这里介绍一下如何用 EXCEL 进行抽样。使用 EXCEL 进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，编号后，将编号输入工作表。【例 1】 假定有 100 个总体单位，每个总体单位给一个编号，共有从 1 到 1

3、00 个编号，输入工作表后如图 1-1 所示：图 1-1 总体各单位编号表输入各总体单位的编号后，可按以下步骤操作：第一步：单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项） ，打开数据分析对话框，从中选择抽样，如图 1-2 所示：图 1-2 数据分析对话框3第二步：单击抽样选项，弹出抽样对话框。如图 1-3图 1-3 抽样对话框第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$10，系统将从 A 列开始抽取样本，然后按顺序抽取 B 列至 J 列。如果输入区域的第一行或第一列为标志项（

4、横行标题或纵列标题） ，可单击标志复选框。第四步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如要在 100 个总体单位中抽取 12 个，则在“间隔”框中输入 8。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所

5、有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第四步：指定输出区域，在这里输入$K$1 ，单击确定后，即可得到抽样结果，如图 1-4 所示。图 1-4 等距抽样结果42. 用 EXCEL 进行统计分组用 EXCEL 进行统计分组有两种方法，一是利用 FREQUENCY 函数；二是利用数据分析中的“直方图”工具，这里主要介绍后者的操作方法。【例 2】 将 50 名工人的月产量资料输入工作表，如图 1-5 所示。图 1-5 工人月产量资料然后按以下步骤操作：第一步：在工具菜单中单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框，如图 1-6

6、所示。图 1-6 直方图对话框第二步：在输入区域输入$A$1:$J$5，在接收区域输入$A$9:$A$15。接收区域指的是分组标志所在的区域，假定我们把分组标志输入到 A9:A15 单元格，注意这里只能输入每一组的上限值，即 130，160，190，220，250，280，310。第三步：选择输出选项，可选择输入区域、新工作表组或新工作薄。我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角） ，这里我们推荐只输入一个单元格（本例为 A7） ，因为我们往往事先并不知道具体的输出区域有多大。第四步：选择图表输出，可以得到直方图；选择累计百分率，系统将在直方

7、图上添加5累积频率折线；选择柏拉图，可得到按降序排列的直方图。第五步：按确定按钮，可得输出结果如图 1-7。图 1-7 频数分布和直方图应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击任一直条，然后右键单击，在弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图 1-8 所示。图 1-8 数据系列格式对话框在对话框中选择选项标签，把间距宽度改为 0，按确定后即可得到直方图，如图 1-9 所示。6图 1-9 调整后的直方图3. 用 EXCEL 作统计图EXCEL 提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图

8、、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。【例 3】 这里学习如何作饼图，首先把数据输入到工作表中，如图 1-10 所示：图 1-10 河北省主要市 2008 年人口情况按以下步骤可作出饼图：第一步：选中某一单元格，单击插入菜单，选择图表选项，弹出图表向导对话框，如图 1-11。7图 1-11 图表向导对话框第二步：在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框，如图 1-12 所示。图 1-12 源数据对话框第三步：在源数据对话框中填入数据所在区域，单击完成按钮，即可得如 1-13 所示的

9、饼图。石 家 庄 市唐 山 市秦 皇 岛 市保 定 市邯 郸 市邢 台 市沧 州 市张 家 口 市图 1-13 饼图实验二 用 EXCEL 计算描述统计量实验目的：用 EXCEL 计算描述统计量。EXCEL 中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。1. 用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在 EXCEL 中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数计算。（1）众数【例 4】 为了解某经济学院新毕业大学生的工资情况，随机抽取

10、 30 人，月工资如下：81560 1340 1600 1410 1590 1410 1610 1570 1710 1550 1490 1690 1380 1680 1470 1530 1560 1250 1560 1350 1560 1510 1550 1460 1550 1570 1980 1610 1510 1440用 函 数 方 法 求 众 数 ， 应 先 将 30 个 人 的 工 资 数 据 输 入 A1： A30 单 元 格 ， 然 后 单击 任 一 空 单 元 格 ， 输 入 “=MODE（ A1： A30） ”， 回 车 后 即 可 得 众 数 为 1560。（ 2） 中 位

11、数仍 采 用 上 面 的 例 子 ， 单 击 任 一 空 单 元 格 ， 输 入 “=MEDIAN（ A1： A30） ”， 回车 后 得 中 位 数 为 1550。（ 3） 算 术 平 均 数单 击 任 一 单 元 格 ， 输 入 “=AVERAGE（ A1： A30） ”， 回 车 后 得 算 术 平 均 数 为1535。（ 4） 标 准 差单 击 任 一 单 元 格 ， 输 入 “=STDEV（ A1： A30） ”， 回 车 后 得 标 准 差 为135.0287。2. 描述统计工具量的使用仍使用上面的例子，我们已经把数据输入到 A1：A30 单元格，然后按以下步骤操作：第 一 步 ：

12、 在 工 具 菜 单 中 选 择 数 据 分 析 选 项 ， 从 其 对 话 框 中 选 择 描 述 统 计 ， 按 确 定后 打 开 描 述 统 计 对 话 框 ， 如 图 1-14 所 示 。图 1-14 描述统计对话框第二步：在输入区域中输入$A$1:$A$30，在输出区域中选择$C$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第 K 大值和第 K 小值会给出样本中第 K 个大值和第 K个小值。第三步：单击确定，可得输出结果，如图 1-15 所示。9图 1-15 描述统计输出结果上面的结果中，平均指样本

13、均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为 n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距。实验三 用 EXCEL 进行参数估计和假设检验实验目的：用 EXCEL 进行参数估计和假设检验在 EXCEL 中，进行参数估计只能使用公式和函数的方法，而假设检验除以上两种方法外，还可以使用假设检验工具。1. 用 EXCEL 进行区间估计【例 5】 某饭店在 7 星期内抽查 49 位顾客的消费额（元）如下：15 24 38 26 30 42 18 30 25 26 34 44 20 35 24 26 34 48 18 28 46 19

14、 30 36 42 24 32 45 36 21 47 26 28 31 42 45 36 24 28 27 32 36 47 53 22 24 32 46 26求在概率 90%的保证下，顾客平均消费额的估计区间，如图 1-16 所示。10图 1-16 参数估计数据及结果计算方法如下：第一步：把数据输入到 A2： A50 单元格。第二步：在 C2 中输入公式“ =COUNT（A2：A50 ） ”，C3 中输入“=AVERAGE（A2：A50） ”，在 C4 中输入“STDEV（A2：A50） ”，在 C5 中输入“=C4/SQRT（C2） ”，在 C6 中输入 0.90，在 C7 中输入“ =

15、C2-1”，在 C8 中输入“=TINV（1-C6，C7） ”，在 C9 中输入“=C8*C5” ，在 C10 中输入“=C3-C9” ，在 C11 中输入“=C3+C9” 。在输入每一个公式回车后，便可得到上面的结果，从上面的结果我们可以知道，顾客平均消费额的置信下限为 29.73536，置信上限为 34.26464。关于总体方差的估计、总体比例的估计等可按类似方法进行。2. 用 EXCEL 进行假设检验用 EXCEL 函数和公式进行假设检验与参数估计类似，大家可以自已做一下，这里介绍一下假设检验工具的使用。在 EXCEL 中，假设检验工具主要有四个，如图 1-17 所示。图 1-17 数据

16、分析对话框平均值的成对二样本分析实际上指的是在总体方差已知的条件下两个样本均值之差的检验，准确的说应该是 Z 检验，双样本等方差检验是总体方差未知，但假定其相等的条件下进行的 t 检验，双样本异方差检验指的是总体方差未知，但假定其不等的条件下进行的 t检验，双样本平均差检验指的是配对样本的 t 检验。这里介绍一下 Z 检验。【例 6】 某厂铸造车间为提高缸体的耐磨性而试制了一种镍合金铸件以取代一种铜合金铸件，现从两种铸件中各抽一个样本进行硬度测试（表示耐磨性的一种考核指标）其结果如下：合镍铸件（X）72.069.574.070.571.872合铜铸件（Y）69.870.072.068.573.

17、070.011根据以往经验知硬度 21,NX， 2,Y，且 21，试在05.水平上比较镍合金铸件硬度有无显著提高。计算步骤如下：第一步：输入数据到工作表。第二步：单击工具菜单，选择数据分析选项，弹出对话框后，在其中选择双样本平均差分析，弹出对话框如图 1-18 所示。图 1-18 双样本平均差分析对话框第三步：按上图所示输入后，按确定按钮，得输出结果如图 1-19。图 1-19 双样本平均差分析结果在上面的结果中，我们可以根据 P 值进行判断，也可以根据统计量和临界值比较进行判断。如本例采用的是单尾检验，其单尾 P 值为 0.17，大于给定的显著性水平 0.05，所以应该接受原假设，即镍合金铸

18、件硬度没有明显提高；若用临界值判断，得出的结论是一样的，如本例 Z 值为 0.938194，小于临界值 1.644853，由于是右尾检验，所以也是接受原假设。实验四 用 EXCEL 进行相关与回归分析实验目的：用 EXCEL 进行相关与回归分析采用下面的例子进行相关和回归分析。【例 7】 10 个学生身高和体重的情况如下表，要求对身高和体重作相关和回归分析。学生 身高（公分） 体重（公斤）1212345678910171167177154169175163152172160535664495566524758501. 用 EXCEL 进行相关分析首先把有关数据输入 EXCEL 的单元格中，如图

19、 1-20 所示。用 EXCEL 进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。图 1-20 EXCEL 数据集（1）利用函数计算相关系数在 EXCEL 中，提供了两个计算两个变量之间相关系数的方法，CORREL 函数和PERSON 函数，这两个函数是等价的，这里介绍用 CORREL 函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择 CORREL，单击确定后，出现 CORREL 对话框。第二步：在 array1 中输入 B2：B11，在 array2 中输入 C2：C11 ，即可在对话框

20、下方显示出计算结果为 0.896，如图 1-21 所示。13图 1-21 CORREL 对话框及输入结果（2）用相关系数宏计算相关系数第一步：单击工具菜单，选择数据分析选项，在数据分析选项中选择相关系数，弹出相关系数对话框，如图 1-22 所示：图 1-22 相关系数对话框第二步：在输入区域输入$B$1:$C$1，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$1，单击确定，得输出结果如图 1-23 所示。图 1-23 相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为 1，身高和体重的相关系数为0.896，和用函数计算的结果完全相同。2. 用 EXCEL 进行回归分析

21、EXCEL 进行回归分析同样分函数和回归分析宏两种形式，其提供了 9 个函数用于建立回归模型和预测。这 9 个函数分别是：14INTERCEPT 返回线性回归模型的截距SLOPE 返回线性回归模型的斜率RSQ 返回线性回归模型的判定系数FORECAST 返回一元线性回归模型的预测值STEYX 计算估计的标准误TREND 计算线性回归线的趋势值GROWTH 返回指数曲线的趋势值LINEST 返回线性回归模型的参数LOGEST 返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分析。第一步：单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，

22、如图 1-24 所示。图 1-24 数据分析对话框第二步：单击确定按钮，弹出回归对话框，在 Y 值输入区域输入$B$2：$B$11，在 X值输入区域输入$C$2：$C$11，在输出选项选择新工作表组，如图 1-25 所示。图 1-25 回归对话框第三步：单击确定按钮，得回归分析结果如图 1-26 所示。15图 1-26 EXCEL 回归分析结果在上面的输出结果中，第一部分为汇总统计，MultipleR 指复相关系数，RSquare 指判定系数，Adjusted 指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df 指自由度， SS 指平方和，MS 指均方，F 指

23、 F 统计量，Significance of F 指 p 值；第三部分包括：Intercept 指截距，Coefficient 指系数，tstat 指 t 统计量。实验五 用 EXCEL 进行时间序列和指数分析实验目的：用 EXCEL 进行时间序列和指数分析季节因素是时间序列非常重要的一个成分，指数分析法是研究社会经济现象数量变动情况的一种统计分析法。在这一部分介绍如何用 EXCEL 进行季节指数的计算和指数分析。1. 用 EXCEL 计算季节指数【例 8】 煤炭集团 20052008 年煤炭销售量资料如下：单位：亿吨年度季度2005 2006 2007 20081 季度 4.78 5.18

24、6.46 6.822 季度 3.97 4.61 5.62 5.683 季度 5.07 5.69 6.96 7.384 季度 5.12 5.71 7.12 7.40试用按季平均法测定该数列的季节指数。所计算的季节指数，如图 1-27 所示。16图 1-27 用 EXCEL 计算季节指数及结果计算步骤：第一步：计算各行平均数：在 F2 中输入“=Average （B2:E2） ”，并用鼠标拖曳将公式复制到 F2：F4 区域。第二步：计算各列平均数：有 B6 中输入“=Average（B2:B5） ”，并用鼠标拖曳将公式复制到 B6：F6 区域。第三步：计算季节指数：在 G2 中输入“=F2/$G$

25、6” ，并用鼠标拖曳将公式复制到G2：G6 区域。这样最后一列得到的就是按照平均法测定的季节指数。注意：在输入公式的时候，不要忘记等号，否则就不会出现数值；也要注意单元格的绝对引用。2. 用 EXCEL 计算总指数【例 9】 图中是某企业甲、乙、丙三种产品的生产情况，以基期价格 p 作为同度量因素，计算生产量指数。如图 1-28 所示。图 1-28 用 EXCEL 计算总指数资料及结果计算步骤：第一步：计算各个 ：在 G2 中输入“=C2*D2 ”，并用鼠标拖曳将公式复制到0qpG2：G4 区域。第二步：计算各个 ：有 H2 中输入“=C2*F2 ”，并用鼠标拖曳将公式复制到10H2：H4 区

26、域。第三步：计算 和 ：选定 G2：G4 区域，单击工具栏上的“”按0qp1017钮，在 G5 出现该列的求和值。选定 H2：H4 区域，单击工具栏上的“”按钮，在 H5 出现该列的求和值。第四步：计算生产量综合指数 ：在 C6 中输入“=H5/G5”010qpIq便可得到生产量综合指数注意：在输入公式的时候，不要忘记等号，否则就不会出现数值。3. 用 EXCEL 计算平均指数现以生产量平均指数为例，说明加权算术平均法的计算方法。【例 10】 图中的 A1：A4 区域内是某企业生产情况的统计资料，我们要以基期总成本为同度量因素，计算生产量平均指数，如图 1-29 所示。图 1-29 用 EXCEL 计算平均指数资料及结果计算步骤：第一步：计算个体指数 ：在 F2 中输入“=D2/C2” 。并用鼠标拖曳将公式复01qk制到 F2：F4 区域。第二步：计算 并求和。在 G2 中输入“=F2*E2”并用鼠标拖曳将公式复制0p到 G2：G4 区域。选定 G2： G4 区域，单击工具栏上的：“”按钮，在 G5 列出现该列的求和值。第三步：计算生产量平均指数：在 C7 中输入“=G5/E5”即得到所求的值。