Excel与数据处理-数据分析工具及应用.ppt

Excel与数据处理与数据处理本章教学目的与要求本章教学目的与要求1 1、掌握宏的加载方法、掌握宏的加载方法2 2、掌握追踪从属或引用单元格的方法、掌握追踪从属或引用单元格的方法3 3、掌握限定单元格数据的范围及圈释无效数据的、掌握限定单元格数据的范围及圈释无效数据的 应用方法应用方法4 4、掌握模拟运算表及变量求解的应用、掌握模拟运算表及变量求解的应用5 5、掌握方案的建立和应用、掌握方案的建立和应用6 6、掌握规划求解工具的应用、掌握规划求解工具的应用7 7、了解假设检验和回归分析等工具的应用、了解假设检验和回归分析等工具的应用本章重点、难点及学时数本章重点、难点及学时数n重点：重点：n掌握数据审核的方法掌握数据审核的方法n掌握模拟运算表的应用掌握模拟运算表的应用n掌握单变量求解的应用掌握单变量求解的应用n掌握方案的应用掌握方案的应用n掌握规划求解的应用掌握规划求解的应用n难点：难点：n掌握规划求解的应用掌握规划求解的应用学时数：学时数：1212学时（上机学时（上机6 6学时）学时）本章目录本章目录7.1 分析工具分析工具的安装的安装7.2 数据审核数据审核及跟踪分析及跟踪分析7.3 模拟模拟运算表运算表7.4 单变量单变量求解求解7.5 方案方案分析分析7.6 线性规划线性规划求解求解7.7 数据分析数据分析工具库工具库小结小结思考与练习思考与练习7.1 分析工具的安装分析工具的安装1、加载宏的概念加载宏的概念n加载宏是一种可选择性地安装到计算机中的软件组件，加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为用户可根据需要决定是否安装。其作用是为 Excel Excel 添加命令和函数，扩充添加命令和函数，扩充ExcelExcel的功能。的功能。nExcelExcel加载宏的扩展名是加载宏的扩展名是.xla.xla或或.xll.xll。n在默认情况下，在默认情况下，ExcelExcel将下表列出的加载宏程序安装将下表列出的加载宏程序安装在如下某一磁盘位置：在如下某一磁盘位置：“Microsoft OfficeOffice”Microsoft OfficeOffice”文件夹下的文件夹下的“Library”Library”文件夹或其子文件夹，或文件夹或其子文件夹，或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名Application DataMicrosoftAddIns”Application DataMicrosoftAddIns”文件夹下。文件夹下。网络管理员也可将加载宏程序安装到其他位置。网络管理员也可将加载宏程序安装到其他位置。7.1 分析工具的安装分析工具的安装2、ExcelExcel内置加载宏内置加载宏加加 载载 宏宏描描 述述分析工具分析工具库库添加财务、统计和工程分析工具和函数添加财务、统计和工程分析工具和函数条件求和条件求和向导向导对于数据清单中满足指定条件的数据进行求和对于数据清单中满足指定条件的数据进行求和计算计算欧元工具欧元工具将数值的格式设置为欧元的格式，并提供将数值的格式设置为欧元的格式，并提供EUROCONVERT函数以用于转换货币函数以用于转换货币查阅向导查阅向导创建一个公式，通过数据清单中的已知值查找创建一个公式，通过数据清单中的已知值查找所需数据所需数据ODBC 加加载宏载宏利用安装的利用安装的 ODBC 驱动程序，通过开放式数据驱动程序，通过开放式数据库互连（库互连（ODBC）功能与外部数据源相连）功能与外部数据源相连7.1 分析工具的安装分析工具的安装报告管理报告管理器器为工作簿创建含有不同打印区域、自定义视面为工作簿创建含有不同打印区域、自定义视面以及方案的报告以及方案的报告规划求解规划求解对基于可变单元格和条件单元格的假设分析方对基于可变单元格和条件单元格的假设分析方案进行求解计算案进行求解计算模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置模板时就可自动访问这些工具模板时就可自动访问这些工具Internet Assistant VBA通过使用通过使用 Excel 97 Internet Assistant 语法，开发语法，开发者可将者可将 Excel 数据发布到数据发布到 Web 上上7.1 分析工具的安装分析工具的安装3、安装分析工具安装分析工具n选择选择“工具工具”|“|“加载宏加载宏”菜单菜单在对话框中选择所需在对话框中选择所需工具，按确定工具，按确定 n注：若在安装注：若在安装EXCELEXCEL系统时没有安装加载宏，则必须重系统时没有安装加载宏，则必须重新启动新启动EXCELEXCEL的安装程序，选择其中的的安装程序，选择其中的“添加添加/删除删除”命令，安装命令，安装EXCELEXCEL的加载宏。的加载宏。目录目录7.2 数据审核及跟踪分析数据审核及跟踪分析1、概念概念n数据审核是一种查找单元格数据错误来源的工具，快速数据审核是一种查找单元格数据错误来源的工具，快速地找出具有引用关系的单元格，借此分析造成错误的单地找出具有引用关系的单元格，借此分析造成错误的单元格。元格。n数据审核使用追踪箭头，通过图形的方式显示或追踪单数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。元格与公式之间的关系。2、数据审核的方式数据审核的方式n追踪引用单元格追踪引用单元格 见见ch7ch7.xls.xls追踪引用单元格追踪引用单元格 操作方法：选定菜单操作方法：选定菜单“工具工具”“”“审核审核”显示显示审核审核工具栏工具栏选择要追踪引用的含公式单元格选择要追踪引用的含公式单元格“审核审核”工具栏中工具栏中“追踪引用单元格追踪引用单元格”按钮按钮再次单击再次单击“追踪引追踪引用单元格用单元格”按钮提供数据的下一级单元格按钮提供数据的下一级单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头：操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去引用单元格中追移去引用单元格中追踪箭头踪箭头”7.2 数据审核及跟踪分析数据审核及跟踪分析n追踪从属单元格追踪从属单元格见见ch7.xls追踪从属单元格追踪从属单元格n某单元格公式引用了其它单元格，则该单元格为从属单元某单元格公式引用了其它单元格，则该单元格为从属单元格。格。操作方法：选定菜单操作方法：选定菜单“工具工具”“审核审核”显示显示审核审核工具栏工具栏选择要追踪从属单元格的单元格选择要追踪从属单元格的单元格“审核审核”工具工具栏中栏中“追踪从属单元格追踪从属单元格”按钮按钮再次单击再次单击“追踪从属单元追踪从属单元格格”按钮提供从属的的单元格按钮提供从属的的单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头：操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去从属单元格中移去从属单元格中追踪箭头追踪箭头”7.2 数据审核及跟踪分析数据审核及跟踪分析3、数据有效性数据有效性数据有效性：对数据进行检验和检查的有效方法，把错误限数据有效性：对数据进行检验和检查的有效方法，把错误限制在数据输入阶段。制在数据输入阶段。n 限定数据类型和有效范围：限定数据类型和有效范围：如：限定数据大小范围、日期的范围、输入字符的个数、如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式单元格的公式7.2 数据审核及跟踪分析数据审核及跟踪分析数据限制的操作方法：选择数据限制的操作方法：选择“数据数据”“有效性有效性”在对在对话话 框中操作：框中操作：限定文本长度：限定文本长度：“设置设置”选项卡中选项卡中“允许允许”下拉列表中下拉列表中选择文本长度。选择文本长度。限定数据的有效范围：限定数据的有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择整数表中选择整数/小数小数-确定最大确定最大/小值小值设置单元格有效范围：设置单元格有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择序列表中选择序列输入序列值输入序列值设置输入提示信息：设置输入提示信息：“输入信息输入信息”选项卡中输入要显示的选项卡中输入要显示的信息信息7.2 数据审核及跟踪分析数据审核及跟踪分析n例：例：见见ch7.xls限定数据范围限定数据范围 某班要建立一个成绩登记表，为了减少成绩输入错某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。误，可对成绩表中数据的输入类型及范围进行限制。n限制学号为限制学号为8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。n限制科目列标题的取值范围，如限制科目列标题的取值范围，如“高数高数”不能输入不能输入为为“高等数学高等数学”。7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对已使用数据有效性规则可限制单元格可接收的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无效输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规则的错误单元格。数据的方法，可以显示不满足有效性规则的错误单元格。n操作方法：（选择数据区域操作方法：（选择数据区域设置数据有效性规则）设置数据有效性规则）选择选择“工具工具”菜单菜单“审核审核”选择选择“显示审核工显示审核工具栏具栏”选中有效性检测的数据区域选中有效性检测的数据区域单击单击“审核审核”工具栏的工具栏的“圈释无效数据圈释无效数据”按钮按钮 注：要先设置数据的有效范围，然后再圈释无效数据注：要先设置数据的有效范围，然后再圈释无效数据n例：例：见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成绩登记表，已经对成绩表中数据某班要建立一个成绩登记表，已经对成绩表中数据的输入类型及范围进行限制，找出其中不符合规定的输入类型及范围进行限制，找出其中不符合规定的数据。的数据。目录目录7.3 模拟运算表模拟运算表1、概念概念n模拟运算表是对工作表中一个单元格区域内的数据进模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。对运算结果的影响。2、模拟运算表的类型模拟运算表的类型n基于一个输入变量基于一个输入变量的表，用这个输入变量测试它对的表，用这个输入变量测试它对多个公式的影响；多个公式的影响；单模拟运算表单模拟运算表n基于两个输入变量的表，用这两个变量测试它们对基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响于单个公式的影响双模拟运算表双模拟运算表7.3 模拟运算表模拟运算表3、单变量模拟运算表、单变量模拟运算表n概念概念n在单变量模拟运算表中，输入数据的值被安排在一行或在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格输入单元格”。n输入单元格，就是被替换的含有输入数据的单元格输入单元格，就是被替换的含有输入数据的单元格 n操作步骤：操作步骤：1、在工作表中建立模拟运算表的结构；、在工作表中建立模拟运算表的结构；2、输入模拟运算表要用到的公式；、输入模拟运算表要用到的公式；3、选择包括公式、引用单元格和运算结果单元格区域（、选择包括公式、引用单元格和运算结果单元格区域（3部分）；部分）；4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项；5、在、在“模拟运算表模拟运算表”对话框中输入引用单用格（行或列一对话框中输入引用单用格（行或列一种）种）确定确定7.3 模拟运算表模拟运算表n例：例：见见ch7.xls单变量模拟运算表单变量模拟运算表 假设某人正考虑购买一套住房，要承担一笔假设某人正考虑购买一套住房，要承担一笔250 000250 000元的贷款，分元的贷款，分1515年还清。现想查看每月的还贷金额，年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。并想查看在不同的利率下，每月的应还贷金额。若贷款额分别为若贷款额分别为400 000400 000，550 000550 000，800 000800 000元，元，每月的应还贷金额又是多少？每月的应还贷金额又是多少？7.3 模拟运算表模拟运算表4、双变量模拟运算表、双变量模拟运算表n概念：概念：单变量模拟运算表只能解决一个输入变量对一个或多个公式单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，要查看两个变量对公式计算结果的影响，计算结果的影响，要查看两个变量对公式计算结果的影响，就要用到双变量模拟运算表。所谓双模拟变量，就是指公式就要用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两个变量。公式中两个变量所在的单元格是任取的。可中有两个变量。公式中两个变量所在的单元格是任取的。可以是工作表中任意空白单元格。以是工作表中任意空白单元格。7.3 模拟运算表模拟运算表n操作步骤：操作步骤：n1、在工作表中建立模拟运算表的结构；、在工作表中建立模拟运算表的结构；n2、在行列交叉处输入模拟运算表要用到的公式；、在行列交叉处输入模拟运算表要用到的公式；n3、选择包括公式、选择包括公式,引用单元格和运算结果单元格区引用单元格和运算结果单元格区域（域（3部分）；部分）；n4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项；n5、在、在“模拟运算表模拟运算表”对话框中输入公式中行和列引对话框中输入公式中行和列引用的单用格用的单用格确定确定n例：例：见见ch7.xls.xls双变量模拟运算表双变量模拟运算表 假设某人想贷款假设某人想贷款4545万元购买一部车，要查看在不同万元购买一部车，要查看在不同的利率和不同的偿还年限下，每个月应还的贷款金的利率和不同的偿还年限下，每个月应还的贷款金额。假设要查看贷款利率为额。假设要查看贷款利率为5%5%、5.5%5.5%、6.5%6.5%、7%7%、7.5%7.5%、8%8%，偿还期限为，偿还期限为1010年、年、1515年、年、2020年、年、3030年、年、3535年时，每月应归还的贷款金额是多少年时，每月应归还的贷款金额是多少？目录目录7.4 单变量求解单变量求解1、概念、概念所谓单变量求解，就是求解具有一个变量的方程，所谓单变量求解，就是求解具有一个变量的方程，ExcelExcel通过调整可变单元格中的数值，使之按照给定的通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的公式来满足目标单元格中的目标值目标值.2、单变量求解方法、单变量求解方法在工作表中输入原始数据；在工作表中输入原始数据；建立可变数公式；建立可变数公式；设置求解公式：菜单设置求解公式：菜单“工具工具”单变量求解单变量求解对话框对话框中输入：目标单元格、目标值、可变单元格中输入：目标单元格、目标值、可变单元格n例：例：见见ch7.xls单变量求解单变量求解 某公司想向银行贷款某公司想向银行贷款900900万元人民币，贷款利率是万元人民币，贷款利率是8.7%8.7%，贷款限期为，贷款限期为8 8年，每年应偿还多少金额？年，每年应偿还多少金额？如果公司每年可偿还如果公司每年可偿还120120万元，该公司最多可贷款多少万元，该公司最多可贷款多少金额？金额？前一问题可用前一问题可用PMTPMT函数函数,后一问题可用单变量求解。后一问题可用单变量求解。目录目录7.5 方案分析方案分析1、概念、概念n方案是已命名的一组输入值，是方案是已命名的一组输入值，是 Excel 保存在工作表中并保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。输出结果。例例:n已知某茶叶公司已知某茶叶公司2004年的总销售额及各种茶叶的销售成本，年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竞争的不断现要在此基础上制订一个五年计划。由于市场竞争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。叶的销售成本增长率低。n最好的估计是总销售额增长最好的估计是总销售额增长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)”，并将，并将其复制到其复制到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、显示方案、显示方案 选择选择“工具工具”“方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击“显示显示”按钮按钮3、建立方案报告、建立方案报告见见ch7.xls方案摘要方案摘要 选择选择“工具工具”“方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击“总结总结”按钮按钮在在“方案总结方案总结”对话框中结果类型中选择对话框中结果类型中选择“方案总结方案总结”4、建立方案透视图、建立方案透视图见见ch7.xls方案数据透视图方案数据透视图 选择选择“工具工具”“方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击“总结总结”按钮按钮在在“方案总结方案总结”对话框中结果类型中选择对话框中结果类型中选择“方案数据透视表方案数据透视表”目录目录7.6 线性规划求解线性规划求解1、概述概述 EXCELEXCEL提供提供的规划求解工具，可求解出线性与非线性两种的规划求解工具，可求解出线性与非线性两种规划求解问题，规划求解问题常用于解决产品比例、人员规划求解问题，规划求解问题常用于解决产品比例、人员调度、优化路线、调配材料等方面问题。调度、优化路线、调配材料等方面问题。2、规划求解问题的特点：、规划求解问题的特点：n问题有单一的目标，如求运输的最佳路线、求生产的问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时最低成本、求产品的最大盈利，求产品周期的最短时间等。间等。n问题有明确的不等式约束条件，例如生产材料不能超问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。过库存，生产周期不能超过一个星期等。n问题有直接或间接影响约束条件的一组输入值。问题有直接或间接影响约束条件的一组输入值。7.6 线性规划求解线性规划求解3、Excel规划求解问题的组成部分规划求解问题的组成部分（1 1）一个或一组可变单元格）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划可变单元格称为决策变量，一组决策变量代表一个规划求解的方案求解的方案（2 2）目标函数）目标函数目标函数表示规划求解要达到的最终目标，是规划求解目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数的关键。它是规划求解中可变量的函数 （3 3）约束条件）约束条件约束条件是实现目标的限制条件。约束条件是实现目标的限制条件。意义：通过规划求解，用户可为工作表的目标单元格意义：通过规划求解，用户可为工作表的目标单元格中的公式找到一个优化值，规划求解将直接或间接与中的公式找到一个优化值，规划求解将直接或间接与目标单元格公式相联系的一组单元格数值进行调整，目标单元格公式相联系的一组单元格数值进行调整，最终在目标单元格公式中求得期望的结果。最终在目标单元格公式中求得期望的结果。7.6 线性规划求解线性规划求解例：例：见见ch7.xls规划求解规划求解n某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料为底层肥料、中层肥料、上层肥料、劣质肥料4种。为种。为使问题简单，假设收集废物的劳动力是自愿的，除了使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。收集成本之外，材料成本是低廉的。n该厂目前的原材料、生产各种肥料需要的原材料比例，该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下页各表所示。各种肥料的单价等如下页各表所示。n问题：求出在现有的情况下，即利用原材料的现有库问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？利润，最大利润是多少？n分析分析:所求是在现有的原材料情况下所求是在现有的原材料情况下,应如何合理搭配应如何合理搭配,才才能获取生产产品的最大利润能获取生产产品的最大利润.7.6 线性规划求解线性规划求解表表2 生产肥料的库存原材料生产肥料的库存原材料库存库存情况情况现有现有库存库存泥土泥土4100有机有机垃圾垃圾3200矿物矿物质质3500修剪修剪物物1600表1 各肥料成品用料及其价格表表的意思是生产一个单位的肥料需要多少各种原材料多少单位产品产品泥土泥土有机垃圾有机垃圾矿物质矿物质修剪物修剪物单价单价底层肥料底层肥料55547623105.00中层肥料中层肥料6432452084.00上层肥料上层肥料43329844105.00劣质肥料劣质肥料1845231857.00表表3单位原材料成本单价单位原材料成本单价项项 目目单位成本单位成本泥土泥土0.20有机垃圾有机垃圾0.15矿物质矿物质0.10修剪物修剪物0.237.6 线性规划求解线性规划求解n建立规划求解模型步骤：建立规划求解模型步骤：规划求解第一步规划求解第一步建立求解工作表建立求解工作表(输入原始数据及相应的各公输入原始数据及相应的各公式式)7.6 线性规划求解线性规划求解规划求解第二步规划求解第二步设置求解参数设置求解参数n选择选择“工具工具”“规划求解规划求解”菜单，设置以下求解的各菜单，设置以下求解的各项参数：项参数：n设置目标单元格：输入目标函数所在单元格设置目标单元格：输入目标函数所在单元格(为总余额为总余额单元格单元格)n设置目标：最大值、最小值或值的数值设置目标：最大值、最小值或值的数值(最大利润最大利润,即最即最大值大值)n设置可变单元格：它的确定决定结果（为生产数量）设置可变单元格：它的确定决定结果（为生产数量）n设置约束条件：单击设置约束条件：单击“添加添加”按钮按钮输入约束条件输入约束条件按添加按添加依次输入所有约束条件依次输入所有约束条件确定确定7.6 线性规划求解线性规划求解n规划求解第规划求解第3步步保存求解结果保存求解结果在规划求解对话框中按在规划求解对话框中按“求解求解”在规划求解结果对在规划求解结果对话框中按话框中按“保存规划求解结果保存规划求解结果”7.6 线性规划求解线性规划求解4、修改资源、修改资源 例例1 1：见见ch7ch7.xls.xls规划求解规划求解 n肥料厂接到一个电话：只要公司肯花肥料厂接到一个电话：只要公司肯花1010元的运费就能元的运费就能得到得到150150个单位的矿物。这笔交易稍稍降低了矿物质的个单位的矿物。这笔交易稍稍降低了矿物质的平均价格，但这些矿物质值平均价格，但这些矿物质值1010元吗？元吗？n解决该问题的方法是，将库存矿物解决该问题的方法是，将库存矿物35003500改为改为36503650，用，用规划求解重新计算最大盈余。看除去￥规划求解重新计算最大盈余。看除去￥1010的成本后，的成本后，盈余是否增加盈余是否增加 n操作操作:将库存矿物将库存矿物35003500改为改为3650,3650,其它所有公式不变其它所有公式不变,再次进行再次进行求解求解,求得盈余额为求得盈余额为4483.41,4483.41,原盈余额为原盈余额为4425.89.4425.89.可知盈利为可知盈利为57.52.57.52.扣除扣除1010元成本后仍有元成本后仍有47.52.47.52.因此该因此该矿物还是要的矿物还是要的.7.6 线性规划求解线性规划求解5、修改约束条件、修改约束条件 见见ch7.xls规划求解规划求解n肥料厂接到一个电话，一个老顾客急需肥料厂接到一个电话，一个老顾客急需2525个单位的上个单位的上层肥料，公司经理在检查打印结果后，发现没有安排层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。数量为生产上层肥料。数量为0 0。决定增加约束条件，为他生。决定增加约束条件，为他生产产2525个单位的上层肥料。个单位的上层肥料。n结果可发现结果可发现:盈余额仅盈余额仅3246.51,3246.51,比原来比原来4483.414483.41少了少了1236.91236.9。显然不值得。但如该顾客为长期顾客，则短。显然不值得。但如该顾客为长期顾客，则短期内将损失一些钱，但得到了顾客的信任。期内将损失一些钱，但得到了顾客的信任。增加的增加的约束条约束条件件7.6 线性规划求解线性规划求解6、规划求解的结果报告规划求解的结果报告n运算结果报告：列出目标单元格、可变单元格及它们的初运算结果报告：列出目标单元格、可变单元格及它们的初始值、最终结果、约束条件和有关约束条件的信息。始值、最终结果、约束条件和有关约束条件的信息。见见ch7.xls运算结果报告运算结果报告7.6 线性规划求解线性规划求解n敏感性报告：敏感性报告：见见ch7.xls敏感性报告敏感性报告7.6 线性规划求解线性规划求解n极限报告：列出目标单元格、可变单元格及它们的数极限报告：列出目标单元格、可变单元格及它们的数值、上下限和目标值。下限为在满足约束条件和保持值、上下限和目标值。下限为在满足约束条件和保持其它可变单元格数值不变的情况下，某个可变单元格其它可变单元格数值不变的情况下，某个可变单元格可以取得的最小值，上限则为在这种情况下可以取到可以取得的最小值，上限则为在这种情况下可以取到的最大值。的最大值。见见ch7.xls极限值报告极限值报告7.6 线性规划求解线性规划求解7、求解精度及求解模型设置求解精度及求解模型设置 Excel采用迭代的方式进行规划求解，当求解到一定精度采用迭代的方式进行规划求解，当求解到一定精度时就结束求解，但有时要修改求解的精度、计算时间、规时就结束求解，但有时要修改求解的精度、计算时间、规划模型和迭代次数。修改上述设置的方法如下：划模型和迭代次数。修改上述设置的方法如下：在在“规划求解参数规划求解参数”对话框中设置好各项求解参数；对话框中设置好各项求解参数；单击单击“选项选项”按钮，在按钮，在“规划求解选项规划求解选项”对话框中设置对话框中设置各项求解参数。各项求解参数。7.6 线性规划求解线性规划求解例例2：求解不等式：求解不等式：见见ch7ch7.xls.xls规划求解不等式规划求解不等式 某工厂生产甲、乙两种产品，假设生产甲产品某工厂生产甲、乙两种产品，假设生产甲产品1 1吨，要消耗吨，要消耗9 9吨煤，吨煤，4 4千瓦电力，千瓦电力，3 3吨钢材，获利吨钢材，获利0.70.7万元；生产乙产品万元；生产乙产品1 1吨，吨，要消耗要消耗4 4吨煤，吨煤，5 5千瓦电力，千瓦电力，1010吨钢材，获利吨钢材，获利1.21.2万元。按计划万元。按计划国家能提供给该厂的煤为国家能提供给该厂的煤为360360吨，电力吨，电力200200千瓦，钢材千瓦，钢材300300吨，吨，问应该生产多少吨甲种产品和乙种产品，才能获得最大利润问应该生产多少吨甲种产品和乙种产品，才能获得最大利润？假设生产甲种产品假设生产甲种产品X1X1吨，生产乙种产品吨，生产乙种产品x2x2吨，吨，其最大利润是求其最大利润是求=0.7x1+1.2x2=0.7x1+1.2x2的最大值。这个问题可用数学的最大值。这个问题可用数学建模如下：建模如下：7.6 线性规划求解线性规划求解规划求解如下：规划求解如下：nB3B3和和C3C3分别用于保存甲和乙产品的生产量。分别用于保存甲和乙产品的生产量。n目标单元格为目标单元格为B8B8；可变单元格为可变单元格为$B$3:$C$3B$3:$C$3；约束条件为：约束条件为：n$B$3=0B$3=0n$C$3=0$C$3=0n$B$4=360B$4=360n$B$5=200B$5=200n$B$6=300B$6=3007.6 线性规划求解线性规划求解n例例3：见见ch7.xls线形规划求解线形规划求解n某公司在某公司在A A地有一个生产基地地有一个生产基地,其生产能力为其生产能力为400,400,随着市场随着市场需求的增长及该公司业务量的增大需求的增长及该公司业务量的增大,现有现有3 3个配送中心的需个配送中心的需求都在增长求都在增长,预计分别为预计分别为200,400,300200,400,300。公司正考虑再建立。公司正考虑再建立一个生产能力为一个生产能力为500500的工厂，准备建在的工厂，准备建在B B地。从地。从A A地的工厂地的工厂向向3 3个配送中心的单位运输成本分别为个配送中心的单位运输成本分别为5.05.0元，元，6.06.0元，元，5.45.4元，从元，从B B地的工厂向地的工厂向3 3个配送中心的单位运输成本是个配送中心的单位运输成本是7.07.0元，元，4.64.6元，元，6.66.6元。应怎样分配元。应怎样分配A A、B B两地到两地到3 3个配送中心的产个配送中心的产品量，才能使运输成本最小？品量，才能使运输成本最小？n规划模型解释如下规划模型解释如下：7.6 线性规划求解线性规划求解D5D5、E5E5、F5F5表示从表示从A A厂将厂将1 1个单位产品分别个单位产品分别送到配送中心送到配送中心1 1，2 2，3 3的费用；的费用；D7D7，E7E7，F7F7表示从表示从B B厂将厂将1 1个单位产品分别个单位产品分别送到配送中心送到配送中心1 1，2 2，3 3的费用；的费用；D6D6，E6E6，F6F6为可变单元格，保存从为可变单元格，保存从A A厂运厂运到到3 3个配送中心的最佳产品量；个配送中心的最佳产品量；D8D8，E8E8，F8F8为可变单元格，保存从为可变单元格，保存从B B厂运厂运到到3 3个配送中心的最佳产品量；个配送中心的最佳产品量；D10D10，E10E10，F10F10表示配送中心表示配送中心1 1，2 2，3 3的最的最大负荷能力；大负荷能力；H5H5，H7H7分别是分别是A A厂、厂、B B厂的生产能力厂的生产能力，H9H9是是A A，B B两厂的总生产能力两厂的总生产能力7.6 线性规划求解线性规划求解G6G6，G8G8分别为分别为A A，B B两厂各自的产品总量。两厂各自的产品总量。G6G6公式：公式：=D6+E6+F6D6+E6+F6；G8G8公式：公式：=G8+E8+F8G8+E8+F8；D9D9，E9E9，F9F9分别为两厂送到各配送中心的产品总量。分别为两厂送到各配送中心的产品总量。D9D9公式：公式：=D6+D8D6+D8；E9E9公式：公式：=E6+E8E6+E8；F9F9公式：公式：=F6+F8F6+F8；本模型的约束条件分析如下：本模型的约束条件分析如下：A A，B B两厂送到各配送中心的产品总量不能超过各配送中心的负两厂送到各配送中心的产品总量不能超过各配送中心的负荷能力：荷能力：D9D9：F9=D10F9=0F6=0，D8:F8=0D8:F8=0本模型的目标函数，求下列公式本模型的目标函数，求下列公式的最大值：的最大值：S=D5*D6+E5*E6+F5*F6+D7*D8+E7S=D5*D6+E5*E6+F5*F6+D7*D8+E7*E8+F7*F8*E8+F7*F8目录目录7.7 数据分析工具库数据分析工具库1、概述、概述nExcelExcel提供了一组数据分析工具，称为分析工具库。其提供了一组数据分析工具，称为分析工具库。其中提供的分析工具在工程分析、数理统计、经济计量中提供的分析工具在工程分析、数理统计、经济计量分析等学科中有较强的实用价值。分析等学科中有较强的实用价值。n分析工具库由分析工具库由ExcelExcel自带的加载宏提供。自带的加载宏提供。如果启动如果启动ExcelExcel后，在后，在ExcelExcel的的“工具工具”菜单中没有菜单中没有“数据分析数据分析”菜单项，就需启动菜单项，就需启动“工具工具”中的中的“加载宏加载宏”菜单项，将菜单项，将“分析工具库分析工具库”加载到加载到ExcelExcel系统中。系统中。n如果加载宏对话框中没有分析工具库，则单击加载宏如果加载宏对话框中没有分析工具库，则单击加载宏对话框中对话框中“浏览浏览”按钮，定位到分析工具库加载宏文按钮，定位到分析工具库加载宏文件件“Analy32.dll”Analy32.dll”所在的驱动器和文件夹，通常位于所在的驱动器和文件夹，通常位于“Microsoft OfficeOfficeLibraryAnalysis”Microsoft OfficeOfficeLibraryAnalysis”中中,否否则需运行则需运行OfficeOffice系统的安装程序。系统的安装程序。nExcelExcel的的“分析工具库分析工具库”加载宏提供的一些统计函数、加载宏提供的一些统计函数、财务函数和工程函数。这些函数只有在安装了财务函数和工程函数。这些函数只有在安装了“分析分析工具库工具库”后才能使用后才能使用 。7.7 数据分析工具库数据分析工具库2、ExcelExcel分析工具库中的工具分析工具库中的工具分析工具分析工具名称名称说说 明明方差分析方差分析 包括包括3种类型的分析，它们是单因素方差分析、可种类型的分析，它们是单因素方差分析、可重复双因素分析、无重复双因素分析重复双因素分析、无重复双因素分析相关系数相关系数分析分析用于判断两组数据集（可以使用不同的度量单位）用于判断两组数据集（可以使用不同的度量单位）之间的关系。之间的关系。协方差分协方差分析析用于返回各数据点的一对均值偏差之间的乘积的用于返回各数据点的一对均值偏差之间的乘积的平均值。平均值。描述统计描述统计分析分析用于生成对输入区域中数据的单变值分析，提供用于生成对输入区域中数据的单变值分析，提供有关数据趋中性和易变性的信息有关数据趋中性和易变性的信息指数平滑指数平滑分析分析基于前期预测值导出相应的新预测值，并修正前基于前期预测值导出相应的新预测值，并修正前期预测值的误差。期预测值的误差。7.7 数据分析工具库数据分析工具库傅里叶分傅里叶分析析解决线性系统问题，并