Используйте формулу Trendline, чтобы получить значения для любого данного X с Excel

Вы можете решить это с помощью простой LINESTформулы (без построения графиков)

Для массива 3-й степени введите
=LINEST(C2:C15,B2:B15^)
четыре горизонтальные ячейки, чтобы получить коэффициенты уравнения (aX ^ 3 + bX ^ 2 + cX + D), а затем просто замените X

LINESTдля более сложных регрессий рассматривается здесь

Вы можете написать пользовательскую функцию 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 

Я нашел решение, которое работает для каждого типа линий тренда (за исключением, конечно, скользящей средней). Вы можете настроить точность Datalabel в соответствии с вашими потребностями.

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