因素分析法的Excel应用
2009-7-7 16:13刘跃科【大中小】【打印】
因素分析法的相关知识：
一、概念：因素分析法也称因素替代法。它是对某个综合财务指标或经济指标的变动原因按其内在的影响因素，计算和确定各个因素对这一综合指标发生变动的影响程度的一种分析方法
二、适用范围：适用于多种因素构成的综合指标的分析，如：成本、利润、资金收益率等指标。
三、前提条件：当有若干因素对分析对象发生影响作用时，假定其他各个因素都无变化，顺序确定每一因素单独变化所产生的影响，是在具有乘积关系的指数体系中进行
四、一般程序：
1. 要根据经济指标形成的过程，找出该项经济指标受哪些因素变动的影响；
2. 要根据经济指标与各影响因素的内在关系，建立起分析计算公式；
3. 按照一定顺序依次进行因素替换，以计算各因素变动对经济指标的影响程度。计算某一因素变动对经济指标影响程度时，假定其他因素不变，通过每次替代后计算的结果与上一次替代后计算的结果相比较，以逐次确定各个因素的影响程度。
4. 验证各因素影响程度计算的正确性。各因素影响程度的代数和应等于指标变动总差异。
五、主要作用：因素分析是从数量方面研究现象动态变动中受各种因素变动的影响程度，它主要借助于指数体系来分析社会经济现象变动中各种因素变动发生作用的影响程度。
六、方法：因素分析法有连环替代法和差额计算法两种。连环替代法是将影响某项经济指标的各个因素列成算式，按照一定顺序替代各个因素，以确定各个因素变动对该项经济指标变动的影响程度的一种分析方法。分析计算时以计划指标为基础，用各个因素的实际数依次替代计划数，每次替代后实际数就被保留下来，直到所有的因素都变为实际数。差额分析法是根据各个因素实际数同计划数的差异，分别确定各该因素的变动对某项经济指标的影响程度的一种分析方法。分析计算时也要按一定顺序逐项以实际数与计划数进行对比。差额分析法实际上是连环替代法的另一种形式，即直接用实际数与计划数之间的差额来计算各因素变动对指标的影响程度。这一方法较连环替代法更为简便。
差额分析法在发电企业燃煤成本分析中的Excel应用的具体操作实例
众所周知，在目前，电价由国家控制的情况下燃煤成本的管理好坏决定着发电企业的存亡问题，发电企业的燃煤成本占发电总成本的比例不低于60%，在当前煤价持续长涨的趋势下，这个比例将会更高，因此必须加大对燃煤成本的分析力度，从内部挖潜，加强管理，
才是企业生存之本。而影响燃煤成本的因素是多方面的，各方面又相互关联，完全依靠手工相对因难，而各相关因素看起来也不直观，借助于Excel，可以实现自动化分析。下面通过具体的实例来说明Excel在燃煤成本分析中的具体应用。有关资料数据如下表所示。
M电厂2009年1月原煤成本分析表
A B C D
1 项目计划实际差异
2 实际发电量（万度）10，000.00 9，500.00 -500.00
3 原煤耗量（吨）65，000.00 76，000.00 11，000.00
4 单位原煤耗（吨/万度）6.50 8.00 1.50
5 煤单价（元/吨）320.00 350.00 0.00
6 燃煤成本（元）20，800，000.00 26，600，000.00 5，800，000.00
7 差异分析
8 发电量影响数（元）-1，040，000.00
9 原煤耗影响数（元）4，560，000.00
10 煤价影响数（元）2，280，000.00
11 三项总影响数（元）5，800，000.00
a）所涉及的计算公式：
单位原煤耗=原煤耗量/发电量
燃煤成本=发电量*单位原煤耗*原煤单位成本
各项差异额=实际发生额-计划发生额
b）建立Excel的分析模型
第一步：在Excel工作表中建立如上表所示的差异分析格式。
第二步：定义计算公式
1、每月计划数可事先排列好，涉及公式有B4=B3/B2 ，B6=B2*B4*B5 ，若每月分析差异时，该列金额固定不变。
2、定义好公式C4=C3/C2 和C6=C2*C4*C5，每月只需要把当月的实际发电量、原煤耗量、原煤单位成本按实际发生额对应输入，实际单位煤耗、燃煤成本就会自动出来。
3、定义计算公式：D2=C2-B2，用鼠标拖动D2单元格右下角的填充柄到D6单元格，利用Excel的自动填充技术，完成的D2、D3、D
4、D
5、D6这四个单元格公式的定义，从而计算出各单项指标的变动情况，上图可以看出，M电厂元月份燃煤成本实际比计划超支580万元，发电量比计划少发500万度，原煤耗量比计划多燃11000吨，原煤单位成本比计划超30元/吨。
4、而各单项指标又具体影响燃煤成本多少呢？
这就是需要设立差异分析公式：
1）发电量的变动对燃煤成本的影响公式：C8=（C1-B1）*B4*B5
2）原煤耗变动对燃煤成本的影响公式：C9=C1（C4-B4）*B5
3）燃煤单位成本对燃煤成本的影响公式：C10=C2*C4*（C5-B5）
4）三项综合因素公式：C11=C8+C9+C10
设好上述公式后，各指标影响因素都会自动填充，使各指标的影响指标看起来一目了然，非常直观。
第三步：具体分析：从上表可以看出，该电厂1月份由于发电量的减少使燃煤成本比计划减少104万元，由于原煤耗的上升使燃煤成本比计划增加456万元，由于原煤单位成本的上涨使使燃煤成本比计划增加228万元，三者综合因素造成使燃煤成本比计划增加580万元，与各单项差异额正好相符。从而可以得出该电厂影响成本最大因素是煤耗问题，煤质的好坏是影响煤耗的关键，对电厂的发展会造成有形和无形成本的增加，具体表现有如下几点：
1. 造成厂用电率和供电煤耗率上升。因煤质问题造成制粉系统长时间运行，增加了球磨机、碎煤机、输煤皮带、冷轧机、给煤机、给粉机等设备的运行时间，造成制粉系统单耗增加，导致厂用电率和供电煤耗率上升
2. 煤质差，一方面造成锅炉燃煤不稳，助燃用油大量增加；另一方面燃煤量增加，造成运费成本的增加，同时使生产维护费用大幅上升。
3. 设备严重磨损。煤质差最大的影响是锅炉设备磨损严重，磨煤机每天要不停连续运转，经常出现管道、排粉风机、再循环风门粗细粉分离器等部位磨穿漏粉，从而造成无形成本的增加。
4. 由于煤质过差，造成制粉系统和给煤系统经常堵塞以及炉膛结焦，增加了运行人员的工作量和劳动强度，从而增加了职工薪酬成本的增加。
5. 由于煤质差，降低了锅炉的安全运行系数，同时给经济运行带来了很大压力。
三、采用因素分析法时注意的问题
（1）因素分析的关联性所确定的构成某项经济指标的各个因素，必须在客观上存在因果关系，这些因素要能反映所分析的经济指标发生差异的内在原因，否则计算结果不能说明问题。
（2）因素替代的顺序性替代因素时，必须按照各个因素的依存关系，排列成一定的顺序并依次替代，不可随意颠倒，否则就会得出不同的计算结果。确定各个因素排列替代的顺序，应该考虑分析对象的经济性质，从诸因素的相互关系出发，并使分析结果有助于分清责任。
（3）顺序替代的连环性应用因素分析法在计算每一因素变动的影响时，都是在前一次计算的基础上进行的，并且采用连环比较的方法来确定因素变动影响的结果。因为只有保持计算程序上的连环性，才能使各个因素影响之和等于分析指标变动的差异，以全面说明分析指标变动的原因。
（4）计算结果的假定性应用因素分析法计算的各因素变动的影响数，会因替代顺序不同而有差别，因而计算结果不免带有假定性。即它只是在某种假定前提下的影响结果，离开了这种假定前提，可能就不会是这种影响结果。因此有关分析人员对分析计算的结果要紧密结合实际经济活动加以评价。
Excel 数据管理与图表分析 单因素方差分析 单因素方差分析不考虑个体差异的影响，仅涉及一个处理因素，但可以有两个或多个水平。主要用于多个样本均数间的比较，其统计原理是推断各样本所代表的各总体均数是否相等。 例如，某军区总医院欲研究A 、B 、C 三种降血脂药物，对家兔ACE （血清肾素血管紧张素转化酶）的影响，将26只家兔随机分为4组，均喂以高脂饮食，其中三个试验组，分别给予不同的降血脂药物，而对照组不给药。一段时间后测定家兔血清ACE 浓度（u/ml ），问4组家兔血清ACE 浓度是否相同？ 下面来运用单因素方差分析的方法来分析4组家兔血清ACE 浓度是否相同。在使用单因素方差分析功能之前，应首先通过【加载宏】来加载【分析工具库】工具。 单击Office 按钮，并且单击【Excel 选项】按钮。在弹出的【Excel 选项】对话框中，选择【加载项】选项卡，并单击【转到】按钮。然后，在弹出的【加载宏】对话框中，启用【分析工具库】复选框，如图13-1所示。 图13-1 加载【分析工具库】 提 示 单击【加载宏】对话框中的【确定】按钮后，用户将发现在【数据】选项卡的【分析】组中添加了一个【数据分析】按钮。 接下来进行数据的单因素方差分析。在一张空白的工作表中，创建“对照组及各实验组家兔血清ACE 浓度(u/ml)”表格，如图13-2所示。 图13-2 创建表格 图13-3 选择方差分析项 选择【数据】选项卡，单击【分析】组中的【数据分析】按钮。在弹出的【数据分析】对话框中，选择【分析工具】栏中的【方差分析：单因素方差分析】选项，如图13-3所示。 在弹出的【方差分析：单因素方差分析】对话框中，设置【输入区域】为$B$3:$E$10；并启用【标志位于第一行】复选框，如图13-4所示。 选择 单击 创建 单击 单击
方差分析 方差分析可以用来检验来多个均值之间差异的显著性，可以看成是两样本t检验的扩展。统计学原理中涉及的方差分析主要包括单因素方差分析、两因素无交互作用的方差分析和两因素有交互作用的方差分析三种情况。虽然Excel可以进行这三种类型的方差分析，但对数据有一些限制条件，例如不能有缺失值，在两因素方差分析中各个处理要有相等的重复次数等；功能上也有一些不足，例如不能进行多重比较。而在方差分析方面SPSS的功能特别强大，很多输出结果已经超出了统计学原理的范围。 用SPSS检验数据分布的正态性 方差分析需要以下三个假设条件：（1）、在各个总体中因变量都服从正态分布；（2）、在各个总体中因变量的方差都相等；（3）、各个观测值之间是相互独立的。 在SPSS中我们很方便地对前两个条件进行假设检验。同方差性检验一般与方差分析一起进行，这一小节我们只讨论正态性的检验问题。 [例7.4] 检验生兴趣对考试成绩的影响的例子中各组数据的正态性。 在SPSS中输入数据（或打开数据文件），选择Analyze→Descriptive Statistics→Explore，在Explore对话框中将统计成绩作为因变量，兴趣作为分类变量（Fator），单击Plots按钮，选中“Histogram”复选框和“Normality plots with Test”，单击“Continue”按钮，在单击主对话框中的“OK”，可以得到分类别的描述统计信息。从数据的茎叶图、直方图和箱线图都可以对数据分布的正态性做出判断，由于这些内容前面已经做过讲解，这里就不再进一步说明了。 图7-2 用Expore过程进行正态性检验 top↑
用Excel进行数据分析：单因素方差分析 什么是方差分析？什么又是单因素方差分析？ 方差分析(Analysis of Variance，简称ANOVA)，又称“变异数分析”或“F检验” 一个复杂的事物，其中往往有许多因素互相制约又互相依存。方差分析的目的是通过数据分析找出对该事物有显着影响的因素，各因素之间的交互作用，以及显着影响因素的最佳水平等。方差分析是在可比较的数组中，把数据间的总的“变差”按各指定的变差来源进行分解的一种技术。单因素方差分析，顾名思义，就是基于一个因素分组研究，比较该因素的效应。 一、应用场景 基本思想：通过分析研究中不同来源的变异对总变异的贡献大小，从而确定可控因素对研究结果影响力的大小。 下面我们用一个简单的例子来说明方差分析的基本思想： 如某克山病区测得11例克山病患者和13名健康人的血磷值（mmol/L）如下： |患者：0.84 1.05 1.20 1.20 1.39 1.53 1.67 1.80 1.87 2.07 2.11 健康人：0.54 0.64 0.64 0.75 0.76 0.81 1.16 1.20 1.34 1.35 1.48 1.56 1.87 问该地克山病患者与健康人的血磷值是否不同？ 二、操作步骤 1、选中数据，点击功能区数据—>数据分析—>方差分析：单因素方差分析 注：本操作需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，可以参考该专题文章的第一篇《用Excel进行数据分析：数据分析工具在哪里？》。 2、在弹出的选项框里面，进行如下设置 3、点击确认，得到如下结果 从以上资料可以看出，24个患者与健康人的血磷值各不相同，如果用离均差平方和（SS）描述 其围绕总均数的变异情况，则总变异有以下两个来源： 组内变异，即由于随机误差的原因使得各组内部的血磷值各不相等； 组间变异，即由于克山病的影响使得患者与健康人组的血磷值均数大小不等。 而且：SS总=SS组间+SS组内v总=v组间+v组内 如果用均方（即自由度v去除离均差平方和的商）代替离均差平方和以消除各组样本数不同的影响，则，则说明各组均数间1值接近F相比较，若1值）与F方差分析就是用组内均方去除组
E x c e l进行单因素方 差分析的步骤
Excel进行单因素方差分析步骤 1. 调出数据分析命令窗口： 1.1 单击下图1中第一个红圈所示箭头，在出现的下拉框中，单击“其他命令”。 图1 1.2 在弹出的对话框中（图2），依次单击“加载项”——“分析工具库-VBA”——“转到”，如下图2所示。
图2 1.3 在新弹出的对话框中（图3），勾选“分析工具库-VBA”，再单击“确定”‘ 图3
注：若您当时装office时没有装全，此时可能会提示您的系统缺少一个配置，您只需要根据提示，选中office的安装文件，系统会自动完成配置安装，安装完成后，再进行步骤1.1— 1.3即可。 2. 数据统计分析，以单因素方差分析为例： 2.1完成上述步骤后，即可在“数据”菜单栏右侧看到“数据分析”命令窗口。 图4 2.2 单击“数据分析”，在弹出的对话窗中，选择左侧窗口中第一行的“方差分析：单因素方差分析”，并“确定”。 图5 2.2 弹出如下对话框，单击“输入区域”右侧窗口，下图6中箭头所示，选择待分析的数据（图7），选择完毕，按回车键或者单击图7中红圈所示处按钮，回到“方差分析：单因素方差分析”窗（图8）。 注：一般数据默认分组方式为按列分组，进行分析，我们也可改为按行分组，α一般默认为0.05（图6）。
图6 图7 2.3回到“方差分析：单因素方差分析”窗，共有三种输出选项（任一种均可输出分析结果）： 1）输出区域：单击“输出区域”右侧窗口（图8中红圈所示），在弹出新的对话窗后，选择您想要放置数据分析结果的位置（图9），再按回车键或者单击图9中红圈所示处按钮，回到“方差分析：单因素方差分析”窗（图10），并单
Excel 数据处理技巧：Power Query 插件一键生成报表 每个月月底，财务小王都要汇总全国各地区的销售数据做数 据分析。之前他采用的方法就是把各个工作表的数据复制再 粘贴到一个工作表里，然后进行数据透视表分析。可是全国 有那么多城市，复制粘贴太费时间，而且如果中间数据有变 动怎么办？现在不用担心了，今天给大家介绍一个EXCEL 很好用的技能 Power Query，帮助大家实现动态获取多工作表 数据。 一、初识 Power Query 如下图，在这个工作簿里，各个工作表分别列举了全国各个 地区的销售数据，这里为了举例，只列举四个城市的基本数 据。 首先，打开数据选项卡下，在[ 获取与转换 ]组中，新建查询---从文件 --- 从工作簿。
Power Query 本来是 EXCEL 的一个插件，在EXCEL2016 以前版本都需要另外下载，但是在 Excel2016 中，这项技能已经内置在数据选项卡 [ 获取与转换 ] 组中，以供大家直接使用。由此 足以可见 Power Query 的重要性。这里就推荐大家升级到最新的EXCEL 版本， EXCEL2016 以前的版本呢，大家可以自行百度下载安装这个插件。 在弹出的窗口中找到工作簿，导入。 在弹出的导航器界面，勾选“选择多项”，把这几个需要合 并的工作表全部选中，再选择右下角的“编辑”。 这样就进入了POWER QUERY 编辑器界面。 这个界面就是我们进行 Power Query 操作的主要界面，可以看到，上面的菜单栏跟 EXCEL 菜单栏很相似，左侧的查询窗口 显示的是打开的四个工作表，右侧查询设置界面是类似于 PS 一样的可以进行操作的记录和返回。中间区域则显示了表格的 内容。 二、使用 Power Query 汇总数据 点击开始选项卡下[ 组合 ] 组里面的追加查询。
E X C E L单因素方差分析Prepared on 21 November 2021
用Excel进行数据分析：单因素方差分析 什么是方差分析什么又是单因素方差分析 方差分析(Analysis of Variance，简称ANOVA)，又称“变异数分析”或“F检验”，是R.A.Fister发明的，用于两个及两个以上样本均数差别的显着性检验。 一个复杂的事物，其中往往有许多因素互相制约又互相依存。方差分析的目的是通过数据分析找出对该事物有显着影响的因素，各因素之间的交互作用，以及显着影响因素的最佳水平等。方差分析是在可比较的数组中，把数据间的总的“变差”按各指定的变差来源进行分解的一种技术。 单因素方差分析，顾名思义，就是基于一个因素分组研究，比较该因素的效应。 一、应用场景 基本思想：通过分析研究中不同来源的变异对总变异的贡献大小，从而确定可控因素对研究结果影响力的大小。 下面我们用一个简单的例子来说明方差分析的基本思想： 如某克山病区测得11例克山病患者和13名健康人的血磷值（mmol/L）如下： |患者：0.84 1.05 1.20 1.20 1.39 1.53 1.67 1.80 1.87 2.07 2.11 健康人：0.54 0.64 0.64 0.75 0.76 0.81 1.16 1.20 1.34 1.35 1.48 1.56 1.87 问该地克山病患者与健康人的血磷值是否不同？ 二、操作步骤 1、选中数据，点击功能区数据—>数据分析—>方差分析：单因素方差分析 注：本操作需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，可以参考该专题文章的第一篇《》。 2、在弹出的选项框里面，进行如下设置 3、点击确认，得到如下结果 从以上资料可以看出，24个患者与健康人的血磷值各不相同，如果用离均差平方和（SS）描述其围绕总均数的变异情况，则总变异有以下两个来源： 组内变异，即由于随机误差的原因使得各组内部的血磷值各不相等； 组间变异，即由于克山病的影响使得患者与健康人组的血磷值均数大小不等。 而且：SS总=SS组间+SS组内 v总=v组间+v组内 如果用均方（即自由度v去除离均差平方和的商）代替离均差平方和以消除各组样本数不同的影响，则方差分析就是用组内均方去除组间均方的商（即F值）与1相比较，若F值接近1，则说明各组均数间的差异没有统计学意义，若F值远大于1，则说明各组均数间的差异有统计学意义。实际应用中检验假设成立条件下F值大于特定值的概率可通过查阅F界值表（方差分析用）获得。
Excel进行单因素方差分析步骤 1. 调出数据分析命令窗口： 1.1 单击下图1中第一个红圈所示箭头，在出现的下拉框中，单击“其他命令”。 图1 1.2 在弹出的对话框中（图2），依次单击“加载项”——“分析工具库-VBA”——“转到”，如下图2所示。
图2 1.3 在新弹出的对话框中（图3），勾选“分析工具库-VBA”，再单击“确定”‘ 图3 注：若您当时装office时没有装全，此时可能会提示您的系统缺少
一个配置，您只需要根据提示，选中office的安装文件，系统会自动完成配置安装，安装完成后，再进行步骤1.1— 1.3即可。 2. 数据统计分析，以单因素方差分析为例： 2.1完成上述步骤后，即可在“数据”菜单栏右侧看到“数据分析”命令窗口。 图4 2.2 单击“数据分析”，在弹出的对话窗中，选择左侧窗口中第一行的“方差分析：单因素方差分析”，并“确定”。 图5 2.2 弹出如下对话框，单击“输入区域”右侧窗口，下图6中箭头所示，选择待分析的数据（图7），选择完毕，按回车键或者单击图7中红圈所示处按钮，回到“方差分析：单因素方差分析”窗（图8）。注：一般数据默认分组方式为按列分组，进行分析，我们也可改为按
行分组，α一般默认为0.05（图6）。 图6 图7 2.3回到“方差分析：单因素方差分析”窗，共有三种输出选项（任一种均可输出分析结果）： 1）输出区域：单击“输出区域”右侧窗口（图8中红圈所示），在弹出新的对话窗后，选择您想要放置数据分析结果的位置（图9），
再按回车键或者单击图9中红圈所示处按钮，回到“方差分析：单因素方差分析”窗（图10），并单击“确定”。Excel随即完成计算，在页面上出现分析结果表（图11），其中包含均值、方差以及P值。 图8 图9
Excel自动生成员工简历表 我们可以用Excel自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表……[编者]前面我们介绍了《Excel常用函数的功能和使用方法》，现在我们学以致用，介绍一系列用这些函数实现的数据统计实例解析。今天我们介绍用Excel自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表。 朋友在做人事工作，常常要打印指定员工的简历表，由于“员工基本情况登记表”是用Excel制作的，我们可以用Excel来自动提取“员工基本情况登记表”中的信息，生成并打印员工简历表，十分方便。文章末尾提供.xls文件供大家下载参考。 本文涉及的Excel函数如下，点击可查看详细信息： 1、IF (Logical,Value_if_true,Value_if_false) 2、ISERROR (value) 3、VLOOKUP (lookup_value,table_array,col_index_num,range_lookup) 应用过程： 1、启动Excel2003（其他版本请仿照操作），打开“员工基本情况登记表”工作簿（如图1）。 2、切换到Sheet2工作表中，仿照图2的样式制作好简历表的框架。
3、分别选中需要填写内容的单元格（除B2单元格外，如D2、F2等），输入公式：=IF(ISERROR(VLOOKUP(B2,简历!A3:K12,*,FALSE)),"",VLOOKUP(B2,简 历!A3:K12,*,FALSE))。 注意：①上述公式中的“*”号，表示“简历”表中，相应的数据所对应的列数：如“出生时间”在第“5”列（参见图1），在J2单元格中输入公式时，将“*”修改为“5”，即： =IF(ISERROR(VLOOKUP(B2,简历!A3:K12,5,FALSE)),"",VLOOKUP(B2,简 历!A3:K12,5,FALSE))。 ②公式的含义是：如果公式“VLOOKUP(B2,简历!A3:K12,5,FALSE)”返回错误（ISERROR），则有显示“空”（""），反之显示公式的返回结果。 4、选中J2单元格，再按住Ctrl键，点击C3单元格，同时选中J2和C3单元格，执行“格式→单元格”命令，打开“单元格格式”对话框（如图3），在“数字”标签中，选中“分类”下面的“日期”选项，然后在右侧选定一种日期类型，确定返回。
Excel中的单因素方差分析 一、目的要求 为了解决多个样本平均数差异显著性的测验问题，需要应用方差分析。方差分析是把试验看成一个整体，分解各种变异的原因。从总的方差中，将可能的变异原因逐个分出，并用误差的方法作为判断其他方差是否显著的标准，如果已知变异原因的方差比误差方差大得多，那么，该方差就不是随机产生的，试验的处理间的差异不会是由于误差原因造成的，这时处理的效应是应该肯定的。 通过学习Excel中方差分析，掌握基本的分析操作，能够处理实验的数据。 二、实验工具 Microsoft Excel 三、试验方法 叶内平均含硼量的差异显著性。 在Excel统计中，完全随机试验设计的方差分析，只须经过单因素方差分析即可得出结果，具体步骤如下： ①打开Excel，向单元格中输入文字与数字，建立表格； ②单击“工具”，在出现的对话框中，选择“数据分析”，选取“方差分析： 单因素方差分析”； ③单击“确定”，单击“输入区域：”框右边的按钮，用鼠标选中数据，再次 单击按钮；其他设置选择α为0.05。分组方式：行。点选标志位于第一列。 ④单击“确定”，即可输出单因素方差分析结果。
4、方差分析输出结果： SUMMARY 组观测数求 和平均方差 A 6 52 8.666667 4.666667 B 6 245 40.83333 13.76667 C 6 96 16 11.6 D 6 169 28.16667 34.96667 E 6 249 41.5 3.5 差异源SS df MS F P-value F crit 组间5160.467 4 1290.117 94.1691 1.07E-14 2.75871 组内342.5 25 13.7 总计5502.967 29 5、多重比较：由方差分析的结果，采用新复极差测验法，再稍加计算比较处理，即可得出： 60.05 显著，并可知除E与B二处理间无极显著差异外，其他均有极显著差异。
使用EXCEL VBA实现图表批量生成并发送 业务需求 office word2007的邮件合并功能是财务、文秘类工作经常使用的功能之一，该功能可以生成包含可变内容的批量邮件文档，广泛适用于发送工资条、成绩单、通知书等，这里不再赘述。笔者所在单位最近为加强员工考核管理工作力度，提出了一个类似于邮件合并功能，但使用邮件合并功能却不能实现的需求，下面用文字结合图表描述： 1.将员工百分制考核结果批量以图表(折线图)反应出来，每一条员工数据均生成一个类似于下图右侧的图表。 （图1） 2.使用类似于邮件合并方式实现员工考核图表的批量发送，将生成的图表插入邮件正文分别发送给每位员工。 （图2） 解决思路 思路一：使用https://www.doczj.com/doc/f714013901.html,+Sqlserver（或ACCESS等，下同）开发一个网站，设置好权限，允许员工查看自己的反馈结果。 思路评价：可行，但达不到设想中的推送效果。 思路二：使用https://www.doczj.com/doc/f714013901.html,+Sqlserver开发一个系统，内置图表模板，图表基于固定区域数据生成；为每位员工复制一份作为报表，将该员工数据填写到固定区域；将每位员工的报表作为附件发送给每位员工。 思路评价：可行，但开发量大，效果不直观。
思路三：使用https://www.doczj.com/doc/f714013901.html,+Sqlserver开发一个系统，使用VS2008版以上自带控件或第三方控件如dotnetcharting，为每位员工生成一张图片，将该图片插入邮件发送给每位员工。 思路评价：可行，但开发量大。 思路四：使用excel VBA为每位员工生成一张图片，将该图片插入邮件发送给每位员工。这个思路一开始并没有列入考虑范围，主要原因是当时认为在excel中为每位员工生成一个图表是不可能的，即使能生成也没办法脱离excel工作薄，分别和员工对应起来并发送。最终采用本思路，是缘于笔者发现VBA可以非常容易地把图表导出为图片。 思路评价：可行，事实证明，开发量比想象的小很多。 开发环境准备 1.在运行该程序的电脑上安装Office Excel2007或以上版本； 2.正确配置OUTLOOK使之能够正常发送邮件； 3.打开Excel2007新建工作薄，把测试数据输入到sheet1工作表，把工作薄保存到工作目录（为方便后文描述，这里的目录设为“E:\excel-vba”），在该目录下新建“imgfile”子目录，以存放图片。示例数据如下： 注意：附件路径是个公式：=concatenate(“E:\excel-vba\imgfile\”,A1,”.jpg”) 含义是图片以员工姓名命名，这意味着员工姓名不可重复。 4.完成宏安全设置，如图所示： （图3） 实现过程 这个业务需求实现包含两个难点，一是为每位员工生成对应的图片；二是批量发送带图片的邮件。基于这两点，笔者在excel工作薄中设置了两个按钮，编写了两段代码，分别对应上述两个问题。事实上，这两段代码完全可以整合到一起，这里为了方便理解，还是分开介绍。笔者采取代码中加入注释的方式帮助大家理解代码含义。
E X C E L单因素方差分析 Document serial number【NL89WT-NY98YT-NC8CB-NNUUT-NUT108】
用Excel进行数据分析：单因素方差分析 什么是方差分析？什么又是单因素方差分析？ 方差分析(Analysis of Variance，简称ANOVA)，又称“变异数分析”或“F检验”，是R.A.Fister发明的，用于两个及两个以上样本均数差别的显着性检验。 一个复杂的事物，其中往往有许多因素互相制约又互相依存。方差分析的目的是通过数据分析找出对该事物有显着影响的因素，各因素之间的交互作用，以及显着影响因素的最佳水平等。方差分析是在可比较的数组中，把数据间的总的“变差”按各指定的变差来源进行分解的一种技术。 单因素方差分析，顾名思义，就是基于一个因素分组研究，比较该因素的效应。 一、应用场景 基本思想：通过分析研究中不同来源的变异对总变异的贡献大小，从而确定可控因素对研究结果影响力的大小。 下面我们用一个简单的例子来说明方差分析的基本思想： 如某克山病区测得11例克山病患者和13名健康人的血磷值（mmol/L）如下： |患者：0.84 1.05 1.20 1.20 1.39 1.53 1.67 1.80 1.87 2.07 2.11 健康人：0.54 0.64 0.64 0.75 0.76 0.81 1.16 1.20 1.34 1.35 1.48 1.56 1.87 问该地克山病患者与健康人的血磷值是否不同？ 二、操作步骤 1、选中数据，点击功能区数据—>数据分析—>方差分析：单因素方差分析 注：本操作需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，可以参考该专题文章的第一篇《》。 2、在弹出的选项框里面，进行如下设置 3、点击确认，得到如下结果 从以上资料可以看出，24个患者与健康人的血磷值各不相同，如果用离均差平方和（SS）描述其围绕总均数的变异情况，则总变异有以下两个来源： 组内变异，即由于随机误差的原因使得各组内部的血磷值各不相等； 组间变异，即由于克山病的影响使得患者与健康人组的血磷值均数大小不等。 而且：SS总=SS组间+SS组内 v总=v组间+v组内 如果用均方（即自由度v去除离均差平方和的商）代替离均差平方和以消除各组样本数不同的影响，则方差分析就是用组内均方去除组间均方的商（即F值）与1相比较，若F值接近1，则说明各组均数间的差异没有统计学意义，若F值远大于1，则说明各组均数间的差异有统计学意义。实际应用中检验假设成立条件下F值大于特定值的概率可通过查阅F界值表（方差分析用）获得。
Excel数据处理技巧：Power Query插件一键生成报表 每个月月底，财务小王都要汇总全国各地区的销售数据做数据分析。之前他采用的方法就是把各个工作表的数据复制再粘贴到一个工作表里，然后进行数据透视表分析。可是全国有那么多城市，复制粘贴太费时间，而且如果中间数据有变动怎么办？现在不用担心了，今天给大家介绍一个EXCEL 很好用的技能Power Query ，帮助大家实现动态获取多工作表数据。 一、初识Power Query 如下图，在这个工作簿里，各个工作表分别列举了全国各个地区的销售数据，这里为了举例，只列举四个城市的基本数据。 首先，打开数据选项卡下，在[ 获取与转换]组中，新建查询---从文件--- 从工作簿。 Power Query 本来是EXCEL 的一个插件，在EXCEL2016 以前版本都需要另外下载，但是在Excel2016 中，这项技能已经内置在数据选项卡［获取与转换］组中，以供大家直接使用。由此足以可
见Power Query 的重要性。这里就推荐大家升级到最新的EXCEL 版本，EXCEL2016 以前的版本呢，大家可以自行百度下载安装这个插件。 在弹出的窗口中找到工作簿，导入。 在弹出的导航器界面，勾选“选择多项”，把这几个需要合并的工作表全部选中，再选择右下角的“编辑”。 这样就进入了POWER QUERY 编辑器界面。 这个界面就是我们进行Power Query 操作的主要界面，可以看到，上面的菜单栏跟EXCEL 菜单栏很相似，左侧的查询窗口显示的是打开的四个工作表，右侧查询设置界面是类似于PS 一样的可以进行操作的记录和返回。中间区域则显示了表格的内容。 二、使用Power Query 汇总数据点击开始选项卡下［组合］组里面的追加查询。 在下拉列表中选择“将查询追加为新查询 由于这里有多个表，所以选择追加三个或更多表。把左侧的可用表添加到右侧，点击确定
基于Excel2003软件的单因素方差分析实验报告 ——以混凝土成型面对其强度的影响为例土木工程与力学学院管理科学与工程 土木硕1603班王奕欣 一、例题数据及假设 混凝土强度的优化一直是工程领域关注的重点，在此试验中，混凝土的成型面分为顶面、侧面和底面三种，现采用直拔法测试混凝土强度，供试验的混凝土均采用150mm×150mm×150mm的规格，其配合比均相同，采用的骨料级配也一致，现用四台相同型号的机器制作部分试块，实验结束，得到的强度数据如表1所示。现用单因素方差分析来检验混凝土成型面是否对其强度产生影响。 表1 不同成型面的混凝土强度 试分析这三种成型面的混凝土是否有显著差异？(α=0.05) 二、分析实验操作流程 1.提出假设 要分析混凝土成型面对其强度是否有影响，那么其成型面是要检验的因素。 假设：H0:μ1 =μ2=μ3=μ4 成型面对混凝土强度没有影响 H1: μ1、μ2、μ3、μ4不全相等 成型面对混凝土强度有影响 2.运用Excel软件验证假设 ①将数据输入到Excel中
图1 输入数据 ②在工具栏点击加载宏选项，跳出对话框，如图2，勾选分析工具库，选中后点击确定。 图2 加载宏 ③再次在工具栏点击数据分析选项，跳出对话框如图3，选择“方差分析：单因素方差分析”选项，选中后点击确定。 图3 选择分析方法
④开始选择数据，以行分组，如图4，点击输入区域框右侧的图标，开始在工作簿上划定区域，或者直接输入数据所在的行、列范围，如$B$2:$E$4，所选范围边界呈虚线状，并会显示区域范围名称，如图5。 图4 选择数据 图5 区域选择完成 ⑤点击enter键后，跳出对话框如图6，确认数据输入区域，检查置信水平α，是否符合要求，不符合的话可以输入更改，本题的显著性水平为0.05。选择分析结果输出在新的工作簿上。（也可以如上一步操作一样在现有的工作簿上划区域选择分析结果输出的范围），点击确定。
竭诚为您提供优质文档/双击可除 excel生成大表格 篇一：如何使excel表格自动生成相同的表头 类别：闲云笔记 如何使excel表格自动生成相同的表头 从“文件”菜单中选“页面设置”，在最后一个选项卡“工作表”中有一个“打印标题”，再单击“顶端标题行”右端的切换按钮，在表格中选中需要的标题行，再单击切换按钮回到“页面设置”页面，最后单击“确定”按钮。然后通过“打印预览”观看实际效果。也可在“打印标题”下面的“顶端标题行”中输入“$1:$2”（不含引号）。 第页总第页 篇二：excel随机数据生成方法 求教:我的电子表格中rand()函数的取值范围是-1到1,如何改回1到0 回答：有两种修改办法： 是[1－rand()]/2， 或[1＋rand()]/2。 效果是一样的，都可生成0到1之间的随机数
电子表格中Rand()函数的取值范围是0到1，公式如下：=Rand() 如果取值范围是1到2，公式如下： =Rand()*(2-1)+1 Rand() 注解： 若要生成a与b之间的随机实数： =Rand()*(b-a)+a 如果要使用函数Rand生成一随机数，并且使之不随单元格计算而改变，可以在编辑栏中输入“=Rand()”，保持编辑状态，然后按F9，将公式永久性地改为随机数。 示例 Rand()介于0到1之间的一个随机数（变量） =Rand()*100大于等于0但小于100的一个随机数（变量） excel产生60-70随机数公式 =Rand()*10+60 要取整可以用=int(Rand()*10+60) 我想用excel在b1单元个里创建一个50-80的随机数且这个随机数要大于a1单元个里的数值，请教大家如何编写公式！整数： =Round(Rand()*(80-max(50,a1+1))+max(50,a1+1),0)
VBA一键拆分合并批量生成Excel工作表 目录 1. 一次取消所有工作表的隐藏 (2) 2. 根据模板批量生成日报表 (2) 3. 拆分工作表为单独的excel文件 (3) 4. 合并多个Excel文件工作表到一个文件中 (4)
1.一次取消所有工作表的隐藏 Sub 取消隐藏() For x = 1 To Sheets.Count If Sheets(x).Name <> "Sheet1" Then Sheets(x).Visible = -1 End If Next x End Sub Sub 隐藏() For x = 1 To Sheets.Count If Sheets(x).Name <> "Sheet1" Then Sheets(x).Visible = 0 End If Next x End Sub 2.根据模板批量生成日报表 Sub 生成报表() Dim x As Integer Dim sh As Worksheet For x = 1 To 31
Set sh = Sheets.Add With sh .Name = x & "日" Sheets("日报模板").Range("1:15").Copy sh.Range("A1") End With Next x End Sub 3.拆分工作表为单独的excel文件 Sub 拆分表格() Dim x As Integer Dim wb As Workbook Application.ScreenUpdating = False For x = 2 To 32 Sheets(x).Copy Set wb = ActiveWorkbook With wb .SaveAs ThisWorkbook.Path & "/3月/" & Sheets(x).Name & ".xlsx" .Close True End With Next x Application.ScreenUpdating = True End Sub
E X C E L单因素方差分 析 Document serial number【LGGKGB-LGG98YT-LGGT8CB-LGUT-
用Excel进行数据分析：单因素方差分析 什么是方差分析什么又是单因素方差分析 方差分析(Analysis of Variance，简称ANOVA)，又称“变异数分析”或“F检验” 一个复杂的事物，其中往往有许多因素互相制约又互相依存。方差分析的目的是通过数据分析找出对该事物有显着影响的因素，各因素之间的交互作用，以及显着影响因素的最佳水平等。方差分析是在可比较的数组中，把数据间的总的“变差”按各指定的变差来源进行分解的一种技术。 单因素方差分析，顾名思义，就是基于一个因素分组研究，比较该因素的效应。 一、应用场景 基本思想：通过分析研究中不同来源的变异对总变异的贡献大小，从而确定可控因素对研究结果影响力的大小。 下面我们用一个简单的例子来说明方差分析的基本思想： 如某克山病区测得11例克山病患者和13名健康人的血磷值（mmol/L）如下： |患者： 健康人： 问该地克山病患者与健康人的血磷值是否不同 二、操作步骤 1、选中数据，点击功能区数据—>数据分析—>方差分析：单因素方差分析 注：本操作需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，可以参考该专题文章的第一篇《》。 2、在弹出的选项框里面，进行如下设置 3、点击确认，得到如下结果 从以上资料可以看出，24个患者与健康人的血磷值各不相同，如果用离均差平方和（SS）描述其围 绕总均数的变异情况，则总变异有以下两个来源： 组内变异，即由于随机误差的原因使得各组内部的血磷值各不相等； 组间变异，即由于克山病的影响使得患者与健康人组的血磷值均数大小不等。 而且：SS总=SS组间+SS组内 v总=v组间+v组内 如果用均方（即自由度v去除离均差平方和的商）代替离均差平方和以消除各组样本数不同的影响，则方差分析就是用组内均方去除组间均方的商（即F值）与1相比较，若F值接近1，则说明各
图2 Excel 进行单因素方差分析步骤 1.调出数据分析命令窗口： 单击下图1中第一个红圈所示箭头，在出现的下拉框中，单击“其他命令” 图1 在弹出的对话框中（图 2），依次单击“加载项”一一“分析工具库 -VBA ” 一一 “转到” 如下图2所示。 匸
图5 8 9 护 11 12 1 ■■ 14 在新弹出的对话框中（图 3）,勾选“分析工具库-VBA ”，再单击“确定” 询工射-VEi 分肝工具军1更用酌托A 幽觀 图3 注：若您当时装office 时没有装全，此时可能会提示您的系统缺少一个配置， 您只需要根据 提示，选中office 的安装文件，系统会自动完成配置安装，安装完成后，再进行步骤一 即 可。 2.数据统计分析，以单因素方差分析为例： 完成上述步骤后，即可在“数据”菜单栏右侧看到“数据分析”命令窗口。 图4 单击“数据分析”，在弹出的对话窗中，选择左侧窗口中第一行的“方差分析：单因素方差 分析”，并“确定”。 ②凶 分列比强站合村计負塩设分析 皇复坝BS 住* * H I I r | K i 丄 Taterjiet K ES is! ant VBA 标整打印阿导 査阅商导 □跋元工具 快购瞎a N Bi 啊旅么 as 二■ 缶 B3 0 * 矗 f
弹出如下对话框，单击“输入区域”右侧窗口，下图6 中箭头所示，选择待分析的数据（图 7），选择完毕，按回车键或者单击图 7中红圈所示处按钮，回到“方差分析：单因素方差分 析”窗（图8）。 注：一般数据默认分组方式为按列分组， 进行分析，我们也可改为按行分组，％—般默认为 （图 6）。 图7 回到“方差分析：单因素方差分析”窗，共有三种输出选项（ 任一种均可输出分析结果） 1）输出区域：单击“输出区域”右侧窗口（图 8中红圈所示），在弹出新的对话窗后， 选择您想要放置数据分析结果的位置 （图9）,再按回车键或者单击图 9中红圈所示处按钮， 回到“方差分析：单因素方差分析”窗（图 10）,并单击“确定” 。Excel 随即完成计算，在 页面 上出现分析结果表（图 11），其中包含均值、方差以及 P 值。 U] f ；二. nF 孚 A D 2.L 2.1 2.2 2.3 幺L 2.2 2.3 2.1 2.1 Z1 2.2 Z2 至丘爲冉 E :BE B 9 To s ■於确育井f 岳二 立匪耳知 性* B3& 二# 13 15 16 丄” 图6
单因素方差分析方法 首先在单因素试验结果的基础上，求出总方差V 、组内方差 v w 、组间方差 v B 。 总方差 v= ()2 ij x x -∑ 组内方差 v w =()2 ij x x i -∑ 组间方差 v B =b ()2 i x x -∑ 从公式可以看出，总方差衡量的是所有观测值x ij 对总均值x 的偏离程度，反映了抽样随 机误差的大小，组内方差衡量的是所有观测值x ij 对组均值x 的偏离程度，而组间方差则衡 量的是组均值 x i 对总均值x 的偏离程度，反映系统的误差。 在此基础上，还可以得到组间均方差和组内均方差： 组间均方差 2B s ∧= 1 B -a v 组内均方差 2 w s ∧= a ab v w - 在方差相等的假定下，要检验n 个总体的均值是否相等，须首先给定原假设和备择假设。 原假设 H 0 ：均值相等即μ1 =μ 2 =…= μ n 备择假设 H 1 ：均值不完全不相等 则可以应用F 统计量进行方差检验： F=)()(b ab a v v w --1B =2 2 ∧∧ s s W B 该统计量服从分子自由度a-1，分母自由度为ab-a 的F 分布。 给定显著性水平a ，如果根据样本计算出的F 统计量的值小于等于临界值)(a ab 1a F --， α，则说明原假设 H 0 不成立，总体均值不完全相等，差异并非仅由随机因素引起。 下面通过举例说明如何在Excel 中实现单因素方差分析。 例1：单因素方差分析 某化肥生产商需要检验三种新产品的效果，在同一地区选取3块同样大小的农田进行试验，甲农田中使用甲化肥，在乙农田使用乙化肥，在丙地使用丙化肥，得到6次试验的结果如表2所示，试在0.05的显著性水平下分析甲乙丙化肥的肥效是否存在差异。 表2 三块农田的产量
JAVA 无需JXL和POI用PageOffice自动生成Excel表格很多情况下，软件开发者需要从数据库读取数据，然后将数据动态填充到手工预先准备好的Excel模板文件里，这对于生成复杂格式的Excel报表文件非常有用，这个功能应用PageOffice的基本动态填充功能即可实现。但若是用户想动态生成一个没有固定模版格式的Excel报表时，换句话说，没有办法事先准备一个固定格式的模板时，就需要开发人员用后台代码实现Excel报表的动态生成功能了，即通过后台代码在Excel的工作表上画出相应表格，实现Excel文件的从零到有。这里的“零”指的是Excel空白文件。 下面我就如何通过后台代码实现在空白Excel文件中画表格，这一问题的具体步骤和大家分享一下。 就以通过后台自动生成一张“出差开支预算表”为例来向大家介绍一下吧。 第一步：拷贝文件到WEB项目的“WEB-INF/lib”目录下。拷贝PageOffice 示例中下的“WEB-INF/lib”路径中的pageoffice.cab和pageoffice.jar到新建项目的“WEB-INF/lib”目录下。 第二步：修改WEB项目的配置文件。将如下代码添加到配置文件中：