Excel word ppt office使用技巧大全(DOC格式)-第95部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
…………………………………………………………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）选择数据源：
图表向导的第二步打开 “图表源数据”对话框。如果你需要将图 4 中的数据按 “还
款年限”分类，应把对话框中的 “列”选中；如果你需要将图 4 中的数据按 “可能贷款利率”
分类，应把对话框中的 “行”选中。然后将光标放进 “数据区域”框内，根据图标制作需要，
512
…………………………………………………………Page 513……………………………………………………………
选中工作表中的数据区域 （如图 4 中的D3：G5），则 “=Sheet1！
S｜DS｜3：S｜GS｜5”引用就会出现在 “数据区域”框内。
如果你要引用的数据区域不连续。可以按下 Ctrl 键，然后用鼠标逐个选中工作表中
的数据区域。采用这种方法可以在一个图表中引用多个工作表中的多处数据。
“图表源数据”对话框中的 “系列”选项卡用来添加新的数据区域和 X 轴标志等内
容。如果你要将新的数据区域 （即 “系列”）加入图表，点击 “添加”按钮，就可以看到 “系
列”框中增加了一个 “系列 3”。将其选中后按上面介绍的方法将光标放入 “值”框内，就可
以根据需要选中工作表（包括其他工作表）中的数据区域。
（3）设置图表选项：
图表选项的第三步是设置图表选项，该对话框共有六个选项卡供你输入或设置图表
选项。例如 “标题”选项卡可供你输入 X 轴和 Y 轴的名称 （如 “周”、“数量和售价”），“坐
标轴”选项卡可以设置 Y 轴显示 “刻度”与否等。这里的操作比较简单，你只要边预览边尝
试就可以掌握。
3。图表加工
向导制作图表结束后，可以对图中的内容进行加工，从而使它变得更加美观。
（1）修改图例格式：
如果你感觉向导使用的图例字体和背景等不合适，可以右击图表中的图例，选择 “图
例格式”命令。打开 “图例格式”对话框，可以看到 “图案”、 “字体”、 “位置”三个选
项卡。 “图案”选项卡用来设置图例的边框和内部填充色等属性。通过 “字体”选项卡你可
以像Word 那样设置图例的字体、字号、字形等。“位置”选项卡提供了 “靠下”、“右上角”
等五个位置选项，你只须选中一个并确定，图例就会自动放到你需要的地方。当然，你也可
以选中图例，然后用鼠标将它拖到图表的任何位置。如果你不需要图例，选择右键菜单中的
513
…………………………………………………………Page 514……………………………………………………………
“清除”命令即可删除。
（2）修改坐标格式：
与 “图例”相似，图表的坐标轴文字的属性也可以修改。你只要右击坐标轴文字，
选择 “坐标轴格式”命令，即可打开 “坐标轴格式”对话框。其中 “图案”、 “字体”两个
选项卡的用途与 “图例”相同。 “对齐”选项卡用来设置坐标轴文本的对齐方式，它和 “单
元格格式”、 “对齐”选项卡基本相同，了解工作表格式设置的用户很容易掌握有关操作。
（3）修改背景和字体：
如果你感觉图表的背景不够靓丽，只须右击图表的背景，选择 “图表区格式”命令
打开相应的对话框。其中 “图案”选项卡可以设置图表区的边框样式、是否显示阴影和圆角
等，还可以选择不同颜色或填充效果美化图表背景。 “字体”选项卡可以修改图表的文字格
式，使文字显示的更加协调。 “属性”选项卡用来设置 “对象位置”和 “锁定”等。你可以
充分发挥自己的想象力，把图表打扮得漂漂亮亮。
（4）修改图表类型：
如果你感觉向导中选择的图表类型不合适，只要右击图表中的任意位置，选择 “图
表类型”命令，即可打开 “图表类型”对话框，你就可以再次进行选择了。
（5）添加趋势线：
为了准确了解数据的变化趋势，我们还可以给图表加上趋势线，具体操作方法是：
用鼠标右击图表中需要添加趋势线的数据系列 （就是与数据对应的图线），选择 “添加趋势
线”命令，就可以在对话框中选择你需要的趋势线类型了。
4。单变量分析图表
上文介绍过的某企业四季度销售利润总额指标定为 1650 万元，如果其他条件保持不
变，销售收入需要增加多少的问题。这个问题采用图表同样可以解决，下面介绍处理的具体
514
…………………………………………………………Page 515……………………………………………………………
方法：
（1）创建图表：
选中存放 “销售收入”、“销售成本”、“销售费用”、“营业税”和 “销售利润”
的A2、B2、C2、D2 和 E2 单元格，点击 “图表向导”按钮，按提示制作一个柱形图。为方便
查看，在 “图表向导步骤之 2”中，打开 “系列”选项卡，点击 “名称”框，点击数据清单中
的列表记，点击 “添加”按钮，将系列名称放入图例完成图表制作。
（2）分析图表：
接着双击利润总额数据的柱状图，使其四周出现六个黑色的控点。再用鼠标指向利
润总额柱状图的上沿，向上拖动直到显示的数据为 1650为止 （光标附近会显示当前数据大小）。
此时松开鼠标，就会弹出 “单变量求解”对话框。Excel 自动将利润总额地址 “E2”填入 “目
标单元格”框，同时将 “1650”填入到 “目标值”框 （如果利润总额的数值不准确，可以在这
里手工填写）。在可变单元格框中输入销售收入所在的单元格 “S｜AS｜2”，也可以点击 “可变
单元格”选项，然后用鼠标点击 A2 单元格。点击 “确定”按钮开始执行单变量求解，完成后
再次 “确定”。与图表关联的工作表数据就会发生变化，显示出单变量求解的结果。
四、数据透视表
Excel 图表侧重于数据的静态分析，很难使用数�
