Excel word ppt office使用技巧大全(DOC格式)-第94部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
数学成绩相关。分析的操作方法是：选中数据清单中的一个空白单元格，在编辑栏内输入公式
―=CORREL（F2：F7，I2：I7）‖，回车即可得到数学和物理成绩的相关系数。这个计算结果有以下
504
…………………………………………………………Page 505……………………………………………………………
三种情况：如果物理成绩中的高（低）分与数学成绩中的高（低）分对应，说明这两个数据集合是
正相关。计算结果应该是一个小于 1 的实数，它越接近于 1，说明两者相关性越强；如果物理
成绩中的低（高）分与数学成绩中的高（低）分对应，说明这两个数据集合是负相关。计算结果应
该是接近…1 的实数，它越接近于…1，说明两者负相关越强；如果上述计算结果接近零，说明这
两个集合中的数据互不相关。另外，为了保证分析结果的准确性。用于分析的两个数据集合中
的数据个数不能太少，一般应在 30 个以上。
如果你将图 1 中的数学成绩换成一段时间内的商场客流量，同时将物理成绩换成同一时间
内的商场销售额。运用相同的方法进行计算，就可以得知客流量与销售额是否相关，从而为制
定营业策略提供依据。
3。方差分析
在质量检验等领域，经常需要检验两个对象的平均值是否存在差异。例如两个班级的数学
课分别采用了不同的教学方法，我们需要通过期中考试成绩检验两种方法是否存在实质性差
异，以便对教学改革的成果做出判断。再如，一种手机经过改进，我们要了解两种手机的通话
距离是否存在实质性差异。就可以随机抽取数目相同的两种手机，在不同条件下测试它们的通
话距离，从而了解手机的改进是否有效。
在上述两个例子中，影响实验结果的因素只有一个。我们将其称为单因素实验，对应的方
差分析就是单因素方差分析。下面我们以不同教法的两个班级的数学课为例，说明单因素方差
分析的操作方法：
假设这两个班级的期中考试数学成绩分别存放在 A2：A46 和 B2：B46 区域，请你将光标放
在该区域以外的任意一个单元格。点击―工具→数据分析‖菜单命令，在打开的对话框中选中―方
差分析：单因素方差分析‖，―确定‖之后打开同名对话框（如无此选项，请检查你的 Office
XP 安装方式） 。
505
…………………………………………………………Page 506……………………………………………………………
其中“输入区域”让你输入数据区域的单元格引用，它由两个或两个以上按“列”或“行”
排列的相邻数据区域组成，我们这个实例应当输入“S｜AS｜2：S｜BS｜46‖（可以用鼠标选中区域的方
法输入） ；―分组方式‖用来确定输入区域中的数据如何排列。由于两个班级的成绩分别存放在
A 、B 列，所以应当选中―分组方式‖ 中的―列‖选项；又由于 A1 、B1 单元格带有班级代码，故
应将―标志位于第一行‖选中；它下面的―0。05‖称为显著性水平，一般取默认值 0。05 即可；―输
出区域‖必须输入一个空白单元格引用，用来确定计算结果存放区域左上角的位置；如果你选
中了―新工作表组‖，就需要在右侧的框中输入该工作表的名称；若选中了―新工作簿‖，则可以
创建一个新的工作簿，并在其中粘贴计算结果。
以上设置完成后点击“确定”按钮，就可以在选定区域内输出分析结果。其中的“组间”
就是影响成绩的因素（不同教学方法） ，―组内‖就是误差，―总计‖就是总和，―差异源‖则是方差
来源，―SS‖就是平方和，―df‖称为自由度（上例为 1），―MS‖就是均方，―F‖称为 F 比，―P…value‖
则是原假设（结论）成立的概率（这个数值越接近 0，说明原假设（实验班和对照班的数学成绩没
有显著差异）成立的可能性越小，反之原假设成立的可能性越大） ，―Fcrit‖为拒绝域的临界值。
假如上面两个班级的计算结果是“P…value‖等于 0。1，因此在显著性水平 0。05 的条件下原假
设不成立，实验班和对照班的数学成绩有显著的差异，说明教学方法对成绩有显著影响。
4。实现利润分析
上面介绍的内容属于统计分析的范畴，目的是大量数据中寻找统计规律。而在企业管理等
领域，管理人员则要了解不同因素或方案对经营目标的影响。例如确定四季度的销售利润总额
以后，如何确定其他开支的数额，才能保证完成销售利润目标等。下面介绍一个分析实例：
假设某企业四季度的销售利润指标定为 1650 万元，如果其他条件保持不变，销售收入需
要增加多少。由于销售利润与销售收入不是简单的线性关系（例如销售收入增加 2 万元，销售
利润同比增加 1。5 万元） ，而要受到多种因素的制约。例如增加销售收入就要加大销售成本和
506
…………………………………………………………Page 507……………………………………………………………
费用，还要缴纳更多的营业税。使用手工解决这类问题非常麻烦，需要根据销售收入与成本的
关系逐步计算。
如果利用 Excel 的单变量求解命令，就可以快速计算出结果，甚至可以针对不同情况反复
计算。下面介绍这个问题的求解方法：设 Excel 工作表 A2 、B2 、C2、D2 和 E2 单元格分别存
放―销售收入‖、―销售成本‖、―销售费用‖、―营业税‖和―销售利润‖ 的数值。根据经验，―销售成
本‖约占―销售收入‖ 的 8％，―销售费用‖约占―销售收入‖ 的 25％ ，―营业税‖约占―销售收入‖ 的
10％。据此可以建立―销售收入‖与其他因素的数学关系，这是执行单变量求解的关键。
根 据 上 面 的 分 析 ， 可 以 选 中 E2 单 元 格 ， 在 编 辑 栏 输 入 公 式
―=A2…（A2*0。08＋A2*0。25＋A2*0。1）‖ 。点击―工具→单变量求解‖
菜单命令，在打开的对话框中可见 E2 自动进入― 目标单元格‖ 。接着在对话框的― 目标值‖
内输入―1650‖，在―可变单元格‖框内输入―S｜AS｜2‖（也可以将光标放入框中，然后点击 A2 单元
格） 。上述操作完成后点击―确定‖按钮，就会弹出―单变量求解状态‖对话框，说明已经求得一
个解，而且目标值和―当前解‖相同。与此同时，你可以在 A2 单元格中看到求出的―销售收入‖，
上面这个例子的计算结果是 2894。737 万元，即要想完成 1650 万元的销售利润指标，销售收入
必须达到 2894。737 万元。
5。成绩评价分析
上面的实例讲解的是目标设定以后，实现这个目标必须满足的条件。但是实际中往往存在
这样的问题，就是实现目标的某个或多个条件发生了变化，它会对结果产生哪些影响。
下面以图 1 所示的―学籍管理表为例‖，说明某个学生单科成绩发生的变化，会对全体学生
的总平均分有什么影响。
假如我们要分析学生“赵明君”的“数学”成绩提高到了 70、80 或 90
分，全班学生的总平均分的变化情况如何。由于这种分析只涉及一种数据（―赵明君‖ 的―数
507
…………………………………………………………Page 508……………………………………………………………
学‖成绩） ，所以称之为单变量数据表。分析的具体操作过程是：
在 F10 、F11 、F12 单元格中，依次输入 70、80 和 90 。然后在第一个数据（70）的上一行，
而且位于该数据列右边的单元格（即 G9） 中输入公式―=SUM（F5：K5）‖，敲回车计算出结果。然后
在这个公式右边的单元格（即 H9） 中输入总平均分计算公式―=AVERAGE（L2：L7）‖ ，再次敲回车
计算出结果。
接着选中含有待分析数据（70、80、90）和个人总分及总平均分计算公式在内的单元格区域
（F9：H12），点击―数据→模拟运算表‖
菜单命令打开相应对话框。因为我们要引用的数据放在列方向，所以必须将光标放入“输
入引用列的单元格”，点击“赵明君”的“数学”成绩所在的单元格（即 F5） ，让它的绝对引用
―S｜FS｜5‖进入―输入引用列的单元格‖，确定即可看到如图2 所示的计算结果。
如果模拟分析数据（70、80、90）是沿着行存放（例如 F9 、G9 和 H9） ，就应该在第一个数据
所在单元格（F9）左边一列，而且位于数据行下方的单元格（E10） 中输入公式―=SUM（F5：K5）‖ ，然
后在其下方输入总平均分计算公式―=AVERAGE（L2：L7）‖ 。最后选中E9：H11 区域，按上面介绍
的方法打开―模拟运算表‖对话框，点击―赵明君‖ 的―数学‖成绩所在的单元格（即F5） ，让它的绝
对引用―S｜FS｜5‖进入―输入引用行的单元格‖，回车即可计算出如图 3 所示的计算结果。
6。贷款成本分析
上面介绍的单变量模拟运算表只能分析其他因素不变时，一个参数的变化对目标值
的影响。如果要分析两个参数的变化对目标值的影响，例如贷款利率和偿还期限同时变化时，
每月偿还金额发生的变化，就必须使用双变量模拟运算表。
假设某企业准备贷款 6000 万元，贷款期限预计为 10 年，已知该笔贷款的现行月利
率为 5％。企业领导考虑到这笔贷款的期限较长，必须分析利率变动和还款时间变化的影响。
为此，双变量模拟运算表分析以上两个因素对偿还金额的影响。下面介绍这类问题的解决方
508
…………………………………………………………Page 509……………………………………………………………
法：
首先打开一个空白工作表，在有关单元格中输入说明数据意义的文字图 4，然后在
B3、B4 和 B5 单元格中依次输入 “现行年利率”、 “贷款年限”和 “贷款金额 （万元）”的值。
接着选中 B2 单元格，在其中输入公式 “=PMT（B3/12；B4*12；…B5）”。公式中的第一个参数是
利率，因为还贷额是按月计算的，所以要将年利率除以 12 变为月利率；第二个参数是还款年
限，由于按月还贷的缘故，必须将B4 中的还贷年限乘以 12；第三个参数为贷款金额，如果不
在 B5 前面加负号，计算出来的月还款金额就是负数。为了照顾人们的阅读习惯，事先在贷款
金额前加上负号，即可使计算出来的还贷金额便为正数。此时依据上述公式计算出来的结果
是 “63。64”，即年利率为 5％、期限 10 年的条件下，每月偿还贷款的金额是 63。64 万元。
另外，PMT 函数还有 Fv 和 Type 两个参数。Fv 是贷款全部归还完毕后剩余的金额，
省略时该值为零，即一笔贷款归还完毕后其账面金额为零。Type 的值是 0 或 1，用来指定贷
款的还款时间是在月初还是月末，0 或省略表示还款时间是月初。
为了给模拟运算表提供分析依据，要紧接着公式 “=PMT（B3/12；B4*12；…B5）”的右
侧，即 D2、E2、F2 和 G2 单元格中分别输入 “可能发生的还款年限”（8、9、11、12）。最后
在公式下方的 C3、C4 和 C5 中依次输入 “可能贷款利率”（4％、6％、7％）。
完成后将公式所在的单元格、 “可能发生的还款年限”和 “可能贷款利率”两种数
据所在的区域 （C2：G5）选中。点击 “数据→模拟运算表”
菜单命令，在打开的对话框中，在 “输入引用行的单元格”框中，输入由行数值 （就
是 “可能发生的还款年限”）替换的输入单元格（B4）的绝对引用 （S｜BS｜4）。然后在 “输入引用
列的单元格”框中，输入由列数值 （就是 “可能贷款利率”）替换的输入单元格（B3）的绝对引
用 （S｜BS｜3）。
上述内容输入结束以后，点击 “确定”按钮，D3：G5 区域就会显示分析结果。从中可
509
…………………………………………………………Page 510……………………………………………………………
