Excel求解運輸問題的方法

1、 TITLETITLE 運輸問題建模和優(yōu)化 在供應(yīng)鏈管理中，運輸是一項重要環(huán)節(jié)，包括：在供應(yīng)鏈管理中，運輸是一項重要環(huán)節(jié)，包括：原材料原材料生產(chǎn)商，生產(chǎn)商生產(chǎn)商，生產(chǎn)商倉庫，倉庫倉庫，倉庫零售零售商商 在運輸環(huán)節(jié)中，如何進行優(yōu)化安排，降低成本，在運輸環(huán)節(jié)中，如何進行優(yōu)化安排，降低成本，是供應(yīng)鏈管理中的一項重要內(nèi)容。是供應(yīng)鏈管理中的一項重要內(nèi)容。 供應(yīng)地點供應(yīng)量供應(yīng)地點供應(yīng)量 需求地點需求量需求地點需求量 從供應(yīng)地點到需求地點的單位運輸成本從供應(yīng)地點到需求地點的單位運輸成本1. 簡單運輸問題網(wǎng)絡(luò)圖 S o urcesD estina tio ns 已知：已知： 供應(yīng)地點供應(yīng)量 需求地點需求量 從

2、供應(yīng)地點到需求地點的單位運輸成本運輸成本運輸成本最小？最小？例1 供需平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量150150200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B3，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地分別運往各銷地的單位運費如下表所示。問：應(yīng)如何調(diào)運可使總運輸費用最?。拷猓?供需平衡問題-設(shè) xij 為從產(chǎn)地Ai運往銷地Bj的運輸量（下標(biāo)i表示源，下標(biāo)j表示目的地），得到下列運輸量表：解-設(shè)產(chǎn)地銷 地產(chǎn)量B1B2B3A1X11X12X13200A2X21X22X23300銷量150150200例1 供需平衡的運輸產(chǎn)地銷 地產(chǎn)量B1B

3、2B3A1X11X12X13200A2X21X22X23300銷量150150200產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量150150200目標(biāo)函數(shù)：Min z= 6x11+ 4x12+ 6x13+ 6x21+ 5x22+ 5x23 s.t. x11+ x12 + x13 = 200 x21 + x22+ x23 = 300 x11 + x21 = 150 x12 + x22 = 150 x13 + x23 = 200 xij0(i=1、2；j=1、2、3）模 型目標(biāo)函數(shù)： Min z= 6x11+ 4x12+ 6x13+ 6x21+ 5x22+ 5x23 s.t. x

4、11+ x12 + x13 = 200 x21 + x22+ x23 = 300 x11 + x21 = 150 x12 + x22 = 150 x13 + x23 = 200 xij0(i=1、2；j=1、2、3）決策結(jié)果配送方案2. 運輸問題模型的一般形式njjmiiba11 假設(shè) A1, A2,Am 表示某產(chǎn)品的m個產(chǎn)地；B1,B2,Bn 表示某產(chǎn)品的n個銷地；ai表示產(chǎn)地 Ai 的產(chǎn)量；bj 表示銷地 Bj 的銷量；cij 表示把產(chǎn)品從產(chǎn)地 Ai 運往銷地 Bj 的單位運價。問怎樣調(diào)運這些產(chǎn)品才能使總運費最??？ 如果 則稱該運輸問題為產(chǎn)銷平衡問題；否則，稱產(chǎn)銷不平衡。 銷地產(chǎn)地B1 B

5、2 Bn產(chǎn)量A1 A2 Amc11 c12 c1nc21 c22 c2n cm1 cm2 cmna1 a2 am銷量b1 b2 bn 運輸問題數(shù)據(jù)表運輸問題決策變量表 銷地產(chǎn)地B1 B2 Bn產(chǎn)量A1 A2 Amx11 x12 x1nx21 x22 x2n xm1 xm2 xmna1 a2 am銷量b1 b2 bn 設(shè) xij 為從產(chǎn)地 Ai 運往銷地 Bj 的運輸量，根據(jù)這個運輸問題的要求，可以建立運輸決策變量表。 銷地產(chǎn)地B1B2Bn產(chǎn)量A1 c11x11 c12 x12 c1n x1na1 A2 c21 x21 c22 x22 c2n x2na2 Am cm1 xm1 cm2 xm2 c

6、mn xmnam銷量b1b2bn 產(chǎn)銷平衡運輸問題的數(shù)學(xué)模型0, 1, 1.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz例 2 運輸問題Shipping CostFactoryCustomer1Customer2Customer3Outputf1 700.00 900.00 800.00 12f2 800.00 900.00 700.00 15Order Size1089例2 Big M公司在2個工廠生產(chǎn)一系列大型機器。產(chǎn)品之一是大型機床?，F(xiàn)收到3位顧客的訂單。這些機床會被單獨運送。下表給出了單位運輸成本、工廠產(chǎn)量和客戶的訂貨量。求費用最小的配送方案。模

7、型設(shè)：xij 為工廠到客戶的配送量目標(biāo)函數(shù)：z=700 x11+900 x12+800 x13 +800 x21+900 x22+700 x23 s.t. X11 + x21 + x31 = 12 x21 + x22 + x23 = 15 x11 + x21 = 10 x12 + x22 = 8 x13 + x23 = 9 xij = 0模型的模型的ExcelExcel描述：描述：區(qū)域地址EXCEL 求解3.供需不平衡的運輸問題例 3 供需不平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646300A2655300銷量150150200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B

8、3，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地運往銷地的單位運費如下表所示，問：應(yīng)如何調(diào)運可使總運輸費用最小？3.供需不平衡的運輸問題轉(zhuǎn)化為供需平衡轉(zhuǎn)化：產(chǎn)銷平衡產(chǎn)地銷 地產(chǎn)量B1B2B3B4A16460300A26550300銷量150150200100求解轉(zhuǎn)化：產(chǎn)銷平衡產(chǎn)地銷 地產(chǎn)量B1B2B3B4A16460300A26550300銷量150150200100產(chǎn)地銷 地產(chǎn)量B1B2B3B4A1x11x12x13x14300A2x21x22x23x24300銷量150150200100求解運輸方案產(chǎn)地銷 地產(chǎn)量B1B2B3B4A115015000300=300A200200100300=300供應(yīng)

9、量150150200100=運輸費用銷量產(chǎn)大于銷運輸問題的數(shù)學(xué)模型0, 1, 1.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz例 4 供需不平衡的運輸問題產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300銷量250200200 某公司從兩個產(chǎn)地A1、A2將物品運往三個銷地B1、B2、B3，各產(chǎn)地的產(chǎn)量、各銷地的銷量和各產(chǎn)地運往銷地的單位運費如下表所示，問：應(yīng)如何調(diào)運可使總運輸費用最小？轉(zhuǎn)化為供需平衡轉(zhuǎn)化：產(chǎn)銷平衡 產(chǎn)地銷 地產(chǎn)量B1B2B3A1646200A2655300A3000150銷量250200200銷大于產(chǎn)運輸問題的數(shù)學(xué)模型0, 1, 1

10、.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz例5 供需不平衡的運輸問題銷 地 及 單 位 運 費產(chǎn)地1區(qū)2區(qū)3區(qū)產(chǎn)量山西盂縣1.81.71.554000河北臨城1.61.51.751500需求量300010002000 某單位有3個生活區(qū)。每年分別需要用煤3000、1000、2000噸，由河北臨城、山西盂縣兩處煤礦負(fù)責(zé)供應(yīng)，價格、質(zhì)量相同。供應(yīng)能力分別為1500、4000噸，單位運價如表所示。 由于需大于供，經(jīng)研究決定1區(qū)供應(yīng)量最多可減少300噸，2區(qū)必須滿足需求量，3區(qū)供應(yīng)量不少于1500噸，試求總費用為最低的調(diào)運方案。模 型設(shè)：設(shè)：目標(biāo)函數(shù)：目標(biāo)函數(shù)

11、： s.t.s.t.ExcelExcel求解結(jié)果：求解結(jié)果：目標(biāo)函數(shù)：目標(biāo)函數(shù)：H18H18：=SUMPRODUCT(C6:E7,C13:E14)=SUMPRODUCT(C6:E7,C13:E14)約束：約束：F13F13：=SUM(C13:E13)=SUM(C13:E13)F14F14：=SUM(C14:E14)=SUM(C14:E14)C17C17：=SUM(C13:C14)=SUM(C13:C14)D17D17：=SUM(D13:D14)=SUM(D13:D14)E17E17：=SUM(E13:E14)=SUM(E13:E14)例5 EXCEL求解運輸問題線性規(guī)劃的一般模型0, 1, 1

12、.min1111ijmijijinjijminjijijxnjbxmiaxtsxcz容量線路 如果從起點i到終點j的路線有容量Lij ，那么可以增加約束 xij = Mij 。 如果起點i不能向終點j送貨，則增加約束 xij = 0 。 例 6如果起點i不能向終點j送貨，則增加約束 Xij = 0生產(chǎn)廠需求1 需求2需求3需求4產(chǎn)量F11613221750F21413191560F3192023-50最低需求量3070010最高需求量507030不限生產(chǎn)能力、需求量和單位運輸費用等數(shù)據(jù)如下表所示。注意工廠3不能向需求4供貨。求解最佳配送方案。例6 EXCEL 求解過程例 7 轉(zhuǎn)運1 1 1h4

13、h4f1f1h3h3R5R5R6R6R7R72 23 33 3f2f2 2 26 63 36 64 44 46 65 5 R8R8600600400400200200150150350350300300求解步驟：1. 問題轉(zhuǎn)化為表格。2. 寫出目標(biāo)函數(shù)和約束。3. 使用解題工具求解。例7 求解1.轉(zhuǎn)運站的輸入=輸出2.所有不可配送單元格=0目標(biāo)函數(shù)：目標(biāo)函數(shù)：K15K15：=SUMPRODUCT(C2:H5,C9:H12)=SUMPRODUCT(C2:H5,C9:H12)約束：約束： I9 I9：=C9+D9=C9+D9I10I10：=C10+D10=C10+D10I11I11：=SUM(E1

14、1:H11)=SUM(E11:H11)I12I12：=SUM(E12:H12)=SUM(E12:H12)C13C13：=C9+C10=C9+C10D13D13：=D9+D10=D9+D10E13E13：=E11+E12=E11+E12F13F13：=F11+F12=F11+F12G13G13：=G11+G12=G11+G12H13H13：=H11+H12=H11+H12C11:D12=0C11:D12=0E9:H10=0E9:H10=0例 8 轉(zhuǎn)運2例8-1求解結(jié)果（F2R8）1. 問題轉(zhuǎn)化為表格。2. 寫出目標(biāo)函數(shù)和約束。3. 使用解題工具求解。EXCEL求解目標(biāo)函數(shù)：目標(biāo)函數(shù)：K15K15

15、：=SUMPRODUCT(C2:H5,C9:H12)=SUMPRODUCT(C2:H5,C9:H12)約束：約束： I9 I9：=C9+D9=C9+D9I10I10：=C10+D10+H10=C10+D10+H10I11I11：=SUM(E11:H11)=SUM(E11:H11)I12I12：=SUM(E12:H12)=SUM(E12:H12)C13C13：=C9+C10=C9+C10D13D13：=D9+D10=D9+D10E13E13：=E11+E12=E11+E12F13F13：=F11+F12=F11+F12G13G13：=G11+G12=G11+G12H13H13：=H10+H11+

16、H12=H10+H11+H12C11:D12=0C11:D12=0E9:H9=0E9:H9=0E10:G10=0E10:G10=0F2F2向向R8R8送貨送貨EXCEL求解過程ExcelExcel公式：公式：K16K16：=SUMPRODUCT(C2:H6,C10:H14)=SUMPRODUCT(C2:H6,C10:H14)I10I10：=SUM(C10:H10)=SUM(C10:H10)I11I11：=SUM(C11:H11)=SUM(C11:H11)I12: =SUM(C12:H12)I12: =SUM(C12:H12)I13: =SUM(C13:H13)I13: =SUM(C13:H13)I14I14：=H14=H14C15C15：=SUM(C10:C11)=SUM(C10:C11)D15D15：=SUM(D10:D11)=SUM(D10:D11)E15E15：=SUM(E10:E14)=SUM(E10:E14)F15F15：=SUM(F10:F14)=SUM(F10:F14)G15G15：=SUM(G10:G14)-I14=SUM(G10:G14)-I14H15H15：=SUM(H10:H14)=SUM(H10:H14)E10E10：H10=0H10=0E11E11：G11=0G11=0C12C12：D14=0D14=0E14E14：G14=0G14=0