Excel与数据处理第七章数据分析工具及应用Excel与数据处理第七章数据分析工具及应用本章教学目的与要求本章教学目的与要求1 1、掌握宏的加载方法、掌握宏的加载方法2 2、掌握追踪从属或引用单元格的方法、掌握追踪从属或引用单元格的方法3 3、掌握限定单元格数据的范围及圈释无效数据的、掌握限定单元格数据的范围及圈释无效数据的 应用方法应用方法4 4、掌握模拟运算表及变量求解的应用、掌握模拟运算表及变量求解的应用5 5、掌握方案的建立和应用、掌握方案的建立和应用6 6、掌握规划求解工具的应用、掌握规划求解工具的应用7 7、了解假设检验和回归分析等工具的应用、了解假设检验和回归分析等工具的应用本章教学目的与要求1、掌握宏的加载方法本章重点、难点及学时数本章重点、难点及学时数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 分析工具的安装7.1 分析工具的安装分析工具的安装1、加载宏的概念加载宏的概念n加载宏是一种可选择性地安装到计算机中的软件组件，加载宏是一种可选择性地安装到计算机中的软件组件，用户可根据需要决定是否安装。其作用是为用户可根据需要决定是否安装。其作用是为 Excel Excel 添加命令和函数，扩充添加命令和函数，扩充ExcelExcel的功能。的功能。nExcelExcel加载宏的扩展名是加载宏的扩展名是.xla.xla或或.xll.xll。n在默认情况下，在默认情况下，ExcelExcel将下表列出的加载宏程序安装将下表列出的加载宏程序安装在如下某一磁盘位置：在如下某一磁盘位置：“Microsoft Microsoft OfficeOffice”OfficeOffice”文件夹下的文件夹下的“Library”Library”文件夹或其文件夹或其子文件夹，或子文件夹，或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名Application DataMicrosoftAddIns”Application DataMicrosoftAddIns”文件文件夹下。网络管理员也可将加载宏程序安装到其他位置。夹下。网络管理员也可将加载宏程序安装到其他位置。 7.1 分析工具的安装1、加载宏的概念7.1 分析工具的安装分析工具的安装2、ExcelExcel内置加载宏内置加载宏加加 载 宏宏描描 述述分析工具分析工具库添加添加财务、统计和工程分析工具和函数和工程分析工具和函数条件求和条件求和向向导对于数据清于数据清单中中满足指定条件的数据足指定条件的数据进行求和行求和计算算欧元工具欧元工具将数将数值的格式的格式设置置为欧元的格式，并提供欧元的格式，并提供EUROCONVERT函数以用于函数以用于转换货币查阅向向导创建一个公式，通建一个公式，通过数据清数据清单中的已知中的已知值查找找所需数据所需数据ODBC 加加载宏宏利用安装的利用安装的 ODBC 驱动程序，通程序，通过开放式数据开放式数据库互互连（ODBC）功能与外部数据源相）功能与外部数据源相连7.1 分析工具的安装2、Excel内置加载宏加 载 7.1 分析工具的安装分析工具的安装报告管理告管理器器为工作簿工作簿创建含有不同打印区域、自定建含有不同打印区域、自定义视面面以及方案的以及方案的报告告规划求解划求解对基于可基于可变单元格和条件元格和条件单元格的假元格的假设分析方分析方案案进行求解行求解计算算模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置模板模板时就可自就可自动访问这些工具些工具Internet Assistant VBA通通过使用使用 Excel 97 Internet Assistant 语法，开法，开发者可将者可将 Excel 数据数据发布到布到 Web 上上7.1 分析工具的安装报告管理器为工作簿创建含有不同打印区7.1 分析工具的安装分析工具的安装3、安装分析工具安装分析工具n选择选择“工具工具”|“|“加载宏加载宏”菜单菜单在对话框中选择所需在对话框中选择所需工具，按确定工具，按确定 n注：若在安装注：若在安装EXCELEXCEL系统时没有安装加载宏，则必须重系统时没有安装加载宏，则必须重新启动新启动EXCELEXCEL的安装程序，选择其中的的安装程序，选择其中的“添加添加/ /删除删除”命令，安装命令，安装EXCELEXCEL的加载宏。的加载宏。目录目录7.1 分析工具的安装3、安装分析工具目录7.2 数据审核及跟踪分析数据审核及跟踪分析1、概念概念n数据审核是一种查找单元格数据错误来源的工具，快速数据审核是一种查找单元格数据错误来源的工具，快速地找出具有引用关系的单元格，借此分析造成错误的单地找出具有引用关系的单元格，借此分析造成错误的单元格。元格。n数据审核使用追踪箭头，通过图形的方式显示或追踪单数据审核使用追踪箭头，通过图形的方式显示或追踪单元格与公式之间的关系。元格与公式之间的关系。 2、数据审核的方式数据审核的方式n追踪引用单元格追踪引用单元格 见见ch7ch7.xls.xls追踪引用单元格追踪引用单元格 操作方法：选定菜单操作方法：选定菜单“工具工具”“”“审核审核” ” 显示显示审核审核工具栏工具栏选择要追踪引用的含公式单元格选择要追踪引用的含公式单元格“审核审核”工具栏中工具栏中“追踪引用单元格追踪引用单元格”按钮按钮再次单击再次单击“追踪引追踪引用单元格用单元格”按钮提供数据的下一级单元格按钮提供数据的下一级单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去引用单元格中追移去引用单元格中追踪箭头踪箭头”7.2 数据审核及跟踪分析1、概念7.2 数据审核及跟踪分析数据审核及跟踪分析n追踪从属单元格追踪从属单元格见见ch7.xls追踪从属单元格追踪从属单元格n某单元格公式引用了其它单元格，则该单元格为从属单元某单元格公式引用了其它单元格，则该单元格为从属单元格。格。 操作方法：选定菜单操作方法：选定菜单“工具工具”“审核审核” 显示显示审核审核工具栏工具栏选择要追踪从属单元格的单元格选择要追踪从属单元格的单元格“审核审核”工具工具栏中栏中“追踪从属单元格追踪从属单元格”按钮按钮再次单击再次单击“追踪从属单元追踪从属单元格格”按钮提供从属的的单元格按钮提供从属的的单元格n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去从属单元格中移去从属单元格中追踪箭头追踪箭头”7.2 数据审核及跟踪分析7.2 数据审核及跟踪分析数据审核及跟踪分析3、 数据有效性数据有效性数据有效性：对数据进行检验和检查的有效方法，把错误限数据有效性：对数据进行检验和检查的有效方法，把错误限制在数据输入阶段。制在数据输入阶段。n 限定数据类型和有效范围：限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、如：限定数据大小范围、日期的范围、输入字符的个数、单元格的公式单元格的公式7.2 数据审核及跟踪分析3、 数据有效性7.2 数据审核及跟踪分析数据审核及跟踪分析数据限制的操作方法：选择数据限制的操作方法：选择“数据数据” “有效性有效性”在对在对话话 框中操作：框中操作：限定文本长度：限定文本长度： “设置设置”选项卡中选项卡中“允许允许” 下拉列表中下拉列表中选择文本长度。选择文本长度。限定数据的有效范围：限定数据的有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择整数表中选择整数/小数小数- 确定最大确定最大/小值小值设置单元格有效范围：设置单元格有效范围：“设置设置”选项卡中选项卡中“允许允许”下拉列下拉列表中选择序列表中选择序列输入序列值输入序列值设置输入提示信息：设置输入提示信息： “输入信息输入信息”选项卡中输入要显示的选项卡中输入要显示的信息信息7.2 数据审核及跟踪分析数据限制的操作方法：选择“数据”7.2 数据审核及跟踪分析数据审核及跟踪分析n例：例：见见ch7.xls限定数据范围限定数据范围 某班要建立一个成绩登记表，为了减少成绩输入错某班要建立一个成绩登记表，为了减少成绩输入错误，可对成绩表中数据的输入类型及范围进行限制。误，可对成绩表中数据的输入类型及范围进行限制。n限制学号为限制学号为8位字符，不能小于位字符，不能小于8位，也不能多于位，也不能多于8位。位。n限制所有学科成绩为限制所有学科成绩为0100之间的整数。之间的整数。n限制科目列标题的取值范围，如限制科目列标题的取值范围，如“高数高数”不能输入不能输入为为“高等数学高等数学”。7.2 数据审核及跟踪分析例：见ch7.xls限定数据7.2 数据审核及跟踪分析数据审核及跟踪分析4、圈释无效数据、圈释无效数据 使用数据有效性规则可限制单元格可接收的数据，但对已使用数据有效性规则可限制单元格可接收的数据，但对已输入数据的区域，不能显示出有误的数据。采用圈释无效输入数据的区域，不能显示出有误的数据。采用圈释无效数据的方法，可以显示不满足有效性规则的错误单元格。数据的方法，可以显示不满足有效性规则的错误单元格。n操作方法：（选择数据区域操作方法：（选择数据区域设置数据有效性规则）设置数据有效性规则）选择选择“工具工具”菜单菜单“审核审核”选择选择“显示审核工显示审核工具栏具栏”选中有效性检测的数据区域选中有效性检测的数据区域单击单击“审核审核”工具栏的工具栏的“圈释无效数据圈释无效数据”按钮按钮 注：要先设置数据的有效范围，然后再圈释无效数据注：要先设置数据的有效范围，然后再圈释无效数据n例：例：见见ch7.xls圈释无效数据圈释无效数据 某班要建立一个成绩登记表，已经对成绩表中数据某班要建立一个成绩登记表，已经对成绩表中数据的输入类型及范围进行限制，找出其中不符合规定的输入类型及范围进行限制，找出其中不符合规定的数据。的数据。目录目录7.2 数据审核及跟踪分析4、圈释无效数据目录7.3 模拟运算表模拟运算表1、概念概念n模拟运算表是对工作表中一个单元格区域内的数据进模拟运算表是对工作表中一个单元格区域内的数据进行模拟运算，测试使用一个或两个变量的公式中变量行模拟运算，测试使用一个或两个变量的公式中变量对运算结果的影响。对运算结果的影响。2、模拟运算表的类型模拟运算表的类型n基于一个输入变量基于一个输入变量的表，用这个输入变量测试它对的表，用这个输入变量测试它对多个公式的影响；多个公式的影响；单模拟运算表单模拟运算表n基于两个输入变量的表，用这两个变量测试它们对基于两个输入变量的表，用这两个变量测试它们对于单个公式的影响于单个公式的影响双模拟运算表双模拟运算表7.3 模拟运算表1、概念7.3 模拟运算表模拟运算表3、单变量模拟运算表、单变量模拟运算表n概念概念n在单变量模拟运算表中，输入数据的值被安排在一行或在单变量模拟运算表中，输入数据的值被安排在一行或一列中。同时，单变量模拟表中使用的公式必须引用一列中。同时，单变量模拟表中使用的公式必须引用“输入单元格输入单元格”。n输入单元格，就是被替换的含有输入数据的单元格输入单元格，就是被替换的含有输入数据的单元格 n操作步骤：操作步骤：1、在工作表中建立模拟运算表的结构；、在工作表中建立模拟运算表的结构；2、输入模拟运算表要用到的公式；、输入模拟运算表要用到的公式；3、选择包括公式、引用单元格和运算结果单元格区域（、选择包括公式、引用单元格和运算结果单元格区域（3部分）；部分）；4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项；5、在、在“模拟运算表模拟运算表”对话框中输入引用单用格（行或列一对话框中输入引用单用格（行或列一种）种） 确定确定7.3 模拟运算表3、单变量模拟运算表7.3 模拟运算表模拟运算表n例：例：见见ch7.xls单变量模拟运算表单变量模拟运算表 假设某人正考虑购买一套住房，要承担一笔假设某人正考虑购买一套住房，要承担一笔250 000250 000元的贷款，分元的贷款，分1515年还清。现想查看每月的还贷金额，年还清。现想查看每月的还贷金额，并想查看在不同的利率下，每月的应还贷金额。并想查看在不同的利率下，每月的应还贷金额。 若贷款额分别为若贷款额分别为400 000400 000，550 000550 000，800 000800 000元，元，每月的应还贷金额又是多少？每月的应还贷金额又是多少？ 7.3 模拟运算表例：见ch7.xls单变量模拟运算表7.3 模拟运算表模拟运算表4、双变量模拟运算表、双变量模拟运算表n概念：概念： 单变量模拟运算表只能解决一个输入变量对一个或多个公式单变量模拟运算表只能解决一个输入变量对一个或多个公式计算结果的影响，要查看两个变量对公式计算结果的影响，计算结果的影响，要查看两个变量对公式计算结果的影响，就要用到双变量模拟运算表。所谓双模拟变量，就是指公式就要用到双变量模拟运算表。所谓双模拟变量，就是指公式中有两个变量。公式中两个变量所在的单元格是任取的。可中有两个变量。公式中两个变量所在的单元格是任取的。可以是工作表中任意空白单元格。以是工作表中任意空白单元格。7.3 模拟运算表4、双变量模拟运算表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.3 模拟运算表操作步骤：目录7.4 单变量求解单变量求解1、概念、概念n所谓单变量求解，就是求解具有一个变量的方程，所谓单变量求解，就是求解具有一个变量的方程，ExcelExcel通过调整可变单元格中的数值，使之按照给定的通过调整可变单元格中的数值，使之按照给定的公式来满足目标单元格中的公式来满足目标单元格中的目标值目标值.2、单变量求解方法、单变量求解方法n在工作表中输入原始数据；在工作表中输入原始数据；n建立可变数公式；建立可变数公式；n设置求解公式：菜单设置求解公式：菜单“工具工具”单变量求解单变量求解对话框对话框中输入：目标单元格、目标值、可变单元格中输入：目标单元格、目标值、可变单元格n例：例： 见见ch7.xls单变量求解单变量求解 某公司想向银行贷款某公司想向银行贷款900900万元人民币，贷款利率是万元人民币，贷款利率是8.7%8.7%，贷款限期为，贷款限期为8 8年，每年应偿还多少金额？年，每年应偿还多少金额？ 如果公司每年可偿还如果公司每年可偿还120120万元，该公司最多可贷款多少万元，该公司最多可贷款多少金额？金额？ 前一问题可用前一问题可用PMTPMT函数函数, , 后一问题可用单变量求解。后一问题可用单变量求解。目录目录7.4 单变量求解1、概念目录7.5 方案分析方案分析1、概念、概念n方案是已命名的一组输入值，是方案是已命名的一组输入值，是 Excel 保存在工作表中并保存在工作表中并可用来自动替换某个计算模型的输入值，用来预测模型的可用来自动替换某个计算模型的输入值，用来预测模型的输出结果。输出结果。 例例:n已知某茶叶公司已知某茶叶公司2004年的总销售额及各种茶叶的销售成本，年的总销售额及各种茶叶的销售成本，现要在此基础上制订一个五年计划。由于市场竞争的不断现要在此基础上制订一个五年计划。由于市场竞争的不断变化，所以只能对总销售额及各种茶叶销售成本的增长率变化，所以只能对总销售额及各种茶叶销售成本的增长率做一些估计。最好的方案当然是总销售额增长率高，各茶做一些估计。最好的方案当然是总销售额增长率高，各茶叶的销售成本增长率低。叶的销售成本增长率低。 n最好的估计是总销售额增长最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、，花茶、绿茶、乌龙茶、红茶的销售成本分别增长红茶的销售成本分别增长10%、6%、10%、7%。 见见ch7.xls方案方案7.5 方案分析1、概念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 方案分析建立方案解决工作表建立方法如下，输入下表A7.5 方案分析方案分析输入方案变量值如下图所示：输入方案变量值如下图所示：7.5 方案分析输入方案变量值如下图所示：7.5 方案分析方案分析2、显示方案、显示方案 选择选择“工具工具” “方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击 “显示显示”按钮按钮3、建立方案报告、建立方案报告见见ch7.xls方案摘要方案摘要 选择选择“工具工具” “方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击 “总结总结”按钮按钮在在“方案总结方案总结”对话框中结果类型中选择对话框中结果类型中选择“方案总结方案总结”4、建立方案透视图、建立方案透视图见见ch7.xls方案数据透视图方案数据透视图 选择选择“工具工具” “方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话框中的某一方案框中的某一方案单击单击 “总结总结”按钮按钮在在“方案总结方案总结”对话框中结果类型中选择对话框中结果类型中选择“方案数据透视表方案数据透视表”目录目录7.5 方案分析2、显示方案目录7.6 线性规划求解线性规划求解1、概述概述 EXCELEXCEL提供提供的规划求解工具，可求解出线性与非线性两种的规划求解工具，可求解出线性与非线性两种规划求解问题，规划求解问题常用于解决产品比例、人员规划求解问题，规划求解问题常用于解决产品比例、人员调度、优化路线、调配材料等方面问题。调度、优化路线、调配材料等方面问题。2、规划求解问题的特点：、规划求解问题的特点：n问题有单一的目标，如求运输的最佳路线、求生产的问题有单一的目标，如求运输的最佳路线、求生产的最低成本、求产品的最大盈利，求产品周期的最短时最低成本、求产品的最大盈利，求产品周期的最短时间等。间等。n问题有明确的不等式约束条件，例如生产材料不能超问题有明确的不等式约束条件，例如生产材料不能超过库存，生产周期不能超过一个星期等。过库存，生产周期不能超过一个星期等。n问题有直接或间接影响约束条件的一组输入值。问题有直接或间接影响约束条件的一组输入值。 7.6 线性规划求解1、概述7.6 线性规划求解线性规划求解3、Excel规划求解问题的组成部分规划求解问题的组成部分 （1 1）一个或一组可变单元格）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划可变单元格称为决策变量，一组决策变量代表一个规划求解的方案求解的方案（2 2）目标函数）目标函数目标函数表示规划求解要达到的最终目标，是规划求解目标函数表示规划求解要达到的最终目标，是规划求解的关键。它是规划求解中可变量的函数的关键。它是规划求解中可变量的函数 （3 3）约束条件）约束条件约束条件是实现目标的限制条件。约束条件是实现目标的限制条件。 意义：通过规划求解，用户可为工作表的目标单元格意义：通过规划求解，用户可为工作表的目标单元格中的公式找到一个优化值，规划求解将直接或间接与中的公式找到一个优化值，规划求解将直接或间接与目标单元格公式相联系的一组单元格数值进行调整，目标单元格公式相联系的一组单元格数值进行调整，最终在目标单元格公式中求得期望的结果。最终在目标单元格公式中求得期望的结果。7.6 线性规划求解3、Excel规划求解问题的组成部分 7.6 线性规划求解线性规划求解例：例： 见见ch7.xls规划求解规划求解n某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢某肥料厂专门收集有机物垃圾，如青草、树枝、凋谢的花朵等。该厂利用这些废物，并掺进不同比例的泥的花朵等。该厂利用这些废物，并掺进不同比例的泥土和矿物质来生产高质量的植物肥料，生产的肥料分土和矿物质来生产高质量的植物肥料，生产的肥料分为底层肥料、中层肥料、上层肥料、劣质肥料为底层肥料、中层肥料、上层肥料、劣质肥料4种。为种。为使问题简单，假设收集废物的劳动力是自愿的，除了使问题简单，假设收集废物的劳动力是自愿的，除了收集成本之外，材料成本是低廉的。收集成本之外，材料成本是低廉的。n该厂目前的原材料、生产各种肥料需要的原材料比例，该厂目前的原材料、生产各种肥料需要的原材料比例，各种肥料的单价等如下页各表所示。各种肥料的单价等如下页各表所示。n问题：求出在现有的情况下，即利用原材料的现有库问题：求出在现有的情况下，即利用原材料的现有库存，应生产各种类型的肥料各多少数量才能获得最大存，应生产各种类型的肥料各多少数量才能获得最大利润，最大利润是多少？利润，最大利润是多少？ n分析分析:所求是在现有的原材料情况下所求是在现有的原材料情况下,应如何合理搭配应如何合理搭配,才能获取生产产品的最大利润才能获取生产产品的最大利润.7.6 线性规划求解例： 见ch7.xls规划求解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 线性规划求解表2 生产肥料的库存原材料库存情况现有7.6 线性规划求解线性规划求解n建立规划求解模型步骤：建立规划求解模型步骤：规划求解第一步规划求解第一步建立求解工作表建立求解工作表(输入原始数据及相应的各输入原始数据及相应的各公式公式)7.6 线性规划求解建立规划求解模型步骤：7.6 线性规划求解线性规划求解规划求解第二步规划求解第二步设置求解参数设置求解参数n选择选择“工具工具” “规划求解规划求解”菜单，设置以下求解的各菜单，设置以下求解的各项参数：项参数：n设置目标单元格：输入目标函数所在单元格设置目标单元格：输入目标函数所在单元格(为总余额为总余额单元格单元格)n设置目标：最大值、最小值或值的数值设置目标：最大值、最小值或值的数值(最大利润最大利润,即即最大值最大值)n设置可变单元格：它的确定决定结果（为生产数量）设置可变单元格：它的确定决定结果（为生产数量）n设置约束条件：单击设置约束条件：单击“添加添加”按钮按钮输入约束条件输入约束条件按添加按添加依次输入所有约束条件依次输入所有约束条件确定确定7.6 线性规划求解规划求解第二步设置求解参数7.6 线性规划求解线性规划求解n规划求解第规划求解第3步步保存求解结果保存求解结果在规划求解对话框中按在规划求解对话框中按“求解求解”在规划求解结果对在规划求解结果对话框中按话框中按“保存规划求解结果保存规划求解结果”7.6 线性规划求解规划求解第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 线性规划求解4、修改资源 7.6 线性规划求解线性规划求解5、修改约束条件、修改约束条件 见见ch7.xls规划求解规划求解n肥料厂接到一个电话，一个老顾客急需肥料厂接到一个电话，一个老顾客急需2525个单位的上个单位的上层肥料，公司经理在检查打印结果后，发现没有安排层肥料，公司经理在检查打印结果后，发现没有安排生产上层肥料。数量为生产上层肥料。数量为0 0。决定增加约束条件，为他生。决定增加约束条件，为他生产产2525个单位的上层肥料。个单位的上层肥料。n结果可发现结果可发现: :盈余额仅盈余额仅3246.51,3246.51,比原来比原来4483.414483.41少了少了1236.91236.9。显然不值得。但如该顾客为长期顾客，则短。显然不值得。但如该顾客为长期顾客，则短期内将损失一些钱，但得到了顾客的信任。期内将损失一些钱，但得到了顾客的信任。增加的增加的约束条约束条件件7.6 线性规划求解5、修改约束条件 见ch7.xls7.6 线性规划求解线性规划求解6、 规划求解的结果报告规划求解的结果报告n运算结果报告：列出目标单元格、可变单元格及它们的初运算结果报告：列出目标单元格、可变单元格及它们的初始值、最终结果、约束条件和有关约束条件的信息。始值、最终结果、约束条件和有关约束条件的信息。 见见ch7.xls运算结果报告运算结果报告7.6 线性规划求解6、 规划求解的结果报告7.6 线性规划求解线性规划求解n敏感性报告：敏感性报告： 见见ch7.xls敏感性报告敏感性报告7.6 线性规划求解敏感性报告： 见ch7.xls敏感7.6 线性规划求解线性规划求解n极限报告：列出目标单元格、可变单元格及它们的数极限报告：列出目标单元格、可变单元格及它们的数值、上下限和目标值。下限为在满足约束条件和保持值、上下限和目标值。下限为在满足约束条件和保持其它可变单元格数值不变的情况下，某个可变单元格其它可变单元格数值不变的情况下，某个可变单元格可以取得的最小值，上限则为在这种情况下可以取到可以取得的最小值，上限则为在这种情况下可以取到的最大值。的最大值。 见见ch7.xls极限值报告极限值报告7.6 线性规划求解极限报告：列出目标单元格、可变单元格及7.6 线性规划求解线性规划求解7、求解精度及求解模型设置求解精度及求解模型设置 Excel采用迭代的方式进行规划求解，当求解到一定精度采用迭代的方式进行规划求解，当求解到一定精度时就结束求解，但有时要修改求解的精度、计算时间、规时就结束求解，但有时要修改求解的精度、计算时间、规划模型和迭代次数。修改上述设置的方法如下：划模型和迭代次数。修改上述设置的方法如下： 在在“规划求解参数规划求解参数”对话框中设置好各项求解参数；对话框中设置好各项求解参数； 单击单击“选项选项”按钮，在按钮，在“规划求解选项规划求解选项”对话框中设置对话框中设置各项求解参数。各项求解参数。 7.6 线性规划求解7、求解精度及求解模型设置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 线性规划求解例2：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 线性规划求解规划求解如下：7.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 线性规划求解例3： 见ch7.xls线形规划求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 线性规划求解D5、E5、F5表示从A厂将1个单位产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.6 线性规划求解G6，G8分别为A，B两厂各自的产品总7.7 数据分析工具库数据分析工具库1、概述、概述nExcelExcel提供了一组数据分析工具，称为分析工具库。其提供了一组数据分析工具，称为分析工具库。其中提供的分析工具在工程分析、数理统计、经济计量中提供的分析工具在工程分析、数理统计、经济计量分析等学科中有较强的实用价值。分析等学科中有较强的实用价值。n分析工具库由分析工具库由ExcelExcel自带的加载宏提供。自带的加载宏提供。 如果启动如果启动ExcelExcel后，在后，在ExcelExcel的的“工具工具”菜单中没有菜单中没有“数据分析数据分析”菜单项，就需启动菜单项，就需启动“工具工具”中的中的“加载宏加载宏”菜单项，将菜单项，将“分析工具库分析工具库”加载到加载到ExcelExcel系统中。系统中。n如果加载宏对话框中没有分析工具库，则单击加载宏如果加载宏对话框中没有分析工具库，则单击加载宏对话框中对话框中“浏览浏览”按钮，定位到分析工具库加载宏文按钮，定位到分析工具库加载宏文件件“Analy32.dll”Analy32.dll”所在的驱动器和文件夹，通常位于所在的驱动器和文件夹，通常位于“Microsoft OfficeOfficeLibraryAnalysis”Microsoft OfficeOfficeLibraryAnalysis”中中, ,否则需运行否则需运行OfficeOffice系统的安装程序。系统的安装程序。nExcelExcel的的“分析工具库分析工具库”加载宏提供的一些统计函数、加载宏提供的一些统计函数、财务函数和工程函数。这些函数只有在安装了财务函数和工程函数。这些函数只有在安装了“分析分析工具库工具库”后才能使用后才能使用 。7.7 数据分析工具库1、概述7.7 数据分析工具库数据分析工具库2、ExcelExcel分析工具库中的工具分析工具库中的工具分析工具分析工具名称名称说 明明方差分析方差分析 包括包括3种种类型的分析，它型的分析，它们是是单因素方差分析、可因素方差分析、可重复双因素分析、无重复双因素分析重复双因素分析、无重复双因素分析相关系数相关系数分析分析用于判断两用于判断两组数据集（可以使用不同的度量数据集（可以使用不同的度量单位）位）之之间的关系。的关系。协方差分方差分析析用于返回各数据点的一用于返回各数据点的一对均均值偏差之偏差之间的乘的乘积的的平均平均值。描述描述统计分析分析用于生成用于生成对输入区域中数据的入区域中数据的单变值分析，提供分析，提供有关数据有关数据趋中性和易中性和易变性的信息性的信息指数平滑指数平滑分析分析基于前期基于前期预测值导出相出相应的新的新预测值，并修正前，并修正前期期预测值的的误差。差。7.7 数据分析工具库2、Excel分析工具库中的工具分析工7.7 数据分析工具库数据分析工具库傅里叶分傅里叶分析析解决解决线性系性系统问题，并能通，并能通过快速傅里叶快速傅里叶变换（FFT）分析周期性的数据。）分析周期性的数据。F-检验用来比用来比较两个两个样本本总体的方差体的方差直方直方图分分析析在在给定工作表中数据定工作表中数据单元格区域和接收区元格区域和接收区间的情的情况下，况下，计算数据的个算数据的个别和累和累计频率率移移动平均平均分析分析基于特定的基于特定的过去某段去某段时期中期中变量的均量的均值，对未来未来值进行行预测t-检验分分析析提供提供3种不同种不同检验：双：双样本等方差假本等方差假设t-检验，双，双样本异方差假本异方差假设t-检验，平均，平均值的成的成对双双样本本t-检验回回归分析分析通通过对一一组观察察值使用使用“最小二乘法最小二乘法”直直线拟合，合，进行行线形回形回归分析。分析。抽抽样分析分析以以输入区域入区域为总体构造体构造总体的一个体的一个样本本z-检验双双样本平均差本平均差检验7.7 数据分析工具库傅里叶分析解决线性系统问题，并能通过快7.7 数据分析数据分析工具库工具库3、 统计分析统计分析 nExcelExcel的分析工具库提供了的分析工具库提供了3 3种统计观测分析工具：指种统计观测分析工具：指数平滑分析、移动平均分析和回归分析数平滑分析、移动平均分析和回归分析 n三种工具用法相同，下面以指数平滑分析为例。三种工具用法相同，下面以指数平滑分析为例。 见见ch7ch7.xls .xls 指数平滑分析指数平滑分析 （1 1）在工作表的一列上输入各时间点上的观察值，如下）在工作表的一列上输入各时间点上的观察值，如下图图A A列所示。列所示。 7.7 数据分析工具库3、 统计分析 7.7 数据分析工具库数据分析工具库（2 2）选择）选择“工具工具”菜单菜单“数据分析数据分析” ” 选项，对话框中选择选项，对话框中选择“指数平滑指数平滑”按确定。按确定。 （3 3）在）在“指数平滑指数平滑”对话框中设置对话框中设置“输入区域输入区域”、“阻尼系阻尼系数数”、“输出区域输出区域”选项。选项。（4 4）选定对话框中）选定对话框中“图表输出图表输出”和和“标准误差标准误差”复选框标志。复选框标志。分析结果：分析结果：B B列为分析之后输出的预测数据；列为分析之后输出的预测数据；C C列是分析工具列是分析工具输出的标准误差。输出的标准误差。7.7 数据分析工具库（2）选择“工具”菜单“数据分析” 选7.7 数据分析工具库数据分析工具库4、假设检验、假设检验 n假设检验是根据对事物进行抽样所得的少量样本信息，假设检验是根据对事物进行抽样所得的少量样本信息，判断总体分布的某个假设是否成立的一种数理统计方判断总体分布的某个假设是否成立的一种数理统计方法。法。n假设分析工具有三种：假设分析工具有三种：t-检验、检验、z-检验、检验、F-检验。检验。n运用这些检验工具可以完成均值、方差的假设检验。运用这些检验工具可以完成均值、方差的假设检验。 方法见下例方法见下例7.7 数据分析工具库4、假设检验 7.7 数据分析工具库数据分析工具库n例：例： 见见ch7.xlst检验检验 （双样本等方差（双样本等方差t-t-检验，以确定两个样本均值实际上检验，以确定两个样本均值实际上是否相等）是否相等） 某种子公司为比较两个稻种的产量，选择了某种子公司为比较两个稻种的产量，选择了2525块条块条件相似的试验田，采用相同的耕种方法进行耕种试件相似的试验田，采用相同的耕种方法进行耕种试验，结果播种甲稻种的验，结果播种甲稻种的1313块田的亩产量（单位：市块田的亩产量（单位：市斤）分别是：斤）分别是：880880、1 1201 120、980980、885885、828828、927927、924924、942942、766766、1 1801 180、780780、1 0681 068、650650；播种乙；播种乙稻种的稻种的1212块试验田的亩产量分别是：块试验田的亩产量分别是：940940、1 1421 142、1 1 020020、785785、645645、780780、1 1801 180、680680、810810、824824、846846、780780。问这两个稻种的产量有没有明显的高低之分。问这两个稻种的产量有没有明显的高低之分。 说明：要判断两稻种有无显著差别，用说明：要判断两稻种有无显著差别，用t-t-检验方法，需检验方法，需先计算各样本的平均值和方差，才能作进一步的检验先计算各样本的平均值和方差，才能作进一步的检验分析。分析。7.7 数据分析工具库例： 见ch7.xlst检验7.7 数据分析工具库数据分析工具库nt-t-检验操作过程：检验操作过程：n(1)(1)输入输入A A、B B两列样本数据（下表中右边数据全为产生的两列样本数据（下表中右边数据全为产生的分析结果）分析结果）7.7 数据分析工具库t-检验操作过程：7.7 数据分析工具库数据分析工具库n(2)(2)选择选择“工具工具”菜单中菜单中“数据分析数据分析”选项，对话框中选选项，对话框中选择择t t检验双样本等方差假设；检验双样本等方差假设；n（3 3）在）在“双样本等方差假设分析双样本等方差假设分析”对话框中设置对话框中设置t t检验的检验的各项参数各项参数n按确定按确定7.7 数据分析工具库(2)选择“工具”菜单中“数据分析”选7.7 数据分析工具库数据分析工具库5、回归分析、回归分析n回归分析主要用于分析单个因变量是如何受一个或几个自回归分析主要用于分析单个因变量是如何受一个或几个自变量影响的。如观察某个运动员的运动成绩与一系列统计变量影响的。如观察某个运动员的运动成绩与一系列统计因素的关系。如年龄、体重、身高等。因素的关系。如年龄、体重、身高等。n回归分析分为线性回归和非线性回归两种。线性回归的数回归分析分为线性回归和非线性回归两种。线性回归的数学模型为：学模型为：nExcelExcel通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直线拟合，进直线拟合，进行线性回归分析，该回归分析可同时解决一元回归与多元行线性回归分析，该回归分析可同时解决一元回归与多元回归问题。回归问题。7.7 数据分析工具库5、回归分析7.7 数据分析工具库数据分析工具库n例：（用一个多元回归线性分析例子来说明回归分例：（用一个多元回归线性分析例子来说明回归分析工具的使用方法析工具的使用方法）nch7.xlsch7.xls回归分析数据表中，列出了美国回归分析数据表中，列出了美国1956197019561970年间历年的人均可支配收入年间历年的人均可支配收入x xi i和人均和人均可消费支出可消费支出y yi i的数据。试用图中的数据拟合模型。的数据。试用图中的数据拟合模型。n模型中的趋势变量模型中的趋势变量t t，用于反映除人均收入之外，用于反映除人均收入之外的所有其他因素对人均消费的影响的所有其他因素对人均消费的影响 7.7 数据分析工具库例：（用一个多元回归线性分析例子来说明7.7 数据分析工具库数据分析工具库n利用回归分析工具求解此模型的方法利用回归分析工具求解此模型的方法n输入原始数据表；输入原始数据表；n选择选择“工具工具”菜单中菜单中“数据分析数据分析”选项，在选项，在“数据分数据分析析”对话框中选择对话框中选择“回归回归”列表。系统弹出如下对话列表。系统弹出如下对话框；框；n在在“回归回归”对话框中输入因变量对话框中输入因变量y和自变量和自变量x的数据区的数据区域；域；n若需要线性拟合的若需要线性拟合的“残差图残差图”和和“线性拟合图线性拟合图”等，等，则需选择相应复选框则需选择相应复选框n本题结果见本题结果见见见ch7.xls回归分析的输出结果回归分析的输出结果目录目录7.7 数据分析工具库利用回归分析工具求解此模型的方法目录综合实例综合实例1.单变量模拟运算表实例单变量模拟运算表实例n超市要进行一些改革，如对某些产品采取分期付款的方式超市要进行一些改革，如对某些产品采取分期付款的方式进行销售。假设有一液晶电视，每台售价为进行销售。假设有一液晶电视，每台售价为98,000，采，采用分期付款的方式进行销售，初步确定分期付款的方式为用分期付款的方式进行销售，初步确定分期付款的方式为零首付，月分期手续费率为零首付，月分期手续费率为0.7，求在不同的分期付款，求在不同的分期付款期数（月）下，每期（月）消费者需要付款的金额期数（月）下，每期（月）消费者需要付款的金额 综合实例1.单变量模拟运算表实例综合实例综合实例n【实例操作步骤实例操作步骤】n具体操作步骤如下：具体操作步骤如下：n步骤步骤1：创建工作表，建立基本的模拟运算工作表，输入：创建工作表，建立基本的模拟运算工作表，输入必要的数据及要测试的工作表中的数据，即不同的分期付必要的数据及要测试的工作表中的数据，即不同的分期付款期数，如图所示。款期数，如图所示。 综合实例【实例操作步骤】综合实例综合实例n步骤步骤2：创建运算公式：创建运算公式n在单元格在单元格D3中输入公式中输入公式“=PMT（B5,C3,B2）”，得出，得出当分期付款期数为当分期付款期数为C3单元格中的数值时，消费者每月的单元格中的数值时，消费者每月的付款额。如图付款额。如图7-2所示。公式中所示。公式中B5为月分期手续费率，为月分期手续费率，C3为分期付款期数，以月为单位，为分期付款期数，以月为单位，B2为付款本金。为付款本金。综合实例步骤2：创建运算公式综合实例综合实例n步骤步骤3：建立单变量模拟运算表：建立单变量模拟运算表n首先要选定公式、数值序列和模拟运算结果所在的单元格首先要选定公式、数值序列和模拟运算结果所在的单元格区域，即区域，即C3:D12，以定义这个模拟运算表，然后选择，以定义这个模拟运算表，然后选择“数据数据”菜单中的菜单中的“模拟运算表模拟运算表”命令，打开命令，打开“模拟运算表模拟运算表”对话框，在对话框，在“模拟运算表模拟运算表”对话框的对话框的“输入引用列的单输入引用列的单元格元格”文本框中输入第一个变量所在的单元格地址文本框中输入第一个变量所在的单元格地址“$C$3”，如图所示。，如图所示。综合实例步骤3：建立单变量模拟运算表综合实例综合实例n【实例操作结果实例操作结果】n最终的计算结果如图所示。最终的计算结果如图所示。 综合实例【实例操作结果】综合实例综合实例n双变量模拟运算表实例双变量模拟运算表实例n在前面的例子中，仅仅把分期付款期数作为单变量进行模在前面的例子中，仅仅把分期付款期数作为单变量进行模拟运算，但在现实生活中，月分期付款手续费率也是经常拟运算，但在现实生活中，月分期付款手续费率也是经常要发生变化的。假设想查看在不同的分期付款期数和不同要发生变化的。假设想查看在不同的分期付款期数和不同的月分期付款手续费率下消费者每月付款金额的变化情况，的月分期付款手续费率下消费者每月付款金额的变化情况，就必须建立双变量模拟运算表了。就必须建立双变量模拟运算表了。综合实例双变量模拟运算表实例综合实例综合实例n【实例操作步骤实例操作步骤】n步骤步骤1：创建工作表：创建工作表n建立基本的运算工作表，输入必要的数据及要测试的工作建立基本的运算工作表，输入必要的数据及要测试的工作表中的数据，即不同的月分期付款手续费率和分期付款期表中的数据，即不同的月分期付款手续费率和分期付款期数，其中，月分期付款手续费率属于行变量，分期付款期数，其中，月分期付款手续费率属于行变量，分期付款期数属于列变量。如图所示。数属于列变量。如图所示。综合实例【实例操作步骤】综合实例综合实例n步骤步骤2：创建运算公式：创建运算公式n双变量模拟运算中首先要在行、列交叉处所在的单元格输双变量模拟运算中首先要在行、列交叉处所在的单元格输入运算公式。在本例中，在分期付款期数和月分期付款手入运算公式。在本例中，在分期付款期数和月分期付款手续费率的交叉处，即续费率的交叉处，即D3单元格中输入公式单元格中输入公式“=PMT(B5,B7,B2)”，运算结果如图所示。，运算结果如图所示。 综合实例步骤2：创建运算公式综合实例综合实例n步骤步骤3：建立双变量模拟运算表：建立双变量模拟运算表n首先要选定公式、数值序列和模拟运算结果所在的单元格首先要选定公式、数值序列和模拟运算结果所在的单元格区域，即区域，即D3:J13，以定义这个模拟运算表，然后选择，以定义这个模拟运算表，然后选择“数据数据”菜单中的菜单中的“模拟运算表模拟运算表”命令，打开命令，打开“模拟运算表模拟运算表”对话框，在对话框，在“输入引用行的单元格输入引用行的单元格”中输入月分期手续中输入月分期手续费率所在的单元格费率所在的单元格“$B$5”，在，在“输入引用列的单元格输入引用列的单元格”中输入分期付款期数所在的单元格中输入分期付款期数所在的单元格“$B$7”，如图所示。，如图所示。最后单击最后单击“模拟运算表模拟运算表”对话框的对话框的“确定确定”按钮。按钮。 综合实例步骤3：建立双变量模拟运算表综合实例综合实例n【实例操作结果实例操作结果】n最终的计算结果如图所示。最终的计算结果如图所示。综合实例【实例操作结果】综合实例综合实例n利用单变量求解计算付款期数案例利用单变量求解计算付款期数案例n在上面模拟运算的例子中，主要是求出消费者在固定的分在上面模拟运算的例子中，主要是求出消费者在固定的分期手续费率和分期付款期数下每月应付的金额。假设现在期手续费率和分期付款期数下每月应付的金额。假设现在有一消费者可负担的每月的付款金额为￥有一消费者可负担的每月的付款金额为￥5000，想知道，想知道在固定的月分期手续费率下，该消费者可承受的付款期数在固定的月分期手续费率下，该消费者可承受的付款期数是多少？是多少？综合实例利用单变量求解计算付款期数案例综合实例综合实例n【案例操作步骤案例操作步骤】n步骤步骤1：在单变量求解工作表中输入原始数据。如图所示：在单变量求解工作表中输入原始数据。如图所示综合实例【案例操作步骤】综合实例综合实例n步骤步骤2：在月付款金额对应的单元格：在月付款金额对应的单元格B4中输入公式中输入公式“=PMT(B3,B5,B2)”，如图所示。，如图所示。综合实例步骤2：在月付款金额对应的单元格B4中输入公式“=P综合实例综合实例n步骤步骤3：选择：选择“工具工具”菜单的菜单的“单变量求解单变量求解”命令，打开命令，打开“单变量求解单变量求解”对话框，在对话框，在“目标单元格目标单元格”中输入中输入“$B$4”，在，在“目标值目标值”中输入中输入“-5000”，表示经过求，表示经过求解之后，单元格解之后，单元格B4的值应是的值应是-5000，目标单元格必须包，目标单元格必须包含公式，在含公式，在“可变单元格可变单元格”中输入中输入“$B$5”，即最后分期，即最后分期付款期数所在的单元格，如左图所示。接着在单击付款期数所在的单元格，如左图所示。接着在单击“确定确定”按钮后，屏幕上可能出现出错提示，表示无法进行求解，按钮后，屏幕上可能出现出错提示，表示无法进行求解，如右图所示。如右图所示。综合实例步骤3：选择“工具”菜单的“单变量求解”命令，打开“综合实例综合实例n在这里可以先给可变单元格设置一个值，比如在这里可以先给可变单元格设置一个值，比如5，因为其，因为其中的数值也要根据目标单元格中的结果而发生变化，所以中的数值也要根据目标单元格中的结果而发生变化，所以事先给的值并不会影响到最终的计算结果。具体设置如左事先给的值并不会影响到最终的计算结果。具体设置如左图所示。图所示。n步骤步骤4：再打开：再打开“单变量求解单变量求解”对话框，按上面同样的设对话框，按上面同样的设置，可得到置，可得到“单变量求解状态单变量求解状态”对话框，表明求得一个解，对话框，表明求得一个解，如右图所示。如右图所示。 综合实例在这里可以先给可变单元格设置一个值，比如5，因为其中综合实例综合实例n【案例操作结果案例操作结果】n求解结果如图所示。求解结果如图所示。综合实例【案例操作结果】综合实例综合实例n超市收入预测方案实例超市收入预测方案实例n假设已知超市在假设已知超市在2007年的各部门的收入情况，现在想对年的各部门的收入情况，现在想对2008年的收入情况做一个估计，分析在不同增长方案下年的收入情况做一个估计，分析在不同增长方案下的收入情况，这些增长方案包括经营情况一般、经营情况的收入情况，这些增长方案包括经营情况一般、经营情况良好和经营情况较差三种方案。良好和经营情况较差三种方案。综合实例超市收入预测方案实例综合实例综合实例n【案例操作步骤案例操作步骤】n具体操作步骤如下：具体操作步骤如下：n步骤步骤1：制作如图所示的工作：制作如图所示的工作表，包括表，包括2007年度的收入情年度的收入情况，以及况，以及2008年度根据经营年度根据经营情况（一般）设置的收入增长情况（一般）设置的收入增长率。其中在单元格率。其中在单元格D3输入公式输入公式“=C3-B3”，然后将其复制到，然后将其复制到D4、D5、D6；在单元格；在单元格D11中输入公式中输入公式“=C3*(1+C11)-B3*(1+B11)”，并将其复制，并将其复制到到D12、D13、D14；在单元；在单元格格D15中输入公式中输入公式“=SUM(D11:D14)”。从图中可。从图中可以看出，在经营情况一般这种以看出，在经营情况一般这种方案下，方案下，2008年企业的总利年企业的总利润为润为5052715。 综合实例【案例操作步骤】综合实例综合实例n步骤步骤2：选择：选择“工具工具”菜单的菜单的“方案方案”命令，弹出命令，弹出“方案方案管理器管理器”对话框，如图所示。对话框，如图所示。综合实例步骤2：选择“工具”菜单的“方案”命令，弹出“方案管综合实例综合实例n步骤步骤3：在：在“方案管理器方案管理器”中单击中单击“添加添加”按钮，弹出按钮，弹出“编辑方案编辑方案”对话框，在对话框，在“方案名方案名”中输入方案名称中输入方案名称“经营经营情况一般情况一般”；在；在“可变单元格可变单元格”中输入需要更改的单元格中输入需要更改的单元格的引用的引用“$B$11:$C$14”，选中，选中“防止更改防止更改”复选项。如复选项。如图所示。图所示。综合实例步骤3：在“方案管理器”中单击“添加”按钮，弹出“编综合实例综合实例n步骤步骤4：在上图所示的对话框中单击：在上图所示的对话框中单击“确定确定”按钮，将打按钮，将打开开“方案变量值方案变量值”对话框。在对话框。在“方案变量值方案变量值”对话框中分对话框中分别输入可变单元格所对应的单元格，如图所示。别输入可变单元格所对应的单元格，如图所示。 综合实例步骤4：在上图所示的对话框中单击“确定”按钮，将打开综合实例综合实例n步骤步骤5：在上图所示的对话框中单击：在上图所示的对话框中单击“确定确定”按钮，则该按钮，则该方案创建完成，返回方案创建完成，返回“方案管理器方案管理器”对话框，将显示出所对话框，将显示出所定义方案的名称。如图所示。定义方案的名称。如图所示。综合实例步骤5：在上图所示的对话框中单击“确定”按钮，则该方综合实例综合实例n步骤步骤6：在：在“方案管理器方案管理器”对话框中单击对话框中单击“添加添加”按钮，按钮，使用类似的步骤再创建两个方案。使用类似的步骤再创建两个方案。n【实例操作结果实例操作结果】n最终结果如图所示最终结果如图所示 综合实例步骤6：在“方案管理器”对话框中单击“添加”按钮，使综合实例综合实例n利用规划求解制定销售决策实例利用规划求解制定销售决策实例n假设超市某一部门要销售两种商品，其中假设超市某一部门要销售两种商品，其中A商品的采购价商品的采购价为为900元，销售价为元，销售价为2400元，元，B商品的采购价位商品的采购价位800元，元，销售价为销售价为1800元，而且两种商品的物流和仓储成本不同，元，而且两种商品的物流和仓储成本不同，A商品的物流成本和仓储成本分别为商品的物流成本和仓储成本分别为30元和元和19.5元，元，B商商品物流成本和仓储成本分别为品物流成本和仓储成本分别为25元和元和6.5元，现在的问题元，现在的问题是在每月的物流和仓储费用固定的情况下（物流和仓储总是在每月的物流和仓储费用固定的情况下（物流和仓储总费用分别为费用分别为2800元和元和1500元）。该如何分配元）。该如何分配A、B两种两种商品的进货数量，才能得到最大的销售利润？商品的进货数量，才能得到最大的销售利润？综合实例利用规划求解制定销售决策实例综合实例综合实例n【实例操作步骤实例操作步骤】n具体操作步骤如下：具体操作步骤如下：n步骤步骤 1：建立商品数据表，如图所示。：建立商品数据表，如图所示。综合实例【实例操作步骤】综合实例综合实例n步骤步骤 2：输入公式，在：输入公式，在B12单元格中输入公式单元格中输入公式“=(B6-B5-B4-B3)*B7+(C6-C5-C4-C3)*C7”，在单元格，在单元格B13和和B14中分别输入中分别输入“=B4*B7+C4*C7”和和“=B5*B7+C5*C7”。如图所示。如图所示。 综合实例步骤 2：输入公式，在B12单元格中输入公式“=(B综合实例综合实例n步骤步骤 3：建立好上面表格后，就可以利用规划求解工具对：建立好上面表格后，就可以利用规划求解工具对表格的问题进行求解了。选择表格的问题进行求解了。选择“工具工具”菜单中的菜单中的“规划求规划求解解”命令，弹出命令，弹出“规划求解参数规划求解参数”对话框，如图所示。在对话框，如图所示。在“设置目标单元格设置目标单元格”文本框中输入文本框中输入B12单元格，即要求的单元格，即要求的销售最大利润。在销售最大利润。在“等于等于”单选项中选择单选项中选择“最大值最大值”，表，表示所要求的目标函数的最大值。在示所要求的目标函数的最大值。在“可变单元格可变单元格”文本框文本框中输入中输入B7:C7，就是目标函数的两个变量，也就是两种商，就是目标函数的两个变量，也就是两种商品各自的进货量。这里的引用均为绝对引用。品各自的进货量。这里的引用均为绝对引用。 综合实例步骤 3：建立好上面表格后，就可以利用规划求解工具对综合实例综合实例n步骤步骤4：设置规划求解约束条件。选择：设置规划求解约束条件。选择“规划求解参数规划求解参数”对话框中的对话框中的“添加添加”按钮，弹出按钮，弹出“添加约束添加约束”对话框，在对话框，在对话框中添加限制条件。对于物流总成本的限制，应该是对话框中添加限制条件。对于物流总成本的限制，应该是物流总成本小于每月的物流费用控制，即物流总成本小于每月的物流费用控制，即B13B9。添加。添加完该约束后，单击对话框中的完该约束后，单击对话框中的“添加添加”按钮，接着添加其按钮，接着添加其它的约束条件。最终添加完约束条件的结果如图所示。它的约束条件。最终添加完约束条件的结果如图所示。综合实例步骤4：设置规划求解约束条件。选择“规划求解参数”对综合实例综合实例n步骤步骤5：进行规划求解。选择：进行规划求解。选择“规划求解参数规划求解参数”对话框中对话框中的的“求解求解”按钮，弹出按钮，弹出“规划求解结果规划求解结果”对话框。接着单对话框。接着单击击“确定确定”按钮，就可以看到规划求解的求解结果，如图按钮，就可以看到规划求解的求解结果，如图所示所示 。综合实例步骤5：进行规划求解。选择“规划求解参数”对话框中的综合实例综合实例n利用数据分析工具进行销售预测实例利用数据分析工具进行销售预测实例n超市有关部门要作如下的销售预测。根据超市近超市有关部门要作如下的销售预测。根据超市近7年来的年来的销售收入数据（如图所示），预测下一年（第销售收入数据（如图所示），预测下一年（第8年）的销年）的销售量。售量。 综合实例利用数据分析工具进行销售预测实例综合实例综合实例n【实例操作步骤实例操作步骤】n步骤步骤1：选择：选择“工具工具”菜单中的菜单中的“数据分析数据分析”命令，弹出命令，弹出“数据分析数据分析”对话框，从对话框，从“分析工具列表分析工具列表”中选择中选择“回归回归”选项，然后单击选项，然后单击“确定确定”按钮，打开按钮，打开“回归回归”对话框，对话框，如图所示。如图所示。 综合实例【实例操作步骤】综合实例综合实例n 步骤步骤2：在：在“Y值输入框值输入框”中输入中输入“$B$2:$B$8”，在，在“X值输入框值输入框”中输入中输入“$A$2:$A$8”，在，在“输出选项输出选项”中选中选“输出区域输出区域”，并填入，并填入“$D$1”，然后根据实际需要，勾，然后根据实际需要，勾选其他需要的选项，如图所示。单击选其他需要的选项，如图所示。单击“确定确定”按钮，回归按钮，回归分析的摘要就输出在本工作表上。分析的摘要就输出在本工作表上。 综合实例 步骤2：在“Y值输入框”中输入“$B$2:$B$8综合实例综合实例n【实例操作结果实例操作结果】n最终求解结果如图所示。最终求解结果如图所示。 综合实例【实例操作结果】综合实例综合实例n利用数据分析工具进行数据统计实例利用数据分析工具进行数据统计实例n超市有关部门要作如下的数据统计。根据当月的员工加班超市有关部门要作如下的数据统计。根据当月的员工加班情况（如图所示），统计出各加班段的人数。情况（如图所示），统计出各加班段的人数。综合实例利用数据分析工具进行数据统计实例综合实例综合实例n【实例操作步骤实例操作步骤】n对于实例所提出的问题，可以通过直方图分析工具来解决。对于实例所提出的问题，可以通过直方图分析工具来解决。具体操作步骤如下：具体操作步骤如下：n步骤步骤1：建立如图所示的统计表：建立如图所示的统计表综合实例【实例操作步骤】综合实例综合实例n步骤步骤2：选择：选择“工具工具”菜单中的菜单中的“数据分析数据分析”命令，弹出命令，弹出“数据分析数据分析”对话框，从对话框，从“分析工具列表分析工具列表”中选择中选择“直方直方图图”选项，然后单击选项，然后单击“确定确定”按钮，打开按钮，打开“直方图直方图”对话对话框，如图所示。框，如图所示。综合实例步骤2：选择“工具”菜单中的“数据分析”命令，弹出“综合实例综合实例n步骤步骤3：在：在“输入区域输入区域”中输入中输入“$C$2:$C$49”，在，在“接接受区域受区域”中输入中输入“$E$2:$E$7”，在，在“输出选项输出选项”中选中选“输出区域输出区域”，并填入，并填入“$E$11”，然后根据实际需要，勾，然后根据实际需要，勾选其他需要的选项，如图所示。最后单击选其他需要的选项，如图所示。最后单击“确定确定”按钮，按钮，系统将生成频率数和直方图。系统将生成频率数和直方图。综合实例步骤3：在“输入区域”中输入“$C$2:$C$49”综合实例综合实例n【实例操作结果实例操作结果】n最终求解结果如图所示。最终求解结果如图所示。综合实例【实例操作结果】本章小结本章小结 ExcelExcel的分析工具库中提供了大量非常实用的的分析工具库中提供了大量非常实用的分析工具。分析工具。数据审核和跟踪分析数据审核和跟踪分析可以查找单元格可以查找单元格或公式中的数据来源，并可由此分析产生错误的或公式中的数据来源，并可由此分析产生错误的根源。根源。数据的有效性检验数据的有效性检验工具可以把单元格中的工具可以把单元格中的数据输入或数据值限制在一个有效的范围内，以数据输入或数据值限制在一个有效的范围内，以减少错误数据的产生。要分析两个变量之间的因减少错误数据的产生。要分析两个变量之间的因果关系，果关系，模拟运算和单变量求解模拟运算和单变量求解是一个较佳的工是一个较佳的工具。具。线形规划线形规划工具可用于求解人员分配、生产计工具可用于求解人员分配、生产计划、公路运输及生产、投资等的最佳方案。划、公路运输及生产、投资等的最佳方案。统计统计分析、假设检验及回归分析在数理统计分析、假设检验及回归分析在数理统计、经济分、经济分析等领域有着较强的应用价值，它们为许多复杂析等领域有着较强的应用价值，它们为许多复杂问题的解决提供了一些有用的模型和解决方案。问题的解决提供了一些有用的模型和解决方案。目录目录本章小结 Excel的分析工具库中提供了大量非常本章思考与练习本章思考与练习1 1、分析工具如何安装？、分析工具如何安装？2 2、追踪引用单元格和追踪从属单元格有什么区别？、追踪引用单元格和追踪从属单元格有什么区别？3 3、什么前提下适合用模拟运算表解决问题？、什么前提下适合用模拟运算表解决问题？4 4、什么前提下适合用变量求解解决问题？、什么前提下适合用变量求解解决问题？5 5、什么前提下适合用方案分析解决问题？、什么前提下适合用方案分析解决问题？6 6、什么前提下适合用规划求解解决问题？规划求解、什么前提下适合用规划求解解决问题？规划求解的数学模型如何创建？的数学模型如何创建？7 7、了解数据分析工具库中的其他分析工具。、了解数据分析工具库中的其他分析工具。8 8、某人欲贷款、某人欲贷款100100万元买一幢别墅，此人每月有还万元买一幢别墅，此人每月有还1 1万元的能力，基于年利率万元的能力，基于年利率7%7%每月末还款至少多久每月末还款至少多久能还清？思考用函数和用单变量求解两种方法。能还清？思考用函数和用单变量求解两种方法。目录目录本章思考与练习1、分析工具如何安装？目录感谢聆听