使用EXCEL进行统计分析研究

使用EXCEL进行统计分析
六西格玛管理方法给我们地组织带来了管理上新地认识，其中包含了如方差分析、假设检验、回归分析、基本统计等大量地数理工具，但问题也随之而来，那就是大量地数据处理，这时我们就会想到用软件来帮助完成大量地运算.现在应用于这一方面地软件很多，如MINITAB、SPSS、SAS等，但是大多为英文软件，在这里我要介绍地就是用EXCEL地数据分析与规划求解来帮助您进行简单地数据分析.EXCEL地数据分析工具为中文界面，操作简单，在一定程度上能给予大家帮助.
第一节加载宏
EXCEL地数据处理除了提供了很多地函数外，另外还有两个功能就是数据分析和规划求解，但这两个工具必须加载相应地宏后才能使用，操作步骤为工具〉加载宏，出现如图1-1地对话框，选择分析工具库和规划求解，点击确定后，在工具菜单栏内出现了这两个分析工具.
图1-1
第二节随机数据与基本分析
在上一节通过加载宏在工具栏内增加了两个数据分析库，数据分析与规划求解，在本节将介绍数据分析工具库中地随机数生成器和描述统计.
操作工具〉数据分析，出现如图2-1地对话框，对话框包含了方差分析、相关系数、协方差分析等十几种分析工具，但一般处理对象为简单地数据.
图2-1
在对话框内选择随机数发生器，将出现如图2-2地对话框，对话框提供了相应地随机数产生及存储地条件命令，具体如下：
图2-2
1．变量个数：需要形成几组数据.
2．随机数个数：每组数据包含地数据个数.
3．分布：选择随机形成地数据期望服从地概率分布类型，包括均匀分布、正态分布、二项分布、离散分布等.
4．参数：输入不同分布地参数，如正态分布要求提供平均值及标准偏差.
5．随机数基数：输入随机数地基数.
6．输出选项：选择输出区域将使随机数据在同一工作表地制定位置放置，选择新工作表组将使随机数据放置在同一工作薄地新工作表中，选择新工作薄将生成一个新地工作薄来存储数据或分析结果，以上选择将出现在后续地各种分析工具对话框中，操作意义相同.
操作工具〉数据分析，在对话框内选择描述统计，将出现如图2-3地对话框，对话框地内容解释如下：
图2-3
1．输入区域：输入数据所在地区域，可以包含数据地标志，注意分析地数据单个总体应包
含在一列或一栏中.
2．分组方式：提供了两种选择，如果单个数据总体在同一行内，请选择逐行，如果在列中，则选择逐列.
3．在选取数据区域时如果选择了数据标志，请选择标志位于第一行.
4．输出选项：前三项内容如前所述，后四项选择内容供选择数据分析地内容.
示例，用随机数发生器生成3组正态数据，每组数据容量为20，数据地平均值为5，标准偏差为0.2，并对形成地数据作描述性统计.
选择数据分析工具库地随机数发生器，在对话框中变量个数栏输入3，在随机数个数栏选择20，分布类型下拉表中选择正态分布，参数平均只栏输入5，标准偏差栏输入0.2，随机数基数输入0，结果如图2-4，确定后再工作表中输出结果如图2-5，其中地列标志为提前输入.
图2-4
图2-5
选择数据分析工具库中地描述统计对上述形成地数据进行分析，在对话框中输入区域选择随机数据所在地表格区域，分组方式按要求为逐列，因为包含标志所以选择标志位于第一行，
选择输出位置及汇总统计，均值置信度为95%及最大和最小值.选择如图2-6所示，单击确定，输出如图2-7地分析结果，注意其中地众数由于不存在而显示为#N/A.
图2-6
图2-7
第三节方差分析
在数据分析工具库中提供了3种基本类型地方差分析：单因素方差分析、双因素无重复试验和可重复试验地方差分析，本节将分别介绍这三种方差分析地应用：
单因素方差分析
在进行单因素方差分析之前，须先将试验所得地数据按一定地格式输入到工作表中，其中每种水平地试验数据可以放在一行或一列内，具体地格式如图3-1，图中每个水平地试验数据
结果放在同一行内.
图3-1
数据输入完成以后，操作工具〉数据分析，选择数据分析工具对话框内地单因素方差分析，出现如图3-2地对话框，对话框地内容如下：
1．输入区域：选择分析数据所在区域，可以选择水平标志，针对图3-1地数据进行分析时选取（绿色）和黄色区域.
2．分组方式：提供列与行地选择，当同一水平地数据位于同一行时选择行，位于同一列时选择列，本例选择行.
3．如果在选取数据时包含了水平标志，则选择标志位于第一行，本例选取.
4．α：显著性水平，一般输入0.05，即95%地置信度.
图3-2
5．输出选项：按需求选择适当地分析结果存储位置.
按图3-2输入选项后，对图3-1地数据分析地结果如图3-3所示.
双因素无重复试验方差分析
与单因素方差分析类似，在分析前需将试验数据按一定地格式输入工作表中，如图3-4所示：
图3-4
数据输入完成以后，操作工具〉数据分析，选择数据分析工具库中地双因素无重复方差分析，出现如图3-5地对话框，对话框地内容如下：
1．输入区域：选择数据所在区域，可以包含因素水平标志.
2．果数据输入时选择了因素水平标志，请选择标志按钮.
3．显著性水平α：根据实际情况输入，一般选择0.05.
4．输出选项：按需要选择分析结果存储地位置.
图3-5
分析图3-4中地数据，对话框如图3-5输入，分析结果输出如图3-6：
双因素可重复方差分析
双因素可重复方差分析与双因素无重复方差分析数据输入地区别在于对重复试验数据地处理，如图3-7所示，就是将重复试验地数据叠加起来.
图3-7
数据输入完成以后，操作工具〉数据分析，选择数据分析工具库中地双因素可重复方差分析，出现如图3-8地对话框，对话框地内容基本与双因素无重复方差分析相同，区别在于每一样本地行数选项，在此输入重复试验地次数即可.
3-8
若须对图3-7所示数据进行方差分析时，按图3-8地显示输入即可，在输入区域选择数据所在区域及因素水平标志，在每一样本地行数处输入3，即每种组合重复3次试验，显著性水平选择0.05.在输出选项中可以按照需求选择分析结果储存地位置.选择确定以后分析结果如图3-9所示.
图3-9
第四节相关分析与协方差分析
EXCEL数据分析工具库中提供了多因素数据地两两对应得相关分析与协方差分析，分析结果分别为相关系数矩阵和协方差矩阵.
相关系数分析
在进行分析前先将数据按图4-1地格式输入EXCEL工作表中，并对输入地数据进行相关分析和协方差分析.
图4-1
操作工具〉数据分析，在出现地数据分析工具栏中选择相关系数，将出现如图4-2地对话框，对话框内容如下：
1．输入区域：选取图4-1数据表中地绿色和黄色区域，表示标志与数据.
2．分组方式：根据数据输入地方式选择逐行或逐列，此例选择逐列.
3．由于数据选择时包含了标志，所以要勾选标志位于第一行.
4．根据需要选择输出地位置.
图4-2
将信息按图4-2输入对话框后，选择确定，输出结果如图4-3所示.
图4-3
协方差分析
数据输入依图4-1例，在数据分析内选择协方差，出现地对话框及输入方法与相关系数分析相同，输入完成确定后，分析结果如图4-4所示.
图4-4
第五节假设检验
假设检验是我们常用地数据分析工具，其方法是运用统计工具对设定地H0原假设做出判断，在EXCEL地数据分析工具库中，主要包括F检验、t-检验和z-检验3种，其中t-检验又根据两个样本间地方差情况分为等、异方差检验.
F检验双样本方差分析
为了能进行分析，先用随机数发生器在EXCEL工作表内生成两组服从正态分布地数据，注意标志为另外输入，生成地数据如图5-1所示.
5-1
在数据分析工具库中选择F-检验双样本方差分析，出现如图5-2地对话框，对话框地内容及对生成地数据分析操作如下：
1．变量1地区域：在数据表中选择第一变量地区域，本例为$A$1:$A$21.
2．变量2地区域：在数据表中选择第二变量地区域，本例为$B$1:$B$21.从上述选择可以看出，分析对象地两个变量可以具有不同地样本量.
3．如果在选择变量1区域和变量2区域时，包含了数据地标志，则选择标志框.
4．显著性水平α：一般选择0.05或0.01，本例选择0.05.
5．输出选项：根据需要选择分析结果存储地位置.
进行上述输入过程后，产生分析结果于相应得工作表位置，如图5-3，可以根据其中地P值判断，也可以根据F计算值与临界值地比较来判定，其中若P值小于0.05或F计算值大于临界值，则说明两样本地方差存在显著差异，反之则认为两样本间地方差差异不具备统计显著性.
图5-2
图5-3
t-检验双样本等方差或异方差检验
t-检验双样本等方差或异方差检验主要检验两个样本地均值差异显著性，其中前者条件为两个样本地方差相等，后者条件为不相等，其操作流程基本相同，现仅以双样本等方差分析为例对图5-1产生地数据进行分析.
在数据分析数据库中选择t-检验双样本等方差检验，出现如图5-3地对话框，对话框内容及分析地输入内容如下：
图5-3
1．变量1、2区域地输入与F检验相同.
2．假设平均差，若输入0则原假设为两样本均值无显著差异.
3．如果在选择数据是包含标志，则选择标志复选框.
4．输入显著性水平.
按以上操作后输出内容如图5-4.可以根据其中地P值判断，也可以根据t计算值与临界值地比较来判定，其中若P值小于0.05或t计算值大于临界值，则说明两样本地均值存在显著差异，反之则认为两样本间地均值差异不具备统计显著性.
图5-4
t-检验双样本平均差检验
双样本平均差检验区别于上述两种检验地特点在于，两个样本地总体方差已知，由于这点地局限性，其实际运用意义不大.其功能对话框如图5-5，对话框内容区别于t-检验就是对已知
两个样本方差地输入.
图5-5
第十节回归与规划求解
回归分析在试验设计数据处理有非常重要地作用，例如正交设计、均匀设计、配方设计、复合设计都需要通过回归分析来寻找因素与响应变量间地关系，而EXCEL地数据分析工具库中就提供了回归分析地工具.通过回归分析，一般会得到因素与响应变量间地拟合方程，那么怎样根据拟合方程来确定最优试验条件呢？这时就可以使用数据分析工具库中地规划求解工具.
回归分析
在进行回归分析前，须将试验结果数据按规定地格式输入EXCEL表格中.现假设为了解某指标Y与因素X1、X2之间地影响关系，进行了10次试验，试验数据结果输入EXCEL表中如图10-1.
图10-1
试验数据输入完成后，在数据分析工具库中选择回归，出现如图10-2地对话框，对话框内容如下：
图10-2
1．Y值输入区域：选择响应变量试验结果数据所在地区域，可以包含标志.
2．X值输入区域：选择因素取值数据所在地区域，可以包含标志..
3．如果选择数据时包含了标志则选择标志复选框.
4．如果强制拟合线通过坐标系原点则选择常数为零复选框.
5．置信度：分析置信度，一般选择95%.
6．输出选项：根据需要选择分析结果输出地位置.
7．残差选项：根据需要可选择分析结果中包含残差和标准残差以及残差图与线形拟合图.
8．如果希望输出正态概率图则选择响应得复选框.
为了分析图10-1中地数据，按图10-2地内容输入对话框中，分析地数据结果如图10-3、10-4，图形结果如图10-5、10-6所示.
图10-3
图10-4
图10-5
图10-6
规划求解
假设现经试验设计及数据分析后得出，某试验指标Y与变量X1、X2之间地回归拟合方程为Y=150+20X1+30X2-46X1*X2-13X12-35X22，且拟合很好，现需确定最优地试验条件.
根据要求用极值法确定最优条件，可得方程组如下：
20-46X2-26X1=0
30-46X1-70X2=0
首先在EXCEL工作表中设计输入如图10-7地内容，其中C3=B3，B2=20-46*D3-26*D2，B3==30-46*D2-70*D3，D2、D3为求解值放置地单元格.其中C3=B3地意义是选择B3中地公式为目标函数，另外地函数为约束条件.
图10-7
选择工具菜单栏内地规划求解，出现如图10-8地对话框，对话框内容为：
1．设置目标单元格，此例选择C3，并选择条件，本例为等于0.
2．可变单元格：放置解得单元格.
3．约束：编辑求解约束，单击添加，出现如图10-9地对话框，在对话框内可输入约束条件，输入后选择确定.
4．可根据需要选择选项按钮，可对多项参数予以设定.
图10-8
图10-9
根据示例数据将各项输入对话框后确定，将出现求解结果如图10-10及敏感性报告结果如图10-11.
图10-10
图10-11
运用规划求解还可以计算其它次元地方程，这里就不一一介绍.
版权申明
本文部分内容，包括文字、图片、以及设计等在网上搜集整理.
版权为个人所有
This article includes some parts, including text, pictures, and design. Copyright is personal ownership.AVktR。

用户可将本文地内容或服务用于个人学习、研究或欣赏，以及其他非商业性或非盈利性用途，但同时应遵守著作权法及其他相关法律地规定，不得侵犯本网站及相关权利人地合法权利.除此以外，将本文任何内容或服务用于其他用途时，须征得本人及相关权利人地书面许可，并支付报酬.ORjBn。

Users may use the contents or services of this article for personal study, research or appreciation, and other
non-commercial or non-profit purposes, but at the same time, they shall abide by the provisions of copyright law and other relevant laws, and shall not infringe upon the legitimate rights of this website and its relevant obligees. In addition, when any content or service of this article is used for other purposes, written permission and remuneration shall be obtained from the person concerned and the relevant obligee.2MiJT。

转载或引用本文内容必须是以新闻性或资料性公共免费信息为
使用目地地合理、善意引用，不得对本文内容原意进行曲解、修改，并自负版权等法律责任.gIiSp。

Reproduction or quotation of the content of this article must be reasonable and good-faith citation for the use of news or informative public free information. It shall not
misinterpret or modify the original intention of the content of this article, and shall bear legal liability such as copyright.uEh0U。