第 19 章 規劃求解求算最大利潤 著作權所有 © 旗標出版股份有限公司

應用實例說明 每一家公司, 總是希望在有限的生產資源下, 製造出利潤最高的產品, 以獲得最大收益。以尚泉公司為例, 該公司是一家專門生產各式飲料的公司, 打算利用現有的廠房設備, 製造「梅子烏龍茶」與「無糖綠茶」這兩種新飲料。在生產研討會議之後, 高層決定每月使用 800 公克的原料, 並分配 650 個小時的機器運轉時間來生產這兩種新產品。

應用實例說明 製造部門目前已經計算出來, 每生產一打的「梅子烏龍茶」, 需要機器運轉 2 小時、耗費原料 3.2 公克, 並可獲得 100 元的毛利；生產一打「無糖綠茶」則需要機器運轉 3.5 小時、耗費原料 4 公克, 並可獲得 130元的毛利。現在要面臨的問題就是, 到底尚泉公司每個月應該生產幾打的「梅子烏龍茶」和「無糖綠茶」, 才能賺取最大的利潤呢？

本章提要 分析規劃求解問題 進行規劃求解 變更規劃求解條件 建立規劃求解報表

分析規劃求解問題 問題分析 建立工作表 輸入公式

問題分析 尚泉公司面對的生產問題, 看似相當複雜難解, 其實只要能將上述的生產限制化做數學條件式, 然後建入 Excel 工作表當中, 並運用規劃求解功能, 就能輕輕鬆鬆求得一個利潤最大化的解答喔！

問題分析 首先我們可以將廠房的生產條件列表如下：

問題分析 假設「梅子烏龍茶」與「無糖綠茶」的每月生產量分別應為 X 與 Y 打、總利潤為 Z, 根據上表的生產條件限制, 可以列出下面的數學式： 原料限制式：3.2X + 4Y <= 800 時間限制式：2X + 3.5Y <= 650 生產數量：X >= 0、Y >=0 求解目標：MAX Z=100X + 130 Y (求出最大利潤)

建立工作表 當然, 光是把數學式列出來還不夠, 必須設法將這些資料建立到工作表中。因此, 接下來的工作就是為此項生產計劃建立一張工作表。 請開啟範例檔案 Ch19-01, 並切換至 Sheet1 工作表：

建立工作表

輸入公式 除了輸入基本生產資料, 還有毛利、總收益…等計算公式也要建入工作表中： 梅子烏龍茶的毛利合計為梅子烏龍茶的單位毛利乘上應生產量, 因此 G4 的公式為 "=E4 * F4"；同理, G5 的公式應為 "=E5*F5"。

輸入公式 梅子烏龍茶和無糖綠茶的總實際原料用量為梅子烏龍茶的所需原料乘上應生產量, 加上無糖綠茶的所需原料乘上應生產量, 因此 C9 的公式為 "=C4*F4 + C5*F5"。同理, 實際生產時間 C10 的公式就可以設計成 "=D4*F4 + D5*F5 " 總收益 C11 的公式為梅子烏龍茶與無糖綠茶的毛利加總, 也就是"= G4 + G5"

輸入公式 請逐一將公式輸入到工作表中, 或者切換至 Ch19-01 的 Sheet2：

進行規劃求解 規劃求解的意義 規劃求解的條件設定 執行求解

規劃求解的意義 在使用規劃求解功能前, 必須釐清您的求解目標, 看是希望目標值越大越好或是越小越好, 或者等於某特定的數值, 然後設定好變數儲存格與條件限制式。接著再根據您的設定目標, 運用規劃求解為目標儲存格的公式 (即本例的 C11 儲存格) 尋找一個最符合求解目標的值。

規劃求解的條件設定 現在, 我們就要正式進入規劃求解的階段了。請您依序跟著底下的說明進行操作： 請選取 Sheet2 工作表的 C11 儲存格, 然後執行『工具/規劃求解』命令, 開啟規劃求解參數交談窗：

安裝『規劃求解』功能 規劃求解是 Excel 的一項增益功能, 如果拉下工具功能表, 沒有看見『規劃求解』命令, 請將 Office 光碟片放入光碟機並執行『工具/增益集』命令, 勾選規劃求解項目後按下確定鈕來進行安裝。

規劃求解的條件設定

規劃求解的條件設定 當規劃求解的變數儲存格有一個以上的時候, 須以 "," 隔開。在本例中,變數儲存格恰為連續的範圍, 因此也可設定為 "$F$4：$F$5"。 按下交談窗中的新增鈕, 開啟新增限制式交談窗來進行如下的限制式設定：

規劃求解的條件設定

規劃求解的條件設定 接著繼續按下新增限制式交談窗中的新增鈕, 設定原料配額的限制式：

限制值的設定 既然原料配額的限制是 800, 那麼我們何不在上圖的限制值欄內輸入 "800",而要設定成內容為 800 的 C7 儲存格？這樣做的目的是為了方便日後的編修工作。舉例來說, 假如未來要投入的原料變成 1000, 那麼我們就可以直接到工作表中, 把 C7 的值改成 1000, 而不需要再去修改規劃求解的限制式了。

規劃求解的條件設定 最後還有時間配額的限制, 因此請再按下新增鈕完成如下的設定：

規劃求解的條件設定 完成限制式的設定之後, 按下確定鈕, 即可回到規劃求解參數交談窗：

執行求解 現在, 請您按下求解鈕, 計算的結果即將呈現在眼前：

執行求解

執行求解 原本感到複雜的生產問題, 經由規劃求解的運算, 便可得知最佳的生產組合！尚泉公司只要利用規劃的生產資源, 每個月生產梅子烏龍茶 62.5 打、生產無糖綠茶 150 打, 就可獲得最大利潤 25,750 元。

變更規劃求解條件 修改規劃條件 修改求解限制式

修改規劃條件 假設此時高階主管修訂了部分生產計劃, 決定投入更多的原料與時間來生產梅子烏龍茶與無糖綠茶, 那麼這時候我們就要到工作表中修改數據, 然後重新進行求解了。 請開啟範例檔案 Ch19-03：

修改規劃條件

修改規劃條件 由於應生產量和總收益是透過規劃求解功能所計算出來的, 因此當我們更新工作表中的資料後, 還必須選定目標儲存格 C11, 然後重新執行『工具/規劃求解』命令, 才能得到新的求解結果喔！

修改規劃條件

修改求解限制式 還有另外一種狀況是要修改規劃求解的限制式, 例如主管希望每月固定生產梅子烏龍茶 100 打, 然後剩餘的資源再來生產無糖綠茶, 那麼這時候就必須要修改規劃求解的限制式囉！請開啟範例檔案 Ch19-04,選定 Sheet1 的 C11 儲存格後執行『工具/規劃求解』命令：

修改求解限制式

修改求解限制式 回到變更限制式交談窗後, 請按下取消鈕即可在規劃求解參數交談窗中看到限制式已修改成功, 您只要按下求解鈕, 就可獲得新的運算結果：

修改求解限制式

設定整數限制式 以本範例來說, 假如在生產飲料的時候都是以一打、一打為單位來製造,也就是希望計算出來的應生產量為整數的話,這時候就必須再新增 如下的限 制式：

建立規劃求解報表 產生報表 檢視報表

產生報表 請您開啟範例檔案 Ch19-05, 選取 C11 儲存格並執行『工具/規劃求解』命令, 接著按下規劃求解參數交談窗中的求解鈕後, 跟著底下的步驟來建立報表：

產生報表

產生報表

檢視報表 若要檢視報表, 只需切換至該工作表中即可。底下一一檢視剛才產生的 3 種規劃求解報表： 運算結果報表 敏感度報表 極限值報表

運算結果報表 運算結果報表會列出目標儲存格及變數儲存格的初值、終值、及參數限制式的公式內容：

運算結果報表

敏感度報表 敏感度報表提供有關目標儲存格的公式或限制式中微小變化的敏感度資訊。

極限值報表 極限值報表列出目標儲存格和變數儲存格的數值、上下限, 以及對應的目標值。