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

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

2、 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 数据分析工具库数据分析工具库

3、小结小结 思考与练习思考与练习 7.1 分析工具的安装分析工具的安装 1、加载宏的概念加载宏的概念 n加载宏是一种可选择性地安装到计算机中的软件组加载宏是一种可选择性地安装到计算机中的软件组 件，用户可根据需要决定是否安装。其作用是为件，用户可根据需要决定是否安装。其作用是为 Excel Excel 添加命令和函数，扩充添加命令和函数，扩充ExcelExcel的功能。的功能。 nExcelExcel加载宏的扩展名是加载宏的扩展名是.xla.xla或或.xll.xll。 n在默认情况下，在默认情况下，ExcelExcel将下表列出的加载宏程序安装将下表列出的加载宏程序安装 在如下某一磁盘位置：在

4、如下某一磁盘位置：“Microsoft OfficeOffice”Microsoft OfficeOffice” 文件夹下的文件夹下的“Library”Library”文件夹或其子文件夹，或文件夹或其子文件夹，或 Windows Windows 所在文件夹下的所在文件夹下的“ProfilesProfiles用户名用户名 Application DataMicrosoftAddIns”Application DataMicrosoftAddIns”文件夹下。文件夹下。 网络管理员也可将加载宏程序安装到其他位置。网络管理员也可将加载宏程序安装到其他位置。 7.1 分析工具的安装分析工具的安装 2、

5、ExcelExcel内置加载宏内置加载宏 加加 载载 宏宏描描 述述 分析工具分析工具 库库 添加财务、统计和工程分析工具和函数添加财务、统计和工程分析工具和函数 条件求和条件求和 向导向导 对于数据清单中满足指定条件的数据进行求和对于数据清单中满足指定条件的数据进行求和 计算计算 欧元工具欧元工具将数值的格式设置为欧元的格式，并提供将数值的格式设置为欧元的格式，并提供 EUROCONVERT函数以用于转换货币函数以用于转换货币 查阅向导查阅向导创建一个公式，通过数据清单中的已知值查找创建一个公式，通过数据清单中的已知值查找 所需数据所需数据 ODBC 加加 载宏载宏 利用安装的利用安装的 O

6、DBC 驱动程序，通过开放式数据驱动程序，通过开放式数据 库互连（库互连（ODBC）功能与外部数据源相连）功能与外部数据源相连 7.1 分析工具的安装分析工具的安装 报告管理报告管理 器器 为工作簿创建含有不同打印区域、自定义视面为工作簿创建含有不同打印区域、自定义视面 以及方案的报告以及方案的报告 规划求解规划求解对基于可变单元格和条件单元格的假设分析方对基于可变单元格和条件单元格的假设分析方 案进行求解计算案进行求解计算 模板工具模板工具提供提供 Excel 的内置模板所使用的工具。使用内置的内置模板所使用的工具。使用内置 模板时就可自动访问这些工具模板时就可自动访问这些工具 Intern

7、et Assistant VBA 通过使用通过使用 Excel 97 Internet Assistant 语法，开语法，开 发者可将发者可将 Excel 数据发布到数据发布到 Web 上上 7.1 分析工具的安装分析工具的安装 3、安装分析工具安装分析工具 n选择选择“工具工具”|“|“加载宏加载宏”菜单菜单在对话框中选择所需在对话框中选择所需 工具，按确定工具，按确定 n注：若在安装注：若在安装EXCELEXCEL系统时没有安装加载宏，则必须重系统时没有安装加载宏，则必须重 新启动新启动EXCELEXCEL的安装程序，选择其中的的安装程序，选择其中的“添加添加/ /删除删除” 命令，安装命

8、令，安装EXCELEXCEL的加载宏。的加载宏。 7.2 数据审核及跟踪分析数据审核及跟踪分析 1、概念概念 n数据审核是一种查找单元格数据错误来源的工具，快速数据审核是一种查找单元格数据错误来源的工具，快速 地找出具有引用关系的单元格，借此分析造成错误的单地找出具有引用关系的单元格，借此分析造成错误的单 元格。元格。 n数据审核使用追踪箭头，通过图形的方式显示或追踪单数据审核使用追踪箭头，通过图形的方式显示或追踪单 元格与公式之间的关系。元格与公式之间的关系。 2、数据审核的方式数据审核的方式 n追踪引用单元格追踪引用单元格 见见ch7ch7.xls.xls追踪引用单元格追踪引用单元格 操作

9、方法：选定菜单操作方法：选定菜单“工具工具”“审核审核” 显示显示审核审核 工具栏工具栏选择要追踪引用的含公式单元格选择要追踪引用的含公式单元格“审核审核”工工 具栏中具栏中“追踪引用单元格追踪引用单元格”按钮按钮再次单击再次单击“追踪引用追踪引用 单元格单元格”按钮提供数据的下一级单元格按钮提供数据的下一级单元格 n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去引用单元格中移去引用单元格中 追踪箭头追踪箭头” 7.2 数据审核及跟踪分析数据审核及跟踪分析 n追踪从属单元格追踪从属单元格见见ch7.xls追踪从属单元格追踪从属单

10、元格 n某单元格公式引用了其它单元格，则该单元格为从属单元某单元格公式引用了其它单元格，则该单元格为从属单元 格。格。 操作方法：选定菜单操作方法：选定菜单“工具工具”“审核审核” 显示显示审核审核 工具栏工具栏选择要追踪从属单元格的单元格选择要追踪从属单元格的单元格“审核审核”工具工具 栏中栏中“追踪从属单元格追踪从属单元格”按钮按钮再次单击再次单击“追踪从属单元追踪从属单元 格格”按钮提供从属的的单元格按钮提供从属的的单元格 n移去引用单元格追踪箭头：移去引用单元格追踪箭头： 操作方法：选择操作方法：选择“审核审核”工具栏中工具栏中“移去从属单元格中移去从属单元格中 追踪箭头追踪箭头” 7

11、.2 数据审核及跟踪分析数据审核及跟踪分析 3、 数据有效性数据有效性 数据有效性：对数据进行检验和检查的有效方法，把错误数据有效性：对数据进行检验和检查的有效方法，把错误 限制在数据输入阶段。限制在数据输入阶段。 n 限定数据类型和有效范围：限定数据类型和有效范围： 如：限定数据大小范围、日期的范围、输入字符的个数、如：限定数据大小范围、日期的范围、输入字符的个数、 单元格的公式单元格的公式 7.2 数据审核及跟踪分析数据审核及跟踪分析 数据限制的操作方法：选择数据限制的操作方法：选择“数据数据” “有效性有效性”在对在对 话话 框中操作：框中操作： 限定文本长度：限定文本长度： “设置设置

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

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

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

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

16、型 n基于一个输入变量基于一个输入变量的表，用这个输入变量测试它对的表，用这个输入变量测试它对 多个公式的影响；多个公式的影响；单模拟运算表单模拟运算表 n基于两个输入变量的表，用这两个变量测试它们对基于两个输入变量的表，用这两个变量测试它们对 于单个公式的影响于单个公式的影响双模拟运算表双模拟运算表 7.3 模拟运算表模拟运算表 3、单变量模拟运算表、单变量模拟运算表 n概念概念 n在单变量模拟运算表中，输入数据的值被安排在一行或在单变量模拟运算表中，输入数据的值被安排在一行或 一列中。同时，单变量模拟表中使用的公式必须引用一列中。同时，单变量模拟表中使用的公式必须引用 “输入单元格输入单元

17、格”。 n输入单元格，就是被替换的含有输入数据的单元格输入单元格，就是被替换的含有输入数据的单元格 n操作步骤：操作步骤： 1、在工作表中建立模拟运算表的结构；、在工作表中建立模拟运算表的结构； 2、输入模拟运算表要用到的公式；、输入模拟运算表要用到的公式； 3、选择包括公式、引用单元格和运算结果单元格区域（、选择包括公式、引用单元格和运算结果单元格区域（3 部分）；部分）； 4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项； 5、在、在“模拟运算表模拟运算表”对话框中输入引用单用格（行或列一对话框中输入引用单用格（行或列一 种）种） 确定确定 7.3 模拟运算表模拟运算表

18、 n例：例：见见ch7.xls单变量模拟运算表单变量模拟运算表 假设某人正考虑购买一套住房，要承担一笔假设某人正考虑购买一套住房，要承担一笔250 000250 000 元的贷款，分元的贷款，分1515年还清。现想查看每月的还贷金额，年还清。现想查看每月的还贷金额， 并想查看在不同的利率下，每月的应还贷金额。并想查看在不同的利率下，每月的应还贷金额。 若贷款额分别为若贷款额分别为400 000400 000，550 000550 000，800 000800 000元，元， 每月的应还贷金额又是多少？每月的应还贷金额又是多少？ 7.3 模拟运算表模拟运算表 4、双变量模拟运算表、双变量模拟运算

19、表 n概念：概念： 单变量模拟运算表只能解决一个输入变量对一个或多个公式单变量模拟运算表只能解决一个输入变量对一个或多个公式 计算结果的影响，要查看两个变量对公式计算结果的影响，计算结果的影响，要查看两个变量对公式计算结果的影响， 就要用到双变量模拟运算表。所谓双模拟变量，就是指公式就要用到双变量模拟运算表。所谓双模拟变量，就是指公式 中有两个变量。公式中两个变量所在的单元格是任取的。可中有两个变量。公式中两个变量所在的单元格是任取的。可 以是工作表中任意空白单元格。以是工作表中任意空白单元格。 7.3 模拟运算表模拟运算表 n操作步骤：操作步骤： n1、在工作表中建立模拟运算表的结构；、在工

20、作表中建立模拟运算表的结构； n2、在行列交叉处输入模拟运算表要用到的公式；、在行列交叉处输入模拟运算表要用到的公式； n3、选择包括公式、选择包括公式,引用单元格和运算结果单元格区引用单元格和运算结果单元格区 域（域（3部分）；部分）； n4、选择、选择“数据数据”菜单菜单“模拟运算表模拟运算表”选项；选项； n5、在、在“模拟运算表模拟运算表”对话框中输入公式中行和列引对话框中输入公式中行和列引 用的单用格用的单用格确定确定 n例：例： 见见ch7.xls.xls双变量模拟运算表双变量模拟运算表 假设某人想贷款假设某人想贷款4545万元购买一部车，要查看在不同万元购买一部车，要查看在不同

21、的利率和不同的偿还年限下，每个月应还的贷款金的利率和不同的偿还年限下，每个月应还的贷款金 额。假设要查看贷款利率为额。假设要查看贷款利率为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通过调整可变单元格中的数值，使之按照给定的通过调整可变

22、单元格中的数值，使之按照给定的 公式来满足目标单元格中的公式来满足目标单元格中的目标值目标值. 2、单变量求解方法、单变量求解方法 在工作表中输入原始数据；在工作表中输入原始数据； 建立可变数公式；建立可变数公式； 设置求解公式：菜单设置求解公式：菜单“工具工具”单变量求解单变量求解对话框对话框 中输入：目标单元格、目标值、可变单元格中输入：目标单元格、目标值、可变单元格 n例：例： 见见ch7.xls单变量求解单变量求解 某公司想向银行贷款某公司想向银行贷款900900万元人民币，贷款利率是万元人民币，贷款利率是8.7%8.7%， 贷款限期为贷款限期为8 8年，每年应偿还多少金额？年，每年应

23、偿还多少金额？ 如果公司每年可偿还如果公司每年可偿还120120万元，该公司最多可贷款多少万元，该公司最多可贷款多少 金额？金额？ 前一问题可用前一问题可用PMTPMT函数函数, , 后一问题可用单变量求解。后一问题可用单变量求解。 7.5 方案分析方案分析 1、概念、概念 n方案是已命名的一组输入值，是方案是已命名的一组输入值，是 Excel 保存在工作表中并保存在工作表中并 可用来自动替换某个计算模型的输入值，用来预测模型的可用来自动替换某个计算模型的输入值，用来预测模型的 输出结果。输出结果。 例例: n已知某茶叶公司已知某茶叶公司2004年的总销售额及各种茶叶的销售成本，年的总销售额及

24、各种茶叶的销售成本， 现要在此基础上制订一个五年计划。由于市场竞争的不断现要在此基础上制订一个五年计划。由于市场竞争的不断 变化，所以只能对总销售额及各种茶叶销售成本的增长率变化，所以只能对总销售额及各种茶叶销售成本的增长率 做一些估计。最好的方案当然是总销售额增长率高，各茶做一些估计。最好的方案当然是总销售额增长率高，各茶 叶的销售成本增长率低。叶的销售成本增长率低。 n最好的估计是总销售额增长最好的估计是总销售额增长13%，花茶、绿茶、乌龙茶、，花茶、绿茶、乌龙茶、 红茶的销售成本分别增长红茶的销售成本分别增长10%、6%、10%、7%。 见见ch7.xls方案方案 7.5 方案分析方案分

25、析 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中输入公式中输入公式 “

26、=B10*(1+$B$20)”， 并将其复制到并将其复制到D10F10； 第第11行数据是第行数据是第7，8，9， 10行数据对应列之和；净行数据对应列之和；净 收入是相应的总销售额和收入是相应的总销售额和 销售成本之差，销售成本之差，E19的总的总 净收入是第净收入是第13行数据之和。行数据之和。 7.5 方案分析方案分析 输入方案变量值如下图所示：输入方案变量值如下图所示： 7.5 方案分析方案分析 2、显示方案、显示方案 选择选择“工具工具” “方案方案”菜单菜单选择选择“方案管理器方案管理器”对话对话 框中的某一方案框中的某一方案单击单击 “显示显示”按钮按钮 3、建立方案报告、建立方

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

28、” 7.6 线性规划求解线性规划求解 1、概述概述 EXCELEXCEL提供提供的规划求解工具，可求解出线性与非线性两种的规划求解工具，可求解出线性与非线性两种 规划求解问题，规划求解问题常用于解决产品比例、人员规划求解问题，规划求解问题常用于解决产品比例、人员 调度、优化路线、调配材料等方面问题。调度、优化路线、调配材料等方面问题。 2、规划求解问题的特点：、规划求解问题的特点： n问题有单一的目标，如求运输的最佳路线、求生产的问题有单一的目标，如求运输的最佳路线、求生产的 最低成本、求产品的最大盈利，求产品周期的最短时最低成本、求产品的最大盈利，求产品周期的最短时 间等。间等。 n问题有明

29、确的不等式约束条件，例如生产材料不能超问题有明确的不等式约束条件，例如生产材料不能超 过库存，生产周期不能超过一个星期等。过库存，生产周期不能超过一个星期等。 n问题有直接或间接影响约束条件的一组输入值。问题有直接或间接影响约束条件的一组输入值。 7.6 线性规划求解线性规划求解 3、Excel规划求解问题的组成部分规划求解问题的组成部分 （1 1）一个或一组可变单元格）一个或一组可变单元格 可变单元格称为决策变量，一组决策变量代表一个规划可变单元格称为决策变量，一组决策变量代表一个规划 求解的方案求解的方案 （2 2）目标函数）目标函数 目标函数表示规划求解要达到的最终目标，是规划求解目标函

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

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

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

33、材料生产肥料的库存原材料 库存库存 情况情况 现有现有 库存库存 泥土泥土4100 有机有机 垃圾垃圾 3200 矿物矿物 质质 3500 修剪修剪 物物 表1 各肥料成品用料及其价格表 表的意思是生产一个单位的肥料需要多少各种原材料多少单位 产品产品泥土泥土有机垃圾有机垃圾矿物质矿物质修剪物修剪物单价单价 底层肥料底层肥料55547623105.00 中层肥料中层肥料6432452084.00 上层肥料上层肥料43329844105.00 劣质肥料劣质肥料1845231857.00 表表3单位原材料成本单价单位原材料成本单价 项项 目目单位成本单位成本 泥土泥土0.20 有机垃圾有机垃圾0.

34、15 矿物质矿物质0.10 修剪物修剪物0.23 7.6 线性规划求解线性规划求解 n建立规划求解模型步骤：建立规划求解模型步骤： 规划求解第一步规划求解第一步建立求解工作表建立求解工作表(输入原始数据及相应的各输入原始数据及相应的各 公式公式) 7.6 线性规划求解线性规划求解 规划求解第二步规划求解第二步设置求解参数设置求解参数 n选择选择“工具工具” “规划求解规划求解”菜单，设置以下求解的各菜单，设置以下求解的各 项参数：项参数： n设置目标单元格：输入目标函数所在单元格设置目标单元格：输入目标函数所在单元格(为总余额为总余额 单元格单元格) n设置目标：最大值、最小值或值的数值设置目

35、标：最大值、最小值或值的数值(最大利润最大利润,即最即最 大值大值) n设置可变单元格：它的确定决定结果（为生产数量）设置可变单元格：它的确定决定结果（为生产数量） n设置约束条件：单击设置约束条件：单击“添加添加”按钮按钮输入约束条件输入约束条件 按添加按添加依次输入所有约束条件依次输入所有约束条件确定确定 7.6 线性规划求解线性规划求解 n规划求解第规划求解第3步步保存求解结果保存求解结果 在规划求解对话框中按在规划求解对话框中按“求解求解”在规划求解结果对在规划求解结果对 话框中按话框中按“保存规划求解结果保存规划求解结果” 7.6 线性规划求解线性规划求解 4、修改资源、修改资源 例

36、例1 1： 见见ch7ch7.xls.xls规划求解规划求解 n肥料厂接到一个电话：只要公司肯花肥料厂接到一个电话：只要公司肯花1010元的运费就能元的运费就能 得到得到150150个单位的矿物。这笔交易稍稍降低了矿物质的个单位的矿物。这笔交易稍稍降低了矿物质的 平均价格，但这些矿物质值平均价格，但这些矿物质值1010元吗？元吗？ n解决该问题的方法是，将库存矿物解决该问题的方法是，将库存矿物35003500改为改为36503650，用，用 规划求解重新计算最大盈余。看除去￥规划求解重新计算最大盈余。看除去￥1010的成本后，的成本后， 盈余是否增加盈余是否增加 n操作操作: : 将库存矿物将

37、库存矿物35003500改为改为3650,3650,其它所有公式不变其它所有公式不变, ,再次进再次进 行求解行求解, ,求得盈余额为求得盈余额为4483.41,4483.41,原盈余额为原盈余额为4425.89.4425.89. 1010元成本后仍有元成本后仍有47.52.47.52.因此该矿物还是要的因此该矿物还是要的. . 7.6 线性规划求解线性规划求解 5、修改约束条件、修改约束条件 见见ch7.xls规划求解规划求解 n肥料厂接到一个电话，一个老顾客急需肥料厂接到一个电话，一个老顾客急需2525个单位的上个单位的上 层肥料，公司经理在检查打印结果后，发现没有安排层肥料，公司经理在检

38、查打印结果后，发现没有安排 生产上层肥料。数量为生产上层肥料。数量为0 0。决定增加约束条件，为他生。决定增加约束条件，为他生 产产2525个单位的上层肥料。个单位的上层肥料。 n结果可发现结果可发现: :盈余额仅盈余额仅3246.51,3246.51,比原来比原来4483.414483.41少了少了 1236.91236.9。显然不值得。但如该顾客为长期顾客，则短。显然不值得。但如该顾客为长期顾客，则短 期内将损失一些钱，但得到了顾客的信任。期内将损失一些钱，但得到了顾客的信任。 增加的增加的 约束条约束条 件件 7.6 线性规划求解线性规划求解 6、 规划求解的结果报告规划求解的结果报告

39、n运算结果报告：列出目标单元格、可变单元格及它们的运算结果报告：列出目标单元格、可变单元格及它们的 初始值、最终结果、约束条件和有关约束条件的信息。初始值、最终结果、约束条件和有关约束条件的信息。 见见ch7.xls运算结果报告运算结果报告 7.6 线性规划求解线性规划求解 n敏感性报告：敏感性报告： 见见ch7.xls敏感性报告敏感性报告 7.6 线性规划求解线性规划求解 n极限报告：列出目标单元格、可变单元格及它们的数极限报告：列出目标单元格、可变单元格及它们的数 值、上下限和目标值。下限为在满足约束条件和保持值、上下限和目标值。下限为在满足约束条件和保持 其它可变单元格数值不变的情况下，

40、某个可变单元格其它可变单元格数值不变的情况下，某个可变单元格 可以取得的最小值，上限则为在这种情况下可以取到可以取得的最小值，上限则为在这种情况下可以取到 的最大值。的最大值。 见见ch7.xls极限值报告极限值报告 7.6 线性规划求解线性规划求解 7、求解精度及求解模型设置求解精度及求解模型设置 Excel采用迭代的方式进行规划求解，当求解到一定精度采用迭代的方式进行规划求解，当求解到一定精度 时就结束求解，但有时要修改求解的精度、计算时间、规时就结束求解，但有时要修改求解的精度、计算时间、规 划模型和迭代次数。修改上述设置的方法如下：划模型和迭代次数。修改上述设置的方法如下： 在在“规划

41、求解参数规划求解参数”对话框中设置好各项求解参数；对话框中设置好各项求解参数； 单击单击“选项选项”按钮，在按钮，在“规划求解选项规划求解选项”对话框中设置对话框中设置 各项求解参数。各项求解参数。 7.6 线性规划求解线性规划求解 例例2： 求解不等式：求解不等式： 见见ch7ch7.xls.xls规划求解不等式规划求解不等式 某工厂生产甲、乙两种产品，假设生产甲产品某工厂生产甲、乙两种产品，假设生产甲产品1 1吨，要消耗吨，要消耗9 9 吨煤，吨煤，4 4千瓦电力，千瓦电力，3 3吨钢材，获利万元；生产乙产品吨钢材，获利万元；生产乙产品1 1吨，要吨，要 消耗消耗4 4吨煤，吨煤，5 5千

42、瓦电力，千瓦电力，1010吨钢材，获利万元。按计划国家吨钢材，获利万元。按计划国家 能提供给该厂的煤为能提供给该厂的煤为360360吨，电力吨，电力200200千瓦，钢材千瓦，钢材300300吨，问应吨，问应 该生产多少吨甲种产品和乙种产品，才能获得最大利润？该生产多少吨甲种产品和乙种产品，才能获得最大利润？ 假设生产甲种产品假设生产甲种产品X1X1吨，生产乙种产品吨，生产乙种产品x2x2吨，吨， 其最大利润是求的最大值。这个问题可用数学建模如下：其最大利润是求的最大值。这个问题可用数学建模如下： .0,0 .300103 .20054 .36049 21 21 21 21 xx xx xx

43、xx 7.6 线性规划求解线性规划求解 规划求解如下：规划求解如下： nB3B3和和C3C3分别用于保存甲和乙产品的生产量。分别用于保存甲和乙产品的生产量。 n目标单元格为目标单元格为B8B8；可变单元格为可变单元格为$ $B$3:$C$3B$3:$C$3；约束条件为：约束条件为： n$ $B$3=0B$3=0 n$C$3=0$C$3=0 n$ $B$4=360B$4=360 n$ $B$5=200B$5=200 n$ $B$6=300B$6=300 7.6 线性规划求解线性规划求解 n例例3： 见见ch7.xls线形规划求解线形规划求解 n某公司在某公司在A A地有一个生产基地地有一个生产基

44、地, ,其生产能力为其生产能力为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个配送中心的单

45、位运输成本是个配送中心的单位运输成本是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

46、的费用；的费用； 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 线性规划求解线性规划求解

47、 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两厂送到各配送中心的产品总量不能超过各配送中心的负两厂送到各配送中心

48、的产品总量不能超过各配送中心的负 荷能力：荷能力：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* *F8F8 7.7 数据分析工具库数据分析工具库 1、概述、概述 nExcelExcel提供了一组数据分析工具，称为分析工具库。其提供了一组数据分析工具，称为分析工具库。其 中提供的分析工具在工程分析、数理统计、经济计量中提供的分析工具在工程分析、

49、数理统计、经济计量 分析等学科中有较强的实用价值。分析等学科中有较强的实用价值。 n分析工具库由分析工具库由ExcelExcel自带的加载宏提供。自带的加载宏提供。 如果启动如果启动ExcelExcel后，在后，在ExcelExcel的的“工具工具”菜单中没有菜单中没有 “数据分析数据分析”菜单项，就需启动菜单项，就需启动“工具工具”中的中的“加载加载 宏宏”菜单项，将菜单项，将“分析工具库分析工具库”加载到加载到ExcelExcel系统中。系统中。 n如果加载宏对话框中没有分析工具库，则单击加载宏如果加载宏对话框中没有分析工具库，则单击加载宏 对话框中对话框中“浏览浏览”按钮，定位到分析工具

50、库加载宏文按钮，定位到分析工具库加载宏文 件件“Analy32.dll”Analy32.dll”所在的驱动器和文件夹，通常位于所在的驱动器和文件夹，通常位于 “Microsoft OfficeOfficeLibraryAnalysis”Microsoft OfficeOfficeLibraryAnalysis”中中, , 否则需运行否则需运行OfficeOffice系统的安装程序。系统的安装程序。 nExcelExcel的的“分析工具库分析工具库”加载宏提供的一些统计函数、加载宏提供的一些统计函数、 财务函数和工程函数。这些函数只有在安装了财务函数和工程函数。这些函数只有在安装了“分析分析 工

51、具库工具库”后才能使用后才能使用 。 7.7 数据分析工具库数据分析工具库 2、ExcelExcel分析工具库中的工具分析工具库中的工具 分析工具分析工具 名称名称 说说 明明 方差分析方差分析 包括包括3种类型的分析，它们是单因素方差分析、可种类型的分析，它们是单因素方差分析、可 重复双因素分析、无重复双因素分析重复双因素分析、无重复双因素分析 相关系数相关系数 分析分析 用于判断两组数据集（可以使用不同的度量单位）用于判断两组数据集（可以使用不同的度量单位） 之间的关系。之间的关系。 协方差分协方差分 析析 用于返回各数据点的一对均值偏差之间的乘积的用于返回各数据点的一对均值偏差之间的乘积

52、的 平均值。平均值。 描述统计描述统计 分析分析 用于生成对输入区域中数据的单变值分析，提供用于生成对输入区域中数据的单变值分析，提供 有关数据趋中性和易变性的信息有关数据趋中性和易变性的信息 指数平滑指数平滑 分析分析 基于前期预测值导出相应的新预测值，并修正前基于前期预测值导出相应的新预测值，并修正前 期预测值的误差。期预测值的误差。 7.7 数据分析工具库数据分析工具库 傅里叶分傅里叶分 析析 解决线性系统问题，并能通过快速傅里叶变换解决线性系统问题，并能通过快速傅里叶变换 （FFT）分析周期性的数据。）分析周期性的数据。 F-检验检验用来比较两个样本总体的方差用来比较两个样本总体的方差

53、 直方图分直方图分 析析 在给定工作表中数据单元格区域和接收区间的情在给定工作表中数据单元格区域和接收区间的情 况下，计算数据的个别和累计频率况下，计算数据的个别和累计频率 移动平均移动平均 分析分析 基于特定的过去某段时期中变量的均值，对未来基于特定的过去某段时期中变量的均值，对未来 值进行预测值进行预测 t-检验分检验分 析析 提供提供3种不同检验：双样本等方差假设种不同检验：双样本等方差假设t-检验，双检验，双 样本异方差假设样本异方差假设t-检验，平均值的成对双样本检验，平均值的成对双样本t- 检验检验 回归分析回归分析通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直

54、线拟合，直线拟合， 进行线形回归分析。进行线形回归分析。 抽样分析抽样分析以输入区域为总体构造总体的一个样本以输入区域为总体构造总体的一个样本 z-检验检验双样本平均差检验双样本平均差检验 7.7 数据分析工具库数据分析工具库 3、 统计分析统计分析 nExcelExcel的分析工具库提供了的分析工具库提供了3 3种统计观测分析工具：指种统计观测分析工具：指 数平滑分析、移动平均分析和回归分析数平滑分析、移动平均分析和回归分析 n三种工具用法相同，下面以指数平滑分析为例。三种工具用法相同，下面以指数平滑分析为例。 见见 ch7ch7.xls .xls 指数平滑分析指数平滑分析 （1 1）在工作

55、表的一列上输入各时间点上的观察值，如下）在工作表的一列上输入各时间点上的观察值，如下 图图A A列所示。列所示。 7.7 数据分析工具库数据分析工具库 （2 2）选择）选择“工具工具”菜单菜单“数据分析数据分析” 选项，对话框中选择选项，对话框中选择 “指数平滑指数平滑”按确定。按确定。 （3 3）在）在“指数平滑指数平滑”对话框中设置对话框中设置“输入区域输入区域”、“阻尼系阻尼系 数数”、“输出区域输出区域”选项。选项。 （4 4）选定对话框中）选定对话框中“图表输出图表输出”和和“标准误差标准误差”复选框标志。复选框标志。 分析结果：分析结果：B B列为分析之后输出的预测数据；列为分析之

56、后输出的预测数据；C C列是分析工具列是分析工具 输出的标准误差。输出的标准误差。 7.7 数据分析工具库数据分析工具库 4、假设检验、假设检验 n假设检验是根据对事物进行抽样所得的少量样本信息，假设检验是根据对事物进行抽样所得的少量样本信息， 判断总体分布的某个假设是否成立的一种数理统计方判断总体分布的某个假设是否成立的一种数理统计方 法。法。 n假设分析工具有三种：假设分析工具有三种：t-检验、检验、z-检验、检验、F-检验。检验。 n运用这些检验工具可以完成均值、方差的假设检验。运用这些检验工具可以完成均值、方差的假设检验。 方法见下例方法见下例 7.7 数据分析工具库数据分析工具库 n

57、例：例： 见见ch7.xlst检验检验 （双样本等方差（双样本等方差t-t-检验，以确定两个样本均值实际上检验，以确定两个样本均值实际上 是否相等）是否相等） 某种子公司为比较两个稻种的产量，选择了某种子公司为比较两个稻种的产量，选择了2525块条块条 件相似的试验田，采用相同的耕种方法进行耕种试件相似的试验田，采用相同的耕种方法进行耕种试 验，结果播种甲稻种的验，结果播种甲稻种的1313块田的亩产量（单位：市块田的亩产量（单位：市 斤）分别是：斤）分别是：880880、1 1201 120、980980、885885、828828、927927、 924924、942942、766766、1

58、 1801 180、780780、1 0681 068、650650；播种；播种 乙稻种的乙稻种的1212块试验田的亩产量分别是：块试验田的亩产量分别是：940940、1 1421 142、 1 0201 020、785785、645645、780780、1 1801 180、680680、810810、824824、 846846、780780。问这两个稻种的产量有没有明显的高低。问这两个稻种的产量有没有明显的高低 之分。之分。 说明：要判断两稻种有无显著差别，用说明：要判断两稻种有无显著差别，用t-t-检验方法，需检验方法，需 先计算各样本的平均值和方差，才能作进一步的检验先计算各样本的平

59、均值和方差，才能作进一步的检验 分析。分析。 7.7 数据分析工具库数据分析工具库 nt-t-检验操作过程：检验操作过程： n(1)(1)输入输入A A、B B两列样本数据（下表中右边数据全为产生的两列样本数据（下表中右边数据全为产生的 分析结果）分析结果） 7.7 数据分析工具库数据分析工具库 n(2)(2)选择选择“工具工具”菜单中菜单中“数据分析数据分析”选项，对话框中选选项，对话框中选 择择t t检验双样本等方差假设；检验双样本等方差假设； n（3 3）在）在“双样本等方差假设分析双样本等方差假设分析”对话框中设置对话框中设置t t检验的检验的 各项参数各项参数 n按确定按确定 7.7

60、 数据分析工具库数据分析工具库 5、回归分析、回归分析 n回归分析主要用于分析单个因变量是如何受一个或几个自回归分析主要用于分析单个因变量是如何受一个或几个自 变量影响的。如观察某个运动员的运动成绩与一系列统计变量影响的。如观察某个运动员的运动成绩与一系列统计 因素的关系。如年龄、体重、身高等。因素的关系。如年龄、体重、身高等。 n回归分析分为线性回归和非线性回归两种。线性回归的数回归分析分为线性回归和非线性回归两种。线性回归的数 学模型为：学模型为： nExcelExcel通过对一组观察值使用通过对一组观察值使用“最小二乘法最小二乘法”直线拟合，进直线拟合，进 行线性回归分析，该回归分析可同