編按：

一個(gè)工廠生產(chǎn)多種產(chǎn)品，一個(gè)銷售企業(yè)進(jìn)銷多種商品。那么在實(shí)際條件下，什么樣的產(chǎn)品生產(chǎn)組合或者進(jìn)銷組合會(huì)帶來最大的利潤呢？這種已知多種限制條件，求符合某種目標(biāo)的產(chǎn)品數(shù)量，在Excel中被稱為規(guī)劃求解。

如何合理的運(yùn)用有限的物力、財(cái)力、人力等資源，得到最佳的經(jīng)濟(jì)效果？

我們可以用Excel的規(guī)劃求解來組合產(chǎn)品以實(shí)現(xiàn)利潤最大化。下面舉個(gè)例子。

江南皮革廠生產(chǎn)三種產(chǎn)品，皮鞋、皮手套、皮帽。三種產(chǎn)品需要原材料甲、乙、丙。近期，原材料供應(yīng)有限制，生產(chǎn)工時(shí)也有限制。已知產(chǎn)品單件的用時(shí)、用料、利潤，求如何組合產(chǎn)品利潤最大。

一、加載規(guī)劃求解工具

規(guī)劃求解工具位于“數(shù)據(jù)”菜單下。如果沒有，則可以按下方的步驟進(jìn)行加載。

1.單擊“文件”>“選項(xiàng)”命令，彈出“Excel選項(xiàng)”對話框，選擇“加載項(xiàng)”選項(xiàng)。

2.單擊下方“轉(zhuǎn)到”按鈕，彈出“加載宏”對話框，在“可用加載宏”列表框中勾選“規(guī)劃求解”加載項(xiàng)，單擊“確定”按鈕。

經(jīng)過前面的操作，即可在“數(shù)據(jù)”選項(xiàng)卡的“分析”組中找到“規(guī)劃求解”功能。

二、設(shè)置目標(biāo)的計(jì)算公式

目標(biāo)是總利潤最大?？偫麧櫟扔诟鳟a(chǎn)品的產(chǎn)量乘以單件利潤。選擇B9單元格，輸入公式“=SUMPRODUCT(B7:D7,B8:D8)”，返回兩個(gè)區(qū)域?qū)?yīng)數(shù)值的乘積的和，即得到總利潤。

三、規(guī)劃求解

1．設(shè)置目標(biāo)和變量。選擇B9單元格，單擊“數(shù)據(jù)”>“分析”>“規(guī)劃求解”按鈕，在彈出的“規(guī)劃求解參數(shù)”對話框中，設(shè)置目標(biāo)為“最大值”。（因?yàn)槲覀冃枰麧欁畲蠡?。）設(shè)置“通過更改可變單元格”為“$B$8:$D$8”（生產(chǎn)量數(shù)值）。

2．設(shè)置約束條件。單擊“添加”按鈕，在彈出的“添加約束”對話框，設(shè)置材料甲的約束條件，在“單元格引用”參數(shù)框中輸入“$E$3”，將“<=”更改為“>=”符號(hào)，在“約束”參數(shù)框中輸入“$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．增加產(chǎn)量為整數(shù)的條件。再次單擊“添加”按鈕，設(shè)置皮鞋、皮手套、皮帽的數(shù)量為整數(shù)的條件，在“單元格引用”參數(shù)框中輸入“$B$6:$B$15”，在運(yùn)算符下拉列表中選擇“int”。

4．由于產(chǎn)品數(shù)量為非負(fù)數(shù)，勾選“使無約束變量為非負(fù)數(shù)”復(fù)選框；在“選擇求解方法”下拉列表框中選擇“單純線性規(guī)劃”。

5．得出答案。單擊“求解”按鈕，彈出“規(guī)劃求解結(jié)果”對話框，單擊“確定”按鈕。

經(jīng)過前面的操作，即可計(jì)算出每天生產(chǎn)皮鞋17件、皮手套50件、皮帽72件，能夠?qū)崿F(xiàn)利潤最大化，總利潤為5430元。

四、規(guī)劃求解報(bào)告

規(guī)劃求解會(huì)生成報(bào)告。再次執(zhí)行“規(guī)劃求解”，單擊“求解”>“運(yùn)算結(jié)果報(bào)告”>“確定”，即可得到報(bào)告。報(bào)告中清楚看到目標(biāo)單元格、可變單元格及約束條件，以及是否達(dá)到條件限制。

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

加了*的 SUMPRODUCT函數(shù)無所不能

excel單變量求解原理以及三個(gè)excel單變量求解案例

Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（上篇）

Excel進(jìn)階之路必學(xué)函數(shù)：動(dòng)態(tài)統(tǒng)計(jì)之王——OFFSET（下篇）