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所示。

图3.1 数据阐发东西之直方图对话框
1) 输入
输入区域：A1:A51
担当区域：B1:B22 (这些区中断点或界限必须按升序排列)
选择标记
2) 输出选项
输出区域: C1
选定图表输出
(2)．单击确定，Excel将盘算出结果显示在输出区域中。

图3.2 数据阐发东西之直方图对话框
Excel将把频率漫衍和直方图放在事情表中，如图3.3所示，输出表的C和D列中包罗开始指定的界限。

这些界限实际上是每一区间的上限，也就是说，界限实际上是界限。

图3.3 频数漫衍与直方图
为了使图表更像传统的直方图和更易于理解，可双击图表并对它做如下修改：1．图例：因为只有一个系列的数据显示在图表中，所以不需要图例。

单击图例(位于图表右侧的“频率”)并按Delete键。

2．图表区：画图区是以X和Y轴为界限的矩形区域。

通过在柱形上面单击可选取画图区，单击鼠标右键并选择画图区格式，将边框改为无并将区域改为无，单击确定。

3．条宽：在传统的直方图中，柱形是相互相连接而不是离开的。

选择某个柱形，单击鼠标右键，选择数据系列格式，并单击选择标签，将间距宽度从150％改为0％，单击确定。

4．X轴标记：选取x轴，单击鼠标右键，选择坐标轴格式，单击对齐标签，将偏向从自动改为水平文本，在这种设置下，纵然图表已重置尺寸，x轴标记也
4.6
图3.4 修改后的直方图
二、不指定担当区域直方图
在进行探索性阐发时，为了方便，通常不指定担当区域作直方图，步调如下：
（1
, 在阐发东西框中“直方图”。

如图4
所示。

1) 输入
输入区域：A1:A51
担当区域：(
该处为空)
选择标记
2) 输出选项
输出区域: B1
选定图表输出(2)
(3)按前面要领对直方图进行进一步修饰即得图3.5
图3.5 修改后的直方图3.2 计数数据的透视表与条图
例3.2（3-3）数据见图
步调如下：
（1）选择数据菜单之数据透视表和图表陈诉选项, 如图4所示。

(2)．选择数据源区域
（3）选定命据透视表位置，完成
（4）将“性别”作为行字段拖至G列，并将“性别”作为数据拖至数据项处，
得下表结果
同理可得“文化水平”的透视表
此时如点击图形按钮，立即得到如下的透视图
（5）将“性别”作为行字段拖至行字段处，并将“文化水平”作为列字段拖至列字段处，将“性别”或“文化水平”作为列字段拖至数据字段处得下表结果
第四章总量指标和相对指标
例4.1 (4-13)
盘算步调：
（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 加权平均数
例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/C8 G9=G8/D8
众数F10=A4+(C4-C3)/(C4-C3+C4-C5)*10 G10=A6+(D6-D5)/(D6-D5+D6-D7)*10 中位数F11=A4+(C8/2-A4)/C4*10 G11=A5+(D8/2-C5)/D5*10
第六章变异度指标
6.1 简朴变异度指标
例6.1（6-1）
变异度指标的盘算步调如下：
（1）将甲乙两组数据输入到A, B列，凭据Excel提供的公式盘算种种变
异度指标
（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 ,…并求和
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 盘算结果：
Excel 盘算公式：
（Ctrl+` 交换）
例7.3 (7-6) 泊松漫衍
盘算公式：
P(x>=5)=1-P(x<=4) Excel 盘算结果：
Excel 盘算公式：
（Ctrl+` 交换）
例7.4 (7-9) 超多少漫衍
例7.5 (7-10) 正态漫衍
其中 F2=1-D2, D4=D3-D2
第八章假设查验
8.1 大样本——使用正态漫衍的假设查验
例8.1 商店经理想为商店的持信用卡的主顾建一新的付款系统，经过详细的经济阐发，她判定如果新系统每月平均利润低于70元的话就不能有效地使用
资金。

于是随机抽取了200个月的利润，其平均月利润为66元。

如果α＝0.05，有无充实的证据说明新系统不是一项节省资金的系统?假设总体的尺度偏差
为30元。

图8.1 正态假设查验的标记和公式---已知均值尺度差盘算公式
上图所示的事情表可用于正态漫衍平均值的左尾、右尾和双尾假设查验。

查验结果包罗基于α判决法和P值陈诉法。

输入样本巨细、样本平均值和尺度偏差作为值、公式或引用，指定假设的平均值(Mean)和显著水平α作为值。

下面各步调描述了如何创建该事情表：
（1）打开一新事情表并输入B列所示标记。

（2）要在C列的公式使用B列中的名称，选取单位B4:C12，从插入菜单中选择名称 指定，在指定名称对话框中复选名称创建于最左列，单击确定。

（3）输入C列所示的公式(按图所示键入公式或通过单击适当的已命名的单位插入函数来创建公式)。

（4）要得图8.1的A列所示的外观，按Ctrl+`。

因为经理想知道平均月利润是否小于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元，如
果整个公司的平均支付变革与该样本的情况差别，那么可用治理筹划来修改赔偿筹划。

凭据这一结果的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，明确说明
平均值改变了。

图8.4 t假设查验
例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行被隐藏。

图8.5 小样本t假设查验公式
结论：P=0.0131<0.05，说明有充实证据来拒绝零假设。

可得出如下结论：新工艺在统计上可带来平均断裂强度的显著提高。

图8.6 小样本t假设查验结果
第九章相关与回归简朴线性相关阐发
例9.1 （9-1）
1．Excel进行相关阐发：
（1）输数据: 将数据输入A1:C9单位格。

（2）绘制散点图:
图9.1 简朴相干系数及散点图
3. 盘算相干系数
(1)选择东西菜单之数据阐发选项, 在阐发东西框中“相干系数”。

相干系数对话框将显示为图9.2所示，它带输入输出的提示。

图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水平上拒绝原假
设, 认为产物产量与单位本钱间有负的线性相关干系
9.2简朴回归阐发
上面的简朴相关阐发只是说明两变量之间的线性干系密切的水平，如果要
创建它们之间线性依存的干系式，就需用回归阐发。

可按下列步调使用“回归”阐发东西：
1. 输数据: 将数据输入A1:C9单位格。

2. 回归阐发:
(1) , 在阐发东西框中“回归”。

回归对话
框将显示为图9.3所示，
图9.3 回归阐发对话框
1) 输入
Y值输入区域：C1:C9
X值输入区域：B1:B9
标记: 选择
常数为零: 只有当用户想强制使回归线通过原点(0,0)时才选此框
置信度: Excel自动包罗了回归系数的95％置信区间。

要使用其他置信区间, 选择该框并在Confidence Levet框中输入置信水平
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是斜率。

回归方程是：
y＝77.30769-0.80769 * x
图9.5 残差及拟合线
在图9.5所示的残差输出中，预测 y，有时又称拟合值，是用这个回归方程盘算的单位本钱的预计值。

残差是实际值和拟合值之间的差值。

答复“拟合干系怎么样”问题的最通用的四个要领是尺度误差，R2，t统计值和方差阐发。

尺度误差0.83205显示在图9.4的单位E7中。

作为残数的尺度偏差，它权衡单位本钱在回归线周围的疏散情况，尺度误差通常称为预计尺度误差。

R2（R Square），如图9.4的单位E5所示，权衡用回归线解释的因变量变革的比例。

这一比例必击是0和1之间的一个数据，经常以百分数表现。

这里，约有的94％的单位本钱的变革是在线性方程中用产物产量做为预测因子来解释的。

单位E6显示的Adjusted R square在用附加解释变量把此模型和其他模型比力时很有用。

第十章时间序列阐发指标
例10.1 (10-2) 1995-2000的销售额见下图B列，则其速度阐发指标盘算如下：
Excel盘算公式如下:
(Ctrl+` 切换) 例10.2 (10-3)
盘算步调：
（1）各季平均每月总产值盘算公式
说明单位格公式
一季B16 = A VERAGE(B2:B4)
二季B17 = A VERAGE(B5:B7)
三季B18 = A VERAGE(B8:B10)
四季B19 = A VERAGE(B11:B13)
全年B20 = A VERAGE(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
（3）百姓生产总值翻两翻需要时间F4 = LOG(4,10)/LOG(1+F3/100,10)
第十一章时间数列预测要领
例11.1 (11-1)
（1）盘算按5日扩大时距的时间数列和盘算按5日平均日产量的时间数列，结果如下表
Excel盘算公式
（3）5日移动平均数。

按下列步调使用“移动平均”阐发东西：
1.选择东西菜单之数据阐发选项, 在阐发东西框中“移动平均”。

移动平均对话框将显示为下图所示，它带输入输出的提示。

1) 输入
输入区域：B1:B31
标记位于第一行
隔断：5
2) 输出选项
输出区域: C1
2. 单击确定，Excel将盘算出结果显示在输出区域中。

例11.2 (11-2)
(2)选择东西菜单之数据阐发选项, 在阐发东西框中“指数平滑”。

指数平滑对话框将显示为图所示，它带输入输出的提示。

1) 输入
输入区域：B1:B13
阻尼系树：0.2
标记
2) 输出选项
输出区域: C2
图表输出
(2)．单击确定，Excel将盘算出结果显示在输出区域中。

例11.3 (11-6)
1．输入数据，绘制散点图
2．点击图中数据点，在菜单“图表”中添加趋势线，在对话框中选择2阶多项式，并在选项中选中“显示公式”和“显示R平方值”
图表标题
y = 10.206x 2 - 40620x + 4E+07
R 2 = 1
1000
1200
1400
1600
1800
200022002400
1990199219941996199820002002
例11.4 (11-7)
1． 求年合计和年平均：M2 = SUM(B2:M2)，。

N2 =A VERAGE(B2:M2)，。

2． 求月合计和月平均：B6 =SUM(B2:B5)，。

B7
=A VERAGE(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，。

第十二章统计指数例12.1 (12-1)
盘算步调：
（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)
Excel盘算公式如下:
(Ctrl+` 切换)。