生產(chǎn)計劃問題最優(yōu)解的EXCEL實現(xiàn)

摘 要:求解生產(chǎn)計劃問題的最優(yōu)解一般需要通過專門的運籌學軟件進行，本文結(jié)合具體案例探討了EXCEL下生產(chǎn)計劃問題最優(yōu)解的求解的實現(xiàn)過程，較為簡捷、準確地得出了生產(chǎn)計劃問題最優(yōu)解的求解結(jié)果。

關(guān)鍵詞:線性規(guī)劃;生產(chǎn)計劃問題;EXCEL;規(guī)劃求解

文獻標識碼: A 中圖分類號:F224.31

在工業(yè)領(lǐng)域用線性規(guī)劃求解的典型問題有運輸問題、生產(chǎn)計劃問題、配套生產(chǎn)問題、下料和配料問題等。生產(chǎn)計劃問題是指用m種資源生產(chǎn)n種產(chǎn)品，已知各種產(chǎn)品每生產(chǎn)一單位可得的利潤和所需的各種資源的數(shù)量，以及各種資源的限額，問如何計劃各種產(chǎn)品的生產(chǎn)量，使總的利潤為最大?求解生產(chǎn)計劃問題的最優(yōu)解的過程較為復雜，一般需要通過專門的運籌學軟件進行。本文結(jié)合具體案例探討EXCEL下生產(chǎn)計劃問題最優(yōu)解求解的實現(xiàn)過程。

1應(yīng)用案例[1]

永久機械廠生產(chǎn)三種產(chǎn)品，每種產(chǎn)品要經(jīng)過A、B兩道工序加工。設(shè)該廠有兩種規(guī)格的設(shè)備能完成A工序，它們以A1、A2表示;有三種規(guī)格的設(shè)備能完成B工序，它們以B1、B2、B3表示，產(chǎn)品1可以在A、B任何一種規(guī)格設(shè)備上加工;產(chǎn)品2可在任何一種規(guī)格的A設(shè)備上加工，但完成B工序時只能在B1設(shè)備上加工;產(chǎn)品3只能在A2與B2 設(shè)備上加工。已知各種機床設(shè)備的單件工時，原材料單價，產(chǎn)品銷售價格，各種設(shè)備有效臺時以及滿負荷操作時機床的設(shè)備費用，如表1所示，要求安排最優(yōu)的生產(chǎn)計劃，使廠利潤最大。

第1步:對表1進行補充計算，得到單位臺時設(shè)備費用與單件利潤。

單位臺時設(shè)備費用G3=F3/E3，向下復制到G7(見圖1G1:G7);

單件利潤B10=B9-B8，向右復制到D10(見圖1A10:D10)。

第2步:設(shè)置三種產(chǎn)品的產(chǎn)量變量矩陣，賦初始值1(見圖1B12:D16)。

第3步:確定A、B設(shè)備產(chǎn)量(見圖1A17:D18)。

從案例條件知，各產(chǎn)品在A設(shè)備加工的產(chǎn)量應(yīng)等于在B設(shè)備上加工的產(chǎn)量，因此有:

故設(shè):產(chǎn)品1在A設(shè)備上加工的產(chǎn)品產(chǎn)量為B17=B12+B13，在B設(shè)備上加工的產(chǎn)品產(chǎn)量為B18=B14+B15+B16;同理，產(chǎn)品2為 C17=C12+C13，C18=C15;產(chǎn)品3為D17=D13，D18=D15。

第4步:設(shè)置設(shè)備有效臺時約束公式。

H3 =SUMPRODUCT(B3:D3，B12:D12)，向下復制到H7，得到各設(shè)備加工三種產(chǎn)品的總臺時(見圖1H1:H7)。

第5步:設(shè)置目標函數(shù)公式。

本案例目標函數(shù)為利潤最大值，根據(jù)案例資料利潤值宜分三步計算(見圖1H9:H11):

(1)計算收入減原料費用，H9= SUMPRODUCT(B17:D17，B10:D10)

(2)計算設(shè)備費用，H10= SUMPRODUCT(G3:G7，H3:H7)

(3)計算利潤值=(收入-原料費用)-設(shè)備費用，H11=H9-H10

第6步:利用EXCEL規(guī)劃求解工具進行最優(yōu)產(chǎn)量求解。

單擊工具-規(guī)劃求解，作如下規(guī)劃求解參數(shù)設(shè)置(見圖2):

(1)目標單元格為:H11，等于最大值;

(2)可變單元格為三種產(chǎn)品的產(chǎn)量變量矩陣:B12:D16;

(3)添加的約束條件包括:

H3:H7<=E3:E7(各設(shè)備加工臺時小于等于設(shè)備有效臺時)

B17:D17=B18:D18(各產(chǎn)品A設(shè)備產(chǎn)量等于B設(shè)備產(chǎn)量)

B12:D16=int(三種產(chǎn)品的產(chǎn)量變量矩陣為整數(shù))

B12:B16>=0 (三種產(chǎn)品的產(chǎn)量變量矩陣>=0)

C15:C16=0，D12=0，D14=0，D16=0(不經(jīng)過該工序加工，產(chǎn)量為0)

(4)單擊求解得到求解結(jié)果，見圖3。

三種產(chǎn)品在A、B設(shè)備加工的產(chǎn)量分別為1432件、500件、323件，在五道工序上的產(chǎn)量具體見圖3中A11:D16，此時達到利潤最大值為1146.39元(H11)。

比較EXCEL與運籌學專門軟件的求解過程和求解結(jié)果，在EXCEL下通過補充計算單位臺時設(shè)備費用與單件利潤、設(shè)置出三種產(chǎn)品的產(chǎn)量變量矩陣、分步確定目標利潤公式等前期準備，就能較為簡捷地列示出目標函數(shù)與約束條件，本例中目標函數(shù)在EXCEL下為SUMPRODUCT(B17:D17，B10:D10)-SUMPRODUCT(G3:G7，H3:H7)，其列示過程較原公式簡單，原約束條件包括11個公式，在EXCEL下只需要5個公式;從求解結(jié)果看，二者的結(jié)果是一致的，且EXCEL的實現(xiàn)過程更為簡捷。

參考文獻:

[1]韓伯棠編著，管理運籌學[M]北京:高等教育出版社 2000 P46-48

[2] 郁玉環(huán)輔助生產(chǎn)費用代數(shù)分配法實際應(yīng)用研究會計之友2006.5

[3] 郁玉環(huán)Excel“規(guī)劃求解”在多方案組合排隊投資決策中的應(yīng)用中國管理信息化2010.3