excel线性规划实验：奶制品厂生产(销售)的最优化决策实验指导书
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
B 8 9 10 11 12 13 C 奶制品 A（1 号生产线） 10 安排生产数量（公斤） =C9/C3*C4 所需要工时（小时） 折算为需要的牛奶 （桶） =C9/C3 =C9 *C5 所获利润（元） D E F 限额 100 480 50 奶制品 B （2 实际量 号生产线） 10 =D9/D3*D4 =D9/D3 =D9 *D5 =C9 =SUM （C10:D10） =SUM （C11:D11） =SUM （C12:D12）
“敏感性报告”(见图 1-6)提供关于求解结果对目标函数和约束条件微小变化 的敏感性信息。本实验是线性模型，在规划求解时必须将“规划求解选项”中的“采 用线性规划”选项打上钩，那么“敏感性报告”中将包含缩减成本、阴影价格、目标 系数以及右侧约束区域。如果采用非线性规划，则“敏感性报告”中将包含缩减梯 度和拉格朗日乘数。
基础数据模块
线性规划优化模块
图 1-1 参考模型 在模型中，基础数据模块的所有数据全部为已知的原始数据，可以直接在单 元格中输入数据，他们是约束条件函数中确定决策变量 x1 和 x 2 系数的基本依据。 在优化模块中，除了决策变量、限额的取值可以采用直接输入数据录入的方 式进行表达，其他单位格的数据必须采用引入单元格的方法使用基础数据模块中 的数值，并运用函数，表达各种约束条件中的系数值，不能够直接填写（图 1-1 中灰色的部分） 。模型中各单元格的计算公式参见图 1-2。
实验 1 奶制品厂生产/销售的最优化决策
实验 1-1
“猛牛”奶制品厂生产计划制定
实验目的 理解最优化模型的概念； 掌握在 Excel 中构造线性规划模型的方法； 掌握用"规划求解"工具正确求解最优化问题的步骤。
实验环境 Microsoft Office Excel 2003（2007） ； 在 Excel 中加载"规划求解"工具。
格$C$9 和$D$9 的最优解分别为 60 和 1200。$C$9 的上、下限(60 和 0)分别表示 在满足约束条件和保持其他可变单元格 $D$9 的值不变的情况下，可变单元格 $C$9 可以取到的最大、最小值。同样，$D$9 的上、下限(120 和 0)分别表示在满 足约束条件和保持其他可变单元格 $C$9 的值不变的情况下，可变单元格$D$9 可以取到的最大、最小值。
供应站 1 2 3 4 5 2 1.3 3 2.1 0.9 4 0.9 1.8 2.6 5 0.7 1.2 1.0 0.8 6 1.8 2.6 2.5 1.6 0.9
实验步骤 步骤 1：分析问题，确定解决方案。 本问题中，线路的走向必须在满足每个供应站都连上的基础上，使得所选的
线路的总距离最短，这种要求在保证满足目标函数最优的条件下，选中几个变量 的问题属于“选址问题”，可用“0-1”规划的方法来求解。 步骤 2：在 Excel 中建立模型框架。 建立如图 1-8 所示的参考模型。该模型分为 4 部分：第 1 部分为各供应站之 间的距离 (系数矩阵)；第 2 部分为与系数矩阵相似的规划矩阵，该矩阵的取值只 有 0 和 1 两种可能；第 3 部分是单位“人工费”、“材料费”等成本要素的单位值计 算出的每公里成本（180=30 人工费+50 材料费+100 其他费用） ；第 4 部分为由系 数矩阵和规划矩阵运算后得到的所选线路的总距离，以及由每公里成本与总距离 的乘积，即铺设管道的总成本。
选择“规划求解加载项”，点击 “确定”。
“规划求解”加载项将出现在“数据”工具栏中。
实验要求 “猛牛”奶制品厂用牛奶为原料生产 A 和 B 两种奶制品。 一桶牛奶可以在 1 号 生产线上用 12 小时加工成 3 公斤 A 产品， 或者在 2 号生产线上用 8 小时加工成 4 公斤 B 产品。由于市场对 A、B 两种奶制品的需求非常旺盛，因此每天所生产的 A、B 两种奶制品都能全部出售。根据目前的市场情况，销售每公斤 A 产品可获 利 24 元，销售每公斤 B 产品可获利 16 元。“猛牛”奶制品厂每天能得到 50 桶牛 奶的供应量，每天工人的总劳动时间为 480 小时，并且 1 号生产线每天至多能够 加工 100 公斤 A 产品，2 号生产线的加工能力没有限制。请为该厂制定一个生产 计划。如何安排 A、B 两种奶制品的生产，能使每天获利最大？
例如， 我们使用第 10 行单元格进行时间约束表达。 实际量代表在目前决策变 量 x1 和 x 2 取值条件下，不同产品生产总共消耗的时间。其取值为 60，60 这个值 不能够直接输入数值，必须使用函数表达“=SUM（C10:D10）”，以表示总生产和 特定品种生产量之间的数学关系。该函数表达的含义是，生产需要消耗的时间为 A、B 两种奶制品生产加工时间之和。其中，奶制品 A 在现有产量下的生产时间 为 40，40 这个值同样不能够直接输入，需要使用函数表达“=C9/C3*C4”，其含义 表达为“生产 10 公斤奶制品 A 需要消耗的时间”。 再一次强调，在线性规划优化模块中，呈灰色的单元格中的数据全部都是使 用函数进行表达，不能过直接输入数据。 在图 1-1 中，决策变量 x1 和 x 2 所在单位格为 C9、D10，其初始值可以随意设 置，直接填写。在本例中我们直接填写为 10。在使用“规划求解”加载项进行求解 后，决策变量所在单元格的数值会自动发生变化。 步骤 3：参考图 1-2，检查模型中各单元格的计算公式。
实验准备 （1）使用 Microsoft Office Excel 2003 加载规划求解工具 “工具”→“加载宏”→“规划求解”
（2）使用 Microsoft Office Excel 2007 加载规划求解工具
新建 Excel 文件，点击
，选择 Excel 加载项。
点击“加载项”，选择“规划求解加载项”，点击 “转到”。
因为每公斤 A 产品可获利 24 元，每公斤 B 产品可获利 16 元，那么目标函数 即为 y 24 x1 16 x2 。 由于 1 号生产线可用 12 小时加工成 3 公斤 A 产品，因此生产每公斤 A 产品 所需要的时间为 4 小时；同理，2 号生产线生产每公斤 B 产品所需要的时间为 2 小 时 ， 生 产 两 种 产 品 的 总 时 间 不 能 超 过 480 小 时 ， 工 时 的 约 束 条 件 为
图 1-2 参考模型中的公式 步骤 4：设置规划求解的各项参数并求解。 在步骤 2 中完成的工作主要是表达各个变量之间的函数关系，构造线性方程 中左侧的函数表达式。 而方程右侧的约束关系则使用“规划求解”加载项进行表达， 并进行决策变量的最优解求解。 选择“工具”中的“规划求解”选项，参考图 1-3，设置求解此问题的规划求解的 各项参数。其中$E$12 为目标函数的因变量，$C$9：$D$9 为决策变量，$E$9： $E$11<= $F$9: $F$11 为约束条件。
4 x1 2 x2 480 。
因为一桶牛奶可加工成 3 公斤 A 产品或 4 公斤 B 产品，每天只能得到 50 桶 牛奶，所以原材的约束条件为：
x1 x 2 50 。又因为 1 号生产线每天至多只能加 3 4
工 100 公斤 A 产品，2 号生产线的加工能力没有限制，所以生产能力约束条件为
实验步骤 步骤 1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件 函数。 本生产计划是要解决如何安排 A、B 两种奶制品生产数量，才能获利最大， 因此目标变量为总利润，设为 y。决策变量为 A、B 两种奶制品生产数量，设 x1 和
x 2 分别为 A、B 奶制品的产量(公斤) ，显然 x1 和 x 2 必须大于等于 0。
图 1-6 敏感性报告
可变单元格$C$9 和$D$9 允许的增量为 8 和 2，允许的减量为 2. 667 和 4，说 明决策 变量可允许的变化范围分别为(60-2.667，60+8)和(120-4，120+2)。约束变 量单元格$E$9、$E$10 和$E$11 允许的增量和减量说明生产能力、工时和原材料 供应量可允许的变化范围分别是（ 60-40 ， 60+1030 ） 、 (480-80 ， 480+53.333) 和 (50-6.667，50+10)。 总产量的影子价格为 0，说明增加生产能力对总利润无影响；工时的阴影价 格为 2，说明每增加一个单位的工时，可以使总利润增加 2；原材料的阴影价格为 48，说明每增加一个单位的原材料供应量，可以使总利润增加 48。由此可看出原 材料供应量对总利润的影响较大。如果现在有多余资金可以增加人力和购买原材 料，这时应该优先考虑人力还是原材料，除了考虑阴影价格外，还应该考虑各自 的单位成本。阴影价格与单位成本的比值较大的资源就是应该优先考虑购买的资 源。另外，当资源限制发生改变，求解出新的最优解后，各资源的阴影价格也可 能会有相应的变化，这时应该重新生成规划求解报告进行分析。 “极限值报告”（见图 1-7）列出目标单元格$E$12 最大值为 3360，可变单元
图 1-5 运算结果报告
对于本例，可以发现所需工时和原材料牛奶的需求量都已达到限制值，而总 产量未达到限制值，说明生产能力还有剩余。因此，如果要提高总利润，必须增 加工时和原材料的可供量，若增加生产能力则对总利润无影响，相反生产能力可 以减少 40， 然能达到当前最大总利润。第 4、第 5 约束条件其实是两种产量的非 零约束，“状态”栏中“未到限制值”只说明两个决策变量的求解结果都满足大于 0 的条件。
x1 100 。根据上述分析，可以列出以下目标函数和约束条件:
max : y 24 x1 16 x 2 st : 4 x1 2 x 2 480 x1 x 2 50 3 4 x1 100 x1，x 2 0
步骤 2：根据目标函数和约束条件，在 Excel 中建立如图 1-1 所示的参考模 型。 …………………………..时间约束 …………………………..原料约束 …………………………..产能约束
图 1-7 实验思考
极限值报告
1、 “猛牛”奶制品厂如果另有 500 元的资金可投入到两种产品的生产中， 请问： 应该将这笔资金用于增加生产能力、人力还是原材料?并请详细说明理由。 2、如果要使 1 号生产线满负荷运转，则需要安排工人加班。请问：支付给加 班工人每小时的加班工资最多为多少?
实验 1-2 "猛牛"奶制品厂原料供应站的分布决策
实验目的 理解"选址问题"的规划模型； 掌握在 Excel 中构造 0-1 规划模型的方法； 掌握用“规划求解”工具正确求解选址问题的步骤。
实验环境 Microsoft Office Excel 2003（2007） ； 通过 Excel 中的“加载宏”加载“规划求解”工具。
实验要求 由于“猛牛”奶制品厂的产品在市场上畅销，为了有利于原料的及时获得和质 量控制，工厂决定对其 6 个原料供应站铺设管道输送牛奶，6 个供应站相互间的 距离如表所示。已知：1 号供应站离工厂的距离为 5 公里，每铺设 1 公里管道的 成本为人工费 30 万元、材料费 50 万元、其他费用 100 万元。请设计从 1 号供应 站开始铺设管道，把各供应站连接起来的铺设方案，使建设总成本最低。 表 1-1 各供应站间距离
图 1-3
规划求解参数对话框
图 1-4 规划求解后的结果
Leabharlann Baidu
步骤 5：分析求解结果，仔细阅读"运算结果报告"、"敏感性报告"与"极限 值报告"，并理解报告中的各项内容。 求解后发现：当 1 号生产线安排 60 公斤的生产量且 2 号生产线上安排 120 公斤的生产量时，“猛牛”公司的获利最大，达到 3360 元，见图 1-5。 在单击"确定"规划求解按钮前，可以选择 “运算结果报告”、“敏感性报告”与 “极限值报告”进行保存。报告中包含了许多对管理决策有用的信息，基于这些信 息，可以帮助管理人员挖掘资源利用潜力，达到节能增效的目的。 “运行结果报告”列出目标单元格$E$12 在规划求解之前的初始值是 3360；可 变单元格$C$9 和$D$9 在规划求解之前的初值都是 10，规划求解以后的终值分别 为 60 和 120。在该报告的约束区域中显示每个约束条件的公式，当前值和是否达 到限制值。
“敏感性报告”(见图 1-6)提供关于求解结果对目标函数和约束条件微小变化 的敏感性信息。本实验是线性模型，在规划求解时必须将“规划求解选项”中的“采 用线性规划”选项打上钩，那么“敏感性报告”中将包含缩减成本、阴影价格、目标 系数以及右侧约束区域。如果采用非线性规划，则“敏感性报告”中将包含缩减梯 度和拉格朗日乘数。
基础数据模块
线性规划优化模块
图 1-1 参考模型 在模型中，基础数据模块的所有数据全部为已知的原始数据，可以直接在单 元格中输入数据，他们是约束条件函数中确定决策变量 x1 和 x 2 系数的基本依据。 在优化模块中，除了决策变量、限额的取值可以采用直接输入数据录入的方 式进行表达，其他单位格的数据必须采用引入单元格的方法使用基础数据模块中 的数值，并运用函数，表达各种约束条件中的系数值，不能够直接填写（图 1-1 中灰色的部分） 。模型中各单元格的计算公式参见图 1-2。
实验 1 奶制品厂生产/销售的最优化决策
实验 1-1
“猛牛”奶制品厂生产计划制定
实验目的 理解最优化模型的概念； 掌握在 Excel 中构造线性规划模型的方法； 掌握用"规划求解"工具正确求解最优化问题的步骤。
实验环境 Microsoft Office Excel 2003（2007） ； 在 Excel 中加载"规划求解"工具。
格$C$9 和$D$9 的最优解分别为 60 和 1200。$C$9 的上、下限(60 和 0)分别表示 在满足约束条件和保持其他可变单元格 $D$9 的值不变的情况下，可变单元格 $C$9 可以取到的最大、最小值。同样，$D$9 的上、下限(120 和 0)分别表示在满 足约束条件和保持其他可变单元格 $C$9 的值不变的情况下，可变单元格$D$9 可以取到的最大、最小值。
供应站 1 2 3 4 5 2 1.3 3 2.1 0.9 4 0.9 1.8 2.6 5 0.7 1.2 1.0 0.8 6 1.8 2.6 2.5 1.6 0.9
实验步骤 步骤 1：分析问题，确定解决方案。 本问题中，线路的走向必须在满足每个供应站都连上的基础上，使得所选的
线路的总距离最短，这种要求在保证满足目标函数最优的条件下，选中几个变量 的问题属于“选址问题”，可用“0-1”规划的方法来求解。 步骤 2：在 Excel 中建立模型框架。 建立如图 1-8 所示的参考模型。该模型分为 4 部分：第 1 部分为各供应站之 间的距离 (系数矩阵)；第 2 部分为与系数矩阵相似的规划矩阵，该矩阵的取值只 有 0 和 1 两种可能；第 3 部分是单位“人工费”、“材料费”等成本要素的单位值计 算出的每公里成本（180=30 人工费+50 材料费+100 其他费用） ；第 4 部分为由系 数矩阵和规划矩阵运算后得到的所选线路的总距离，以及由每公里成本与总距离 的乘积，即铺设管道的总成本。
选择“规划求解加载项”，点击 “确定”。
“规划求解”加载项将出现在“数据”工具栏中。
实验要求 “猛牛”奶制品厂用牛奶为原料生产 A 和 B 两种奶制品。 一桶牛奶可以在 1 号 生产线上用 12 小时加工成 3 公斤 A 产品， 或者在 2 号生产线上用 8 小时加工成 4 公斤 B 产品。由于市场对 A、B 两种奶制品的需求非常旺盛，因此每天所生产的 A、B 两种奶制品都能全部出售。根据目前的市场情况，销售每公斤 A 产品可获 利 24 元，销售每公斤 B 产品可获利 16 元。“猛牛”奶制品厂每天能得到 50 桶牛 奶的供应量，每天工人的总劳动时间为 480 小时，并且 1 号生产线每天至多能够 加工 100 公斤 A 产品，2 号生产线的加工能力没有限制。请为该厂制定一个生产 计划。如何安排 A、B 两种奶制品的生产，能使每天获利最大？
例如， 我们使用第 10 行单元格进行时间约束表达。 实际量代表在目前决策变 量 x1 和 x 2 取值条件下，不同产品生产总共消耗的时间。其取值为 60，60 这个值 不能够直接输入数值，必须使用函数表达“=SUM（C10:D10）”，以表示总生产和 特定品种生产量之间的数学关系。该函数表达的含义是，生产需要消耗的时间为 A、B 两种奶制品生产加工时间之和。其中，奶制品 A 在现有产量下的生产时间 为 40，40 这个值同样不能够直接输入，需要使用函数表达“=C9/C3*C4”，其含义 表达为“生产 10 公斤奶制品 A 需要消耗的时间”。 再一次强调，在线性规划优化模块中，呈灰色的单元格中的数据全部都是使 用函数进行表达，不能过直接输入数据。 在图 1-1 中，决策变量 x1 和 x 2 所在单位格为 C9、D10，其初始值可以随意设 置，直接填写。在本例中我们直接填写为 10。在使用“规划求解”加载项进行求解 后，决策变量所在单元格的数值会自动发生变化。 步骤 3：参考图 1-2，检查模型中各单元格的计算公式。
实验准备 （1）使用 Microsoft Office Excel 2003 加载规划求解工具 “工具”→“加载宏”→“规划求解”
（2）使用 Microsoft Office Excel 2007 加载规划求解工具
新建 Excel 文件，点击
，选择 Excel 加载项。
点击“加载项”，选择“规划求解加载项”，点击 “转到”。
因为每公斤 A 产品可获利 24 元，每公斤 B 产品可获利 16 元，那么目标函数 即为 y 24 x1 16 x2 。 由于 1 号生产线可用 12 小时加工成 3 公斤 A 产品，因此生产每公斤 A 产品 所需要的时间为 4 小时；同理，2 号生产线生产每公斤 B 产品所需要的时间为 2 小 时 ， 生 产 两 种 产 品 的 总 时 间 不 能 超 过 480 小 时 ， 工 时 的 约 束 条 件 为
图 1-2 参考模型中的公式 步骤 4：设置规划求解的各项参数并求解。 在步骤 2 中完成的工作主要是表达各个变量之间的函数关系，构造线性方程 中左侧的函数表达式。 而方程右侧的约束关系则使用“规划求解”加载项进行表达， 并进行决策变量的最优解求解。 选择“工具”中的“规划求解”选项，参考图 1-3，设置求解此问题的规划求解的 各项参数。其中$E$12 为目标函数的因变量，$C$9：$D$9 为决策变量，$E$9： $E$11<= $F$9: $F$11 为约束条件。
4 x1 2 x2 480 。
因为一桶牛奶可加工成 3 公斤 A 产品或 4 公斤 B 产品，每天只能得到 50 桶 牛奶，所以原材的约束条件为：
x1 x 2 50 。又因为 1 号生产线每天至多只能加 3 4
工 100 公斤 A 产品，2 号生产线的加工能力没有限制，所以生产能力约束条件为
实验步骤 步骤 1：分析问题，整理出决策变量、目标变量、目标函数和各项约束条件 函数。 本生产计划是要解决如何安排 A、B 两种奶制品生产数量，才能获利最大， 因此目标变量为总利润，设为 y。决策变量为 A、B 两种奶制品生产数量，设 x1 和
x 2 分别为 A、B 奶制品的产量(公斤) ，显然 x1 和 x 2 必须大于等于 0。
图 1-6 敏感性报告
可变单元格$C$9 和$D$9 允许的增量为 8 和 2，允许的减量为 2. 667 和 4，说 明决策 变量可允许的变化范围分别为(60-2.667，60+8)和(120-4，120+2)。约束变 量单元格$E$9、$E$10 和$E$11 允许的增量和减量说明生产能力、工时和原材料 供应量可允许的变化范围分别是（ 60-40 ， 60+1030 ） 、 (480-80 ， 480+53.333) 和 (50-6.667，50+10)。 总产量的影子价格为 0，说明增加生产能力对总利润无影响；工时的阴影价 格为 2，说明每增加一个单位的工时，可以使总利润增加 2；原材料的阴影价格为 48，说明每增加一个单位的原材料供应量，可以使总利润增加 48。由此可看出原 材料供应量对总利润的影响较大。如果现在有多余资金可以增加人力和购买原材 料，这时应该优先考虑人力还是原材料，除了考虑阴影价格外，还应该考虑各自 的单位成本。阴影价格与单位成本的比值较大的资源就是应该优先考虑购买的资 源。另外，当资源限制发生改变，求解出新的最优解后，各资源的阴影价格也可 能会有相应的变化，这时应该重新生成规划求解报告进行分析。 “极限值报告”（见图 1-7）列出目标单元格$E$12 最大值为 3360，可变单元
图 1-5 运算结果报告
对于本例，可以发现所需工时和原材料牛奶的需求量都已达到限制值，而总 产量未达到限制值，说明生产能力还有剩余。因此，如果要提高总利润，必须增 加工时和原材料的可供量，若增加生产能力则对总利润无影响，相反生产能力可 以减少 40， 然能达到当前最大总利润。第 4、第 5 约束条件其实是两种产量的非 零约束，“状态”栏中“未到限制值”只说明两个决策变量的求解结果都满足大于 0 的条件。
x1 100 。根据上述分析，可以列出以下目标函数和约束条件:
max : y 24 x1 16 x 2 st : 4 x1 2 x 2 480 x1 x 2 50 3 4 x1 100 x1，x 2 0
步骤 2：根据目标函数和约束条件，在 Excel 中建立如图 1-1 所示的参考模 型。 …………………………..时间约束 …………………………..原料约束 …………………………..产能约束
图 1-7 实验思考
极限值报告
1、 “猛牛”奶制品厂如果另有 500 元的资金可投入到两种产品的生产中， 请问： 应该将这笔资金用于增加生产能力、人力还是原材料?并请详细说明理由。 2、如果要使 1 号生产线满负荷运转，则需要安排工人加班。请问：支付给加 班工人每小时的加班工资最多为多少?
实验 1-2 "猛牛"奶制品厂原料供应站的分布决策
实验目的 理解"选址问题"的规划模型； 掌握在 Excel 中构造 0-1 规划模型的方法； 掌握用“规划求解”工具正确求解选址问题的步骤。
实验环境 Microsoft Office Excel 2003（2007） ； 通过 Excel 中的“加载宏”加载“规划求解”工具。
实验要求 由于“猛牛”奶制品厂的产品在市场上畅销，为了有利于原料的及时获得和质 量控制，工厂决定对其 6 个原料供应站铺设管道输送牛奶，6 个供应站相互间的 距离如表所示。已知：1 号供应站离工厂的距离为 5 公里，每铺设 1 公里管道的 成本为人工费 30 万元、材料费 50 万元、其他费用 100 万元。请设计从 1 号供应 站开始铺设管道，把各供应站连接起来的铺设方案，使建设总成本最低。 表 1-1 各供应站间距离
图 1-3
规划求解参数对话框
图 1-4 规划求解后的结果
Leabharlann Baidu
步骤 5：分析求解结果，仔细阅读"运算结果报告"、"敏感性报告"与"极限 值报告"，并理解报告中的各项内容。 求解后发现：当 1 号生产线安排 60 公斤的生产量且 2 号生产线上安排 120 公斤的生产量时，“猛牛”公司的获利最大，达到 3360 元，见图 1-5。 在单击"确定"规划求解按钮前，可以选择 “运算结果报告”、“敏感性报告”与 “极限值报告”进行保存。报告中包含了许多对管理决策有用的信息，基于这些信 息，可以帮助管理人员挖掘资源利用潜力，达到节能增效的目的。 “运行结果报告”列出目标单元格$E$12 在规划求解之前的初始值是 3360；可 变单元格$C$9 和$D$9 在规划求解之前的初值都是 10，规划求解以后的终值分别 为 60 和 120。在该报告的约束区域中显示每个约束条件的公式，当前值和是否达 到限制值。