통계하는 가장 큰 이유는 예측이 아닐까요. 누구든 미래를 알고 싶습니다. 다음 분기 매출을 알고 싶고 내일 주가를 알고 싶고 부동산 시세를 알고 싶고, 내일 비가 올지 안 올지를 알고 싶습니다.
회귀분석처럼 변수 사이 관계를 밝혀내서 미래를 예측하기도 하지만, 지금까지 변수가 변하는 과정을 토대로 미래를 예측하기도 합니다. 시계열(Time series) 분석은 과거 자료의 행태를 분석해 미래를 예측하는 분석방법입니다.
오늘은 이중 단순이동평균법, 가중 이동평균법, 지수평활법을 알아봅시다.
단순이동평균법
단순이동평균법(Simple Moving Average)은 아주 쉽습니다. 한 문장으로 말하자면 ‘내일 수치는 최근 며칠 수치의 평균이다’입니다. 즉 최근 데이터의 평균으로 다음 데이터를 예측하는 것이죠.
미래를 예측하기 전에 지금까지 모은 자료로 단순이동평균법을 실행합니다. 왜 이미 있는 자료까지 예측할까요? 어느 정도까지 평균을 낼지, 즉 n을 정해야 하기 때문입니다.
n은 얼마로 해야 좋을까요? 너무 최근 값만 반영하면 특이치에 휘둘립니다. 너무 오랜 값까지 반영하면 최근 추세가 묻힐 수 있습니다. n을 바꿔 가면서 원래 그래프와 제일 비슷한 그래프를 만드는 n을 골라야 하겠죠.
아니면 수치로 재 볼까요? 정확도를 재는 수치로는 평균절대편차(Mean Absolute Deviation, MAD)나 평균제곱오차(Mean Squared Error, MSE)가 있습니다. 엑셀로 통계하기를 봐 오셨다면 MSE는 익숙하시겠죠. MAD는 오차를 제곱하는 대신 절댓값을 구한 편차입니다. 정확도를 나타내는 수치, 특히 MSE가 최소가 되는 n을 정하는 것도 방법입니다.
엑셀에서 이동평균법 하기
1) AVERAGE 함수 이용하기
AVERAGE 함수에 원하는 기간 만큼 과거 자료를 넣습니다. 드래그해 자동으로 밑 셀을 채우면 과거 자료로 평균을 구할 수 있습니다.
(원하면 옆에 MAD나 MSE를 계산해도 좋습니다.)
2) 데이터 분석 도구
[데이터 분석]에 들어가 [이동 평균법]을 고릅니다.
원본 데이터를 선택하고(‘첫째 행 이름표 사용’을 선택하면 제목을 포함해 선택 가능합니다.) 구간을 씁니다. 구간이 바로 n입니다. 결과를 낼 셀 범위를 선택합니다.
가중 이동평균법
평균에는 그냥 자료 수로 나누는 평균도 있지만(속된 말로 n빵) 조금 고급스러운(?) 평균도 있습니다. 가중평균은 자료마다 다른 가중치를 두어 평균을 냅니다. 일반적 평균은 모든 자료 가중치가 1입니다. 이 숫자를 바꾸면 가중평균이 됩니다. 예를 들어 신뢰도가 더 높은 자료나 더 최신 자료라면 가중치를 높여서 평균에 주는 영향을 키울 수 있습니다.
가중 이동평균법(Weighted Moving Average)은 이동평균법을 가중평균으로 시행합니다. 최근 자료에 가중치를 더 준다면 최근 수치를 더 많이 반영하겠죠.
엑셀에서 가중 이동평균법 하기
n과 가중치를 정한 다음에(가중치는 전부 합해 1이어야 합니다) 함수로 계산합니다. 행렬을 곱하는 SUMPRODUCT 함수를 추천합니다.
엑셀에서 가중치 찾기
MSE가 최소가 되는 가중치를 찾고 싶다면 방법이 하나 있습니다.
[파일] - [옵션]에서 추가 기능 중에 ‘해 찾기 추가 기능’이 있습니다. 원하는 셀이 원하는 값이 되는 해를 찾는다는 점에선 목표값 찾기와 비슷합니다.
해 찾기 기능을 쓰기 전에 MSE를 계산해 놓습니다.
목표 설정 : MSE가 나올 셀을 고릅니다.
대상 : 우린 MSE가 최소이길 원하니까 '최소'를 고릅니다.
변수 셀 변경 : 가중치를 쓴 셀 범위입니다.
제한 조건에 종속 : '추가'를 눌러 규칙을 추가합니다. 가중치는 0 이상 1 이하고 가중치 총합은 1입니다. 사진에 보이는 대로 입력합니다.
지수평활법
지수평활법(Exponential Smoothing)의 개념은 이렇습니다.
‘내일 수치는 일정 비율은 오늘 값, 일정 비율은 오늘 예측값을 합친 것이다.’
이 ‘일정 비율’은 그리스 문자 α로 나타냅니다. 이동평균법의 n이나 가중 이동평균법의 가중치처럼 현재 자료를 가장 잘 재현하는 α를 찾아야겠죠.
엑셀에서 지수평활법 하기
[데이터 분석] - [지수 평활법]에 들어갑니다. 입력범위에 자료를 넣습니다.
(감쇠 인수는 1-α입니다)
엑셀 지수평활법은 지금 자료를 재현하는 기능만 지원합니다. 원하는 α를 구하셨다면 직접 수식을 입력해 미래 수치를 예측해야 합니다.
지금까지 보신 지수평활법은 단순지수평활법입니다. 이중지수평활법, 삼중지수평활법(계절지수평활법)은 단순지수평활법과 달리 자료의 추세나 계절성을 반영합니다.
'엑셀 > 엑셀로 통계하기' 카테고리의 다른 글
| 엑셀로 통계하기 24 - 다중회귀분석 (2) | 2018.06.25 |
|---|---|
| 엑셀로 통계하기 23 - 잔차분석 (0) | 2018.06.22 |
| 엑셀로 통계하기 22 - 단순선형회귀(2) (3) | 2018.06.21 |
| 엑셀로 통계하기 21 - 단순선형회귀(1) (0) | 2018.06.20 |
| 엑셀로 통계하기 20 - 모비율 비교와 독립성 검정 (0) | 2018.06.15 |