Board logo

標題: [發問] excel規劃求解問題（是否能自動更新） [打印本頁]
作者: m06o2    時間: 2016-12-8 00:16     標題: excel規劃求解問題（是否能自動更新）

a1=7
a1=0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07
求解b1
目前嘗試過規劃求解，是可以實際算出b1 但因為a1會一直變動 b1每次都需要重新規劃求解 想詢問是否有辦法可以自動更新
作者: c_c_lai    時間: 2016-12-8 10:47

回復 1# m06o2
你的意思是
b1 = a1*(0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07)
？
作者: m06o2    時間: 2016-12-8 22:31

回復 2# c_c_lai
你的意思是
b1 = a1*(0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07)
不太一樣   
a1=(0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07)
但我已知a1的時候怎麼求解b1
作者: ML089    時間: 2016-12-9 17:35

本帖最後由 ML089 於 2016-12-10 12:10 編輯

回復 1# m06o2
A1 輸入數值
B1 為變數(需求)
C1 為目標公式求B1值為多少時計算值為0
C1 =A1-(0.9*0.49+9.36*LOG(b1+1)-0.2+LOG(1.5/2.7)/(0.4+1094/(b1+1)^5.19)+2.32*LOG(412000)-8.07)


ALT-F11
選擇 Sheet1 插入輸入Worksheet_Change
當A1改變值時，啟動 目標搜尋 函數，找出B1值。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub

[attach]26062[/attach]
作者: m06o2    時間: 2016-12-10 10:26

回復 4# ML089
大大您好我使用了您的方式後，可以使用了

但是不知道為何我寫的無法跟您一樣自動改變 而需要按執行才會進行計算
作者: m06o2    時間: 2016-12-10 10:41

回復 5# m06o2
我發現問題所在了！！！我選錯工作表了！！非常感謝:)
作者: ML089    時間: 2016-12-10 12:09

回復 6# m06o2

'放在 ThisWorkbook，每個Sheet都能作用
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub


'放在該Sheet中，只有該Sheet才能作用
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
        Range("C1").GoalSeek Goal:=0, ChangingCell:=Range("B1")
    End If
End Sub
作者: m06o2    時間: 2016-12-12 11:19

回復 7# ML089
感謝您，發現如果要求解的數值再分母  是不是就無法記算!
EX:=((1.5*P3*Q3)*((1/(L3*(1+((U3/Q3)^2)*((N3/L3)^(2/3)))^(1/2)))+((1-(1/((1+((U3/Q3)^2))^(1/2))))/N3))-O3))
求解N3  其他變數都是已知
作者: m06o2    時間: 2016-12-12 12:01

回復 8# m06o2

如果我希望他算出來的數值要大於0的最接近解  需要怎麼修正
作者: ML089    時間: 2016-12-12 16:23

回復 8# m06o2


EX:=((1.5*P3*Q3)*((1/(L3*(1+((U3/Q3)^2)*((N3/L3)^(2/3)))^(1/2)))+((1-(1/((1+((U3/Q3)^2))^(1/2))))/N3))-O3))
的關係式是為 0 嗎?
作者: m06o2    時間: 2016-12-13 21:42

回復 10# ML089
在EXCEL表中的計算表內黃色兩行(第m行及n行)M行是想求的解   N行是計算出來的答案
接著我用計算出來得答案帶入求解  計算表驗算那一分頁   計算結果與原始資料  有算出他的誤差
但我發現存在一個問題   每次計算的答案都不相同，不知道原因是什麼!



[attach]26089[/attach]
作者: ML089    時間: 2016-12-13 22:23

回復 11# m06o2

看不太懂
若有公式就直接就直接求解
作者: m06o2    時間: 2016-12-14 10:22

回復 12# ML089

   O3 =((1.5*P3*Q3)*((1/(L3*(1+((U3/Q3)^2)*((N3/L3)^(2/3)))^(1/2)))+((1-(1/((1+((U3/Q3)^2))^(1/2))))/N3)))
   O3、P3、L3、U3、N3、U3都已知   要求Q3  所以用了求解的方法求   Q3
   接著把求出來的Q3帶入原式子中計算新的的O3   在跟原始的O3比較  發現他差異很大
   [attach]26091[/attach]
作者: ML089    時間: 2016-12-14 23:00

J3 =(1.5*D3*E3)*((1/(C3*(1+((F3/E3)^2)*((B3/C3)^(2/3)))^(1/2)))+((1-(1/((1+((F3/E3)^2))^(1/2))))/B3))-H3
下拉

精度低速度快
Sub 目標搜尋()
    [E3:E30] = 1
    For i = 3 To 30
        Range("J" & i).GoalSeek Goal:=0, ChangingCell:=Range("E" & i)
    Next
End Sub


求解精度比較高
'SolverSolve 函數
'
'開始執行規劃求解解決方案。相當於按一下 [規劃求解參數] 對話方塊中的 [規劃求解]。
'
'使用本函數之前，必須建立對規劃求解增益集的參照。在 Visual Basic 編輯器中，將焦點移至某個模組，按一下 [工具] 功能表上的 [參照]，然後選取 [可用參照] 底下的 [Solver.xlam] 核取方塊。如果 [Solver.xlam] 沒有出現在 [可用參照] 底下，按一下 [瀏覽]，然後開啟 \office12\library\Solver 子資料夾中的 Solver.xlam。
'
Sub 規劃求解()
    [E3:E30] = 1
    For i = 3 To 30
        SolverOk SetCell:="$J$" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$E$" & i
        SolverSolve UserFinish:=True
        'SolverFinish KeepFinal:=1, ReportArray:=Array(1)    '1 表示建立答案報告，2 表示建立敏感度報告，而 3 表示建立限制報告
    Next
End Sub

[attach]26100[/attach]
作者: Scott090    時間: 2016-12-15 06:45

回復 13# m06o2

假如是用 range.goalseek 單變數求解， goal := 0
建議調整 "檔案""選項""公式""計算選項" "最大誤差"，例如 0.00001 應該會得到不同的誤差百分比
作者: m06o2    時間: 2016-12-15 12:05

回復 15# Scott090
有喔   這個方法有變好一點!!
作者: m06o2    時間: 2016-12-15 12:07

回復 14# ML089
我有測試這個方法  發現一個問題  
Sub 規劃求解()
    For i = 1 To 30
        SolverOk SetCell:="$J$" & i, MaxMinVal:=3, ValueOf:="0", ByChange:="$B$" & i
        SolverSolve UserFinish:=True
        'SolverFinish KeepFinal:=1, ReportArray:=Array(1)    '1 表示建立答案報告，2 表示建立敏感度報告，而 3 表示建立限制報告
    Next
End Sub
這樣可以執行  
但是當我的i 從  1 To 30  改成  1 To 1990  跑完我全部的時候  發現跑很久  結果跑完後  數值都沒改變!!  但一次調整小範圍就可以執行
作者: ML089    時間: 2016-12-15 12:55

回復 17# m06o2

昨天我有測試 99筆還OK，
要執行1~2000筆會很久吧?
晚上我再試試

目前沒有想法可以解決
作者: m06o2    時間: 2016-12-15 13:01

回復 18# ML089
我測試 1-100也還OK  約1分鐘  但我cpu是i7的   跑大概只需要15分鐘  2000筆
作者: ML089    時間: 2016-12-15 13:22

回復 19# m06o2

[E3:E30] = 1
初始值有修改嗎?
作者: m06o2    時間: 2016-12-15 13:29

回復 18# ML089
我解決了，原來是因為中間有不能解的  把那個刪除就可以了!
作者: m06o2    時間: 2016-12-15 13:30

回復 20# ML089
有喔  我手動改了!



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)