Excel与数据统计管理分析

1、Excel与数据统计分析 统计计算与统计分析强调与计算机密切结合，Excel与数据统计分析旨在提高学生计算机的综合运用能力，用统计方法分析问题、解决问题而编写的。根据教材内容，也可以选择使用SPSS、QSTAT、Evievs、SAS、MINITAB等统计软件。第三章 统计整理3.1 计量数数据的频频数表与与直方图图例3.1 (3-1) 一、指定接接受区域域直方图图 在应用此此工具前前，用户户应先决决定分布布区间。否否则，EExceel将用用一个大大约等于于数据集集中某数数值的平平方根作作区间，在在数据集集的最大大值与最最小值之之间用等等宽间隔隔。如果果用户自自己定义义区间，可可用2、5或10的

2、倍倍数，这这样易于于分析。 对对于工资资数据，最最小值是是1000，最大大值是2298。一一个紧凑凑的直方方图可从从区间1100开开始，区区间宽度度用100，最后后一区间间为3000结束束，需要要21个个区间。这这里所用用的方法法在两端端加了一一个空区区间，在在低端是是区间“1000或小于于1000”，高端端是区间间“大于3300”。 参参考图33.3，利利用下面面这些步步骤可得得到频率率分布和和直方图图： 11为了了方便，将将原始数数据拷贝贝到新工工作表“指定频频数直方方图”中。2在B11单元中中输入“组距”作为一一标记，在在B2单元中中输入1100，B3单元中中输入1110，选选取B22:

3、B3，向下下拖动所所选区域域右下角角的+到B22单元。3按下列列步骤使使用“直方图图”分析工工具：（1）选择择工具菜单单之数据据分析选选项, 在分析析工具框框中“直方图图”。如图图4所示示。图3.1 数据据分析工工具之直直方图对对话框1) 输入入输入区域：A1:A511 接受区域：B1:B222 (这些区区间断点点或界限限必须按按升序排排列)选择标志 2) 输出选选项 输出区区域: C1 选定定图表输输出(2)单单击确定定，Exccel将将计算出出结果显显示在输输出区域域中。图3.2 数据据分析工工具之直直方图对对话框Excell将把频频率分布布和直方方图放在在工作表表中，如如图3.3所示示，

4、输出出表的CC和D列列中包括括开始指指定的界界限。这这些界限限实际上上是每一一区间的的上限，也也就是说说，界限限实际上上是边界界。图3.3 频数数分布与与直方图图为了使图表表更像传传统的直直方图和和更易于于理解，可可双击图图表并对对它做如如下修改改：1图例：因为只只有一个个系列的的数据显显示在图图表中，所所以不需需要图例例。单击击图例(位于图图表右侧侧的“频率”)并按Deelette键。2图表区区：绘图图区是以以X和Y轴为边边界的矩矩形区域域。通过过在柱形形上面单单击可选选取绘图图区，单单击鼠标标右键并并选择绘绘图区格格式，将将边框改改为无并将区域域改为无，单击击确定。3条宽：在传统统的直方方

5、图中，柱柱形是彼彼此相连连接而不不是分开开的。选选择某个个柱形，单单击鼠标标右键，选选择数据据系列格格式，并并单击选选择标签签，将间间距宽度度从1500改为为0，单单击确定定。4X轴标标志：选选取x轴，单单击鼠标标右键，选选择坐标标轴格式式，单击击对齐标签签，将方方向从自自动改为为水平文文本，在在这种设设置下，即即使图表表已重置置尺寸，x轴标记也会变为水平的，单击确定。最后的直方图4.6图3.4 修改改后的直直方图二、不指定定接受区区域直方方图 在进进行探索索性分析析时，为为了方便便，通常常不指定定接受区区域作直直方图，步步骤如下下：（1）选择择工具菜单单之数据据分析选选项, 在分析析工具框框

6、中“直方图图”。如图图4所示示。1) 输入入输入区域：A1:A511 接受区域：(该处为为空)选择标志 2) 输出选选项 输输出区域域: BB1 选选定图表表输出(2)单单击确定定，得结结果。(3)按前前面方法法对直方方图进行行进一步步修饰即即得图33.5图3.5 修改改后的直直方图3.2 计数数数据的透透视表与与条图例3.2（33-3）数数据见图图步骤如下：（1）选择择数据菜单单之数据据透视表表和图表表报告选选项, 如图44所示。(2)选选择数据据源区域域（3）选定定数据透透视表位位置，完完成（4）将“性别”作为行行字段拖拖至G列列，并将将“性别”作为数数据拖至至数据项项处，得得下表结结果同

7、理可得“文化程程度”的透视视表此时如点击击图形按按钮，立立即得到到如下的的透视图图（5）将“性别”作为行行字段拖拖至行字字段处，并并将“文化程程度”作为列列字段拖拖至列字字段处，将将“性别”或“文化程程度”作为列列字段拖拖至数据据字段处处得下表表结果第四章 总总量指标标和相对对指标例4.1 (4-13) 计算步步骤：（1）计算算各厂计计划完成成% E3=D3/C3*1000, （2）20000年年实际产产量为119999年的% F3=D3/B3*1000, 第五章 平平均指标标 5.1 简单平平均数例5.1某组有有学生110人统统计课考考试成绩绩为655，822，766，800，822，866

8、，844，888，955，988分，试试求其平平均指标标。平均数的计计算步骤骤如下：（1）将数数据输入入到A列列，根据据Exccel提提供的公公式计算算各种平平均数（2）用CCtrll+ 可切换换到下面面的结果果：5.2 加加权平均均数例5.2（55-1）原原始数据据见下图图A-DD列，其其中A、BB列放日日产量的的下限和和上限平均数的计计算步骤骤如下：（1）计算算日产量量的组中中值 EE3=(A3+B3)/2, （2）计算算每个组组段的总总产量 F3=C3*E3, G33=D33*E33, （3）计算算每月的的总产量量 F88=SUUM(FF3:FF7), G88=Suum(GG3:GG7)

9、, （4）计算算平均数数公式如如下：均数F9=F88/C88G9=G88/D88众数F10=AA4+(C4-C3)/(CC4-CC3+CC4-CC5)*10G10=AA6+(D6-D5)/(DD6-DD5+DD6-DD7)*10中位数F11=AA4+(C8/2-AA4)/C4*10G11=AA5+(D8/2-CC5)/D5*10第六章 变异度度指标 6.1 简单变变异度指指标例6.1（66-1）变异度指标标的计算算步骤如如下：（1）将甲甲乙两组组数据输输入到AA, BB列，根根据Exxcell提供的的公式计计算各种种变异度度指标（2）用CCtrll+ 可切换换到下面面的公式式：6.2 加加权变

10、异异度指标标例6.2（66-2）甲甲品种的的原始数数据见下下图B-C列，乙乙品种的的原始数数据见下下图G-H列下面以甲品品种的数数据计算算为例：（1）计算算单产值值 DD4=CC4/BB4, （2）计算算单产均均值 D9=C9/B9（3）计算算次数XX离差平平方 EE4=BB4*(D4-$D$9)2 , 并求求和 EE9=SSUM(E4:E8)（4）计算算标准差差：D111=SSQRTT(E99/B99)=668.991（5）计算算变异系系数：DD12=D111/D99*1000=66.9%同理可得乙乙品种的的标准差差为1662.771, 变异系系数为116.330%第七章 抽抽样调查查例7.

11、1 (7-5) 期望求 E(XX)的公公式 BB4=SSUM(B11:F11)*(B2:F2), 由于此此处用到到数组乘乘积求和和，所以以要得到到结果，需需用Cttrl+Shiift+Entter组组合键。例7.2 (7-6) 二项分分布 计计算公式式：P(5=x=10)=P(x=10)-P(x55)=PP(x=100)-PP(x=9)=1-PP(x9)=1-PP(x=5)=1-PP(x=4)Excell 计算算结果：Excell 计算算公式： （Cttrl+ 互互换）例7.4 (7-9) 超几何何分布例7.5 (7-10) 正态态分布 其中中 F22=1-D2, DD4=DD3-DD2 第八

12、章 假设设检验8.1 大大样本使用用正态分分布的假假设检验验例8.1 商店经经理想为为商店的的持信用用卡的顾顾客建一一新的付付款系统统，经过过详细的的经济分分析，她她判定如如果新系系统每月月平均利利润低于于70元的的话就不不能有效效地使用用资金。于于是随机机抽取了了2000个月的的利润，其其平均月月利润为为66元。如如果0.05，有有无充分分的证据据说明新新系统不不是一项项节省资资金的系系统?假设总总体的标标准偏差差为300元。图8.1 正态态假设检检验的标标记和公公式-已知知均值标标准差计计算公式式 上上图所示示的工作作表可用用于正态态分布平平均值的的左尾、右右尾和双双尾假设设检验。检检验结

13、果果包括基基于判决决法和PP值报告告法。输输入样本本大小、样样本平均均值和标标准偏差差作为值值、公式式或引用用，指定定假设的的平均值值(Meean)和显著著水平作作为值。下面各步骤骤描述了了如何建建立该工工作表： （1）打开开一新工工作表并并输入BB列所示示标记。 （2）要在在C列的公公式使用用B列中的的名称，选选取单元元B4:C122，从插入入菜单中中选择名名称指定定，在指指定名称称对话框框中复选选名称创创建于最最左列，单单击确定定。 （3）输入入C列所示示的公式式(按图所所示键入入公式或或通过单单击适当当的已命命名的单单元插入入函数来来建立公公式)。（4）要得得图8.1的AA列所示示的外观

14、观，按Ctrrl+。因为经理想想知道平平均月利利润是否否小于770元，所所以备择择假设为为Hd：Meaan70，零零假设为为Ho：Meaan70或简简单地为为Ho：Meann700。由于于数据已已经总结结过了，可可直接在在工作表表单元中中输入样样本大小小n、样本本平均值值、总体体的标准准偏差、假假设总体体平均值值和显著著水平。图8.2 正态态假设检检验结论：得到到Z小于-1.8866的概率率是0.02997。如如果零假假设为真真(每月平平均利润润为700元)，得得到样本本平均值值为666元或小小于它的的概率约约为3，即即有充分分的证据据说明新新系统是是一项节节省资金金的系统统。8.2 小小样本

15、使t分布的的假设检检验家保险公司司用代理理的方式式支付其其客户，赔赔偿假定定每年的的平均代代理赔偿偿费用为为320000元元，如果果平均支支付费用用与计划划不同，就就需要对对计划进进行修改改。对一一个有336个代代理的样样本，上上一年的的平均支支付费用用为2775000元，标标准偏差差为84400元元，如果果整个公公司的平平均支付付变化与与该样本本的情况况不同，那那么可用用管理计计划来修修改赔偿偿计划。根根据这一一结果的的P值，这这一样本本能充分分说明平平均值变变化了吗吗？本例已知均均值标准准差，下下面各步步骤描述述了如何何建立计计算工作作表：1打开一一新工作作表，输输入A列所示示标记。2要在

16、CC列的公公式使用用B列中的的名称，选选取单元元B4:C133，从插入入菜单中中选择名名称指定定，在指指定名称称对话框框中复选选名称创创建于最最左列，单单击确定定。图8.3 已知知均值标标准差计计算公式式-t假设检检验的标标记和公公式因为经理想想知道平平均支付付是否发发生了变变化(不用指指出变化化的方向向)，所以以备择假假设为HH1：Meaan320000，零零假设为为Ho：Meaan320000。由由于数据据已经总总结过了了，可以以直接向向工作表表的单元元中输入入样本大大小n、样本本平均值值、总体体的标准准偏差和和假设总总体的平平均值。尽尽管例中中未指明明显著水水平可输输入为00.05。 结

17、结论：由由于P=0.0002880.05，所所以，有有足够的的理由拒拒绝在显显著水平平为5(双尾检检验)时的零零假设，可可得出如如下结论论：平均均支付值值不等于于320000，明明确说明明平均值值改变了了。图8.4 t假假设检验验例8.3（已知知原始数数据）一一家制造造商生产产钢棒，为为了提高高质量，如如果某新新的生产产工艺生生产出的的钢棒的的断裂强强度大于于现有平平均断裂裂强度标标准的话话，公司司将采用用该工艺艺。当肪肪钢棒的的平均断断裂强度度标准是是5000公斤。对对新工艺艺生产的的钢捧进进行抽样样，122件棒材材的断裂裂强度如如下：5502，4496，5100，5088，5066，498

18、8，5122，4977，5155，5033，510和和5066，假设设断裂强强度的分分布比较较近似于于正态分分布，将将样本数数据画图图，所画画图形能能表明平平均断裂裂强度有有所提高高吗?图8.5显显示了假假设检验验所需的的数据。 因为经经理想检检查是否否有提高高，备择择假设为为H1：Meann5000，所以以用右尾尾检验比比较合适适。零假假设为HHo：Meaan5000，或简简单地HHo：Meann=5000。如如例8.2所述述，D2：D13单元已已命名为为Datta，单单元B55:B7包含含了公式式COUNTT(Daata), AVEERAGGE(DDataa)和STDDEV(Datta)。

19、尽尽管例55.5未未指定一一显著水水平, 在B100单元中中入了显显著水平平为0.05。包包含了左左尾检验验结果的的15到18行被被隐藏。图8.5 小样样本t假设检检验公式式 结结论：PP=0.0133100.055，说明明有充分分证据来来拒绝零零假设。可可得出如如下结论论：新工工艺在统统计上可可带来平平均断裂裂强度的的显著提提高。图8.6 小样样本t假设检检验结果果第九章 相关与与回归简单线性相相关分析析例9.1 （9-1）1Exccel进进行相关关分析： （1）输数数据: 将数据据输入AA1:CC9单元元格。（2）绘制制散点图图: 图9.1 简单单相关系系数及散散点图3. 计算算相关系系数

20、 选择工具具菜单之之数据分分析选项项, 在在分析工工具框中中“相关系系数”。相关系数对对话框将将显示为为图9.2所示示，它带带输入输输出的提提示。图9.2 相关系系数对话话框1) 输入入输入区域：B1:C9 分组方式：逐列选择标志位位于第一一行 2) 输出选选项 输输出区域域: AA13(2)单单击确定定，Exccel将将计算出出结果显显示在输输出区域域中。4. 相关关系数假假设检验验在单元格FF14中中输入公公式 =B155/SQQRT(1-B1552)/(88-2) 计计算得相相关系数数的t值为449.446在单元格FF15中中输入公公式 =TDIIST(ABSS(F114),B-2,2)

21、 计算得得 p=0.000011(3) 结结论: 由于rr=-0.996899, 且p0.05, 所以, 在00.055水平上上拒绝原原假设, 认为为产品产产量与单单位成本本间有负负的线性性相关关关系 简单回归归分析上面的简单单相关分分析只是是说明两两变量之之间的线线性关系系密切的的程度，如如果要建建立它们们之间线线性依存存的关系系式，就就需用回回归分析析。可按按下列步步骤使用用“回归”分析工工具：1. 输数数据: 将数据据输入AA1:CC9单元元格。2. 回归归分析: (1) 选选择工具具菜单之之数据分分析选项项, 在在分析工工具框中中“回归”。回归归对话框框将显示示为图99.3所示示， 图

22、9.3 回归归分析对对话框1) 输入入Y值输入区区域：CC1:CC9 X值输入区区域：BB1:BB9 标志: 选选择常数为零: 只有有当用户户想强制制使回归归线通过过原点(0,0)时才选选此框置信度: Exccel自自动包括括了回归归系数的的95置置信区间间。要使使用其他他置信区区间, 选择该该框并在在Connfiddencce LLeveet框中中输入置置信水平平2) 输出出选项 输出区区域: D1 3) 残差残差(R)：选择择此框可可得到预预测值和和残差(Ressiduual)。残差图(DD): 选择此此框可得得到残差差和每一一x值的图图表。标准残差(T)：选择此此框可得得到标准准化的残残

23、差,每每一残差差被估计计标准误误差除)。这一一输出可可使曲线线较容易易分层。线性拟合图图(I)：选择择此框可可得到一一含有yy输入数数据和拟拟合的yy值的散散点图。4) 正态态概率图图: 绘制制因变量量的正态态概率图图(2)单单击确定定，Exccel将将计算出出结果显显示在输输出区域域中。图9.4 回归归分析结结果3. 回归归解释 拟合回回归线的的截距和和斜率放放在图99.4的的总结输输出中标标记有“Coeeffiientts的左下下部。截截距系数数77.307769是是线性回回归方程程中的常常数项，x系数-00.8007699是斜率率。回归归方程是是：y77.307769-0.8807669

24、 * x图9.5 残差及及拟合线线在图9.55所示的的残差输出中中，预测测 y，有有时又称称拟合值值，是用用这个回回归方程程计算的的单位成成本的估估计值。残差差是实际际值和拟拟合值之之间的差差值。回答“拟合合关系怎怎么样”问题的的最通用用的四个个方法是是标准误误差，RR2，t统计值值和方差差分析。标标准误差差0.8832005显示示在图99.4的单元元E7中。作作为残数数的标准准偏差，它它衡量单单位成本本在回归归线周围围的分散散情况，标标准误差差通常称称为估计计标准误误差。 R2（R Squarre），如如图9.4的单单元E55所示，衡衡量用回回归线解解释的因因变量变变化的比比例。这这一比例例

25、必击是是0和11之间的的一个数数据，经经常以百百分数表表示。这这里，约约有的994的的单位成成本的变变化是在在线性方方程中用用产品产产量做为为预测因因子来解解释的。单单元E66显示的的Adjusstedd R squuaree在用附附加解释释变量把把此模型型和其他他模型比比较时很很有用。第十章 时时间序列列分析指指标例10.11 (110-22) 19995-220000的销售售额见下下图B列列，则其其速度分分析指标标计算如如下：Excell计算公公式如下下:(Ctrll+ 切换)例10.22 (110-33) 计计算步骤骤：（1）各季季平均每每月总产产值计算算公式 说明 单单元格 公式一季

26、BB16 = AVVERAAGE(B2:B4)二季 BB17 = AVVERAAGE(B5:B7)三季 BB18 = AVVERAAGE(B8:B100)四季 BB19 = AVVERAAGE(B111:B133)全年 BB20 = AVVERAAGE(B2:B133)（2）全年年平均职职工人数数： C16 = (C2/2+CC3+CC4+CC5+CC6+CC7+CC8+CC9+CC10+C111+C112+CC13+C144/2)/133（3）月平平均劳动动生产率率： CC17 =B220/CC16*100000 年平均均劳动生生产率： C118 =SUMM(B22:B113)/C166*1

27、000000（4）全年年职工构构成指标标： C19 = (DD2/22+D33+D44+D55+D66+D77+D88+D99+D110+DD11+D122+D113+DD14/2)/(C2/22+C33+C44+C55+C66+C77+C88+C99+C110+CC11+C122+C113+CC14/2)*1000例10.33 (10-5) 计算步骤：（1）计算算每年的的增长速速度 AA2=11+A11/1000, （2）5年年平均增增长速度度 F3 = (PROODUCCT(AA2:EE2)(1/5)-1)*1000（3）国民民生产总总值翻两两翻需要要时间 FF4 = LOOG(44,10

28、0)/LLOG(1+FF3/1100,10)第十一章 时间间数列预预测方法法例11.11 (111-1)（1）计算算按5日日扩大时时距的时时间数列列和计算算按5日日平均日日产量的的时间数数列，结结果如下下表Excell计算公公式（3）5日日移动平平均数。按下列步骤骤使用“移动平平均”分析工工具：选择工具菜菜单之数数据分析析选项, 在分分析工具具框中“移动平平均”。移动平均对对话框将将显示为为下图所所示，它它带输入入输出的的提示。1) 输入入输入区域：B1:B311 标志位于第第一行间隔：5 2) 输出选选项 输出出区域: C11 2. 单击确确定，Exccel将将计算出出结果显显示在输输出区域域中。 例11.22 (111-2)选择工具菜菜单之数数据分析析选项, 在分分析工具具框中“指数平平滑”。指数平滑对对话框将将显示为为图所示示，它带带输入输输出的提提示。1) 输入入输入区域：B1:B133 阻尼系树：0.22 标志 2) 输出选选项 输输出区域域: CC2 图表输输出 (2)单击击确定，Exccel将将计算出出结果显显示在输输出区域域中