有没有一种简单的方法可以将图表中的趋势线公式应用于 Excel 中任何给定的 X 值？
例如，我想要获取给定 X = $2,006.00 的 Y 值。我已经获取了公式并将其重新输入为：
=-0.000000000008*X^3 - 0.00000001*X^2 + 0.0003*X - 0.0029
我通过添加更多数据不断调整趋势线，并且不想每次都重新输入公式。
答案1
您可以编写一个 vba 用户定义函数来使用趋势线公式来评估给定的 x
下面是一个入门示例
Function TrendLineValue(x As Double) As Double
Dim c As Chart
Dim t As Trendline
Dim s As String
' Get the trend line object
' this code assumes the first chart on the active sheet,
' and the first series, first trendline
Set c = ActiveSheet.ChartObjects(1).Chart
Set t = c.SeriesCollection(1).Trendlines(1)
' make sure equation is displayed
t.DisplayRSquared = False
t.DisplayEquation = True
' set number format to ensure accuracy
' adjust to suit requirements
t.DataLabel.NumberFormat = "0.0000E+00"
' get the equation
s = t.DataLabel.Text
' massage the equation string into form that will evaluate
' this code assumes 3rd order polynomial
s = Replace(s, "y =", "")
s = Replace(s, "x3", "x^3")
s = Replace(s, "x2", "x^2")
s = Replace(s, "x", " * " & x & " ")
' evaluate for given x value
TrendLineValue = Evaluate(s)
End Function
答案2
你可以用一个简单的LINEST公式来解决这个问题（无需图表）
对于三级数组输入
=LINEST(C2:C15,B2:B15^{1,2,3})
分成四个水平单元格，得到方程系数 (aX^3+bX^2+cX+D)，然后代入 X
LINEST对于更复杂的回归来说此处介绍
答案3
我找到了一种适用于每种趋势线（当然，移动平均线除外）的解决方案。您可能需要设置数据标签的精度以满足您的需求。
Option Explicit
'Testdrive for the function
Public Sub main()
Dim sht As Worksheet
Dim graph As ChartObject
Dim formula As String
Dim x As Double
Dim result As String
Set sht = Sheets("graph")
'I have a sheet with one scatter plot in sheet "graph"
Set graph = sht.ChartObjects(1)
'Set the x value to evaluate at
x = 56
result = calcTrendlineValueForX(graph.Chart.SeriesCollection(1).Trendlines(1), x)
Debug.Print "f(" & x & ") = " & result
End Sub
'
' Evaluate a trendline at a certain x
' Param : * {trendline object} The trendline to use
' * {double} the x value
' Return : * {double} The value for a given x
'
Public Function calcTrendlineValueForX(trendline As trendline, xValue As Double) As Double
Dim trendlineWasVisible As Boolean
Dim i As Integer
Dim char As String
Dim preChar As String
Dim newFormula As String
Dim bCharIsPower As Boolean
Dim bPreCharIsPower As Boolean
'If the trendline is a moving average, return 0
If trendline.Type = xlMovingAvg Then
newFormula = "0"
Else
'If equation is logarithmic and x <= 0, return 0
If trendline.Type = xlLogarithmic And xValue <= 0 Then
newFormula = "0"
Else
'Keep track of the style of the trendline.
'You may set the precision here
trendlineWasVisible = trendline.DisplayEquation
'Display the equation of the trenline
If Not trendlineWasVisible Then
trendline.DisplayEquation = True
End If
newFormula = ""
bPreCharIsPower = False
bCharIsPower = False
preChar = ""
'Loop equation char by char
For i = 1 To trendline.DataLabel.Characters.Count
char = Mid(trendline.DataLabel.Characters.Text, i, 1) 'get the actual char
'Look if the char in written in superscript
bCharIsPower = trendline.DataLabel.Characters(i).Font.Superscript
'Treat the superscript
If bCharIsPower And Not bPreCharIsPower Then
newFormula = newFormula & "^("
Else
If Not bCharIsPower And bPreCharIsPower Then
newFormula = newFormula & ")"
preChar = ")"
End If
End If
'if actual char is "x" or "e"
If char = "x" Or char = "e" Then
'If we need to add a "*" before the actual char
If preChar = "x" Or preChar = "e" Or preChar = ")" Or IsNumeric(preChar) Then
newFormula = newFormula & " * " & char
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
Else
'if "ln"
If char = "l" Then
'If we need to add a "*" before the "ln"
If preChar = "x" Or preChar = "e" Or IsNumeric(preChar) Or preChar = ")" Then
newFormula = newFormula & " * l"
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
Else
'Process for numeric
If IsNumeric(char) Then
If preChar = ")" Then
newFormula = newFormula & "*" & char
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
Else
'Add the char to the new formula string
newFormula = newFormula & char
End If
End If
End If
'Keep track of the preceding char
preChar = char
bPreCharIsPower = bCharIsPower
Next i
'Add parenthesis if the formula finishes with a superscript char
If bCharIsPower Then
newFormula = newFormula & ")"
End If
'Put back the trendline equation like it was before
'If you have set the precision, you can set it back here
trendline.DisplayEquation = trendlineWasVisible
'Format the new formula to be understanding by Evaluate() function
newFormula = Replace(newFormula, "y =", "") 'Strips "y ="
newFormula = Replace(newFormula, Application.DecimalSeparator, ".") 'Replace decimal separator
newFormula = Replace(newFormula, "x", xValue) 'Assign the given x
newFormula = Replace(newFormula, "e^", "exp") 'e
newFormula = Replace(newFormula, " ", "") 'Strip spaces (occurs on the formating of some sort)
End If
End If
calcTrendlineValueForX = Evaluate(newFormula)
End Function