Excel与数据统计分析.docx
《Excel与数据统计分析.docx》由会员分享，可在线阅读，更多相关《Excel与数据统计分析.docx（31页珍藏版）》请在冰点文库上搜索。
Excel与数据统计分析
Excel与数据统计分析
统计计算与统计分析强调与计算机密切结合，《Excel与数据统计分析》旨在提高学生计算机的综合运用能力，用统计方法分析问题、解决问题而编写的。
根据教材内容，也可以选择使用SPSS、QSTAT、Evievs、SAS、MINITAB等统计软件。
第三章统计整理
3.1计量数据的频数表与直方图
例3.1（3-1）
一、指定接受区域直方图
在应用此工具前，用户应先决定分布区间。
否则，Excel将用一个大约等于数据集中某数值的平方根作区间，在数据集的最大值与最小值之间用等宽间隔。
如果用户自己定义区间，可用2、5或10的倍数，这样易于分析。
对于工资数据，最小值是100，最大值是298。
一个紧凑的直方图可从区间100开始，区间宽度用10，最后一区间为300结束，需要21个区间。
这里所用的方法在两端加了一个空区间，在低端是区间“100或小于100”，高端是区间“大于300”。
参考图3.3，利用下面这些步骤可得到频率分布和直方图:
1（为了方便，将原始数据拷贝到新工作表“指定频数直方图”中。
2（在B1单元中输入“组距”作为一标记，在B2单元中输入100，B3单元中输入110，选取B2:
B3，向下拖动所选区域右下角的+到B22单元。
3（按下列步骤使用“直方图”分析工具:
（1）选择工具菜单之数据分析选项,在分析工具框中“直方图”。
如图4所示。
1
图3.1数据分析工具之直方图对话框
1）输入
输入区域:
A1:
A51
接受区域:
B1:
B22（这些区间断点或界限必须按升序排列）
选择标志
2）输出选项
输出区域:
C1
选定图表输出
（2）（单击确定，Excel将计算出结果显示在输出区域中。
图3.2数据分析工具之直方图对话框Excel将把频率分布和直方图放在工作表中，如图3.3所示，输出表的C和
2
D列中包括开始指定的界限。
这些界限实际上是每一区间的上限，也就是说，界限实际上是边界。
图3.3频数分布与直方图
为了使图表更像传统的直方图和更易于理解，可双击图表并对它做如下修改:
1（图例:
因为只有一个系列的数据显示在图表中，所以不需要图例。
单击图例（位于图表右侧的“频率”）并按Delete键。
2（图表区:
绘图区是以X和Y轴为边界的矩形区域。
通过在柱形上面单击可选取绘图区，单击鼠标右键并选择绘图区格式，将边框改为无并将区域改为无，单击确定。
3（条宽:
在传统的直方图中，柱形是彼此相连接而不是分开的。
选择某个柱形，单击鼠标右键，选择数据系列格式，并单击选择标签，将间距宽度从150,改为0,，单击确定。
4（X轴标志:
选取x轴，单击鼠标右键，选择坐标轴格式，单击对齐标签，将方向从自动改为水平文本，在这种设置下，即使图表已重置尺寸，x轴标记也会变为水平的，单击确定。
最后的直方图4.6
3
图3.4修改后的直方图
直方图
7
6
5
4
频率3
2
1
0
100120140160180200220240260280300
组距二、不指定接受区域直方图
在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如
下:
（1）选择工具菜单之数据分析选项,在分析工具框中“直方图”。
如图4
所示。
1）输入
输入区域:
A1:
A51
接受区域:
（该处为空）
选择标志
2）输出选项
输出区域:
B1
选定图表输出
（2）（单击确定，得结果。
（3）按前面方法对直方图进行进一步修饰即得图3.5
4
图3.5修改后的直方图
3.2计数数据的透视表与条图
例3.2（3-3）数据见图
步骤如下:
（1）选择数据菜单之数据透视表和图表报告选项,如图4所示。
5
（2）（选择数据源区域
（3）选定数据透视表位置，完成
（4）将“性别”作为行字段拖至G列，并将“性别”作为数据拖至数据项处，
得下表结果
6
同理可得“文化程度”的透视表
此时如点击图形按钮，立即得到如下的透视图
7
（5）将“性别”作为行字段拖至行字段处，并将“文化程度”作为列字段拖
至列字段处，将“性别”或“文化程度”作为列字段拖至数据字段处
得下表结果
第四章总量指标和相对指标
例4.1（4-13）
计算步骤:
8
（1）计算各厂计划完成%E3=D3/C3*100,…
（2）2000年实际产量为1999年的%F3=D3/B3*100,…
第五章平均指标
5.1简单平均数
例5.1（某组有学生10人统计课考试成绩为65，82，76，80，82，86，84，88，95，98分，试求其平均指标。
平均数的计算步骤如下:
（1）将数据输入到A列，根据Excel提供的公式计算各种平均数
（2）用Ctrl+`可切换到下面的结果:
5.2加权平均数
9
例5.2（5-1）原始数据见下图A-D列，其中A、B列放日产量的下限和上限
平均数的计算步骤如下:
（1）计算日产量的组中值E3=（A3+B3）/2,…
（2）计算每个组段的总产量F3=C3*E3,G3=D3*E3,…
（3）计算每月的总产量F8=SUM（F3:
F7）,G8=Sum（G3:
G7）,
（4）计算平均数公式如下:
均数F9=F8/C8G9=G8/D8
众数F10=A4+（C4-C3）/（C4-C3+C4-C5）*10G10=A6+（D6-D5）/（D6-D5+D6-D7）*10中位数F11=A4+（C8/2-A4）/C4*10G11=A5+（D8/2-C5）/D5*10
第六章变异度指标
6.1简单变异度指标
例6.1（6-1）
变异度指标的计算步骤如下:
（1）将甲乙两组数据输入到A,B列，根据Excel提供的公式计算各种变异度指标
10
（2）用Ctrl+`可切换到下面的公式:
6.2加权变异度指标
例6.2（6-2）甲品种的原始数据见下图B-C列，乙品种的原始数据见下图G-H列
下面以甲品种的数据计算为例:
（1）计算单产值D4=C4/B4,…
（2）计算单产均值D9=C9/B9
（3）计算次数X离差平方E4=B4*（D4-$D$9）^2,…并求和
11
E9=SUM（E4:
E8）
（4）计算标准差:
D11=SQRT（E9/B9）=68.91
（5）计算变异系数:
D12=D11/D9*100=6.9%
同理可得乙品种的标准差为162.71,变异系数为16.30%
第七章抽样调查
例7.1（7-5）期望
求E（X）的公式B4=SUM（（B1:
F1）*（B2:
F2））,由于此处用到数组乘积求和，所以要得到结果，需用Ctrl+Shift+Enter组合键。
例7.2（7-6）二项分布
计算公式:
P（5<=x<=10）=P（x<=10）-P（x<5）=P（x<=10）-P（x<=4）
P（x>=9）=1-P（x<9）=1-P（x<=8）
Excel计算结果:
12
Excel计算公式:
（Ctrl+`互换）
例7.3（7-6）泊松分布
计算公式:
P（x>=5）=1-P（x<=4）
Excel计算结果:
Excel计算公式:
13
（Ctrl+`互换）
例7.4（7-9）超几何分布
例7.5（7-10）正态分布
其中F2=1-D2,D4=D3-D2
第八章假设检验
8.1大样本——使用正态分布的假设检验
例8.1商店经理想为商店的持信用卡的顾客建一新的付款系统，经过详细的经济分析，她判定如果新系统每月平均利润低于70元的话就不能有效地使用
14
资金。
于是随机抽取了200个月的利润，其平均月利润为66元。
如果,0.05，有无充分的证据说明新系统不是一项节省资金的系统?
假设总体的标准偏差为30元。
图8.1正态假设检验的标记和公式---已知均值标准差计算公式
上图所示的工作表可用于正态分布平均值的左尾、右尾和双尾假设检验。
检验结果包括基于判决法和P值报告法。
输入样本大小、样本平均值和标准偏差作为值、公式或引用，指定假设的平均值（Mean）和显著水平作为值。
下面各步骤描述了如何建立该工作表:
（1）打开一新工作表并输入B列所示标记。
（2）要在C列的公式使用B列中的名称，选取单元B4:
C12，从插入菜单中选择名称指定，在指定名称对话框中复选名称创建于最左列，单击确定。
（3）输入C列所示的公式（按图所示键入公式或通过单击适当的已命名的单元插入函数来建立公式）。
（4）要得图8.1的A列所示的外观，按Ctrl+`。
15
因为经理想知道平均月利润是否小于70元，所以备择假设为Hd:
Mean,70，零假设为Ho:
Mean,70或简单地为Ho:
Mean,70。
由于数据已经总结过了，可直接在工作表单元中输入样本大小n、样本平均值、总体的标准偏差、假设总体平均值和显著水平。
图8.2正态假设检验
结论:
得到Z小于-1.886的概率是0.0297。
如果零假设为真（每月平均利润为70元），得到样本平均值为66元或小于它的概率约为3,，即有充分的证据说明新系统是一项节省资金的系统。
8.2小样本——使t分布的假设检验
家保险公司用代理的方式支付其客户，赔偿假定每年的平均代理赔偿费用为32000元，如果平均支付费用与计划不同，就需要对计划进行修改。
对一个有36个代理的样本，上一年的平均支付费用为27500元，标准偏差为8400元，如果整个公司的平均支付变化与该样本的情况不同，那么可用管理计划来修改
16
赔偿计划。
根据这一结果的P值，这一样本能充分说明平均值变化了吗,
本例已知均值标准差，下面各步骤描述了如何建立计算工作表:
1（打开一新工作表，输入A列所示标记。
2（要在C列的公式使用B列中的名称，选取单元B4:
C13，从插入菜单中选择名称指定，在指定名称对话框中复选名称创建于最左列，单击确定。
图8.3已知均值标准差计算公式---t假设检验的标记和公式
因为经理想知道平均支付是否发生了变化（不用指出变化的方向），所以备择假设为H1:
Mean?
32000，零假设为Ho:
Mean,32000。
由于数据已经总结过了，可以直接向工作表的单元中输入样本大小n、样本平均值、总体的标准偏差和假设总体的平均值。
尽管例中未指明显著水平可输入为0.05。
结论:
由于P=0.0028<0.05，所以，有足够的理由拒绝在显著水平为5,（双尾检验）时的零假设，可得出如下结论:
平均支付值不等于32000，明确说明平均值改变了。
17
图8.4t假设检验
例8.3（已知原始数据）一家制造商生产钢棒，为了提高质量，如果某新的生产工艺生产出的钢棒的断裂强度大于现有平均断裂强度标准的话，公司将采用该工艺。
当肪钢棒的平均断裂强度标准是500公斤。
对新工艺生产的钢捧进行抽样，12件棒材的断裂强度如下:
502，496，510，508，506，498，512，497，515，503，510和506，假设断裂强度的分布比较近似于正态分布，将样本数据画图，所画图形能表明平均断裂强度有所提高吗?
图8.5显示了假设检验所需的数据。
因为经理想检查是否有提高，备择假设为H1:
Mean,500，所以用右尾检验比较合适。
零假设为Ho:
Mean,500，或简单地Ho:
Mean=500。
如例8.2所述，D2:
D13单元已命名为Data，单元B5:
B7包含了公式COUNT（Data）,AVERAGE（Data）和STDEV（Data）。
尽管例5.5未指定一显著水平,在B10单元中入了显著水平为0.05。
包含了左尾检验结果的15到18行被隐藏。
18
图8.5小样本t假设检验公式
结论:
P=0.0131<0.05，说明有充分证据来拒绝零假设。
可得出如下结论:
新工艺在统计上可带来平均断裂强度的显著提高。
图8.6小样本t假设检验结果
19
第九章相关与回归简单线性相关分析
例9.1（9-1）
1（Excel进行相关分析:
（1）输数据:
将数据输入A1:
C9单元格。
（2）绘制散点图:
图9.1简单相关系数及散点图3.计算相关系数
（1）选择工具菜单之数据分析选项,在分析工具框中“相关系数”。
相关系数对话框将显示为图9.2所示，它带输入输出的提示。
20
图9.2相关系数对话框
1）输入
输入区域:
B1:
C9
分组方式:
逐列
选择标志位于第一行
2）输出选项
输出区域:
A13
（2）（单击确定，Excel将计算出结果显示在输出区域中。
4.相关系数假设检验
（1）在单元格F14中输入公式=B15/SQRT（（1-B15^2）/（8-2））计算得相关系数
的t值为49.46
（2）在单元格F15中输入公式=TDIST（ABS（F14）,B-2,2）计算得
p=0.0001
（3）结论:
由于r=-0.9689,且p<0.05,所以,在0.05水平上拒绝原假设,认为产品产量与单位成本间有负的线性相关关系
21
9.2简单回归分析
上面的简单相关分析只是说明两变量之间的线性关系密切的程度，如果要建立它们之间线性依存的关系式，就需用回归分析。
可按下列步骤使用“回归”分析工具:
1.输数据:
将数据输入A1:
C9单元格。
2.回归分析:
（1）选择工具菜单之数据分析选项,在分析工具框中“回归”。
回归对话
框将显示为图9.3所示，
图9.3回归分析对话框
1）输入
Y值输入区域:
C1:
C9
X值输入区域:
B1:
B9
标志:
选择
常数为零:
只有当用户想强制使回归线通过原点（0,0）时才选此框
22
置信度:
Excel自动包括了回归系数的95,置信区间。
要使用其他置信
区间,选择该框并在ConfidenceLevet框中输入置信水平2）输出选项
输出区域:
D1
3）残差
残差（R）:
选择此框可得到预测值和残差（Residual）。
残差图（D）:
选择此框可得到残差和每一x值的图表。
标准残差（T）:
选择此框可得到标准化的残差,每一残差被估计标准误
差除）。
这一输出可使曲线较容易分层。
线性拟合图（I）:
选择此框可得到一含有y输入数据和拟合的y值的散点
图。
4）正态概率图:
绘制因变量的正态概率图
（2）（单击确定，Excel将计算出结果显示在输出区域中。
图9.4回归分析结果
3.回归解释
拟合回归线的截距和斜率放在图9.4的总结输出中标记有“Coeffients’’的左下部。
截距系数77.30769是线性回归方程中的常数项，x系数-0.80769是斜
23
率。
回归方程是:
y,77.30769-0.80769*x
图9.5残差及拟合线
在图9.5所示的残差输出中，预测y，有时又称拟合值，是用这个回归方程计算的单位成本的估计值。
残差是实际值和拟合值之间的差值。
回答“拟合关系怎么样”问题的最通用的四个方法是标准误差，R2，t统计值和方差分析。
标准误差0.83205显示在图9.4的单元E7中。
作为残数的标准偏差，它衡量单位成本在回归线周围的分散情况，标准误差通常称为估计标准误差。
2R（RSquare），如图9.4的单元E5所示，衡量用回归线解释的因变量变化的比例。
这一比例必击是0和1之间的一个数据，经常以百分数表示。
这里，约有的94,的单位成本的变化是在线性方程中用产品产量做为预测因子来解释的。
单元E6显示的AdjustedRsquare在用附加解释变量把此模型和其他模型比较时很有用。
第十章时间序列分析指标
例10.1（10-2）1995-2000的销售额见下图B列，则其速度分析指标计算如下:
24
Excel计算公式如下:
（Ctrl+`切换）
例10.2（10-3）
25
计算步骤:
（1）各季平均每月总产值计算公式
说明单元格公式
一季B16=AVERAGE（B2:
B4）
二季B17=AVERAGE（B5:
B7）
三季B18=AVERAGE（B8:
B10）
四季B19=AVERAGE（B11:
B13）
全年B20=AVERAGE（B2:
B13）
（2）全年平均职工人数:
C16=（C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2）/13（3）月平均劳动生产率:
C17=B20/C16*10000
年平均劳动生产率:
C18=SUM（B2:
B13）/C16*10000
（4）全年职工构成指标:
C19=（D2/2+D3+D4+D5+D6+D7+D8+D9+D10+D11+D12+D13+D14/2）/
（C2/2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13+C14/2）*100例10.3（10-5）
计算步骤:
（1）计算每年的增长速度A2=1+A1/100,…
（2）5年平均增长速度F3=（PRODUCT（A2:
E2）^（1/5）-1）*100
26
（3）国民生产总值翻两翻需要时间F4=LOG（4,10）/LOG（1+F3/100,10）
第十一章时间数列预测方法
例11.1（11-1）
（1）计算按5日扩大时距的时间数列和计算按5日平均日产量的时间数列，结果如下表
Excel计算公式
（3）5日移动平均数。
按下列步骤使用“移动平均”分析工具:
1.选择工具菜单之数据分析选项,在分析工具框中“移动平均”。
移动平均对话框将显示为下图所示，它带输入输出的提示。
27
1）输入
输入区域:
B1:
B31
标志位于第一行
间隔:
5
2）输出选项
输出区域:
C1
2.单击确定，Excel将计算出结果显示在输出区域中。
28
例11.2（11-2）
（2）选择工具菜单之数据分析选项,在分析工具框中“指数平滑”。
指数平滑对话框将显示为图所示，它带输入输出的提示。
1）输入
输入区域:
B1:
B13
阻尼系树:
0.2
标志
2）输出选项
输出区域:
C2
图表输出
（2）（单击确定，Excel将计算出结果显示在输出区域中。
29
例11.3（11-6）
1（输入数据，绘制散点图
2（点击图中数据点，在菜单“图表”中添加趋势线，在对话框中选择2
阶多项式，并在选项中选中“显示公式”和“显示R平方值”
30
图表标题
24002y=10.206x-40620x+4E+0722002R=12000
1800
1600
1400
1200
1000
199********41996199820002002
例11.4（11-7）
1（求年合计和年平均:
M2=SUM（B2:
M2），。
。
。
N2
=AVERAGE（B2:
M2），。
。
。
2（求月合计和月平均:
B6=SUM（B2:
B5），。
。
。
B7
31
=AVERAGE（B2:
B5），。
。
。
3（求季节指数:
B8=B2/$O$2，。
。
。
例11.5（11-7）
1（计算线性趋势值T，D3=FORECAST（B3,C$3:
C$22,B$3:
B$22），
FORECAST函数直接给出以B$3:
B$22为自变量，C$3:
C$22为因变量
的线性回归的预测（估计）值，将公式拷贝到D22即的结果。
2（计算循环及不规则变动C?
I（%）E3=C3/D3*100，。
。
。
3（用循环不规则变动的3项移动平均计算循环变动C（%）:
F4=AVERAGE（E3:
E5），。
。
。
4（计算不规则变动I（%）:
G4=E4/F4*100，。
。
。
32
第十二章统计指数例12.1（12-1）
计算步骤:
33
（1）计算poq1:
F3=C3*E3,…
（2）计算p1q1:
G3=D3*E3,…
（3）计算?
poq1:
F6=SUM（F3:
F5）,?
p1q1:
G6=SUM（G3:
G5）,
（4）物价总指数%?
p1q1/?
poq1:
F7=G6/F6*100
（5）因物价变动而增减的销售额?
p1q1-?
poq1:
F8=G6-F6
例12.2（12-4）
计算可按例12.1步骤一步步进行，也可按下面的数组方式快速计算，例如销售量总指数计算公式为:
B7=SUM（（C3:
C5）*（F3:
F5））/SUM（（C3:
C5）*（E3:
E5））*100,
但要得到数组结果需用组合键Ctrl+Shift+Enter
例12.3（12-8）
34
Excel计算公式如下:
（Ctrl+`切换）
35
36
升级会员 