在Excel中根據(jù)工時(shí)、材料和利潤(rùn)規(guī)劃產(chǎn)品最佳生產(chǎn)組合

?

作者：賦春風(fēng)來(lái)源：部落窩教育發(fā)布時(shí)間：2020-10-05 01:01:55點(diǎn)擊：3173

分享到:

收藏收藏人氣：0人

版權(quán)說(shuō)明：原創(chuàng)作品，禁止轉(zhuǎn)載。

編按：

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

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

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

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

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

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

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

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

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

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

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

三、規(guī)劃求解

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

2．設(shè)置約束條件。單擊“添加”按鈕，在彈出的“添加約束”對(duì)話框，設(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