统计学实验讲义 - 图文

数学与统计 学院

本科教学实验讲义

（实验）课程名称：统计学

贵州财经学院教务处制

2010 年 10月

目 录

实验项目一 EXCEL的基本功能和在描述统计学中的应用 ..................................................... 4

一、实验内容、目标及要求 ................................................................................................... 4

（一）实验内容 ............................................................................................................... 4 （二）实验目标 ............................................................................................................... 4 （三）实验要求 ............................................................................................................... 4 二、实验准备 ........................................................................................................................... 4

（一）运行环境说明 ....................................................................................................... 4 （二）基础数据设置说明 ............................................................................................... 4 三、实验基本操作流程及说明 ............................................................................................... 5

1、Excel数据收集........................................................................................................... 5 2、用Excel整理数据...................................................................................................... 7 3、用Excel作统计图.................................................................................................... 12 4、用Excel计算描述统计量 ........................................................................................ 15 四、验测评与考核 ................................................................................................................. 19 实验项目二 Excel进行统计推断 ............................................................................................... 20

一、实验内容、目标及要求 ................................................................................................. 20

（一） 实验内容......................................................................................................... 20 （二）实验目标 ............................................................................................................. 20 （三）实验要求 ............................................................................................................. 20 二、实验准备 ......................................................................................................................... 20

（一）运行环境说明 ..................................................................................................... 20 （二）基础数据设置说明 ............................................................................................. 20 三、实验基本操作流程及说明 ............................................................................................. 20

（一）系统界面及说明 ................................................................................................. 20 （二）操作步骤 ............................................................................................................. 21

1、估计总体均值 ................................................................................................... 21 2、假设检验 ........................................................................................................... 25

四、实验测评与考核 ............................................................................................................. 29 实验项目三 Excel进行相关分析和回归分析 ........................................................................... 30

一、实验内容、目标及要求 ................................................................................................. 30

（一） 实验内容......................................................................................................... 30 （二）实验目标 ............................................................................................................. 30 （三）实验要求 ............................................................................................................. 30 二、实验准备 ......................................................................................................................... 30

（一）运行环境说明 ..................................................................................................... 30 （二）基础数据设置说明 ............................................................................................. 30 三、实验基本操作流程及说明 ............................................................................................. 31

（一）系统界面及说明 ................................................................................................. 31 （二）操作步骤 ............................................................................................................. 31 1、用Excel进行相关分析 ............................................................................................ 31

1．利用函数计算相关系数 ................................................................................... 31 2．用相关系数宏计算相关系数 ........................................................................... 32

2

2、用Excel进行回归分析 ............................................................................................ 33 四、实验测评与考核 ............................................................................................................. 35 实验项目四 Excel在时间序列分析和统计指数中的应用 ....................................................... 36

一、 实验内容、目标及要求 ......................................................................................... 36

（一）实验内容 ............................................................................................................. 36 （二）实验目标 ............................................................................................................. 36 （三）实验要求 ............................................................................................................. 36 二、实验准备 ......................................................................................................................... 36

（一）运行环境说明 ..................................................................................................... 36 （二）基础数据设置说明 ............................................................................................. 36 三、实验基本操作流程及说明 ............................................................................................. 37

（一）系统界面及说明 ................................................................................................. 37 （二）操作步骤 ............................................................................................................. 37

一、测定增长量和平均增长量 ............................................................................. 37 二、测定发展速度和平均发展速度 ..................................................................... 37 三、计算长期趋势 ................................................................................................. 38 四、计算季节变动 ................................................................................................. 40 五、用Excel进行指数分析 .................................................................................. 44 六、用Excel计算平均指数 .................................................................................. 44

四、实验测评与考核 ............................................................................................................. 46

3

实验项目一 EXCEL的基本功能和在描述

统计学中的应用

一、实验内容、目标及要求 （一）实验内容

（1）数据的输入、复制、移动、删除等；

（2）利用EXCEL进行计算平均值、方差、协方差、标准误差、中值、众数、最大/最小值、相关系数、相关性检验值、极差、样本总和、样本个数、置信区间、百分比排位、四分位数的方法；

（3）熟练掌握Excel绘制条形图、柱形图、散点图、饼图等标准的常用形式图表，以及用户可定义的多种形式图表。

（二）实验目标

使学生掌握EXCEL的基本功能和在描述统计学中的应用，通过实例分析，培养学生的动手操作能力，达到理论知识与实践相结合的目的

（三）实验要求

要求熟练应用计算机软件进行描述统计分析。

二、实验准备 （一）运行环境说明

电脑操作系统为Windows XP或更高版本，所需软件为Excel 2003或更高版本。

（二）基础数据设置说明

将数据正确输入Excel，设置相应的参数值。

三、实验基本操作流程及说明 1、Excel数据收集

搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查，以后我国的统计调查将以抽样为主。针对抽样调查，Excel的数据分析工具中提供了一个专门的“抽样”工具，可以帮助使用者快速完成抽样工作。

使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，本节以下例为操作范例进行阐述。

例：假定有100名消费者，从中选取15人进行工资收入调查。

首先，将100名消费者编号为1~100号，输入编号，形成总体单位编号表如图1所示。

图1

总体各单位编号表

完成总体单位编号清单后，利用“抽样”工具进行抽样的具体操作如下：

第一步：单击“工具”菜单，选择“数据分析”选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项）。

第二步：打开“数据分析”对话框，从其对话框的“分析工具”列表中选择“抽样”选项，如图2所示。

图2 “数据分析”对话框

第三步：单击“确定”按钮，打开“抽样”对话框，确定输入区域、抽样方法和输出区

5

域，如图3所示。

图3 “抽样”对话框

1．确定输入区域

在“抽样”对话框的“输入区域”框中输入总体单位编号所在的单元格区域。在本例中，输入区域为$A$1:$J$10。输入区域有两种方法：一是用手工逐字录入；二是用鼠标左键单击图1中的$A$1位置，出现虚线框，然后拖拉虚线框，选中表中全部数字，自动在图3中的“输入区域”显示出$A$1:$J$10。系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击“标志”选框。

2．选定抽样方法

在“抽样方法”框中，有“周期”和“随机”两种抽样模式。 （1）“周期”模式即所谓的等距抽样、机械抽样或系统抽样。此种抽样方法，需要确定周期间隔，周期间隔由总体单位数除以要抽取的样本数而求得。本例中，要在100个总体单位中抽取15个，则在“间隔”框中输入6。

（2）“随机”模式适用于简单随机抽样、分层抽样、整群抽样和阶段抽样。

简单随机抽样即纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可，本例为15。

分类抽样即类型抽样，先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合。

整群抽样，先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，本例所使用的编号输入方法，只适用于等距抽样和简单随机抽样。 3．指定输出方向

在“输出选项”框中有三种输出去向。在“输出区域”框中输入总体单位编号所在的单元格区域。在本例中，输入区域为$A$12。也可以通过选择“新工作表”或“新工作簿”将抽样结果放在新工作表或新工作薄中。

第四步：单击“确定”按钮后，在指定的位置给出抽样的结果。如图4所示。

6

图4 等距抽样结果2

2、用Excel整理数据

通过统计调查得到的数据是杂乱的，没有规则的，因此，必须对搜集到的大量的原始数据加工整理，经过数据分析得到科学结论。统计整理包括对数据进行分类汇总并计算各类指标及利用统计图或统计表描述统计汇总结果等。Excel提供了多种数据整理工具：频数分布函数与直方图分析工具等。

例 根据抽样调查，某月某市50户居民购买消费品支出资料如图5所示。（单位：元）

图5 居民购买消费品月支出

对其按800~900、900~1000、1000~1100、1100~1200、1200~1300、1300~1400、1400~1500、1500~1600、1600以上分为9个组。 1．频数分布函数

7

Excel提供了一个专门用于统计分组的频数分布函数（FREQUENCY），它以一列垂直数组返回某个区域中的数据分布，描述数据分布状态。用频数分布函数进行统计分组的操作过程如下。

首先，在使用此函数时，先将样本数据排成一列，本例中为A1：A50。 然后，利用频数分布函数进行统计分组和计算频数，具体操作步骤如下：

第一步：选定单元格区域，本例中选定的区域为D3：D11，单击“插入”菜单，选择“函数”选项，弹出“插入函数”对话框，如图6所示。

图6 插入函数对话框

在“选择类别”中选择“统计”，在“选择函数”中选择“FREQUENCY”。如图7所示：

图7 选择“FREQUENCY”对话框

第二步：打开“FREQUENCY”对话框，输入待分组数据与分组标志，如图8所示。

8

图8 “FREQUENCY”对话框

在“FREQUENCY”对话框中有“Data_array”和“Bins_array”两个文本框。

（1）Data_array用于计算频率的数组，或对数组单元区域的引用。本例中为A1：A50。 （2）Bins_array数据接受区间，为一组数或对数组区间的引用，设定对Data_array进行频率计算的分段点。本例中为899、999、1099、1199、1299、1399、1499、1599、1699。 注意：频数分布函数要求按组距的上限分组，不接受非数值的分组标志（如“不足××”或“××以上”等）。在输入的数据两端必须加大括号，各数据之间用分号隔开。输入完成后，由于频数分布是数组操作，所以不能单击“确定”按钮。

第三步：按“Ctrl+Shift+Enter” 组合键，在最初选定单元格区域内得到频数分布结果，在本例中为D3：D11，如图9所示。

图9 频数分布结果

至此，频数分布函数进行统计分析的功能就全部操作完成了。 2．直方图分析工具

与频数分布函数只能进行统计分组和频数计算相比，直方图分析工具可完成数据的分组、频数分布与累积频数的计、绘制直方图与累积折线图等一系列操作。仍以例2为操作范例，阐述直方图分析工具的统计整理功能，其操作过程如下。

9

首先，将样本数据排成一列，最好对数据进行排序，本例中已利用排序操作排好序，为A1：A51。输入分组标志，本例中为B1：B10，分别是899、999、1099、1199、1299、1399、1499、1599、1699。如图10所示：

图10 数据升序排列

然后 ，利用直方图分析工具进行分析，具体操作步骤如下。

第一步：单击“工具”菜单，选择“数据分析”选项；打开“数据分析”对话框，从“分析工具”列表中选择“直方图”选项，如图11所示。

图11 “数据分析”对话框

第二步：打开“直方图”对话框，确定输入区域、接收区域和输出区域，如图12所示。

10

图12 “直方图”对话框

（1）“输入区域”输入待分析数据区域的单元格引用，若输入区域有标志项，则选中“标志”复选框；否则，系统自动生成数据标志。“接收区域”输入接收区域的单元格引用，该框可为空，则系统自动利用输入区域中的最小值和最大值建立平均分布的区间间隔的分组。本例中输入区域为$A$2:$A$51，接收区域为$B$2:$B$10。

（2）在“输出”选项中可选择输出去向，输出去向类似于“抽样”对话框的输出去向。本例中选择“输出区域”为$C$1。

（3）选择“柏拉图”可以在输出表中同时按降序排列频数数据；选择“累积百分率”可在输出表中增加一列累积百分比数值，并绘制一条百分比曲线；选择“图表输出”可生成一个嵌入式直方图。

第三步：单击“确定”按钮，在输出区域单元格可得到频数分布，如图13所示。

图13 直方图频数分布结果

第四步：将条形图转换成标准直方图，如图14所示。具体做法：左键单击条形图的任一直条，再单击右键，在快捷菜单中选取“数据系列格式”，然后在“数据系列格式”对话框中选择选项标签，将间距宽度改为0，单击“确定”按钮即可。

11

图14 标准直方图

3、用Excel作统计图

传统的统计表格需要数据使用者自己精心地进行分析，而统计图显示资料则具有形象生动、一目了然的优点，通过图形可以方便地观察到数量之间的对比关系、总体的结构特征以及变化发展趋势。统计图在统计整理中的应用越来越广泛。Excel提供了大量的统计图形供用户根据需要和图形功能选择使用。Excel提供的图形工具有：柱形图、折线图、饼图、散点图、面积图、环形图、股价图等。各种图的作法大同小异，本章以柱形图为，介绍制作统计图的工作步骤。

例 根据我国1991-2003年国内生产总值构成数据制作相应的统计图。 第一步：创建工作表，即将统计资料输入到Excel中，如图15所示。

12

图15 1991-2003年国内生产总值构成数据

第二步：在想绘制图形的数据中选择任一单元格，然后在“插入”菜单中选择“图表”选项，或单击工具栏中的图表向导按钮，出现图16。

图16 “图表向导-4步骤之1-图表类型”对话框

第三步：选定图表类型。在弹出的“图表向导-4步骤之1-图表类型”对话框中选择所需要的图表的类型，此处在“图表类型”中选“柱形图”，在“子图表类型”中选第一个“簇状柱形图”，然后单击“下一步”按钮，如图17所示。

13

图17 “图表向导-4步骤之2-图表源数据”对话框

第四步：确定数据范围。在弹出的“图表向导-4步骤之2-图表源数据”对话框中规定数据区域。点击“系列”选项，规定每一数据系列的名字和数值的区域，如图18所示。然后单击“下一步”按钮。

图18 “图表向导-4步骤之2-图表源数据”数据区域选项

第五步：选用图表选项。在弹出的“图表向导-4步骤之3-图表选项”对话框中可给图表命名，将“分类（X）轴”命名为“年份”；将“数值（Y）轴”命名为“百分比（%）”。然后，单击“下一步”按钮，如图19年示。

图19 “图表向导-4步骤之3-图表选项”对话框

第六步：选择图表位置并显示结果。在弹出的“图表向导-4步骤之4图表位置”对话框中可为图表选择保存位置，或放在独立的工作表中，或作为一个对象放在当前工作表中。然后，单击“完成”按钮。本例中选择了放在独立的工作表中，如图20所示。

14

图20 1991-2003年国内生产总值构成图表

其他图形的绘制步骤与柱形图的绘制步骤类似，只是在图形类型中选择不同的图形类型即可。

4、用Excel计算描述统计量

Excel中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。

(1)用函数计算描述统计量

常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。一般来说，在Excel中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数来计算未分组资料的描述统计量。

用函数运算有两种方法：一是手工输入函数名称及参数。这种输入形式比较简单、快捷。但需要非常熟悉函数名称及其参数的输入形式。所以，只有比较简单的函数才用这种方法输入；二是函数导入法。这是一种最为常用的办法，它适合于所有函数的使用，而且在导入过程中有向导提示，因而非常方便。函数的一般导入过程为：点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别（如常用函数或统计）；在“选择函数”内确定欲选的函数名称，如SUM、MODE等；点“确定”后即可出现该函数运算的对话框向导，再按向导的提示往下运行即可。

下面介绍统计中常用指标的函数运算方法。 1．众数

例 为了解某单位职工的工资收入情况，随机抽取30人月工资如下：

15

图21 职工月工资收入

用函数方法求众数，应先将30个人的工资数据输入A1：A30单元格。 （1）手工输入函数名称及参数

单击任一单元格，输入“=MODE（A1：A30）”，回车后即可得众数为1560。如图22所示。

图22 手工输入函数名称及参数

（2）函数导入法 点菜单“插入”；找“函数”，此时出现一个“插入函数”对话框；在对话框的“选择类别”中确定函数的类别“统计”；在“选择函数”内确定欲选的函数名称“MODE”，如图23所示。

16

图22 函数导入法“插入函数”对话框

点“确定”后即可出现该函数运算的对话框向导，在Number1处输入A1：A30或选择Excel中的A1：A30区域，如图23所示。按“确定”，在Excel中即得到众数1560。

图23 函数导入法“函数参数”对话框

运用函数导入法，只要知道每个函数的涵义，即可按上述程序得到相应的运算结果，为节约篇幅这里不再一一讲解，下面仅列示各函数的涵义及本例中的运行结果。 2．中位数

单击任一空白单元格，输入“=MEDIAN（A1：A30）”，回车后得中位数为1550。 3．算术平均数

单击任一空白单元格，输入“=AVERAGE（A1：A30）”，回车后得算术平均数为1531.666667。 4．几何平均数

单击任一空白单元格，输入“=GEOMEAN（A1：A30）”，回车后得几何平均数为1526.3。 5．调和平均数

单击任一空白单元格，输入“=HARMEAN（A1：A30）”，回车后得调和平均数为1521.06。 6．截尾平均数

将数据按由小到大顺序排列后，因数据两端值不够稳定，按一定比例去掉头尾两端一

17

定数量的观察值，然后再求平均，这样得到的平均数就是截尾平均数。如果按6/30，即从30个数据中去掉最大的三个值和最小的三个值，再求平均数。

单击任一空白单元格，输入“=TRIMEAN（A1：A30，6/30）”，回车后得截尾平均数为1526.25 7．全距

单击任一空白单元格，输入“=MAX（A1：A30）-MIN（A1：A30）”，回车后得全距为730。 8．标准差

单击任一空白单元格，输入“=STDEV（A1：A30）”，回车后得标准差为132.5371。 9．标准差系数

单击任一空白单元格，输入“=STDEV（A1：A30）/AVERAGE（A1：A30）”，回车后得标准差系数为0．086531 10．偏度系数

单击任一空白单元格，输入“=SKEW（A1：A30）”，回车后得偏度系数为0.914565。 11．峰度系数

单击任一空白单元格，输入“=KURT（A1：A30）”，回车后得峰度系数为3.808279。 (2) 描述统计菜单项的使用

仍使用上面的例子，我们已经把数据输入到A1：A30单元格，然后按以下步骤操作： 第一步：在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图24所示。

图24 描述统计对话框

第二步：在输入区域中输入$A$1:$A$30，在输出区域中选择$C$13，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。

第三步：单击确定，可得输出结果，如图25所示。

18

图25 描述统计输出结果

上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距；可以看出与我们前面用函数计算的结果完全相同。最大值为1980，最小值为1250，第三个最大值为1690，第三个最小值为1350。

四、验测评与考核

本章实验要求学生结合实际案例撰写实验报告，要求实验报告要包含所要解决的问题及其对结果的分析。

19

实验项目二 Excel进行统计推断

一、实验内容、目标及要求 （一） 实验内容

（1）利用Excel进行正态分布的概率计算； （2）利用Excel进行区间估计 （3）利用Excel进行假设检验 （4）利用Excel进行方差分析

（二）实验目标

要求学生熟练利用Excel进行统计推断，通过实例分析，培养学生的动手操作能力，达到理论知识与实践相结合的目的。

（三）实验要求

要求利用计算机软件进行Excel进行统计推断，提高实际应用能力。

二、实验准备 （一）运行环境说明

电脑操作系统为Windows XP或更高版本，所需软件为Excel 2003或更高版本。

（二）基础数据设置说明

同实验项目一。

三、实验基本操作流程及说明 （一）系统界面及说明

同实验项目一。

20

在本例中是双侧检验，所以最后的p值为0．010969536X 2<0．05，所以拒绝原假设。 见“投保年龄答案”表中的操作及答案。 (二)小样本条件下 1．z检验

应用案例5某工厂采用自动包装机分装产品，假定每包产品的重量服从正态分布，每包标准重量为800克。某日随机抽查10包，测得每包净重数据如下(单位：克)：

789、780、794、762、802、813、770、785、810、806。试在0．05的显著性水平下，检验当天自动包装机工作是否正常?并报告p值。

利用Excel对单一总体均值进行检验。具体步骤如下： 第1步：输入相关数据。

第2步：选择“工具”下拉菜单。 第3步：选择“数据分析”选项。

第4步：在分析工具中选择“描述统计”。

第5步：当对话框出现时，在“输入区域”设置框内键人数据单元格区域A1：A10，在“输出区域”设置框内键人数据单元格区域C1，并选择“汇总统计”然后选择“确定”，得到输出结果。此时样本均值出现在单元格D3中，抽样误差的值出现在单元格D4中(见 表7．4)。

第6步：计算统计量‘值。选择单元格A12，键人公式：(D3—800)／D4，即统计量t。因为原假设为H0：u=800，所以键人数据为800，然后选择“确定”。 由a=0．05，查表得临界值t(a，n-1)：t(o．05,9)=±2．262

由于单元格A12中的值|t|=1．64237<2．262，所以不能拒绝原假设。即可认为这天自动包装机工作正常。 2、p值法

在t值法的基础求出t值后，我们可以通过Excel的函数功能来求出P值。 具体做法如下：

第1步：选择“插入”下拉菜单。 第2步：选择“函数”选项。

第3步：当粘贴函数对话框出现时，在左侧函数分类窗口选择 “统计”，右侧函数名窗口选择“TDIST'’，然后选择“确定”。

第4步：当“TDIST'’对话框出现时，在X窗口输入t的绝对值1．64237(已在t值法中求出)，在Deg—freedom(自由度)窗口输人本例中的自由度9，在Tails窗口中输入2(表明是双侧检验，如果是单侧检验则输入1)，然后选择“确定”，得到p值(见表7．5)。

26

由于p值为0．134>0．05，所以不能拒绝原假设。 二、独立样本的两个总体均值差异的假设检验 (一)大样本条件下 在大样本条件下，进行独立样本的两个总体均值差异的假设检验时，我们可以用样本的方差来代替总体的方差。

应用案例6某汽车公司经理要比较A种与B种两种汽油的性能。选用同类型汽车，分两组各30辆，试开一周，甲队使用A种汽油，乙队使用B种汽油，记录下每辆汽车每加仑行驶的平均里程，数据如下(公里/加仑)。试按显著性水平o=0．05，判断两种汽油的公里/加仑指标有无明显差别。

下面我们以本例题：说明如何在大样本条件下，利用Excel对独立样本的两个总体均 值进行检验。具体步骤如下： 第1步：输人相关数据。

第2步：选择“工具”下拉菜单。 第3步：选择“数据分析”选项。

第4步：在分析工具中选择“z—检验：双样本平均差检验”。

第5步：当对话框出现时，在“变量1的区域(1)”设置框内键人数据单元格区域A1：A30，在“变量2的区域(2)”设置框内键人数据单元格区域B1：B30，在假设平均差设置框内键人0，在“变量1的方差(已知)”设置框内键人数据单元格区域本例题的方差值16，在“变量2的方差(已知)”设置框内键人数据单元格区域本例题的方差值12，在“a(A)”设置框内键人0．05，在“输出区域”设置框内键人数据单元格区域D1，然后选择“确定”，得到最终输出结果。见表7．6和表7．7。

27

检验统计量的值z=1．41463出现在单元格E8中。双边p—值= 0．15718出现在单元格Ell中。由于P值大于0．025，所以不能拒绝原假设。 (二)小样本条件下

以应用案例4(等方差情况下)，某生产车间为了比较改进生产工艺前后，工人组装产品的平均用时是否缩短，在改进生产工艺前后各抽取12名工人，调查得到他们某次组装产品的时间数据(单位：秒)如下：

假设改进生产工艺前后工人组装产品的时间均服从正态分布，且方差相等，试在0．05的显著性水平下检验改进生产工艺后工人的平均组装产品的时间是否比以前显著缩短? 说明如何在小样本条件下，利用Excel对独立样本的两个总体均值进行检验。 具体步骤如下：

第1步：输入相关数据。

28

第2步：选择“工具”下拉菜单。 第3步：选择“数据分析”选项。

第4步：在分析工具中选择“t—检验：双样本等方差检验”。

第5步：当对话框出现时，在“变量1的区域(1)”设置框内键人数据单元格区域A1：A12，在“变量2的区域(2)”设置框内键人数据单元格区域趴：B12，在假设平均差设置框内键人0，在“o(A)”设置框内键人0．05，在“输出区域”设置框内键人数据单元格区域D1，然后选择“确定”，得到最终输出结果。见表7．8和表7．9。

0．021158出现在单元格E11中。由于p值小于0．05，所以拒绝原假设。

注：若已知两总体方差不相等，我们只需在第4步中重新作出选择，即在分析工具中选择“t—检验：双样本异方差检验”。

三、配对样本的两个总体均值差异的假设检验 说明如何在小样本条件下，利用Excel对配对样本的两个总体均值进行检验。具体步骤如下： 第1步：输入相关数据。

第2步：选择“工具”下拉菜单。 第3步：选择“数据分析”选项。

第4步：在分析工具中选择“t—检验：平均值的成对二样本分析”。

第5步：当对话框出现时，在“变量1的区域(1)”设置框内键人数据单元格区域A1：A6，在“变量2的区域(2)”设置框内键人数据单元格区域趴：B6，在“假设平均差”设置框内键人0，在“a(A)”设置框内键人0．05，在“输出区域”设置框内键人数据单元格

四、实验测评与考核

本章实验要求学生结合实际案例撰写实验报告，要求实验报告要包含所要解决的问题及其对结果的分析。

29

实验项目三 Excel进行相关分析和回归分

析

一、 实验内容、目标及要求 （一） 实验内容

（1）利用Excel绘制数据的散点图 （2）利用Excel进行相关与回归分析 （3）利用Excel进行进行回归预测

（二）实验目标

要求学生利用Excel进行相关分析和回归分析，通过实例分析，培养学生的动手操作能力，达到理论知识与实践相结合的目的。

（三）实验要求

要求学生能熟练应用计算机软件进行相关分析和回归分析，并对结果进行分析，培养实际应用能力。

二、实验准备 （一）运行环境说明

同实验项目一。

（二）基础数据设置说明

同实验项目一。

30

三、实验基本操作流程及说明 （一）系统界面及说明

同实验一。

（二）操作步骤

例 10个学生身高和体重的情况如下：

首先把有关数据输入Excel的单元格中，如图39所示：

图39 Excel数据集

1、用Excel进行相关分析

用Excel进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。 1．利用函数计算相关系数

在Excel中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON 函数，这两个函数是等价的，这里我们介绍用CORREL函数计算相关系数。

第一步：单击任一个空白单元格，单击“插入”菜单，选择“函数”，打开“插入函数”对话框，在“选择类别”中选择“统计”；在“选择函数”中选择“CORREL”，单击确定后，出现CORREL对话框。

31

第二步：在array1中输入B2：B11，在array2中输入C2：C11，即可在对话框下方显示出计算结果为0.896028。如图40所示。

图40 CORREL对话框及输入结果

2．用相关系数宏计算相关系数

第一步：单击“工具”菜单，选择“数据分析”项，在“数据分析”项中选择“相关系数”，弹出“相关系数”对话框，如图41所示。

图41 相关系数对话框

第二步：在“输入区域”输入$B$1:$C$11，“分组方式”选择逐列，选择“标志位于第一行”，在“输出区域”中输入$E$1，单击“确定”，得输出结果如图42所示。

32

图42 相关分析输出结果

在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.896028，与用函数计算的结果完全相同。

2、用Excel进行回归分析

Excel进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测，这9个函数分别是：

1．INTERCEPT返回线性回归模型的截距 2．SLOPE返回线性回归模型的斜率 3．RSQ返回线性回归模型的判定系数

4．FORECAST返回一元线性回归模型的预测值 5．STEYX计算估计的标准误差

6．TREND计算线性回归线的趋势值 7．GROWTH返回指数曲线的趋势值 8．LINEST返回线性回归模型的参数 9．LOGEST返回指数曲线模型的参数

用函数进行回归分析比较麻烦，这里介绍使用回归分析宏进行回归分析。

第一步：单击“工具”菜单，选择“数据分析”选项，出现“数据分析”对话框，在分析工具中选择“回归”，如图43所示。

33

图43 相关系数对话框

第二步：单击“确定”按钮，弹出“回归”对话框，在“Y值输入区域”输入$B$1：$B$11；在“X值输入区域”输入$C$1:$C$11，在“输出选项”选择“新工作表组”，如图44所示。

图44 回归对话框

第四步：单击确定按钮，得回归分析结果如图45所示。

在输出结果中，第一部分为汇总统计，MultipleR指复相关系数，RSquare指判定系数，Adjusted指调整的判定系数，标准误差指估计的标准误差，观测值指样本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指F统计量，Singnificance of F指p值；第三部分包括：Intercept指截距，Coefficient指系数，t stat指t统计量。

34

图45 Excel回归分析结果

四、实验测评与考核

本章实验要求学生结合实际案例撰写实验报告，要求实验报告要包含所要解决的问题及其对结果的分析。

35

实验项目四 Excel在时间序列分析和统计

指数中的应用

一、 实验内容、目标及要求 （一）实验内容

（1）利用Excel进行时间序列的水平分析和速度分析

（2）利用Excel对时间序列长期趋势、季节变动和循环变动进行计算分析

（3）利用Excel求解综合指数和平均指数，并能借助于软件对指数体系进行因素分析

（二）实验目标

要求学生系统掌握Excel在时间序列分析和统计指数中的应用，通过实例分析，培养学生的动手操作能力，达到理论知识与实践相结合的目的。

（三）实验要求

要求学生能熟练应用计算机软件进行时间序列分析和计算统计指数，培养实际应用能力。

二、实验准备 （一）运行环境说明

同实验项目一。

（二）基础数据设置说明

同实验项目一。

36

三、实验基本操作流程及说明 （一）系统界面及说明

同实验项目一。

（二）操作步骤

一、测定增长量和平均增长量

例 根据我国1998年~2003年社会消费品零售总额，计算逐期增长量、累计增长量和平均增长量。如图26所示。

图26 用Excel计算增长量和平均增长量资料及结果

计算步骤如下：

第一步：在A列输入年份，在B列输入社会消费品零售总额。 第二步：计算逐期增长量，在C3中输入公式：“=B3-B2”，并用鼠标拖曳将公式复制到C3：C7区域。

第三步：计算累计增长量，在D3中输入公式：“=B3-$B$2”，并用鼠标拖曳将公式复制到D3：D7区域。

第四步：计算平均增长量（水平法），在C10中输入公式：“=（B7-B2）/5”，按回车键，即可得以平均增长量。

二、测定发展速度和平均发展速度

例 以1998年~2003年社会消费品零售总额为例，说明如何计算定基发展速度、环比发展速度和平均发展速度。如图27所示。

37

图27 用Excel计算发展速度和平均发展速度资料及结果

第一步：在A列输入年份，在B列输入社会消费品零售总额。 第二步：计算定基发展速度，在C3中输入公式：“=B3/$B$2”，并用鼠标拖曳将公式复制到C3：C7区域。

第三步：计算环比发展速度，在D3中输入公式：“=B3/B2”，并用鼠标拖曳将公式复制到D3：D7区域。

第四步：计算平均发展速度（水平法），选中C9单元格，单击插入菜单，选中函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D8即可。

三、计算长期趋势

影响时间数列各项数值变动的因素是多方面的，主要有4种：长期趋势（T）、季节变动（S）、循环变动（C）、不规则变动（I）。本书主要就长期趋势与季节变动进行分析。以直线趋势说明长期趋势的测定与预测方法。而测定直线趋势的方法主要采用移动平均法。

移动平均法按一定的间隔逐期移动，计算一系列动态平均数，从而形成一个由动态平均数组成的新的时间数列，修匀原时间数列，显示出长期趋势。在Excel中，使用移动平均法测定长期趋势，可以利用公式或AVERAGE函数，也可利用Excel提供的“移动平均”工具。由于公式或函数方法前面已讲过，而且只能获得数据，不能直接获得长期趋势图，因此长期趋势主要以“移动平均”工具来计算分析。

例 根据我国1990~2003年的国内生产总值的数据（单位：亿元），如图28所示，用移动平均法计算预测我国国内生产总值的长期发展趋势。

38

图28 1990~2003年国内生产总值数据表

第一步：单击“工具”菜单，选择“数据分析”选项。打开“数据分析”对话框，从其“分析工具”列表中选择“移动平均”选项，单击“确定”按钮，打开“移动平均”对话框。图29所示。

图29 “移动平均”对话框

第二步：确定输入区域和输出区域，选中“图表输出”复选框。如图29所示。 第三步：单击“确定”按钮后，在指定位置给出移动平均计算结果，如图30所示。

39

图30 移动平均法计算结果

四、计算季节变动

季节变动是指在一年以内，受自然季节和社会习俗等因素影响而发生的有规律的、周期性的变动。测定季节变动的方法有两种：按月（季）平均法和移动平均趋势剔除法。 1、按月平均法

例 某啤酒厂近五年全年及分月啤酒销售量数据如图31所示。结合五年分月数据，利用Excel按月平均法测定季节变动。

图31 某酒厂近五年全年及分月啤酒销售量

第一步：按已知数据资料列出计算表，将各年同月的数值列在同一列内。

第二步：计算各年合计与各年同月数值之和。计算每年的啤酒销量总数：单击N3单元格，输入“=SUM（B3：M3）”，并用鼠标拖曳将公式复制到N4：N7区域，得各年销量总数；计算各年同月销售总数：单击B8单元格，输入“=SUM（B3：B7）”，并用鼠标拖曳将公式复制到C8：N8区域，得各年同月销量总数与全部销量之和。

第三步：计算同月平均数与总的月平均数。计算同月平均数：单击B9单元格，输入

40

“=B8/5”，并用鼠标拖曳将公式复制到C9：M9区域；计算总的月平均数：单击N9单元格，输入“=N8/60”回车得结果为43.21667。

第四步：计算季节比率。单击B10单元格，输入“=B9*100/43.21667”，并用鼠标拖曳将公式复制到C10：M10区域。 第五步：计算季节比率之和，绘制季节变动曲线。单击N10单元格，输入“=SUM（B10：M10）”，回车得季节比率之和为1200。根据季节比率，可绘制季节变动曲线。

图32 按月平均法分析季节变动数据图

2．移动平均趋势剔除法。

直接用按月平均法忽略了长期趋势的影响，因此得出的季节比率不够精确。为了弥补这一缺点，可以采用移动平均趋势剔除法来测定季节变动。利用移动平均趋势剔除法分析季节变动有两种方法：乘法型时间数列季节变动分析和加法型时间数列季节变动分析。下面仍以上例的资料，采用乘法型时间数列变动分析说明移动趋势剔除法的操作方法。 第一步：输入各年季度数据资料，如图33所示。

41

图33 某酒厂近五年全年及分季啤酒销售量

第二步：计算四个季度的移动平均数。计算移动平均数，可以采用移动平均工具，也可以使用公式与函数。移动平均工具在前面内容中已讲过，本例采用公式与函数方法来计算。单击D4单元格，输入“=AVERAGE（C2：C5）”，并用鼠标拖曳将公式复制到D5：D20区域。

第三步：移正平均。因为本例是偶数项移动平均，所以还需将四项移动平均值再进行两项“移正”平均，如果是奇数项移动平均，则该步骤省去。单击E4单元格，输入“=AVERAGE（D4：D5）”，并用鼠标拖曳将公式复制到E5：E19区域。

第四步：消除长期趋势。本例采用乘法模型，因此，将原数列除以趋势值以消除长期趋势。单击F4单元格，输入“=C4*100/E4”，并用鼠标拖曳将公式复制到F5：F19区域。

42

图34 移动平均及剔除趋势数据

第五步：计算季节比率。将图34中Y/T*100得到的数据重新编排，得到如图35中左边数据表中前五行的基本数据（本例将图34中Y/T*100的数据四舍五入，保留两位小数）。然后利用按月平均法计算季节比率，具体步骤参见月平均法，本例省略，最终结果如图35所示。

图35 利用移动平均趋势剔除法分析季节变动数据图

43

五、用Excel进行指数分析

指数分析法是研究社会经济现象数量变动情况的一种统计分析法。指数有总指数与平均指数之分，在这一节我们介绍如何用Excel进行指数分析与因素分析。

一、用Excel计算总指数

例 图中是某企业甲、乙、丙三种产品的生产情况，以基期价格P作为同度量因素，计算生产量指数。如图36所示。

图36 用Excel计算总指数资料及结果

计算步骤：

第一步：计算各个p0q0：在G2中输入“=C2*D2”，并用鼠标拖曳将公式复制到G2：G4区域。

第二步：计算各个p0q1：在H2中输入“=C2*F2”，并用鼠标拖曳将公式复制到H2：H4区域。

第三步：计算∑p0q0和∑p0q1：选定G2：G4区域，单击工具栏上的“∑”按钮，在G5出现该列的求和值。选定H2：H4区域，单击工具栏上的“∑”按钮，在H5出现该列的求和值。

第四步：计算生产量综合指数Iq=∑p0q1/∑p0q0：在C6中输入“=H5/G5”便可得到生产量综合指数。

六、用Excel计算平均指数

现以生产量平均指数为例，说明加权算术平均法的计算方法。 例 图中的A1：A4区域内是某企业生产情况的统计资料，我们要以基期总成本为同度量因素，计算生产量平均指数。如图37所示。

44

图37 用Excel计算平均指数资料及结果

计算步骤：

第一步：计算个体指数k=q1/q0：在F2中输入“=D2/C2”，并用鼠标拖曳将公式复制到F2：F4区域。

第二步：计算k* p0q0并求和。在G2中输入“=F2*E2”，并用鼠标拖曳将公式复制到G2：G4区域。选定G2：G4区域，单击工具栏上的“∑”按钮，在G5列出现该列的求和值。

第三步：计算生产量平均指数：在C6中输入“=G5/E5”即得到所求的值。

三、用Excel进行因素分析

例 资料同例10，有关资料及运算结果如图38所示，进行因素分析的步骤如下： 第一步：计算各个p0q0和∑p0q0：在G2中输入“=C2*D2”，并用鼠标拖曳将公式复制到G2：G4区域。选定G2：G4区域，单击工具栏上的“∑”按钮，在G5出现该列的求和值。

第二步：计算各个p0q1和∑p0q1：在H2中输入“=C2*F2”，并用鼠标拖曳将公式复制到H2：H4区域。选定H2：H6区域，单击工具栏上的“∑”按钮，在H5出现该列的求和值。

45

图38 用Excel进行因素分析资料及结果

第三步：计算各个p1q1和∑p1q1：在I2中输入“=E2*F2”，并用鼠标拖曳将公式复制到I2：I4区域。选定I2：I4区域，单击工具栏上的“∑”按钮，在I5出现该列的求和值。

第四步：计算总成本指数：在C6中输入“=I5/G5”，即求得总成本指数。 第五步：计算产量指数：在C7中输入“=H5/G5”，即得产量指数。

第六步：计算单位成本指数：在C8中输入“=I5/H5”即求得单位成本指数。

四、实验测评与考核

本章实验要求学生结合实际案例撰写实验报告，要求实验报告要包含所要解决的问题及其对结果的分析。

46

图38 用Excel进行因素分析资料及结果

第三步：计算各个p1q1和∑p1q1：在I2中输入“=E2*F2”，并用鼠标拖曳将公式复制到I2：I4区域。选定I2：I4区域，单击工具栏上的“∑”按钮，在I5出现该列的求和值。

第四步：计算总成本指数：在C6中输入“=I5/G5”，即求得总成本指数。 第五步：计算产量指数：在C7中输入“=H5/G5”，即得产量指数。

第六步：计算单位成本指数：在C8中输入“=I5/H5”即求得单位成本指数。

四、实验测评与考核

本章实验要求学生结合实际案例撰写实验报告，要求实验报告要包含所要解决的问题及其对结果的分析。

46

本文来源：https://www.bwwdw.com/article/5bwr.html