|
之前的分享（Excel中单变量求解的用途和使用方法）中介绍的单变量求解一样，规划求解也是假设分析的一种，它根据约束条件，求达成目标单元格的变量值。单变量求解的可变单元格即变量只能是单个单元格即一个，而规划求解的可变单元格可以是单个单元格，也可以是单元格区域等，即可以同时求解多个变量值。 在演示之前，先在电脑中找到规划求解工具，它是Excel的一个插件，需要加载，步骤如下： 点击工作表左上角OFFICE按钮，在弹出的菜单中点击右下角的Excel选项。
在“Excel选项”对话框中，左侧选择“加载项”，然后点击右侧下方“转到”按钮。
在弹出的“加载宏”对话框中勾选“规划求解加载项”，确认之后功能区便添加了“规划求解”工具。
我们先用一个凑数求和的例子来说明规划求解的使用方法。譬如从单元格A2至A15挑出合计等于5000的数字。
规划求解有三个参数：目标值、变量和约束条件。目标值和变量需要在工作表中有存储地址，即目标单元格和可变单元格；约束条件是指对目标值和变量的限制，包括“<=”、“>=”、“=”、“Int”（整数）和“Bin”（二进制数，即0和1）。 同时，规划求解有两个基本规则：一是目标单元格必须包含公式；二是可变单元格必须直接或者间接与目标单元格相关。 现在来整理一下“凑数求和”的思路，挑出附合条件的数字即要给数字做标记，因此我们将B列设置为标记列，也是可变单元格，通过在此列填充数字“1”或者“0”，标记A列相应数字是否为凑数求和选中的数字。
合计5000是目标，即标记为1的数字合计5000，我们将此目标体现在单元格C2，键入公式“=SUMPRODUCT(A2:A15,B2:B15)”，SUMPRODUCT函数返回数组乘积的和，返回A1*B1+A2*B2+……+A5*B15，因为B列均为“0”和“1”，因此将返回被选中数字的和。
数据表格即目标单元格和公式、可变单元格和约束条件设置好之后，才能有效使用“规划求解”工具。点击“数据”选项卡，点击刚刚加载的“规划求解”按钮。
弹出“规划求解参数”对话框。 “目标单元格”参数选择单元格C2；目标值勾选“值为”并在输入框键入“5000”；如果是求解“最大值”和“最小值”则不需要键入具体目标值。 “可变单元格”参数选择“B2:B15”。
点击“约束”右侧“添加”按钮，弹出“添加约束”对话框，第一个框选择单元格B2:B15，第二个框选择“bin”，即为二进制数，只能是“0”或者“1”，“凑数求和”只需要这一个约束条件。如果还有其它约束条件，点击“添加”按钮继续添加即可。
约束条件添加完毕之后，点击“确认”按钮，返回“规划求解参数”对话框，可以看见刚才录入的约束条件已经添加。
规划求解的参数设置完成之后，点击右上角的“求解”按钮。系统计算之后，会跳出一个“规划求解结果”的通知窗口，你可以选择“保留规划求解结果”或者“恢复为原值”即工作表不显示结果。
选择“保留规划求解结果”，并确定，答案出来了，标记列自动填充了“0”或者“1”，单元格C2仍然是公式，计算结果5000。是不是要比逐一数字去试快捷得多。
以上便是一个规划求解的完整过程，下面举几个实例进一步说明规划求解的用途。 1、利润最大化 譬如：某工厂某车间一台机器同时轮换生成产品1和产品2，产品1成本15元，毛利润50元，单个产品生产耗时1分钟，每日最小生成量100个；产品2成本20元，毛利润75元，单个产品生产耗时2分钟，每日最小生成量100个，该机器每天最长工作时间为8小时，要求制定利润最大化的单日生产计划。 同样，首先整理思路，设置数据表格。
利润是目标，生产计划是变量，单元格H2和H3分别键入公式“=+B2*(D2-C2)”与“=+B3*(D3-C3)”建立目标与变量的关系，因为目标单元格必须是单个单元格，我们在单元格H4键入公式“=SUM(H2:H3)”汇总利润作为目标单元格。 接下来，整理约束条件，即变量的结果需要符合下列条件： 1）生成计划必须是整数 2）生产计划大于等于最小生成量 3）产品的耗时合计小于等于机器最长工作时间 数据表格和思路设置整理完成之后，打开“规划求解参数”对话框。目标单元格选择H4，目标类型选择“最大值”；可变单元格选择B2:B3。 通过点击“添加”按钮设置三个约束条件，分别是“B2:B3=整数”、“B2:B3>=G2:G3”、“F4<=F5”（F5是换算成分钟的机器最长工作时间）。其中“B2:B3=整数”相当于添加了B2=整数和B3=整数两个条件，“B2:B3>=G2:G3”相当于添加了B2>=G2和B3>=G3两个条件，这个是利用了数组公式的原理，当然你可以按照单元格逐个添加约束条件，但会繁琐很多，特别是当同一个条件涉及较多单元格的时候。
点击“求解”按钮，答案显示在工作表相应区域。生产计划分别为“280个”和“100个”，利润约“15300元”。
2、排班表 譬如：某美容院有一个老板和四个美容师；周一至周四要求早班1人，中班2人，周五至周日要求早班1人，中班4人；早班从10:00-18:00，中班从12:00-20:00，均为8小时；美容师每周工作时间不少于48小时，老板周五至周日至少会来两天，要求制定周排班表。 还是整理思路，设计数据表格。首先确立目标，因为目的是排班，所以可以将一周的排班总时长作为目标，通过E列的计算得出计划总时长为216小时，但单元格E16并非目标单元格，这个只是我们计算得出的目标值。 单元格L2才是目标单元格，键入公式“=SUMPRODUCT(C2:C15,K2:K15)”计算排班表总时长，其中K列统计排班表每日每班次人数，公式为“=SUM(F2:J2)”并下拉复制。由此建立目标单元格和可变单元格的关系。 单元格F16:J16统计排班表每个人的合计时长，公式为“=SUMPRODUCT($C$2:$C$15,F2:F15)”等。
再来整理约束条件： 1）排班为二进制数，即“0”和“1”，1表示排班，0表示不排班 2）合计当班人数等于计划当班人数 3）合计时长大于等于最低工作小时数 将以上整理的思路录入“规划求解参数”对话框。
求解之后，会发现排班表虽然满足以上约束条件，但差强人意，譬如有的美容师一天要分身上两个班次，老板的班次也没有都排在周末，这都是缺失约束条件造成的。
因此，要增加两个约束条件，一个是每人每天只能排一个班次，另一个是老板的班次只能排在周五-周日。都是关于排班的规则，是否可以把这两个约束条件整合成一个？如何实现呢？ 我们可以统计每人每天排班次数，然后设置每天的班次上限作为一个约束条件。每人排班合计区域为公式，从排班表中取值；每日排班上限为绝对值。
在“规划求解参数”中添加一个约束条件“F19:G25<=L19:P25”。
再“求解”，一份更加合理的排班表就生成了。
3、解决数学问题 只要有相应的约束条件，规划求解可以帮助我们快速解决数学问题，譬如鸡兔同笼：鸡和兔合计35个头，110条腿，问鸡、兔各几只。 一般会列出两个方程式来解，分别为X+Y=35、2X+4Y=110 ，那么如何用规划求解来解答呢？ 数据表格很简单，首先确立目标值，因为是求解鸡和兔分别的数量，我们可以将鸡和兔的合计数作为目标值，题目给出了两个条件，其中“合计35个头”已经被被设置为目标值，约束条件只剩下“合计110条腿”，设置数据表格如下。
然后打开“规划求解参数”，目标单元格选择B4，选择“值为”，输入框键入数字“35”；可变单元格选择“B2:B3”；约定条件C4=110。
点击“求解”按钮，答案便填充在可变单元格，鸡和兔分别为15和20只。 |
|
|
