［摘 要］ Excel電子表格軟件以其靈活、易用、方便的特點(diǎn)，在企業(yè)管理中發(fā)揮著重要作用。在生產(chǎn)與庫(kù)存決策中應(yīng)用其規(guī)劃求解的方法，快速建立求解模型，可以使企業(yè)管理者減輕勞動(dòng)強(qiáng)度，并得到及時(shí)、準(zhǔn)確的企業(yè)決策信息。

［關(guān)鍵詞］ Excel；生產(chǎn)；庫(kù)存；規(guī)劃求解

［中圖分類號(hào)］F232［文獻(xiàn)標(biāo)識(shí)碼］A［文章編號(hào)］1673-0194（2007）04-0005-03

在信息社會(huì)中，利用方便的計(jì)算機(jī)軟件，來(lái)解決企業(yè)生產(chǎn)、管理中的難題已成為一種趨勢(shì)和必然。通過(guò)使用簡(jiǎn)單的Excel電子表格軟件，就可以將手工中復(fù)雜的數(shù)學(xué)計(jì)算轉(zhuǎn)為簡(jiǎn)單的對(duì)單元格公式的確定，即：用計(jì)算機(jī)為企業(yè)管理建模。利用Excel 2000提供的規(guī)劃求解功能可以解決作業(yè)研究的多種問(wèn)題，諸如線性規(guī)劃、運(yùn)輸問(wèn)題、人事安排等，只要與生產(chǎn)、制造、分配、財(cái)務(wù)、工程等有關(guān)的求最大利潤(rùn)、最小費(fèi)用等問(wèn)題均可使用規(guī)劃求解法找到答案。在此將以企業(yè)管理中的“生產(chǎn)—庫(kù)存”模型為依據(jù)，闡明如何將Excel更好地應(yīng)用于企業(yè)管理之中。

設(shè)某廠計(jì)劃全年生產(chǎn)某種產(chǎn)品，其4個(gè)月的訂貨量分別為600件，700件，500件和1 200件。已知生產(chǎn)該產(chǎn)品的生產(chǎn)費(fèi)用與產(chǎn)品件數(shù)的平方成正比，其比例系數(shù)為0.005，廠內(nèi)有倉(cāng)庫(kù)可以存放未銷售掉的產(chǎn)品，其存儲(chǔ)費(fèi)為每件每月1元。求該廠應(yīng)如何確定最優(yōu)的生產(chǎn)計(jì)劃，使企業(yè)能夠按時(shí)完成訂貨任務(wù)并且總的生產(chǎn)費(fèi)用與庫(kù)存費(fèi)用最低。

在手工計(jì)算企業(yè)的“生產(chǎn)—庫(kù)存”最優(yōu)決策中，可以運(yùn)用管理控制中的動(dòng)態(tài)規(guī)劃法來(lái)解決，即先建立管理系統(tǒng)的狀態(tài)方程，寫(xiě)出它的目標(biāo)函數(shù)，然后應(yīng)用動(dòng)態(tài)規(guī)劃的基本方程遞推求解。在解題過(guò)程中，列出相鄰兩步目標(biāo)函數(shù)的最優(yōu)值之間的遞推關(guān)系式，但是在計(jì)算過(guò)程中其遞推數(shù)據(jù)復(fù)雜而且數(shù)據(jù)量大，比較容易犯錯(cuò)誤，從而得不到正確的結(jié)論。

如果考慮用計(jì)算機(jī)建立相應(yīng)的模型，則其處理過(guò)程如下。

1 整理模型所需數(shù)據(jù)

（1） 據(jù)題意可知此模型的決策變量是各個(gè)月的最優(yōu)生產(chǎn)量。

（2） 各月庫(kù)存量是與期初數(shù)量和本期生產(chǎn)、需求數(shù)量相關(guān)的變量。

（3） 本問(wèn)題的目標(biāo)函數(shù)是生產(chǎn)與庫(kù)存的總費(fèi)用最小。

（4） 本問(wèn)題的約束條件是：各個(gè)月的可供量應(yīng)大于或等于各月的需求量。

2 用Excel建立模型

（1） 錄入初始數(shù)據(jù)

首先設(shè)計(jì)表格單元內(nèi)容，將上例中已有的可知條件錄入表格單元中（見(jiàn)圖1）。

（2） 錄入公式

將事先設(shè)計(jì)的每個(gè)計(jì)算公式、約束條件公式錄入到各個(gè)單元格中。在本例題中，應(yīng)該在B7單元格中錄入最小成本公式：0.005*(B4*B4+C4*C4+D4*D4+E4*E4)+SUM(B5:E5)，在B9~E9中錄入各約束條件公式，在全部公式及數(shù)據(jù)錄入完畢之后，形成的模型畫(huà)面如圖2所示。

在Excel中，一般只顯示各個(gè)單元格的內(nèi)容，如果想要顯示各個(gè)單元格中的公式，則可以點(diǎn)擊“工具”菜單選項(xiàng)，在菜單項(xiàng)中選取“選項(xiàng)”，并在“視圖”選項(xiàng)卡中點(diǎn)擊“公式”復(fù)選框，則返回Excel主畫(huà)面時(shí)，畫(huà)面則顯示如圖2所示的各個(gè)單元格公式。

這里需要注意的問(wèn)題：一是公式的位置，必須與要計(jì)算的指標(biāo)在同一行；二是公式的寫(xiě)法，如果引用其他單元格的資料要用該單元格的地址來(lái)表示，至于用絕對(duì)單元格還是用相對(duì)單元格則視需要而定，一般用相對(duì)單元格即可。

3 求最優(yōu)解

點(diǎn)擊Excel中的“工具”菜單選項(xiàng)，在其下拉菜單中選取“規(guī)劃求解”，在該對(duì)話框中輸入目標(biāo)單元格地址、可變單元格地址、約束條件參數(shù)地址。在本例題中，對(duì)約束條件參數(shù)的選擇，還要考慮所求變量的整數(shù)特征，因?yàn)楫a(chǎn)量件數(shù)不能為小數(shù)，所以在約束條件中添加一項(xiàng)指定單元格中的數(shù)據(jù)為整數(shù)（見(jiàn)圖3）。

選擇目標(biāo)函數(shù)為“最小值”；單擊“選項(xiàng)”按鈕，在“選項(xiàng)”對(duì)話框中，選擇“假定非負(fù)”；最后單擊“求解按鈕”，即可求出最優(yōu)解。其總體設(shè)置如圖4所示：

其最優(yōu)解為每月生產(chǎn)量為600，700，800，900，最小成本為11 800，如圖5所示。

4 模型的應(yīng)用

在上述模型建立以后，如果企業(yè)中的數(shù)據(jù)發(fā)生變化，則企業(yè)管理人員可以在此模型的基礎(chǔ)上，修改相應(yīng)的數(shù)據(jù)，可以得到其對(duì)應(yīng)的最優(yōu)解，從而避免了手工計(jì)算的復(fù)雜、易出錯(cuò)的缺點(diǎn)。在本例中，可以改變的參數(shù)有企業(yè)每個(gè)期間的訂貨量、生產(chǎn)函數(shù)與生產(chǎn)費(fèi)用的關(guān)系式、庫(kù)存費(fèi)用與庫(kù)存量的關(guān)系式。如：當(dāng)企業(yè)4個(gè)月的訂貨量分別為200，500，600，100時(shí)，則最優(yōu)生產(chǎn)計(jì)劃應(yīng)為每個(gè)月分別生產(chǎn)333、433、534和100件，4個(gè)月的總生產(chǎn)與庫(kù)存費(fèi)用最低為3 166.67（見(jiàn)圖6）。

當(dāng)然，在條件發(fā)生重大變化時(shí)，可以考慮重新設(shè)計(jì)一個(gè)模型。

5 模型的保護(hù)措施

為了防止已輸入單元格中的公式被誤刪、誤改或人為篡改，為了防止數(shù)據(jù)的泄密和修改，同時(shí)為了工作表下次繼續(xù)使用，可以將表的格式，即各項(xiàng)目單元格和帶有公式的單元格設(shè)定密碼保護(hù)。如果擬不顯示公式，還可將公式予以隱藏。其做法是：首先將某些單元格排除在保護(hù)范圍之外，即：選定—格式—單元格—保護(hù)—對(duì)上述單元格選定為不“鎖定”；然后，再對(duì)擬保護(hù)的帶有公式的單元格設(shè)定為鎖定、隱藏，方法同上；最后，再對(duì)工作表進(jìn)行保護(hù)，即：工具—保護(hù)—保護(hù)工作表—選項(xiàng)—選擇對(duì)話框的“內(nèi)容”等項(xiàng)—輸入密碼—確定—再輸入密碼—確定。這樣就實(shí)現(xiàn)了保護(hù)。當(dāng)下次再打開(kāi)時(shí)，必須輸入密碼。當(dāng)然，對(duì)整個(gè)文件還可以設(shè)定打開(kāi)口令和修改權(quán)限口令保護(hù)。

主要參考文獻(xiàn)

[1] 丁以中，Jennifer S.Shang. 管理科學(xué)——運(yùn)用Spreadsheet建模和求解[M]. 北京:清華大學(xué)出版社，2003.

[2] 鐘愛(ài)軍.Excel在邊際成本規(guī)劃中的應(yīng)用[J]. 中國(guó)管理信息化，2006，(6).