Excel的线性规划问题

Excel教程 2021-11-13 11:54:36

问题分析：

这个问题，在数学上属于线性规划（根据一定的约束条件，列出某个多元一次方程的解集）问题，可以在Excel中用“规划求解”工具解答。线性规划的数学原理以及用Excel解答该问题的方法在人教新课标高中数学教材中有讲解，此处不再赘述，只是简要介绍一下操作步骤。

本方法适用于Excel 97~2016。

另外，题主问什么比例，这个问法不完善，应该问在指定范围内每种产品的出货量。当然，这个问题存在无解的可能。

问题解答：

1. 为Excel添加规划求解工具。

Excel 2010~2016：点击文件→选项→加载项，在“选项”对话框底部找到“管理”，将其设为“Excel加载项”，然后点击“转到”；在“加载项”对话框中勾选“规划求解加载项”，点击“确定”。

Excel 2007：点击Office按钮→选项→加载项，在“选项”对话框底部找到“管理”，将其设为“Excel加载项”，然后点击“转到”；在“加载项”对话框中勾选“规划求解加载项”，点击“确定”。

Excel 97~2003：点击工具→加载宏，在“加载宏”对话框中勾选“规划求解加载项”，点击“确定”。

2. 录入数据

在Excel中新建一个工作簿，输入如图所示的内容。商品1~商品3要打三折，其他商品打四五折。

我们要计算每种产品的出货量，让总计出货金额（E10单元格）恰好等于原价的四折（E12单元格）。

3. 规划求解

首先，要调出规划求解工具。

Excel 2007~2016：点击数据→分析→规划求解。弹出“规划求解参数”对话框。如上图。

Excel 97~2003：点击工具→规划求解。弹出“规划求解参数”对话框。如上图。

然后，把目标单元格设为$E$10。在此处有三个选项：最大值、最小值、值为X。要根据实际情况选择，笔者选了最大值。

第三，把可变单元格设为$D$2:$D$9，也就是所有产品的出货量。因为我们要让电脑一个值一个值地去尝试，所以才称为可变单元格。

第四，添加约束条件。点击“添加”按钮，弹出“添加约束”对话框。

（1）出货量不能低于1，所以把单元格引用位置设为$D$2:$D$9，中间的连接符号设为>=，约束值设为1。点击“确定”。

（2）出货量必须是整数，所以把单元格引用位置设为$D$2:$D$9，中间的连接符号设为int，约束值自动变成“整数”。点击“确定”。

（3）再添加两个约束条件：$D$2:$D$9 <= 50 （每款商品最多出货50件），$E$10 = $E$12 （出货金额合计为原价打四折）

第五，在约束条件添加完毕后，点击“求解”按钮。Excel开始进入计算状态，计算出结果以后会弹出一个对话框报告找到一解，并自动将工作表中的可变单元格和目标单元格修改成计算出的结果。另外你还可以查看运算报告。点击“确定”即可。

案例源文件下载：

http://pan.baidu.com/s/1o7OEy7K

密码: v2mf