Use uma fórmula de linha de tendência para obter valores para qualquer dado X com o Excel

6

Existe uma maneira fácil de aplicar a fórmula da linha de tendência de um gráfico a qualquer valor X no Excel?

Por exemplo, quero obter o valor Y para um determinado X = $ 2.006,00. Eu já peguei a fórmula e redigi-a:

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

Estou continuamente fazendo ajustes na linha de tendência adicionando mais dados e não quero redigitar a fórmula toda vez.

    
por Kirk Ouimet 08.03.2011 / 23:29

3 respostas

3

Você pode escrever uma função definida pelo usuário vba para usar a fórmula de linha de tendência para avaliar um determinado x
Veja um exemplo para começar

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
    
por 09.03.2011 / 06:39
5

Você pode resolver isso com uma simples fórmula LINEST (sem gráficos)

Para uma matriz de terceiro grau, insira
=LINEST(C2:C15,B2:B15^{1,2,3})
em quatro células horizontais para obter seus coeficientes de equação (aX ^ 3 + bX ^ 2 + cX + D) e, em seguida, basta substituir X

LINEST para regressões mais complexas é coberto aqui

    
por 20.09.2011 / 16:16
0

Eu encontrei uma solução que funciona para todos os tipos de linhas de tendência (exceto para a média móvel, é claro). Você pode querer definir a precisão do Datalabel para atender às suas necessidades.

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
    
por 19.07.2013 / 21:40