EXCEL规划求解案例分析 ppt课件
合集下载
第13讲Excel的规划求解解读
2018/9/29
大学计算机信息科技教程(第二版)
4
计 算 机 高 级 办 公 自 动 化
SUMPRODUCT函数
用法:
SUMPRODUCT(array1,array2,array3, ...)
功能:在给定的几组数组中,将数组间对应的元 素相乘，并返回乘积之和。 说明:
Array1, array2, array3, ... 为 2 到 30 个数组，其相 应元素需要进行相乘并求和。 数组参数必须具有相同的维数 函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理
2018/9/29 大学计算机信息科技教程(第二版) 5
计 算 机 高 级 办 公 自 动 化
2018/9/29
实例
大学计算机信息科技教程(第二版)
6
计 算 机 高 级 办 公 自 动 化
上机实验
芯片制造商如何最大化它每月的利润？
该厂有四位技术员（A、B、C
和 D），生产三种
产品（产品 1、2 和 3）。 芯片制造商每月可以销售 80 件产品 1，50 件产品 2，产品 3 最多可销售 50 件。 技术员 A 只能生产产品 1 和 3，技术员 B 只能生 产产品 1 和 2，技术员 C 只能生产产品 3，技术 员 D 只能生产产品 2。 对于生产的每件产品，产品 1、2 和 3 的利润分别 为 6 美元、7 美元和 1
2
计 算 机 高 级 办 公 自 动 化
规划求解概述
在创建模型过程中，可对“规划求解”模型中的可变 单元格数值应用约束条件 约束条件：“规划求解”中设置的限制条件。 可以将约束条件应用于可变单元格、目标单元格 或其他与目标单元格直接或间接相关的单元格。 约束条件可以引用其他影响目标单元格公式的单 元格。
EXCEL规划求解案例分析
(1)由于产品II销售疲软，故希望产品II的 产量不超过产品I 的一半； (2)原材料严重短缺，生产中应避免过量消 耗； (3)最好能节约4h设备工时； (4)计划利润不少于48元。
类似这样的多目标决策问题是典型的 目标规划问题。
运用EXCEL求解线性规划问题
outline
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 敏感性分析
第五步 选择“采用线性模型”和“假定非负”，单 击“确定”，返回下图。单击“求解”，即可解决 此题。
最后结果如下页图所示。
用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。
最优解
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解，改进对 最大值为增加，对 最小值为减少。
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
4. “规划求解”步骤
⑴ 启用“规划求解”宏；
⑵ 输入数据；
⑶ 利用函数“SUMPRODUCT”引入约束 与目标
⑷ 对话框“规划求解”的各要素.
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。
因此，付给某工人10元以增加l小时劳动时间是值得的， 可多获利为：
类似这样的多目标决策问题是典型的 目标规划问题。
运用EXCEL求解线性规划问题
outline
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 敏感性分析
第五步 选择“采用线性模型”和“假定非负”，单 击“确定”，返回下图。单击“求解”，即可解决 此题。
最后结果如下页图所示。
用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。
最优解
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解，改进对 最大值为增加，对 最小值为减少。
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
4. “规划求解”步骤
⑴ 启用“规划求解”宏；
⑵ 输入数据；
⑶ 利用函数“SUMPRODUCT”引入约束 与目标
⑷ 对话框“规划求解”的各要素.
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。
因此，付给某工人10元以增加l小时劳动时间是值得的， 可多获利为：
运用EXCEL求解线性规划模型PPT58页
END
16、业余生活要有意义，不要越轨。——华盛顿 17、一个人即使已登上顶峰，也仍要自强不息。——罗素·贝克 18、最大的挑战和突破在于用人，而用人最大的突破在于信任人。——马云 19、自己活着，就是为了使别人过得更美好。——雷锋 20、要掌握书，莫被书掌握；要为生而读，莫为读而生。——布尔沃
运用EXCEL求解线性规划模型
51、没有哪个社会可以制订一部永远 适用的 宪法， 甚至一 条永远 适用的 法律。 ——杰 斐逊 52、法律源于人的自卫本，法律就是这样 一种的 网，触 犯法律 的人， 小的可 以穿网 而过， 大的可 以破网 而出， 只有中 等的才 会坠入 网中。 ——申 斯通 54、法律就是法律它是一座雄伟的大 夏，庇 护着我 们大家 ；它的 每一块 砖石都 垒在另 一块砖 石上。 ——高 尔斯华 绥 55、今天的法律未必明天仍是法律。 ——罗·伯顿
EXCEL规划求解案例分析
什么是规划问题?
在计划管理中常会遇到：人力资源的调 1、约束条件的表达 度、产品生产的安排、运输线路的规划、生 产材料的搭配、采购批次的确定等问题。 2、目标的数学描述 这类问题有一个共同点，即需要解决： 3、应用Excel的规划 如何合理利用各种存在约束的资源，而获得 求解工具对问题求解 最佳的经济效益，也就是达到利润最大、成 本最低等目标。这就是本节要解决的“在约 束条件下寻求目标函数最优解的规划问题”。
第五步 选择“采用线性模型”和“假定非负”， 单击“确定”，返回下图。单击“求解”，即可解 决此题。
最后结果如下页图所示。
用Excel求解得对应的敏感性报告（灵敏度分）析如下表所示。
递减成本指目标函 数中决策变量的系 数必须改进多少才 能得到该决策变量 的正数解，改进对 最大值为增加，对 最小值为减少。 最优解
c
+ △c
-△c
实际使用量
对偶最 优解
b
+△
b
-△
b
5. 敏感性分析
• 在实际问题中，规划模型中的大多数数 据是测量、统计、评估或决策而得出来的。 因此有必要分析当这些数据发生波动时会 对最优解和最优值产生什么影响。这就是 灵敏度分析。
出现以下假设，上述案例如何决策？？?
• （2）家具厂是否愿意出10元的加班费，让 某工人加班1小时？
产品 I II 限量
原材料(kg/件)
设备工时(h/件) 利润(元/件)
5
4 6
10
4 8
60
40
问该公司应制造两种家电各多少件，使获取的利润 为最大。
设产品I和II的产量分别为x1和x2，其数学 模型为：
max Z= 6x1 +8x2 5x1 + 10x2 ≤60 st. 4x1 + 4x2 ≤40 x1 , x2 ≥0
《EXCEL求解示例》PPT课件
注意：此处也是分别变动，而不是同时变动。
极限值报告解释
列出目标单元格和可变单元格以及它们的数值、 上下限和目标值。含有整数约束条件的模型不 能生成本报告。其中，下限是在满足约束条件 和保持其它可变单元格数值不变的情况下，某 个可变单元格可以取到的最小值。上限是在这 种情况下可以取到的最大值。
延伸
（2）约束限制值同时变动的百分之百法则 （ The 100 percent rule of simultaneous changes in right-hand sides）： 同时改变几个或所有函数约束的约束右端值 ，如果这些变动的幅度不大，那么可以用影 子价格预测变动产生的影响。为了判别这些 变动的幅度是否允许，计算每一变动占同方 向可容许变动范围的百分比，如果所有的百 分比之和不超过百分之一百，那么影子价格 还是有效的；如果所有的百分比之和超过百 分之一百，那就无法确定影子价格是否有效。
工厂1每周可用时间在[4-2，+∞]之间发生变 化时，影子价格恒为0，对目标函数值无影响；
工厂2每周可用时间在[12-6，12+6]之间发生 变化时，影子价格恒为150，即每增加一个单 位可用时间，目标函数值就增加150，
工厂3每周可用时间在[18-6，18+6]之间发生 变化时，影子价格恒为100，即每增加一个单 位可用时间，目标函数值就增加100。
下面对目标式系数同时变动以及约束限制值同 时变动的情况分别作以延伸。
（1）目标式系数同时变动的百分之百法则 （ The 100 percent rule of simultaneous changes in objective function coefficients）： 如果目标函数系数同时变动，计算出每一系 数变动量占该系数同方向可容许变动范围的 百分比，而后将各个系数的变动百分比相加 ，如果所得的和不超过百分之一百，最优解 不会改变；如果超过百分之一百，则不能确 定最优解是否改变。
极限值报告解释
列出目标单元格和可变单元格以及它们的数值、 上下限和目标值。含有整数约束条件的模型不 能生成本报告。其中，下限是在满足约束条件 和保持其它可变单元格数值不变的情况下，某 个可变单元格可以取到的最小值。上限是在这 种情况下可以取到的最大值。
延伸
（2）约束限制值同时变动的百分之百法则 （ The 100 percent rule of simultaneous changes in right-hand sides）： 同时改变几个或所有函数约束的约束右端值 ，如果这些变动的幅度不大，那么可以用影 子价格预测变动产生的影响。为了判别这些 变动的幅度是否允许，计算每一变动占同方 向可容许变动范围的百分比，如果所有的百 分比之和不超过百分之一百，那么影子价格 还是有效的；如果所有的百分比之和超过百 分之一百，那就无法确定影子价格是否有效。
工厂1每周可用时间在[4-2，+∞]之间发生变 化时，影子价格恒为0，对目标函数值无影响；
工厂2每周可用时间在[12-6，12+6]之间发生 变化时，影子价格恒为150，即每增加一个单 位可用时间，目标函数值就增加150，
工厂3每周可用时间在[18-6，18+6]之间发生 变化时，影子价格恒为100，即每增加一个单 位可用时间，目标函数值就增加100。
下面对目标式系数同时变动以及约束限制值同 时变动的情况分别作以延伸。
（1）目标式系数同时变动的百分之百法则 （ The 100 percent rule of simultaneous changes in objective function coefficients）： 如果目标函数系数同时变动，计算出每一系 数变动量占该系数同方向可容许变动范围的 百分比，而后将各个系数的变动百分比相加 ，如果所得的和不超过百分之一百，最优解 不会改变；如果超过百分之一百，则不能确 定最优解是否改变。
EXCEL规划求解案例分析
什么是规划问题?
在计划管理中常会遇到：人力资源的调度、
产品生产的安排、运1输、线约路束的条规划件、的生表产达材 料的搭配、采购批次2的、确目定标等的问题数。学描述
这类问题有一个共3同、点应，用即需Ex要ce解l的决：规如划 何合理利用各种存在求约解束的工资具源对，问而题获求得解最
佳的经济效益，也就是达到利润最大、成本 最低等目标。这就是本节要解决的“在约束 条件下寻求目标函数最优解的规划问题”。
4. 利用Excel技术可以简单的求得问题满足约束 条件求的目标最优解。
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。
• （3）如果可提供的工人劳动时间变为398 小时，该厂的日利润有何变化？
• （4）该厂应优先考虑购买何种资源？ • （5）若因市场变化，第一种家具的单位利
润从60元下降到55元，问该厂的生产计划 及日利润将如何变化？
本问题的敏感性报告如上页表所示。 由上述敏感性报告可进行灵敏度分析，并回答题目中的问题 (2)一(5)。 (2)由敏感性报告可知，劳动时间的影子价格为12元，即在劳 动时间的增量不超过25小时的条件下，每增加l小时劳动时间， 该厂的利润(目标值)将增加12元。
EXCEL规划求解案例分析
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
• （3）如果可提供的工人劳动时间变为398 小时，该厂的日利润有何变化？ • （4）该厂应优先考虑购买何种资源？ • （5）若因市场变化，第一种家具的单位利 润从60元下降到55元，问该厂的生产计划 及日利润将如何变化？
本问题的敏感性报告如上页表所示。 由上述敏感性报告可进行灵敏度分析，并回答题目中的问题 (2)一(5)。 (2)由敏感性报告可知，劳动时间的影子价格为12元，即在劳 动时间的增量不超过25小时的条件下，每增加l小时劳动时间， 该厂的利润(目标值)将增加12元。 因此，付给某工人10元以增加l小时劳动时间是值得的， 可多获利为： 12—10＝2(元)。 (3)当可提供的劳动时间从400小时减少为398小时时，该减 少量在允许的减量(100小时)内，所以劳动时间的影子价格不 变，仍为12元。 因此，该厂的利润变为： 9200+12X(398—400)＝9 176(元)。
c
+ △c
-△c
实际使用量
对偶最 优解
b
+△
b
-△
b
5. 敏感性分析
• 在实际问题中，规划模型中的大多数数 据是测量、统计、评估或决策而得出来的。 因此有必要分析当这些数据发生波动时会 对最优解和最优值产生什么影响。这就是 灵敏度分析。
出现以下假设，上述案例如何决策？？?
规划求解培训课件PPT
• “规划求解”得到的结果与以前的结果不同
– “规划求解”显示如下消息：“规划求解已收敛到当前结果。满 足所有约束条件”。这表明目标单元格中的数值在最近五次求解 过程中的变化量小于“规划求解选项”对话框中“收敛度”设置 的值。“收敛度”中设置的值越小，“规划求解”在计算时就会 越精细，但求解过程将花费更多的时间。
特殊饲料的营养要求是至少30%的蛋白质和至多5%的纤 维。该农场希望确定每天最小成本的饲料配制。
– 因为饲料由玉米和大豆粉配制而成，所以模型的决策变量定义为：
x1=每天混合饲料中玉米的重量（磅） x2=每天混合饲料中大豆粉的重量（磅） – 目标函数是使配制这种饲料的每天总成本最小，因此表示为： min z=0.3×x1+0.9×x2
• “规划求解”不能达到最优解，下面列出“规划求解”显 示的完整消息。
– “规划求解”不能改进当前解，所有约束条件都得到了满足：这 表明仅得到近似值，迭代过程无法得到比显示结果更精确的数值； 或是无法进一步提高精度，或是精度值设置得太小，请在“规划 求解选项”对话框中试着设置较大的精度值，然后再运行一次。
– 求解达到最长运算时间后停止:这表明在达到最长运算时间限制时， 没有得到满意的结果。若要保存当前结果并节省下次计算的时间， 请单击“保存规划求解”或“保存方案”选项。 – 求解达到最大迭代次数后停止：这表明在达到最大迭代次数时， 没有得到满意的结果。增加迭代次数也许有用，但是应该先检查 结果数值来确定问题的原因。若要保存当前值并节省下次计算的 时间，请单击“保存规划求解”或“保存方案”选项。 – 目标单元格中的数值不收敛：这表明即使满足全部约束条件，目 标单元格数值也只是有增或有减但不收敛。这可能是在设置问题 时忽略了一项或多项约束条件。请检查工作表中的当前值，确定 数值发散的原因，并且检查约束条件，然后再次求解。 – “规划求解”未找到合适结果：这表明在满足约束条件和精度要 求的条件下，“规划求解”无法得到合理的结果，这可能是约束 条件不一致所致。请检查约束条件公式或类型选择是否有误。
EXCEL规划求解案例分析 ppt课件
EXCEL规划求解案例分析
目标规划问题及其数学模型？？？
目标规划问题的提出
例1 某工厂生产两种产品，受到原材料供应和设备工 时的限制。在单件利润等有关数据已知的条件下，要 求制定一个获利最大的生产计划。具体数据如下：
产品
I
II
限量
原材料(kg/件) 5
10
60
设备工时(h/件) 4
4
40
利润(元/件)
EXCEL规划求解案例分析
在计划管理中常会遇到：人力资源的调度、
产品生产的安排、运1输、线约路束的条规件划、的生表产达材 料的搭配、采购批次2的、确目定标等的问数题。学描述
这类问题有一个共3同、点应，用即需Ex要ce解l的决：规如划 何合理利用各种存在求约解束工的资具源对，问而题获求得解最
佳的经济效益，也就是达到利润最大、成本 最低等目标。这就是本节要解决的“在约束 条件下寻求目标函数最优解的规划问题”。
应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
家具类型
12
3
劳动时间（小时/件） 木材（单位/件）
213 421
玻璃（单位/件）
621
单位利润（元/件） 60 20 40
最大销售量（件） 100 200 50
4
可提供量
2 400小时 2 600单位 2 1000单位 30 100
• “太阳当空照，花儿对我笑，小鸟说早早早……”
设产品I和II的产量分别为x1和x2，其数学 模型为：
max Z= 6x1 +8x2 5x1 + 10x2 ≤60
st. 4x1 + 4x2 ≤40 x1 , x2 ≥0
目标规划问题及其数学模型？？？
目标规划问题的提出
例1 某工厂生产两种产品，受到原材料供应和设备工 时的限制。在单件利润等有关数据已知的条件下，要 求制定一个获利最大的生产计划。具体数据如下：
产品
I
II
限量
原材料(kg/件) 5
10
60
设备工时(h/件) 4
4
40
利润(元/件)
EXCEL规划求解案例分析
在计划管理中常会遇到：人力资源的调度、
产品生产的安排、运1输、线约路束的条规件划、的生表产达材 料的搭配、采购批次2的、确目定标等的问数题。学描述
这类问题有一个共3同、点应，用即需Ex要ce解l的决：规如划 何合理利用各种存在求约解束工的资具源对，问而题获求得解最
佳的经济效益，也就是达到利润最大、成本 最低等目标。这就是本节要解决的“在约束 条件下寻求目标函数最优解的规划问题”。
应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
家具类型
12
3
劳动时间（小时/件） 木材（单位/件）
213 421
玻璃（单位/件）
621
单位利润（元/件） 60 20 40
最大销售量（件） 100 200 50
4
可提供量
2 400小时 2 600单位 2 1000单位 30 100
• “太阳当空照，花儿对我笑，小鸟说早早早……”
设产品I和II的产量分别为x1和x2，其数学 模型为：
max Z= 6x1 +8x2 5x1 + 10x2 ≤60
st. 4x1 + 4x2 ≤40 x1 , x2 ≥0
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
例1. 雅致家具厂生产计划优化问题
雅致家具厂生产4种小型家具，由于该四种家具具 有不同的大小、形状、重量和风格，所以它们所需 要的主要原料（木材和玻璃）、制作时间、最大销 售量与利润均不相同。该厂每天可提供的木材、玻 璃和工人劳动时间分别为600单位、1000单位与400 小时，详细的数据资料见下表。
完整的模型描述：
第二步 在“工具”菜单中选择“规划求解”。
第三步 在“规划求解参数”对话框进行选择如下图。
第四步 点击“选项”按钮，弹出“规划求解选项”对话框
第五步 单击“求解”，即可解决此题。
最后结果如下页图所示。
实验内容：
分别运用 EXCEL和LINDO 求解, 学委 在下周上课前把电子版的结果收齐。
运用EXCEL求解线性规划问题
广东商学院 数学与计算科学学院
outline
1.关于“规划求解” 2.如何加载“规划求解” 3. “规划求解”各参数设置 4. “规划求解”步骤 5. 利用“规划求解”解线性规划问题
1. 关于“规划求解”
Microsoft Excel的“规划求解”工具取自德克 萨斯大学奥斯汀分校的Leon Lasdon和克里 夫兰州立大学的Allan Waren共同开发的 Generalized Reduced Gradient(GRG2)非线性 最优化代码. 线性和整数规划问题取自 Frontline Systems公司的John Waston和Dan Fylstra提供的有界变量单纯形法和分支定界 法
（木材约束）
6x1 2x2 x3 2x4 1000 （玻璃约束）
2x1 1x2 3x3 2x4 400 （劳动时间约束）
s.t
.
x1 x2
100 200
（家具1需求量约束） （家具2需求量约束）
x3
50
x4 100
（家具3需求量约束） （家具4需求量约束）
2.如何加载“规划求解”
1) 在“工具”菜单上,单击“加载宏”
2) 在弹出的对话框中的“可用加载宏”列表框 中,选定待添加的加载宏“规划求解”选项旁 的复选框,然后单击“确定”.单击“确定” 后,“工具”菜单下就会出现一项“规划求解”
3. “规划求解”各参数设置
单击“规划求解”按钮,将会出现以下规划求 解参数设置对话框
x1, x2 , x3 , x4 0 （非负约束）
其中 x1, x2 , x3, x4 分别为四种家具的日产量。
下面介绍用Excel中的“规划求解”功能求此题。 第一步 在Excel中描述问题、建立模型，如下图所示。
并将同一种类型的数据指定“名称”
调用函数“SUMPRODUCT”
若不指定名称： 若指定名称：
解：依题意，设置四种家具的日产量分别为决策变量
x1, x2 , x3, x4 ，目标要求是日利润最大化，
约束条件为三种资源的供应量限制和产品销售量限制。 据此，列出下面的线性规划模型：
MaxZ 60x1 20x2 40x3 30x4
4x1 2x2 x3 2x4 600
应如何安排这四种家具的日产量，使得该厂的日利 润最大？
表1 雅致家具厂基本数据
家具类型
劳动时间（小时/件） 木材（单位/件）
玻璃（单位/件） 单位利润（元/件） 最大销售量（件）
12 3
213 421 621 60 20 40 100 202 600单位 2 1000单位 30 100
• 单击“添加”,显示添加约束对话框
• 选项:显示”规划求解选项”对话框.在其中可 以加载或保存规划求解模型,并对规划求解过 程的高级属性进行控制
4. “规划求解”步骤
⑴ 启用“规划求解”宏； ⑵ 输入数据； ⑶ 利用函数“SUMPRODUCT”引入约束与目标 ⑷ 对话框“规划求解”的各要素.