規劃求解:Excel幫你做最優路線選擇

2021-02-20 Excel實務

周三 · 分析    關鍵詞:最優路線

從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培訓,歡迎留言聯繫。

 長按下方圖片,識別圖中二維碼,訂閱我們吧!

相關焦點

  • excel規劃求解
    今天要跟大家分享的是excel的規劃求解的功能!
  • Excel規劃求解
    今天和大家分享Excel2010中的規劃求解功能,下面以一個實際問題來說明一下規劃求解所能夠解決的問題。生產兩種風機(無所謂是什麼了),兩種產品各生產一個需要工時3小時和7小時,用電量4千瓦和5千瓦,需要原材料9噸和5噸。公司可提供的工時為300小時,可提供的用電量為250千瓦,可提供的原材料為420噸。
  • 人生道路坎坷 但仍然在最優路線上
    人生道路坎坷 但仍然在最優路線上李俊一路走來,我的人生道路有各種坎坷
  • Excel中的規劃求解
    如圖1所示:圖 1儘管只有30筆應收款,但是學過高中數學的同學都知道,如果一單一單去湊數,就是一個排列組合問題,如果運氣好的話,可能很快就湊出來了,但如果運氣不好呢?今天就和大家分享一種解法:規劃求解,操作步驟如下:Step1:調出規劃求解。
  • Excel規劃求解,你會嗎?
    Excel規劃求解,你會嗎?很多複雜問題用函數公式或數據透視表無法解決時,可以藉助規劃求解輕鬆搞定,今天就來介紹一下這個強大的利器。1 規劃求解工具藏在哪裡?規劃求解工具在加載以後,會出現在數據選項卡下。
  • Excel 數據規劃求解
    這個根本不現實,這類問題用到Excel規劃求解就能輕鬆解決,實現步驟如下:首先我們在D3單元格輸入一個求和公式=SUMPRODUCT(B2:B18*C2:C18),然後在D4單元格輸入一個求差公式:然後,選擇【數據】-【規劃求解】。
  • 讓你徹底搞明白規劃求解!
    首先看下規劃求解的定義:「規劃求解」是一組命令的組成部分,這些命令有時也稱作假設分析 ,可通過更改其他單元格來確定某個單元格的最大值或最小值或者目標值。具體到下圖就是:通過更改I7和J7單元格中的值來確定L7中的值。
  • 用Excel規劃求解解決購買難題
    Excel規劃求解可以幫助我們找到答案。假設下圖商品為想要購買的商品,領取的優惠券是滿299減150。1.將想要購買的商品信息輸入Excel表中。對問題進行分析可以發現,約束條件如下:       ①商品數量為整數       ②每樣商品的數量均大於或等於1       ③總的商品價格需大於或等於2994.規劃求解:步驟1:加載規劃求解模塊:點擊文件—選項,調出"選項"對話框
  • Excel規劃求解,90%的小夥伴還只是略有耳聞!
    Excel公眾號Excel是門手藝 玩轉需要勇氣今天帶大家了解一下Excel規劃求解吧!調出規劃求解功能:規劃求解是Excel的一個插件,需要安裝。打開左上角OFFICE文件按鈕—Excel選項—加載項—規劃求解加載項—點擊下方轉到—彈出加載宏對話框—選中規劃求解加載項—點擊確定。最後即可在數據中看到規劃求解功能鍵了。
  • 規劃求解_Excel
    我們篩選1,這些數字相加就可以得到39121,值得注意的是,規劃求解給出的是一個解,但不代表只有一個解。有時候按某個成本反推生產數量,呵呵,你懂的。
  • 最佳數字組合問題(規劃求解)?這個東西你肯定用得著!
    >在E3單元格中輸入目標值581,在F3單元格中輸入公式:=SUMPRODUCT(A2:A14,B2:B14)G3單元格為匯總與目標值的差異,輸入公式:=E3-F3在H3單元格中輸入公式:=SUM(B2:B14)在I3單元格中輸入公式:=ABS(E3-F3)*100-H3第二步:【規劃求解
  • 利用Excel的規劃求解尋求最佳方案,老闆都說好!
    Excel中的規劃求解除了拼湊一些數字性的東西,還可以解決實際工作中的一些問題,比如方案的最佳方案方面,可以減少一些工作量。 加載步驟:單擊【開發工具】-【Excel加載項】-【規劃求解加載項】-【確定】。然後單擊【數據】,即可看到相關的功能。如下圖所示:
  • 技巧丨Excel規劃求解在供應鏈管理中的應用 之 採購分配方案
    假如你是某鋼廠橫著走的採購老大,說的是假如啊。你可能會面對8家良莠不齊的煤供應商A~H,如圖。
  • Excel中的線性規劃,某些數據相加接近於一個固定值!
    有一個做財務工作的小夥伴問我,它收到10幾張發票,然後他希望其中幾張發票金額湊成一個數字,但他表示,湊了一上午的金額還沒有湊出來,有沒有什麼好的解決辦法
  • 做一張EXCEL甘特圖,步驟幫你列好了!
    然而年底了,就像昨天麗詩問的,你是不是感到事情多到做不完呢?很重要的一個原因就是你沒有做好自己的工作規劃。當我們有一個大計劃需要按照進度執行的時候,當我們需要統一管理所有工作的任務與資料的時候,或是當我們必須掌控或參與一個團隊的合作的時候…在這裡,我們就需要一個好用的項目管理工具。相信我,甘特圖就會幫到你!
  • 學會了單變量求解,讓Excel來幫你解方程
    不知道解方程是不是你曾經在數學試卷上一個難以逾越的痛。但我知道,要是沒有計算設備,我覺得一般人是很難手算出這個答案。(至少我不會,>>>Step 2接著我們就要用上神奇的單變量求解工具。它在哪裡呢?請看……
  • 整數線性規劃和混合整數線性規劃(一):概述及分支定界法
    分別對每個分支進行求解。求解每個分支的伴隨問題,若該分支的伴隨問題沒有可行解,對該分支進行剪支(即不再考慮該分支);若該分支伴隨問題的最優解是整數解,那麼該分支的最優解是整數線性規劃最優解的一個備選,此時的最優值是原問題目標值的一個下界。
  • 用EXCEL來搞藝術之方程求解
    但循環引用是求解一元方程的好辦法,但這種方法只能求出一個值,對於有多個值的情況就沒辦法了。如果X處於分母,很明顯從0開始計算時會出現錯誤,為了避免這種情況,須在起始值輸入1,待方程解有第一次迭代後,再令C10=D10,從而實現迭代計算。對於方程的求解有很多種辦法,比如對於一元二次方程,我們在數學上學過求根公式,就很容易用函數來解決。
  • Excel小技巧08:求解聯立方程組
    圖1 然後,在2×1的垂直單元格區域內(本例中為E2:E3)輸入數組公式:=MMULT(MINVERSE(A2:B3),C2:C3) 可以求解出上述方程組的結果,如下圖2所示。 對於三元一次線性方程組,也可以用同樣的方法來求解。如下面所示的三元一次線性方程組:x+2y+z=72x-y+3z=73x+y+2z=18求解x、y和z的值。
  • 告別迷茫 | 投資銀行HR幫你做職業規劃!
    X師兄是我之前社團的學長,前兩天跟他聊了一會,談到關於規劃的話題,他說:我一直不想從事銷售類工作,可我不知道以後我可以做什麼?