编按：

一个工厂生产多种产品，一个销售企业进销多种商品。那么在实际条件下，什么样的产品生产组合或者进销组合会带来最大的利润呢？这种已知多种限制条件，求符合某种目标的产品数量，在Excel中被称为规划求解。

如何合理的运用有限的物力、财力、人力等资源，得到最佳的经济效果？

我们可以用Excel的规划求解来组合产品以实现利润最大化。下面举个例子。

江南皮革厂生产三种产品，皮鞋、皮手套、皮帽。三种产品需要原材料甲、乙、丙。近期，原材料供应有限制，生产工时也有限制。已知产品单件的用时、用料、利润，求如何组合产品利润最大。

一、加载规划求解工具

规划求解工具位于“数据”菜单下。如果没有，则可以按下方的步骤进行加载。

1.单击“文件”>“选项”命令，弹出“Excel选项”对话框，选择“加载项”选项。

2.单击下方“转到”按钮，弹出“加载宏”对话框，在“可用加载宏”列表框中勾选“规划求解”加载项，单击“确定”按钮。

经过前面的操作，即可在“数据”选项卡的“分析”组中找到“规划求解”功能。

二、设置目标的计算公式

目标是总利润最大。总利润等于各产品的产量乘以单件利润。选择B9单元格，输入公式“=SUMPRODUCT(B7:D7,B8:D8)”，返回两个区域对应数值的乘积的和，即得到总利润。

三、规划求解

1．设置目标和变量。选择B9单元格，单击“数据”>“分析”>“规划求解”按钮，在弹出的“规划求解参数”对话框中，设置目标为“最大值”。（因为我们需要利润最大化。）设置“通过更改可变单元格”为“$B$8:$D$8”（生产量数值）。

2．设置约束条件。单击“添加”按钮，在弹出的“添加约束”对话框，设置材料甲的约束条件，在“单元格引用”参数框中输入“$E$3”，将“<=”更改为“>=”符号，在“约束”参数框中输入“$B$8*$B$3+$C$8*$C$3+$D$8*$D$3”。

用同样的方法添加材料乙、材料丙的约束条件。

材料乙：$E$4>=$B$8*$B$4+$C$8*$C$4+$D$8*$D$4

材料丙：$E$5>=$B$8*$B$5+$C$8*$C$5+$D$8*$D$5

3．增加产量为整数的条件。再次单击“添加”按钮，设置皮鞋、皮手套、皮帽的数量为整数的条件，在“单元格引用”参数框中输入“$B$6:$B$15”，在运算符下拉列表中选择“int”。

4．由于产品数量为非负数，勾选“使无约束变量为非负数”复选框；在“选择求解方法”下拉列表框中选择“单纯线性规划”。

5．得出答案。单击“求解”按钮，弹出“规划求解结果”对话框，单击“确定”按钮。

经过前面的操作，即可计算出每天生产皮鞋17件、皮手套50件、皮帽72件，能够实现利润最大化，总利润为5430元。

四、规划求解报告

规划求解会生成报告。再次执行“规划求解”，单击“求解”>“运算结果报告”>“确定”，即可得到报告。报告中清楚看到目标单元格、可变单元格及约束条件，以及是否达到条件限制。

本文配套的练习课件请加入QQ群：264539405下载。

做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！

扫下方二维码关注公众号，可随时随地学习Excel：

相关推荐：

加了*的 SUMPRODUCT函数无所不能

excel单变量求解原理以及三个excel单变量求解案例

Excel进阶之路必学函数：动态统计之王——OFFSET（上篇）

Excel进阶之路必学函数：动态统计之王——OFFSET（下篇）