第6章Excel高级应用案例

1、计算中心监制计算中心Excel高级应用案例高级应用案例计算中心本章介绍本章介绍Excel 2010的的可调图形可调图形的制作、的制作、宏宏、窗体控件窗体控件的使用、的使用、数据的高级管理数据的高级管理和和应用应用等等内容，目标是使用户掌握内容，目标是使用户掌握Excel高级应用功能，高级应用功能，从而有效提高从而有效提高Excel的使用效率。的使用效率。计算中心可调图形的制作可调图形的制作图形图形是是Excel中最重要的部分之一，它可以中最重要的部分之一，它可以形象地表示形象地表示各数据之间关系各数据之间关系。本节主要介绍。本节主要介绍一种通过控件可以随操作者的调节而动态地一种通过控件可以随操

2、作者的调节而动态地改变其内容的图形，即可调图形。改变其内容的图形，即可调图形。计算中心可调图形概述可调图形概述1.定义定义：可调图形之所以可调图形之所以“可调可调”和和“会动会动”，其主要在于，其主要在于Excel具有具有链接功能和自动重新计算功能。链接功能和自动重新计算功能。可调图形组成可调图形组成：第一部分是一个普通图形；第一部分是一个普通图形；第二部分是带有一个或多个控件（与第二部分是带有一个或多个控件（与“读数显示器读数显示器”）的）的“控控制面板制面板”（有时可以有不止一个控制面板），这些控件用于调（有时可以有不止一个控制面板），这些控件用于调节有关参数，从而改变图形；节有关参数，从

3、而改变图形；第三部分是一个显示关于有关参数当前数据状态（或决策结论）第三部分是一个显示关于有关参数当前数据状态（或决策结论）等动态内容的文本框或图片。等动态内容的文本框或图片。计算中心2可调图形原理可调图形原理在一个单元格中输入了引用另一个单元格的公式就在一个单元格中输入了引用另一个单元格的公式就是在这两个单元格之间建立了一种是在这两个单元格之间建立了一种链接关系链接关系。除了通。除了通过公式实现数据的链接以外，过公式实现数据的链接以外，Excel中还可以建立多种中还可以建立多种形式的链接关系：形式的链接关系：工作表工作表和和图形图形之间的链接关系、之间的链接关系、控控件件与与单元格单元格之间

4、的链接关系、之间的链接关系、文本框文本框与与单元格单元格之间的之间的链接关系等。可调图形就是综合引用所有的链接关系等。可调图形就是综合引用所有的Excel链接链接关系而实现的。关系而实现的。计算中心可调图形应用案例可调图形应用案例【例【例6-1】建立一个电流强度随频率、初相位和最大值】建立一个电流强度随频率、初相位和最大值变化的可调变化的可调图形图形（素材文件（素材文件:Ex6-1.xlsx )。在电子技术中，表示交流电电流强度的公式为在电子技术中，表示交流电电流强度的公式为其中其中：i表示电流瞬间的值；表示电流瞬间的值； Im表示电流最大值；表示电流最大值；f表表示交流电的频率示交流电的频率

5、；表示表示交流电初相位；交流电初相位；t为时间（单为时间（单位秒）。现在要求制作一张位秒）。现在要求制作一张Im 、f和和 分别分别发生变化时发生变化时的可调图形的可调图形。计算中心宏应用案例一宏应用案例一【例【例6-2】通过命名宏的方法】通过命名宏的方法来设置来设置总分总分和平均分（素材文件（素材文件:Ex6-2.xlsx）。要求设置两个要求设置两个按钮，分别是按钮，分别是“总分总分”和和“平均分平均分”，当单击，当单击“总分总分”按钮时，统计表格中的总分，当单击按钮时，统计表格中的总分，当单击“平均分平均分”按钮时，统计表格中的平均分。按钮时，统计表格中的平均分。计算中心宏应用案例二宏应用

6、案例二【例【例6-3】创建一个宏，其功能是在打开该宏】创建一个宏，其功能是在打开该宏所在的工作簿时，在所在的工作簿时，在Sheet1工作表的工作表的A1单元单元格中，系统能自动显示当前格中，系统能自动显示当前时间时间。计算中心数据高级管理与分析应用数据高级管理与分析应用Excel除了数据的排序、筛选除了数据的排序、筛选和和分类汇总等分类汇总等简单的管理分析以外，还提供了简单的管理分析以外，还提供了数据透视表数据透视表、单变量求解单变量求解、规划求解规划求解以及以及Excel自带的一些自带的一些数据分析工具数据分析工具等数据的高级管理和分析方法。等数据的高级管理和分析方法。计算中心数据透视表与透

7、视图数据透视表与透视图1数据透视表概述数据透视表概述数据透视表数据透视表是一种可以是一种可以快速汇总快速汇总大量数据的交互式大量数据的交互式方法。数据透视表是专门针对以下用途设计的：方法。数据透视表是专门针对以下用途设计的： 以多种用户友好方式查询大量数据。以多种用户友好方式查询大量数据。 对数值数据进行对数值数据进行分类汇总分类汇总和和聚合聚合，按分类和子分类，按分类和子分类对数据进行汇总，创建自定义计算和公式。对数据进行汇总，创建自定义计算和公式。计算中心 展开展开或或折叠折叠要关注结果的数据级别，查看感兴趣区要关注结果的数据级别，查看感兴趣区域汇总数据的明细。域汇总数据的明细。 将将行移

8、动到列行移动到列或将或将列移动到行列移动到行（或（或“透视透视”），以），以查看源数据的不同汇总。查看源数据的不同汇总。 对最有用和最关注的数据子集进行对最有用和最关注的数据子集进行筛选筛选、排序排序、分分组组和和有条件地设置格式有条件地设置格式，使用户能够关注所需的信，使用户能够关注所需的信息。息。 提供简明、有吸引力并且带有批注的提供简明、有吸引力并且带有批注的联机报表联机报表或或打打印报表印报表。计算中心2.数据数据透视表的创建透视表的创建创建数据透视表有两种方法，一种是创建数据透视表有两种方法，一种是基于基于工作表数据工作表数据创建数据透视表，另一种是创建数据透视表，另一种是外部外部数

9、据源数据源创建法。创建法。计算中心【例【例6-4】基于工作表】基于工作表数据数据(Ex6-4.xlsx)创建数据创建数据透视透视表表计算中心【例【例6-5】利用外部】利用外部数据源数据源(Ex6-5.xlsx)创建创建数据透视表数据透视表计算中心3创建数据透视图创建数据透视图数据透视图数据透视图以图形形式表示数据透视表中所分析的以图形形式表示数据透视表中所分析的数据，可以在数据透视表中显示相应的汇总数据，并数据，可以在数据透视表中显示相应的汇总数据，并且可以方便地查看和比较数据的趋势。且可以方便地查看和比较数据的趋势。创建数据透视图有三种方法：创建数据透视图有三种方法：基于工作表基于工作表数据

10、创建数据透视图数据创建数据透视图外部数据源外部数据源创建法创建法基于数据透视表基于数据透视表创建数据透视图。创建数据透视图。计算中心【例【例6-6】基于工作表】基于工作表数据数据(Ex6-4.xlsx)创建创建数数据透视图据透视图计算中心【例【例6-7】利用外部】利用外部数据源数据源(Ex6-5.xlsx)创建创建数据透视图数据透视图计算中心【例【例6-8】基于数据透视】基于数据透视表表(Ex6-8.xlsx)创建创建数据透视图数据透视图计算中心4数据透视表和透视图的修改数据透视表和透视图的修改 更改行列标签更改行列标签就是将数据透视表行列标签的位置进就是将数据透视表行列标签的位置进行互换。行

11、互换。 通过通过更改数值的计算类型更改数值的计算类型，可以在数据透视表中相，可以在数据透视表中相识不同的数据汇总结果。识不同的数据汇总结果。 如果要如果要美化创建的数据透视表美化创建的数据透视表，可以采用单元格格，可以采用单元格格式设置方法来实现，也可以应用相关的数据透视表式设置方法来实现，也可以应用相关的数据透视表样式来实现。样式来实现。计算中心单变量求解单变量求解1单变量求解概述单变量求解概述单变量求解是解决假定一个公式要取的某一单变量求解是解决假定一个公式要取的某一结果值，其中变量的引用单元格应取值为多少的结果值，其中变量的引用单元格应取值为多少的问题。问题。在在Office Excel

12、中根据所提供的中根据所提供的目标值目标值，将，将引用引用单元格单元格的值的值不断调整，直至达到所需要求的不断调整，直至达到所需要求的公式公式的目标值的目标值时，变量的值才确定。时，变量的值才确定。计算中心2单变量求解应用案例单变量求解应用案例【例【例6-9】已知】已知方程方程7x4+5x2-6x=20 ，使用单变使用单变量求解方法求解方程量求解方法求解方程计算中心模拟运算模拟运算1模拟运算概述模拟运算概述模拟运算表是一种将工作表中单元格区域的数据进行模拟计算，模拟运算表是一种将工作表中单元格区域的数据进行模拟计算，显示更改显示更改公式公式中的一个或两个变量将如何影响这些公式的结果。中的一个或两

13、个变量将如何影响这些公式的结果。模拟运算表可提供在一次运算中计算多个结果的快捷方式，以模拟运算表可提供在一次运算中计算多个结果的快捷方式，以及查看和比较工作表中所有不同变量结果的方法。及查看和比较工作表中所有不同变量结果的方法。模拟运算表分为模拟运算表分为单变量运算表单变量运算表和和双变量运算表双变量运算表两种。单变量运两种。单变量运算表基于算表基于一个输入变量一个输入变量变化时对公式计算结果的影响，双变量变化时对公式计算结果的影响，双变量运算表可以对运算表可以对两个变量两个变量输入不同值时对公式的影响。输入不同值时对公式的影响。计算中心2单变量运算表案例单变量运算表案例【例【例6-10】某人

14、打算贷款购房，在贷款购房之前需】某人打算贷款购房，在贷款购房之前需要分析不同贷款利率下购房贷款的月还款额；同时要分析不同贷款利率下购房贷款的月还款额；同时还需要考虑分期付款期限为还需要考虑分期付款期限为13年中，不同年限的月年中，不同年限的月还款额，具体数据为：首付款还款额，具体数据为：首付款60万，基准利率万，基准利率6%（利率变化为（利率变化为5%7%之间），贷款额为之间），贷款额为80万，贷款万，贷款年限为年限为130年，如图年，如图6-38所示。还贷额可以通过固所示。还贷额可以通过固定利率及等金额分期付款函数定利率及等金额分期付款函数PMT( )求得。求得。计算中心案例中用到的函数说明

15、：函数格式为案例中用到的函数说明：函数格式为PMT(rate,nper,pv,fv,type)。其中参数其中参数rate为：为：贷款利率；贷款利率；nper为：总投资期或总贷款期；为：总投资期或总贷款期；pv为：从该项投资（或贷款）开始计算时已经入账的款项，或一为：从该项投资（或贷款）开始计算时已经入账的款项，或一系列未来付款当前值的累积和；系列未来付款当前值的累积和；fv为：可选参数，未来值，或在最后一次付款后希望得到的现金为：可选参数，未来值，或在最后一次付款后希望得到的现金余额，如果省略余额，如果省略 fv，则假设其值为，则假设其值为0（零），也就是一笔贷款的未（零），也就是一笔贷款的未

16、来值为来值为0；type为：期初和期末，为：期初和期末，0或省略为期末，或省略为期末，1为期初。为期初。计算中心3.双双变量模拟运算表案例变量模拟运算表案例【例【例6-11】已知三元一次方程】已知三元一次方程Z=4X+6Y+11，X为为110之间的整数，之间的整数，Y的变化范围为的变化范围为1120，用模拟运算表求解用模拟运算表求解Z的值的值。计算中心规划求解规划求解1规划求解概述规划求解概述规划求解是一组命令的组成部分，也是规划求解是一组命令的组成部分，也是Excel中的一个加载宏，中的一个加载宏，可用于可用于假设分析假设分析。使用规划求解，可求得工作表上某个单元格（被称为目标单元使用规划求

17、解，可求得工作表上某个单元格（被称为目标单元格）中公式的最优值。其优化模型包括三个部分：格）中公式的最优值。其优化模型包括三个部分：目标单元格目标单元格、可变单元格可变单元格和和约束条件约束条件。规划求解具有多种应用，如应用规划求解对多元一次方程进行规划求解具有多种应用，如应用规划求解对多元一次方程进行求解；企业在生产或财务安排时，在一定条件的约束下，通过求解；企业在生产或财务安排时，在一定条件的约束下，通过规划求解求出最合理的安排等。规划求解求出最合理的安排等。计算中心2.加载加载规划求解规划求解加载规划求解的操作步骤如下：加载规划求解的操作步骤如下：（1）在）在“文件文件”选项卡中，单击选

18、项卡中，单击“选项选项”按钮，打开按钮，打开“Excel选项选项”对话框。对话框。（2）单击左侧窗格中的）单击左侧窗格中的“加载项加载项”项，打开项，打开“查看和管理查看和管理Microsoft Office加载项加载项”窗口。窗口。（3）单击）单击“管理管理”右侧的下拉列表框，选择右侧的下拉列表框，选择“Excel加载项加载项”选项；选项；然后单击然后单击“转到转到”按钮，打开按钮，打开“加载宏加载宏”对话框。对话框。（4）在）在“可用加载宏可用加载宏”列表中勾选列表中勾选“规划求解加载项规划求解加载项”复选框，单复选框，单击击“确定确定”按钮，完成规划求解的加载操作，此时会在功能区的按钮，

19、完成规划求解的加载操作，此时会在功能区的“数据数据”选项卡中便会显示选项卡中便会显示“规划求解规划求解”按钮。按钮。计算中心3规划求解应用案例规划求解应用案例【例【例6-12】某公司生产和销售两种产品，两种产品每生产】某公司生产和销售两种产品，两种产品每生产一个单位需要的工时分别为一个单位需要的工时分别为3小时和小时和7小时，用电量分别为小时，用电量分别为4千瓦和千瓦和5千瓦，需要原材料分别为千瓦，需要原材料分别为9公斤和公斤和4公斤。公司可公斤。公司可提供的工时为提供的工时为300小时，可提供的用电量为小时，可提供的用电量为250千瓦，可提千瓦，可提供的原材料为供的原材料为420公斤。两种产

20、品的单位利润分别公斤。两种产品的单位利润分别200元和元和210元。要求通过规划求解方式来安排两种产品的生产量，元。要求通过规划求解方式来安排两种产品的生产量，使得获得的利润达到最大化。（初始产量假设每个产品分使得获得的利润达到最大化。（初始产量假设每个产品分别生产别生产1个单位）。个单位）。计算中心方案管理器方案管理器1方案管理器概述方案管理器概述 方案管理器是一种方案管理器是一种数据分析工具数据分析工具，是用于预测工作表模，是用于预测工作表模型结果的一组数值，并且可以在工作表中创建并保存多型结果的一组数值，并且可以在工作表中创建并保存多个不同的方案，还可以在这些方案之间任意切换，查看个不同

21、的方案，还可以在这些方案之间任意切换，查看不同方案的结果。不同方案的结果。 方案管理器可以进行多方案的选择，企业对于较为复杂方案管理器可以进行多方案的选择，企业对于较为复杂的计划，可能需要制定多个方案进行比较，然后进行决的计划，可能需要制定多个方案进行比较，然后进行决策。策。计算中心2方案应用案例方案应用案例【例例6-13】一个公司一个公司2012年销售情况和年销售情况和2013年预计销售增长情况年预计销售增长情况的原始数据，的原始数据，2013年的增长预计分为年的增长预计分为好好、一般一般和和差差三种状态，现三种状态，现在要求根据有关预计增长数据生成方案。销售利润在要求根据有关预计增长数据生

22、成方案。销售利润=销售额销售额 销售销售成本，总销售利润是各产品销售利润之和。成本，总销售利润是各产品销售利润之和。案例中用到的函数说明：案例中用到的函数说明：SUMPRODUCT函数的功能为在给定函数的功能为在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和，其的几组数组中，将数组间对应的元素相乘，并返回乘积之和，其语法格式为语法格式为SUMPRODUCT（array1, array2, array3,）。其中）。其中array1, array2, array3,为数组，数组个数介于为数组，数组个数介于2255之间。之间。计算中心数据分析工具库数据分析工具库数据分析工具库能够帮助使用者数据分析工具库能够帮