excel在统计中应用-.pdf
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，单击确定。4X轴标志：选取x 轴，单击鼠标右键，选择坐标轴格式，单击对齐标签，将方向从自动改为水平文本，在这种设置下，即使图表已重置尺寸，x 轴标记也会变为水平的，单击确定。最后的直方图4.6 图 3.4 修改后的直方图二、不指定接受区域直方图在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如下：（1）选择工具菜单之数据分析选项,在分析工具框中“直方图”。如图4 所示。1)输入输入区域：A1:A51 接受区域：(该处为空)选择标志 2)输出选项输出区域:B1 选定图表输出(2)单击确定，得结果。(3)按前面方法对直方图进行进一步修饰即得图3.5 直方图01234567100120140160180200220240260280300组距频率图 3.5 修改后的直方图3.2 计数数据的透视表与条图例 3.2（3-3）数据见图步骤如下：（1）选择数据菜单之数据透视表和图表报告选项,如图 4 所示。(2)选择数据源区域（3）选定数据透视表位置，完成（4）将“性别”作为行字段拖至G 列，并将“性别”作为数据拖至数据项处，得下表结果同理可得“文化程度”的透视表此时如点击图形按钮，立即得到如下的透视图（5）将“性别”作为行字段拖至行字段处，并将“文化程度”作为列字段拖至列字段处，将“性别”或“文化程度”作为列字段拖至数据字段处得下表结果第四章总量指标和相对指标例 4.1(4-13)计算步骤：（1）计算各厂计划完成%E3=D3/C3*10 0,（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(x5)=P(x=10)-P(x=9)=1-P(x9)=1-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：Mean70，零假设为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.00280.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：Mean500，所以用右尾检验比较合适。零假设为Ho：Mean500，或简单地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.01310.05，说明有充分证据来拒绝零假设。可得出如下结论：新工艺在统计上可带来平均断裂强度的显著提高。图 8.6 小样本 t 假设检验结果第九章相关与回归简单线性相关分析例 9.1（9-1）1Excel 进行相关分析：（1）输数据:将数据输入A1:C9 单元格。（2）绘制散点图:图 9.1 简单相关系数及散点图3.计算相关系数(1)(1)选择工具菜单之数据分析选项,在分析工具框中“相关系数”。相关系数对话框将显示为图9.2 所示，它带输入输出的提示。图 9.2 相关系数对话框1)输入输入区域：B1:C9 分组方式：逐列选择标志位于第一行 2)输出选项输出区域:A13(2)单击确定，Excel 将计算出结果显示在输出区域中。4.相关系数假设检验(1)(1)在单元格F14 中输入公式 =B15/SQRT(1-B152)/(8-2)计算得相关系数的t 值为49.46(2)(2)在单元格F15 中输入公式 =TDIST(ABS(F14),B-2,2)计算得 p=0.0001(3)结论:由于 r=-0.9689,且 p0.05,所以,在 0.05 水平上拒绝原假设,认为产品产量与单位成本间有负的线性相关关系9.2 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是斜率。回归方程是：y77.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=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（3）国民生产总值翻两翻需要时间F4=LOG(4,10)/LOG(1+F3/100,10)第十一章 时间数列预测方法例 11.1(11-1)（1）计算按5 日扩大时距的时间数列和计算按5 日平均日产量的时间数列，结果如下表Excel 计算公式（3）5 日移动平均数。按下列步骤使用“移动平均”分析工具：1.1.选择工具菜单之数据分析选项,在分析工具框中“移动平均”。移动平均对话框将显示为下图所示，它带输入输出的提示。1)输入输入区域：B1:B31 标志位于第一行间隔：5 2)输出选项输出区域:C1 2.单击确定，Excel 将计算出结果显示在输出区域中。例 11.2(11-2)(2)(2)选择工具菜单之数据分析选项,在分析工具框中“指数平滑”。指数平滑对话框将显示为图所示，它带输入输出的提示。1)输入输入区域：B1:B13 阻尼系树：0.2 标志 2)输出选项输出区域:C2 图表输出 (2)单击确定，Excel 将计算出结果显示在输出区域中。例 11.3(11-6)1 1 输入数据，绘制散点图2 2 点击图中数据点，在菜单“图表”中添加趋势线，在对话框中选择2 阶多项式，并在选项中选中“显示公式”和“显示R 平方值”图表标题y=10.206x2-40620 x+4E+07R2=1100012001400160018002000220024001990199219941996199820002002例 11.4(11-7)1 1 求年合计和年平均：M2=SUM(B2:M2)，。N2=AVERAGE(B2:M2)，。2 2 求月合计和月平均：B6=SUM(B2:B5)，。B7=AVERAGE(B2:B5)，。3 3 求季节指数：B8=B2/$O$2，。例 11.5(11-7)1 1 计算线性趋势值T，D3=FORECAST(B3,C$3:C$22,B$3:B$22)，FORECAST 函数直接给出以B$3:B$22 为自变量，C$3:C$22 为因变量的线性回归的预测（估计）值，将公式拷贝到D22 即的结果。2 2 计算循环及不规则变动CI（%）E3=C3/D3*100，。3 3用 循 环 不 规 则 变 动 的3项 移 动 平 均 计 算 循 环 变 动C(%)：F4=AVERAGE(E3:E5)，。4 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+切换)
