規劃求解_Excel

2021-02-20 Excel表格之道



=SUMPRODUCT(A2:A21*B2:B21)

公式含義：


從這裡我們可以看到，只要我們不斷的去把輔助列中1與0，做出各種組合，哪幾個數字相加為39121，通過無數次的組合，肯定可以被試出來。

Excel中就有這麼一個功能，規劃求解，輔助列中1與0，做出各種組合，這個過程可以讓它去完成。







設置目標：就是=SUMPRODUCT(A2:A21*B2:B21)這個公式的單元格，這個公式代表了它的邏輯關係

設置目標到：39121，這個值是我們對=SUMPRODUCT(A2:A21*B2:B21)的要求，我們的要求是SUMPRODUCT(A2:A21*B2:B21)=39121

更換可變單元格：$B$2:$B$21，這個就是0與1變化的部分，只要目標SUMPRODUCT(A2:A21*B2:B21)結果為39121，$B$2:$B$21這裡面的值讓Excel自己去測試

當然我們必須要求$B$2:$B$21裡面只能顯示是1或0，所以我們要加一個「約束」，如果不加的話請看下圖


這樣就不行了，我們要的是整個數字，只有要或不要，1是要，0是不要。

    




bin代表了二進位，二進位大家都知道，就是0和1，我們給它的約束是結果必須只能為0或1。



正在計算，由於這種組合有無數多種可能，須要一點時間……

這種組合具體有多少種我們在義務教育時肯定學習過，就不展開講了。




我們篩選1，這些數字相加就可以得到39121，值得注意的是，規劃求解給出的是一個解，但不代表只有一個解。


相加等於

其實有2個解。

補充一點：



像我們這種情況沒有必要精度到0.000001

0.001都用不完


這樣的話推理速度會快上很多很多。

展示一個財務案例

公司有4種產品


這是製造產品需要的原材料配方

如果我們手上有1500塊錢，分別可以生產多少個？


A生產10個，剩下的錢生產BCD,可以生產多少個？



=SUMPRODUCT(C2:C4*B2:B4)*B7+SUMPRODUCT(D2:D4*B2:B4)*B8+SUMPRODUCT(E2:E4*B2:B4)*B9+SUMPRODUCT(F2:F4*B2:B4)*B10

這個很好理解吧，就是

都是0所以ABCD生產總成本為0



    


計算出的這個數值只要是近似值即可，我們手工微調一下數值


如果A產品必需生產10個，剩下的錢才能生產BCD，我們就加一個約束條件






有時候按某個成本反推生產數量，呵呵，你懂的。

結束

相關焦點

  • excel規劃求解
    今天要跟大家分享的是excel的規劃求解的功能！
  • Excel規劃求解
    今天和大家分享Excel2010中的規劃求解功能，下面以一個實際問題來說明一下規劃求解所能夠解決的問題。生產兩種風機（無所謂是什麼了），兩種產品各生產一個需要工時3小時和7小時，用電量4千瓦和5千瓦，需要原材料9噸和5噸。公司可提供的工時為300小時，可提供的用電量為250千瓦，可提供的原材料為420噸。
  • Excel中的規劃求解
    如圖1所示：圖 1儘管只有30筆應收款，但是學過高中數學的同學都知道，如果一單一單去湊數，就是一個排列組合問題，如果運氣好的話，可能很快就湊出來了，但如果運氣不好呢？今天就和大家分享一種解法：規劃求解，操作步驟如下：Step1：調出規劃求解。
  • Excel 數據規劃求解
    這個根本不現實，這類問題用到Excel規劃求解就能輕鬆解決，實現步驟如下：首先我們在D3單元格輸入一個求和公式=SUMPRODUCT(B2:B18*C2:C18)，然後在D4單元格輸入一個求差公式：然後，選擇【數據】-【規劃求解】。
  • Excel規劃求解,你會嗎?
    Excel規劃求解，你會嗎？很多複雜問題用函數公式或數據透視表無法解決時，可以藉助規劃求解輕鬆搞定，今天就來介紹一下這個強大的利器。1 規劃求解工具藏在哪裡？規劃求解工具在加載以後，會出現在數據選項卡下。
  • 用Excel規劃求解解決購買難題
    Excel規劃求解可以幫助我們找到答案。假設下圖商品為想要購買的商品，領取的優惠券是滿299減150。1.將想要購買的商品信息輸入Excel表中。對問題進行分析可以發現，約束條件如下：       ①商品數量為整數       ②每樣商品的數量均大於或等於1       ③總的商品價格需大於或等於2994.規劃求解：步驟1：加載規劃求解模塊：點擊文件—選項，調出"選項"對話框
  • 讓你徹底搞明白規劃求解!
    一、何為規劃求解：我們先來看下中國古代著名的典型趣題之一，雞兔同籠：今有雉（雞）兔同籠，上有三十五頭，下有九十四足。問雉兔各幾何？假設用x表示雞的數量，用y表示兔的數量，那麼這道題目的方程式就是：非常簡單。我們可以用規劃求解來完成上面的題目。
  • Excel規劃求解,90%的小夥伴還只是略有耳聞!
    Excel情報局生產搬運分享Excel基礎技能用1%的Excel基礎搞定99%的日常工作做一個有文藝範的Excel公眾號Excel是門手藝 玩轉需要勇氣今天帶大家了解一下Excel規劃求解吧調出規劃求解功能：規劃求解是Excel的一個插件，需要安裝。打開左上角OFFICE文件按鈕—Excel選項—加載項—規劃求解加載項—點擊下方轉到—彈出加載宏對話框—選中規劃求解加載項—點擊確定。最後即可在數據中看到規劃求解功能鍵了。
  • 規劃求解:Excel幫你做最優路線選擇
    對於這種路線選擇的問題，可以用Excel中的規劃求解功能解決。步驟如下：1加載規劃求解如果你還沒用過加載規劃求解，需要先加載。文件-選項-加載項-Excel加載項-轉到。勾選「規劃求加加載項」，確定。（只出發，不抵達）· 最終目的地L城市淨流量標準為-1（只抵達，不出發）· 其他城市淨流量標準為0（不經過，或抵達後出發）3規劃求解
  • 利用Excel的規劃求解尋求最佳方案,老闆都說好!
    Excel中的規劃求解除了拼湊一些數字性的東西，還可以解決實際工作中的一些問題，比如方案的最佳方案方面，可以減少一些工作量。 加載步驟：單擊【開發工具】-【Excel加載項】-【規劃求解加載項】-【確定】。然後單擊【數據】，即可看到相關的功能。如下圖所示：
  • 最佳數字組合問題(規劃求解)?這個東西你肯定用得著!
    >在E3單元格中輸入目標值581，在F3單元格中輸入公式：=SUMPRODUCT(A2:A14,B2:B14)G3單元格為匯總與目標值的差異，輸入公式：=E3-F3在H3單元格中輸入公式：=SUM(B2:B14)在I3單元格中輸入公式：=ABS(E3-F3)*100-H3第二步：【規劃求解
  • 技巧丨Excel規劃求解在供應鏈管理中的應用 之 採購分配方案
    其實，這是一個很明顯地可以採用Excel規劃求解（solver）來解決的問題。它具有如下特徵，多個變量（各家供應商的採購量），追求一個目標（年採購額）的極值 - 最小化，和數個約束條件。我們需要在Excel表裡明確年採購額為目標單元，寫在B17單元，裡面的公式如圖中該單元右側的藍色文字（sumproduct函數的應用就不用說了吧？）
  • 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的值。
  • 用EXCEL來搞藝術之方程求解
    但循環引用是求解一元方程的好辦法，但這種方法只能求出一個值，對於有多個值的情況就沒辦法了。如果X處於分母，很明顯從0開始計算時會出現錯誤，為了避免這種情況，須在起始值輸入1，待方程解有第一次迭代後，再令C10=D10，從而實現迭代計算。對於方程的求解有很多種辦法，比如對於一元二次方程，我們在數學上學過求根公式，就很容易用函數來解決。
  • Excel中的線性規劃,某些數據相加接近於一個固定值!
    我們使用模擬案例來進行求解，如下所示：A列中有25個數字，如何找出來，哪些數字相加等於66666呢？第1種方法，使用VBA的方法：首先，我們在E1單元格中輸入公式：=SUMPRODUCT(A1:A25*B1:B25)，表示A列和B列相乘相加
  • 整數線性規劃和混合整數線性規劃(一):概述及分支定界法
    分別對每個分支進行求解。求解每個分支的伴隨問題，若該分支的伴隨問題沒有可行解，對該分支進行剪支（即不再考慮該分支）；若該分支伴隨問題的最優解是整數解，那麼該分支的最優解是整數線性規劃最優解的一個備選，此時的最優值是原問題目標值的一個下界。
  • 在Excel中求解二元一次方程組
    利用Excel的循環引用功能，可以求解多元一次方程組。
  • 代碼 | 求解LP問題單純形法的Matlab代碼
    求解線性規劃的單純型法function script_LP()求LP問題的基本(可行)解Matlab代碼基本原理考慮如下線性規劃問題的標準型： max Z= CX s.t.那麼，求解該問題的最優解的的步驟： 找到初始的基本可行解； 判斷是否最優； 非最優時，從一個基本可行解變換到更好的基本可行解（進基、出基、旋轉）；單純形法的本質上是每次去找更好的頂點(基本可行解)，一直找到最優的頂點(基本可行解)為止。
  • Excel求解一元二次、多元一次方程組就是這麼簡單!
    Excel除了日常的數據統計之外，還可以用來求解一元二次、多元一次方程組的解，手動輸入幾個數字，即可驗算結果是否正確，強烈推薦給輔導孩子數學而頭痛的父母們，哈哈！ 先了解下概念：只含有一個未知數（一元），並且未知數項的最高次數是2（二次）的整式方程叫做一元二次方程 。
  • 學會了單變量求解,讓Excel來幫你解方程
    >>>Step 2接著我們就要用上神奇的單變量求解工具。它在哪裡呢？單變量求解，故名思議，只能解決一元未知量的問題。那多元未知量的問題呢？就請繼續關注火箭君的後續文章吧！由於答案可能帶有小數，因此，你可以在 Excel選項 -> 公式 中，對計算的近似度以及迭代次數進行設置。