在Excel中使用加載宏進(jìn)行規(guī)劃求解的方法

1、啟動(dòng)Excel并打開工作表，如圖1所示。在“文件”選項(xiàng)卡中選擇“選項(xiàng)”選項(xiàng)打開“Excel選項(xiàng)”對(duì)話框，在左側(cè)列表中選擇“加載宏”選項(xiàng)，在右側(cè)單擊“轉(zhuǎn)到”按鈕，如圖2所示。

圖1　打開工作表

圖2　單擊“轉(zhuǎn)到”按鈕

2、在打開的“加載宏”對(duì)話框的“可用加載宏”列表中勾選“規(guī)劃求解加載項(xiàng)”復(fù)選框，如圖3所示。單擊“確定”按鈕關(guān)閉“加載宏”對(duì)話框。

圖3　勾選“規(guī)劃求解加載項(xiàng)”復(fù)選框

3、在“數(shù)據(jù)”選項(xiàng)卡的“分析”組中單擊“規(guī)劃求解”按鈕打開“規(guī)劃求解參數(shù)”對(duì)話框，在對(duì)話框的“設(shè)置目標(biāo)”文本框中指定D5單元格，選擇“最小值”單選按鈕，將“通過(guò)更改可變單元格”指定為表格中“數(shù)量”數(shù)據(jù)所在的單元格區(qū)域，即B2至B5單元格區(qū)域，單擊“添加”按鈕，如圖4所示。

圖4　打開“規(guī)劃求解參數(shù)”對(duì)話框

提示

規(guī)劃求解是一個(gè)可以執(zhí)行反復(fù)試驗(yàn)搜索的優(yōu)化引擎，其通過(guò)優(yōu)化模型計(jì)算得出一個(gè)或多個(gè)變量的值。優(yōu)化模型包括3個(gè)部分：目標(biāo)單元格、可變單元格和約束條件。目標(biāo)單元格是要優(yōu)化的最終結(jié)果所在的單元格，在該單元格中必須按照數(shù)據(jù)關(guān)系建立與可變單元格關(guān)聯(lián)的公式，其目標(biāo)值有最大值、最小值和目標(biāo)值這3種情況；可變單元格為需要求解的一個(gè)或多個(gè)未知數(shù)，其被目標(biāo)單元格公式所引用，必須直接或間接與目標(biāo)單元格相關(guān)，其最多可以指定200個(gè)可變單元格；約束條件是對(duì)目標(biāo)單元格和可變單元格的限制條件，約束條件通過(guò)“添加約束”對(duì)話框來(lái)添加。

4、此時(shí)將打開“添加約束”對(duì)話框，在對(duì)話框的“單元格引用”文本框中指定“產(chǎn)品甲”的“數(shù)量”數(shù)據(jù)所在的單元格B2，在中間的下拉列表中選擇“>=”選項(xiàng)，在右側(cè)的“約束”文本框中輸入數(shù)據(jù)“60”，完成設(shè)置后單擊“添加”按鈕，如圖5所示。使用相同的方法添加其他的約束條件，這些條件將添加到“規(guī)劃求解參數(shù)”對(duì)話框的“遵守約束”列表中，如圖6所示。

圖5　添加約束條件

圖6　在“遵守約束”列表中添加約束條件

提示

“規(guī)劃求解參數(shù)”對(duì)話框的“選擇求解方法”下拉列表提供了3種求解方法供用戶選擇。其中，“非線性GRG”選項(xiàng)用于解決平滑非線性問(wèn)題，“單純線性規(guī)劃”選項(xiàng)用于解決線性問(wèn)題，“演化”選項(xiàng)用于解決非平滑問(wèn)題。

在對(duì)話框中單擊“選項(xiàng)”按鈕將打開“選項(xiàng)”對(duì)話框，該對(duì)話框包含“對(duì)約束精確度”、“具有整數(shù)約束的求解”和“求解極限值”等設(shè)置項(xiàng)供用戶選擇，同時(shí)用戶也可以對(duì)非線性GRG和演化等求解方法進(jìn)行設(shè)置。

5、完成約束條件添加后，單擊“求解”按鈕。此時(shí)將打開“規(guī)劃求解結(jié)果”對(duì)話框，在對(duì)話框中選擇“保留規(guī)劃求解的解”單選按鈕，單擊“確定”按鈕關(guān)閉該對(duì)話框，如圖7所示。

圖7　“規(guī)劃求解結(jié)果”對(duì)話框

6、此時(shí)在工作表中將顯示規(guī)劃求解的結(jié)果，這里將能夠得到生產(chǎn)成本最低的時(shí)候的產(chǎn)品生產(chǎn)數(shù)量，如圖8所示。

圖8　顯示求解結(jié)果