（二)线性规划的建模与应用 实验目的：使用Excel软件求解各种线性规划问题。 实验内容： (1)用Exce1软件求解本章P44页例13： (2)用Excel软件求解本章P45页例14： 操作步豫： (1)建立电子表格模型： (2)使用Excel规划求解工具求解问题： (3)结果分析。 例1-13 操作步骤： (1)建立电子表格模型： AB 0 例1.13 产品售价3.4 285 2.25 如工费0.5 0.4 0.3 各原料用于各糖果的数量 7 甲 丙 实际使用 原科成本 A -Sc8:E8) 台2000 上SIc9:E9) e2500 1.5 =50MC10:810)←1200 产品总量=SC8:C10) =sM①8：D10)=SE8:E10) 13 规格瑟求 混合比例 14 甲，原料4=C8 z=14C110.6 甲，原制C=C10 ←==150110.2 1 总售价 =SUNPRODUCT(C3:E3,C11:E11) =SUMPRODUCT (C4:E4.C11:E11) 乙，原4D8 为=I17D110.3 总原科成本=SUC0DUCT8:J10,F8:F10) 乙，原林D10 ←=I18*D110.5 总利润 -C16-C17-C18 丙，原种CE10 ←=I20*E110.6
（二）线性规划的建模与应用 实验目的: 使用 Excel 软件求解各种线性规划问题。 实验内容: （1） 用 Excel 软件求解本章 P44 页例 13； （2） 用 Excel 软件求解本章 P45 页例 14。 操作步骤： （1）建立电子表格模型； （2）使用 Excel 规划求解工具求解问题； （3）结果分析。 例 1-13 操作步骤： （1）建立电子表格模型：
(2)使用Excel规划求解工具求解问题，如图所示： 提求程参敏 设置目标单元格)：如20国 求解) 等于：⊙最大值四)○最小值m)O值为0可 关闭 可支单元格 C8:$E10 国推测) 选项@)□ 约束D 添加 更改心) 全部重设⑧) 册除) 帮助) 100秒 0001 吕显代结果，" (3)结果分析： B D G 1.13 7 产品售价 3.4 2.85 2.25 加工 0.5 0.4 0.3 各原科用于各糖果的数量 7 实际使用 原科成本 580 1420 86.6666667 -2.84228-14 2000 =2000 1. 产品总量966.66666674733-2.8422E-14 2345 甲，原料 混 甲，原c =193.333320% 6718 总售价 16776.6666 2376.60609 1420 1420 8960 200 总利润 5450 丙，原料 0 -1.7E-1460%
（2）使用 Excel 规划求解工具求解问题，如图所示： （3） 结果分析：
例1-14 操作步骤： (1)建立电子表格模型： BC D E F 例-14 1234567 项目1项目 项目3项目 回服率0.20.5 0.4 母年名 项目的投资 海年初各项目收回的本利 项目1项目2项目3项目4总领 可投资额 项 项目2 项目3 项目4 =SUM(C7:F7 300000 =3UM(C89F8 =S叮8：M8 =C7*(1+C3 910 第三年 =SUM(C9:F9) =S0109:9 C8*(1+C3)=D7*(1+3) 第四年 最大本利和=SUI010:M10)=C9*(1+C3 =E8(1+E3)=F9(1+F3} 1 15000020000100000 (2)使用Excel规划求解工具求解问题，如图所示： 规划求解李数 设置目标单元格g):$红$10国 求解你) 等于：⊙最大值圆)O最小值mO值为00 关闭 可变单元格) 507:309,$知7，$8，$轻39 国推测] 选项0) 约束D 添加公 更改C) 全部重设) 册除)] 帮助0 至 保存型 0001 吕 名三格 营粉巴 营品
例 1-14 操作步骤： （1）建立电子表格模型： （2）使用 Excel 规划求解工具求解问题，如图所示：
(3)结果分析： DEF■ G H T L 例1-14 项目1 项目2项目3项目4 3 回报率 20 50%60X 40% 每年 领目3项目4 年 总投资额 项目1项目 项目 项目4 第-年166666.6667133333 8 第二年 200000 200000 200000 200000 9 第三年 100000 100000200000 200000 200000 四年 最大本利和 580000 120000 320000140000 投资限 150000200000100000 作业练习： (1)使用WinQSB软件完成上面两题。 (2)用Excl建立下述排班问题的电子模型，并求解： 某航空公司正准备增加其中心机场的往来航班，因此需要雇佣更多的服务 人员。不同时段有最少需要服务人员数，有5种排班方式，每8小时为一班。 5种排班方式 排班1：6AM~2PM,即早上6点上班；排班2：8AM~4PW,即早上8点上班： 排班3：中午~8PWM,即中午12点上班：排班4：4PWM~午夜，即下午4点上班： 排班5：10PM~6M,即晚上10点上班。 时段 排班1排班2排班3排班4排班5最少需要人数 6AM~8AM 48 8AM~10AM 79 10AM~中午 65 中午~2PM 87 2PM~4PM 64 4PM-6PM 73 6PM~8PM 82 8PM~10PM 43 10PM~午夜 52 午夜6PM 15 每人每天工资（元》 170 160 175 180 195
（3）结果分析： 作业练习： （1）使用 WinQSB 软件完成上面两题。 （2）用 Excel 建立下述排班问题的电子模型，并求解： 某航空公司正准备增加其中心机场的往来航班，因此需要雇佣更多的服务 人员。不同时段有最少需要服务人员数，有 5 种排班方式，每 8 小时为一班。 5 种排班方式 排班 1：6AM～2PM，即早上 6 点上班； 排班 2：8AM～4PM，即早上 8 点上班； 排班 3：中午～8PM，即中午 12 点上班；排班 4：4PM～午夜，即下午 4 点上班； 排班 5：10PM～6M，即晚上 10 点上班。 时段 排班 1 排班 2 排班 3 排班 4 排班 5 最少需要人数 6AM～8AM √ 48 8AM～10AM √ √ 79 10AM～中午 √ √ 65 中午～2PM √ √ √ 87 2PM～4PM √ √ 64 4PM～6PM √ √ 73 6PM～8PM √ √ 82 8PM～10PM √ 43 10PM～午夜 √ √ 52 午夜～6PM √ 15 每人每天工资(元) 170 160 175 180 195
问题：如何规划每个轮班人员才能以最小的成本提供令人满意的服务？ 解：这是一个纯成本收益平衡问题。 (1)决策变量 本问题的决策是不同排班的人数。 设：xi为排班i的人数(i=1,2,5) (2)目标函数 本问题的目标是人员总费用（工资）最少，即 Minz=170x+160x2+175x,+180x+195x (3)约束条件 ①每个时段的在岗人数必须不少于最低可接受水平（最少需要人数） ②非负 数学模型（线性规划模型） Minz=170x,+160x2+175x3+180x4+195x x1≥48 x1+x2≥79 +x2≥65 x1+x2+x3≥87 x2+x3≥64 s.t. x3+x4≥73 x3+x4≥82 x4≥43 x4+x5≥52 x5≥15 1x,≥0(i=1,2,3,4,5) 在Excel中建立的模型如下
问题：如何规划每个轮班人员才能以最小的成本提供令人满意的服务？ 解:这是一个纯成本收益平衡问题。 （1）决策变量 本问题的决策是不同排班的人数。 设：xi 为排班 i 的人数 (i＝1,2,,5) （2）目标函数 本问题的目标是人员总费用（工资）最少，即 （3）约束条件 ① 每个时段的在岗人数必须不少于最低可接受水平（最少需要人数） ② 非负 数学模型（线性规划模型） 在 Excel 中建立的模型如下： Min z 170 160 175 180 195 1 2 3 4 5 x x x x x 1 2 3 4 5 1 1 2 1 2 1 2 3 2 3 3 4 3 4 4 4 5 5 Min z 170 160 175 180 195 48 79 65 87 64 s.t. 73 82 43 52 15 0 ( 1,2,3,4,5) i x x x x x x x x x x x x x x x x x x x x x x x x i
B H 2345 单位成本 时段 是在岗（1表示在岗) 最少人数 =48 7890 SUMPRODUCT (C10:G10,C$19:GS19) Se0DC12:G12,C519:G19 2315578 73 早6点糖早8点班中午班下午点班晚10点 总人数 成 每班人 SU(C19:G19) -SUMPRODUCT (C4:G4,C19:G19) 20 规划求解参数 设置目标单元格):$打$19国 求解S) 等于：O最大值M)⊙最小值即O值为)0 关闭 可变单元格®) t1931 国 推测) 洗项0) 体7：gE16a$J7:$J$16 添加) 更改) 全部重设⑧) 册除血) 帮助D 规划求解选项 量长运算时间①) 100秒 定 选代次数) 100 取消 精度) .000001 结入桓利在). 允许误差您) 5 保存模型)， 收敛度) L0001 帮助☐ ☑采用钱性根型侧) ■目动按比例 缩放 ☑假定非负) ■温示选代结果B) 8 O向前差分尔) O中心差分C) 。共轭法Q)
结果如下 B 单位成本 时段 是舌在肉(1表示注肉) 在人数 2p 18190 班人数 早点班早9点中午下午点10
结果如下：