应用统计学实验指导书EXCEL.docx

资源描述

应用统计学实验指导书EXCEL.docx

《应用统计学实验指导书EXCEL.docx》由会员分享，可在线阅读，更多相关《应用统计学实验指导书EXCEL.docx（42页珍藏版）》请在冰豆网上搜索。

应用统计学实验指导书EXCEL.docx

应用统计学实验指导书EXCEL

EXCEL实验报告

实验一利用Excel对数据进行描述统计分析2

实验二Excel图表制作及编辑13

实验三Excel区间估计24

实验四一元线性性回归模型26

实验五长期趋势分析29

实验六Excel综合指数计算实验36

实验七方差分析39

实验一利用Excel对数据进行描述统计分析

一、实验目的

1、掌握Excel的基本知识

2、学会应用Excel创建表格，输入和编辑信息

3、熟练运用excel的公式和函数求各种统计指标

4、利用Excel的分析工具对数据进行描述性统计

5、掌握组距式变量数列的编制原理

6、用EXCEL进行数据抽样

二、实验要求

1、掌握Excel的基本操作方法

2、通过练习，能够独立运用Excel进行数据整理和数据分析

3、掌握利用Excel对数据进行分组编制的基本操作方法；

4、掌握利用EXCEL对数据进行抽样的方法。

三、实验内容

1、分别用函数和数据分析工具计算这31个地区人口的总和、平均值、中位数、众数、标准差。

表1-12008年全国各地区人口统计

地区

总人口（万人）

地区

总人口（万人）

北京

1695

山东

9417

天津

1176

河南

9429

河北

6989

湖北

5711

山西

3411

湖南

6380

内蒙

2414

广东

9544

辽宁

4315

广西

4816

吉林

2734

海南

854

黑龙江

3825

重庆

2839

上海

1888

四川

8138

江苏

7677

贵州

3793

浙江

5120

云南

4543

安徽

6135

西藏

287

福建

3604

陕西

3762

青海

554

宁夏

618

新疆

2131

甘肃

2628

江西

4400

2、根据抽样调查，某月X市50户居民购买消费品支出资料如下（单位：

元）：

表1-2某月X市50户居民购买消费品支出

830

880

1230

1100

1180

1580

1210

1460

1170

1080

1050

1100

1070

1370

1200

1630

1250

1360

1270

1420

1180

1030

870

1150

1410

1170

1230

1260

1380

1510

1010

860

810

1130

1140

1190

1260

1350

930

1420

1080

1010

1050

1250

1160

1320

1380

1310

1270

1250

根据以上数据，以900、1000、1100、1200、1300、1400、1500、1600为组限，对居民户月消费支出额编制组距式变量数列，并计算居民户月消费支出额的累计频数和频率。

同时对分组资料用各种统计图的形式来加以表现。

3、假定有100个总体单位，每个总体单位给一个编号，共有从1到100个编号，从这些数据中抽取一定的样本数据进行分析。

四、实验步骤

1．用EXCEL函数计算这31个地区人口的总和、平均值、中位数、众数、标准差

（1）如图1.1所示，在工作表中输入数据。

（2）熟悉统计常用函数及其功能

函数

功能

函数

功能

函数

功能

COUNT

记数

NORMSINV

Z值计算

MEDIAN

中位数

MAX

最大值

ABS

绝对值

MODE

众数

MIN

最小值

CHIINV

卡方值

QUARTILE

四分位数

SUM

求和

TINV

T值计算

VAR

方差

SUMSQ

平方和

FINV

F值计算

STDEV

标准差

AVERAGE

平均值

SQRT

平方根

GEOMEAN

几何平均数

（3）使用函数进行统计计算。

（以求数据的总和为例）

1选定E2单元格，单击工具栏上的“插入——函数”命令，出现下图1.2所示对话框，在“选择类别”后的对话框中选择“全部”，在“选择函数”中选择求和函数“SUM”，然后点击确定。

②将鼠标置于“Mumber1”右段的编辑框内，在工作表中选择B2：

B32的区域，单击“确定”按钮，如下图1.3所示。

③则在E2单元格中显示数据“130827”为所求的数据总和。

④使用同样的方法分别求的表中数据的平均值、中位数、众数、标准差为下图1.4所示。

2、用分析工具试计算这31个地区人口的总和、平均值、中位数、众数、标准差。

（1）点击“工具—数据分析”，打开“数据分析”对话框，如下图1.5所示。

（2）选择“描述统计”功能，单击“确定”，系统打开描述统计对话框，如下图所示。

选定数据的输入区域为B2：

B32单元格；分组方式选择“逐列”；选定一个输出区域为D9单元格；再选定“汇总统计”复选框，如图1.6。

（3）系统输出2008年我国各地区人口描述统计的计算结果如图1.7所示。

（4）在系统的输出中，“平均”即均值X，反映了全国各地区人口的平均水平；“标准误差”为样本均值的标准差，反映了用平均值代表性的大小，本例中标准误差的值很大，说明平均值的代表性差，各地区的人口数量差异比较大；“众数”即出现次数最多的标志值，由于本例中31个标志值互不相同，故没有众数；“标准差”为总体标准差；“方差”为总体方差；本例中峰度的值大于零，表示分布比正态分布更集中在平均数周围，分布呈尖峰状态；偏度的值大于零，说明分布呈正偏斜，即大部分标志值是大于平均值的。

2、对居民户月消费支出额编制组距式变量数列，

（1）数据输入。

分别输入“居民消费品支出”和组限。

如图1.8所示

（2）执行菜单命令[工具]→[数据分析]，调出“数据分析”对话框，选择“直方图”选项，调出“直方图”对话框。

注意，若“数据分析”命令没有出现在“工具”菜单上，则应先使用[工具]→[加载宏]命令来加载“分析工具库”。

（3）在“直方图”对话框中，输入相关数据，见下图1.9。

输入区域：

$A$1:

$J$5

接收区域：

$A$7:

$A$14，接收区域的数值应按升序排列

输出区域：

$B$7（为输出结果左上角单元格地址）

选中“标志”复选框。

柏拉图：

选中此复选框，可以在输出表中同时按降序排列频率数据。

如果此复选框被清除，Excel将只按升序来排列数据。

累积百分比：

选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。

如果清除此选项，则会省略累积百分比。

图表输出：

选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。

本例中选中“累积百分比”和“图表输出”两个复选框。

（4）实验结果见下图1.10

（5）由图中可知，对分组资料进行整理后可得如下结果

表1-3某月X市50户居民购买消费品支出分组资料

组名

居民购买消费品支出分组

居民户数

900以下

900～1000

1000～1100

1100～1200

1200～1300

1300～1400

1400～1500

1500～1600

1600以上

3、用Excel进行抽样

搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查，以后我国的统计调查将以抽样为主，所以我们在这里介绍一下如何用Excel进行抽样。

（1）使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，这里我们按照1-100的数据进行编号，如下图1.11所示：

图1.11总体各单位编号表

（2）单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样。

如图1-2所示：

图1-2数据分析对话框

（3）单击抽样选项，弹出抽样对话框。

如图1.12所示：

图1.12

（4）在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:

$J$10，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。

如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。

（5）在抽样方法项下，有周期和随机两种抽样模式：

“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。

如我们要在100个总体单位中抽取12个，则在“间隔”框中输入8。

“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。

采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。

可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。

（6）指定输出区域，在这里我们输入$A$14，单击确定后，即可得到抽样结果，如图1.13所示：

图1.13等距抽样结果

实验二Excel图表制作及编辑

一、实验目的

1．掌握Excel的图表创建

2．熟悉Excel图表编辑技巧

3．建立自定义的图表

二、实验要求

1．掌握Excel的基本图表创建操作方法。

2．通过练习，能够独立运用Excel图表进行数据整理和数据分析。

三．实验内容

1．统计图表的建立；

2．学习、练习Excel的图表编辑；

3．学会制作自定义图表

四．实验步骤：

1、统计图表的创建与编辑，以表2-1为例，建立柱形图。

表2-12005—2009年我国普通高等教育、中等职业教育及普通高中招生人数（万人）

年份

普通高等教育

中等职业教育

普通高中

2005

504

656

878

2006

546

748

871

2007

566

810

840

2008

608

812

837

2009

640

874

830

（1）将表2-1中的数据输入Excel表格中，如图2.1所示

（2）移动鼠标到工具栏单击

“图表向导”按钮，出现“图表向导-4步骤之1-图表类型”窗口后，

选择“标准类型”标签，从“图表类型”列表中选择想要的图表类型，然后选择一种子图表类型，再

单击“下一步”按钮。

如图2.2

（3）如图2.3所示，出现“图表向导-4步骤之2-图表源数据”窗口后，选择“数据区域”标签的

按钮，在Excel中选择要建立图表的纵轴数据区域B1:

D6（纵坐标的数据），再次按下

按钮回到对话框，然后在“系列产生在”区选择“行”，再单击下一步按钮。

（4）点击系列按钮出现如图2.4所示的对话框，在“分类（x）轴标志”后的栏内选择图表横轴的数据区域A2:

A6（横坐标的数据），按下“下一步按钮。

”

（5）在如图2.5所示，出现“图表向导-4步骤之3-图表选项”窗口后，选择“标题”标签，在“图表标题”栏输入标题名称，然后分别在“分类X轴”、“数值Y轴”栏输入标题，在单击“下一步”按钮。

另外可以根据需要分别对对话框中的“坐标轴”、“网格线”“图例”“数据标志”等内容进行修改和编辑。

（6）如图2.6所示，出现“图表向导-4步骤之4-图表位置”窗口后，选择“作为其中的对象插入”，则建好的图表与原来的数据在同一个Excel表格中；若选择“作为新工作表插入”，建好的图表会放在另外新生成的Excel表格中。

在此我们选择“作为其中的对象插入”。

（7）最终建立的图表如下图2.7所示。

（8）将鼠标置于图标区域内点击鼠标右键弹出如图2.8的下拉菜单，可以打开“图表类型”“源数据”

“图表选项”等对话框，根据需要对图标进行修改。

2、建立自定义图表，以表2-2为例，建立分离饼形图，并将其添加到“自定义图表”中，以后可以直接套用。

桂林电子科技大学信息科技学院现有专任教师212人，教师队伍中具有高级职称的占30%，具有硕士学位以上教师占35.8%，本科学位以上教师占34.2%，试汇出该院教师职称分布图。

表2-2桂林电子科技大学信息科技学院教师职称分布情况表

职称

比例

高级职称

30%

硕士学位教师

35.8%

本科学位教师

34.2%

（1）将表2-2中的数据输入Excel表格中，如图2.9所示

（2）移动鼠标到工具栏单击

“图表向导”按钮，出现“图表向导-4步骤之1-图表类型”窗口后，

选择“自定义类型”标签，从“图表类型”列表中选择“分裂的饼图”。

如图2.10

（3）如图2.11所示，出现“源数据”窗口后，选择“数据区域”标签的

按钮，在Excel中选择要建立图表的数据区域A2:

B4，再单击下一步按钮。

（4）在如图2.12所示，出现“图表向导-4步骤之3-图表选项”窗口后，选择“标题”标签，在“图表标题”栏输入标题名称，然后分别在“图例”、“数据标志”栏输入相应的信息，再单击“下一步”按钮。

（5）将鼠标置于图标区域内点击鼠标右键弹出对图表进行编辑的下拉菜单，可以打开“图表类型”、“源数据”、“图表选项”等对话框，根据需要对图标进行修改，得到如图2.13的最终图表。

（6）将图表改成合适的样式之后，将图表添加到自定义类型中进行保存，以后若要使用就可以直接进行套用了。

具体操作方法：

移动光标到图表区上单击鼠标右键，弹出快捷菜单后，从菜单中选择“图表类型”，再选择“自定义类型”标签，在“选自”区选择“自定义”。

如图2.14“图表类型”后，点击“添加”按钮。

（7）弹出如图2.15“添加自定义图标类型”对话框后，在“名称”栏中输入一个代表性的名字。

然后联系点击“确定”，则可将自己所作的图表保存在“自定义类型”图表中，以便以后直接套用。

（8）如以后需要直接套用已经存在的自定义图表时，点击“图标向导”——“自定义类型”，在“选自”区选择“自定义”，则会有刚才所建立的“职称比例分布图”，如下图162.所示，可以直接套用，无需再修改。

实验三Excel区间估计

一、实验目的:

掌握Excel区间估计方法。

二、实验流程：

1.进入Excel.

2.使用直方图进行初步分析。

3.利用“描述统计”计算平均数和标准差。

4.进行区间估计。

三、实验内容

以下数据为50辆汽车传动系统出现故障时所行驶的实际里程数。

85092

32609

59465

77437

32534

64090

32464

59902

39323

89641

94219

116803

92857

63436

65605

85861

64342

61978

67998

59817

101769

95774

121352

69568

74276

66998

40001

72069

25066

77098

69922

35662

74425

67202

118444

53500

79294

64544

86813

116269

37831

89431

73341

85288

138114

53402

85586

82256

77539

88798

求曾经出现过传动系统问题的汽车总体中在出现传动系统问题时所行驶里程均值的95%置信区间。

四、实验步骤

1、首先将分析数据输入Excel工作表Sheet1单元格A1:

J5。

如图所示：

2、利用“函数”分别计算平均值和标准差（步骤同实验一），得到计算结果为平均里程为73342.1，标准偏差为24899.9。

3、进行区间估计

[1].单击存放结果的单元格E7。

[2].在单元格E7输入公式：

“=73342.1-CONFIDENCE（0.05,24899.9,50）”，其中函数CONFIDENCE功能为返回均值的置信区间，其使用方法同实验一中所讲函数的使用方法相同，见下图。

然后再按Enter键，得到结果为66440.34。

[2].在在单元格E8输入公式：

“=73342.1+CONFIDENCE（0.05,24899.9,50）”，按Enter键，得到结果为80243.86。

得到区间估计结果为80243.86。

如图所示

实验四一元线性性回归模型

一、实验目的

1．掌握用excel一次性算出回归模型参数的方法和步骤；

2．正确分析输出结果并得出正确的回归模型。

二、实验内容：

某地区1994年到2002年的人均收入和商品零售总额的数据如下：

表4.1地区1994年到2002年的人均收入和商品零售总额

年份

人均收入X（元）

商品零售总额（Y亿元）

1994

450

1995

550

1996

680

1997

730

1998

810

1999

930

2000

1050

103

2001

1160

115

2002

1250

128

试配合适当的回归模型并进行显著性检验；若2003年该地区人均收入为1300元，当显著性水平a＝0.05时，试估计2003年商品零售总额为多少？

三、操作步骤

1．在excel的工作表中输入如表4.1所示的人均收入X和商品零售总额Y的样本数据。

2．点击主菜单中的“插入”菜单，在弹出的子菜单中点击“图表”选项，在标准类型选项下选择“XY散点图”，点击下一步，在弹出对话框的输入区域，拖动鼠标选择人均收入X和商品零售总额Y的样本值；系列产生在，选择列；得到散点图如图4.1所示。

由图4.1可以看出人均收入X和商品零售总额Y之间存在很明显的线性关系，可以建立一元线性回归模型。

3．点击主菜单中的“工具”菜单，在弹出的子菜单中点击“数据分析”（若弹出的子菜

单无“数据分析”选项，可通过“工具”菜单中的“加载宏”选项进行补充安装），在

出现的数据分析对话框中选择回归，如图4.2所示。

图4.2应用excel软件求回归分析相关参数

4．点击图4.2所示对话框中的确定，弹出“回归”对话框，在Y值输入区域，拖动鼠标选择Y样本值C2:

C10，在X值输入区域，拖动鼠标选择X样本值B2:

B10,“置信度”输入95%，“输出区域”为A12，如图4.3所示。

图4.3应用excel软件求回归分析相关参数

5．点击图4.3所示中的确定，弹出回归分析有关参数的窗口，如图4.4所示。

图4.4应用excel软件求回归分析相关参数

四、结果分析

“回归统计”中MultipleR为复相关系数；RSquare为可决系数R2；AdjustedRSquare为修正的可决系数；“标准误差”为σ的点估计值，该值在求Y的预测区间和控制范围时要用到。

方差分析表中SingnificanceF为对回归方程检验所达到的临界显著性水平，即P值；SS

为平方和；df是自由度；P-value为P值，即所达到的临界显著水平；Fcrit是Fα（t-1，N-t）的值。

图4.4中最后部分给出的是各回归系数及对回归系数的显著性检验结果。

Intercept为截距，即常数项；Coefficients为回归系数；“标准误差”为回归系数标准差的估计；tStat为对回归系数进行t检验时t统计量的值。

下限95%和上限95%分别给出了各回归系数的95%置信区间。

由图4.4的输出结果，可以得到本例中的回归系数为aˆ=0.1336577,bˆ=-38.68166，

故所求回归方程为Yˆ=0.1336577X+-38.68166，由于SignificanceF=4.36511E-08

<<0.001，可知回归方程是极高度显著的，说明该回归模型和回归方程合理反映了人均收入和商品零售总额的相关关系，可以用来进行预测和控制。

实验五长期趋势分析

一、实验目的

1、掌握用移动平均法、趋势方程法和利用函数进行长期趋势分析的方法与步骤；

2、测定发展速度和平均发展速度、增长量和平均增长量。

二、实验内容：

1、表5.1所示为我国2001-2009年国内生产总值，请根据所给数据，分别用移动平均法、趋势方程法和利用函数对2010年的国民生产总值进行预测。

表5.1所示为我国2001-2009年国内生产总值

年份

国内生产总值（亿元）

2001

109655

2002

120333

2003

135823

2004

159878

2005

184937

2006

216314

2007

265810

2008

314045

2009

335353

2、计算发展速度和平均发展速度、增长量和平均增长量的值

三、实验步骤：

1.移动平均法

（1）在excel的工作表中输入如表5.1所示的年份和国民国民生产总值的样本数据。

点击“工具—数据分析—移动平均”，出现数据分析对话框，如图5.1所示。

图5.1数据分析对话框

（2）点击“确定”按钮，出现“移动平均”对话框，在其中设置各个参数，输入区域为：

$B$2:

$B$10；输出区域为：

$C$2；选中“图表输出”复选框。

如图5.2所示。

图5.2移动平均对话框

（3）．点击“确定”按钮，即可得到经过移动平均之后的预测数值和绘制的长期趋势图，如图5.3所示

图5.3用移动平均法得到的长期趋势线

2、趋势方程法

（1）点击主菜单中的“工具”菜单，在弹出的子菜单中点击“数据分析”，在出现的数据分析对话框中选择“回归”。

弹出“回归”对话框，在Y值输入区域，拖动鼠标选择国内生产总值B2:

B10，在X值输入区域，拖动鼠标选择年份值A2:

A10,“置信度”输入95%，“输出区域”为C2，如图5.4所示。

图5.4应用excel软件求回归分析相关参数

（2）点击图5.4所示中的确定，弹出回归分析有关参数的窗口及模型拟合值图，如下图5.5所示。

（3）由表中数据可得直线趋势方程为Yˆ=30005.63333X+-59956611.72，将X=2010带入趋势方程可得，2010年我国国内生产总值的预测值为354711.2733

3、利用函数进行趋势预测

（1）打开“插入”菜单，选择“函数”，在“函数分类”中选择“全部”，在“函数名”中选择“FORECAST”,按确定键，如下图5.5所示。

（2）在弹出的“函数参数”对话框中的“X”中输入预测期的时间代码“2010”；

在“KNOWN-YS”中输入国内生产总值的样本数据；在“KNOWN-XS”中输入时间代码；点击“确定”按钮。

如下图5.6所示。

（3）最后返回的数值“354711.3”即为对2010年我国国内生产总值的预测值。

4、测定增长

展开阅读全文