任務(wù)6.1網(wǎng)絡(luò)運(yùn)輸問題規(guī)劃求解【任務(wù)目的】1.掌握規(guī)劃求解的概念；2.掌握規(guī)劃求解的建模過程；3.掌握約束條件的定義?！局R(shí)鏈接】1.規(guī)劃求解在經(jīng)濟(jì)建設(shè)中，經(jīng)常碰到大宗物資的聯(lián)合調(diào)動(dòng)問題，如煤炭、鋼材、糧食等物資在全國(guó)有若干個(gè)生產(chǎn)基地。根據(jù)現(xiàn)有的交通網(wǎng)，應(yīng)如何制定調(diào)運(yùn)方案，將這些物資運(yùn)到各消費(fèi)點(diǎn)且總運(yùn)費(fèi)達(dá)到最小，這類問題稱為運(yùn)輸問題。從運(yùn)籌學(xué)中我們可知運(yùn)輸問題的數(shù)學(xué)模型如式(1)所示:其中，w為總運(yùn)費(fèi)，m為產(chǎn)地，n為銷地。用ai(i=1,2,…，m)表示m個(gè)產(chǎn)地，用bj(j=1,2,…,n)表示n個(gè)銷地。ai運(yùn)到bj的單位運(yùn)價(jià)為Cij(i=1,2,…,m;j=1,2,...,n)；ai調(diào)運(yùn)到bj的運(yùn)量為X個(gè)單位(i=1,2,...,m;j=1,2,...,n)。在Excel規(guī)劃求解中，將w作為目標(biāo)函數(shù)，將其公式創(chuàng)建在目標(biāo)單元格中，求其最小值，C、X作為變量創(chuàng)建在可變單元格中，s.t中三個(gè)條件作為約束條件，從而建立模型進(jìn)行求最優(yōu)解。Excel提供的規(guī)劃求解可以很好的解決這類問題，它避免了在手工狀態(tài)下圖解法的不準(zhǔn)確性和繁瑣，使用起來也非常方便，它是Excel中的一個(gè)加載宏，集成了優(yōu)化程序，它能根據(jù)約束條件自動(dòng)調(diào)整指定的可變單元格(數(shù)學(xué)中稱為變量)中的值，從而求出最優(yōu)解。Excel規(guī)劃求解主要涉及目標(biāo)單元格、可變單元格、約束及最值，它與數(shù)學(xué)模型中的函數(shù)、變量、條件及最大值或最小值、固定值相對(duì)應(yīng)，因此可以利用規(guī)劃求解來解決數(shù)學(xué)方面的問題。2.SUMPRODUCT函數(shù)【格式】=SUMPRODUCT(區(qū)域1，區(qū)域2，……)【作用】將多個(gè)給定區(qū)域中的對(duì)應(yīng)單元格相乘再相加，給定區(qū)域必須具有相同的維數(shù)（即行數(shù)和列數(shù)），否則計(jì)算將出現(xiàn)錯(cuò)誤值

#VALUE!。【工作任務(wù)】某公司擁有兩個(gè)處于不同地理位置的生產(chǎn)工廠和五個(gè)位于不同地理位置的客戶，現(xiàn)在需要將產(chǎn)品從兩個(gè)工廠運(yùn)往五個(gè)客戶。已知兩個(gè)工廠的最大產(chǎn)量均為60000，五個(gè)客戶的需求總量分別為30000、23000、15000、32000、16000，從各工廠到各客戶的單位產(chǎn)品運(yùn)輸成本如表所示，請(qǐng)用Excel的規(guī)劃求解功能計(jì)算出使總成本最小的運(yùn)輸方案。

客戶1客戶2客戶3客戶4客戶5工廠11.752.251.502.001.50工廠22.002.502.501.501.00

單位產(chǎn)品運(yùn)輸成本【實(shí)施步驟】1.根據(jù)已知條件建立Excel表格模型2.加載“規(guī)劃求解”宏3.設(shè)定規(guī)劃求解參數(shù)和約束條件4.求解最優(yōu)解1.建立表格模型根據(jù)已知條件建立Excel表格模型，如圖所示。說明：（1）B9:F10單元格區(qū)域是變量區(qū)域（可變單元格），變量區(qū)域的初始值全部設(shè)置為0（2）B11單元格為客戶1收到的來自兩個(gè)工廠的產(chǎn)品數(shù)合計(jì)，公式為“=SUM(B9:B10)”（3）G9單元格為工廠1出貨的合計(jì)，公式為“=SUM(B9:F9)”（4）C13單元格為運(yùn)輸總成本，公式為“=SUMPRODUCT(B3:F4,B9:F10)2.加載“規(guī)劃求解”宏選擇“文件”選項(xiàng)卡，選擇“選項(xiàng)”，進(jìn)入“Excel選項(xiàng)”對(duì)話框，如左圖所示。在“Excel選項(xiàng)”對(duì)話框中切換到“加載項(xiàng)”頁(yè)，點(diǎn)擊下方“轉(zhuǎn)到”按鈕，在“加載項(xiàng)”對(duì)話框中勾選“規(guī)劃求解加載項(xiàng)”，如右圖所示，點(diǎn)擊“確定”完成加載。3.設(shè)定規(guī)劃求解參數(shù)和約束條件選擇“數(shù)據(jù)”選項(xiàng)卡下“分析”組中的“規(guī)劃求解”命令，進(jìn)入“規(guī)劃求解參數(shù)”對(duì)話框，在對(duì)話框中設(shè)置運(yùn)算參數(shù)和約束條件，如圖所示?！灸繕?biāo)單元格】：即放置運(yùn)輸總成本運(yùn)算結(jié)果的單元格【等于】：這里求最小值【可變單元格】：可變單元格是電子表格中我們可以進(jìn)行更改或調(diào)整以優(yōu)化目標(biāo)單元格的單元格，即變量區(qū)域，本例中各工廠到各客戶的運(yùn)量為變量?！炯s束條件】：（1）非負(fù)約束：由于產(chǎn)品的運(yùn)輸數(shù)量不能是負(fù)數(shù)，故必須有此約束，這是一個(gè)隱含條件，否則求解結(jié)果不正確（即對(duì)話框中的第二個(gè)約束）（2）其他約束：各客戶接受的產(chǎn)品數(shù)量>=需求量（即對(duì)話框中的第一個(gè)約束）；各工廠的出貨總量<=工廠產(chǎn)能（即對(duì)話框中的第三個(gè)約束）思考與點(diǎn)撥：規(guī)劃求解中各約束條件的含義是什么？規(guī)劃求解的約束條件十分關(guān)鍵。在定義約束條件時(shí)要注意約束條件的含義，其中int表示引用的單元格結(jié)果值要為整數(shù)，bin表示引用的單元格結(jié)果值要為二進(jìn)制（0或者1），dif表示引用的單元格結(jié)果值為不重復(fù)值。4.求解最優(yōu)解點(diǎn)擊“規(guī)劃求解參數(shù)”對(duì)話框中的“求解”按鈕，即可看到運(yùn)算出的優(yōu)化運(yùn)輸方案和運(yùn)輸總成本，如圖所示?！救蝿?wù)小結(jié)】物流中經(jīng)常需要遇到解決優(yōu)化問題，包括網(wǎng)絡(luò)調(diào)運(yùn)物資的運(yùn)費(fèi)最小問題、選址問題、經(jīng)濟(jì)訂貨批量模型等，這些問題都可以通過Excel的規(guī)劃求解宏來完成。Excel規(guī)劃求解的基本步驟包括建立表格模型、設(shè)定計(jì)算目標(biāo)、添加約束條件、求解最優(yōu)解等，將工作問題轉(zhuǎn)化為表格模型，并準(zhǔn)確設(shè)置約束條件是規(guī)劃求解的關(guān)鍵?！緦?shí)踐訓(xùn)練】某商品混凝土公司在城市的不同位置修建了三個(gè)攪拌站生產(chǎn)混凝土，三個(gè)攪拌站的最大產(chǎn)量分別為2000t、2500t、2200t，這三個(gè)攪拌站要為城市的六個(gè)建筑工地供應(yīng)混凝土，六個(gè)建筑工地的需求量分別為800t、750t、600t、780t、820t、670t，已知各攪拌站到各工地的單位運(yùn)輸成本如表所示（單位：元/噸），請(qǐng)用Excel計(jì)算出能使運(yùn)輸總成本最低