Verwenden Sie eine Trendline-Formel, um Werte für ein beliebiges gegebenes X mit Excel abzurufen

Sie können dies mit einer einfachen LINESTFormel (ohne Diagramm) lösen.

Geben Sie für ein Array dritten Grades
=LINEST(C2:C15,B2:B15^)
vier horizontale Zellen ein, um die Gleichungskoeffizienten (aX ^ 3 + bX ^ 2 + cX + D) zu erhalten, und ersetzen Sie dann einfach X

LINESTfür komplexere Regressionen wird hier eingegangen

Sie können eine vom Benutzer definierte vba-Funktion schreiben, um mit der Trendlinienformel ein gegebenes x auszuwerten.
Hier ist ein Beispiel, um zu beginnen

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 

Ich habe eine Lösung gefunden, die für alle Arten von Trendlinien funktioniert (abgesehen vom gleitenden Durchschnitt). Sie können die Genauigkeit des Datalabels an Ihre Anforderungen anpassen.

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 : * The trendline to use ' * the x value ' Return : * 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