石建國

（安徽工程大學計算機與信息學院，安徽蕪湖241000）

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

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

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

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

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

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

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

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

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

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

在Excel 中點擊“數(shù)據(jù)”菜單，找到“模擬分析”中的“規(guī)劃求解”選項，出現(xiàn)圖2“規(guī)劃求解參數(shù)”對話框。其中求解目標在工作表的B14單元格顯示，數(shù)據(jù)可動態(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 運輸問題的電子表格模型

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

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

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

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

3 結(jié)果分析

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

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

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

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

3.2 敏感性報告輸出

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

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

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

表2 敏感性報告之一：可變單元格部分

表3 敏感性報告之二：約束部分

3.3 參數(shù)分析

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

表4 單位運價變動范圍

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

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

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

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

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

表5 單位運價變動后的最優(yōu)化方案

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

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

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

表6 運輸總費用增量表

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

4 結(jié)束語

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