使用单变量求解和规划求解分析数据.ppt

上周回顾 课堂学习 执行电子数据表的假设分析(第22章) 交流学习 处理单元格和区域(第4章) 格式化工作表(第5章) 理解文件和模版(第6章) 归纳excel的用途，并举出一个实例(详细描述 课后自学 介绍公式和函数(第8章) 课题研究 确定自己小组的工作目标和工作计划 本周内容 课堂学习 使用单变量求解和规划求解分析数据(第23章) 交流学习 介绍公式和函数(第8章) 课后自学 创建计数和求和的公式(第11章) 课题研究 收集数据信息 由于课程安排原因，讲演交流内容应 与时俱进 参照同期教学要求同步进行！ 此外： 与以上同学相关各组的同学发扬团队精神，做好相应的配合。 其他小组的自学报告和课题研究的电子版，请在上机时上交汇总检查。 （用U盘或MP3装） 第23章 使用单变量求解和规划求解分析数据 23.1 反转的假设分析 p.421 假设分析解决什么样的问题? “如果销售额增长20％，总收益是多少？” 条件→结果 新问题 “销售增长多少才可以产生1200万美元的收益？” 结果→条件 类似于数学中的反函数 自变量和因变量发生角色转换 反转假设工具 单变量求解:(Goal Seek) 规划求解 反转的假设分析 两种工具 单变量求解： 决定单一输入单元格中所需的数值 这一数字产生用户在独立（公式）单元格中所需结果 规划求解： 决定用户在多重输入单元格中输入的产生用户所需的结果 23.2 单一单元格变量求解 23.2.1 一个单变量求解的例子 p.421~422 假设 用户要购买一处新住宅 每月可以提供$1800的抵押支付 贷方对80％的抵押贷款（20％为预付定金） 要求6.50%的固定利率 问 用户能够支付最大购买价格是多少？ 分析 用户可以在C11中显示为$1800时， 在C4单元格中改变数值 步骤： 【工具】|【单变量求解】p.423 23.2.2 更多了解单变量求解 遇到“可变单元格”不能产生求解时，可以： 将“可变单元格”中当前值调整为更接近求解的值 调整 “工具”|“选项”|“重新计算”|“最多迭代次数” 检查函数的逻辑性 注意 必须确认目标单元格与可变单元格具有函数依赖关系 精确度局限性 可以通过“选项”|“重新计算”中调整精度 多个数值产生同样结果 当两个解都有可能时 Excel给出有同样标记的解作为单元格当前值 23.3 规划求解 可能是Excel中最难掌握的一个功能！ 规划求解与单变量求解的区别 指定多个可调整的单元格 指定可调整单元格可能有的数值约束 求出特定单元格的最大值或最小值 对一个问题求多个解 23.3.1 选择适当问题 p.424 选择标准 目标单元格依靠于其他单元格或公式； 如， 使目标单元格是最大或最小，或使之同某些数值相等 目标单元格依靠一组单元格（可变单元格） 可通过规划求解调整这组单元格来影响目标单元格 解必须服从一定的约束和限制 【规划求解】命令安装 【工具】|【加载宏】|【规划求解】 提示:?? 如果“规划求解”未列出 单击“浏览”进行查找 23.3.2 生产规划的规划求解使用实列 p.424 约束条件 组合生产能力：每天300单位； 生产能力为300单位 B6=300 现有订单要求：产品A，50单位； 至少50单位产品A B3=50 预订单：产品B，40单位； 至少40单位产品B B4=40 市场需求限制：产品C，40单位以下； 不超过40单位产品C B5=40 保存和装入模型： 保存模型： 【工具】【规划求解】【选项】【保存模型】 装入模型 【工具】【规划求解】【选项】【装入模型】 规划求解过程小结 “设置目标单元格” 绝对引用? 建议通过选取单元格的方式进行操作 对目标单元格中的期待值进行设置： “最大值” “最小值” “值为”：直接键入数值 “可变单元格”框 建议通过选取单元格的方式进行操作 也可键入每个可变单元格的名称或引用 用逗号分隔不相邻的引用 由计算机自动设定 单击“推测” “约束”框 通过【添加】进行输入相应的约束条件 单击“求解”，再执行下列操作之一： 若要在工作表中保存求解后的数值， 单击“保存规划求解结果”。 若要恢复原始数据， 单击“恢复为原值”。 规划求解选项 2 收敛度 只应用于非线性规划求解问题 必须表示为小数（0 到 1 之间）。 设置的数值越小，收敛度就越高。 例如，0.0001 表示比 0.01 更小的相对差别。 收敛度越小，“规划求解”得到结果所需的时间就越长。 采用线性模型 当模型中的所有关系都是线性的，并且希望解决线性优化问题时，选中此复选框可加速求解进程。 显示迭代结果 如果选中此复选框，每进行一次迭代后都将中断“规划求解”，并显示当前的迭代结果。 规划求解选项 3 假定非负 对于在“添加约束”对话框的“约束值”框中没有