엑셀 그래프에서 수식에서 x값을 입력하여 자동 계산하는 VB.
AI 도움으로 작성하였음.
사용법
=trendxx("차트 1", 10) 수식 x=10에서 계산값 출력
=trendxx("차트 1", "R") 수식의 R^2 값 출력
=trendxx("차트 2", ,TRUE) 수식을 엑셀에 표시
그래프에서 변경사항있으면 F9 를 눌러서 다시 계산
제미나이와 claude의 도움을 받아 작성하였음.
===============================================================
' 맨 뒤에 Optional WatchRange As Range 를 추가합니다.
Function TrendXX(Optional ChartName As String, Optional ValX As Variant = 0, Optional blnFormula As Variant = False, Optional idx As Long = 1, Optional WatchRange As Range) As Variant
' Function TrendXX(Optional ChartName As String, Optional ValX As Variant = 0, Optional blnFormula As Variant = False, Optional idx As Long = 1) As Variant
Application.Volatile
Dim Cht As Chart: Dim strFormula As String: Dim formulaOnly As String: Dim result As Double
Dim a As Double, b As Double: Dim i As Long: Dim arrPoly() As String
On Error GoTo ErrorHandler
' --- 차트 로드 부분 (기존과 동일) ---
If Len(ChartName) = 0 Then
If Application.Caller.Parent.ChartObjects.Count = 0 Then TrendXX = CVErr(xlErrNA): Exit Function
Set Cht = Application.Caller.Parent.ChartObjects(1).Chart
Else
Set Cht = Application.Caller.Parent.ChartObjects(ChartName).Chart
End If
With Cht.SeriesCollection(idx).Trendlines(1)
.DisplayEquation = True: .DisplayRSquared = True
strFormula = .DataLabel.Text
End With
' --- 전처리 ---
Dim cleanAll As String
cleanAll = Replace(strFormula, " ", "")
cleanAll = Replace(cleanAll, Chr(11), "|")
cleanAll = Replace(cleanAll, Chr(13), "|")
cleanAll = Replace(cleanAll, Chr(10), "|")
' R2 값만 필요한 경우 처리
If VarType(ValX) = vbString And (LCase(ValX) = "r" Or LCase(ValX) = "r2") Then
Dim rPos As Long: rPos = InStr(cleanAll, "R²=")
If rPos = 0 Then rPos = InStr(cleanAll, "R2=")
If rPos > 0 Then TrendXX = Val(Mid(cleanAll, rPos + 3)): Exit Function
End If
' y= 수식 본체 추출
Dim eqPos As Long: eqPos = InStr(cleanAll, "y=")
If eqPos = 0 Then TrendXX = CVErr(xlErrValue): Exit Function
formulaOnly = Mid(cleanAll, eqPos + 2)
If InStr(formulaOnly, "|") > 0 Then formulaOnly = Split(formulaOnly, "|")(0)
' 수식 텍스트 반환 모드
If blnFormula = True Then TrendXX = "y=" & formulaOnly: Exit Function
' --- 수식 유형별 정밀 계산 ---
' 1. 로그 (ln)
If InStr(formulaOnly, "ln") > 0 Then
arrPoly = Split(Replace(formulaOnly, "ln", "|"), "|")
a = Val(arrPoly(0)): If a = 0 And Left(arrPoly(0), 1) <> "0" Then a = IIf(InStr(arrPoly(0), "-") > 0, -1, 1)
b = Val(Replace(Replace(arrPoly(1), "(x)", ""), "x", ""))
result = a * WorksheetFunction.Ln(ValX) + b
' 2. 다항식 (x2, x^2)
ElseIf InStr(formulaOnly, "x2") > 0 Or InStr(formulaOnly, "x^2") > 0 Or InStr(formulaOnly, "x²") > 0 Then
formulaOnly = Replace(Replace(formulaOnly, "x^2", "x2"), "x²", "x2")
formulaOnly = Replace(formulaOnly, "-", "+-")
arrPoly = Split(formulaOnly, "+")
result = 0
For i = LBound(arrPoly) To UBound(arrPoly)
Dim term As String: term = Trim(arrPoly(i))
If term <> "" Then
If InStr(term, "x2") > 0 Then
a = Val(term): If a = 0 And InStr(term, "0") = 0 Then a = IIf(InStr(term, "-") > 0, -1, 1)
result = result + a * (ValX ^ 2)
ElseIf InStr(term, "x") > 0 Then
a = Val(term): If a = 0 And InStr(term, "0") = 0 Then a = IIf(InStr(term, "-") > 0, -1, 1)
result = result + a * ValX
Else: result = result + Val(term): End If
End If
Next i
' 3. 지수 함수 (e^x)
ElseIf InStr(LCase(formulaOnly), "e") > 0 And InStr(formulaOnly, "x") > 0 And InStr(formulaOnly, "e-") = 0 And InStr(formulaOnly, "e+") = 0 Then
arrPoly = Split(LCase(formulaOnly), "e")
a = Val(arrPoly(0)): If a = 0 Then a = 1
b = Val(Replace(Replace(arrPoly(1), "x", ""), "^", ""))
result = a * Exp(b * ValX)
' 4. 거듭제곱 (Power: y = ax^b) 또는 선형 (Linear: y = ax + b) 판별
ElseIf InStr(formulaOnly, "x") > 0 Then
Dim xPos As Long: xPos = InStr(formulaOnly, "x")
Dim tail As String: tail = Mid(formulaOnly, xPos + 1) ' x 뒷부분 추출
' x 뒤에 +나 -가 없고 숫자가 있다면 "거듭제곱"으로 판단
If InStr(tail, "+") = 0 And (InStr(tail, "-") = 0 Or (InStr(tail, "-") = 1 And Len(tail) > 1)) Then
' --- 거듭제곱 계산 ---
a = Val(Left(formulaOnly, xPos - 1))
If a = 0 And InStr(Left(formulaOnly, xPos - 1), "0") = 0 Then a = 1
b = Val(Replace(tail, "^", "")) ' ^0.0944 에서 숫자만 추출
result = a * (ValX ^ b)
Else
' --- 선형 계산 ---
a = Val(Left(formulaOnly, xPos - 1))
If a = 0 And InStr(Left(formulaOnly, xPos - 1), "0") = 0 Then a = 1
b = Val(tail) ' + 0.1506 에서 숫자 추출
result = (a * ValX) + b
End If
End If
TrendXX = result
Exit Function
ErrorHandler:
TrendXX = CVErr(xlErrValue)
End Function
'-------------------------------
' 해당 워크시트 코드 창에 복사해서 넣으세요.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' 시트에서 셀 선택을 바꿀 때마다 수식을 갱신함
' 차트 설정을 바꾼 후 아무 셀이나 클릭하면 자동으로 업데이트됩니다.
Application.CalculateFull
End Sub
'-------------------------------
Sub DebugTrendLabel()
Dim WS As Worksheet
Dim Cht As Chart
Dim strFormula As String
Dim i As Integer
Dim result As String
Set WS = ActiveSheet
Set Cht = WS.ChartObjects("차트 1").Chart
strFormula = Cht.SeriesCollection(1).Trendlines(1).DataLabel.Text
result = "길이: " & Len(strFormula) & Chr(10)
result = result & "원본: [" & strFormula & "]" & Chr(10) & Chr(10)
For i = 1 To Len(strFormula)
result = result & i & ": [" & Mid(strFormula, i, 1) & "] = " & AscW(Mid(strFormula, i, 1)) & Chr(10)
Next i
WS.Range("E1").Value = strFormula
WS.Range("E2").Value = "길이=" & Len(strFormula)
MsgBox result
End Sub
