线性规划实验-Excel求解

用Excel求解线性规划问题实验
（实验题目在最后）
一、Excel函数
使用Excel求解线性规划问题时，SUMPRODUCT函数可以大大降低资料录入工作量，提高工作效率。

计算数组或向量的乘积时，使用SUMPRODUCT 函数，格式如下：
SUMPRODUCT(数组1,数组2,…,数组n)
其中2≤n≤30，即最多可以使用30个数组参数，返回值为n个数组对应元素乘积之和。

以图1为例，在单元格D1中输入公式
=SUMPRODUCT(A1:B1,A2:B2,A3:B3)
得到111（相当于A1*A2*A3 + B1*B2*B3 = 1*2*3 + 3*5*7 = 111）。

在单元格D2中输入公式
=SUMPRODUCT(A1:C1,A2:C2)
得到53(相当于A1*A2 + B1*B2 + C1*C2 = 1*2 + 3*5 + 4*9 = 53)。

图1. 乘积和(SUMPRODUCT函数)计算结果
1
1
二、求解实例
1. 问题描述与模型建立
某玩具厂生产猫和龟两种玩具，制造一个玩具猫可获利30元，制造一个玩具龟可获利20元。

制造一个猫需要2小时机工和1小时手工；制造一个龟需要1小时机工和1小时手工。

在一周内，机工不能超过100h ，手工不能超过80h ，猫的产量不能超过45个。

求产品的最佳生产量和最大利润。

设1x 为一周内猫的生产量，2x 为一周内龟的生产量。

可建立如下线性规划模
型：
⎪⎩⎪⎨⎧≤≤+≤++=4580
10022030max 1
21212
1x x x x x x x g
2. 数据录入
（1）启动Excel ，建立如图2所示的Excel 工作表，输入系数矩阵A 到区域C2：D4；输入约束常数b 到区域F2：F4；输入目标系数到区域C5：D5。

（2）指定单元格C6和D6存储变量1x 和2x 的值，称之为可变单元格。

在
可变单元格中输入数字1表示给定初始值121==x x ，但并非一定这样；若这两个单元格不输入内容，Excel 将按0处理，不影响求解。

（3）在有关单元格输入计算公式：
单元格公式
E2 =SUMPRODUCT(C2:D2,C6:D6)
E3 =SUMPRODUCT(C3:D3,C6:D6)
E4 =SUMPRODUCT(C4:D4,C6:D6)
C7 =SUMPRODUCT(C5:D5,C6:D6)
单元格E2，E3，E4分表储存3个约束方程左端的函数表达式；单元格C7储存目标函数表达式。

图2中的文字是为了使问题表达更清楚，并非必要。

图2. 规划求解数据输入格式
3. 求解操作
延续图2,求最优解的步骤如下：
（1）选择“工具”、“规划求解”，显示画面如图3所示。

（2）在“设置目标单元格”输入框内输入C7。

1
1
图3. “规划求解参数”对话框
（3）选定“最大值”选项（用鼠标左键点击其圆形复选框）。

（4）在“可变单元格”输入框内输入C6:D6。

（5）点击“添加”按钮，输入约束条件，画面显示如图4。

图4. “添加约束”对话框
（6）在“单元格引用位置”输入框内输入E2:E4，指明约束条件左端函数的存放位置。

（7）选定运算符号 < = 。

（8）在“约束值”输入框内输入F2:F4，指明约束条件右端常数的存放位置。

（9）点击“添加”按钮，添加约束条件E2:E4<=F2:F4（相当于3
个约束
条件E2<=F2，E3<=F3，E4<=F4）。

（10）点击“取消”按钮，回到图3所示的“规划求解参数”对话框。

接下来，需要输入变量非负约束。

有如下两种方法：
（1）点击图3中“选项”按钮，显示“规划求解选项”对话框，画面如图5所示。

在图5中，用鼠标左键点击“假定非负”前面的复选框，打上复选标记；而后点击“取消”按钮，返回图3所示的“规划求解参数”对话框。

（2）点击“添加”按钮，显示“添加约束”对话框，画面显示如图6。

在“单元格引用位置”输入框内输入C6:D6；在“约束值”输入框内输入0；选定运算符号> = ；点击“确定”按钮添加变量非负信息；最后点击“取消”按钮，返回图3所示的“规划求解参数”对话框。

这是，在“约束”对话框内增加了$C$6:$D$6 >= 0的约束条件。

到此为止，已完成“原始数据”和“规划求解参数”的全部输入和选择工作。

只要点击图3所示的“规划求解参数”对话框中“求解”按钮，即可达到“求最优解”的目的。

图5. “规划求解选项”对话框
1
图6. “添加约束”对话框
图7. 求解后“变量取值”、及“目标函数值”的变化此时的图2变为图7；并出现“规划求解结果”对话框如图8。

用鼠标左键点击图8中的“计算结果报告”，而后点击“确定”按钮，将给出图9所示的计算结果报告。

图8. “规划求解结果”对话框
1
图9. 计算结果报告
4. 几点说明
（1）Excel可以对数据进行智能化处理。

在上述求解过程中，原本有3个约束条件，但仅输入1个约束条件E2:E4<=F2:F4。

这相当于3个约束条件E2<=F2，E3<=F3和E4<=F4，即E2～E4的3个单元格的内容分别<=F2～F4的3个单元格的内容。

（2）对“设置目标单元格”、“可变单元格”、“单元格引用位置”和“约束值”对话框，可通过“鼠标点击和拖拉”进行输入。

例如，在“设置目标单元格”对话框中输入C7,可用鼠标点击C7单元格；在“可变单元格”对话框中输入C6:D6，可先将鼠标指针移到C6单元格，再按下鼠标左键并移动指针到D6单元格后松开鼠标。

（3）使用Excel求解线性规划问题时，对约束条件提供了“<=”、“=”和“>=”选项，不必对模型标准化。

二、实验题目
1
1
1. 求上述求解实例的对偶规划的最优解。

2. 丽莎为了健康，打算每天从柳丁、木瓜、香蕉、胡萝卜4种水果中获取维生素A 、B 、C 、D 。

假设柳丁每个20元，木瓜每个30元，香蕉每根15元，胡萝卜每根25元。

每天她最少必需摄取500单位维生素A, 250单位维生素B, 600单位维生素C, 以及300单位维生素D,各水果所含维生素及价格如表1。

表1. 水果价格及含维生素含量
试求花费最少，又能满足各种维生素摄取量的最佳解。

3. 某工厂生产1A ，2A 两种产品。

已知生产1A 一万瓶要用原料5公斤1B ，300公斤2B ，12公斤3B ，可获利润8000元；生产2A 一万瓶要用原料3公斤1B ，80公斤2B ，4公斤3B ，可获利润3000元。

该厂现有原料500公斤1B ，20000公斤2B ，900公斤3B 。

问生产1A ，2A 各多少，才能获得最大利润？
4. 某厂用1A ，2A 两台机床，加工1B ，2B ，3B 三种不同零件。

已知在一个生产周期内1A 只能工作80机时，2A 只能工作100机时。

一个生产周期计划加工70个零件1B ，50个2B ，20个3B 。

两台机床加工每个零件的时间和每个零件的成本，分别如表2和表3,问如何安排加工任务，才能使加工成本最低？
旗开得胜表2. 加工每个零件所需时间表（单位：机时∕个）
表3. 加工每个零件成本表（单位：元∕个）
三、实验要求
至少要完成其中的两个实验题目，写出实验报告。

1。