Excel在统计学中的应用.docx

资源描述

Excel在统计学中的应用.docx

《Excel在统计学中的应用.docx》由会员分享，可在线阅读，更多相关《Excel在统计学中的应用.docx（30页珍藏版）》请在冰豆网上搜索。

Excel在统计学中的应用.docx

Excel在统计学中的应用

　　统计分析常用的软件有SAS、SPSS和Excel等，其中Excel应用较为普遍。

本附录主要介绍Excel在统计中的应用，并用实例操作的形式进行介绍。

一、用Excel作数据的频率分布表和直方图

利用Excel处理数据，可以建立频率分布表和条形图。

一般统计数据有两大类，即定性数据和定量数据。

定性数据用代码转化为定量数据后再处理，这里就不涉及了，下面主要以定量数据为例来说明如何利用Excel进行分组，并作频率分布表和直方图。

［资料］

现有某管理局下属40个企业产值计划完成百分比资料如下：

97、123、119、112、113、117、105、107、120、107、125、142、

103、115、119、88、115、158、146、126、108、110、137、136、

108、127、118、87、114、105、117、124、129、138、100、103、

92、95、127、104

（1）据此编制分布数列（提示：

产值计划完成百分比是连续变量）；

（2）计算向上累计频数（率）；

（3）画出次数分布直方图。

［步骤］

第1步：

打开Excel界面，输入40个企业的数据，从上到下输入A列（也可分组排列）。

第2步：

选择“工具”下拉菜单，如附图1－1。

附图1－1

第3步：

选择“数据分析”选项，如果没有该功能则要先行安装。

“数据分析”的具体安装方法，选择“工具”下拉菜单中“加载宏”，在出现的选项中选择“分析工具库”，并“确定”就可自动安装。

附图1－2

第4步：

在分析工具中选择“直方图”，如附图1－2。

附图1－3

第5步：

当出现“直方图”对话框时，在“输入区域”方框内键入A2:

A41或$A$2：

$A$41（“$”符号起到固定单元格坐标的作用，表示的是绝对地址），40个数据已输入该区域内，如果是分组排列的，就应选择整个分组区域。

在“接收区域”方框内键入C2：

C9或$C$2：

$C$9，所有数据分成8组（主要根据资料的特点，决定组数、组距和组限），把各组的上限输入该区域内。

在“输出区域”方框内键入E2或$E$2，也可重新建表在其他位置。

对话框中，还选择“累积百分率”、“图表输出”（如附图1－3）。

最后：

点“确定”，就可得到结果。

对话框内主要选项的含义如下：

输入区域：

在此输入待分析数据区域的单元格范围。

接收区域（可选）：

在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。

这些值应当按升序排列。

只要存在的话，Excel将统计在各个相邻边界值之间的数据出现的次数。

如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。

标志：

如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel将在输出表中生成适宜的数据标志。

输出区域：

在此输入计算结果显示的单元格地址。

如果不输入具体位置将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。

柏拉图：

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

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

累积百分比：

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

如果清除此选项，则会省略以上结果。

图表输出：

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

［结果］

有关结果如附图1－4。

完整的结果通常包括三列和一个频数分布图，第一列是数值的区间范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。

附图1－4

直方图是用矩形的宽度和高度来表示频数分布的图形。

绘制直方图时，将所研究的变量放在横轴上，频数、频率放在纵轴上。

每组的频数、频率在图上就是一个长方形，长方形的底在横轴上，宽度是组距，长方形的高就是对应的频数或频率。

应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：

用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出数据系列格式对话框。

在对话框中选择“选项”标签，把“分类间距”宽度改为0，按确定后即可得到直方图，如附图1－5所示。

附图1－5

二、用Excel作常用统计图

Excel有较强的作图功能，可根据需要选择各类型的图形。

Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。

（一）饼图的绘制

饼图也称圆形图，是用圆形及圆内扇形的面积来表示数值大小的图形。

饼图主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。

［资料］

据中国互联网络信息中心2006年6月底的统计，我国目前网民的年龄分布如下表（附表2－1），根据资料利用Excel绘制饼图。

我国目前网民的年龄分布结构表

附表2－1

年龄

比重%

18岁以下

14.90%

18～24岁

38.90%

25～30岁

18.40%

31～35岁

10.10%

36～40岁

7.50%

41～50岁

7.00%

51～60岁

2.40%

60岁以上

0.80%

［步骤］

先把数据输入到工作表中，如附图2－1所示，可按下面的步骤操作。

第1步：

选择“插入”下拉菜单，选择“图表”。

第2步：

在图表类型中选择“饼图”，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。

然后单击下一步按钮，打开源数据对话框。

如附图2－2。

附图2－1

附图2－2

第3步：

在图表源数据对话框中填入数据所在区域，单击下一步，在图表选项中，对“标题”、“图例”和“数据标志”适当处理。

如果要对图形修改，可用鼠标双击图表，然后用鼠标双击需要修改的部分，并进行修改。

［结果］

即可得如附图2－3所示的饼图。

附图2－3

（二）折线图的绘制

折线图主要用于比较几类数据变动的方向和趋势，表现数据在不同时期发展变化的不同趋势。

［资料］

根据我国2001－2005年外贸货物进出口总额资料（如附表2－2），绘制折线图，描述我国近年来货物进出口额的变化趋势。

附表2－2　　　　　　　　　　　　　　　　　　　　　　　单位：

人民币亿元

年份

2001

2002

2003

2004

2005

货物进出口总额

42183.6

51378.2

70483.5

95539.1

116921.8

出口总额

22024.4

26947.9

36287.9

49103.3

62648.1

进口总额

20159.2

24430.3

34195.6

46435.8

54273.7

［步骤］

第1步：

资料输入工作表后，选择“插入”下拉菜单，再选择“图表”。

第2步：

在图表类型中选择“折线图”，然后在子图表类型中选择一种类型，这里我们选用如附图2－4的方式。

然后单击下一步按钮，打开源数据对话框。

附图2－4

第3步：

在源数据对话框中，“数据区域”中输入相关资料（可用用鼠标点击并框定数据区域）。

再在“系列”的“分类（x）轴标志”区域输入年份区域，如附图2－5。

第4步：

资料输入后的下一步，进入“图表选项”。

分别对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”等选项进行设置，当然设置各选项时根据需要进行取舍。

最后点“完成”，就在工作表中得到折线图。

附图2－5

［结果］

经过上述各步骤，在工作表中得到折线图，如附图2－6所示。

附图2－6

三、用Excel计算描述统计量

我们学习了平均指标，也掌握了测定数据的集中趋势和离散程度的常用统计量，下面将利用Excel来计算这些统计量。

为了说明该方法，仍用实例操作。

（一）利用“数据分析”功能计算

［资料］

设某班40名学生《统计学》考试成绩分别为：

66898884868775737268758297

58815479769576716090657672

76858992645783817877726170

对该班学生的考试成绩进行描述统计分析。

［步骤］

第1步：

在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：

A40”的单元格中。

第2步：

选择“工具”下拉菜单，再选择“数据分析”选项。

第3步：

在分析工具中选择“描述统计”，如附图3－1。

附图3－1

第4步：

当出现对话框时，在“输入区域”方框内键入A1：

A40（或用鼠标选择这区域），在“输出选项”中选择输出区域（在此选择C4），再选择“汇总统计”（该选项给出全部描述统计量）；

最后：

选择确定。

［结果］

其计算结果如附图3－2所示。

附图3－2

［结果分析］

　　　　附表3－1

平均（算术平均值）

76.525

标准误差（抽样标准误差）

1.69160351

中值（中位数）

模式（众数）

标准偏差（样本标准差）

10.69863998

样本方差（方差）

114.4608974

峰值（峰度系数）

-0.510964335

偏斜度（偏度系数）

-0.206203168

区域（极差或全距）

最小值（第K个最小值）

最大值（第K个最大值）

求和（标志值总和）

3061

计数（总频数）

置信度（95.0%）

3.421587697

（二）利用“统计函数”工具计算

描述统计量除上述“数据分析”功能计算外，还可采用Excel的函数工具计算。

仍以40名学生《统计学》考试成绩为例进行计算。

［步骤］

第1步：

在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：

A40”的单元格中。

第2步：

选择“插入”下拉菜单，再选择“函数”选项，如附图3－3。

附图3－3

第3步：

出现的“插入函数”界面中，在“或选择类别”选项中，选“统计”。

再在“选择函数”中，选“AVERAGE”（算术平均数），点击确定如附图3－4。

第4步：

出现“AVERAGE函数参数”界面中，在“Number1”中键入A1：

A40（或用鼠标选择这区域），然后点“确定”，就能得出“算术平均数＝76.525”。

附图3－4

重复上述各步骤，还可计算“调和平均数”、“几何平均数”、“样本标准差”和“总体标准差”等统计量，只是要分别选择相应的函数。

常用描述统计量函数如附表3－2所示。

EXCEL中常用描述统计量函数对照表

附表3－2

函数名称（英）

函数名称（中）

公式或符号

AVEDEV

平均差

AVERAGE

算术平均数

GEOMEAN

几何平均数

HARMEAN

调和平均数

MAX

最大值

MEDIAN

中位数

MIN

最小值

MODE

众数

STDEV

样本标准差（标准偏差）

STDEVP

总体标准差

VAR

样本方差

VARP

总体方差

四、用Excel进行随机抽样

使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见教材有关抽样的章节，编号后，将编号输入工作表。

［资料］

假定有80个总体单位，每个总体单位给一个编号，共有从1到80个编号，输入工作表后如附图4－1所示：

附图4－1

［步骤］

输入各总体单位的编号后，可按以下步骤操作：

第1步：

单击工具菜单，选择“数据分析”选项，打开“数据分析”对话框，从中选择“抽样”，如附图4－2所示。

附图4－2

第2步：

单击“抽样”选项，弹出“抽样”对话框，如附图4－3。

附图4－3

第3步：

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

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

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

第4步：

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

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

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

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

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

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

第5步：

指定输出区域，在这里我们输入$A$12，单击确定后，即可得到抽样结果。

［结果］

8个随机抽取的样本编号就显示在区域“A12：

A19”单元格中。

五、用Excel求置信区间

用Excel的“统计函数”工具进行抽样推断中的区间估计测算。

下面结合实例来说明具体的操作步骤。

［资料］

某商店随机抽查10名营业员，统计他们的日营业额（千元）如附图5－1中的“A2：

A11”。

假定该商店各营业员的日营业额是服从正态分布，试以95%的置信水平估计该商店营业员的日营业额的置信区间。

为构造区间估计的工作表，在工作表中输入下列内容：

A列输入样本数据，B列输入变量名称，C列输入计算公式，其实C列中当计算公式输入后显现的是计算结果，为了说明计算过程，在D列中展示C列的计算公式。

附图5－1

［步骤］

第1步：

把样本数据输入到A2：

A11单元格

第2步：

在C2中输入公式“=COUNT（A2：

A11）”，得到计算结果“10”。

“COUNT”是计数函数，得出样本容量（n=10）。

第3步：

在C3中输入“=AVERAGE（A2：

A11）”，在C4中输入“=STDEV（A2：

A11）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.95，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。

在输入每一个公式回车后，便可得到如附表5－1的结果。

附表5－1

样本数据

计算指标

计算公式

计算结果

样本数据个数

C2=COUNT（A2:

A11）

样本均值

C3=AVERAGE（A2:

A11）

38.4

样本标准差

C4=STDEV（A2：

A11）

4.195235393

抽样平均误差

C5=C4/SQRT（C2）

1.326649916

置信水平

C6=0.95

0.95

自由度

C7=C2-1

t值

C8=TINV（1-C6，C7）

2.262158887

误差范围

C9=C8*C5

3.001092898

置信下限

C10=C3-C9

35.3989071

置信上限

C11=C3+C9

41.4010929

［结果］

从上面的结果我们可以知道，该商店营业员的日营业额的置信下限为35.3989071（千元），置信上限为41.4010929（千元）。

计算结果可以得出，我们有95%的把握认为该商店营业员的日营业额平均在35.3989071（千元）到41.4010929（千元）之间。

在附表5－1中，对于不同的样本数据，依上表的格式，只要输入新的样本数据，再对C列公式略加修改，置信区间就会自动给出。

六、用Excel进行假设检验

假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。

对于一个正态总体参数的检验,可利用函数工具和自己输入公式的方法计算统计量,并进行检验。

本例主要介绍如何使用Excel进行两个正态分布的均值方差的检验。

［资料］

为了评价两个学校的教学质量,分别在两个学校抽取样本。

在A学校抽取30名学生,在B学校抽取40名学生,对两个学校的学生同时进行一次英语标准化考试，成绩如附表6－1所示。

假设学校A考试成绩的方差为64，学校B考试成绩的方差为100。

检验两个学校的教学质量是否有显著差异。

（

=0.05）

附表6－1

学校A

学校B

70 97 85 87 64 73

86 90 82 83 92 74

72 94 76 89 73 88

91 79 84 76 87 88

85 78 83 84 91 74

76 91 57 62 89 82 93 64

80 78 99 59 79 82 70 85

83 87 78 84 84 70 79 72

91 93 75 85 65 74 79 64

84 66 66 85 78 83 75 74

假定我们将上表中学校A的数据输入到工作表中的A2：

A31,学校B的数据输入到工作表的B2：

B41。

［步骤］

第1步：

选择“工具”下拉菜单。

再选择“数据分析”选项。

第2步：

在分析工具中选择“Z－检验:

双样本平均差检验”，如附图6－1。

附图6－1

第3步：

当出现对话框后，在“变量1的区域”方框内键入A2：

A31；在“变量2的区域”方框内键入B2：

B41；在“假设平均差”方框内键入0；在“变量1的方差”方框内键入64；在“变量2的方差”方框内键入100；在“α”方框内键入0.05；在“输出选项”中选择输出区域（在此选择“新工作表”）。

如附图6－2所示。

附图6－2

第4步：

所有选项设置好，选择确定。

［结果］

输出结果如附表6－2。

　　　　　附表6－2

变量1

变量2

平均数

82.5

已知协方差

100

观测值个数

假设平均差

2.0905749

P（Z<=z）单尾

0.018283

z单尾临界

1.6448535

P（Z<=z）双尾

0.0365661

z双尾临界

1.9599628

由于

，所以拒绝

，即两个学校的教学质量有显著差异。

七、用Excel进行相关与回归分析

（一）相关分析

相关分析可用于判断两组数据之间的关系。

我们可以使用“相关分析”来确定两个区域中数据的变化是否相关。

用Excel进行相关分析有两种方法，一是利用相关系数函数计算，如“CORREL函数”和“PERSON函数”；另一种是利用“数据分析”功能相关分析宏计算。

这里主要介绍后者。

［资料］

有10个同类企业生产性固定资产年均价值和工业增加值资料如附表7－1：

　　　附表7－1

企业编号

生产性固定资产价值（万元）

工业增加值（万元）

318

524

910

1019

200

638

409

815

415

913

502

928

314

605

1210

1516

1022

1219

1225

1624

合计

6525

9801

要求根据资料计算相关系数，并说明两变量相关的方向和程度。

［步骤］

　　将数据输入工作表后，按如下步骤：

第1步：

选择“工具”下拉菜单，再选择“数据分析”选项。

第2步：

在分析工具中选择“相关系数”。

第3步：

当出现对话框时，在“输入区域”方框内键入A2：

B11，在“输出选项”中选择输出区域（在此我们选择“新工作表”）。

最后：

“确定”，得出附图7－1。

附图7－1

［结果］

根据上述步骤计算的相关系数矩阵如附图7－1所示。

表中得出了两个变量之间的相关系数，如“生产性固定资产价值（万元）”与“工业增加值（万元）”的相关系数为0.947757，属于高度正相关。

（二）回归分析

利用Excel可以很容易地进行回归分析，包括一元线性回归和多元线性回归。

［资料］

根据附表7－1的资料，编制直线回归方程，计算估计标准误，并估计生产性固定资产（自变量）为1100万元时，工业增加值（因变量）的可能值。

［步骤］

我们仍结合上面的例子说明其操作步骤：

第1步：

选择“工具”下拉菜单。

第2步：

选择“数据分析”选项。

第3步：

在分析工具中选择“回归”。

第4步：

当出现对话框时，在“输入Y的区域”方框内键入B2：

B11，在“输入X的区域”方框内键入A2：

A11，在“输出选项”中选择输出区域（这里我们选择“新工作表”）。

最后：

“确定”。

［结果］

得到附图7－2所示的结果。

附图7－2

为了让大家看清楚，我们把有关的指标稍作解释。

附图7－2中回归统计部分给出了判定系数

、调整后的

、估计标准误差等；方差分析表部分给出的显著水平F值表明回归方程是显著的；最下面的一部分是

，

。

以及参数

的标准差、t检验的统计量、p-值、下限95%和上限95%给出了参数

置信区间。

比如，我们有95%的把握确信，

在210.4844和580.64964之间，

在0.650009和1.1416632之间。

除表中输出的结果外，我们还可以根据需要给出残差图、线性拟合图等。

所以，该例题中得到的回归方程为：

，回归估计标准误为：

126.6279。

当生产性固定资产

万元时，工业总产值为：

（万元）。

八、用Excel进行季节变动分析

为介绍Excel在季节变动分析中的应用，我们以实例操作，采用趋势剔除法计算季节指数。

［资料］

　　某小型企业销售收入如附表8－1。

　　　　　　附表8－1　　　　　　　　　　　　　　　单位：

万元

年份

春

夏

秋

冬

2002

107

2003

134

2004

113

100

148

2005

136

105

125

174

［步骤］

把数据输入到工作表中的B2：

B17。

用Excel构造一张季节变动分析表（如附图8－1），计算的步骤如下：

附图8－1

第1步：

计算4项移动平均数。

在C3单元格输入公式“=AVERAGE（B2：

B5）”，然后将公式复制到C4：

C15单元格。

结果如附图8－2的C列。

第2步：

计算计算移动平均趋势值（中心化移动平均数）。

也就是对C列的结果再进行一次二项移动平均。

在D4单元格输入公式“AVERAGE（C3：

C4）”，然后将公式复制到D5：

D15单元格。

结果如附图8－2中的D列。

第3步：

将实际值除以相应的趋势值。

在E4单元格输入公式“=B4/D4”，然后将公式复制到E5：

E15单元格。

结果如附图8－2中的E列。

第4步：

计算同季平均。

在F2单元格输入公式“=（E6+E10+E

展开阅读全文