友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
Excel word ppt office使用技巧大全(DOC格式)-第63部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 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……………………………………………………………
以看出不同还款年限和利率所对应的月还款金额，从而为贷款成本评估提供依据。
7。最小还贷分析
模拟运算表可以分析某个或某两个因素改变时，由它们决定的结果会发生怎样的变
化。但在企业管理、金融证券等领域，还存在着另外一类问题。就是在财力、物力和劳动力
等资源受到限制的情况下，如何使经营利润最大或生产成本最小。这就是所谓的规划问题，
寻求答案的过程就是 “规划求解”。下面介绍它的加载及使用方面的有关问题：
（1）加载 “规划求解”：
与上面介绍的其他分析工具不同， “规划求解”是以 “加载宏”形式提供的工具。
在默认情况下，它并不随着 Excel 的启动而运行，所以在 “工具”菜单中看不到 “规划求解”
命令。如果你要加载 “规划求解”，可以点击 “工具→加载宏”
菜单命令，在打开的对话框中选中 “加载宏”命令，确定之后即可在 “工具”菜单
下看到 “规划求解”命令。
（2）规划求解方法：
假设某公司需要从不同银行贷三笔款项，金额分别为 5000 万元、6000 万元和 3000
万元。假设贷款年利率的计算办法是贷款年限加一，然后乘以 6〃，原定三笔贷款的还款期限
分别是 8 年、9 年和 10
年，现在需要计算各笔贷款的还款期限分别是几年，才能确保到期时的还贷总金额
最少。
首先建立有关的数据清单，在 A2、A3 和 A4 单元格分别输入三笔贷款的金额，接着
在 C2、C3 和 C4 单元格输入原定的贷款年限。然后在 B2 单元格建立利率计算公式
“=（C2＋1）*0。006”，并将它复制到 B3 和 B4 单元格，分别计算出三笔贷款的利率。继续在 D2
单元格建立公式 “=PMT（B2，C2，…A2）”（相关参数的意义见上文），并将它复制到 D3 和 D4 单
510
…………………………………………………………Page 511……………………………………………………………
元格，分别计算出三笔贷款的年还款金额。最后在 D5 单元格内输入公式 “=SUM（D2：D4）”，
计算出到贷款期时的还款总金额。
因为我们的目的是寻找D5 单元格满足什么条件才能使还贷总金额最少，所以是一个
求解 “最小值”的问题，并且应该将D5 将其作为目标单元格。点击 “工具→规划求解”
菜单命令，打开 “规划求解参数”对话框 （如图 5 所示）。
此时 D5 单元格的绝对引用 S｜DS｜5 会自动出现在 “目标单元格”框中，接下来就应
该将 “最小值”选中。由于贷款期限是决定还款总金额的因素，选择合适的贷款期限是我们
的目标。所以要用鼠标点击 “可变单元格”框，将光标拖过 C2：C4 区域，其绝对引用
S｜CS｜2：S｜CS｜4 自动进入其中。由于贷款年限都是整数，因此要对 “可变单元格”进行 “约
束”。请点击 “添加”按钮打开 “添加约束”对话框，在 “单元格引用位置”中指定C2：C4
区域的地址 S｜CS｜2：S｜CS｜4；再打开对话框中间的下拉列表，选择 “INT”关系符，使 “约束
值”框内显示 “整数”。点击 “求解”按钮，Excel 开始进行计算，最后出现 “规划求解结果”
对话框。
在规划求解找到结果的情况下，一般应出现在 “报告”下的 “运算结果报告”。确
定之后，即可在原来的工作表旁边建立一个 “运算结果报告”。从计算结果中可以看出，最
佳的还款是 14 年。原来的计划需要归还贷款 2087。57 万元，而现在的计划只需 1798。06 万元，
后一方案可以节约 289。50 万元。
当然，不是每一个规划求解问题都可以获得答案。如果问题的数学关系建立错误，
约束条件选取不当等等。均可能导致目标单元格数值不收敛，或者在目标或约束条件单元格
中发现错误。这都需要我们仔细分析问题的数学关系，重新建立模型和设置约束条件。
三、分析图表
Excel 提供的信息不容易理解。如果你要更直观地观察数据反映的信息，必须借助图
511
…………………………………………………………Page 512……………………………………………………………
表这种数据分析和表现手段。
1。直接制作图表
如果你要利用图 1 的数据制作一个各科成绩随序号变化的簇状柱形图，只要选中相
关的数据区域 （图 1 的F2：K7），然后按F11 键即可在当前工作簿插入一个图表。
2。图表向导
点击 “插入→图表”菜单命令，或者点击工具栏中的 “图表向导”按钮，就可以打
开 “图表向导”，在它的指引下一步步建立图表。下面以建立 60090105d^4 的分析结果图表
为例，说明图表向导的操作方法：
（1）选择图表类型：
“图表向导”第一步是选择你需要的图表类型，它的 “标准类型”以及 “自定义类
型”选项卡总共提供了 14（不含子图表）和 20 种图表。各种图表都有自己的最佳适用范围，例
如柱形图显示一段时间内的数据变化或比较结果，用来反映数据随时间的变化很合适。条形
图可以对数据进行比较，用来反映数据间的相对大小比较好。
如果你不知道当前工作表中的数据使用何种图表，可以通过以下方法找到满意的图
表类型：选中工作表数据清单中的任意一个数据，对标准类型的图表来说，你可以选中 “图
表类型”及其 “子图表类型”。然后点击 “按下不放可查看实例”按钮，就可以在选项卡右
侧看到数据生成的图表实例。查看 “自定义类型”选项卡的图表实例更简单，你只要将其中
的图表类型选中，就可以在选项卡右侧看到结果了。
（2）选择数据源：
图表向导的第二步打开 “图表源数据”对话框。如果你需要将�
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！