周三 · 分析 關鍵詞:最優路線
從A城市到L城市,有多種路線可供選擇,每段路程的裡程數已知,怎樣才能找到最短的路線呢?
對於這種路線選擇的問題,可以用Excel中的規劃求解功能解決。步驟如下:
1加載規劃求解
如果你還沒用過加載規劃求解,需要先加載。
文件-選項-加載項-Excel加載項-轉到。
勾選「規劃求加加載項」,確定。
2用表格描述問題
列出每個路程的裡程數,「決策」列留空(求解是會用1表示採用,0表示不採用);
合計裡程用決策與裡程數相乘後求和,C25公式:
=SUMPRODUCT(B2:B24,C2:C24)
在F2:Q24,列出每個城市的情況,如果對應的位置是出發點,單元格填1,如果是到達點,單元格填-1。
在25行,設置A城市淨流量,F25公式:
=SUMPRODUCT($C$2:$C$24,F2:F24)
並向右填充至Q25單元格。
在26行,F26、Q26分別輸入1、-1,G26:P26輸入0。解釋一下:
· A城市淨流量標準為1(只出發,不抵達)
· 最終目的地L城市淨流量標準為-1(只抵達,不出發)
· 其他城市淨流量標準為0(不經過,或抵達後出發)
3規劃求解
每在「數據」選項卡中找到並打開「規劃求解」,設定目標處輸入「B25」,選額「最小值」,可變單元格處輸入「C2:C24」;
點擊「添加」設置約束條件:
· 條件1為C2:C24= 二進位BIN(即只能為1或0)
· 條件2為F25:Q25=F26:Q26。
然後點擊「求解」,等幾秒鐘後會顯示找到一解。
現在,凡是決策列中數值為1的,就是被選擇的路線,最優路線為A→C→G→J→L,裡程數為84。
親們可在官方QQ群中下載本案例並嘗試下喔~
延伸閱讀:EXCEL會思考
本文由Excel實務原創,作者白永乾。
Excel實務承接全國Excel培訓,歡迎留言聯繫。
長按下方圖片,識別圖中二維碼,訂閱我們吧!