지수평활법에서는 사용하는 계수(α, β, γ)를 최소제곱법으로 구하는 방법(엑셀 VBA 구하기)

링크

 

목차

1. 지수평활법과 계수들

2. 계수들의 값을 정하는 법

3. 엑셀로 최소제곱법 적용 준비하기

4. 엑셀로 최소제곱법을 적용하여 계수 구하기

5. 엑셀의 FORECAST.ETS.STAT 함수 사용하기

 

 

1. 지수평활법과 계수들

 

시간에 따라 움직이는 데이터를 분석해서 미래값을 예상하는 지수평활법에서는 각각에 계수를 사용합니다.

계수에 딱히 정해진 이름은 없지만, 이 포스팅에서는 α, β, γ를 사용하겠습니다.

수준(L_t : Level)에는 α, 추세(T_t : Trend)에 β, 계절성(S_t : Seansonality )의 γ로 하고,

삼중지수평활법 (Triple Exponential Smoothing)의 형태는 아래와 같습니다.

 

삼중지수평활법 (Triple Exponential Smoothing)

 

계수들은 특별하게 정해진 규칙은 없지만, 1보다 작은 값으로 구성되며 보통은 보통은 아래범위에서 사용합니다.

 

α (수준 평활 상수): 0.1 ~ 0.3

β (추세 평활 상수): 0.01 ~ 0.3

γ (계절성 평활 상수): 0.01 ~ 0.3

 

 

2. 계수들의 값을 정하는 법

 

계수들의 값을 정하는 법, 역시 이거다 하는 규칙이나 공식은 없습니다.

보통은 아래 3가지 중에 하나는 사용하고는 합니다.

 

① 직관적으로 결정한다.

그래프의 과거 데이터와 가장 잘 맞는 값을 직관적으로 선택합니다.

민감도가 작을 수록 값을 작게, 클 수록 큰 값이 됩니다.

 

② 최적화 알고리즘을 사용한다.

일부 통계 프로그램에선 최적회된 알고리즘을 제공합니다.

Python이 대표적인 예로 scipy.optimize 가 있습니다.

 

③ 최소제곱법(Least Squares Method)

최소자승법이라고 불리기도 하는 방법으로 기존의 관측값과 계산을 수행한 값들의 차가 최소가 되는 계수를 구합니다.

즉 과거의 데이터로 이미 관측된 값을 계산하여 가장 오차가 적은 계수값을 적용하는 방식입니다.

오늘은 이걸 가지고 포스팅해 보겠습니다.

 

 

3. 엑셀로 최소제곱법 적용 준비하기

 

엑셀에서는 최적화된 알고리즘을 지원하지만 이번에는 VBA로 최소제곱법으로 구해보겠습니다.

대부분의 사람들이 엑셀을 가지고 있고 가벼운 소스임으로 지수평활법을 사용하는 원리를 알기 위해 계산해 보겠습니다.

그 전에 준비물이 몇개 있습니다.

 

① 해 찾기 추가 기능

 

VBA에서는 최소제곱법으로 해를 찾는 Solve 기능을 지원하는데 사용하기 위해서는 우선 엑셀에서 먼처 활성화 합니다.

[상단매뉴] - [파일] - [Excel 옵션] - [추가 기능] - [이동] - [해 찾기 추가 기능]을 활성화 시킵니다.

그럼 데이터 매뉴에서 해 찾기가 추가된 것도 확인할 수 있습니다.

 

해 찾기 기능 활성화 하기

 

② Solver 참조하기

 

엑셀의 VBA 인터페이스에서는 추가 기능을 참조할 수 있습니다.

상단의 매뉴에서 [도구] - [참조] - [Solver를 활성화] 합니다.

이제 해찾기 매뉴인 Solver를 VBA에서 사용할 수 있습니다.

 

Solver를 참조합니다

 

 

4. 엑셀로 최소제곱법을 적용하여 계수 구하기

 

Solver 명령어와 그 파생기능을 VBA에서 사용할 수 있습니다.

이 기능을 이용해서 VBA 로 지수평활법의 계수를 구해보도록 하겠습니다.

 

Sub TripleExponentialSmoothingOptimization()
    Dim data As Variant
    Dim alpha As Double
    Dim beta As Double
    Dim gamma As Double
    Dim L() As Double
    Dim Te() As Double
    Dim S() As Double
    Dim forecast() As Double
    Dim i As Integer, t As Integer, m As Integer
    Dim n As Integer

'-------------------------------------------------------------------------------------
    '① 입력 데이터를 찾고 배열을 만듭니다.
    '입력 데이터
    data = Range("A2:A102").Value ' 데이터는 A2:A102 범위에 있습니다.
    m = 12 ' 계절 주기
    n = UBound(data) - LBound(data) + 1

    ReDim L(1 To n + m)
    ReDim Te(1 To n + m)
    ReDim S(1 To m)
    ReDim forecast(1 To n + m) ' 12개월 예측



'-------------------------------------------------------------------------------------
    '② 지수평활법의 초기값들을 입력합니다.
    
    ' 초기 수준과 초기 계절성 계산
    L(1) = Application.WorksheetFunction.Average(Range("A2:A13"))
    For i = 1 To m
        S(i) = data(i, 1) / L(1)
    Next i

    ' 초기 추세 계산
    Te(1) = (Application.WorksheetFunction.Average(Range("A14:A25")) - Application.WorksheetFunction.Average(Range("A2:A13"))) / m

    ' 초기값으로 사용할 평활 상수
    alpha = 0.1
    beta = 0.1
    gamma = 0.1

    ' 초기 알파, 베타, 감마 값을 시트에 입력
    Range("G2").Value = alpha
    Range("H2").Value = beta
    Range("I2").Value = gamma


'-------------------------------------------------------------------------------------
    '③ 주어진 값들로 이미 있는 데이터와 차이를 구하는 CalulateSSE 함수를 만들었습니다.
    

    ' 목표 함수 셀에 CalculateSSE 결과 표시
    Range("J2").Formula = "=CalculateSSE(A2:A102, G2, H2, I2)"
    
'-------------------------------------------------------------------------------------
    '④ Solver 작동시킵니다.
        
    
    SolverReset
    SolverOptions Precision:=0.000001, Convergence:=0.0001, StepThru:=False
    SolverOk SetCell:=Range("J2"), MaxMinVal:=2, ValueOf:=0, ByChange:=Range("G2:I2")
    SolverAdd CellRef:=Range("G2:I2"), Relation:=1, FormulaText:="1"
    SolverAdd CellRef:=Range("G2:I2"), Relation:=3, FormulaText:="0.0000001"
    SolverSolve UserFinish:=True
    
    
    ' 최적화된 알파, 베타, 감마 값을 사용하여 삼중지수평활법 계산
    alpha = Range("G2").Value
    beta = Range("H2").Value
    gamma = Range("I2").Value
    
'-------------------------------------------------------------------------------------
    '⑤ 최적화 된 계수 값으로 수준, 추세, 계절성를 구합니다.
    
    
    forecast(2) = (L(1) + Te(1)) * S(1)
    
    For t = 2 To m
        L(t) = alpha * (data(t, 1) / S((t - 1) Mod m + 1)) + (1 - alpha) * (L(t - 1) + Te(t - 1))
        Te(t) = beta * (L(t) - L(t - 1)) + (1 - beta) * Te(t - 1)
        forecast(t + 1) = (L(t) + Te(t)) * S((t - 1) Mod m + 1)
    Next t
    

    For t = m + 1 To n
        L(t) = alpha * (data(t, 1) / S((t - 1) Mod m + 1)) + (1 - alpha) * (L(t - 1) + Te(t - 1))
        Te(t) = beta * (L(t) - L(t - 1)) + (1 - beta) * Te(t - 1)
        S((t - 1) Mod m + 1) = gamma * (data(t, 1) / L(t)) + (1 - gamma) * S((t - 1) Mod m + 1)
        forecast(t + 1) = (L(t) + Te(t)) * S((t - 1) Mod m + 1)
    Next t

'-------------------------------------------------------------------------------------
    '⑤ 그래프에 없는 예측값을 구합니다.
    
     
    For t = n + 1 To n + 12
    
        L(t) = alpha * (data(UBound(data), 1) / S((t - 1) Mod m + 1)) + (1 - alpha) * (L(t - 1) + Te(t - 1))
        Te(t) = beta * (L(t) - L(t - 1)) + (1 - beta) * Te(t - 1)
        S((t - 1) Mod m + 1) = gamma * (data(UBound(data), 1) / L(t)) + (1 - gamma) * S((t - 1) Mod m + 1)
    
        forecast(t) = (L(t) + (t - UBound(data)) * Te(t)) * S((t - 1) Mod m + 1)
    Next t

    ' 결과 출력
    For i = 1 To n + m
        Cells(i + 1, 2).Value = L(i) ' 수준
        Cells(i + 1, 3).Value = Te(i) ' 추세
        Cells(i + 1, 4).Value = S((i - 1) Mod m + 1) ' 계절성
        Cells(i + 1, 5).Value = forecast(i) ' 예측값
    Next i

    ' 향후 12개월 예측 출력
    For i = n + 1 To n + 12
        Cells(i + 1, 5).Value = forecast(i)
    Next i
End Sub

'----------지수평활법을 사용하기 위한 함수를 만듭니다.-----------

Function CalculateSSE(dataRange As Range, alphaCell As Range, betaCell As Range, gammaCell As Range) As Double
    Dim data As Variant
    Dim alpha As Double
    Dim beta As Double
    Dim gamma As Double
    Dim L() As Double
    Dim Te() As Double
    Dim S() As Double
    Dim n As Integer
    Dim m As Integer
    Dim t As Integer
    Dim SSE As Double

    data = dataRange.Value
    alpha = alphaCell.Value
    beta = betaCell.Value
    gamma = gammaCell.Value
    n = UBound(data)
    m = 12

    ReDim L(1 To n)
    ReDim Te(1 To n)
    ReDim S(1 To m)
'-------------------------------------------------------------------------------------
    '③-1. 에러값을 구하기 위해 별도로 지수평활법을 실행합니다.
    
  
    ' 초기 수준과 초기 계절성 계산
    L(1) = Application.WorksheetFunction.Average(dataRange.Cells(1, 1).Resize(m))
    For i = 1 To m
        S(i) = data(i, 1) / L(1)
    Next i

    ' 초기 추세 계산
    Te(1) = (Application.WorksheetFunction.Average(dataRange.Cells(m + 1, 1).Resize(m)) - L(1)) / m

'-------------------------------------------------------------------------------------
    '③-2. 지수평활법을 수행하여 관측된 데이터와 차이를 계산합니다.
    
    ' 삼중지수평활법 계산 및 SSE 계산
    SSE = 0
    For t = m + 1 To n
        L(t) = alpha * (data(t, 1) / S((t - 1) Mod m + 1)) + (1 - alpha) * (L(t - 1) + Te(t - 1))
        Te(t) = beta * (L(t) - L(t - 1)) + (1 - beta) * Te(t - 1)
        S((t - 1) Mod m + 1) = gamma * (data(t, 1) / L(t)) + (1 - gamma) * S((t - 1) Mod m + 1)
        SSE = SSE + (data(t, 1) - (L(t) + Te(t)) * S((t - 1) Mod m + 1)) ^ 2
    Next t

    CalculateSSE = SSE
End Function

 

 

꽤 긴소스가 되었지만 실제로 구성은 간단합니다.

 

① 입력 데이터를 찾고 배열을 만듭니다.

이번에 사용해야 할 것은 수준(L), 추세(T), 계절성(S)와 예측값 Futrure(F)입니다.

그리고 기존의 입력한  DATA 행렬을 포함해서 작업 영역을 만듭니다.

 

② 지수평활법의 초기값들을 입력합니다.

계산을 수행할 초기값들을 입력합니다.

수준(L), 추세(T), 계절성(S)과 이번에 구할 계수 α, β, γ 들의 초기값을 입력합니다.

지수평활법에는 일반적인 값을 입력하고 계수는 0.1을 일괄적으로 입력합니다.

 

③ 주어진 값들로 이미 있는 데이터와 차이를 구하는 CalulateSSE 함수를 만들었습니다.

함수로 만들면 Solver를 적용하기 쉽습니다.

③-1. 함수인 CalulateSSE에는 에러값을 구하기 위해 별도로 지수평활법을 실행합니다.

③-2. 지수평활법을 수행하여 관측된 데이터와 차이-에러를 계산합니다.

 

④ Solver 작동시킵니다.

계수값을 변경하면서 Error인 출력값이 최소가 될 때까지 수행합니다.

정밀도를 0.000001수준으로 하여 고밀도록 작업하게는 했지만 이렇게 까지는 필요없습니다.

 

⑤ 최적화 된 계수 값으로 수준, 추세, 계절성를 구합니다.

⑥ 1주기의 예측값을 구합니다.

 

이렇게해서 구한 예측값입니다.

예측값이 정확한 것처럼 보이시나요?

조금 부정확한 면도 있는 것 같습니다.

 

알고리즘을 통해 예측한 값들

 

 

5. 엑셀의 FORECAST.ETS.STAT 함수 사용하기

 

엑셀에서는 계수를 구하기 위한 함수를 제공합니다.

아래 링크는 각 계수를 추출하는 방식에 대해서 FORECAST.ETS.STAT 함수에 대한 설명입니다.

 

https://toast-story.tistory.com/457#C3

엑셀(EXCEL)로 지수평활법의 ETS 모델을 사용하는 FORECAST.ETS 함수와 파생함수인 SEASONALITY / STAT 함수

 

 

---

 

< 지수평활법과 ETS 관련 포스팅 > 

 

01. 시계열 분석에서 미래를 예측하는 이동평균법(SMA), 지수평활법(ES)으로 미래값을 예상하고 풀어보기

02. 지수평활법으로 데이터를 분석할 때 계절성과 주기를 파악하는 방법(Exponential Smoothing의 Seasonality)

03. 시계열 예측에서 지수평활법의 기본 모델(Additive)과와 확장 형태인 감쇠 (Damped) 모델들의 정의

04. 시계열 예측에서 계절성에 따른 추세가 변하는 승법적 모델과(Multiplicative Model), 감쇠하는 승법적 모델(Damped Multiplicative Model)

05. 상황에 맞게 지수평활법과 ETS 모델(Exponential Triple Smoothing)을 사용해서 시계열 예측을 수행하기

06. 지수평활법에서는 사용하는 계수(α, β, γ)를 최소제곱법으로 구하는 방법(엑셀 VBA 구하기) - 현재글

07. 엑셀(EXCEL)로 지수평활법의 ETS 모델을 사용하는 FORECAST.ETS 함수와 파생함수인 SEASONALITY / STAT 함수

08. 엑셀(EXCEL)의 FORECAST.ETS 함수의 오차를 계산하는 CONFINT 함수(지수평활법을 사용하는 미래 예측 함수)