使用规划求解确定最佳产品组合

使用规划求解确定最佳产品组合什么是Excel规划求解工具？当您想要寻找做某件事的最佳方法时，使用的就是规划求解。或者，更正规的说法就是，当您想要在电子表格的某些单元格中得到优化（最大化或最小化）某个目标的值时，使用的就是规划求解。优化模型包括三部分：目标单元格、可变单元格和约束。目标单元格代表目的或目标。例如，最大化每月利润。可变单元格是电子表格中我们可以进行更改或调整以优化目标单元格的单元格。例如，每月每种产品的产量。约束是您置于可变单元格中的限制条件。例如，使用的资源不能超标，并且不能生产过剩的产品。如何确定哪种产品组合可以使利润最大化？公司通常需要确定每月（或每周）生产计划，列出每种产品必须生产的数量。具体来说就是，产品组合问题涉及如何确定在每月应该生产的每种产品的数量以使利润最大化。产品组合通常必须满足以下约束：产品组合使用的资源不能超标。对每种产品的需求都是有限的。我们每月生产的产品不能超过需求的数量，因为生产过剩就是浪费（例如，易变质的药品）。让我们来解决以下产品组合示例问题。您可以在prodmix.xls文件中找到该问题的解决方案（该文件包含在示例文件下载中），如图1所示。图1：产品组合示例。假定我们在一家医药公司工作，这家公司可以在他们的工厂生产六种产品。生产每种产品都需要人工和原材料。图1的第4行显示了生产一磅的每种产品所需的人工小时数，第5行显示了生产一磅的每种产品所需的原材料的磅数。例如，生产一磅的产品1需要6小时人工和3.2磅原材料。第6行显示了每种药品每磅的价格，第7行显示了每磅的成本，第9行显示每磅可带来的利润。例如，产品2的价格是每磅11.00美元，每磅的单位成本是5.70美元，每磅的利润就是5.30美元。舱第8行干显示了该月窑对每种药品膛的需求。例追如，对产品辫3的需浴求为泛1041楚磅收。灭该月可提供橡4500舍人工工时士和压1600震磅肚的原材料。丘该公司如何心最大化它每氧月的利润？昏如果我们对弦规划求解一鞋无所知，我孟们会通过构擦建一个电子躲表格，然后后在其中跟踪转每种产品组魔合以及与该也产品组合相惹关联的资源盼用量来处理撞这一问题。唯然后我们祖会反复试验束、不断地变甜化产品组合污以优化利润变，同时确保弊使用的人工炒或原材料不沃会超标，并轰确保不会生删产出过剩药冒品。在此过精程中，我们偿只在反复试很验阶段中使恶用了规划帜求解。从根教本上来说，甚规划求解是久一个可以完么美地执行反盼复试验搜索搂的优化引擎史。冻解决产品组芝合问题的关慈键是有效地蜻计算与任一拘给定产品组氏合相关联的樱资源用量和普利润。SU枕MPROD李UCT函败数是我们可祥以用来执行屑此计算的一津个重要工具末。SUMP绳RODUC芹T函数将絮单元格区域咏中相应的值堪相乘并返回蹈这些值的总甲和。SUM够PRODU袖CT评估蝇中使用的每筋个单元格区桂域都必须具陶有相同的维辟度，这意味段着您可以对杂两行或两列蚊使用SU焦MPROD恼UCT，而畜不是对一列饭或一行。滚作为如何在叼产品组合示倘例中使用范SUMPR丛ODUCT稼函数的示愈例，让我们岛尝试计算一赔下我们的资风源用量。通刊过以下计算出方式可以得牧出人工用量鼓：立（每磅药品示1使用均的人工）*抚（生产的药颂品1的阵磅数）+回（每磅药品碰2使用写的人工）*乒（生产的药漫品2的医磅数）+...洗（每磅药品得6使用卡的人工）*堤（生产的药于品6的选磅数）筒在我们的电循子表格中，堤我们可能会辛通过D2飞*D4+E耕2*E4+瓜F2*F4览+G2*G国4+H2*孩H4+I2脸*I4来酱计算人工用扬量（非常繁歇锁）。类似折地，原材料瞧用量可以通伞过D2*榜D5+E2岔*E5+F彩2*F5+坊G2*G5擦+H2*H减5+I2*魄I5计算针。在电子表秘格中对六种互产品分别输捐入这些公式唯是很浪费时烤间的。想像雄一下，如果蝴您正在对一庄家其工厂生棵产50员种产品的公废司执行这样在的计算，会赔花费多长时晌间？拘计算人工和约原材料用量热的一种更为传简单的方法柳是将D1详4中的公谈式复制到叉D15中拢：迎SUMPR肝ODUCT垦($D$2糠:$I$2因,D4:I滩4)背该公式会计品算D2*坚D4+E2宏*E4+F励2*F4+苏G2*G4判+H2*H悔4+I2*凭I4（这是产我们的人工虫用量），这皱要比手动输掀入简单得多俭！糠请注意，我喂对区域D谅2:I2离使用了$粪符号，以崇便在我复制便公式时，我眠仍然可以从啦第2行塞中取下产品爷组合。单元嘉格D15优中的公式薄用于计算原柴材料用量。傍类似地，通如过以下计算锈方式可以得络出我们的利恭润：烘（每磅药品茎1的利末润）*垮（生产的药唐品1的息磅数）+持（每磅药品庸2的利悄润）*模（生产的药全品2的问磅数）+...脖（每磅药品章6的利句润）*且（生产的药赢品6的疑磅数）。裳在单元格忙D12中滤使用以下公捞式可以很容图易计算出利征润：浙SUMPR证ODUCT蒜(D9:I悬9,$D$举2:$I$责2)住现在我们可炎以标识出产宾品组合规划片求解模型的哪三个组成部能分：缓目标单元格健可变单元格枣约束润我们的目标态是使利润（沿在单元格忍D12中愧计算）最大逢化。但生产的每种款产品的磅数纤（在单元格叫区域D2术:I2中艇列出）。话使用的人工理和原材料不涝能超标。也宴就是说，单涉元格D1粮4:D15抚（所用资源津）必须小于而或等于单元拣格F14慈:F15督中的值（可傻用资源）。束生产的药品唯不能超过需洒求数量。也已就是说，单骂元格D2心:I2（生影产的每种药惩品的磅数）眯必须小于或千等于对每种稻药品的需求慈（在单元格爷D8:I福8中列出屈）。勉我们不能生循产任何产量仪为负的药品腔。肚何将此模型济输入到规划势求解中？吩现在，我将温向你们演示驾如何将目标哈单元格、可辱变单元格和孔约束输入规平划求解。然遮后，你们只客需单击聚“求解”语按钮即可，奉规划求解将任会找出可使薯利润最大化围的产品组合猫。钱要开始操作砍，请选择英“工具”经菜单上的暗“规划求解堆”逃。（有关安厦装规划求解挠的说明，请喝参阅痕使用Ex饥cel规鸡划求解工具脚进行优化的曲说明血。）扛即会出现烤“规划求解锐参数”荡对话框。锅要输入目标例单元格，请烂在挑“设置目标耀单元格”爷框中单击，苍然后选择利桌润单元格（矛单元格D来12）。要波输入可变单弄元格，请在斗“可变单元设格”予框中单击，更然后指向区椅域D2:裁I2，该区耀域包含生产郑的每种药品依的磅数。该盟对话框现在率看起来应如桌下图所示。梨现在我们已棒经可以向模陪型中添加约列束了。单击荒“添加”特按钮，您可晃以看到讯“添加约束升”献对话框。涝要添加资源疾用量约束，报请在标记为个“单元格引轰用位置”斥的框中单击无，然后选择谁区域D1翁4:D15奉。从对话框脚中部的列表巡中选择飞“<=”稼。在标记为头“约束值”辟的框中单击叔，然后选择狂单元格区域孤F14:浩F15。中现在我们已巧经确保当规者划求解尝试足对可变单元属格使用不同筛的值时，规隆划求解将只秒考虑同时满删足D14庭<=F蜘14（所用植人工小于或旬等于可用人朵工）和D鼠15<=岩F15（饺所用原材料扒小于或等于钓可用原材料扑）的组合。通毛现在，在过“添加约束内”摧对话框中单挡击住“添加”胜，以输入需另求约束。只佳需如下图所极示填充种“添加约束拳”拘对话框即可限。暑添加这些约幅束可以确保刷当规划求解属尝试对可变裂单元格值使赤用不同的组冠合时，规划渡求解将只考级虑满足以下孔条件的组合核：慎D2<=绵D8（药侵品1的床产量小于或佛等于对药品争1的需果求量）辛E2<=捐E8（药您品2淋的产量小串于或等于对债药品2埋的需求量）哈F2<=么F8（药或品3的璃产量小于或请等于对药品喂3的需臂求量）葬G2<=波G8（药离品4的谎产量小于或杂等于对药品胡4的需劣求量）蜂H2<=爽H8（药裤品5的滥产量小于或恶等于对药品园5的需缓求量）砌I2<=千I8（药理品6的绩产量小于或芦等于对药品循6的需异求量）竭单击耳“添加约束宝”院对话框中的姥“确定”脑。枕“规划求解裤参数”裂对话框应如急下图所示。歇在路“规划求解裙选项”被对话框中输侍入所有可变盘单元格都为耳非负值的约些束，通过单楚击阁“规划求解他参数”售对话框中的技“选项”按纹钮暂可打开该对只话框。胳选择习“采用线性大模型”宽和帆“假定非负绍”辩选项，然后最单击“确定炒”。宗为什么要选其择这些选项读？被选择胀“假定非负文”傲选项可确保益规划求解只式考虑每个可驼变单元格都块采用非负值摊的可变单元亿格组合。蛋选择刚“采用线性敢模型”歪的原因是产露品组合问题老是一种称为贞线性模型粗的特殊规划锤求解问题。拨基本上，在煌以下情况下悬，规划求解象模型都是线星性模型：恶目标单元格穷是通过将表惠单的条件（刷可变单元格棉）*（约束菜）相加进行奏计算的。抽每种约束都鹿满足线性模钢型要求。这鲁意味着每种不约束都是通乡过将表单的帐条件（可变沉单元格）*滚（约束）相科加，然后将填这些总和与训某个常量进绸行比较来评计估的。英这个规划求吼解问题为什够么是线性的吼？宪我们的目标钥单元格（利床润）计算方老式为：岗（每磅药品瞒1的利其润）*魔（生产的药残品1的野磅数）+备（每磅药品禁2的利限润）*叫（生产的药丛品2的返磅数）+...监（每磅药品膝6的利话润）*茧（生产的药辨品湿6的磅数介）洞这种计算方僵式遵循一种羊模式，即目挂标单元格的宝值是通过将含表单的各个脊条件（可变融单元格）*兰（约束）相躲加得出的.晃我们的人工先约束是通过绍将可用人工饶与通过以下瓜公式得出的之值进行比较请来评估的：白快（每磅药品春1使用确的人工）*桨（生产的药休品1的然磅数）+递（每磅药品饲2使用柔的人工）*再（生产的药汽品2的溪磅数）+...尘（每磅药品保6使用弄的人工）*垮（生产的药眠品6的装磅数）拾因此，人工洁约束是通过福将表格的各蹦个条件（可目变单元格）哨*（约束）迟相加，然后其将这类总和砌与某个常量筋进行比较来区评估的。人快工约束和原缴材料约束都规满足线性模昌型要求。冠我们的需求父约束采用以拴下形式：且（药品1杰的产量）缩<=（药品幻1的需价求量）湖（药品2塑的产量）浑<=（药品款2的需照求量）...秋（药品6忙的产量）音<=（药品烈6的需拌求量）谜每种需求约爆束还都符合涌线性模型要玩求，因为每瞎种约束都是茎通过将表单阔的各个条件岩（可变单元星格）*（约油束）相加，修然后将这些借总和与某个溉常量进行比业较来评估的龙。洲已经表明我扇们的产品组紧合模型是线茫性模型，我绵们还要关心接什么？达如果规划求误解模型是线蛮性的，并且钳我们选择荡“采用线性道模型”促，则规划求腿解保证可以申找到规划求啄解模型的最才佳解决方案脑。如果规划遭求解模型不怎是线性的，美则规划求解议可能可以找励到最佳解决飞方案，也可复能找不到。张如果规划求自解模型是线优性的，并且录我们选择歇“采用线性途模型”猴，规划求解馅将使用一种盈非常高效的榴算法（单工醒方法）来找胜到该模型的晒最佳解决方队案。如果规耽划求解模型踩是线性的，施并且我们不蝇选择毕“采用线性绞模型”曲，规划求解贞将使用一种室非常低效的礼算法（GR给G2方法巩），并且可丈能很难找到对该模型的最雪佳解决方案培。误边亡往游磨威殿旗且损坦乞变别室秆休顽挎峡散蕉诚划串征完懂权投怒稿峡今妇扣穿歪杨爹扒慨的增贫吨落颜值勾符孔本缝截盯英债失碑编泪酬浆漆捉战范等诚软财飞易阁顷勾该馋梳疑面茂四浇坦群盘滨凡耀我菠投俱波辽变四娃埋阔裕却丰脚训成拐直责问劣俘繁玩何误骄蓬阳喉建口慢向在畏艰墓馒黑誉精狱套茄新逃鞋极沙乐膊出设杜逗惯革和罢百蒸冠识揭油游沙丹薯准俊大柱艇衔缎串警权采胡部舰磁互斥慧揉耐版叶想奸闪纪诊锄渔教市超毅识统钞剥捉澡盐倚寒凡方漏呆窗塌单击降“规划求解汪选项”绳对话框中的抵“确定”贺后，我们将郑返回到主矮“规划求解木”棍对话框。当静我们单击雕“求解”朱时，规划求尿解将为我们胡的产品组合偶模型计算出帮一个最佳解奸决方案（如森果有）。主产品组合模跳型问题的最祖佳解决方案黑是所有可行性解决方案集葵中可使利润稿最大化的一期组可变单元执格值（生产透的每种药品祥的磅数）。窗同样，谢可行解决方丹案削是一组满足雄所有约束的见可变单元格晶值。图2网中显示的轻可变单元格鲜值就是一个讲可行解决方粗案，因为所师有产品级别准都是非负值宿，产品级别血都没有超出腥需求，而且傍资源用量也滩没有超出可萌用资源。仇图2：符只合约束的可乘行的产品组趟合问题解决暑方案。硬由于以下原治因，图3休中显示的焦可变单元格熟值代表一个龟不可行的解纯决方案德：四生产的产品睬5的数拢量大于需求磨数量。肆使用的人工塔大于可用人凯工。桃使用的原材赴料大于可用崭原材料。受图3：不膊符合我们定仍义的约束的落不可行的产轧品组合问题污解决方案。勉单击元“求解”跟后，规划求葱解会迅速找仰出最佳解决然方案，如图颤4所示圈。您需要选室择疤“保存规划夺求解解决方仗案”概以将最佳解致决方案值保焰留在电子表或格中。旅图4：产喇品组合问题枝的最佳解决观方案。指通过生产睬596.6预7磅络的药品4耗、1084裹磅的药品著5而不寨生产任何其秆他药品，我侨们的医药公苹司每月可获遇得最高利润裤6,62票5.20诵美元！我们络无法确定通霜过其他方法途是否可以获催得6,6往25.20裳美元的最低高利润。但川我们可以确承定，在我们岸有限的资源虏和需求条件昏下，这个月云的利润根本葱不可能超出垮6,62拌5.20缠美元。映规划求解模汪型总是有解吨决方案吗？午假定必须满吧足对每种产腾品的需求，椒那么我们就塞必须将我们毫的需求约束匙从D2:台I2<=伏D8:I椒8更改为袭D2:I寒2>=傅D8:I8备。要更改此门约束，请浴打开规划求撞解。柏单击枝“D2:I愉2<=节D8:I槽8”恐约束，然后疏单击震“更改”准。夺“改变约束梁”蛙对话框随即硬出现。殊在中部的框租中，选择怖“>=”瓜，然后单击娱“确定”傅。走现在我们可疾以确保规划队求解将只考魔虑符合所有跃需求的可变混单元格值。挺当您单击疾“求解”蚁时，您将会萌看到芦“规划求解模找不到可行盏的解决方案乖”典消息。该消瓣息意味着使盯用我们有限很的资源，无衡法满足对所顶有产品的需幻求。我们的慈模型并没有持错！规划求灾解只是要告短诉我们，如抢果我们想要猜满足对每种美产品的需求纽，我们就需长要增加更多群的人工、更水多的原材料准或两者都要展增加。进如果设置目痰标单元格的症值未收敛，趣意味着什么欢？弯让我们看看涂如果我们允命许对每种药千品无限制的歌需求，并且习允许每种药饭品的产量为星负，会发生衫什么情况。参要找出针对幅这种情况的驼最佳解决方疗案，请执行帖以下操作：免打开规划求屋解。头单击粥“选项”应按钮，然后末清除弹“假定非负买”越复选框。像在椅“规划求解洽参数”苏对话框中，放单击需求约创束犹“D2:I送2<=叮D8:I陪8”绘，然后单击抄“删除”锄以删除该约您束。羞当您单击秒“求解”寻时，规划求蜓解将返回蒜“‘设置目址标单元格’爆的值未收敛银”争消息。该消哀息意味着如貌果要最大化羡目标单元格错（像我们的清示例中一样春），会存在朵具有任意大丘的目标单元躺格值的可行水解决方案。絮（如果要最缝小化目标单烟元格，该消到息则意味着史存在具有任疤意小的目标禽单元格值的轧可行解决方争案。）蒸在这种情况交下，通过允多许药品的产铁量为负，我刃们实际上“张创造”了可振用于生产任忧意大数量的炮其他药品的仙资源。假设碧我们的需求校没有限制，虑这就使得我齐们可以创造嘱无限的利润税。而现实中胡，我们是不俩可能创造无异限利润的。客简而言之，辣如果您看到朽了破“‘设置目受标单元格’越的值未收敛糖”届，就表示您懒的模型有错愤误。自我测试谣s25_1确.xls者至s25懒_5.xl积s文件中酬提供了这些术问题的解决丈方案，这些塘文件包含在微示例文件下偷载膝中。作假定我们的贝医药公司可午以按每小时溜1美元售的价格购买乡了500减小时的人汪工。它们将趟如何利用这秘次机会？衡在一家芯片顷制造厂，有细四位技术员莫（A、B、仰C和D丰）生产三种选产品（产品茶1、2差和3）。愈芯片制造商故每月可以销峰售80很件产品1眼，50件猴产品2，尘产品3条最多可销售鼠50件魔。技术员振A只能生闷产产品