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


Есть ли простой способ применить форму линии тренда от диаграммы к любому заданному значению X в Excel?

Например, я хочу получить значение Y для данного X = $ 2,006.00. Я уже принял формулу и перепечатал ее:

= -0,000000000008 * X ^ 3 - 0,00000001 * X ^ 2 + 0,0003 * X - 0,0029

Я постоянно вношу коррективы в линию тренда, добавляя больше данных и не хочу повторять формулу каждый раз.

enter image description here


6
2018-03-08 22:29


происхождения


Прежде чем вы некритически используете значения, отображаемые Excel, проверьте этот поток, superuser.com/questions/194011/excel-trendline-accuracy/... - W_Whalley


ответы:


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

3
2018-03-09 05:39



Это допинг. Спасибо Крису. знак равно - Kirk Ouimet
Спасибо за это, это здорово, хотя немного проще было бы использовать s = Replace(s, "x", "x^") :) - Buksy
@Busky, но это поймает x тоже. - chris neilsen


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

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

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


5
2017-09-20 14:16



Я не хочу проголосовать за формулу vba, но я хочу сказать, что LINEST намного проще, чем подход VBA, потому что он использует вычисления напрямую, а не формулу, которая не может быть отформатирована для достаточной точности (см. Ранее W_Whalley комментарий: используйте формат номера 0,000000000000E + 00, чтобы улучшить точность формулы линии тренда). - Jon Peltier


Я нашел решение, которое работает для каждого типа линий тренда (за исключением скользящего среднего). Вы можете установить точность 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      : * {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

0
2017-07-19 19:40