許多計(jì)算模型中涉及到諸多因素，而且這些因素之間還存在著相互制約的關(guān)系，那么歸納起來其實(shí)都是數(shù)學(xué)上的求解反函數(shù)問題，即對(duì)已有的函數(shù)y=∫(x),給定Y的值，反過來求解X。Excel的單變量求解功能可以直接求各種方程，特別是非線性方程的根。

　　示例３０．２使用單變量求解功能求解非線性方程

　　如果要求解下述非線性方程的根，2x³-2x² +5x=12

 　　其具體操作步驟如下。

　　步驟１．假設(shè)用Ａ１單元格存放ｘ的解，則選定Ａ１單元格，并將其命名為Ｘ。

　　步驟２．選定Ａ２單元格，并在其中輸入公式"=2*x^3-2*x^2+5*x-12"。此時(shí)因?yàn)锳1單元格的值為空，故X的值按0計(jì)算，所以A2單元格的值為“-12”。

　　步驟３．單擊菜單“工具”——“單變量求解”。在“單變量求解”對(duì)話框，Excel自動(dòng)將當(dāng)前單元格A2填入目標(biāo)單元格；在目標(biāo)值框中輸入“0”；指定可變單元格為A1。

　　步驟４．單擊“確定”按鈕后，出現(xiàn)“單變量求解狀態(tài)”對(duì)話框，顯示已求得一個(gè)解。單擊“確定”按鈕，A1單元格顯示的是求得的方程的一個(gè)根。

　　３０．２．４使用單變量求解的注意事項(xiàng)

　　并非在每個(gè)計(jì)算模型中做逆向敏感分析都是有解的，比如方程式x²=-1，是沒有根的。在這種情況下，“單變量求解狀態(tài)”對(duì)話框會(huì)告知用戶無解。

　　在單變量求解正在根據(jù)用戶的設(shè)置進(jìn)行計(jì)算時(shí)，“單變量求解狀態(tài)”對(duì)話框上會(huì)動(dòng)態(tài)顯示“在進(jìn)行第Ｎ次迭代計(jì)算”。事實(shí)上，單變量求解正是由反復(fù)的迭代計(jì)算來得到最終結(jié)果的。如果增加Excel允許的最大迭代計(jì)算次數(shù)，可以使每次求解進(jìn)行更多的計(jì)算，以獲得更多的機(jī)會(huì)救出精確結(jié)果。設(shè)置這一選項(xiàng)的方法如下：

　　單擊菜單“工具”——“選項(xiàng)”，在“選項(xiàng)”對(duì)話框的“重新計(jì)算”選項(xiàng)卡中，在“最多迭代計(jì)算次數(shù)”框里面輸入一個(gè)數(shù)值。

　?。常埃骋?guī)劃分析

　　在實(shí)際的工作和生活中，經(jīng)常會(huì)遇到一些規(guī)劃問題。從生產(chǎn)的組織安排、產(chǎn)品的運(yùn)輸調(diào)度、作物的合理布局到原料的恰當(dāng)搭配，無一不需要進(jìn)行認(rèn)真的規(guī)劃分析，才能找到最優(yōu)方案。這些問題的共同點(diǎn)是必須合理地利用有限的資源，以得到最佳的效果。

　　這些問題中通常要涉及到眾多的關(guān)聯(lián)因素和復(fù)雜的數(shù)量關(guān)系，只憑經(jīng)驗(yàn)進(jìn)行簡(jiǎn)單估算顯然是不行的。而線性規(guī)劃、非線性規(guī)劃和動(dòng)態(tài)規(guī)劃等方法正是研究和求解該類問題的有效數(shù)學(xué)方法。但是這些方法的求解大多十分繁瑣復(fù)雜，常令人望而卻步。

　　前一節(jié)介紹的單變量求解工具雖然實(shí)用，但是它每次只能對(duì)一個(gè)變量進(jìn)行求解，而且只能得到一個(gè)解。而利用Excel的規(guī)劃求解工具，將可以方便快捷地幫助用戶得到各種規(guī)劃問題的最佳解。

　　由于在默認(rèn)情況下，Excel并不加載規(guī)劃求解工具。所以要應(yīng)用規(guī)劃求解工具，應(yīng)先對(duì)其加載，操作步驟如下：

　　步驟１．單擊菜單“工具”——“加載宏”，這里將出現(xiàn)“加載宏”對(duì)話框。

　　步驟２．在當(dāng)前加載宏列表框中，勾選“規(guī)劃求解”復(fù)選框，單擊“確定”。

　?。常埃常闭J(rèn)識(shí)規(guī)劃模型

　　規(guī)劃問題的種類繁多，根據(jù)其所要解決的問題可以分成兩類。

　　●確定了某個(gè)任務(wù)，研究如何使用最少的人力、物力和財(cái)力去完成它；

　　●已經(jīng)有了一定數(shù)量的人力、物力和財(cái)力，研究如何使它們獲得最大的收益。

　　從數(shù)學(xué)角度來看，規(guī)劃問題都有下述共同特征。

　　●決策變量：每個(gè)規(guī)劃問題都有一組需要求解的求知數(shù)，稱作決策變量。這組決策變量的一組確定值就代表一個(gè)具體的規(guī)劃方案。

　　●約束條件：對(duì)于規(guī)劃問題的決策變量通常都有一定的限制條件，稱作約束條件。約束條件可以用與決策變量有關(guān)的不等式或等式來表示。

　　●目標(biāo)：每個(gè)問題都有一個(gè)明確的目標(biāo)，如利潤(rùn)最大或成本最小。目標(biāo)通?？捎门c決策變量有關(guān)的函數(shù)表示。

　　解決規(guī)劃分析問題的首要任務(wù)和關(guān)鍵是必須將實(shí)際問題數(shù)學(xué)化、模型化。即將實(shí)際問題通過一組決策變量、一組用不等式或等式表示的約束條件以及目標(biāo)函數(shù)來表示。在Excel中，這些變量、等式或函數(shù)，其實(shí)都是利用單元格中的簡(jiǎn)單公式來構(gòu)成，以及在規(guī)劃求解對(duì)話框設(shè)置的。因此，只要能在工作表中將問題分解，讀者并不需要掌握高深的數(shù)學(xué)知識(shí)和統(tǒng)計(jì)方法，就能很容易地使用規(guī)劃求解功能。

　?。常埃常矂?chuàng)建規(guī)劃模型

　　示例３０．３尋找成本最低的采購(gòu)方案

　　某公司需要采購(gòu)一批贈(zèng)品用于促銷活動(dòng)，采購(gòu)的目標(biāo)品種有４種，分別有不同的單價(jià)。根據(jù)促銷活動(dòng)的需要，采購(gòu)的原則如下：

　　●全部贈(zèng)品的總數(shù)量為４０００件

　　●贈(zèng)品Ａ不能少于４００件

　　●贈(zèng)品Ｂ不能少于６００件

　　●贈(zèng)品Ｃ不能少于８００件

　　●贈(zèng)品Ｄ不能少于２００件，但也不能多于１０００件

　　那么如何擬定采購(gòu)計(jì)劃，才能既滿足促銷需要，又使得采購(gòu)總成本最小呢？

　　要解決這樣的問題，首先將其模型化，分別確定決策變量，設(shè)置約束條件和目標(biāo)。

　?。保疀Q策變量

　　該問題的決策變量顯然應(yīng)為贈(zèng)品Ａ、贈(zèng)品Ｂ、贈(zèng)品Ｃ和贈(zèng)品Ｄ的采購(gòu)數(shù)量，用變量來分別表示它們x₁,x₂,x₃,x₄。

　?。玻s束條件

　　該問題的約束條件主要來自采購(gòu)數(shù)量。

　　　　　　　　　　　　　x₁≥400

                                                   x₂≥600

　　采購(gòu)數(shù)量的約束：          x₃≥800

                                                 200≤x₄≤1000

　?。常繕?biāo)

　　該問題的目標(biāo)是采購(gòu)總成本最小。

　?。常埃常骋?guī)劃模型求解

　　建立好規(guī)劃模型后，就可以使用Excel的規(guī)劃求解工具求解了。

 　　１．建立工作表

　　規(guī)劃求解的第一步，是將規(guī)劃模型的有關(guān)數(shù)據(jù)輸入到工作表中。

　　提示：讀者可以從Excel Home網(wǎng)站下載本示例的工作簿文件。

　　在圖３０．１４中，因?yàn)樯形撮_始規(guī)劃求解，所以暫時(shí)將每種贈(zèng)品的采購(gòu)數(shù)量都設(shè)置為１０００，此時(shí)則目標(biāo)（總成本）為58 000。

 　?。玻?guī)劃求解

　　下面利用Excel的規(guī)劃求解工具幫助找到最佳方案，具體操作步驟如下：

　　步驟１．單擊菜單“工具”——“規(guī)劃求解”，這時(shí)將出現(xiàn)“規(guī)劃求解參數(shù)”對(duì)話框。

　　步驟２．設(shè)置目標(biāo)函數(shù)。指定設(shè)置目標(biāo)單元格為目標(biāo)所在的單元格Ｄ６，并選定“最小值”單選按鈕。

　　步驟３．設(shè)置決策變量。指定可變單元格為決策變量所在的單元格區(qū)域Ｂ2：B5。

 　　步驟４．設(shè)置約束條件。單擊添加按鈕，這時(shí)將出現(xiàn)“添加約束”對(duì)話框。

　　步驟５．在單元格引用位置中指定贈(zèng)品Ａ數(shù)量所在單元格的地址Ｂ２，選擇〉=關(guān)系運(yùn)算符，在約束值中輸入400，單擊“添加”按鈕，即添加了一個(gè)約束條件“$B$2〉=400”。也就是指贈(zèng)品A的數(shù)量不能少于400。

 　　步驟６．按照上述步驟逐個(gè)添加表中其余各約束條件。

 規(guī)劃求解所需要的各項(xiàng)約束條件

約束條件	說明
B3〉=600	贈(zèng)品B不能少于600件
B4〉=800	贈(zèng)品C不能少于800件
B5〉=200	贈(zèng)品D不能少于200件
B5〈=1000	贈(zèng)品D不能多于1000件
B6=4000	采購(gòu)總數(shù)量為4000

　　添加完畢后，單擊“確定”按鈕。這時(shí)的“規(guī)劃求解參數(shù)”對(duì)話框。

　　步驟７．單擊“求解”按鈕。Excel即開始進(jìn)行計(jì)算，最后出現(xiàn)“規(guī)劃求解結(jié)果”對(duì)話框。

 　　用戶可根據(jù)需要選擇是保存規(guī)劃求解結(jié)果還是恢復(fù)為原值、是否保存方案以及是否生成運(yùn)算結(jié)果報(bào)告、敏感度分析報(bào)告和限制范圍報(bào)告。按?。茫裕遥?nbsp;鍵盤或ＳＨＩＦＴ鍵再單擊報(bào)告名稱，可同時(shí)選擇多種報(bào)告。

　　這里選擇保存規(guī)劃求解結(jié)果，并生成運(yùn)算結(jié)果報(bào)告、敏感度分析報(bào)告和限制范圍報(bào)告。

　　從計(jì)算結(jié)果可以看出，最佳采購(gòu)方案是各贈(zèng)品分別采購(gòu)400、1800、800和1000，采購(gòu)總成本為52000。

　?。常埃常捶治銮蠼饨Y(jié)果

　　通過查看規(guī)劃求解工具生成的各種報(bào)告，可以進(jìn)一步分析規(guī)劃求解結(jié)果，并根據(jù)需要修改或重新設(shè)置規(guī)劃求解參數(shù)。當(dāng)規(guī)劃求解失敗時(shí)，還可以適當(dāng)調(diào)整規(guī)劃求解選項(xiàng)。

　?。保@示分析報(bào)告

　　圖分別展示了剛才生成的運(yùn)算結(jié)果報(bào)告、敏感報(bào)告和極限值報(bào)告。

單元格	名字	初值	終值
$D$6	合計(jì) 成本	58,000	52,000
單元格	名字	初值	終值
$B$2	贈(zèng)品A 數(shù)量	1,000	400
$B$3	贈(zèng)品B 數(shù)量	1,000	1,800
$B$4	贈(zèng)品C 數(shù)量	1,000	800
$B$5	贈(zèng)品D 數(shù)量	1,000	1,000
單元格	名字	單元格值	公式	狀態(tài)	型數(shù)值
$B$6	合計(jì) 數(shù)量	4,000	$B$6=4000	未到限制值	0
$B$2	贈(zèng)品A 數(shù)量	400	$B$2>=400	到達(dá)限制值	-
$B$3	贈(zèng)品B 數(shù)量	1,800	$B$3>=600	未到限制值	1,200
$B$4	贈(zèng)品C 數(shù)量	800	$B$4>=800	到達(dá)限制值	-
$B$5	贈(zèng)品D 數(shù)量	1,000	$B$5<=1000	到達(dá)限制值	0
$B$5	贈(zèng)品D 數(shù)量	1,000	$B$5>=200	未到限制值	800

      　　規(guī)劃求解運(yùn)算結(jié)果報(bào)告

Microsoft Excel 11.0 敏感性報(bào)告
工作表 [規(guī)劃求解.xls]規(guī)劃模型
報(bào)告的建立: 2007-6-7 21:40:56
可變單元格
			終	遞減
	單元格	名字	值	梯度
	$B$2	贈(zèng)品A 數(shù)量	400	7
	$B$3	贈(zèng)品B 數(shù)量	1,800	-
	$B$4	贈(zèng)品C 數(shù)量	800	9
	$B$5	贈(zèng)品D 數(shù)量	1,000	-2
約束
			終	拉格朗日
	單元格	名字	值	乘數(shù)
	$B$6	合計(jì) 數(shù)量	4,000	11

　　　　規(guī)劃求解敏感性報(bào)告

Microsoft Excel 11.0 極限值報(bào)告
工作表 [規(guī)劃求解.xls]極限值報(bào)告 1
報(bào)告的建立: 2007-6-7 21:40:56
		目標(biāo)式
	單元格	名字	值
	$D$6	合計(jì) 成本	52,000
		變量			下限	目標(biāo)式		上限	目標(biāo)式
	單元格	名字	值		極限	結(jié)果		極限	結(jié)果
	$B$2	贈(zèng)品A 數(shù)量	400		400	52,000		400	52,000
	$B$3	贈(zèng)品B 數(shù)量	1,800		1,800	52,000		1,800	52,000
	$B$4	贈(zèng)品C 數(shù)量	800		800	52,000		800	52,000
	$B$5	贈(zèng)品D 數(shù)量	1,000		1,000	52,000		1,000	52,000

　　　　　　　規(guī)劃求解極限值報(bào)告

　　從報(bào)告中可以清楚地看出最佳方案與原方案的差異，通過約束單元格的狀態(tài)、遞減梯度、極限值等指標(biāo)可以進(jìn)一步了解規(guī)劃求解的細(xì)節(jié)。

　?。玻薷囊?guī)劃求解參數(shù)

　　當(dāng)規(guī)劃模型有所變動(dòng)時(shí)，可以在工作表中方便地修改有關(guān)數(shù)值后，再重新運(yùn)行規(guī)劃求解功能進(jìn)行計(jì)算即可。默認(rèn)情況下，Excel會(huì)自動(dòng)保存用戶所創(chuàng)建過的所有規(guī)劃求解參數(shù)，但在每張工作表上只允許保留一套參數(shù)。

　　有時(shí)還可能根據(jù)需要和模型的變化修改約束條件。例如上例，如果促銷所需要的贈(zèng)品總數(shù)量增加到５０００，則修改約束條件的操作步驟如下。

　　步驟１．單擊菜單“工具”——“規(guī)劃求解”。

　　步驟２．在彈出的“規(guī)劃求解參數(shù)”對(duì)話框中選中約束條件“&B$6=4000"，單擊“更改”按鈕。

　　步驟３．在彈出的“改變約束”對(duì)話框中的重新輸入約束值“5000”，單擊“確定”按鈕即可完成更改。

　　如果對(duì)規(guī)劃模型的參數(shù)修改內(nèi)容較多，或需要計(jì)算另一個(gè)規(guī)劃模型時(shí)，可以在“規(guī)劃求解參數(shù)”對(duì)話框中直接單擊“全部重設(shè)”按鈕。１然后重新設(shè)置規(guī)劃求解的目標(biāo)、可變單元格和約束條件。

　?。常薷囊?guī)劃求解選項(xiàng)

　　如果規(guī)劃模型設(shè)置的約束條件矛盾，或在限制條件下無可行解，系統(tǒng)將會(huì)給出規(guī)劃求解失敗的信息。規(guī)劃求解失敗也有可能是當(dāng)前設(shè)置的最大求解時(shí)間太短，最大求解次數(shù)太少或是精度過高等原因引起。對(duì)此可以修改規(guī)劃求解選項(xiàng)。

　　在“規(guī)劃求解參數(shù)”對(duì)話框中單擊“選項(xiàng)”按鈕，將彈出“規(guī)劃求解選項(xiàng)”對(duì)話框。

規(guī)劃求解各參數(shù)選項(xiàng)的含義

選項(xiàng)及按鈕	含　　　　　　義
最長(zhǎng)運(yùn)算時(shí)間	求解一個(gè)問題所花費(fèi)的最大時(shí)間值。盡管默認(rèn)的設(shè)置下，規(guī)劃求解能在大多數(shù)情況下得到解，但如果報(bào)告說求解走出了時(shí)間限制，則可以增加運(yùn)算時(shí)間
迭代次數(shù)	進(jìn)行挖求解的最大次數(shù)，它決定了規(guī)劃求解運(yùn)算到什么程度
精度	確定約束單元格的值的計(jì)算精度，此選項(xiàng)的數(shù)值越接近１，精度越低，反之則精度超高，但是計(jì)算時(shí)間也越長(zhǎng)
允許誤差	只用于使用整數(shù)約束條件的問題，用于計(jì)算非線性問題時(shí)決定規(guī)劃求解的計(jì)算深度
收斂度	確定一個(gè)０～１之間的數(shù)值，用于計(jì)算非線性問題時(shí)決定規(guī)劃求解的計(jì)算深度
采用線性模型	只有用戶明確知道規(guī)劃分析模型中的所有關(guān)系全部是線性的時(shí)候，才可以勾選此復(fù)選框，它將加快求解過程
假定非負(fù)	勾選此復(fù)選框可以為所有未設(shè)定下限的決策變量假設(shè)下限為零值
自動(dòng)按比例縮放	勾選此復(fù)選框會(huì)使規(guī)劃求解設(shè)置一些變動(dòng)單元格，相對(duì)于其他單元格來放大或縮小
顯示迭代結(jié)果	勾選此臨行選框可以顯示決策變量的多種組合，在每次迭代后都將顯示“顯示試驗(yàn)解”對(duì)話框，此對(duì)話框允許保存目前的方案，并可選擇停止或繼續(xù)迭代計(jì)算
估計(jì)、導(dǎo)數(shù)和搜索	此３項(xiàng)選項(xiàng)需要用戶對(duì)線性計(jì)算技術(shù)有充分的了解，一般情況下保持默認(rèn)設(shè)置即可
裝入模型	此功能所顯示的“裝入模型”對(duì)話框允許用戶指定截入某個(gè)已存在的模型參數(shù)
保存模型	此功能所顯示的“保存模型”對(duì)話框允許用戶保存模型參數(shù)

　　用戶可以根據(jù)自己的需要設(shè)置上述選項(xiàng)，再重新求解。選擇不同的選項(xiàng)將可能帶來不同的求解結(jié)果。

 

　?。常埃常怠∈褂靡?guī)劃求解的注意事項(xiàng)

　　要想利用規(guī)劃求解得到最佳方案，最關(guān)鍵的工作是將問題轉(zhuǎn)變?yōu)橐?guī)劃模型，并利用電子表格來進(jìn)行實(shí)現(xiàn)，利用單元格之間的關(guān)系來描述決策變量、約束條件和目標(biāo)之間的互相影響。

　　但是，即使有了正確的模型，規(guī)劃求解也并不總是能幫助用戶找到最佳方案。這就需要用戶對(duì)規(guī)劃求解的技術(shù)特點(diǎn)與運(yùn)算過程有充分的了解，并且通過調(diào)整現(xiàn)有的選項(xiàng)，以及調(diào)整決策變量的初始值來優(yōu)化計(jì)算。根據(jù)經(jīng)驗(yàn)，Excel的規(guī)劃求解在處理非線性總是時(shí)，由于計(jì)算過于復(fù)雜，因此決策變量的初始值對(duì)計(jì)算的影響非常大。

 　　實(shí)際上，規(guī)劃求解并非Excel特有的工具，而且Excel的規(guī)劃求解工具有許多局限性。許多公司都開發(fā)了更強(qiáng)大的規(guī)劃求解工具，用以解決更復(fù)雜的規(guī)劃模型。如果在互聯(lián)網(wǎng)上搜索，將能找到這樣的工具或提供這樣工具及服務(wù)的公司。其中，F(xiàn)rontline Systems,Inc就是比較著名的一家，該公司開發(fā)了許多規(guī)劃求解工具，其中包括大家在Excel使用到的這個(gè)簡(jiǎn)化版本。

 　　可以查看網(wǎng)址http://www./xlscompare.htm 了解Excel的規(guī)劃求解的局限性。