Excel规划求解：[3]解简单的线性规划应用题

来源：网络收集点击：时间：2024-03-01

【导读】：

Excel的规划求解工具有着很强大的功能，可以帮助我们解决线性规划问题，运筹学方面等实际问题。本系列经验将以Excel2010为例详细的介绍规划求解的在各方面的应用。上一篇经验讲解了高中数学所涉及到的简单的线性规划如何使用“规划求解”功能进行求解。本篇经验将开始使用“规划求解”工具解决实际问题，从简单的应用题为例开始讲解“线性求解”工具在实际问题里的运用。工具/原料moreExcel方法/步骤1/10分步阅读

某工厂要在A、B、C三条流水线上生产甲、乙两种新产品。经测算，每生产一单位甲产品需要占用流水线A的1工时，占用流水线B的3工时；每生产一单位乙产品需要占用流水线B的2工时，占用流水线C的2工时。而流水线A、B、C每天可用于生产这两种新产品的时间分别是4工时、18工时、12工时。已知一单位的甲产品的利润为300元，一单位的乙产品的利润为500元。问工厂应当如何安排这两种新产品的生产计划，以获得最大的利润？

2/10

这个问题只有两个变量，三个约束，可以较容易的列出约束条件，通过在平面直角坐标系中画图求出最大利润点。如果当新产品（变量）较多时，要这样计算就会非常的困难。

对于变量和约束条件较少的问题，在使用“规划求解”工具计算时可以不用设出变量和列出约束条件，直接制作表格进行计算即可。

3/10

首先我们还是需要设出生产产品甲、产品乙分别为x单位、y单位。

对于需要求的最大值（即每日总利润）可表示为z=300x+500y。

接下来列出约束条件。第一个约束条件是流水线A每日可用工时的限制。即每种产品需要占用流水线A的时间乘该产品的产量，最后加起来必须小于等于流水线A每日可用的工时。因此列式为x≤4（1x+0y≤4）。

同理，第二个约束条件应为3x+2y≤18，第三个约束条件应为2y≤12（0x+2y≤12）。

实际上还有一个非负的约束条件，只是在计算过程中很少用上，容易被忽略。即x≥0，y≥0。

4/10

接下来就需要在Excel中建立模型，以使用“规划求解”工具进行计算。建立表格如图，与上一篇经验一样，为了便于理解，分别将已知条件，变量，目标值分别用蓝色、橙色、绿色填充。

5/10

现在需要在F3:F5区域补齐约束条件，即分别录入约束条件中前三个不等式中不等号左边的内容。以F3单元格为例，约束条件是x≤4（1x+0y≤4），就要在F3单元格内录入“1x+0y”。可以看到D3和E3单元格的数据分别是“1”和“0”，而x和y所对应的单元格是D7和E7。这里使用对应相乘后求和的SUMPRODUCT函数，在F3单元格输入公式“=SUMPRODUCT(D3:E3,D7:E7)”，即D3*D7+E3*E7。

6/10

对于F4和F5单元格，可以使用F3单元格直接向下拖动填充。但是必须要注意的是，D7:E7区域需要添加绝对引用，避免拖动填充的时候，这个区域随拖动而变动。因此将F3单元格的公式改为“=SUMPRODUCT(D3:E3,$D$7:$E$7)”后对下面的单元格进行填充。

7/10

还需要输入公式的是目标值H9单元格，对于目标函数z=300x+500y，需要在H9单元格输入“300x+500y”部分。D9和E9单元格的数据分别是“300”和“500”，变量x和y的值分别在D7和D9单元格，依然使用SUMPRODUCT函数，在H9单元格输入公式“=SUMPRODUCT(D7:E7,D9:E9)”。