我试图通过改变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
3条答案
按热度按时间9ceoxa921#
你会得到NUM错误,因为F1的值在你当前的解决方案中是负的--你试图在你的一个表达式中取F1的平方根。
此外,在这种情况下,目标搜索对您使用的F1的特定初始“猜测”非常敏感。如果您在现在使用的0.1的两侧稍微改变F1初始值,这将是显而易见的。事实上,根据F1值,目标搜索解决方案中存在很大的不稳定区域:
正如您在问题中提到的,如果您可以对解决方案搜索的可能输入设置约束,则更有可能获得可用的结果。Excel附带了一个名为
Solver
的加载项,该加载项允许此操作,并提供了几种不同的搜索方法。当您第一次启动Excel时,Solver不会自动加载,但加载它很容易,如here所解释的那样。0yg35tkg2#
你需要其他的解决方案。为了找到替代方案和一些理论来帮助理解正在发生的事情,查看Numerical Recipes(在线书籍here)。第10章介绍了这一点。如果您想尝试与
GoalSeek
或Solver外接程序不同的东西,它包括现成的代码示例。当然,代码是Fortran/C/C++的,但这些代码很容易转换为VBA(我已经做过很多次了)。uoifb46i3#
goalseek函数使用二分法算法,可以这样编码:
a和B是你的边界。