基于Excel數(shù)據(jù)建模求解及結(jié)果分析

石建國

（安徽工程大學(xué)計(jì)算機(jī)與信息學(xué)院，安徽蕪湖241000）

數(shù)據(jù)分析和處理在各領(lǐng)域都有重要的應(yīng)用，根據(jù)數(shù)據(jù)之間的關(guān)系和實(shí)際問題的需求，建立現(xiàn)實(shí)問題的數(shù)學(xué)模型，通過相關(guān)軟件或設(shè)計(jì)算法并進(jìn)行編程對(duì)模型進(jìn)行分析和求解，以此找出解決問題的最優(yōu)化方案。為此，需要借助數(shù)據(jù)處理軟件進(jìn)行復(fù)雜數(shù)據(jù)的處理,常用的數(shù)據(jù)處理分析軟件有Excel、SPSS、Matlab、R等。但除Excel外，其他軟件對(duì)數(shù)據(jù)分析人員的專業(yè)技術(shù)能力要求相對(duì)較高、使用較復(fù)雜、軟件購置費(fèi)較昂貴，成為使用過程中的一個(gè)瓶頸。

規(guī)劃求解問題在很多領(lǐng)域都有廣泛的應(yīng)用，如果不用Excel求解，通過編程實(shí)現(xiàn)非常復(fù)雜，而且算法的準(zhǔn)確性不易得到保證。類似的數(shù)據(jù)處理工具諸如Spss，雖然也具有較強(qiáng)的數(shù)據(jù)處理和分析功能，但與辦公軟件如Office或WPS兼容困難；另一種用于數(shù)值分析和計(jì)算的重要軟件Matlab雖然功能強(qiáng)大，但較難掌握，尤其是在算法設(shè)計(jì)和實(shí)現(xiàn)時(shí)需要通過對(duì)象化編程，程序執(zhí)行速度較慢，而且使用成本昂貴。Excel是一款通用且簡單易學(xué)的數(shù)據(jù)處理軟件，同時(shí)具有操作簡單方便、使用廣泛和成本低等優(yōu)點(diǎn)，常用于一些簡單的數(shù)據(jù)處理。同時(shí)，Excel也有一些實(shí)用功能可解決復(fù)雜的應(yīng)用問題，但這種復(fù)雜的應(yīng)用在普通教材和Excel幫助中均沒有詳細(xì)介紹。文章通過實(shí)例探討利用Excel規(guī)劃求解工具求解最優(yōu)化問題的思路和方法，并對(duì)處理結(jié)果中輸出的敏感性報(bào)告進(jìn)行了分析，并驗(yàn)證了結(jié)論的正確性。

1 Excel數(shù)據(jù)處理及分析方法

（1）Excel提供了很多數(shù)據(jù)處理和分析方法。目前常見的應(yīng)用于數(shù)據(jù)處理的方法有排序、篩選、分類匯總、合并計(jì)算及各類數(shù)據(jù)處理函數(shù)，這些數(shù)據(jù)處理方法，通過簡單學(xué)習(xí)便可熟練掌握并運(yùn)用。

（2）用于數(shù)據(jù)分析的方法有規(guī)劃求解、數(shù)據(jù)透視圖表、各類圖表(如條形圖、柱形圖、散點(diǎn)圖、餅圖等)、描述性統(tǒng)計(jì)、均值推斷、概率統(tǒng)計(jì)、回歸分析（線性回歸、邏輯回歸和多元回歸等）、時(shí)間序列、T-test、Z-test等。其中描述性統(tǒng)計(jì)和概率統(tǒng)計(jì)方法通常用于匯總數(shù)據(jù)，其與回歸分析、T-test和Z-test統(tǒng)稱為傳統(tǒng)的統(tǒng)計(jì)分析方法。時(shí)間序列用于預(yù)測分析，為非傳統(tǒng)統(tǒng)計(jì)分析方法[1]。

2 線性規(guī)劃問題求解

線性規(guī)劃問題數(shù)學(xué)模型通常由變量、約束條件和目標(biāo)函數(shù)3個(gè)要素組成。通過列出約束條件及目標(biāo)函數(shù)，找出約束條件所表示的可行域，在可行域內(nèi)求目標(biāo)函數(shù)的最優(yōu)解[2]。根據(jù)現(xiàn)實(shí)情況，從中建立數(shù)學(xué)模型一般分3個(gè)步驟：（1）根據(jù)目標(biāo)影響因素確定決策變量；（2）由決策變量和目標(biāo)之間的數(shù)量關(guān)系確定目標(biāo)函數(shù)；（3）根據(jù)決策變量的客觀限制條件確定決策變量應(yīng)滿足的約束[3]。以下以運(yùn)輸問題為例來闡明Excel在線性規(guī)劃求解中的應(yīng)用。

3家企業(yè)X1、X2、X3生產(chǎn)同一產(chǎn)品，其產(chǎn)量分別是46、22、32（單位：萬件），現(xiàn)要將產(chǎn)品運(yùn)到Y(jié)1、Y2、Y3、Y44個(gè)地方，這4地的產(chǎn)品需求分別為27、39、20和14（單位：萬件），試確定最優(yōu)化方案[4]。從產(chǎn)地到銷地的產(chǎn)品運(yùn)價(jià)如下面矩陣所示：

若設(shè)xij為從產(chǎn)地Xi到需求地Yj的產(chǎn)品運(yùn)輸量，則該運(yùn)輸問題的線性規(guī)劃模型為

min Z =13x11+13x12+11x13+7x14+15x21+9x22+5x23+15x24+11x31+19x32+21x33+13x34，將上述模型對(duì)應(yīng)的數(shù)據(jù)填入Excel工作表中，如圖1所示。各單元格之間的運(yùn)算關(guān)系如表1所示。

在Excel 中點(diǎn)擊“數(shù)據(jù)”菜單，找到“模擬分析”中的“規(guī)劃求解”選項(xiàng)，出現(xiàn)圖2“規(guī)劃求解參數(shù)”對(duì)話框。其中求解目標(biāo)在工作表的B14單元格顯示，數(shù)據(jù)可動(dòng)態(tài)改變的單元格范圍為$B$8：$E$10，滿足的約束條件為（1）$B$11：$E$11=$B$13：$E$13；（2）$F$8：$F$10 =$H$8：$H$10。

圖1 運(yùn)輸問題的電子表格模型

表1 運(yùn)輸問題模型中的公式設(shè)置

點(diǎn)擊圖2中的“求解”按鈕，在Excel中輸出的最優(yōu)解如圖3所示。由圖3可知，通過單純線性規(guī)劃得出的最優(yōu)化運(yùn)輸方案為從X1調(diào)運(yùn)32（萬件）到需求地Y2，調(diào)運(yùn)14（萬件）到需求地Y4；從X2調(diào)運(yùn)2（萬件）到需求地Y2，20（萬件）到需求地Y3；從X3調(diào)運(yùn)27（萬件）到需求地Y1，調(diào)運(yùn)5（萬件）到需求地Y2。這樣可以使得運(yùn)輸費(fèi)用最小為1 024（百元）。

圖2 運(yùn)輸問題的規(guī)劃求解參數(shù)設(shè)置

圖3 運(yùn)輸問題最優(yōu)方案

3 結(jié)果分析

結(jié)果分析中最重要的是敏感性報(bào)告中各項(xiàng)參數(shù)的分析，最重要的參數(shù)有陰影價(jià)格、遞減成本、目標(biāo)式系數(shù)和允許的增減量分析。

3.1 敏感性分析的內(nèi)涵及作用

所謂敏感度分析，是指對(duì)某些可能變化的因素及其對(duì)決策目標(biāo)影響程度的反復(fù)分析，以揭示決策方案如何隨可變因素變化而變化[5]。實(shí)例中建立線性規(guī)劃模型所需的數(shù)據(jù)都是已知的，這些數(shù)據(jù)被稱為模型參數(shù)。但在現(xiàn)實(shí)應(yīng)用中獲取這些模型參數(shù)并不容易，需要付出大量時(shí)間和精力，并通過實(shí)踐和檢驗(yàn)才能獲得可靠的數(shù)據(jù)，有時(shí)即使投入大量時(shí)間和精力，也只能得到模型參數(shù)的粗略估計(jì)或預(yù)測值。因此，結(jié)果分析需要研究兩個(gè)問題：一是當(dāng)參數(shù)中的一個(gè)或多個(gè)發(fā)生變化時(shí)，已求得的線性規(guī)劃最優(yōu)解會(huì)如何變化；二是參數(shù)在什么范圍內(nèi)變動(dòng)，最優(yōu)解能夠保持不變[6]。

敏感度分析的作用主要有兩點(diǎn)：（1）可了解相關(guān)因素的變動(dòng)對(duì)決策方案、目標(biāo)或者其他評(píng)價(jià)指標(biāo)的影響程度，找到影響最優(yōu)決策方案的相關(guān)因素，并進(jìn)一步分析或者估算影響決策目標(biāo)的不確定因素，有利于比較不同備選方案對(duì)關(guān)鍵敏感因素的影響程度，以便選擇敏感性相對(duì)較小的方案，從而減小決策風(fēng)險(xiǎn)。（2）幫助決策者掌握方案最優(yōu)與最劣的可能變動(dòng)范圍，通過分析把握如何采取有效控制措施，以便選取最有經(jīng)濟(jì)意義的決策方案[7]。

3.2 敏感性報(bào)告輸出

在求解輸出最優(yōu)化運(yùn)輸方案時(shí)，點(diǎn)擊“規(guī)劃求解參數(shù)”界面的“求解”按鈕，顯示界面如圖4所示。

選中“報(bào)告”框中的“敏感性報(bào)告”，點(diǎn)擊“確定”，便可輸出“敏感性報(bào)告”，對(duì)敏感性報(bào)告中的數(shù)據(jù)進(jìn)行整理，輸出結(jié)果如表2和表3所示。表2為可變單元格部分，對(duì)應(yīng)于目標(biāo)函數(shù)中的xij；表3為約束部分，即限制條件，對(duì)應(yīng)于各地產(chǎn)銷量的約束限制范圍。

圖4 規(guī)劃求解結(jié)果界面

表2 敏感性報(bào)告之一：可變單元格部分

表3 敏感性報(bào)告之二：約束部分

3.3 參數(shù)分析

（1）可變單元格參數(shù)分析?？勺儐卧癫糠止蔡峁?欄數(shù)據(jù)?！敖K值”表明問題的最優(yōu)解，“遞減成本”說明降低成本的數(shù)量，“目標(biāo)式系數(shù)”表示單位運(yùn)價(jià)，“允許的增量”和“允許的減量”標(biāo)明單位運(yùn)價(jià)在“已知運(yùn)價(jià)，允許增量,允許減量”之間變動(dòng)，運(yùn)輸方案可以不變。若超過這個(gè)范圍，運(yùn)輸方案則需要變更。此范圍即為最優(yōu)解的敏感度。表中“1E+30”表示無窮大，此處可以理解為大于零的任意值。若設(shè)單位運(yùn)價(jià)為xij，則在本例中，的變化范圍為，則，即產(chǎn)地X1到銷地Y1的單位運(yùn)價(jià)x11的取值范圍為。同理可得在保證運(yùn)輸方案不變的前提下，各產(chǎn)地和銷地之間的單位運(yùn)價(jià)xij的變動(dòng)范圍，結(jié)果如表4所示。

表4 單位運(yùn)價(jià)變動(dòng)范圍

遞減成本（Reduced cost）也叫減少成本，等于相應(yīng)目標(biāo)函數(shù)中變量檢驗(yàn)數(shù)的值[8]，是敏感性報(bào)告中的一個(gè)重要參數(shù)，是Excel輸出時(shí)目標(biāo)函數(shù)中xij的系數(shù)。而對(duì)于求最大值的線性規(guī)劃而言，所有檢驗(yàn)數(shù)必小于等于0；對(duì)于求最小值的線性規(guī)劃而言，所有檢驗(yàn)數(shù)必大于等于0。與之對(duì)應(yīng)，如果目標(biāo)函數(shù)若是求最大值，則遞減成本必小于等于0。故根據(jù)遞減成本的正負(fù)就能判斷出目標(biāo)函數(shù)是求最大值還是最小值。本例中目標(biāo)函數(shù)是求最小運(yùn)輸費(fèi)用，則遞減成本必小于等于0。

同時(shí)，觀察表2 中的終值和遞減成本的對(duì)應(yīng)關(guān)系，可以得出終值與其相應(yīng)的遞減成本的積為0，即×Rij=0（表示變量xij的終值，Rij表示變量xij的遞減成本）。這種性質(zhì)稱為最優(yōu)解與遞減成本的互補(bǔ)松弛性[9]。據(jù)此，只要知道終值和遞減成本其中一項(xiàng)不為0，則可推知另一項(xiàng)必為0。

表2中產(chǎn)地和銷地之間沒有產(chǎn)品運(yùn)輸關(guān)系的有6個(gè)。以X3→Y3為例，X3到Y(jié)3的單位運(yùn)價(jià)為21（目標(biāo)函數(shù)中x33的系數(shù)），如果要使X3Y3的終值不為零，即最優(yōu)方案中有產(chǎn)地X3往銷地Y3的產(chǎn)品運(yùn)輸，在保證最優(yōu)解不變的前提下，可對(duì)表3中的R33參數(shù)進(jìn)行調(diào)整，當(dāng)R33的值由6減少到0時(shí)，才會(huì)出現(xiàn)X3Y3對(duì)應(yīng)的終值不為0的結(jié)果。若其他條件不變，實(shí)例中產(chǎn)地X3到銷地Y3的單位運(yùn)價(jià)減少到15時(shí)，從產(chǎn)地X3才會(huì)運(yùn)送產(chǎn)品到Y(jié)3，這就是Excel敏感性報(bào)告中遞減成本蘊(yùn)含的有價(jià)信息。

為驗(yàn)證上述判斷的正確性，將產(chǎn)地X3到銷地Y3的單位運(yùn)價(jià)改為15，運(yùn)用非線性內(nèi)點(diǎn)法重新求解一次，得到的最優(yōu)解如表5所示。

由表5可知，方案更改后產(chǎn)地X3到銷地Y3有2萬件產(chǎn)品的運(yùn)輸量（對(duì)應(yīng)于灰色單元格數(shù)值）。將表5數(shù)據(jù)代入目標(biāo)函數(shù)，與之前的最優(yōu)運(yùn)費(fèi)相同，驗(yàn)畢。

表5 單位運(yùn)價(jià)變動(dòng)后的最優(yōu)化方案

（2）約束部分參數(shù)分析。約束部分最重要的參數(shù)是陰影價(jià)格，通常也稱影子價(jià)格。影子價(jià)格是指線性規(guī)劃模型中某個(gè)約束的右端常數(shù)項(xiàng)增加（或減少）一個(gè)單位而導(dǎo)致的目標(biāo)函數(shù)值的增量（或減量）[10]。影子價(jià)格客觀地反映了資源在系統(tǒng)內(nèi)的稀缺程度，影子價(jià)格越高，這種資源越稀缺。而影子價(jià)格為零的約束資源為富余資源。由陰影價(jià)格的定義可得出，陰影價(jià)格反映的是單位資源（消耗）的價(jià)值（費(fèi)用）。

第一個(gè)約束銷地Y1的影子價(jià)格是11，說明銷地Y1每增加1個(gè)需求量，目標(biāo)函數(shù)運(yùn)輸總費(fèi)用會(huì)增加11個(gè)單位。也就是說，目標(biāo)函數(shù)的最優(yōu)解會(huì)發(fā)生變化，從“允許的增量”一欄中可以看出，實(shí)例中銷地Y2的陰影價(jià)格最大，說明Y2每增加1個(gè)需求量所增加的運(yùn)輸總費(fèi)用最高。但從表3可以看出，銷地Y1、Y2、Y3和Y4“允許的增量”為0，產(chǎn)地X1、X2和X3“允許的減量”也為0，說明銷地增加需求量和產(chǎn)地減少生產(chǎn)量都將導(dǎo)致最優(yōu)解發(fā)生改變。

現(xiàn)在假設(shè)如果市場條件已發(fā)生變化，生產(chǎn)量和需求量需要同時(shí)增加（或減少），仍然保持產(chǎn)銷平衡狀態(tài)。當(dāng)生產(chǎn)和需求同時(shí)增加時(shí)，X1和X2允許的增量都為5（萬件），X3允許的增量為任意值，假設(shè)總的需求量增加5（萬件），生產(chǎn)量也要同時(shí)增加5（萬件），這時(shí)仍滿足產(chǎn)量和銷量相等。運(yùn)用Excel進(jìn)行進(jìn)一步求解分析，目標(biāo)是當(dāng)生產(chǎn)量和需求量各增加5（萬件），保持產(chǎn)銷平衡，得出運(yùn)輸總費(fèi)用增加最少的最優(yōu)化方案，所得結(jié)果數(shù)據(jù)如表6所示。

表6 運(yùn)輸總費(fèi)用增量表

從表6可以看出，在X2生產(chǎn)量增加5（萬件）和Y1需求量增加5（萬件）的情況下運(yùn)輸總費(fèi)用增加最少，這是因?yàn)樵诒?中，產(chǎn)地X1的陰影價(jià)格為-6，X2對(duì)應(yīng)的陰影價(jià)格為-10，X3的陰影價(jià)格為0，說明X2增加產(chǎn)量比X1和X3對(duì)總費(fèi)用的降低最大。同時(shí)，銷地Y1對(duì)應(yīng)的陰影價(jià)格為11，Y2對(duì)應(yīng)的陰影價(jià)格為19，Y3對(duì)應(yīng)的陰影價(jià)格為15，Y4對(duì)應(yīng)的陰影價(jià)格為13，說明Y1增加需求量比Y2、Y3和Y4對(duì)總費(fèi)用的增加最小。因此，在生產(chǎn)量和需求量都同樣增加保持產(chǎn)銷平衡的情況下，在產(chǎn)地X2處生產(chǎn)最劃算。同理，在生產(chǎn)量和需求量都同樣減少保持產(chǎn)銷平衡的情況下，不難判斷在X3處減少生產(chǎn)最合理。

4 結(jié)束語

Excel在數(shù)據(jù)處理和分析方面還有很多重要應(yīng)用，比如通過統(tǒng)計(jì)分析對(duì)大量數(shù)據(jù)進(jìn)行統(tǒng)計(jì)處理；通過回歸分析找到數(shù)據(jù)內(nèi)在規(guī)律，并可用于預(yù)測和控制等問題；通過時(shí)間序列分析進(jìn)行市場預(yù)測以及制定生產(chǎn)計(jì)劃等。但最重要的是要理解數(shù)據(jù)背后所表達(dá)的現(xiàn)實(shí)含義。文章以線性規(guī)劃中的運(yùn)輸問題為例，通過對(duì)數(shù)據(jù)處理結(jié)果進(jìn)行分析，揭示敏感性報(bào)告中的重要參數(shù)的現(xiàn)實(shí)內(nèi)涵，通過驗(yàn)證證明了結(jié)論的正確性，對(duì)于使用Excel進(jìn)行數(shù)據(jù)處理和分析的人員具有一定的參考價(jià)值。