Excel에서 추세선 수식을 사용하여 주어진 X의 값 얻기

Question

차트의 추세선 수식을 Excel의 특정 X 값에 적용하는 쉬운 방법이 있습니까?

예를 들어, 주어진 X = $2,006.00에 대한 Y 값을 얻고 싶습니다. 나는 이미 공식을 취하여 다음과 같이 다시 입력했습니다.

=-0.000000000008*X^3 - 0.00000001*X^2 + 0.0003*X - 0.0029

더 많은 데이터를 추가하여 추세선을 지속적으로 조정하고 있으며 매번 수식을 다시 입력하고 싶지 않습니다.

Answer 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

Answer 2

LINEST(차트 없이) 간단한 공식으로 이 문제를 해결할 수 있습니다.

3급의 경우배열 입력
=LINEST(C2:C15,B2:B15^{1,2,3})
4개의 수평 셀에 방정식 계수(aX^3+bX^2+cX+D)를 얻은 다음 X로 대체합니다.

LINEST더 복잡한 회귀 분석은여기에서 다루었습니다

Answer 3

저는 모든 유형의 추세선에 적합한 솔루션을 찾았습니다(물론 이동 평균은 제외). 필요에 맞게 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