使用EXCEL进行统计分析

1 使用使用 EXCELEXCEL 进行统计分析进行统计分析 六西格玛管理方法给我们的组织带来了管理上新的认识 其中包含了如方差分析 假设 检验 回归分析 基本统计等大量的数理工具 但问题也随之而来 那就是大量的数据处 理 这时我们就会想到用软件来帮助完成大量的运算 现在应用于这一方面的软件很多 如 MINITAB SPSS SAS 等 但是大多为英文软件 在这里我要介绍的就是用 EXCEL 的数据分析与规划求解来帮助您进行简单的数据分析 EXCEL 的数据分析工具为中文界面 操作简单 在一定程度上能给予大家帮助 第一节第一节 加载宏加载宏 EXCEL 的数据处理除了提供了很多的函数外 另外还有两个功能就是数据分析和规划求解 但这两个工具必须加载相应的宏后才能使用 操作步骤为工具 加载宏 出现如图 1 1 的 对话框 选择分析工具库和规划求解 点击确定后 在工具菜单栏内出现了这两个分析工 具 图 1 1 第二节第二节 随机数据与基本分析随机数据与基本分析 在上一节通过加载宏在工具栏内增加了两个数据分析库 数据分析与规划求解 在本节将 介绍数据分析工具库中的随机数生成器和描述统计 操作工具 数据分析 出现如图 2 1 的对话框 对话框包含了方差分析 相关系数 协方 差分析等十几种分析工具 但一般处理对象为简单的数据 2 图 2 1 在对话框内选择随机数发生器 将出现如图 2 2 的对话框 对话框提供了相应的随机数产 生及存储的条件命令 具体如下 图 2 2 1 变量个数 需要形成几组数据 2 随机数个数 每组数据包含的数据个数 3 分布 选择随机形成的数据期望服从的概率分布类型 包括均匀分布 正态分布 二项 分布 离散分布等 4 参数 输入不同分布的参数 如正态分布要求提供平均值及标准偏差 5 随机数基数 输入随机数的基数 6 输出选项 选择输出区域将使随机数据在同一工作表的制定位置放置 选择新工作表组 将使随机数据放置在同一工作薄的新工作表中 选择新工作薄将生成一个新的工作薄来存 储数据或分析结果 以上选择将出现在后续的各种分析工具对话框中 操作意义相同 操作工具 数据分析 在对话框内选择描述统计 将出现如图 2 3 的对话框 对话框的内 容解释如下 图 2 3 1 输入区域 输入数据所在的区域 可以包含数据的标志 注意分析的数据单个总体应包 3 含在一列或一栏中 2 分组方式 提供了两种选择 如果单个数据总体在同一行内 请选择逐行 如果在列中 则选择逐列 3 在选取数据区域时如果选择了数据标志 请选择标志位于第一行 4 输出选项 前三项内容如前所述 后四项选择内容供选择数据分析的内容 示例 用随机数发生器生成 3 组正态数据 每组数据容量为 20 数据的平均值为 5 标准 偏差为 0 2 并对形成的数据作描述性统计 选择数据分析工具库的随机数发生器 在对话框中变量个数栏输入 3 在随机数个数栏选 择 20 分布类型下拉表中选择正态分布 参数平均只栏输入 5 标准偏差栏输入 0 2 随机 数基数输入 0 结果如图 2 4 确定后再工作表中输出结果如图 2 5 其中的列标志为提前 输入 图 2 4 图 2 5 选择数据分析工具库中的描述统计对上述形成的数据进行分析 在对话框中输入区域选择 随机数据所在的表格区域 分组方式按要求为逐列 因为包含标志所以选择标志位于第一 4 行 选择输出位置及汇总统计 均值置信度为 95 及最大和最小值 选择如图 2 6 所示 单击确定 输出如图 2 7 的分析结果 注意其中的众数由于不存在而显示为 N A 图 2 6 图 2 7 第三节第三节 方差分析方差分析 在数据分析工具库中提供了 3 种基本类型的方差分析 单因素方差分析 双因素无重复试 验和可重复试验的方差分析 本节将分别介绍这三种方差分析的应用 单因素方差分析 在进行单因素方差分析之前 须先将试验所得的数据按一定的格式输入到工作表中 其中 每种水平的试验数据可以放在一行或一列内 具体的格式如图 3 1 图中每个水平的试验 5 数据结果放在同一行内 图 3 1 数据输入完成以后 操作工具 数据分析 选择数据分析工具对话框内的单因素方差分析 出现如图 3 2 的对话框 对话框的内容如下 1 输入区域 选择分析数据所在区域 可以选择水平标志 针对图 3 1 的数据进行分析时 选取 绿色 和黄色区域 2 分组方式 提供列与行的选择 当同一水平的数据位于同一行时选择行 位于同一列时 选择列 本例选择行 3 如果在选取数据时包含了水平标志 则选择标志位于第一行 本例选取 4 显著性水平 一般输入 0 05 即 95 的置信度 图 3 2 5 输出选项 按需求选择适当的分析结果存储位置 按图 3 2 输入选项后 对图 3 1 的数据分析的结果如图 3 3 所示 6 图 3 3 双因素无重复试验方差分析 与单因素方差分析类似 在分析前需将试验数据按一定的格式输入工作表中 如图 3 4 所 示 图 3 4 数据输入完成以后 操作工具 数据分析 选择数据分析工具库中的双因素无重复方差分 析 出现如图 3 5 的对话框 对话框的内容如下 1 输入区域 选择数据所在区域 可以包含因素水平标志 2 果数据输入时选择了因素水平标志 请选择标志按钮 3 显著性水平 根据实际情况输入 一般选择 0 05 4 输出选项 按需要选择分析结果存储的位置 图 3 5 分析图 3 4 中的数据 对话框如图 3 5 输入 分析结果输出如图 3 6 7 图 3 6 双因素可重复方差分析 双因素可重复方差分析与双因素无重复方差分析数据输入的区别在于对重复试验数据的处 理 如图 3 7 所示 就是将重复试验的数据叠加起来 图 3 7 数据输入完成以后 操作工具 数据分析 选择数据分析工具库中的双因素可重复方差分 析 出现如图 3 8 的对话框 对话框的内容基本与双因素无重复方差分析相同 区别在于 每一样本的行数选项 在此输入重复试验的次数即可 8 3 8 若须对图 3 7 所示数据进行方差分析时 按图 3 8 的显示输入即可 在输入区域选择数据 所在区域及因素水平标志 在每一样本的行数处输入 3 即每种组合重复 3 次试验 显著 性水平选择 0 05 在输出选项中可以按照需求选择分析结果储存的位置 选择确定以后分 析结果如图 3 9 所示 图 3 9 第四节第四节 相关分析与协方差分析相关分析与协方差分析 EXCEL 数据分析工具库中提供了多因素数据的两两对应得相关分析与协方差分析 分析结 9 果分别为相关系数矩阵和协方差矩阵 相关系数分析 在进行分析前先将数据按图 4 1 的格式输入 EXCEL 工作表中 并对输入的数据进行相关 分析和协方差分析 图 4 1 操作工具 数据分析 在出现的数据分析工具栏中选择相关系数 将出现如图 4 2 的对话 框 对话框内容如下 1 输入区域 选取图 4 1 数据表中的绿色和黄色区域 表示标志与数据 2 分组方式 根据数据输入的方式选择逐行或逐列 此例选择逐列 3 由于数据选择时包含了标志 所以要勾选标志位于第一行 4 根据需要选择输出的位置 图 4 2 将信息按图 4 2 输入对话框后 选择确定 输出结果如图 4 3 所示 10 图 4 3 协方差分析 数据输入依图 4 1 例 在数据分析内选择协方差 出现的对话框及输入方法与相关系数分 析相同 输入完成确定后 分析结果如图 4 4 所示 图 4 4 第五节第五节 假设检验假设检验 假设检验是我们常用的数据分析工具 其方法是运用统计工具对设定的 H0 原假设做出判 断 在 EXCEL 的数据分析工具库中 主要包括 F 检验 t 检验和 z 检验 3 种 其中 t 检验 又根据两个样本间的方差情况分为等 异方差检验 F 检验 双样本方差分析 为了能进行分析 先用随机数发生器在 EXCEL 工作表内生成两组服从正态分布的数据 注意标志为另外输入 生成的数据如图 5 1 所示 11 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 计算值大 于临界值 则说明两样本的方差存在显著差异 反之则认为两样本间的方差差异不具备统 计显著性 12 图 5 2 图 5 3 t 检验 双样本等方差或异方差检验 t 检验双样本等方差或异方差检验主要检验两个样本的均值差异显著性 其中前者条件为 两个样本的方差相等 后者条件为不相等 其操作流程基本相同 现仅以双样本等方差分 析为例对图 5 1 产生的数据进行分析 在数据分析数据库中选择 t 检验双样本等方差检验 出现如图 5 3 的对话框 对话框内容 及分析的输入内容如下 13 图 5 3 1 变量 1 2 区域的输入与 F 检验相同 2 假设平均差 若输入 0 则原假设为两样本均值无显著差异 3 如果在选择数据是包含标志 则选择标志复选框 4 输入显著性水平 按以上操作后输出内容如图 5 4 可以根据其中的 P 值判断 也可以根据 t 计算值与临界值 的比较来判定 其中若 P 值小于 0 05 或 t 计算值大于临界值 则说明两样本的均值存在显 著差异 反之则认为两样本间的均值差异不具备统计显著性 图 5 4 t 检验 双样本平均差检验 双样本平均差检验区别于上述两种检验的特点在于 两个样本的总体方差已知 由于这点 的局限性 其实际运用意义不大 其功能对话框如图 5 5 对话框内容区别于 t 检验就是对 14 已知两个样本方差的输入 图 5 5 第十节第十节 回归与规划求解回归与规划求解 回归分析在试验设计数据处理有非常重要的作用 例如正交设计 均匀设计 配方设计 复合设计都需要通过回归分析来寻找因素与响应变量间的关系 而 EXCEL 的数据分析工 具库中就提供了回归分析的工具 通过回归分析 一般会得到因素与响应变量间的拟合方 程 那么怎样根据拟合方程来确定最优试验条件呢 这时就可以使用数据分析工具库中的 规划求解工具 回归分析 在进行回归分析前 须将试验结果数据按规定的格式输入 EXCEL 表格中 现假设为了解 某指标 Y 与因素 X1 X2 之间的影响关系 进行了 10 次试验 试验数据结果输入 EXCEL 表中如图 10 1 图 10 1 15 试验数据输入完成后 在数据分析工具库中选择回归 出现如图 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 所示 16 图 10 3 图 10 4 图 10 5 17 图 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