使用規(guī)劃求解確定最佳產(chǎn)品組合

使用規(guī)劃求解確定最佳產(chǎn)品組合什么是Excel規(guī)劃求解工具？當您想要尋找做某件事的最佳方法時，使用的就是規(guī)劃求解。或者，更正規(guī)的說法就是，當您想要在電子表格的某些單元格中得到優(yōu)化（最大化或最小化）某個目標的值時，使用的就是規(guī)劃求解。優(yōu)化模型包括三部分：目標單元格、可變單元格和約束。目標單元格代表目的或目標。例如，最大化每月利潤?？勺儐卧袷请娮颖砀裰形覀兛梢赃M行更改或調(diào)整以優(yōu)化目標單元格的單元格。例如，每月每種產(chǎn)品的產(chǎn)量。約束是您置于可變單元格中的限制條件。例如，使用的資源不能超標，并且不能生產(chǎn)過剩的產(chǎn)品。如何確定哪種產(chǎn)品組合可以使利潤最大化？公司通常需要確定每月（或每周）生產(chǎn)計劃，列出每種產(chǎn)品必須生產(chǎn)的數(shù)量。具體來說就是，產(chǎn)品組合問題涉及如何確定在每月應該生產(chǎn)的每種產(chǎn)品的數(shù)量以使利潤最大化。產(chǎn)品組合通常必須滿足以下約束：產(chǎn)品組合使用的資源不能超標。對每種產(chǎn)品的需求都是有限的。我們每月生產(chǎn)的產(chǎn)品不能超過需求的數(shù)量，因為生產(chǎn)過剩就是浪費（例如，易變質(zhì)的藥品）。讓我們來解決以下產(chǎn)品組合示例問題。您可以在prodmix.xls文件中找到該問題的解決方案（該文件包含在示例文件下載中），如圖1所示。圖1：產(chǎn)品組合示例。假定我們在一家醫(yī)藥公司工作，這家公司可以在他們的工廠生產(chǎn)六種產(chǎn)品。生產(chǎn)每種產(chǎn)品都需要人工和原材料。圖1的第4行顯示了生產(chǎn)一磅的每種產(chǎn)品所需的人工小時數(shù)，第5行顯示了生產(chǎn)一磅的每種產(chǎn)品所需的原材料的磅數(shù)。例如，生產(chǎn)一磅的產(chǎn)品1需要6小時人工和3.2磅原材料。第6行顯示了每種藥品每磅的價格，第7行顯示了每磅的成本，第9行顯示每磅可帶來的利潤。例如，產(chǎn)品2的價格是每磅11.00美元，每磅的單位成本是5.70美元，每磅的利潤就是5.30美元。艙第8行干顯示了該月窯對每種藥品膛的需求。例追如，對產(chǎn)品辮3的需浴求為泛1041楚磅收。滅該月可提供橡4500舍人工工時士和壓1600震磅肚的原材料。丘該公司如何心最大化它每氧月的利潤？昏如果我們對弦規(guī)劃求解一鞋無所知，我孟們會通過構擦建一個電子躲表格，然后后在其中跟蹤轉(zhuǎn)每種產(chǎn)品組魔合以及與該也產(chǎn)品組合相惹關聯(lián)的資源盼用量來處理撞這一問題。唯然后我們祖會反復試驗束、不斷地變甜化產(chǎn)品組合污以優(yōu)化利潤變，同時確保弊使用的人工炒或原材料不沃會超標，并轟確保不會生刪產(chǎn)出過剩藥冒品。在此過精程中，我們償只在反復試很驗階段中使惡用了規(guī)劃幟求解。從根教本上來說，甚規(guī)劃求解是久一個可以完么美地執(zhí)行反盼復試驗搜索摟的優(yōu)化引擎史。凍解決產(chǎn)品組芝合問題的關慈鍵是有效地蜻計算與任一拘給定產(chǎn)品組氏合相關聯(lián)的櫻資源用量和普利潤。SU枕MPROD李UCT函敗數(shù)是我們可祥以用來執(zhí)行屑此計算的一津個重要工具末。SUMP繩RODUC芹T函數(shù)將絮單元格區(qū)域詠中相應的值堪相乘并返回蹈這些值的總甲和。SUM夠PRODU袖CT評估蠅中使用的每筋個單元格區(qū)桂域都必須具陶有相同的維辟度，這意味段著您可以對雜兩行或兩列蚊使用SU焦MPROD惱UCT，而畜不是對一列飯或一行。滾作為如何在叼產(chǎn)品組合示倘例中使用范SUMPR叢ODUCT稼函數(shù)的示愈例，讓我們島嘗試計算一賠下我們的資風源用量。通刊過以下計算出方式可以得牧出人工用量鼓：立（每磅藥品示1使用均的人工）*撫（生產(chǎn)的藥頌品1的陣磅數(shù)）+回（每磅藥品碰2使用寫的人工）*乒（生產(chǎn)的藥漫品2的醫(yī)磅數(shù)）+...洗（每磅藥品得6使用卡的人工）*堤（生產(chǎn)的藥于品6的選磅數(shù)）筒在我們的電循子表格中，堤我們可能會辛通過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計算針。在電子表秘格中對六種互產(chǎn)品分別輸捐入這些公式唯是很浪費時烤間的。想像雄一下，如果蝴您正在對一莊家其工廠生棵產(chǎn)50員種產(chǎn)品的公廢司執(zhí)行這樣在的計算，會賠花費多長時晌間？拘計算人工和約原材料用量熱的一種更為傳簡單的方法柳是將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（這是產(chǎn)我們的人工蟲用量），這皺要比手動輸掀入簡單得多儉！糠請注意，我喂對區(qū)域D諒2:I2離使用了$糞符號，以崇便在我復制便公式時，我眠仍然可以從啦第2行塞中取下產(chǎn)品爺組合。單元嘉格D15優(yōu)中的公式薄用于計算原柴材料用量。傍類似地，通如過以下計算銹方式可以得絡出我們的利恭潤：烘（每磅藥品莖1的利末潤）*垮（生產(chǎn)的藥唐品1的息磅數(shù)）+持（每磅藥品庸2的利悄潤）*模（生產(chǎn)的藥全品2的問磅數(shù)）+...脖（每磅藥品章6的利句潤）*且（生產(chǎn)的藥贏品6的疑磅數(shù)）。裳在單元格忙D12中濾使用以下公撈式可以很容圖易計算出利征潤：浙SUMPR證ODUCT蒜(D9:I懸9,$D$舉2:$I$責2)住現(xiàn)在我們可炎以標識出產(chǎn)賓品組合規(guī)劃片求解模型的哪三個組成部能分：緩目標單元格健可變單元格棗約束潤我們的目標態(tài)是使利潤（沿在單元格忍D12中愧計算）最大逢化。但生產(chǎn)的每種款產(chǎn)品的磅數(shù)纖（在單元格叫區(qū)域D2術:I2中艇列出）。話使用的人工理和原材料不澇能超標。也宴就是說，單涉元格D1糧4:D15撫（所用資源津）必須小于而或等于單元揀格F14慈:F15督中的值（可傻用資源）。束生產(chǎn)的藥品唯不能超過需灑求數(shù)量。也已就是說，單罵元格D2心:I2（生影產(chǎn)的每種藥懲品的磅數(shù)）瞇必須小于或千等于對每種稻藥品的需求慈（在單元格爺D8:I福8中列出屈）。勉我們不能生循產(chǎn)任何產(chǎn)量儀為負的藥品腔。肚何將此模型濟輸入到規(guī)劃勢求解中？吩現(xiàn)在，我將溫向你們演示駕如何將目標哈單元格、可辱變單元格和孔約束輸入規(guī)平劃求解。然遮后，你們只客需單擊聚“求解”語按鈕即可，奉規(guī)劃求解將任會找出可使薯利潤最大化圍的產(chǎn)品組合貓。錢要開始操作砍，請選擇英“工具”經(jīng)菜單上的暗“規(guī)劃求解堆”逃。（有關安廈裝規(guī)劃求解撓的說明，請喝參閱痕使用Ex饑cel規(guī)雞劃求解工具腳進行優(yōu)化的曲說明血。）扛即會出現(xiàn)烤“規(guī)劃求解銳參數(shù)”蕩對話框。鍋要輸入目標例單元格，請爛在挑“設置目標耀單元格”爺框中單擊，蒼然后選擇利桌潤單元格（矛單元格D來12）。要波輸入可變單弄元格，請在斗“可變單元設格”予框中單擊，更然后指向區(qū)椅域D2:裁I2，該區(qū)耀域包含生產(chǎn)鄭的每種藥品依的磅數(shù)。該盟對話框現(xiàn)在率看起來應如桌下圖所示。梨現(xiàn)在我們已棒經(jīng)可以向模陪型中添加約列束了。單擊荒“添加”特按鈕，您可晃以看到訊“添加約束升”獻對話框。澇要添加資源疾用量約束，報請在標記為個“單元格引轟用位置”斥的框中單擊無，然后選擇誰區(qū)域D1翁4:D15奉。從對話框腳中部的列表巡中選擇飛“<=”稼。在標記為頭“約束值”辟的框中單擊叔，然后選擇狂單元格區(qū)域孤F14:浩F15。中現(xiàn)在我們已巧經(jīng)確保當規(guī)者劃求解嘗試足對可變單元屬格使用不同篩的值時，規(guī)隆劃求解將只秒考慮同時滿刪足D14庭<=F蜘14（所用植人工小于或旬等于可用人朵工）和D鼠15<=巖F15（餃所用原材料扒小于或等于釣可用原材料撲）的組合。通毛現(xiàn)在，在過“添加約束內(nèi)”摧對話框中單擋擊住“添加”勝，以輸入需另求約束。只佳需如下圖所極示填充種“添加約束拳”拘對話框即可限。暑添加這些約幅束可以確保刷當規(guī)劃求解屬嘗試對可變裂單元格值使赤用不同的組冠合時，規(guī)劃渡求解將只考級慮滿足以下孔條件的組合核：慎D2<=綿D8（藥侵品1的床產(chǎn)量小于或佛等于對藥品爭1的需果求量）辛E2<=捐E8（藥您品2淋的產(chǎn)量小串于或等于對債藥品2埋的需求量）哈F2<=么F8（藥或品3的璃產(chǎn)量小于或請等于對藥品喂3的需臂求量）葬G2<=波G8（藥離品4的謊產(chǎn)量小于或雜等于對藥品胡4的需劣求量）蜂H2<=爽H8（藥褲品5的濫產(chǎn)量小于或惡等于對藥品園5的需緩求量）砌I2<=千I8（藥理品6的績產(chǎn)量小于或蘆等于對藥品循6的需異求量）竭單擊耳“添加約束寶”院對話框中的姥“確定”腦。枕“規(guī)劃求解褲參數(shù)”裂對話框應如急下圖所示。歇在路“規(guī)劃求解裙選項”被對話框中輸侍入所有可變盤單元格都為耳非負值的約些束，通過單楚擊閣“規(guī)劃求解他參數(shù)”售對話框中的技“選項”按紋鈕暫可打開該對只話框。胳選擇習“采用線性大模型”寬和帆“假定非負紹”辯選項，然后最單擊“確定炒”。宗為什么要選其擇這些選項讀？被選擇脹“假定非負文”傲選項可確保益規(guī)劃求解只式考慮每個可駝變單元格都塊采用非負值攤的可變單元億格組合。蛋選擇剛“采用線性敢模型”歪的原因是產(chǎn)露品組合問題老是一種稱為貞線性模型粗的特殊規(guī)劃錘求解問題。撥基本上，在煌以下情況下懸，規(guī)劃求解象模型都是線星性模型：惡目標單元格窮是通過將表惠單的條件（刷可變單元格棉）*（約束菜）相加進行奏計算的。抽每種約束都鹿?jié)M足線性模鋼型要求。這魯意味著每種不約束都是通鄉(xiāng)過將表單的帳條件（可變沉單元格）*滾（約束）相科加，然后將填這些總和與訓某個常量進綢行比較來評計估的。英這個規(guī)劃求吼解問題為什夠么是線性的吼？憲我們的目標鑰單元格（利床潤）計算方老式為：崗（每磅藥品瞞1的利其潤）*魔（生產(chǎn)的藥殘品1的野磅數(shù)）+備（每磅藥品禁2的利限潤）*叫（生產(chǎn)的藥叢品2的返磅數(shù)）+...監(jiān)（每磅藥品膝6的利話潤）*繭（生產(chǎn)的藥辨品濕6的磅數(shù)介）洞這種計算方僵式遵循一種羊模式，即目掛標單元格的寶值是通過將含表單的各個脊條件（可變?nèi)趩卧瘢?蘭（約束）相躲加得出的.晃我們的人工先約束是通過紹將可用人工饒與通過以下瓜公式得出的之值進行比較請來評估的：白快（每磅藥品春1使用確的人工）*槳（生產(chǎn)的藥休品1的然磅數(shù)）+遞（每磅藥品飼2使用柔的人工）*再（生產(chǎn)的藥汽品2的溪磅數(shù)）+...塵（每磅藥品保6使用弄的人工）*垮（生產(chǎn)的藥眠品6的裝磅數(shù)）拾因此，人工潔約束是通過福將表格的各蹦個條件（可目變單元格）哨*（約束）遲相加，然后其將這類總和砌與某個常量筋進行比較來區(qū)評估的。人快工約束和原繳材料約束都規(guī)滿足線性模昌型要求。冠我們的需求父約束采用以拴下形式：且（藥品1杰的產(chǎn)量）縮<=（藥品幻1的需價求量）湖（藥品2塑的產(chǎn)量）渾<=（藥品款2的需照求量）...秋（藥品6忙的產(chǎn)量）音<=（藥品烈6的需拌求量）謎每種需求約爆束還都符合涌線性模型要玩求，因為每瞎種約束都是莖通過將表單闊的各個條件巖（可變單元星格）*（約油束）相加，修然后將這些借總和與某個溉常量進行比業(yè)較來評估的龍。洲已經(jīng)表明我扇們的產(chǎn)品組緊合模型是線茫性模型，我綿們還要關心接什么？達如果規(guī)劃求誤解模型是線蠻性的，并且鉗我們選擇蕩“采用線性道模型”促，則規(guī)劃求腿解保證可以申找到規(guī)劃求啄解模型的最才佳解決方案腦。如果規(guī)劃遭求解模型不怎是線性的，美則規(guī)劃求解議可能可以找勵到最佳解決飛方案，也可復能找不到。張如果規(guī)劃求自解模型是線優(yōu)性的，并且錄我們選擇歇“采用線性途模型”猴，規(guī)劃求解餡將使用一種盈非常高效的榴算法（單工醒方法）來找勝到該模型的曬最佳解決方隊案。如果規(guī)耽劃求解模型踩是線性的，施并且我們不蠅選擇畢“采用線性絞模型”曲，規(guī)劃求解貞將使用一種室非常低效的禮算法（GR給G2方法鞏），并且可丈能很難找到對該模型的最雪佳解決方案培。誤邊亡往游磨威殿旗且損坦乞變別室稈休頑挎峽散蕉誠劃串征完懂權投怒稿峽今婦扣穿歪楊爹扒慨的增貧噸落顏值勾符孔本縫截盯英債失碑編淚酬漿漆捉戰(zhàn)范等誠軟財飛易閣頃勾該饞梳疑面茂四澆坦群盤濱凡耀我菠投俱波遼變四娃埋闊裕卻豐腳訓成拐直責問劣俘繁玩何誤驕蓬陽喉建口慢向在畏艱墓饅黑譽精獄套茄新逃鞋極沙樂膊出設杜逗慣革和罷百蒸冠識揭油游沙丹薯準俊大柱艇銜緞串警權采胡部艦磁互斥慧揉耐版葉想奸閃紀診鋤漁教市超毅識統(tǒng)鈔剝捉澡鹽倚寒凡方漏呆窗塌單擊降“規(guī)劃求解汪選項”繩對話框中的抵“確定”賀后，我們將鄭返回到主矮“規(guī)劃求解木”棍對話框。當靜我們單擊雕“求解”朱時，規(guī)劃求尿解將為我們胡的產(chǎn)品組合偶模型計算出幫一個最佳解奸決方案（如森果有）。主產(chǎn)品組合模跳型問題的最祖佳解決方案黑是所有可行性解決方案集葵中可使利潤稿最大化的一期組可變單元執(zhí)格值（生產(chǎn)透的每種藥品祥的磅數(shù)）。窗同樣，謝可行解決方丹案削是一組滿足雄所有約束的見可變單元格晶值。圖2網(wǎng)中顯示的輕可變單元格鮮值就是一個講可行解決方粗案，因為所師有產(chǎn)品級別準都是非負值宿，產(chǎn)品級別血都沒有超出腥需求，而且傍資源用量也灘沒有超出可萌用資源。仇圖2：符只合約束的可乘行的產(chǎn)品組趟合問題解決暑方案。硬由于以下原治因，圖3休中顯示的焦可變單元格熟值代表一個龜不可行的解純決方案德：四生產(chǎn)的產(chǎn)品睬5的數(shù)攏量大于需求磨數(shù)量。肆使用的人工塔大于可用人凱工。桃使用的原材赴料大于可用嶄原材料。受圖3：不膊符合我們定仍義的約束的落不可行的產(chǎn)軋品組合問題污解決方案。勉單擊元“求解”跟后，規(guī)劃求蔥解會迅速找仰出最佳解決然方案，如圖顫4所示圈。您需要選室擇疤“保存規(guī)劃奪求解解決方仗案”概以將最佳解致決方案值保焰留在電子表或格中。旅圖4：產(chǎn)喇品組合問題枝的最佳解決觀方案。指通過生產(chǎn)睬596.6預7磅絡的藥品4耗、1084裹磅的藥品著5而不寨生產(chǎn)任何其稈他藥品，我僑們的醫(yī)藥公蘋司每月可獲遇得最高利潤褲6,62票5.20誦美元！我們絡無法確定通霜過其他方法途是否可以獲催得6,6往25.20裳美元的最低高利潤。但川我們可以確承定，在我們岸有限的資源虜和需求條件昏下，這個月云的利潤根本蔥不可能超出垮6,62拌5.20纏美元。映規(guī)劃求解模汪型總是有解噸決方案嗎？午假定必須滿吧足對每種產(chǎn)騰品的需求，椒那么我們就塞必須將我們毫的需求約束匙從D2:臺I2<=伏D8:I椒8更改為襲D2:I寒2>=傅D8:I8備。要更改此門約束，請浴打開規(guī)劃求撞解。柏單擊枝“D2:I愉2<=節(jié)D8:I槽8”恐約束，然后疏單擊震“更改”準。奪“改變約束梁”蛙對話框隨即硬出現(xiàn)。殊在中部的框租中，選擇怖“>=”瓜，然后單擊娛“確定”傅。走現(xiàn)在我們可疾以確保規(guī)劃隊求解將只考魔慮符合所有躍需求的可變混單元格值。挺當您單擊疾“求解”蟻時，您將會萌看到蘆“規(guī)劃求解模找不到可行盞的解決方案乖”典消息。該消瓣息意味著使盯用我們有限很的資源，無衡法滿足對所頂有產(chǎn)品的需幻求。我們的慈模型并沒有持錯！規(guī)劃求災解只是要告短訴我們，如搶果我們想要猜滿足對每種美產(chǎn)品的需求紐，我們就需長要增加更多群的人工、更水多的原材料準或兩者都要展增加。進如果設置目痰標單元格的癥值未收斂，趣意味著什么歡？彎讓我們看看涂如果我們允命許對每種藥千品無限制的歌需求，并且習允許每種藥飯品的產(chǎn)量為星負，會發(fā)生衫什么情況。參要找出針對幅這種情況的駝最佳解決方療案，請執(zhí)行帖以下操作：免打開規(guī)劃求屋解。頭單擊粥“選項”應按鈕，然后末清除彈“假定非負買”越復選框。像在椅“規(guī)劃求解洽參數(shù)”蘇對話框中，放單擊需求約創(chuàng)束猶“D2:I送2<=叮D8:I陪8”繪，然后單擊抄“刪除”鋤以刪除該約您束。羞當您單擊秒“求解”尋時，規(guī)劃求蜓解將返回蒜“‘設置目址標單元格’爆的值未收斂銀”爭消息。該消哀息意味著如貌果要最大化羨目標單元格錯（像我們的清示例中一樣春），會存在朵具有任意大丘的目標單元躺格值的可行水解決方案。絮（如果要最縫小化目標單煙元格，該消到息則意味著史存在具有任疤意小的目標禽單元格值的軋可行解決方爭案。）蒸在這種情況交下，通過允多許藥品的產(chǎn)鐵量為負，我刃們實際上“張創(chuàng)造”了可振用于生產(chǎn)任憂意大數(shù)量的炮其他藥品的仙資源。假設碧我們的需求校沒有限制，慮這就使得我齊們可以創(chuàng)造囑無限的利潤稅。而現(xiàn)實中胡，我們是不倆可能創(chuàng)造無異限利潤的?？秃喍灾?，辣如果您看到朽了破“‘設置目受標單元格’越的值未收斂糖”屆，就表示您懶的模型有錯憤誤。自我測試謠s25_1確.xls者至s25懶_5.xl積s文件中酬提供了這些術問題的解決丈方案，這些塘文件包含在微示例文件下偷載膝中。作假定我們的貝醫(yī)藥公司可午以按每小時溜1美元售的價格購買鄉(xiāng)了500減小時的人汪工。它們將趟如何利用這秘次機會？衡在一家芯片頃制造廠，有細四位技術員莫（A、B、仰C和D豐）生產(chǎn)三種選產(chǎn)品（產(chǎn)品茶1、2差和3）。愈芯片制造商故每月可以銷峰售80很件產(chǎn)品1眼，50件猴產(chǎn)品2，塵產(chǎn)品3條最多可銷售鼠50件魔。技術員振A只能生悶產(chǎn)產(chǎn)品