用Excel作统计分析图形案例讲解.docx
《用Excel作统计分析图形案例讲解.docx》由会员分享，可在线阅读，更多相关《用Excel作统计分析图形案例讲解.docx（28页珍藏版）》请在冰豆网上搜索。
用Excel作统计分析图形案例讲解
用Excel作统计分析案例讲解
说明：
Excel拥有很强大的统计分析功能，大量的统计分析运用EXCEL就可以完成。
在使用统计分析功能之前，要求按照下列步骤操作：
1、安装Excel，选择自定义安装，加载“宏命令”，其他按照需要进行安装；
2、完成自定义安装，进入EXCEL界面之后，继续下面操作，
EXCEL工具加载宏分析工具库确定
3、完成上述步骤后，在Excel“工具”菜单下面会出现“数据分析”子菜单，所有统计分析功能均在该子菜单下面。
一、如何运用Excel作统计图（直方图）
下列数据是80个工人的月收入（单位：
元），绘制直方图，观察月收入的分布状况。
1027
1302
1573
1658
1705
1794
1920
2040
1050
1356
1578
1665
1712
1800
1934
2070
1109
1435
1579
1670
1730
1830
1940
2076
1154
1452
1600
1670
1750
1854
1958
2140
1200
1452
1630
1678
1753
1860
1965
2143
1235
1543
1630
1679
1758
1870
1973
2270
1237
1543
1645
1690
1779
1870
1987
2300
1260
1546
1649
1690
1780
1870
1998
2354
1280
1560
1652
1699
1784
1890
2000
2478
1298
1570
1652
1700
1793
1893
2038
2595
1、数据处理：
注意：
对任意数据绘制直方图时，首先必须进行简单的数据处理，即对数据进行排序，分组，找分界点。
具体表现为：
（1）组数的确定，
，
为数据总量（80个），可计算拟分为8组
（2）组距的确定，
，
为数据最大值与最小值
（3）结合组数和组距以及原始数据确定各组的起始点，即为
第一组1200以下，第二组1200~1400，依次类推，第八组为2400以上。
（4）在Excel空白区域输入各组分界点，依次为，1199.99，1399.99，1599.99，1799.99，1999.99，2199.99，2399.99，即区域B2:
B8。
2、操作步骤
（1）、输入原始数据（区域A1:
A80，B2:
B8），并对该数据排序。
操作方法：
EXCEL——数据——排序。
（2）绘直方图操作过程：
第一步：
选择“工具”——“数据分析”，见下图：
第二步：
选择“数据分析”——“直方图”，见下图
第三步：
在“直方图”字对话框依次执行下列操作：
在“输入区域”用鼠标点击A1:
A80得到如图所示结果；
在“分组区域”用鼠标点击B2:
B8得到如图所示结果；
在“输出选项”子菜单下面，选择“图表输出”可得到直方图，选
择“新工作簿”表示将频数分布表存放在新的工作表中，也可选择“输出区域”存放在你认为合适的地方
3、操作结果
完成上述操作后，EXCEL会相应输出下列操作结果。
（1）表格：
根据上述操作可得到下列表格，左图为Excel原始输出，右图为对应的含义解释。
接收
频率
1199.99
4
1399.99
8
1599.99
11
1799.99
28
1999.99
17
2199.99
7
2399.99
3
其他
2
按月收入分组
频率
1200以下
4
1200~1400
8
1400~1600
11
1600~1800
28
1800~2000
17
2000~2200
7
2200~2400
3
2400以上
2
（2）直方图：
（调整后如下图）
二、如何运用Excel计算描述统计量：
包括平均数、众数、中位数、方差、偏度、峰度等。
下面是某保险公司160名员工的某月保险销售额，（单位：
千元）要求计算相关描述统计量，了解该公司员工的保险销售额的情况
8.4
14.26
15.76
16.98
17.39
17.61
24.2
26.51
8.81
14.52
15.77
17.07
17.41
17.61
24.26
26.7
9.24
14.61
15.82
17.14
17.43
17.62
24.8
26.74
9.64
14.78
15.84
17.16
17.43
17.66
25.05
26.93
11.05
14.87
15.86
17.16
17.48
17.7
25.29
27.5
11.43
14.92
15.88
17.25
17.48
17.75
25.4
27.76
12.37
15.08
15.88
17.25
17.52
17.75
25.53
31.16
12.74
15.13
15.99
17.25
17.57
17.79
25.64
31.34
12.96
15.15
16.16
17.31
17.57
17.84
25.83
32.4
13.22
15.27
16.22
17.34
17.57
17.9
26.07
32.55
13.25
15.34
17.93
18.42
18.96
21.09
21.53
22.65
13.33
15.37
17.93
18.51
19.05
21.16
21.64
22.84
13.51
15.42
17.96
18.55
19.25
21.16
21.66
23.16
13.61
15.43
17.97
18.63
19.27
21.19
21.72
23.27
13.61
15.46
18.02
18.64
19.35
21.25
21.81
23.4
13.68
15.48
18.22
18.64
19.45
21.31
21.88
23.45
13.8
15.56
18.24
18.69
19.46
21.34
21.92
23.5
13.85
15.64
18.28
18.73
19.55
21.4
22.13
23.65
13.97
15.65
18.29
18.78
19.91
21.42
22.28
23.76
14.16
15.71
18.33
18.94
21.03
21.53
22.38
23.94
1、操作步骤：
第一步：
输入原始数据（A1：
A160），并进入描述统计菜单，操作步骤如下：
Excel——工具——数据分析——描述统计
第二步：
在“描述统计”子菜单下面作如下选择：
“输入区域”：
选择A1：
A160，表示对该数据作描述统计分析。
“输入区域”：
选择“汇总统计”，表示输出各种描述统计量，包括平均数、众数、中位数、方差、偏度、峰度等各个描述统计量。
选择“新工作簿”，表示将描述统计分析结果存放在新的工作表格中。
2、操作结果：
完成上述操作步骤后，Excel相应输出下列分析结果
统计量
计算结果
平均
18.77194
标准误差
0.354332
中位数
17.915
众数
17.25
标准差
4.48198
方差
20.08814
峰度
0.678236
偏度
0.608222
区域
24.15
最小值
8.4
最大值
32.55
求和
3003.51
观测数
160
三、如何运用Excel作区间估计
现有一大批糖果，先从重随机地取16袋，称得重量（以克计）如下：
506508499503504510497512514505493496，设袋装糖果重量近似服从正态分布，试求总体均值
的置信度为0.95的置信区间。
1、操作目标：
要进行区间估计，需构造形如下图的工作表：
其中，左边A5：
A16是变量名，右边B5：
B16是变量相应的计算公式，输入样本数据F2：
F17。
A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准差，B列给出计算三个样本统计量的计算公式（或称函数）；
A列“用户输入”，输入进行区间估计的置信水平，通常为0.95；
A列“计算结果”，下面包含进行区间估计所需的六个量：
抽样标准误差、自由度、t值、置信区间半径、置信区间下限、置信区间上限。
B列为各自对应计算公式
注：
若事先构造好下表，那么任意数据进行区间，只要输入数据，直接可以得到区间估计的结果。
2、操作步骤：
第一步：
在Excel表格中输入上图中A4：
A16和F1：
F17的内容。
第二步：
名称定义。
（1）、进行变量定义，将A列的变量名称定义为B列各公式计算结果的变量名。
方法：
选定A5：
B7和A9：
B9，A11：
B16（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”——“名称”——“指定”——“最左列”——“确定”。
如下图：
（2）、为样本数据进行名称定义，即将F2：
F17的数据定义为“样本数据”。
方法：
“插入”——“名称”——“指定”——“首行”——“确定”
第三步：
进行区间估计。
依次在B列输入A列对应位置变量的计算公式，回车可得到各个变量值。
1、计算“样本个数”，在B5内编辑计算公式：
“=count（F2：
F16）”（点中F2：
F16），回车即可得到样本个数计算值16。
2、计算“样本均值”，在B6内编辑计算公式：
“=average（F2：
F16）”（点中F2：
F16），回车即可得到样本均值计算值503.75。
3、计算“样本标准差”，在B7内编辑计算公式：
“=stdev（F2：
F16）”（点中F2：
F16），回车即可得到样本标准差6.202。
4、“置信水平”，由用户自行输入，一般为0.95
5、“抽样标准误差”，在B11内编辑计算公式：
“=样本标准差/sqrt（样本个数）”
（方法：
输入等于号，点中B7,输入“/”和函数“sqrt”，点中B5，回车即可得到抽样标准差1.55。
6、“自由度”，在B12内编辑计算公式：
“=样本个数-1”,具体公式编辑方法同（5），回车可得自由度值为15。
（或者不需要公式，直接填入自由度值）
7、“t值”，在B13内编辑计算公式：
“=tinv（1-B9,B12）”,具体公式编辑方法同（5），回车t值为2.1314.
8、“置信区间半径”，在B14内编辑计算公式：
“=B13*B11”，具体公式编辑方法同（5），回车，可得值为3.304
9、“置信区间上限”，在B15内编辑计算公式：
“=B6+B14”，具体公式编辑方法同（5），回车，可得值为507.0549
10、“置信区间下限”，在B15内编辑计算公式：
“=B6-B14”，具体公式编辑方法同（5），回车值为500.4451
经过上述步骤可得区间估计的结果，上述过程看似复杂，实际上只要构造好一个区间估计的固定工作表，对任意数据进行区间估计，只要代入数据即可。
五、如何运用Excel作假设检验
某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖重量是一个随机变量，它服从正态分布。
当机器正常时，其均值为0.5公斤，标准差为0.015公斤。
某日开工后为检验包装机是否正常，随机的抽取所包装的糖9袋，称得净重为
0.4970.5060.5180.5240.4980.5110.5200.5150.512（公斤）
问：
机器是否正常？
（总体标准差稳定）
1、操作目标：
构造形如下图的工作表：
要进行假设检验，需构造形如下图的工作表：
其中，左边A5：
A16是变量名，右边B5：
B16是变量相应的计算公式，输入样本数据E2：
E10
A列“样本统计量”包含两个，依次为样本个数、样本均值，B列给出计算个样本统计量的计算公式（或称函数）；
A列“用户输入”，输入进行假设检验的置信水平，通常为0.95，总体标准差和总体均值假设值（通常为已知条件）；
A列“计算结果”，下面包含进行区间估计所需的两个量：
抽样标准误差、计算Z值
后面依次为单侧检验和双侧检验，由你自行选择。
注：
若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以得到假设检验的结果。
2、操作方法：
第一步：
在Excel表格中输入上图中A2：
A19和E1：
E10的内容
第二步：
名称定义。
（方法基本上相同于区间估计）
（1）进行变量定义，将A列的名称定义为B列各个公式计算结果的变量名。
选定A3：
B4和A6：
B8，A10：
B11，A13：
B15，A17：
B19（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”——名称——指定——最左列——确定。
（2）为样本数据定义。
如图“插入”——“名称”——“指定”——“首行”，此操作是将E2：
E10的数据名称定义为“样本数据”
第三步：
计算
依次在B列输入计算公式，形如下图，依次可得到各个函数值，
（1）计算“样本个数”，在B3内编辑计算公式：
“=count（E2：
E10）”（点中E2：
E10），回车即可得到样本个数计算值9。
（2）计算样本均值，在B4内编辑计算公式：
“=average（E2：
E10）”（点中E2：
E10），回车即可得到样本均值计算值0.511
（3）、输入假设检验所需要的参数，包括置信水平0.95，总体标准差0.015，总体均值假设值0.5（在进行假设检验时，这些参数值一般已知）
（4）、“抽样标准误差”，在B10内编辑公式，“=B7/B3”，回车可得0.002
（5）“计算Z值”，在B11内编辑公式，“=NORMSINV（1-B6）”，回车可得-1.64
（6）进行双侧检验，“双侧Z值”，在B16内编辑公式，“=NORMSINV（1-B6）”
“检验结果”，在B18内编辑公式，“=IF（ABS（B11）>ABS（B17）,”拒绝H0”,接受H0”）形如下图，输入该公式后，回车即为检验结果
六、如何运用Excel作方差分析
随机选取26位被访者，了解他们对安乐死的赞同程度。
然后按不同血型进行分组，资料如下表。
在显著性水平为0.05时进行方差分析，判断不同血型的人对安乐死的态度是否有显著差异性？
现假设已把数据输入到Excel表格中，区域A2:
D11
O
A
AB
AB
80
80
73
92
78
77
80
86
94
69
92
70
73
84
76
85
79
52
68
86
40
91
73
75
81
64
（1）操作步骤：
第一步：
选择“工具”下拉菜单，然后选择“数据分析”选项。
第二步：
在分析工具中选择“单因素方差分析”。
如下图：
第三步：
当出现对话框后，在“输入区域”方框内键入A2：
D11（直接让鼠标选中所要分析的数据即可）；在α方框内输入0.05；在“输出选项”中选择输出区域为（可以任意指定空白区域），若选择“新工作表组”，方差分析结果将在另一表格中呈现；最后选择“确定”。
（2）输出结果：
完成上述操作步骤后，EXCEL输出下列分析结果：
表一：
样本数据描述统计
组
计数
求和
平均
方差
列1
10
801
80.1
76.54444
列2
7
475
67.85714
257.8095
列3
5
389
77.8
82.2
列4
4
333
83.25
87.58333
表二：
方差分析结果
差异源
SS
df
MS
F
P-value
Fcrit
组间
840.0775
3
280.0258
2.178953
0.119232
3.049124
组内
2827.307
22
128.514
总计
3667.385
25
从上表中可以看出，
，
所以说明不同血型的人对安乐死的态度没有显著差异。
七、如何运用Excel作相关分析
下表为年龄（X）与脂肪百分比（Y）的数据，试计算两者的相关系数。
年龄
23
23
27
27
39
41
45
49
脂肪的百分比
9.5
27.9
7.8
17.8
31.4
25.9
27.4
25.2
（1）、操作步骤：
第一步：
选择“工具”——“数据分析”——“相关系数”——“确定”
第二步：
在“相关系数”子对话框中，作如下选择：
“输入区域”：
选中原数据即可，如图所示。
“输出选项”：
若选择“输出区域”（任意选定空白区域即可，则相关系数计算结果显示在该区域。
若选则“新工作表”，则相关系数显示在该工作表中。
最后选择“确定”进行相关分析。
（2）结果：
完成上述步骤后，显示结果如下：
列1
列2
列1
1
列2
0.792086
1
表示年龄与脂肪百分比相关系数为0.792
八、如何运用Excel作回归分析
下面是某市人均国民收入与消费金额的数据，试对两个变量作回归分析。
年份
人均国民收入
人均消费金额
1981
393.8
249
1982
419.14
267
1983
460.86
289
1984
544.11
329
1985
668.29
406
1986
737.73
451
1987
859.97
513
1988
1068.8
643
1989
1169.2
690
1990
1250.7
713
1991
1429.5
803
1992
1725.9
947
1993
2099.5
1148
（1）操作步骤：
第一步：
在新工作簿中输入数据如下图所示。
第二步：
“工具”——“数据分析”——“回归”选项，单击“确定”按钮，进入“回归”对话框。
第三步：
在“回归”字对话框作如下操作：
在“Y值输入区域”中输入C2:
C14（点中该区域即可），它代表人均消费金额的数据范围，表示因变量。
在“X值输入区域”中输入B2:
B14（点中该区域即可），它代表人均国民收入的数据范围，表示自变量。
“置信度”，通常为0.95
在“输出选项”，进行适当选择，表示选择将回归结果存放的具体位置；
在“残差”部分表示回归残差的计算结果
（2）、操作结果：
根据上述步骤，最后可得回归结果如下：
表一中：
给出回归的判决系数（拟合优度）并给出回归标准误差
表二中：
，表明本次回归有效
表三中：
给出回归方程中的截距54和自变量的回归系数0.53。
各自的t统计量和P值表明均通过了显著性检验，所以回归方程中截距和自变量都是有统计学意义的。
升级会员 