EXCEL規(guī)化求解功能詳解

曾少晶

【摘 要】隨著OFFICE辦公軟件的日益更新，OFFICE辦公軟件依然能站在市場的前言，這不能不歸功于它的功能強大。尤其是其中的EXCEL，原來只是用到他的許多統(tǒng)計的功能，沒想到他還可以解決很多復雜的數學問題，如果我們用常規(guī)的函數去做的話非常麻煩，但用它的規(guī)化求解功能問題便獨迎刃而解，非常方便快捷。對于今天的社會來說很是實用，因為今天我們面臨的選擇很多，商家企業(yè)會考慮如何能用最小的成本（人員費用、材料費用、廠房費用、機器費用等）達到最大的收益，各行各業(yè)都面臨這種多個變量如何實現最優(yōu)的問題，這個用數學方法來計算都需要很長時間，但用EXCEL兩分鐘就可以出結果，讓人興奮。本文就它的這個功能做一詳細的介紹。

【關鍵詞】EXCEL；規(guī)化求解

大學教程中線性代數中有講到，就是滿足給定條件下，求變量如何分配可以取得函數的極值。在實際生活中應用非常廣泛，如求如何在各種成本（人力、原材料、水電費等）最低的情況下去實現利潤最大化，如果公司只生產一種產品，不用算只需投產；但如果是多種產品，那就需要一系列復雜的運算，考慮每一種產品的消耗；綜合考慮每一種因素；最終才可以得出如何投入可以獲得最大的利潤。但在EXCEL中卻可以很容易的得到答案。不管是EXCEL2003，還是2007，還是2010，雖然版本不同，但都能運用規(guī)化求解功能來解決實際的問題。本文就EXCEL的規(guī)化求解的一實例具體來探討它的好處與應用。

例如某公司生產和銷售兩種產品，兩種產品一個單位各需要工時4.2小時和7.3小時，用電量4.1千瓦和5.3千瓦，需要原材料8公斤和4.6公斤。公司可提供的工時為300小時，可提供的電量為250千瓦，可提供的原材料為420公斤。兩種產品的單位利潤分別為190元和220元。該公式如何安排兩種產品的生產才能獲得最大的利潤。

諸如此類問題，可以再加入產品，再增加條件，但形式一樣。我們首先根據題目內容，讀董題意，題目是想在不超出給定工時、電量、原材料的情況下，如何安排兩種產品的生產可能得到最大利潤。

設第一種產品投入X1件，第二種產品投入X2件，最大利潤為Y，列如下方程，在滿足前三個條件的情況下去求Y的最大值：

4.2X+7.3

X300

4.1X+5.3

X300

8X+4.6

X300

Y=

190X+220

X

這是我們在數學上常見的方程求解，如果通過數學的方法，我們得通過一系列的運算才能得到最終結果，但利用EXCEL卻很方便，那么如何利用EXCEL來解決這個問題呢？

第一步：首先要在EXCEL中添加規(guī)化求解的功能，不同版本添加方法略有不同。

如果你的Excel是2003版得話，步驟如下：

（1）打開Excel程序，選擇“工具”→“加載宏”。

（2）在“規(guī)化求解的復選框中打上“√”，然后點擊確定。

（3）這時再打開“工具”菜單欄，就能找到“規(guī)劃求解”了。

如果你使用的是Excel2007，那么找到規(guī)劃求解的步驟如下：

（1）首先找到“Excel選項”。

（2）然后在“加載項”里面找到“轉到”，點擊“轉到”。

（3）在“規(guī)劃求解加載項”前面的復選框中打上“√”，然后點擊確定。

（4）最后在“數據”選項卡的“分析”組里面能找到它。

如果你使用的是Excel2010，找到規(guī)劃求解的步驟如下：

（1）首先選擇“文件”找到打開里面的“選項”。

（2）選中“自定義功能區(qū)”，在這兩列的右列找到“開發(fā)工具”選項卡，并在它前面的復選框中打上“√”，然后點擊確定。

（3）然后在“開發(fā)工具”選項卡中“加載項”組里面點擊“加載項”。

（4）點擊“規(guī)劃求解加載項”前面的復選框，然后點擊“確定”。

（5）然后像在2007中一樣，去“數據”選項卡找到它。

第二步：打開規(guī)化求解對話框，填入相應的內容，不同版本略有不同，但基本的內容不變，在此以EXCEL2010為例來講解：

說明：圖中的A1單元格相當于變量X1；

圖中的A3單元格相當于變量X2；

圖中的C2單元格相當于函數Y；

選中C2單元格，打開規(guī)化求解對話框，如上圖所示，為了區(qū)別，我把變量單元格填充為黃色，目標單元格填充為紫色。

然后填寫設置目標文本框如上圖示，本題中到項選最大值，設置可變單元格如上圖所示，遵守約束如上圖所示。

最后點確定就可以看到A1，A3，C2中出現結果。

此處需要說明的是遵守約束填寫時注意的幾點：

（1）要將原方程中的不等式變形，換算成一邊只留一個的X1樣式，然后填入。

（2）因為題目中X1和X2為產品，所以一定要加上它們是整數的條件，否則得出的結果可能是非整數。

（3）如果不小心輸錯了可根據右邊的按鈕進行更改或刪除。

以上便是利用規(guī)化求解來解決實際問題一個很好的例子，類似這種多個變量求最優(yōu)值的問題很多，只要我們思路清楚，先根據題目要求列出數學上的線性規(guī)化方程，然后再利用EXCEL的規(guī)化求解功能去做，只需一鍵就可以看到我們想要的結果，非常方便快捷。希望本文能對使用這個功能的人有所幫助。