
1 非線性規劃數學模型
對實際規劃問題作定量分析,必須建立數學模型。建立數學模型首先要選定適當的目標變量和決策變量,并建立起目標變量與決策變量之間的函數關系,稱之為目標函數。然后將各種限制條件加以抽象,得出決策變量應滿足的一些等式或不等式,稱之為約束條件。非線性規劃問題的一般數學模型可表述為求未知量x1,x2,…,xn,使滿足約束條件:
gi(x1,…,xn) ≥ 0 i = 1,…,m
hj(x1,…,xn) = 0 j = 1,…,p
并使目標函數f(x1,…,xn)達到最小值(或最大值)。其中:諸gi和諸hj都是定義在n維向量空間Rn的某子集D(定義域)上的實值函數,且至少有一個是非線性函數。
上述模型可簡記為:
min f(x)
s.t. gi(x) ≥ 0 i = 1,…,m
hj(x) = 0 j = 1,…,p
其中x = (x1,…,xn)屬于定義域D,符號min表示“求最小值”,符號s.t.表示“受約束于”。
定義域D中滿足約束條件的點稱為問題的可行解。全體可行解所成的集合稱為問題的可行集。對于一個可行解x*,如果存在x*的一個鄰域,使目標函數在x*處的值f(x*)優于(指不大于或不小于)該鄰域中任何其他可行解處的函數值,則稱x*為問題的局部最優解(簡稱局部解)。如果f(x*)優于一切可行解處的目標函數值,則稱x*為問題的整體最優解(簡稱整體解)。
2 關于Excel規劃求解
“規劃求解”是一組命令的組成部分,這些命令有時也稱作假設分析 (假設分析:該過程通過更改單元格中的值來查看這些更改對工作表中公式結果的影響。例如,更改分期支付表中的利率可以調整支付金額。)工具。借助“規劃求解”,可求得工作表上某個單元格(被稱為目標單元格)中公式 (公式:單元格中的一系列值、單元格引用、名稱或運算符的組合,可生成新的值。公式總是以等號 (=) 開始。)的最優值。“規劃求解”將對直接或間接與目標單元格中公式相關聯的一組單元格中的數值進行調整,最終在目標單元格公式中求得期望的結果。“規劃求解”通過調整所指定的可更改的單元格(可變單元格)中的值,從目標單元格公式中求得所需的結果。在創建模型過程中,可以對“規劃求解”模型中的可變單元格數值應用約束條件 (約束條件:“規劃求解”中設置的限制條件。可以將約束條件應用于可變單元格、目標單元格或其他與目標單元格直接或間接相關的單元格。),而且約束條件可以引用其他影響目標單元格公式的單元格。
使用“規劃求解”可通過更改其他單元格來確定某個單元格的最大值或最小值。使用規劃求解的操作方法為:
在“工具”菜單上,單擊“規劃求解”。
如果“規劃求解”命令沒有出現在“工具”菜單中,則需要安裝“規劃求解”加載宏 (加載項:為 Microsoft Office 提供自定義命令或自定義功能的補充程序。)程序。操作方法:
在“工具”菜單上,單擊“加載宏”。
如果在“當前加載宏”列表框中沒有所需加載宏 (加載項:為 Microsoft Office 提供自定義命令或自定義功能的補充程序。),請單擊“瀏覽”按鈕,再找到該加載宏。
在“當前加載宏”框中,選中待裝載的加載宏旁邊的復選框,再單擊“確定”。
3 案例資料
某公司生產和銷售甲乙兩種產品,兩種產品各生產一個單位需要工時為4和8,用電量5 kW和6 kW,需要原材料10 kg和5 kg。公司可提供的工時為320,可提供的用電量為260 kW,可提供的原材料為430 kg。兩種產品的單價與銷量之間存在負的線性關系,分別為p1=3 100 - 55q1, p2 = 3 350- 85q2 。當原料用量≥320 kg時,供應商提供的原料價格從180元降為160元。假設兩種產品各生產1個單位,總固定成本10 000元,試在Excel中建立產品組合非線性規劃模型,并且按如下要求操作:計算需要的工時、用電量、原材料和利潤;用規劃求解工具求解兩種產品的最優生產量和總利潤最大值。并用控件表示出兩種產品產量不同組合情況下的利潤變化情況。
4 模型建立
(1) 計算各項指標
在SHEET 1工作表中將已知各項指標填入相關單元格中,并進行相關計算。如圖1所示。
B15 = C10 + D10 - C13 - C12 - D12。
(2) 進行規劃求解
單擊“工具”菜單上的“規劃求解”,在彈出的 “規劃求解參數” 對話框中作如圖2的設置。
在“設置目標單元格”框中單擊,然后選擇利潤單元格(單元格 C14),在“可變單元格”框中單擊,指向區域 C6:D6,該區域為兩種產品的產量。
添加約束:單擊“添加”按鈕,在“添加約束”對話框中,在標記為“單元格引用位置”的框中單擊,選擇區域C6:D6,從對話框中部的列表中選擇“>=”,在標記為“約束值”的框中輸入0;選擇區域E3:E5,從對話框中的列表中選擇“<=”,在標記為“約束值”的框中單擊,選擇單元格區域F3:F5。在“添加約束”對話框中單擊“添加”,以輸入需求約束即可。
在“規劃求解選項”對話框中輸入所有可變單元格都為非負值的約束,通過單擊“規劃求解參數”對話框中的“選項”按鈕可打開該對話框。
選擇“采用線性模型”和“假定非負”選項,然后單擊“確定”。
注意:選擇“假定非負”選項可確保規劃求解只考慮每個可變單元格都采用非負值的可變單元格組合。
選擇“采用線性模型”的原因是產品組合問題是一種稱為線性模型的特殊規劃求解問題。
單擊“規劃求解選項”對話框中的“確定”后,返回到主“規劃求解”對話框,單擊“求解”按鈕即可,這樣,規劃求解會迅速找出最佳解決方案,如圖3所示。需要選擇“保存規劃求解解決方案”以將最佳解決方案值保留在電子表格中。
5 添加控件
根據圖表向導,做一直方圖。打開“窗體”控件,添加兩個滾動條,一個與甲產品鏈接,一個與乙產品鏈接,其控件參數的設置最終結果如圖4所示。
6 小 結
利用Excel規劃求解工具不僅可以解決產品組合問題,還可以求解資金管理、運輸管理、選址規劃等。