excel - 求解 x 的方程

Question

我在字符串中有以下方程

y = 18774x + 82795

求解x我会这样做:-

x = (y-82795) / 18774

我知道y的值

但是方程一直在变化，并且始终采用字符串格式

是否可以简单地将原始方程扔到 Evaluate 语句中并让它自动求解 x？

我知道我可以做这样的事情:-

MsgBox Evaluate("5*(8+3)-2*(3*5)")

但就我而言，它会是

MsgBox Evaluate("67657657 = 18774x + 82795") 

其中y = 67657657

这显然会引发错误。有没有一种简单的方法可以解决 x 问题？

否则我需要解析字符串并更手动地计算出来

Answer 1

试试这个:

Public Function SolveEquation(ByVal eq As String) As Double
    Dim i_eq As Integer, y As Double
    ' eq : "67657657 = 18774x+82795"
    i_eq = InStr(1, eq, "=")
    y = 0#
    If i_eq > 0 Then
        y = Application.Evaluate(Trim(Left(eq, i_eq - 1)))
        'y =67657657
        eq = Trim(Mid(eq, i_eq + 1))
    End If

    Dim eq_1 As String, eq_2 As String
    Dim x_1 As Double, x_2 As Double
    Dim y_1 As Double, y_2 As Double
    x_1 = 0#: x_2 = 1#
    eq_1 = Replace(eq, "x", "*(" & CStr(x_1) & ")")
    y_1 = Application.Evaluate(eq_1)
    eq_2 = Replace(eq, "x", "*(" & CStr(x_2) & ")")
    y_2 = Application.Evaluate(eq_2)
    ' y_1 = 82795, y_2 = 101569

    ' Alternative solution from evaluation points
    ' SolveEquation = x_1 + (x_2-x_1)/(y_2-y_1)*(y-y_1)

    ' y = a*x+b
    Dim a As Double, b As Double
    a = (y_2 - y_1) / (x_2 - x_1)
    b = y_1 - a * x_1
    ' a= 18774, b = 82795

    ' Solution from linear form y = a*x+b
    SolveEquation  = (y - b) / a
    ' 3599.38542665388

End Function