이 페이지에서

실무 위키›VBA 코드›TrendX

TrendX

Function

차트 추세선의 Y 값을 실시간으로 계산하거나 추세선 수식을 출력하는 VBA 사용자 지정 함수입니다.

예제파일 다운로드

구문 = TrendX ( [차트이름], [X값], [수식출력], [범례번호] )

인수구분 형식 설명

차트이름 선택 String

추세선을 불러올 차트의 이름입니다. 기본값은 함수가 입력된 시트의 첫번째 차트입니다.

차트 이름은 차트를 선택한 후 수식입력줄 왼쪽의 이름 상자에서 확인할 수 있습니다.

X값 선택 Double

Y 결과값을 도출할 X 값입니다. 기본값은 0 입니다.

숫자만 입력할 수 있습니다.

수식출력 선택 Boolean

TRUE일 경우 Y값을 계산하지 않고 수식을 출력합니다. 기본값은 FALSE 입니다.

범례번호 선택 Long

차트에 범례가 여러개 있을 경우, 추세선이 추가된 범례의 순번을 입력합니다. 기본값은 1 입니다.

마스터 코드

복사한 코드는 VBA 편집기(Alt + F11) 새 모듈에 붙여넣어 사용하세요

Function TrendX(Optional ChartName As String, Optional Val As Double, Optional blnFormula As Boolean = False, Optional idx As Long = 1)
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'수정 및 배포 시 출처를 반드시 명시해야 합니다.
 
'■ TrendX 함수
'■ 특정 차트의 추세선 수식을 출력하거나 X값에 따른 Y결과값을 계산합니다.
'■ 사용방법
'TrendX( 차트명, X값, [수식출력], [범레번호] )
'■ 인수 설명
'_____________ChartName         : [선택인수] 차트이름을 입력합니다. 값을 입력하지 않을 경우 시트 첫번째 차트를 대상으로 동작합니다.
'_____________Val               : [선택인수] Y 결과값을 도출할 X 값입니다. 기본값은 0 입니다.
'_____________blnFormula        : [선택인수]TRUE일 경우 추세선 수식을 반환합니다. FALSE일 경우 계산된 Y값을 반환합니다. 기본값은 FALSE 입니다.
'_____________idx               : [선택인수]차트에 여러 범례가 있을 경우, 추세선이 입력된 대상 범례 번호를 입렵합니다. 기본값은 1 입니다.
'###############################################################
Dim Cht As Chart: Dim WS As Worksheet
Dim strFormula As String: Dim result As Double
Dim strTemp As String
Dim arr As Variant: Dim i As Long
Dim vSplitsP As Variant: Dim vSplitP As Variant
Dim vSplitsM As Variant: Dim vSplitM As Variant

Set WS = Application.Caller.Parent
If Len(ChartName) = 0 Or IsMissing(ChartName) Then
    Set Cht = WS.ChartObjects(1).Chart
Else
    Set Cht = WS.ChartObjects(ChartName).Chart
End If

If Cht.SeriesCollection(idx).Trendlines.Count = 0 Then TrendX = CVErr(xlErrNull): Exit Function

With Cht.SeriesCollection(idx).Trendlines(1)
.DisplayRSquared = False
.DisplayEquation = True
strFormula = .DataLabel.Text
End With

If strFormula = "" Then TrendX = CVErr(xlErrNull): Exit Function

If blnFormula = True Then TrendX = strFormula: Exit Function
If InStr(1, strFormula, "ln(x)") > 0 Then
    ReDim arr(0 To 0)
    arr(0) = Application.WorksheetFunction.Ln(Val)
    strFormula = Replace(strFormula, "ln(x)", "*" & CStr(arr(0)))
    strFormula = Replace(strFormula, "(", "-")
    strFormula = Replace(strFormula, ")", "")
ElseIf InStr(1, strFormula, "e") > 0 Then
    ReDim arr(0 To 0)
    strFormula = Replace(strFormula, "(", "-")
    strFormula = Replace(strFormula, ")", "")
    i = InStr(1, strFormula, "e")
    arr(0) = Exp(CDbl(Replace(Right(strFormula, Len(strFormula) - i), "x", "")) * Val)
    strFormula = Left(strFormula, i - 1)
    strFormula = strFormula & "*" & arr(0)
Else
    For i = 1 To 6
    strFormula = Replace(strFormula, "x" & i, "x^" & i)
    Next
    strFormula = Replace(strFormula, "E-", "*10^|")
    strFormula = Replace(strFormula, "E+", "*10^")
    strFormula = Replace(strFormula, "x", "*" & Val)
End If

strFormula = Replace(strFormula, " ", "")
strFormula = Replace(strFormula, ",", "")
strFormula = Replace(strFormula, "y=", "")

vSplitsP = Split(strFormula, "+")

For Each vSplitP In vSplitsP
    vSplitsM = Split(vSplitP, "-")
    For i = 0 To UBound(vSplitsM)
        If i = 0 Then
            If vSplitsM(i) <> "" Then
            
            result = result + Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))
            End If
        Else
              result = result - Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))
        End If
    Next
Next

TrendX = result

End Function

Module1 · TrendX

Function TrendX(Optional ChartName As String, Optional Val As Double, Optional blnFormula As Boolean = False, Optional idx As Long = 1)

'###############################################################

'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)

'수정 및 배포 시 출처를 반드시 명시해야 합니다.

'■ TrendX 함수

'■ 특정 차트의 추세선 수식을 출력하거나 X값에 따른 Y결과값을 계산합니다.

'■ 사용방법

'TrendX( 차트명, X값, [수식출력], [범레번호] )

'■ 인수 설명

'_____________ChartName : [선택인수] 차트이름을 입력합니다. 값을 입력하지 않을 경우 시트 첫번째 차트를 대상으로 동작합니다.

'_____________Val : [선택인수] Y 결과값을 도출할 X 값입니다. 기본값은 0 입니다.

'_____________blnFormula : [선택인수]TRUE일 경우 추세선 수식을 반환합니다. FALSE일 경우 계산된 Y값을 반환합니다. 기본값은 FALSE 입니다.

'_____________idx : [선택인수]차트에 여러 범례가 있을 경우, 추세선이 입력된 대상 범례 번호를 입렵합니다. 기본값은 1 입니다.

'###############################################################

Dim Cht As Chart: Dim WS As Worksheet

Dim strFormula As String: Dim result As Double

Dim strTemp As String

Dim arr As Variant: Dim i As Long

Dim vSplitsP As Variant: Dim vSplitP As Variant

Dim vSplitsM As Variant: Dim vSplitM As Variant

Set WS = Application.Caller.Parent

If Len(ChartName) = 0 Or IsMissing(ChartName) Then

Set Cht = WS.ChartObjects(1).Chart

Else

Set Cht = WS.ChartObjects(ChartName).Chart

End If

If Cht.SeriesCollection(idx).Trendlines.Count = 0 Then TrendX = CVErr(xlErrNull): Exit Function

With Cht.SeriesCollection(idx).Trendlines(1)

.DisplayRSquared = False

.DisplayEquation = True

strFormula = .DataLabel.Text

End With

If strFormula = "" Then TrendX = CVErr(xlErrNull): Exit Function

If blnFormula = True Then TrendX = strFormula: Exit Function

If InStr(1, strFormula, "ln(x)") > 0 Then

ReDim arr(0 To 0)

arr(0) = Application.WorksheetFunction.Ln(Val)

strFormula = Replace(strFormula, "ln(x)", "*" & CStr(arr(0)))

strFormula = Replace(strFormula, "(", "-")

strFormula = Replace(strFormula, ")", "")

ElseIf InStr(1, strFormula, "e") > 0 Then

ReDim arr(0 To 0)

strFormula = Replace(strFormula, "(", "-")

strFormula = Replace(strFormula, ")", "")

i = InStr(1, strFormula, "e")

arr(0) = Exp(CDbl(Replace(Right(strFormula, Len(strFormula) - i), "x", "")) * Val)

strFormula = Left(strFormula, i - 1)

strFormula = strFormula & "*" & arr(0)

Else

For i = 1 To 6

strFormula = Replace(strFormula, "x" & i, "x^" & i)

strFormula = Replace(strFormula, "E-", "*10^|")

strFormula = Replace(strFormula, "E+", "*10^")

strFormula = Replace(strFormula, "x", "*" & Val)

End If

strFormula = Replace(strFormula, " ", "")

strFormula = Replace(strFormula, ",", "")

strFormula = Replace(strFormula, "y=", "")

vSplitsP = Split(strFormula, "+")

For Each vSplitP In vSplitsP

vSplitsM = Split(vSplitP, "-")

For i = 0 To UBound(vSplitsM)

If i = 0 Then

If vSplitsM(i) <> "" Then

result = result + Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))

End If

Else

result = result - Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))

End If

TrendX = result

End Function

활용 예제

시트 내 첫번째 차트의 추세선 수식을 출력하기

=TrendX(,,True)

'시트 내 첫번째 차트의 추세선 수식을 출력합니다.

"차트 1"의 추세선에서 x가 100일 경우 y값 예측하기

=TrendX("차트 1",100)

'x가 100일 경우 y값을 계산합니다.

안내사항

추세선 수식을 지원하는 지수·선형·로그·다항식·거듭제곱 추세선에서 사용할 수 있으며, 이동평균 추세선에서는 사용할 수 없습니다.

5 (7개 평가)

유리화뉘 2021.04.29 08:28

안녕하세요. 덕분에 많이 배우고 있습니다.
아래 구문의 뜻이 무엇인지 궁굼해서 여쭤봅니다..
Application.Evaluate(Replace(vSplitsM(i), "^|", "^-"))
"^|", "^-" 의 의미가 어떤건가요??
그럼 잘 부탁 드립니다.
감사합니다.~~

오빠두엑셀 작성자 2021.05.07 02:01

안녕하세요.
^|과 ^-는 지수표시를 구분하기 위한 일종의 구분자로 큰 의미는 없습니다.
^|, ^- 대신, ^** , ^- 등으로 바꿔서 사용하셔도 무방합니다.
제시해드린 답변이 도움이 되셨길 바랍니다. 감사합니다.

천재지변 2021.05.31 16:38

ㅋㅋ 그냥 ^^ 웃는 표시인줄...;;;;;

이창원 2021.05.02 17:45

good !!!!

이상헌 2021.11.23 10:38

안녕하세요. 항상 많이 배우고 있습니다. 감사합니다.

TrendX와 관련된 문의 사항 Q&A에 올렸는데 답변주신다면
정말 감사하겠습니다.

https://www.oppadu.com/question/?uid=23837&mod=document&pageid=1

올린 댓글이 안보여 두개나 달았습니다. 죄송합니다.

오빠두엑셀 작성자 2021.11.24 18:44

이상헌님 안녕하세요?^^
댓글 남겨 드렸으니 확인해보시길 바랍니다.
감사합니다.

ju0**** 2023.03.16 23:49

안녕하세요! 처음 뵙겠습니다. 제가 이 포스팅과 홈페이지를 처음 방문해서 잘 몰라서 그러는데요, 혹시 위의 명령문은 어디에 쓰는 건가요..?? 파이썬 같은 곳에 입력하는 건가요..??

오빠두엑셀 작성자 2023.03.18 02:37

안녕하세요.
엑셀 VBA 매크로 편집기에 작성하면 됩니다.

taz**** 2023.04.18 15:01

안녕하세요. 실상업무중에 유용하게 사용하고있습니다.

해당 매크로를 사용하는 중 데이터 유효성 검사의 목록을 이용하여 차트를 사용하고 있는데요 그때 추세선에 대한 값을 불러오기위하여 위의 매크로를 사용합니다.

그런데 목록선택후에 값이 바로 반영되는게 아니라서 새로 고침을 진행해야지 추세값이 표기되는데요 해결 방법이 있을까요??

위의 댓글중 (이상헌 2021년 11월 23일 10:38 오전) 님의 코드에 적용을 해봤을때도 유효성 검사를 이용하면 바로 반영이 안되더라구요... ㅠㅠ

럽큘 2023.08.25 15:25

선생님 좋은 강의 감사드립니다.

수식
y = 0.009ln(x) + 0.1322R² = 0.9963

= trendX("차트 1", 1) --> 답 0.1322
= trendX("차트 1", 0.5) --> 오류
아마 ln(0.5) = -0.693147181 로 음수가 나와서 오류가 나는 것 같습니다.
엑셀에 수식입력을 하면 답이 나오거든요.
= 0.009* LN(0.5) + 0.1322 = 0.125961675
VBA어디를 수정해야 오류가 수정될까요?
좋은 강의 감사드립니다.

오빠두엑셀 작성자 2023.08.28 16:54

안녕하세요.

 "ln(x)", "*" & CStr(arr(0))

이 부분을

 "ln(x)", "*" & CDbl(arr(0))

로 수정해보세요.
감사합니다.

럽큘 2023.08.29 08:55

선생님 감사합니다. 해결되었습니다.

강민준🤗 2024.08.11 13:07

좋은 자료 감사합니다.🙇‍♂️

TrendX

마스터 코드

활용 예제

안내사항

바로가기

커뮤니티

문의

[공지] 오빠두에듀 프리랜서 보조 강사 모집

[직원 채용] 오빠두엑셀이 정규직 동료를 모십니다

두드림 무료 챌린지, 새단장 프로모션