excel在统计中的应用.pdf

ExcelExcel 在统计中的应用在统计中的应用Microsoft Excel 是美国微软公司开发的 Office 办公软件中最重要的组件之一，它是目前应用最为广泛的表格处理软件之一随着 Excel 版本的不断提高，统计分析功能也逐渐强大，目前通过加载宏添加的数据分析工具使复杂的统计分析变得快捷和易于计算完成与标准统计学软件与标准统计学软件 SASSAS、、SPSSSPSS 等相比较，等相比较，ExcelExcel 的最大优势在于的最大优势在于普及率高、容易得到：普及率高、容易得到：正规渠道购买的电脑内大多数预装有 Office 办公软件，且为中文界面，使用它进行统计分析物版权问题强大的数据填充功能：这使在强大的数据填充功能：这使在 Excel 工作表中输入数据变得相当简单使用简单，不用记许多特殊指令使用简单，不用记许多特殊指令“数据分析”工具可完成大多数常用的统计分析“数据分析”工具可完成大多数常用的统计分析：对于较复杂的统计学方法可简单利用内置函数自编程序完成其缺点其缺点：自动化程度不高，功能不够强大，有些统计计算不能直接完成一一利用分析工具进行描述性统计利用分析工具进行描述性统计二二样本直方图样本直方图三三假设检验假设检验四四方差分析方差分析五五相关与回归相关与回归一、一、 利用分析工具进行描述性统计利用分析工具进行描述性统计此分析工具用于对输入区域中数据的单变量分析， 并提供数据趋中性趋中性和易变性易变性等有关信息。

例 1.某班 20 名学生考试成绩单如下表，试用分析工具中的描述统计对班级成绩进行分析汇总，并给出相关的统计指标操作步骤：操作步骤：1. 新建一个工作表〔表 1〕 2．选择“工具”菜单的“数据分析”子菜单，选择“描述统计”选项 ，单击确定出现描述性统计对话筐；3．出现“描述统计”对话框，对话框内各选项的含义如下：输入区域：输入区域：在此输入待分析数据区域的单元格范围一般情况下 Excel 会自动根据当前单元格确定待分析数据区域分组方式：分组方式： 如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”,“描述统计”工具可以同时对多列或多行数据进行统计分析标志位于第一行标志位于第一行/ /列：列：如果输入区域的第一行中包含标志项(变量名)，则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框；如果输入区域没有标志项， 则不需要选择该复选框， Excel 将在输出表中生成适宜数据标志输出区域：输出区域：填写输出结果表左上角单元格地址,用于控制输出结果的存放位置整个输出结果分为两列,左边一列包含统计标志项，右边一列包含统计值根据所选择的“分组方式”选项的不同，Excel 将为输入表中的每一行或每一列生成一个两列的统计表。

新工作表：新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始存放计算结果如果需要给新工作表命名，则在右侧编辑框中键入名称新工作簿：新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算结果汇总统计：汇总统计：指定输出表中生成以下统计结果，则选中此复选框这些统计结果有：平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏斜度、极差〔全距〕最小值、最大值、总和、样本个数均值置信度：均值置信度：假设需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度例如，假设置信度 95%计算出的总体样本均值置信区间为 10，则表示：在 5%的显著水平下总体均值的置信区间为(-10,+10〕 学习文档 仅供参考第第 K K 个最大个最大/ /小值：小值：如果需要输出每个区域的数据的第 k 个最大或最小值，则选中此复选框然后在右侧的编辑框中，输入 k 的数值 4．填写完“描述统计”对话框之后，按“确定”按扭即可结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中包括样本的平均值〔〕 ，标准误差()，组中值(Medium)，众数(Mode), 样本标准差(S)，样本方差(S2), 峰度值，偏度值，极差(Max-Min), 最小值(Min),最大值(Max),样本总和，样本个数〔n〕和一定显著水平下总体均值的置信区间。

二、样本直方图二、样本直方图在实际的问题中，总体的分布情况往往是不清楚的，利用样本资料通过作出适当的统计图作直观观查，当总体的数量指标是连续型随机变量时，可作样本频率密度直方图，作为总体概率密度函数的进似例 2.100 包颗粒剂每包称重如下，试推断每包颗粒剂重量的概率分布情况操作步骤：1. 新建一个工作表〔表2〕表2 中填入分好组： 〔1〕找出样本数据的最大值和最小值，这里是 0.78 和 1.06； 〔2〕确定分组的组距和组数 2．选择“工具”菜单的“数据分析”子菜单，选择“直方图”选项 ，单击确定出现直方图对话筐；3．出现“直方图”对话框，对话框内主要选项的含义如下：输入区域：输入区域：在此输入待分析数据区域的单元格范围接收区域〔可选〕接收区域〔可选〕 ：：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值这些值应当按升序排列只要存在的话， Excel 将统计在各个相邻边界直之间的数据出现的次数如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间标志：标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的数据标志。

输出区域：输出区域：在此输入结果输出表的左上角单元格的地址用于控制计算结果的显示位置如果输出表将覆盖已有的数据，Excel 会自动确定输出区域的大小并显示信息柏拉图：柏拉图：选中此复选框，可以在输出表中同时显示按降序排列频率数据如果此复选框被清除，Excel 将只按升序来排列数据累积百分比：累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线如果清除此选项，则会省略以上结果图表输出：图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表 4． 按需要填写完“直方图”对话框之后，按“确定”按扭即可结果说明：完整的结果包括三列数据和一个频率分布图，第一列是数值的区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比三三假设检验假设检验假设检验是统计推断中的重要内容 假设检验主要分为两种类型： 双侧尾检验和单侧尾检验当需要检验:H0：σ1=σ2 , HA：σ1≠σ2 H0：σ1=σ2 , HA：σ1＜σ2 H0：σ1=σ2 , HA：σ1＞σ2〔一〕两正态总体方差的假设检验〔一〕两正态总体方差的假设检验对于来自两个正态总体的样本，其总体方差分别为σ1 2 和σ 22，从两个总体中独立地抽取容量为 n1 和 n2 的样本，对应的样本方差分别为 S12 和 S22 ,假设需要检验σ12 =σ22 ,则可利用分析工具的 F 检验。

例 3.合成车间某中间体生产的工艺条件改革后， 收率似有提高， 但工人师傅反映新工艺的条件不易控制，收率波动较大，为此，对新老工艺分别抽取假设干批，结果如表所示试检验学习文档 仅供参考推断老师傅反应的问题是否属实单击“工具”菜单的“数据分析”子菜单，选择““F-F-检验——双样本方差”检验——双样本方差”结论： 由统计数据知道样本统计量F＝2.990066994,其对应的的概率P=0.06884784>0.05，说明抽样的结果不是小概率事件， 所以接受H0：σ12=σ22拒绝HA：σ1 2 ＞ σ 22说明工人师傅反映的问题显著有误的〔二〕两正态总体均数的假设检验〔二〕两正态总体均数的假设检验1 1、配比照较、配比照较例 4、某中医师用中药青木香治疗高血压患者，治疗前后的情况下表所示问该中药治疗高血压是否有效？操作步骤：1.新建一个工作表〔表 4〕2．单击“工具”菜单的“数据分析”子菜单，选择“t-检验——平均值的成对二样本分析”选项 ，单击确定出现 t-检验——平均值的成对二样本分析对话筐； 3. 在对话框中， 按要求填入相应选项， 单击确定按钮， 输出分析结果5. 结论： 由统计数据知道样本统计量 t＝- 5.81435,其对应的的概率 P= 0.000117 ＜0.01，说明抽样的结果是小概率事件，所以拒绝 H0： μ1 - μ2 = 0，接受 HA： μ1 -μ2 ≠0 .说明该中药对质量高血压有极显著效果。

2 2、成组比较、成组比较〔1〕已知σ1 2=σ 22例 5.例 3 中合成车间某中间体生产的工艺条件改革后，收率似有提高，为此，对新老工艺分别抽取假设干批，结果如〔表 3〕所示试推断检验新老工艺的收率是否有差异操作步骤：单击“工具”菜单的“数据分析”子菜单，选择 ““t-t-检验——双样本等方差假检验——双样本等方差假设”设”选项 ， 5. 结论：由统计数据知道样本统计量t＝- 5.04748,其对应的的概率P=0.0000992 〔9.92E-05〕 ＜0.01，说明抽样的结果是小概率事件，所以拒绝H0： μ1 - μ2 = 0，接受 HA： μ1 -μ2 ≠0 .说明新老工艺的收率有极显著差异〔2〕已知σ12 ≠σ 22单击“工具”菜单的“数据分析”子菜单，选择““t-t-检验——双样本等方差假设”检验——双样本等方差假设”选项〔三〕单个正态总体的假设检验〔三〕单个正态总体的假设检验例 6 、根据大量调查，已知健康成年男子脉搏均数为 72 次/分某医生在某山区抽查了 25名成年男子，其脉搏均数为 74.2 次/分，标准差为 6.5 次/分，能否据此认为该山区健康成年男子脉搏高于一般水平？操作步骤：1.在工作表中键入已知数据〔表 5〕2．计算 t 统计量及 P 值等：在 G4 中键入“=ABS(C4-C7)/D7*B7^0.5”;在 G5 中键入“=B7-1”求自由度;在 G6 中键入“=TDIST(G4,G5,1),求单侧 P 值;在 G7 中键入“=TDIST(G4,G5,2),求双侧 P 值;在 G8 中键入“=C7-C4’’,求 X-μ0;3.结果分析：根据专业知识，已知山区不会低于一般水平，故作单侧检验，t 值=1.692308，P 值〔单侧〕=0.052 ，按0.05 水平不拒绝 H0，可认为该山区健康成年男子脉搏与一般人群健康成年男子脉搏的差异无统计学意义。

四四方差分析方差分析单因素方差分析可用于检验两个或两个以上的总体均值相等的假设是否成立 该检验假定总体是服从正太分布的，总体方差是相等的，并且随机样本是独立的这种工具适用于完全随机化试验的结果分析例 8.某公司研制出 A,B,C,D4 种新型压片机， 在单位时间内每台压片机进行了 6 次压片操作,统计它们压片数量如下表，试在α=0.01 的显著水平下检验这 4 种设备在相同时间生产的数学习文档 仅供参考量是否存在显著差异结果分析：结果分析：按照如上的操作步骤即可得到计算结果其中表格的第二部分则是方差分析的结果SS 列分别给出了四个分组的组间方差、组内方差以及总方差，DF 列分别给出了对应方差的自由度， MS 列是平均值方差，由 SS 除于 DF 得到，它是总体方差的两个估计值F 列是 F 统计量的计算结果，如果四个总体均值相等的假设成立的化，它应该服从F 分布，即近似为 1，它是最终的计算结果，通过将它与一定置信水平下的F 临界值 F crit 比较，可以判断均值相等的假设是否成立，在本例中，1.67761<2.94668 ,所以不能拒绝四个总体均值相等的假设P-value 列，是单尾概率值，说明如果四个总体均值相等的假设成立的化，得到如上样本结果的概率是 19.442% ,即得到以上样本并不是小概率事件，同样也得到不能拒绝四个总体均值相等的假设的结论。

按相似方法可进行无重复双因素方差分析，有重复双因素方差分析五五相关与回归相关与回归相关分析按照讨论的相关变量多少可分为简单相关和多元相关又称复相关，简单相关是指一个因变量与一个自变量的相关关系， 而多元相关则是一个因变量与两个或两个以上自变得相关关系这里着重讨论如何应用 EXCEI 确定和度量变量间的简单线性相关与回归分析〔一〕散点图对于两变量相关关系确实定， 可以采用散点图的方法， 通过在 EXCEI 重绘出两变量的散点图，根据散点分布确定两变量的相关关系，当然散点图仅能定性地确定出相关关系，不能给出定量的度量例 9 用双波长薄层扫描仪对紫草含量进行测定，得气浓度 c 与测得积分值 h 的数据如下表试作出浓度 c 与测得积分值 h 的散点图操作步骤： 1.新建一个工作表 〔表 10〕 2． 单击“插入”菜单的“图表”子菜单， 选择“XY-散点图”选项 ，在“子图表类型”选定最上段类型， 单击“下一步” 3.出现“源数据”---选定“数据区域”---“列”---“下一步”4.在“图表向导 4 步骤 3”对话筐中， 单击“网格线”---删去“主要网格 线”---单击“下一步”输出结果〔二〕相关系数使用“相关系数”分析工具来确定两个区域中数据的变化是否相关，即，一个集合的较大数据是否与另一个集合的较大数据相对应〔正相关〕 ；或者一个集合的较小数据是否与另一个集合的较小数据相对应〔负相关〕 ；还是两个集合中的数据互不相关〔相关性为零〕 。

操作步骤： 1. 新建一个工作表〔表 9〕2．单击“工具”菜单的“数据分析”子菜单，选择“相关系数”选项 ， 3 . 填写完“相关系数”对话框，单击“确定”按扭即可得到各个变量的相关系数矩阵〔三〕回归方程线性回归分析通过使用“最小二乘法”对样本数据进行直线拟合， 用于分析单个因变量是如何受一个或几个自变量影响的操作步骤： 1.新建一个工作表〔表 9〕 2．单击“工具”菜单的“数据分析”子菜单，选择“回归”选项 ，弹出回归分析对话框3 . 对话框主要选项的含义如下：Y Y 值输入区域：值输入区域：输入因变量数据区域，该区域必须由单列数据组成；X X 值输入区域值输入区域: : 输入对自变量数据区域，自变量的个数最多为 16；置信度置信度: :如果需要在汇总输出表中包含附加的置信度信息，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度，95%为默认值；常数为零常数为零: :如果要强制回归线通过原点，则选中此复选框；学习文档 仅供参考输出区域输出区域: :在此输入输出表左上角单元格的地址，用于控制计算结果的输出位置汇总输出汇总输出表至少需要有七列的宽度，包含的内容有 anova 表、系数、y 估计值的标准误差、r2 值、观察值个数，以及系数的标准误差；新工作表新工作表:单击此选项，可在当前工作簿中插入新工作表，并由新工作表的 A1 单元格开始显示计算结果，如果需要给新工作表命名，则在右侧的编辑框中键入名称；新工作簿新工作簿: :单击此选项，可创建一新工作簿，并在新工作簿中的新工作表中显示计算结果；残差残差: :如果需要以残差输出表的形式查看残差，则选中此复选框；标准残差标准残差: :如果需要在残差输出表中包含标准残差，则选中此复选框；残差图残差图: :如果需要生成一张图表，绘制每个自变量及其残差，则选中此复选框；线形拟合图线形拟合图: :如果需要为预测值和观察值生成一个图表，则选中此复选框；正态概率图，如果需要绘制正态概率图，则选中此复选框。

4.4. 结果分析结果分析第一部分是回归统计的结果第二部分是方差分析的结果第三部分是回归方程的截距和斜率的估计值以及它们的估计标准误差、t 统计量大小双边拖尾概率值、以及估计值的上下界第四部分是样本散点图: 其中蓝色的点是样本学习文档 仅供参考。