excel 有没有一种方法可以限制目标搜索？如果没有，你会怎么做？

s4n0splo 于 2023-04-13 发布在其他

关注(0)|答案(3)|浏览(129)

我试图通过改变De的值来最小化残差平方和的值，De在F1中找到。我希望CFL Calculated的值尽可能接近CFL Measured的值。残差平方和越小，拟合越好！在向stackoverflow寻求一些建议后，我决定使用Goal Seek来最小化残差平方和，通过改变De的值来尽可能接近零，我想找到最理想的De值。
我让这个程序完美地运行，或者说我是这么想的......我发现，我不小心用了=SUM(D2,D14)，而不是用=SUM(D2:D14)来求和每一个残差。所以我只对第一个和最后一个数字求和。
现在我试着把每个残差的平方加起来，我得到了这些疯狂的错误，和一个疯狂的De值。
我知道De的值必须大于零，小于一。我如何使用这些界限来保持这个单变量求解集中在某个范围内？在这个例子中，De的答案大约是.012，如果这有帮助的话。我在所有剩余单元格中一直得到错误#NUM!。这是因为溢出问题吗？
如果你已经得出结论，使用单目标搜索通过找到De的最理想值来最小化这些总和是行不通的，你会怎么做？有没有其他的求解器可以使用？
代码如下：

Option Explicit

Dim Counter As Long
Dim DeSimpleFinal As Double
Dim simpletime As Variant
Dim Tracker As Double
Dim StepAmount As Double
Dim Volume As Double
Dim SurfArea As Double
Dim pi As Double
Dim FinalTime As Variant
Dim i As Variant

Sub SimpleDeCalculationNEW()

    'This is so you can have the data and the table I'm working with!
    Counter = 13
    Volume = 12.271846
    SurfArea = 19.634954
    pi = 4 * Atn(1)
    Range("A1") = "Time(days)"
    Range("B1") = "CFL(measured)"
    Range("A2").Value = 0.083
    Range("A3").Value = 0.292
    Range("A4").Value = 1
    Range("A5").Value = 2
    Range("A6").Value = 3
    Range("A7").Value = 4
    Range("A8").Value = 5
    Range("A9").Value = 6
    Range("A10").Value = 7
    Range("A11").Value = 8
    Range("A12").Value = 9
    Range("A13").Value = 10
    Range("A14").Value = 11
    Range("B2").Value = 0.0612
    Range("B3").Value = 0.119
    Range("B4").Value = 0.223
    Range("B5").Value = 0.306
    Range("B6").Value = 0.361
    Range("B7").Value = 0.401
    Range("B8").Value = 0.435
    Range("B9").Value = 0.459
    Range("B10").Value = 0.484
    Range("B11").Value = 0.505
    Range("B12").Value = 0.523
    Range("B13").Value = 0.539
    Range("B14").Value = 0.554

    Range("H2").Value = Volume
    Range("H1").Value = SurfArea

    Range("C1") = "CFL Calculated"
    Range("D1") = "Residual Squared"
    Range("E1") = "De value"
    Range("F1").Value = 0.1

    'Inserting Equations
    Range("C2") = "=((2 * $H$1) / $H$2) * SQRT(($F$1 * A2) / PI())"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & Counter + 1), Type:=xlFillDefault

    Range("D2") = "=((ABS(B2-C2))^2)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & Counter + 1), Type:=xlFillDefault

    'Summing up the residuals squared
    Range("D" & Counter + 2) = "=Sum(D2: D" & Counter + 1 & ")"

    'Goal Seek
    Range("D" & Counter + 2).GoalSeek Goal:=0, ChangingCell:=Range("F1")

    Columns("A:Z").EntireColumn.EntireColumn.AutoFit

    DeSimpleFinal = Range("F1")    
    MsgBox ("The Final Value for DeSimple is: " & DeSimpleFinal)

End Sub

excel

来源：https://stackoverflow.com/questions/16950191/is-there-a-way-to-put-bounds-on-goal-seek-if-not-how-would-you-go-about-this

3条答案

按热度按时间

9ceoxa921#

你会得到NUM错误，因为F1的值在你当前的解决方案中是负的--你试图在你的一个表达式中取F1的平方根。
此外，在这种情况下，目标搜索对您使用的F1的特定初始“猜测”非常敏感。如果您在现在使用的0.1的两侧稍微改变F1初始值，这将是显而易见的。事实上，根据F1值，目标搜索解决方案中存在很大的不稳定区域：

正如您在问题中提到的，如果您可以对解决方案搜索的可能输入设置约束，则更有可能获得可用的结果。Excel附带了一个名为Solver的加载项，该加载项允许此操作，并提供了几种不同的搜索方法。当您第一次启动Excel时，Solver不会自动加载，但加载它很容易，如here所解释的那样。

赞(0）回复(0）举报 2023-04-13

0yg35tkg2#

你需要其他的解决方案。为了找到替代方案和一些理论来帮助理解正在发生的事情，查看Numerical Recipes（在线书籍here）。第10章介绍了这一点。如果您想尝试与GoalSeek或Solver外接程序不同的东西，它包括现成的代码示例。当然，代码是Fortran/C/C++的，但这些代码很容易转换为VBA（我已经做过很多次了）。

赞(0）回复(0）举报 2023-04-13

uoifb46i3#

goalseek函数使用二分法算法，可以这样编码：

Sub dicho(ByRef target As Range, ByRef modif As Range, ByVal targetvalue As Double, ByVal a As Double, ByVal b As Double)

Dim i As Integer
Dim imax As Integer
Dim eps As Double

eps = 0.01
imax = 10
i = 0
While Abs(target.Value - targetvalue) / Abs(targetvalue) > eps And i < imax

    modif.Value = (a + b) / 2
    If target.Value - targetvalue > 0 Then
        a = (a + b) / 2
    Else
        b = (a + b) / 2
    End If
    i = i + 1
Wend
End Sub

a和B是你的边界。

赞(0）回复(0）举报 2023-04-13

我来回答

excel 有没有一种方法可以限制目标搜索？如果没有，你会怎么做？

3条答案

相关问题

热门标签

最新问答