与数据处理数据分析工具及应用ppt课件

1、Excel与数据处置与数据处置本章教学目的与要求本章教学目的与要求1 1、掌握宏的加载方法、掌握宏的加载方法2 2、掌握追踪从属或援用单元格的方法、掌握追踪从属或援用单元格的方法3 3、掌握限定单元格数据的范围及圈释无效数据的、掌握限定单元格数据的范围及圈释无效数据的 运用方法运用方法4 4、掌握模拟运算表及变量求解的运用、掌握模拟运算表及变量求解的运用5 5、掌握方案的建立和运用、掌握方案的建立和运用6 6、掌握规划求解工具的运用、掌握规划求解工具的运用7 7、了解假设检验和回归分析等工具的运用、了解假设检验和回归分析等工具的运用本章重点、难点及学时数本章重点、难点及学时数n重点：重点：n掌

2、握数据审核的方法掌握数据审核的方法n掌握模拟运算表的运用掌握模拟运算表的运用n掌握单变量求解的运用掌握单变量求解的运用n掌握方案的运用掌握方案的运用n掌握规划求解的运用掌握规划求解的运用n难点：难点：n掌握规划求解的运用掌握规划求解的运用n学时数：学时数：n1212学时上机学时上机6 6学时学时本章目录本章目录7.1 分析工具的安装分析工具的安装7.2 数据审核及跟踪分析数据审核及跟踪分析7.3 模拟运算表模拟运算表7.4 单变量求解单变量求解7.5 方案分析方案分析7.6 线性规划求解线性规划求解7.7 数据分析工具库数据分析工具库小结小结思索与练习思索与练习7.1 分析工具的安装分析工具的

3、安装1、加载宏的概念、加载宏的概念加载宏是一种可选择性地安装到计算机中的软件组件，用户加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需求决议能否安装。其作用是为可根据需求决议能否安装。其作用是为 Excel 添加命添加命令和函数，扩展令和函数，扩展Excel的功能。的功能。Excel加载宏的扩展名是加载宏的扩展名是.xla或或.xll。在默许情况下，在默许情况下，Excel将下表列出的加载宏程序安装在如下将下表列出的加载宏程序安装在如下某一磁盘位置：某一磁盘位置：“Microsoft OfficeOffice文件夹下文件夹下的的“Library文件夹或其子文件夹，或文件夹或其子文件

4、夹，或 Windows 所所在文件夹下的在文件夹下的“Profiles用户名用户名Application DataMicrosoftAddIns文件夹下。网络管理员也文件夹下。网络管理员也可将加载宏程序安装到其他位置。可将加载宏程序安装到其他位置。 7.1 分析工具的安装分析工具的安装2、Excel内置加载宏内置加载宏加加 载载 宏宏描描 述述分析工具分析工具库库添加财务、统计和工程分析工具和函数添加财务、统计和工程分析工具和函数条件求和条件求和向导向导对于数据清单中满足指定条件的数据进行求和对于数据清单中满足指定条件的数据进行求和计算计算欧元工具欧元工具将数值的格式设置为欧元的格式，并提供将

5、数值的格式设置为欧元的格式，并提供EUROCONVERT函数以用于转换货币函数以用于转换货币查阅向导查阅向导创建一个公式，通过数据清单中的已知值查找创建一个公式，通过数据清单中的已知值查找所需数据所需数据ODBC 加加载宏载宏利用安装的利用安装的 ODBC 驱动程序，通过开放式数据驱动程序，通过开放式数据库互连（库互连（ODBC）功能与外部数据源相连）功能与外部数据源相连7.1 分析工具的安装分析工具的安装报告管理报告管理器器为工作簿创建含有不同打印区域、自定义视面为工作簿创建含有不同打印区域、自定义视面以及方案的报告以及方案的报告规划求解规划求解对基于可变单元格和条件单元格的假设分析方对基于

6、可变单元格和条件单元格的假设分析方案进行求解计算案进行求解计算模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置模板时就可自动访问这些工具模板时就可自动访问这些工具Internet Assistant VBA通过使用通过使用 Excel 97 Internet Assistant 语法，开语法，开发者可将发者可将 Excel 数据发布到数据发布到 Web 上上7.1 分析工具的安装分析工具的安装3、安装分析工具、安装分析工具选择选择“工具工具|“加载宏菜单加载宏菜单在对话框中选择所需工具，在对话框中选择所需工具，按确定按确定 注：假设在安装注

7、：假设在安装EXCEL系统时没有安装加载宏，那么必需重系统时没有安装加载宏，那么必需重新启动新启动EXCEL的安装程序，选择其中的的安装程序，选择其中的“添加添加/删除命删除命令，安装令，安装EXCEL的加载宏。的加载宏。7.2 数据审核及跟踪分析数据审核及跟踪分析1、概念、概念数据审核是一种查找单元格数据错误来源的工具，快速地找出数据审核是一种查找单元格数据错误来源的工具，快速地找出具有援用关系的单元格，借此分析呵斥错误的单元格。具有援用关系的单元格，借此分析呵斥错误的单元格。数据审核运用追踪箭头，经过图形的方式显示或追踪单元格与数据审核运用追踪箭头，经过图形的方式显示或追踪单元格与公式之间

8、的关系。公式之间的关系。 2、数据审核的方式、数据审核的方式追踪援用单元格追踪援用单元格见见ch7.xls追踪援用单元格追踪援用单元格 操作方法：选定菜单操作方法：选定菜单“工具工具“审核审核 显示显示审核审核工具栏工具栏选择要追踪援用的含公式单元格选择要追踪援用的含公式单元格“审核工具栏中审核工具栏中“追踪援追踪援用单元格按钮用单元格按钮再次单击再次单击“追踪援用单元格按钮提供数追踪援用单元格按钮提供数据的下一级单元格据的下一级单元格移去援用单元格追踪箭头：移去援用单元格追踪箭头： 操作方法：选择操作方法：选择“审核工具栏中审核工具栏中“移去援用单元格中追踪箭移去援用单元格中追踪箭头头7.2

9、 数据审核及跟踪分析数据审核及跟踪分析追踪从属单元格追踪从属单元格见见ch7.xls追踪从属单元格追踪从属单元格某单元格公式援用了其它单元格，那么该单元格为从属单元格。某单元格公式援用了其它单元格，那么该单元格为从属单元格。 操作方法：选定菜单操作方法：选定菜单“工具工具“审核审核 显示显示审核审核工具栏工具栏选择要追踪从属单元格的单元格选择要追踪从属单元格的单元格“审核工具栏中审核工具栏中“追踪从追踪从属单元格按钮属单元格按钮再次单击再次单击“追踪从属单元格按钮提供从属追踪从属单元格按钮提供从属的的单元格的的单元格移去援用单元格追踪箭头：移去援用单元格追踪箭头： 操作方法：选择操作方法：选择

10、“审核工具栏中审核工具栏中“移去从属单元格中追踪箭移去从属单元格中追踪箭头头7.2 数据审核及跟踪分析数据审核及跟踪分析3、 数据有效性数据有效性数据有效性：对数据进展检验和检查的有效方法，把错误数据有效性：对数据进展检验和检查的有效方法，把错误限制在数据输入阶段。限制在数据输入阶段。 限定数据类型和有效范围：限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式单元格的公式7.2 数据审核及跟踪分析数据审核及跟踪分析数据限制的操作方法：选择数据限制的操作方法：选择“数据数据 “有效性有效性在对话在对话 框中操

11、作：框中操作：限定文本长度：限定文本长度： “设置选项卡中设置选项卡中“允许允许 下拉列表中选下拉列表中选择文本长度。择文本长度。限定数据的有效范围：限定数据的有效范围：“设置选项卡中设置选项卡中“允许下拉列表允许下拉列表中选择整数中选择整数/小数小数- 确定最大确定最大/小值小值设置单元格有效范围：设置单元格有效范围：“设置选项卡中设置选项卡中“允许下拉列表允许下拉列表中选择序列中选择序列输入序列值输入序列值设置输入提示信息：设置输入提示信息： “输入信息选项卡中输入要显示的输入信息选项卡中输入要显示的信息信息7.2 数据审核及跟踪分析数据审核及跟踪分析n例：例：见见ch7.xls限定数据范

12、围限定数据范围n 某班要建立一个成果登记表，为了减少成果输入错某班要建立一个成果登记表，为了减少成果输入错误，可对成果表中数据的输入类型及范围进展限制。误，可对成果表中数据的输入类型及范围进展限制。n限制学号为限制学号为8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制一切学科成果为限制一切学科成果为0100之间的整数。之间的整数。n限制科目列标题的取值范围，如限制科目列标题的取值范围，如“高数不能输入为高数不能输入为“高等数学。高等数学。7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据、圈释无效数据 运用数据有效性规那么可限制单元格可接纳的数据，但对

13、运用数据有效性规那么可限制单元格可接纳的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无已输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规那么的错误单元效数据的方法，可以显示不满足有效性规那么的错误单元格。格。操作方法：选择数据区域操作方法：选择数据区域设置数据有效性规那么设置数据有效性规那么选选择择“工具菜单工具菜单“审核审核选择选择“显示审核工具栏显示审核工具栏选选中有效性检测的数据区域中有效性检测的数据区域单击单击“审核工具栏的审核工具栏的“圈释无圈释无效数据按钮效数据按钮 注：要先设置数据的有效范围，然后再圈释无效数据注：要先设置数据的有

14、效范围，然后再圈释无效数据例：例：见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成果登记表，曾经对成果表中数据的输入某班要建立一个成果登记表，曾经对成果表中数据的输入类型及范围进展限制，找出其中不符合规定的数据。类型及范围进展限制，找出其中不符合规定的数据。7.3 模拟运算表模拟运算表1、概念、概念模拟运算表是对任务表中一个单元格区域内的数据进展模拟模拟运算表是对任务表中一个单元格区域内的数据进展模拟运算，测试运用一个或两个变量的公式中变量对运算结果运算，测试运用一个或两个变量的公式中变量对运算结果的影响。的影响。2、模拟运算表的类型、模拟运算表的类型基于一个输入变量的表，用这个输

15、入变量测试它对多个公基于一个输入变量的表，用这个输入变量测试它对多个公式的影响；式的影响；单模拟运算表单模拟运算表基于两个输入变量的表，用这两个变量测试它们对于单个基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响公式的影响双模拟运算表双模拟运算表7.3 模拟运算表模拟运算表3、单变量模拟运算表、单变量模拟运算表概念概念在单变量模拟运算表中，输入数据的值被安排在一行或一列中。在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中运用的公式必需援用同时，单变量模拟表中运用的公式必需援用“输入单元格。输入单元格。输入单元格，就是被交换的含有输入数据的单元格输入单元格

16、，就是被交换的含有输入数据的单元格 操作步骤：操作步骤：1、在任务表中建立模拟运算表的构造；、在任务表中建立模拟运算表的构造；2、输入模拟运算表要用到的公式；、输入模拟运算表要用到的公式；3、选择包括公式、援用单元格和运算结果单元格区域、选择包括公式、援用单元格和运算结果单元格区域3部部分；分；4、选择、选择“数据菜单数据菜单“模拟运算表选项；模拟运算表选项；5、在、在“模拟运算表对话框中输入援用单用格行或列一模拟运算表对话框中输入援用单用格行或列一种种 确定确定7.3 模拟运算表模拟运算表n例：例：见见ch7.xls单变量模拟运算表单变量模拟运算表n 假设某人正思索购买一套住房，要承当一笔假

17、设某人正思索购买一套住房，要承当一笔250 000元的贷款，分元的贷款，分15年还清。现想查看每月的还贷金额，年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。并想查看在不同的利率下，每月的应还贷金额。 n 假设贷款额分别为假设贷款额分别为400 000，550 000，800 000元，每月的应还贷金额又是多少？元，每月的应还贷金额又是多少？ 7.3 模拟运算表模拟运算表4、双变量模拟运算表、双变量模拟运算表概念：概念： 单变量模拟运算表只能处理一个输入变量对一个或多个公式计单变量模拟运算表只能处理一个输入变量对一个或多个公式计算结果的影响，要查看两个变量对公式计算结

18、果的影响，就要算结果的影响，要查看两个变量对公式计算结果的影响，就要用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两个变量。公式中两个变量所在的单元格是任取的。可以是任务个变量。公式中两个变量所在的单元格是任取的。可以是任务表中恣意空白单元格。表中恣意空白单元格。7.3 模拟运算表模拟运算表n操作步骤：操作步骤：n1、在任务表中建立模拟运算表的构造；、在任务表中建立模拟运算表的构造；n2、在行列交叉处输入模拟运算表要用到的公式；、在行列交叉处输入模拟运算表要用到的公式；n3、选择包括公式、选择包括公式,援用单元格和运算结果单元格区域援

19、用单元格和运算结果单元格区域3部分；部分；n4、选择、选择“数据菜单数据菜单“模拟运算表选项；模拟运算表选项；n5、在、在“模拟运算表对话框中输入公式中行和列援用模拟运算表对话框中输入公式中行和列援用的单用格的单用格确定确定n例：例：见见ch7.xls双变量模拟运算表双变量模拟运算表n 假设某人想贷款假设某人想贷款45万元购买一部车，要查看在不同万元购买一部车，要查看在不同的利率和不同的归还年限下，每个月应还的贷款金额。的利率和不同的归还年限下，每个月应还的贷款金额。假设要查看贷款利率为假设要查看贷款利率为5%、5.5%、6.5%、7%、7.5%、8%，归还期限为，归还期限为10年、年、15年

20、、年、20年、年、30年、年、35年时，每月应归还的贷款金额是多少年时，每月应归还的贷款金额是多少 ？7.4 单变量求解单变量求解1、概念、概念所谓单变量求解，就是求解具有一个变量的方程，所谓单变量求解，就是求解具有一个变量的方程，Excel经经过调整可变单元格中的数值，使之按照给定的公式来满足过调整可变单元格中的数值，使之按照给定的公式来满足目的单元格中的目的值目的单元格中的目的值.2、单变量求解方法、单变量求解方法在任务表中输入原始数据；在任务表中输入原始数据；建立可变数公式；建立可变数公式；设置求解公式：菜单设置求解公式：菜单“工具工具单变量求解单变量求解对话框中输入：对话框中输入：目的

21、单元格、目的值、可变单元格目的单元格、目的值、可变单元格例：例： 见见ch7.xls单变量求解单变量求解 某公司想向银行贷款某公司想向银行贷款900万元人民币，贷款利率是万元人民币，贷款利率是8.7%，贷款限期为贷款限期为8年，每年应归还多少金额？年，每年应归还多少金额？ 假设公司每年可归还假设公司每年可归还120万元，该公司最多可贷款多少金万元，该公司最多可贷款多少金额？额？ 前一问题可用前一问题可用PMT函数函数, 后一问题可用单变量求解。后一问题可用单变量求解。7.5 方案分析方案分析1、概念、概念方案是已命名的一组输入值，是方案是已命名的一组输入值，是 Excel 保管在任务表中并可用

22、保管在任务表中并可用来自动交换某个计算模型的输入值，用来预测模型的输出结果。来自动交换某个计算模型的输入值，用来预测模型的输出结果。 例例:知某茶叶公司知某茶叶公司2019年的总销售额及各种茶叶的销售本钱，现要年的总销售额及各种茶叶的销售本钱，现要在此根底上制定一个五年方案。由于市场竞争的不断变化，所在此根底上制定一个五年方案。由于市场竞争的不断变化，所以只能对总销售额及各种茶叶销售本钱的增长率做一些估计。以只能对总销售额及各种茶叶销售本钱的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售本钱增长最好的方案当然是总销售额增长率高，各茶叶的销售本钱增长率低。率低。 最好的估计是总

23、销售额增长最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、红茶，花茶、绿茶、乌龙茶、红茶的销售本钱分别增长的销售本钱分别增长10%、6%、10%、7%。 见见ch7.xls方案方案7.5 方案分析方案分析n建立方案处理任务表建立方案处理任务表建立方法如下，输入下表建立方法如下，输入下表A列、列、B列及第列及第3行的一切数行的一切数据；在据；在C4单元格中输入公单元格中输入公式式“=B4*(1+$B$16)，然后将其复制到然后将其复制到D4F4；在在C7中输入公式中输入公式“=B7*(1+$B$17),并并将其复制到将其复制到D7F7；在在C8中输入公式中输入公式“=B8*(1+$B$18)

24、，并将其复制到并将其复制到D8和和F8；在在C9中输入公式中输入公式“=B9*(1+$B$19)，并将其复制到并将其复制到D9F9；在在C10中输入公式中输入公式“=B10*(1+$B$20)，并将其复制到并将其复制到D10F10；第第11行数据是第行数据是第7，8，9，10行数据对应列之和；净行数据对应列之和；净收入是相应的总销售额和收入是相应的总销售额和销售本钱之差，销售本钱之差，E19的总的总净收入是第净收入是第13行数据之和。行数据之和。 7.5 方案分析方案分析输入方案变量值如以下图所示：输入方案变量值如以下图所示：7.5 方案分析方案分析2、显示方案、显示方案 选择选择“工具工具

25、“方案菜单方案菜单选择选择“方案管理器对话框方案管理器对话框中的某一方案中的某一方案单击单击 “显示按钮显示按钮3、建立方案报告、建立方案报告见见ch7.xls方案摘要方案摘要 选择选择“工具工具 “方案菜单方案菜单选择选择“方案管理器对话框方案管理器对话框中的某一方案中的某一方案单击单击 “总结按钮总结按钮在在“方案总结对话方案总结对话框中结果类型中选择框中结果类型中选择“方案总结方案总结4、建立方案透视图、建立方案透视图见见ch7.xls方案数据透视图方案数据透视图 选择选择“工具工具 “方案菜单方案菜单选择选择“方案管理器对话框方案管理器对话框中的某一方案中的某一方案单击单击 “总结按钮

26、总结按钮在在“方案总结对话方案总结对话框中结果类型中选择框中结果类型中选择“方案数据透视表方案数据透视表7.6 线性规划求解线性规划求解1、概述、概述 EXCEL提供的规划求解工具，可求解出线性与非线性两提供的规划求解工具，可求解出线性与非线性两种规划求解问题，规划求解问题常用于处理产品比例、人种规划求解问题，规划求解问题常用于处理产品比例、人员调度、优化道路、调配资料等方面问题。员调度、优化道路、调配资料等方面问题。2、规划求解问题的特点：、规划求解问题的特点：问题有单一的目的，如求运输的最正确道路、求消费的最低问题有单一的目的，如求运输的最正确道路、求消费的最低本钱、求产品的最大盈利，求产

27、品周期的最短时间等。本钱、求产品的最大盈利，求产品周期的最短时间等。问题有明确的不等式约束条件，例如消费资料不能超越库存，问题有明确的不等式约束条件，例如消费资料不能超越库存，消费周期不能超越一个星期等。消费周期不能超越一个星期等。问题有直接或间接影响约束条件的一组输入值。问题有直接或间接影响约束条件的一组输入值。 7.6 线性规划求解线性规划求解3、Excel规划求解问题的组成部分规划求解问题的组成部分 1一个或一组可变单元格一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划求可变单元格称为决策变量，一组决策变量代表一个规划求解的方案解的方案2目的函数目的函数目的函数表示

28、规划求解要到达的最终目的，是规划求解的关目的函数表示规划求解要到达的最终目的，是规划求解的关键。它是规划求解中可变量的函数键。它是规划求解中可变量的函数 3约束条件约束条件约束条件是实现目的的限制条件。约束条件是实现目的的限制条件。 意义：经过规划求解，用户可为任务表的目的单元格中意义：经过规划求解，用户可为任务表的目的单元格中的公式找到一个优化值，规划求解将直接或间接与目的单的公式找到一个优化值，规划求解将直接或间接与目的单元格公式相联络的一组单元格数值进展调整，最终在目的元格公式相联络的一组单元格数值进展调整，最终在目的单元格公式中求得期望的结果。单元格公式中求得期望的结果。7.6 线性规

29、划求解线性规划求解例：例： 见见ch7.xls规划求解规划求解某肥料厂专门搜集有机物渣滓，如青草、树枝、凋谢的花朵某肥料厂专门搜集有机物渣滓，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来消费高质量的植物肥料，消费的肥料分为底层肥料、中来消费高质量的植物肥料，消费的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料层肥料、上层肥料、劣质肥料4种。为使问题简单，假设种。为使问题简单，假设搜集废物的劳动力是自愿的，除了搜集本钱之外，资料本搜集废物的劳动力是自愿的，除了搜集本钱之外，资料本钱是低廉的。钱是低廉的。该厂目前的原

30、资料、消费各种肥料需求的原资料比例，各种该厂目前的原资料、消费各种肥料需求的原资料比例，各种肥料的单价等如下页各表所示。肥料的单价等如下页各表所示。问题：求出在现有的情况下，即利用原资料的现有库存，应问题：求出在现有的情况下，即利用原资料的现有库存，应消费各种类型的肥料各多少数量才干获得最大利润，最大消费各种类型的肥料各多少数量才干获得最大利润，最大利润是多少？利润是多少？ 分析分析:所求是在现有的原资料情况下所求是在现有的原资料情况下,应如何合理搭配应如何合理搭配,才干获才干获取消费产品的最大利润取消费产品的最大利润.7.6 线性规划求解线性规划求解表表2 消费肥料的库存原资料消费肥料的库存

31、原资料库存库存情况情况现有现有库存库存泥土泥土4100有机有机垃圾垃圾3200矿物矿物质质3500修剪修剪物物表1 各肥料废品用料及其价钱表表的意思是消费一个单位的肥料需求多少各种原资料多少单位产品产品泥土泥土有机垃圾有机垃圾矿物质矿物质修剪物修剪物单价单价底层肥料底层肥料55547623105.00中层肥料中层肥料6432452084.00上层肥料上层肥料43329844105.00劣质肥料劣质肥料1845231857.00表表3单位原资料本钱单价单位原资料本钱单价项项 目目单位成本单位成本泥土泥土0.20有机垃圾有机垃圾0.15矿物质矿物质0.10修剪物修剪物0.237.6 线性规划求解线

32、性规划求解n建立规划求解模型步骤：建立规划求解模型步骤：n规划求解第一步规划求解第一步建立求解任务表建立求解任务表(输入原始数据及相应输入原始数据及相应的各公式的各公式)7.6 线性规划求解线性规划求解规划求解第二步规划求解第二步设置求解参数设置求解参数选择选择“工具工具 “规划求解菜单，设置以下求解的各项参规划求解菜单，设置以下求解的各项参数：数：设置目的单元格：输入目的函数所在单元格设置目的单元格：输入目的函数所在单元格(为总余额单元为总余额单元格格)设置目的：最大值、最小值或值的数值设置目的：最大值、最小值或值的数值(最大利润最大利润,即最大值即最大值)设置可变单元格：它确实定决议结果为

33、消费数量设置可变单元格：它确实定决议结果为消费数量设置约束条件：单击设置约束条件：单击“添加按钮添加按钮输入约束条件输入约束条件按添按添加加依次输入一切约束条件依次输入一切约束条件确定确定7.6 线性规划求解线性规划求解n规划求解第规划求解第3步步保管求解结果保管求解结果n在规划求解对话框中按在规划求解对话框中按“求解求解在规划求解结果对话在规划求解结果对话框中按框中按“保管规划求解结果保管规划求解结果7.6 线性规划求解线性规划求解4、修正资源、修正资源 例例1：见见ch7.xls规划求解规划求解肥料厂接到一个：只需公司肯花肥料厂接到一个：只需公司肯花10元的运费就能得到元的运费就能得到15

34、0个个单位的矿物。这笔买卖稍稍降低了矿物质的平均价钱，但单位的矿物。这笔买卖稍稍降低了矿物质的平均价钱，但这些矿物质值这些矿物质值10元吗？元吗？处理该问题的方法是，将库存矿物处理该问题的方法是，将库存矿物3500改为改为3650，用规划，用规划求解重新计算最大盈余。看除去￥求解重新计算最大盈余。看除去￥10的本钱后，盈余能的本钱后，盈余能否添加否添加 操作操作: 将库存矿物将库存矿物3500改为改为3650,其它一切公式不变其它一切公式不变,再次进展再次进展求解求解,求得盈余额为求得盈余额为4483.41,原盈余额为原盈余额为4425.89. 可知盈利为可知盈利为57.52.扣除扣除10元本

35、钱后仍有元本钱后仍有47.52.因此该矿物因此该矿物还是要的还是要的.7.6 线性规划求解线性规划求解5、修正约束条件、修正约束条件 见见ch7.xls规划求解规划求解肥料厂接到一个，一个老顾客急需肥料厂接到一个，一个老顾客急需25个单位的上层肥料，个单位的上层肥料，公司经理在检查打印结果后，发现没有安排消费上层肥料。公司经理在检查打印结果后，发现没有安排消费上层肥料。数量为数量为0。决议添加约束条件，为他消费。决议添加约束条件，为他消费25个单位的上层个单位的上层肥料。肥料。结果可发现结果可发现:盈余额仅盈余额仅3246.51,比原来比原来4483.41少了少了1236.9。显然不值得。但如

36、该顾客为长期顾客，那么短。显然不值得。但如该顾客为长期顾客，那么短期内将损失一些钱，但得到了顾客的信任。期内将损失一些钱，但得到了顾客的信任。添加的添加的约束条约束条件件7.6 线性规划求解线性规划求解6、 规划求解的结果报告规划求解的结果报告运算结果报告：列出目的单元格、可变单元格及它们的初始值、运算结果报告：列出目的单元格、可变单元格及它们的初始值、最终结果、约束条件和有关约束条件的信息。最终结果、约束条件和有关约束条件的信息。 见见ch7.xls运算结果报告运算结果报告7.6 线性规划求解线性规划求解n敏感性报告：敏感性报告： 见见ch7.xls敏感性报告敏感性报告7.6 线性规划求解线

37、性规划求解n极限报告：列出目的单元格、可变单元格及它们的数极限报告：列出目的单元格、可变单元格及它们的数值、上下限和目的值。下限为在满足约束条件和坚持值、上下限和目的值。下限为在满足约束条件和坚持其它可变单元格数值不变的情况下，某个可变单元格其它可变单元格数值不变的情况下，某个可变单元格可以获得的最小值，上限那么为在这种情况下可以取可以获得的最小值，上限那么为在这种情况下可以取到的最大值。到的最大值。 见见ch7.xls极限值报告极限值报告7.6 线性规划求解线性规划求解7、求解精度及求解模型设置、求解精度及求解模型设置 Excel采用迭代的方式进展规划求解，当求解到一定精度采用迭代的方式进展

38、规划求解，当求解到一定精度时就终了求解，但有时要修正求解的精度、计算时间、规时就终了求解，但有时要修正求解的精度、计算时间、规划模型和迭代次数。修正上述设置的方法如下：划模型和迭代次数。修正上述设置的方法如下： 在在“规划求解参数对话框中设置好各项求解参数；规划求解参数对话框中设置好各项求解参数； 单击单击“选项按钮，在选项按钮，在“规划求解选项对话框中设置各规划求解选项对话框中设置各项求解参数。项求解参数。 7.6 线性规划求解线性规划求解例例2：求解不等式：求解不等式：见见ch7.xls规划求解不等式规划求解不等式某工厂消费甲、乙两种产品，假设消费甲产品某工厂消费甲、乙两种产品，假设消费甲

39、产品1吨，要耗费吨，要耗费9吨吨煤，煤，4千瓦电力，千瓦电力，3吨钢材，获利吨钢材，获利0.7万元；消费乙产品万元；消费乙产品1吨，要吨，要耗费耗费4吨煤，吨煤，5千瓦电力，千瓦电力，10吨钢材，获利吨钢材，获利1.2万元。按方案国万元。按方案国家能提供应该厂的煤为家能提供应该厂的煤为360吨，电力吨，电力200千瓦，钢材千瓦，钢材300吨，问吨，问应该消费多少吨甲种产品和乙种产品，才干获得最大利润？应该消费多少吨甲种产品和乙种产品，才干获得最大利润？ 假设消费甲种产品假设消费甲种产品X1吨，消费乙种产品吨，消费乙种产品x2吨，吨， 其最大利润是求其最大利润是求=0.7x1+1.2x2的最大值

40、。这个问题可用数的最大值。这个问题可用数学建模如下：学建模如下：.0,0.300103.20054.3604921212121xxxxxxxx7.6 线性规划求解线性规划求解规划求解如下：规划求解如下：B3和和C3分别用于保管甲和乙产品的消费量。分别用于保管甲和乙产品的消费量。目的单元格为目的单元格为B8；可变单元格为；可变单元格为$B$3:$C$3；约束条件为：；约束条件为：$B$3=0$C$3=0$B$4=360$B$5=200$B$6=3007.6 线性规划求解线性规划求解n例例3： 见见ch7.xls线形规划求解线形规划求解n某公司在某公司在A地有一个消费基地地有一个消费基地,其消费才

41、干为其消费才干为400,随着市随着市场需求的增长及该公司业务量的增大场需求的增长及该公司业务量的增大,现有现有3个配送中心的个配送中心的需求都在增长需求都在增长,估计分别为估计分别为200,400,300。公司正思索再。公司正思索再建立一个消费才干为建立一个消费才干为500的工厂，预备建在的工厂，预备建在B地。从地。从A地地的工厂向的工厂向3个配送中心的单位运输本钱分别为个配送中心的单位运输本钱分别为5.0元，元，6.0元，元，5.4元，从元，从B地的工厂向地的工厂向3个配送中心的单位运输本钱个配送中心的单位运输本钱是是7.0元，元，4.6元，元，6.6元。应怎样分配元。应怎样分配A、B两地到

42、两地到3个配个配送中心的产品量，才干使运输本钱最小？送中心的产品量，才干使运输本钱最小？n规划模型解释如下：规划模型解释如下：7.6 线性规划求解线性规划求解D5、E5、F5表示从A厂将1个单位产品分别送到配送中心1，2，3的费用；D7，E7，F7表示从B厂将1个单位产品分别送到配送中心1，2，3的费用；D6，E6，F6为可变单元格，保管从A厂运到3个配送中心的最正确产品量；D8，E8，F8为可变单元格，保管从B厂运到3个配送中心的最正确产品量；D10，E10，F10表示配送中心1，2，3的最大负荷才干；H5，H7分别是A厂、B厂的消费才干， H9是A，B两厂的总消费才干7.6 线性规划求解线

43、性规划求解G6G6，G8G8分别为分别为A A，B B两厂各自的产品总量。两厂各自的产品总量。G6G6公式：公式：=D6+E6+F6=D6+E6+F6；G8G8公式：公式：=G8+E8+F8=G8+E8+F8；D9D9，E9E9，F9F9分别为两厂送到各配送中心的产品总量。分别为两厂送到各配送中心的产品总量。D9D9公式：公式：=D6+D8=D6+D8；E9E9公式：公式：=E6+E8=E6+E8；F9F9公式：公式：=F6+F8=F6+F8；本模型的约束条件分析如下：本模型的约束条件分析如下：A A，B B两厂送到各配送中心的产品总量不能超越各配送中心的负两厂送到各配送中心的产品总量不能超越

44、各配送中心的负荷才干：荷才干：D9D9：F9=D10F9=0F6=0，D8:F8=0D8:F8=0本模型的目的函数，求以下公式本模型的目的函数，求以下公式的最大值：的最大值：S=D5S=D5* *D6+E5D6+E5* *E6+F5E6+F5* *F6+D7F6+D7* *D8+E7D8+E7* *E8+F7E8+F7* *F8F87.7 数据分析工具库数据分析工具库1、概述、概述Excel提供了一组数据分析工具，称为分析工具库。其中提提供了一组数据分析工具，称为分析工具库。其中提供的分析工具在工程分析、数理统计、经济计量分析等学供的分析工具在工程分析、数理统计、经济计量分析等学科中有较强的适

45、用价值。科中有较强的适用价值。分析工具库由分析工具库由Excel自带的加载宏提供。自带的加载宏提供。 假设启动假设启动Excel后，在后，在Excel的的“工具菜单中没有工具菜单中没有“数据分数据分析菜单项，就需启动析菜单项，就需启动“工具中的工具中的“加载宏菜单项，将加载宏菜单项，将“分析工具库加载到分析工具库加载到Excel系统中。系统中。假设加载宏对话框中没有分析工具库，那么单击加载宏对话假设加载宏对话框中没有分析工具库，那么单击加载宏对话框中框中“阅读按钮，定位到分析工具库加载宏文件阅读按钮，定位到分析工具库加载宏文件“Analy32.dll所在的驱动器和文件夹，通常位于所在的驱动器和

46、文件夹，通常位于“Microsoft OfficeOfficeLibraryAnalysis中中,否那否那么需运转么需运转Office系统的安装程序。系统的安装程序。Excel的的“分析工具库加载宏提供的一些统计函数、财务函分析工具库加载宏提供的一些统计函数、财务函数和工程函数。这些函数只需在安装了数和工程函数。这些函数只需在安装了“分析工具库后分析工具库后才干运用才干运用 。7.7 数据分析工具库数据分析工具库2、Excel分析工具库中的工具分析工具库中的工具分析工具分析工具名称名称说说 明明方差分析方差分析 包括包括3种类型的分析，它们是单因素方差分析、可种类型的分析，它们是单因素方差分析

47、、可重复双因素分析、无重复双因素分析重复双因素分析、无重复双因素分析相关系数相关系数分析分析用于判断两组数据集（可以使用不同的度量单位）用于判断两组数据集（可以使用不同的度量单位）之间的关系。之间的关系。协方差分协方差分析析用于返回各数据点的一对均值偏差之间的乘积的用于返回各数据点的一对均值偏差之间的乘积的平均值。平均值。描述统计描述统计分析分析用于生成对输入区域中数据的单变值分析，提供用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息有关数据趋中性和易变性的信息指数平滑指数平滑分析分析基于前期预测值导出相应的新预测值，并修正前基于前期预测值导出相应的新预测值，并修正前期预

48、测值的误差。期预测值的误差。7.7 数据分析工具库数据分析工具库傅里叶分傅里叶分析析解决线性系统问题，并能通过快速傅里叶变换解决线性系统问题，并能通过快速傅里叶变换（FFT）分析周期性的数据。）分析周期性的数据。F-检验检验用来比较两个样本总体的方差用来比较两个样本总体的方差直方图分直方图分析析在给定工作表中数据单元格区域和接收区间的情在给定工作表中数据单元格区域和接收区间的情况下，计算数据的个别和累计频率况下，计算数据的个别和累计频率移动平均移动平均分析分析基于特定的过去某段时期中变量的均值，对未来基于特定的过去某段时期中变量的均值，对未来值进行预测值进行预测t-检验分检验分析析提供提供3种

49、不同检验：双样本等方差假设种不同检验：双样本等方差假设t-检验，双检验，双样本异方差假设样本异方差假设t-检验，平均值的成对双样本检验，平均值的成对双样本t-检验检验回归分析回归分析通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直线拟合，直线拟合，进行线形回归分析。进行线形回归分析。抽样分析抽样分析以输入区域为总体构造总体的一个样本以输入区域为总体构造总体的一个样本z-检验检验双样本平均差检验双样本平均差检验7.7 数据分析工具库数据分析工具库3、 统计分析统计分析 Excel的分析工具库提供了的分析工具库提供了3种统计观测分析工具：指数平种统计观测分析工具：指数平滑分析、挪

50、动平均分析和回归分析滑分析、挪动平均分析和回归分析 三种工具用法一样，下面以指数平滑分析为例。三种工具用法一样，下面以指数平滑分析为例。见见ch7.xls 指数平滑分析指数平滑分析1在任务表的一列上输入各时间点上的察看值，如以下在任务表的一列上输入各时间点上的察看值，如以下图图A列所示。列所示。 7.7 数据分析工具库数据分析工具库2 2选择选择“工具菜单工具菜单“数据分析数据分析 选项，对话框中选择选项，对话框中选择“指指数平滑按确定。数平滑按确定。 3 3在在“指数平滑对话框中设置指数平滑对话框中设置“输入区域、输入区域、“阻尼系数阻尼系数、“输出区域选项。输出区域选项。4 4选定对话框中

51、选定对话框中“图表输出和图表输出和“规范误差复选框标志。规范误差复选框标志。分析结果：分析结果：B B列为分析之后输出的预测数据；列为分析之后输出的预测数据；C C列是分析工具列是分析工具输出的规范误差。输出的规范误差。7.7 数据分析工具库数据分析工具库4、假设检验、假设检验 假设检验是根据对事物进展抽样所得的少量样本信息，判别假设检验是根据对事物进展抽样所得的少量样本信息，判别总体分布的某个假设能否成立的一种数理统计方法。总体分布的某个假设能否成立的一种数理统计方法。假设分析工具有三种：假设分析工具有三种：t-检验、检验、z-检验、检验、F-检验。检验。运用这些检验工具可以完成均值、方差的

52、假设检验。运用这些检验工具可以完成均值、方差的假设检验。 方法方法见下例见下例7.7 数据分析工具库数据分析工具库n例：例： 见见ch7.xlst检验检验n 双样本等方差双样本等方差t-检验，以确定两个样本均值实践上检验，以确定两个样本均值实践上能否相等能否相等n 某种子公司为比较两个稻种的产量，选择了某种子公司为比较两个稻种的产量，选择了25块条件块条件类似的实验田，采用一样的耕种方法进展耕种实验，类似的实验田，采用一样的耕种方法进展耕种实验，结果播种甲稻种的结果播种甲稻种的13块田的亩产量单位：市斤分块田的亩产量单位：市斤分别是：别是：880、1 120、980、885、828、927、9

53、24、942、766、1 180、780、1 068、650；播种乙稻；播种乙稻种的种的12块实验田的亩产量分别是：块实验田的亩产量分别是：940、1 142、1 020、785、645、780、1 180、680、810、824、846、780。问这两个稻种的产量有没有明显的高低。问这两个稻种的产量有没有明显的高低之分。之分。 n阐明：要判别两稻种有无显著差别，用阐明：要判别两稻种有无显著差别，用t-检验方法，检验方法，需先计算各样本的平均值和方差，才干作进一步的检需先计算各样本的平均值和方差，才干作进一步的检验分析。验分析。7.7 数据分析工具库数据分析工具库nt-t-检验操作过程：检验操

54、作过程：n(1)(1)输入输入A A、B B两列样本数据下表中右边数据全为产生的两列样本数据下表中右边数据全为产生的分析结果分析结果7.7 数据分析工具库数据分析工具库n(2)(2)选择选择“工具菜单中工具菜单中“数据分析选项，对话框中选择数据分析选项，对话框中选择t t检验双样本等方差假设；检验双样本等方差假设；n3 3在在“双样本等方差假设分析对话框中设置双样本等方差假设分析对话框中设置t t检验的检验的各项参数各项参数n按确定按确定7.7 数据分析工具库数据分析工具库5、回归分析、回归分析回归分析主要用于分析单个因变量是如何受一个或几个自变量影回归分析主要用于分析单个因变量是如何受一个或

55、几个自变量影响的。如察看某个运发动的运动成果与一系列统计要素的关系。响的。如察看某个运发动的运动成果与一系列统计要素的关系。如年龄、体重、身高等。如年龄、体重、身高等。回归分析分为线性回归和非线性回归两种。线性回归的数学模型回归分析分为线性回归和非线性回归两种。线性回归的数学模型为：为：Excel经过对一组察看值运用经过对一组察看值运用“最小二乘法直线拟合，进展线性最小二乘法直线拟合，进展线性回归分析，该回归分析可同时处理一元回归与多元回归问题。回归分析，该回归分析可同时处理一元回归与多元回归问题。iiiyxu7.7 数据分析工具库数据分析工具库n例：用一个多元回归线性分析例子来阐明回归分析工

56、具的运用方法nch7.xls回归分析数据表中，列出了美国19561970年间历年的人均可支配收入xi和人均可消费支出yi的数据。试用图中的数据拟合模型。n模型中的趋势变量t，用于反映除人均收入之外的一切其他要素对人均消费的影响 tttutxy2107.7 数据分析工具库数据分析工具库n利用回归分析工具求解此模型的方法利用回归分析工具求解此模型的方法n输入原始数据表；输入原始数据表；n选择选择“工具菜单中工具菜单中“数据分析选项，在数据分析选项，在“数据分析对数据分析对话框中选择话框中选择“回归列表。系统弹出如下对话框；回归列表。系统弹出如下对话框；n在在“回归对话框中输入因变量回归对话框中输入

57、因变量y和自变量和自变量x的数据区域；的数据区域；n假设需求线性拟合的假设需求线性拟合的“残差图和残差图和“线性拟合图等，那么线性拟合图等，那么需选择相应复选框需选择相应复选框n此题结果见此题结果见见见ch7.xls回归分析的输出结果回归分析的输出结果综合实例综合实例1.单变量模拟运算表实例单变量模拟运算表实例超市要进展一些改革，如对某些产品采取分期付款的方式进超市要进展一些改革，如对某些产品采取分期付款的方式进展销售。假设有一液晶电视，每台售价为展销售。假设有一液晶电视，每台售价为98,000，采用，采用分期付款的方式进展销售，初步确定分期付款的方式为零分期付款的方式进展销售，初步确定分期付

58、款的方式为零首付，月分期手续费率为首付，月分期手续费率为0.7，求在不同的分期付款期，求在不同的分期付款期数月下，每期月消费者需求付款的金额数月下，每期月消费者需求付款的金额 综合实例综合实例n【实例操作步骤】【实例操作步骤】n详细操作步骤如下：详细操作步骤如下：n步骤步骤1：创建任务表，建立根本的模拟运算任务表，输入：创建任务表，建立根本的模拟运算任务表，输入必要的数据及要测试的任务表中的数据，即不同的分期付必要的数据及要测试的任务表中的数据，即不同的分期付款期数，如下图。款期数，如下图。 综合实例综合实例n步骤步骤2：创建运算公式：创建运算公式n在单元格在单元格D3中输入公式中输入公式“=

59、PMTB5,C3,B2，得出，得出当分期付款期数为当分期付款期数为C3单元格中的数值时，消费者每月的单元格中的数值时，消费者每月的付款额。如图付款额。如图7-2所示。公式中所示。公式中B5为月分期手续费率，为月分期手续费率，C3为分期付款期数，以月为单位，为分期付款期数，以月为单位，B2为付款本金。为付款本金。综合实例综合实例n步骤步骤3：建立单变量模拟运算表：建立单变量模拟运算表n首先要选定公式、数值序列和模拟运算结果所在的单元格首先要选定公式、数值序列和模拟运算结果所在的单元格区域，即区域，即C3:D12，以定义这个模拟运算表，然后选择，以定义这个模拟运算表，然后选择“数据菜单中的数据菜单

60、中的“模拟运算表命令，翻开模拟运算表命令，翻开“模拟运算表模拟运算表对话框，在对话框，在“模拟运算表对话框的模拟运算表对话框的“输入援用列的单元输入援用列的单元格文本框中输入第一个变量所在的单元格地址格文本框中输入第一个变量所在的单元格地址“$C$3，如下图。如下图。综合实例综合实例n【实例操作结果】【实例操作结果】n最终的计算结果如下图。最终的计算结果如下图。 综合实例综合实例n双变量模拟运算表实例双变量模拟运算表实例n在前面的例子中，仅仅把分期付款期数作为单变量进展模在前面的例子中，仅仅把分期付款期数作为单变量进展模拟运算，但在现实生活中，月分期付款手续费率也是经常拟运算，但在现实生活中，