使用規劃求解決定最佳化產品集合

附註:  我們想要以您的語言,用最快的速度為您提供最新的說明內容。 本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。讓這些內容對您有所幫助是我們的目的。希望您能在本頁底部告訴我們這項資訊是否有幫助。 此為 英文文章 出處,以供參考。

本文說明如何使用規劃求解、 Microsoft Excel 增益集程式您可以使用的模擬分析,來判斷最佳化產品集合。

如何判斷最大化獲利率的每月產品混合?

公司通常需要判斷來產生每個月每個產品的數量。產品混合問題最簡單的形式,包括如何判斷應在一個月最大化利潤產生每個產品的。下列限制式通常必須遵守產品集合:

  • 產品集合無法使用更多資源比可供使用。

  • 有每個產品有限的需求。我們無法產生多個產品在一個月比 demand 即表示,因為過多生產被浪費 (例如,付款方式製藥)。

現在就讓我們現在解決下列產品集合問題的範例。您可以顯示圖 27-1 的檔案 Prodmix.xlsx,尋找解決這個問題。

書籍圖像
圖 27-1 的產品組合

例如,假設很適合製藥產生工廠六個不同的產品。每個產品的生產需要工時與資源。列 4 圖 27-1 顯示的每個產品的磅所需的工時的時數和列 5 會顯示資源的每個產品磅所需的磅。例如,產生的產品 1 井字號需要六個小時的工時與 3.2 磅的資源。每項,井字號單價已列在列 6、 井字號每單位成本指定,第 7 列中,列 9 中指定的利潤比重每井字號。例如,產品 2 $11.00 每井字號的銷售、 單位成本為 $5.70 每井字號,而佔 $ 5.30 元的獲利井字號。每項月份的要求已列在 [資料列 8。例如,產品 3 的是 1041 磅。本月份,4500 小時的工時與 1600 磅的資源可供使用。如何能夠此公司最大化每月獲利?

如果我們知道 Excel 規劃求解的任何動作,我們會攻擊建構追蹤與資源使用狀況相關聯的產品組合,工作表的這個問題。然後我們將使用試用版和錯誤,視情況而定,而不使用更多的人力或資源比可供使用,也不產生超過需求的任何製藥最佳化 profit 產品集合。我們使用此程序中的 [規劃求解只能在試用版錯誤階段。基本上,規劃求解是一種最佳化引擎,能夠完美地執行試驗錯誤搜尋。

若要解決產品集合問題金鑰是以有效率地計算的資源使用狀況及任何產品集合相關聯的利潤。非常重要的工具,我們可以用來進行計算的是 SUMPRODUCT 函數。SUMPRODUCT 函數會將儲存格範圍中的對應值相乘,並傳回的值總和。使用 SUMPRODUCT 評估中每個儲存格範圍必須有相同的維度,其中隱含,您就可以使用 SUMPRODUCT,兩個資料列的兩個資料行,但不是含一欄和一列。

做為範例,我們如何使用 SUMPRODUCT 函數在產品的集合的範例,讓我們試著計算我們資源使用狀況]。我們的工時使用的計算方式

(工時數製藥 1) *(Drug 1 pounds produced) +
(使用每個井字號的 2 製藥工時) * (藥品 2 磅) +...
(使用數製藥 6 工時) * (藥品 6 磅)

我們可能會計算為更冗長的方式的工時使用D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4。同樣地,資源使用狀況可能會計算D2 * D5 + E2 *E5 + F2 * F5 + G2 * G5 + H2 * H5 + I2 * i5 中。不過,六個產品的工作表中輸入下列公式是耗時。假設您想要多久如果您已使用的公司,例如,產生工廠 50 產品。從 D14 複製到 D15 公式SUMPRODUCT($D$2:$I$2,D4:I4)可更容易的方式計算工時和資源使用狀況。此公式會計算D2 * D4 + E2 * E4 + F2 * F4 + G2 * G4 + H2 * H4 + I2 * I4 (這是我們的工時使用),但會更容易輸入 !請注意,我該使用 $ 符號範圍 d2: i2,好讓我複製公式時仍擷取列 2 的產品組合。儲存格 D15 中的公式會計算資源的使用方式。

以類似的方式,我們 profit 取決於

(藥品 1 的利潤每磅) * (產生製藥 1 磅) +
(製藥 2 利潤每磅) * (藥品 2 磅) +...
(製藥 6 利潤每磅) * (藥品 6 磅)

利潤輕鬆地計算儲存格 D12 SUMPRODUCT(D9:I9,$D$2:$I$2)的公式。

我們現在可以找出產品集合規劃求解的三個元件。

  • 目標儲存格。我們的目標是最大利潤 (在儲存格 D12 計算)。

  • 變更儲存格。磅數所產生的每個產品 (列在儲存格範圍 d2: i2)

  • 限制式。下列限制式,我們有:

    • 不要使用更多的人力或資源大於可用。就是儲存格 d14: d15 (使用的資源) 中的值必須小於或等於在儲存格 f14: f15 (可用資源) 值。

    • 不會產生多個非要求製藥。就是儲存格 d2: i2 (磅的每項) 的值必須小於或等於至 (列在儲存格 D8:I8) 每項需求。

    • 我們無法產生任何製藥負值的數量。

我會告訴您怎麼目標儲存格輸入到 [規劃求解變更儲存格及限制。然後您只需要是按一下 [求解] 按鈕,若要尋找的利潤最大化產品混合 !

若要開始,請按一下 [資料] 索引標籤,然後在 [分析] 群組中,按一下 [規劃求解。

附註: 「 簡介若要最佳化與 Excel 規劃求解 」 章節 26 所述規劃求解已安裝 Microsoft Office 按鈕],然後 Excel 選項],後面接著增益集] 即可。在 [管理] 清單中,按一下 [Excel 增益集] 核取 [規劃求解增益集] 方塊中,,再按一下 [確定]。

會出現 [規劃求解參數] 對話方塊,如下圖所示圖 27 為 2。

書籍圖像
圖 27 2 [規劃求解參數] 對話方塊

按一下 [設定目標儲存格] 方塊,然後選取 [我們獲利儲存格 (儲存格 D12)。按一下 [變更儲存格] 方塊,然後指向 [範圍 d2: i2,其中包含磅的每項]。對話方塊現在看起來應該圖 27-3。

書籍圖像
圖 27 3 與目標儲存格變更定義的儲存格 [規劃求解參數] 對話方塊

現在,我們準備好要新增至模型的限制式。按一下 [新增] 按鈕。您會看到 [新增限制式] 對話方塊,顯示圖 27-4。

書籍圖像
圖 27 4 新增限制式] 對話方塊

若要新增資源使用限制式,按一下 [儲存格參照] 方塊,然後選取範圍 d14: d15。選取 [< = 從中間名的清單。按一下 [限制式] 方塊,然後選取 [儲存格範圍 f14: f15。[新增限制式] 對話方塊現在看起來應該像圖 27-5。

書籍圖像
圖 27 5 輸入資源使用狀況限制式的 [新增限制] 對話方塊

我們完現在的規劃求解時嘗試不同的值,變更的儲存格,只滿足兩者的組合D14 < = F14 (使用工時是小於或等於可用工時) 和D15 < = F15 (用 raw 材料小於或等於會被視為資源可用)。按一下 [新增] 以輸入需求限制式]。如下圖所示圖 27-6,新增限制式] 對話方塊中的 [填滿。

書籍圖像
圖月 27 日-6 日新增限制式] 對話方塊輸入需求限制

新增這些限制,以確保當規劃求解嘗試不同的變更儲存格的值組合,會被視為滿足下列參數的組合:

  • D2 < = D8(產生製藥 1 的小於或等於製藥 1 的)

  • E2 < = E8(量所產生的毒品 2 是小於或等於製藥 2)

  • F2 < = F8(產生製藥 3 所做的小於或等於製藥 3 的)

  • G2 < = G8(產生製藥 4 所做的小於或等於製藥 4 的)

  • H2 < = H8(產生製藥 5 所做的小於或等於製藥 5 的)

  • I2 < = I8(產生製藥 6 所做的小於或等於製藥 6 的)

按一下 [新增限制式] 對話方塊中的 [確定]。[規劃求解] 視窗中看起來應該像圖 27-7。

書籍圖像
圖 27-7 最終規劃求解參數] 對話方塊的產品集合問題

我們輸入必須變更儲存格的限制式規劃求解選項] 對話方塊中的非負數。按一下 [規劃求解參數] 對話方塊中的 [選項] 按鈕。核取 [線性模式] 方塊和 [採用非負值] 方塊中,如下圖 27-8 下一個頁面上。按一下 [確定]。

書籍圖像
圖 27 8 規劃求解選項設定

檢查 [採用非負值] 方塊中,可確保規劃求解會考慮只組合的變更的每個變數儲存格假設非負數值的儲存格。我們已核取 [線性模式] 方塊,因為產品混合問題是稱為 「線性模式的規劃求解問題的一種特殊類型。基本上,規劃求解模型是線性在下列情況下:

  • 目標儲存格計算加總起來表單規定(變更共同

  • 每個條件約束滿足 「 線性模式的需求。 」這表示每一個條件約束會評估加總起來表單規定(變更共同與比較總數常數。

為何線性的規劃求解問題?計算方式我們的目標儲存格 (獲利)

(藥品 1 的利潤每磅) * (產生製藥 1 磅) +
(製藥 2 利潤每磅) * (藥品 2 磅) +...
(製藥 6 利潤每磅) * (藥品 6 磅)

這項計算遵循由表單衍生目標儲存格的值的圖樣(變更共同

比較衍生自(工時數製藥 1) 的值來評估我們工時限制式 * (產生製藥 1 磅) + (工時數製藥 2) *(Drug 2 pounds produced) +...(需要我們的勞力ed 數製藥 6) * (藥品 6 磅)至可用工時。

因此,由表單的評估工時限制式(變更共同與比較總數常數。工時限制式和資源限制式滿足線性模式的需求。

我們需要限制式的形式

(製藥 1 產生) < = (製藥 1 Demand)
(產生製藥 2) < = (製藥 2 Demand)
§
(產生製藥 6) < = (製藥 6 Demand)

每個指定的條件約束也滿足線性模式的需求,因為每個藉由新增放在一起的表單評估(變更共同與比較總數常數。

無法顯示產品集合模型是線性模型,為什麼我們注意?

  • 如果是線性的規劃求解模型,而且我們選取線性模式時,[規劃求解保證找出規劃求解的最佳解決方案。如果不線性的規劃求解模型,規劃求解可能,或可能不會找到最佳解決方案。

  • 如果是線性的規劃求解模型,而且我們選取線性模式,規劃求解會使用極有效率的演算法 (simplex 方法) 若要尋找模型的最佳解決方案。如果是線性的規劃求解模型,我們未選取 [線性模式規劃求解使用非常沒有效率演算法 (GRG2 方法),並會有困難尋找模型的最佳解決方案。

後按一下 [確定],在 [規劃求解選項] 對話方塊中的,我們返回 [主要規劃求解] 對話方塊,顯示較舊版本中圖 27-7。當我們按一下 [求解時] 規劃求解會為產品集合模型計算最佳化的解決方案 (如果有的話)。中所述我章節 26,產品混合模型最佳化解決方案是一組變更儲存格的值 (磅的每項),將利潤最大化資料集的所有可行的解決方案。同樣地,可行的解決方案是一組的變更儲存格的值,能滿足所有的限制式。變更儲存格所示的值圖 27-9 是可行的解決方案,因為所有產品層級都的非負數、 產品層級不能超過需求,且資源使用狀況不超過可用的資源。

書籍圖像
圖 27 9 可行的解決方案產品混合問題符合限制式。

變更儲存格所示的值圖 27-10 下一個頁面上代表不可行的解決方案,原因如下:

  • 我們會產生多個為其指定比製藥 5。

  • 我們使用的比可用工時。

  • 我們使用更多資源比可用。

書籍圖像
圖 27 10 不可行的解決方案產品集合問題不符合定義的限制式。

後按一下 [求解],[規劃求解快速找出最佳解決方案顯示圖 27-11。您必須選取 [保留規劃求解解答,若要保留的工作表中的最佳解決方案值。

書籍圖像
圖 27-11 產品混合問題的最佳解決方案

我們製藥可以最大化每月獲利 $6,625.20 的層級,以產生生產 596.67 磅的毒品 4、 1084 磅製藥 5,及任何其他藥品 !我們無法決定是否要達到 $6,625.20 以其他方式的最大利潤。我們可以確定所有是,與我們有限的資源需求,沒有方法,讓多個 $6,627.20 本月份。

假設符合需求必須每個產品。(請參閱無可行的解決方案中的工作表檔案 Prodmix.xlsx)。然後我們若要變更的需求限制式d2: i2 < = D8:I8d2: i2 > = D8:I8。若要這麼做,請開啟 [規劃求解,請選取 d2: i2 < = D8:I8 的限制式,,然後按一下 [變更。顯示在圖 27-12] 的 [變更限制式] 對話方塊隨即出現。

書籍圖像
圖 27 12 變更限制式] 對話方塊

選取 > =,然後再按一下 [確定]。我們已經確定規劃求解會考慮變更符合所有需求的儲存格值。當您按一下 [求解] 時,您會看到訊息 「 規劃求解找不到可行的解決方案 」。我們所錯誤我們的模型,但而的做我們有限的資源,請無法滿足所有產品的需求,並不會表示這則訊息。規劃求解 」 只告訴我們是否我們想要的開會每個產品的需求,我們需要新增更多工時、 更多要素,或兩者的更多。

讓我們來看看會發生什麼情況如果我們允許每個產品的無限的需求,並允許產生的每項的負數值。(您可以看到此規劃求解問題 Prodmix.xlsx 檔案中的設定值沒有涵蓋工作表上)。若要尋找最佳解決方案這種情況下,開啟 [規劃求解、 按一下 [選項] 按鈕,然後清除採用非負值方塊。在 [規劃求解參數] 對話方塊中,選取 [要求限制式 d2: i2 < = D8:I8,然後按一下 [刪除] 以移除限制式。當您按一下 [求解時] 規劃求解傳回訊息 「 沒有涵蓋設定儲存格的值。 」此錯誤訊息表示目標儲存格的最大化 (如本例中),是否有可行的解決方案的任意大目標儲存格的值。(如果目標儲存格的最小化,「 設定儲存格的值沒有涵蓋 」 的訊息表示有可行的解決方案的任意小目標儲存格的值)。在我們的情況下,藉由使用負值生產製藥,我們生效 」 建立 「 可以用來產生任意大量其他藥品的資源。指定無限的需求,這個選項可讓我們進行不受限制的利潤。在實際的狀況,我們無法進行無限金額。總之,如果您看到 「 設定值沒有涵蓋 」,您的模型並沒有錯誤。

  1. 假設我們製藥最多可以購買最多 500 小時的工時 $1 超過每小時目前的人力成本。我們要如何最大利潤?

  2. 在製造廠房序號四個技術人員 (A、 B、 C 和 D) 會產生三個產品 (產品 1、 2 和 3)。本月份,晶片製造商可以銷售 80 單位產品 1、 50 單位的產品 2 和至少 50 單位的產品 3。技術 A 製造只產品 1 和 3。技術 B 製造只產品 1 和 2。技術 C 製造只產品 3。技術 D 製造只產品 2。產品的每個單位產生、 參與下列 profit: 產品 1,6;產品 2、 $7。和產品 3,$10。製造產品的時間 (以小時) 每個技術需求如下:

    產品

    技術人員 A

    技術人員 B

    技術人員 C

    技術人員 D

    1

    2

    2.5

    無法執行

    無法執行

    2

    無法執行

    3

    無法執行

    3.5

    3

    3

    無法執行

    4

    無法執行

  3. 每個技術就可以使用 120 小時,每個月。如何能夠晶片製造商最大化每月獲利?假設可以產生小數的單位。

  4. 滑鼠與鍵盤,視訊遊戲搖桿,則會產生製造廠房的電腦。下表提供每單位的利潤每單位的工時使用、 每月的需求,與每單位機器時間使用方式:

    滑鼠

    鍵盤

    搖桿

    利潤/單位

    $ 8

    $ 11

    $ 9

    工時使用/單位

    .2 小時

    .3 小時

    .24 小時

    機器時間/單位

    .04 小時

    .055 小時

    .04 小時

    每月需求

    15,000

    27,000

    11000

  5. 每個月,總計 13000 工時小時和 3000 小時的機器時間可供使用。如何能夠製造商最大化植物其每月獲利的貢獻?

  6. 解決我們製藥範例,假設,必須符合的每項 200 單位的最低需求。

  7. Jason 可讓菱形 bracelets、 necklaces,且 earrings。他想要使用的每個月的 160 小時最大值。他有 800 盎司的方塊。利潤人力時間與盎司如何每個產品的如下。如果不受限制每個產品的需求,如何可以 Jason 最大化他獲利?

    產品

    單位 profit

    每單位工時

    盎司數菱形每單位

    手鐲

    $300

    .35

    1.2

    項鍊

    $200

    .15

    .75

    Earrings

    $100

    .05

    .5

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×