EXCEL在最小費(fèi)用流問題中的應(yīng)用

方江祎

【摘要】 EXCEL是日常辦公軟件Office的套件之一，除了常用的報(bào)表處理功能外，還有另外一個(gè)強(qiáng)大的功能就是管理決策和優(yōu)化決策的應(yīng)用。本文針對線性規(guī)劃中的最小費(fèi)用流問題，提供了物流配送網(wǎng)絡(luò)的案例，使用最優(yōu)化方法并利用EXCEL軟件給出最小費(fèi)用流問題的解決步驟和方法。

【關(guān)鍵詞】 最優(yōu)化問題 EXCEL 最小費(fèi)用流

一、引言

近幾年，網(wǎng)上購物的快速發(fā)展，使得物流行業(yè)成為國民經(jīng)濟(jì)中迅速成長的新興產(chǎn)業(yè)。配送作為物流行業(yè)的重要組成部分，是物流業(yè)中最有前景和潛能的發(fā)展領(lǐng)域。如何合理安排和選擇最優(yōu)的配送線路，使得運(yùn)輸成本最低，成為物流業(yè)重要的研究課題。

Excel作為我們?nèi)粘＾k公軟件Office的套件之一，除了常用的報(bào)表處理功能外，還有另外一個(gè)強(qiáng)大的功能就是管理決策和優(yōu)化決策的應(yīng)用。EXCEL對于處理最優(yōu)化問題，可以說是簡單理解、方便操作的強(qiáng)大工具，也避免了非專業(yè)人員使用專業(yè)處理軟件不熟悉等棘手問題。

本文以某物流配送網(wǎng)絡(luò)作為最小費(fèi)用流的研究對象，應(yīng)用EXCEL軟件進(jìn)行分析和求解，達(dá)到對解決其他最小費(fèi)用流問題舉一反三的效果。

二、最優(yōu)化問題

獲得最佳處理結(jié)果的問題在數(shù)學(xué)中被稱為最優(yōu)化問題，這類問題的共同特點(diǎn)就是在所有的可能的方案中，選出最合理的，達(dá)到事先規(guī)定的最優(yōu)目標(biāo)的方案，這個(gè)方案是最優(yōu)方案。針對最優(yōu)化方案，尋找最優(yōu)方案的方法稱為最優(yōu)化方法。

最優(yōu)化方法是近幾十年形成的，目的在于針對所研究的系統(tǒng)，求得一個(gè)合理運(yùn)用人力、物力和財(cái)力的最佳方案，發(fā)揮和提高系統(tǒng)的效能及效益，最終達(dá)到系統(tǒng)的最優(yōu)目標(biāo)。

最優(yōu)化方法由目標(biāo)函數(shù)，約束條件和求解方法三個(gè)基本要素組成。

三、最小費(fèi)用流

最小費(fèi)用流是最優(yōu)化問題中的一種，同時(shí)也是線性規(guī)劃問題的特殊類型。我們通過建立線性規(guī)劃模型并求解。

3.1 例子

假設(shè)有一物流配送網(wǎng)絡(luò)，圖1中標(biāo)有LA的節(jié)點(diǎn)表示該公司的工廠，工廠共生產(chǎn)100個(gè)產(chǎn)品，要送往兩個(gè)經(jīng)銷商，分別是圖中標(biāo)有LB和LC的節(jié)點(diǎn)，其中LB經(jīng)銷商需要60個(gè)產(chǎn)品，LC經(jīng)銷商需要40個(gè)產(chǎn)品。從LA工廠運(yùn)送貨物到LB和LC，中途會(huì)經(jīng)過幾個(gè)配送中轉(zhuǎn)的倉庫，在圖中分別標(biāo)為D、E、F、G節(jié)點(diǎn)，節(jié)點(diǎn)之間的弧代表運(yùn)輸路線。

在最小費(fèi)用流問題中，管理者最希望得到的結(jié)果是每條路線運(yùn)送多少產(chǎn)品，使得運(yùn)輸成本的總和達(dá)到最小。根據(jù)最優(yōu)化方法，最小的運(yùn)輸成本總和為目標(biāo)函數(shù)，約束條件則是要遵循的相關(guān)規(guī)則，解決方法是利用EXCEL線性規(guī)劃求解。

圖2是一個(gè)由7個(gè)節(jié)點(diǎn)，13條弧構(gòu)成的有向圖，圖中任意一個(gè)箭頭上方的數(shù)字表示這條運(yùn)輸線路的運(yùn)輸單價(jià)，箭頭下方帶有方括號的數(shù)字表示該條線路最大運(yùn)輸?shù)娜萘俊Ｎ覀兛梢钥吹铰肪€有很多條，并且每條路線的運(yùn)輸容量和成本都不同。

其中供給需求表示該節(jié)點(diǎn)上流出量減去流入量的值。

圖3是根據(jù)該網(wǎng)絡(luò)規(guī)劃問題為基礎(chǔ)得到的電子表格。

其中B列和C列列出了所有的弧，D列的運(yùn)輸數(shù)量表示要求的最優(yōu)解，F(xiàn)列表示了每一條弧所對應(yīng)的最大容量，G列是運(yùn)輸單價(jià)（價(jià)格/容量），D18單元格表示目標(biāo)函數(shù)，在EXCEL中通過函數(shù)D18=SUMPRODUCT（運(yùn)輸數(shù)量，價(jià)格/容量）計(jì)算。J列列出了所有的節(jié)點(diǎn)，K列確定了每個(gè)節(jié)點(diǎn)所產(chǎn)生的凈流量，在K3：K9中輸入的等式用了兩個(gè)SUMIF函數(shù)的差來表示凈流量，第一個(gè)SUMIF計(jì)算該節(jié)點(diǎn)的流出值，第二個(gè)SUMIF計(jì)算該節(jié)點(diǎn)的流入值，兩者之差就是凈流量。

在線性求解參數(shù)對話框中，我們將“設(shè)置目標(biāo)”為目標(biāo)函數(shù)單元格，選擇求解最小值，可變單元格為（D3：D15）。

之前我們列出的約束條件在這里表示為，第一組：D3：D15≤F3：F15，保證弧的流量不會(huì)超過該弧的最大容量；第二組表示為：凈流量K3：K9=供給需求M3：M9。為了保證得到的最優(yōu)解，即最優(yōu)的運(yùn)輸量為正整數(shù)，要勾選“使無約束變量為非負(fù)數(shù)”。

另外在選擇求解方法中選擇單純線性規(guī)劃。通過求解，就得到了圖6中的答案，最小的運(yùn)輸總成本為68000元，最優(yōu)解就是D3：D15。

圖6為得到最優(yōu)解后，該物流配送網(wǎng)絡(luò)的路線選擇圖，任意一個(gè)箭頭上方的數(shù)字表示這條運(yùn)輸線路的運(yùn)輸單價(jià)，箭頭下方的數(shù)字表示該條線路運(yùn)輸?shù)臄?shù)量。

四、總結(jié)

本文介紹了EXCEL線性規(guī)劃在求解最小費(fèi)用流問題的應(yīng)用，既可以對單變量求解，也可以對多變量求解。通過對最小費(fèi)用流問題的典型案例進(jìn)行詳細(xì)介紹，使用者還可以舉一反三地解決最優(yōu)化問題中的最短路徑和最大流等問題。EXCEL對于管理者來說，不需要了解復(fù)雜的求解過程，只需把數(shù)據(jù)、目標(biāo)函數(shù)、約束條件等在電子表格中設(shè)置好，即可以直接求得所需結(jié)果，符合管理者的實(shí)用價(jià)值，也使得EXCEL軟件的使用價(jià)值大大提高。

參 考 文 獻(xiàn)

[1] 朱德通.最優(yōu)化模型與實(shí)驗(yàn)[M].上海：同濟(jì)大學(xué)出版社，2003.

[2] 顧運(yùn)筠.Excel規(guī)劃求解的兩類應(yīng)用[J].計(jì)算機(jī)應(yīng)用與軟件，2005，22（1）：137-139.

[3]陳士成，李橋興，何麗紅.運(yùn)籌學(xué)網(wǎng)絡(luò)優(yōu)化模型的Excel求解的減化方法[J].蘭州：蘭州大學(xué)學(xué)報(bào)（自然科學(xué)版），2010（46）：179-182.

[4]弗雷德里克.S.希利爾，馬克.S.希利爾.數(shù)據(jù)模型與決策[M].北京：中國財(cái)政經(jīng)濟(jì)出版社，2003.

[5]馮英華.Excel平臺下運(yùn)籌學(xué)模型的求解分析[J].長春：長春工業(yè)大學(xué)學(xué)報(bào)（自然科學(xué)版），2014（4）：472-475.