在设计将使用VBA Excel执行最小化的程序时，我需要一些帮助 [英] I need some help on designing a program that will perform a minimization using VBA Excel

问题描述

如何使用Excel VBA查找方程的最小值?

例如，如果我有方程y = 2x ^ 2 + 14，并且我想制作一个循环，该循环将缓慢增加/减小x的值，直到找到y的最小值，然后让我知道x的对应值是什么，我该怎么做?

有没有一种方法可以处理更复杂的方程式?

谢谢您的帮助！

更多详细信息

我正在尝试设计一个程序，该程序将找到绘制核衰变所需的某个常数.这个常数是方程式的一部分，该方程式使我得到了计算的衰减.我正在将这个计算出的衰减与测得的衰减进行比较.但是，常数随衰减的发生而变化很小，这意味着我必须使用一种称为残差平方的方法来找到要使用的最佳常数，该常数将最适合整个衰减，以使我计算出的衰减尽可能准确.

它通过执行(测量的衰减-计算的衰减)^ 2

您可以多次执行此操作以衰减，然后将它们加在一起.我需要我的程序做的是缓慢增加和减少此常数，直到找到使用该衰减将所有时间的残差平方结果相加所得的最小值为止.具有最小值的残差平方具有我想要的常数的值.

我已经起草了一个程序，可以进行所有计算.我只是不确定如何找到这个最小值.我确定某个方法是否适用于y = x ^ 2 + 1之类的东西，我可以使其适应我的需要.

解决方案

在循环搜索测试输出以寻找最小的输出结果时.

这是一个例子:

Sub FormulaLoop()

    Dim x As Double
    Dim y As Double
    Dim yBest As Double

        x = 1
        y = (x ^ 2) + 14
        yBest = y

    For x = 2 To 100
         y = (x ^ 2) + 14
         If y < yBest Then
            yBest = y
         End If
    Next x

    MsgBox "The smallest output of y was: " & yBest

End Sub

如果您想遍历组成x的两个变量的所有可能性，那么我建议以这种格式进行遍历:

Sub FormulaLoop_v2()

    Dim MeasuredDecay  As Double
    Dim CalculatedDecay As Double
    Dim y As Double
    Dim yBest As Double

        MeasuredDecay = 1
        CalculatedDecay = 1
        y = ((MeasuredDecay - CalculatedDecay) ^ 2) + 14
        yBest = y

    For MeasuredDecay = 2 To 100
        For CalculatedDecay = 2 To 100
            y = ((MeasuredDecay - CalculatedDecay) ^ 2) + 14
            If y < yBest Then
               yBest = y
            End If
         Next CalculatedDecay
    Next MeasuredDecay

    MsgBox "The smallest output of y was: " & yBest

End Sub

How do I use Excel VBA to find the minimum value of an equation?

For example, if I have the equation y = 2x^2 + 14, and I want to make a loop that will slowly increase/decrease the value of x until it can find the smallest value possible for y, and then let me know what the corresponding value of x is, how would I go about doing that?

Is there a method that would work for much more complicated equations?

Thank you for your help!

Edit: more details

I'm trying to design a program that will find a certain constant needed to graph a nuclear decay. This constant is a part of an equation that gets me a calculated decay. I'm comparing this calculated decay against a measured decay. However, the constant changes very slightly as the decay happens, which means I have to use something called a residual-square to find the best constant to use that will fit the entire decay best to make my calculated decay as accurate as possible.

It works by doing (Measured Decay - Calculated Decay) ^2

You do that for the decay at several times, and add them all up. What I need my program to do is to slowly increase and decrease this constant until I can find a minimum value for the value I get when I add up the residual-squared results for all the times using this decay. The residual-squared that has the smallest value has the value of the constant that I want.

I already drafted a program that does all the calculations and such. I'm just not sure how to find this minimum value. I'm sure if a method works for something like y = x^2 + 1, I can adapt it to work for my needs.

解决方案

Test the output while looping to look for the smallest output result.

Here's an Example:

Sub FormulaLoop()

    Dim x As Double
    Dim y As Double
    Dim yBest As Double

        x = 1
        y = (x ^ 2) + 14
        yBest = y

    For x = 2 To 100
         y = (x ^ 2) + 14
         If y < yBest Then
            yBest = y
         End If
    Next x

    MsgBox "The smallest output of y was: " & yBest

End Sub

If you want to loop through all the possibilities of two variables that make up x then I'd recommend looping in this format:

Sub FormulaLoop_v2()

    Dim MeasuredDecay  As Double
    Dim CalculatedDecay As Double
    Dim y As Double
    Dim yBest As Double

        MeasuredDecay = 1
        CalculatedDecay = 1
        y = ((MeasuredDecay - CalculatedDecay) ^ 2) + 14
        yBest = y

    For MeasuredDecay = 2 To 100
        For CalculatedDecay = 2 To 100
            y = ((MeasuredDecay - CalculatedDecay) ^ 2) + 14
            If y < yBest Then
               yBest = y
            End If
         Next CalculatedDecay
    Next MeasuredDecay

    MsgBox "The smallest output of y was: " & yBest

End Sub

这篇关于在设计将使用VBA Excel执行最小化的程序时，我需要一些帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！