第6章 Excel高级应用案例

（利率变化为5%~7%之间），贷款额为80万，贷款
年限为1Hale Waihona Puke Baidu30年，如图6-38所示。还贷额可以通过固
定利率及等金额分期付款函数PMT( )求得。
计算中心
案例中用到的函数说明：函数格式为 PMT(rate,nper,pv,[fv],[type])。 其中参数rate为：
贷款利率； nper为：总投资期或总贷款期； pv为：从该项投资（或贷款）开始计算时已经入账的款项，或一 系列未来付款当前值的累积和； [fv]为：可选参数，未来值，或在最后一次付款后希望得到的现金
计算中心
宏应用案例一
【例6-2】通过命名宏的方法来设置总分和平 均分（素材文件:Ex6-2.xlsx）。要求设置两个
按钮，分别是“总分”和“平均分”，当单击
“总分”按钮时，统计表格中的总分，当单击
“平均分”按钮时，统计表格中的平均分。
计算中心
宏应用案例二 【例6-3】创建一个宏，其功能是在打开该宏 所在的工作簿时，在Sheet1工作表的A1单元
的几组数组中，将数组间对应的元素相乘，并返回乘积之和，其 语法格式为SUMPRODUCT（array1, array2, array3,……）。其中 array1, array2, array3,……为数组，数组个数介于2~255之间。
计算中心
数据分析工具库 数据分析工具库能够帮助使用者快速分析 数据，从而方便地解决一些实际应用，节约
数据，可以在数据透视表中显示相应的汇总数据，并
且可以方便地查看和比较数据的趋势。
创建数据透视图有三种方法：
• • •
计算中心
基于工作表数据创建数据透视图 外部数据源创建法 基于数据透视表创建数据透视图。
【例6-6】基于工作表数据(Ex6-4.xlsx)创建数 据透视图
计算中心
【例6-7】利用外部数据源(Ex6-5.xlsx)创建数据透视图
计算中心
可调图形应用案例
【例6-1】建立一个电流强度随频率、初相位和最大值 变化的可调图形（素材文件:Ex6-1.xlsx )。 在电子技术中，表示交流电电流强度的公式为 其中：i表示电流瞬间的值； Im表示电流最大值；f表 示交流电的频率；Ф表示交流电初相位；t为时间（单 位秒）。现在要求制作一张Im 、f和 Ф分别发生变化时 的可调图形。
节有关参数，从而改变图形；
第三部分是一个显示关于有关参数当前数据状态（或决策结论） 等动态内容的文本框或图片。
计算中心
2．可调图形原理
在一个单元格中输入了引用另一个单元格的公式就
是在这两个单元格之间建立了一种链接关系。除了通
过公式实现数据的链接以外，Excel中还可以建立多种 形式的链接关系：工作表和图形之间的链接关系、控 件与单元格之间的链接关系、文本框与单元格之间的 链接关系等。可调图形就是综合引用所有的Excel链接 关系而实现的。
分析与统计数据的时间。Excel数据分析工具
库中共有15中数据分析工具，在使用这些工
具之前必须进行加载。
计算中心
1．加载数据分析工具库 如果在“数据”选项卡中没有“数据分析” 组，则需要加载“分析工具库”。加载“分
析工具库”的操作步骤与规划求解的加载方
法相同。
计算中心
2．数据分析工具库概述 在Excel“分析工具库”中，提供了一组数 据分析工具，包括方差分析、相关系数分析、 协方差分析、描述统计分析、指数平滑分析、 F-检验分析、傅里叶分析、直方图、移动平 均分析等，利用这些分析工具，可以进行复 杂数据分析。
Excel高级应用案例
计算中心监制 计算中心
本章介绍Excel 2010的可调图形的制作、宏、 窗体控件的使用、数据的高级管理和应用等 内容，目标是使用户掌握Excel高级应用功能， 从而有效提高Excel的使用效率。
计算中心
可调图形的制作 图形是Excel中最重要的部分之一，它可以 形象地表示各数据之间关系。本节主要介绍
计算中心
方案管理器 1．方案管理器概述
方案管理器是一种数据分析工具，是用于预测工作表模
型结果的一组数值，并且可以在工作表中创建并保存多
个不同的方案，还可以在这些方案之间任意切换，查看
不同方案的结果。 方案管理器可以进行多方案的选择，企业对于较为复杂 的计划，可能需要制定多个方案进行比较，然后进行决 策。
余额，如果省略 fv，则假设其值为0（零），也就是一笔贷款的未
来值为0； [type]为：期初和期末，0或省略为期末，1为期初。
计算中心
3.双变量模拟运算表案例 【例6-11】已知三元一次方程Z=4X+6Y+11，X 为1~10之间的整数，Y的变化范围为11~20，
用模拟运算表求解Z的值。
计算中心
计算中心
3．数据分析工具案例一 【例6-14】已知某公司销售人员提成数据， 使用直方图工具对员工的提成进行分析，以
方便查看数据的分布。
计算中心
4．数据分析工具案例二 【例6-15】根据某公司的销售收入情况，使 用协方差分析工具对每月的收入、成本与费
用进行相关分析。
计算中心
方法。数据透视表是专门针对以下用途设计的：
以多种用户友好方式查询大量数据。
对数值数据进行分类汇总和聚合，按分类和子分类 对数据进行汇总，创建自定义计算和公式。
计算中心
展开或折叠要关注结果的数据级别，查看感兴趣区
域汇总数据的明细。
将行移动到列或将列移动到行（或“透视”），以
查看源数据的不同汇总。 对最有用和最关注的数据子集进行筛选、排序、分 组和有条件地设置格式，使用户能够关注所需的信 息。 提供简明、有吸引力并且带有批注的联机报表或打 印报表。
求解；企业在生产或财务安排时，在一定条件的约束下，通过
规划求解求出最合理的安排等。
计算中心
2.加载规划求解 加载规划求解的操作步骤如下：
（1）在“文件”选项卡中，单击“选项”按钮，打开“Excel选项”
对话框。 （2）单击左侧窗格中的“加载项”项，打开“查看和管理Microsoft Office加载项”窗口。 （3）单击“管理”右侧的下拉列表框，选择“Excel加载项”选项； 然后单击“转到”按钮，打开“加载宏”对话框。 （4）在“可用加载宏”列表中勾选“规划求解加载项”复选框，单 击“确定”按钮，完成规划求解的加载操作，此时会在功能区的
一种通过控件可以随操作者的调节而动态地
改变其内容的图形，即可调图形。
计算中心
可调图形概述
1.定义：可调图形之所以“可调”和“会动”，其主要在于Excel具有 链接功能和自动重新计算功能。 可调图形组成：
第一部分是一个普通图形；
第二部分是带有一个或多个控件（与“读数显示器”）的“控 制面板”（有时可以有不止一个控制面板），这些控件用于调
计算中心
【例6-8】基于数据透视表(Ex6-8.xlsx)创建数据透视图
计算中心
4．数据透视表和透视图的修改 更改行列标签就是将数据透视表行列标签的位置进
行互换。
通过更改数值的计算类型，可以在数据透视表中相
识不同的数据汇总结果。
如果要美化创建的数据透视表，可以采用单元格格
式设置方法来实现，也可以应用相关的数据透视表
“数据”选项卡中便会显示“规划求解”按钮。
计算中心
3．规划求解应用案例 【例6-12】某公司生产和销售两种产品，两种产品每生产 一个单位需要的工时分别为3小时和7小时，用电量分别为
4千瓦和5千瓦，需要原材料分别为9公斤和4公斤。公司可
提供的工时为300小时，可提供的用电量为250千瓦，可提 供的原材料为420公斤。两种产品的单位利润分别200元和 210元。要求通过规划求解方式来安排两种产品的生产量， 使得获得的利润达到最大化。（初始产量假设每个产品分 别生产1个单位）。
样式来实现。
计算中心
单变量求解 1．单变量求解概述 单变量求解是解决假定一个公式要取的某一 结果值，其中变量的引用单元格应取值为多少的 问题。 在Office Excel中根据所提供的目标值，将引用 单元格的值不断调整，直至达到所需要求的公式 的目标值时，变量的值才确定。
计算中心
2．单变量求解应用案例 【例6-9】已知方程7x4+5x2-6x=20 ，使用单变 量求解方法求解方程
计算中心
2．方案应用案例 【例6-13】一个公司2012年销售情况和2013年预计销售增长情况 的原始数据，2013年的增长预计分为好、一般和差三种状态，现 在要求根据有关预计增长数据生成方案。销售利润 =销售额 – 销售 成本，总销售利润是各产品销售利润之和。
案例中用到的函数说明：SUMPRODUCT函数的功能为在给定
计算中心
2.数据透视表的创建 创建数据透视表有两种方法，一种是基于 工作表数据创建数据透视表，另一种是外部
数据源创建法。
计算中心
【例6-4】基于工作表数据(Ex6-4.xlsx)创建数据透视表
计算中心
【例6-5】利用外部数据源(Ex6-5.xlsx)创建数据透视表
计算中心
3．创建数据透视图 数据透视图以图形形式表示数据透视表中所分析的
格中，系统能自动显示当前时间。
计算中心
数据高级管理与分析应用 Excel除了数据的排序、筛选和分类汇总等 简单的管理分析以外，还提供了数据透视表、
单变量求解、规划求解以及Excel自带的一些
数据分析工具等数据的高级管理和分析方法。
计算中心
数据透视表与透视图
1．数据透视表概述
数据透视表是一种可以快速汇总大量数据的交互式
规划求解
1．规划求解概述 规划求解是一组命令的组成部分，也是 Excel中的一个加载宏， 可用于假设分析。 使用规划求解，可求得工作表上某个单元格（被称为目标单元 格）中公式的最优值。其优化模型包括三个部分：目标单元格、 可变单元格和约束条件。 规划求解具有多种应用，如应用规划求解对多元一次方程进行
算表基于一个输入变量变化时对公式计算结果的影响，双变量 运算表可以对两个变量输入不同值时对公式的影响。
计算中心
2．单变量运算表案例
【例6-10】某人打算贷款购房，在贷款购房之前需
要分析不同贷款利率下购房贷款的月还款额；同时
还需要考虑分期付款期限为1~3年中，不同年限的月
还款额，具体数据为：首付款60万，基准利率6%
计算中心
模拟运算
1．模拟运算概述
模拟运算表是一种将工作表中单元格区域的数据进行模拟计算， 显示更改公式中的一个或两个变量将如何影响这些公式的结果。 模拟运算表可提供在一次运算中计算多个结果的快捷方式，以 及查看和比较工作表中所有不同变量结果的方法。
模拟运算表分为单变量运算表和双变量运算表两种。单变量运