Excel与数据统计分析.doc

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/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提供的公式计算各种变异度指标（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元如果a＝0.05，有无充分的证据说明新系统不是一项节省资金的系统?假设总体的标准偏差为30元图8.1 正态假设检验的标记和公式---已知均值标准差计算公式 上图所示的工作表可用于正态分布平均值的左尾、右尾和双尾假设检验。

检验结果包括基于a判决法和P值报告法输入样本大小、样本平均值和标准偏差作为值、公式或引用，指定假设的平均值(Mean)和显著水平a作为值下面各步骤描述了如何建立该工作表： （1）打开一新工作表并输入B列所示标记 （2）要在C列的公式使用B列中的名称，选取单元B4:C12，从插入菜单中选择名称4指定，在指定名称对话框中复选名称创建于最左列，单击确定 （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，从插入菜单中选择名称4指定，在指定名称对话框中复选名称创建于最左列，单击确定图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单。