2025년 3월에 수리했던 스마트 쓰레기통이 또 고장.
1년에 한번씩 수리해야 하는건가?
모터 주문중....
2026. 3. 24.
엑셀 그래프에서 수식을 출력하고 x축값을 계산해주는 VB코드 작성 excel chart graph equation auto calculation VB code
엑셀 그래프에서 수식에서 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
2026. 2. 16.
피드 구독하기:
덧글 (Atom)