학창 시절 누구나 평균의 개념과 계산 방법을 배웠습니다. 평균은 데이터의 모든 값을 더한 뒤 값의 개수로 나누는 아주 간단한 공식으로 구할 수 있습니다. 이동 평균(Moving Average) 역시 여러 종류의 평균 중 하나로, 주식 시장, 매출 분석 등 실생활의 다양한 분야에서 폭넓게 활용되고 있습니다.
이동 평균은 크게 단순 이동 평균(SMA), 가중 이동 평균(WMA), 지수 이동 평균(EMA) 세 가지 유형으로 나뉩니다. 이 글에서는 엑셀(Excel)에서 세 가지 이동 평균을 각각 계산하는 방법을 단계별로 자세히 설명합니다.
엑셀에서 이동 평균 계산하기
엑셀에서 이동 평균을 계산하는 방법은 두 가지입니다. 하나는 엑셀에 내장된 기능인 데이터 분석 도구를 사용하는 방법이고, 다른 하나는 수식을 직접 입력하는 방법입니다.
1. 데이터 분석 도구(Data Analysis ToolPak) 사용하기
먼저 엑셀의 데이터 분석 도구팩을 활용해 단순 이동 평균을 계산하는 방법을 알아보겠습니다. 데이터 분석 도구팩은 기본 추가 기능(Add-in)으로 제공되기 때문에, 처음에는 메뉴에 나타나지 않을 수 있습니다. 아래 순서대로 따라 하면 도구를 활성화할 수 있습니다.
1] 상단 메뉴에서 파일(File)을 클릭합니다.
2] 목록에서 옵션(Options)을 선택합니다.
3] 옵션 대화상자가 열리면 좌측 메뉴에서 추가 기능(Add-Ins)을 클릭합니다.
4] 대화상자 하단의 드롭다운 메뉴에서 Excel 추가 기능(Excel Add-ins)을 선택하고 이동(Go) 버튼을 클릭합니다.
5] 분석 도구팩(Analysis ToolPak)에 체크한 뒤 확인(OK)을 누릅니다.
6] 위 과정을 마치면 데이터 분석 도구가 설치되며, 리본 메뉴의 데이터(Data) 탭에서 확인할 수 있습니다.
도구 준비가 끝났다면 이제 단순 이동 평균을 계산할 차례입니다. 설명을 위해 한 달 중 처음 10일간의 온도 변화를 담은 샘플 데이터를 만들어 사용하겠습니다.
아래 단계에 따라 데이터 분석 도구팩으로 단순 이동 평균을 계산해 보세요.
1] 데이터(Data) 탭을 클릭하고 데이터 분석(Data Analysis) 옵션을 선택합니다.
2] 작은 대화상자가 열리면 목록에서 이동 평균(Moving Average)을 선택하고 확인을 누릅니다. 이 기능으로 계산되는 것은 단순 이동 평균입니다.
3] 입력 범위(Input Range)에 이동 평균을 계산할 데이터 범위를 입력합니다. 샘플 데이터는 B2부터 B11까지 있으므로 B2:B11을 입력했습니다. 그다음 구간(Interval) 값을 지정하고, 출력 범위(Output Range)에는 결과가 표시될 셀 주소를 입력합니다. 여기서는 E2를 선택했습니다. 모두 입력했으면 확인을 클릭합니다.
4] 결과가 표시됩니다. 아래 스크린샷처럼 첫 세 행에 #N/A 오류가 나타나는데, 이는 구간을 4로 설정했기 때문입니다. 즉, 4일 단순 이동 평균(4일 SMA)을 계산한 것이므로 네 번째 값부터 결과가 채워집니다.
첫 번째 방법은 엑셀 도구를 활용하는 방식이라 편리하지만, 단순 이동 평균만 계산할 수 있다는 한계가 있습니다. 두 번째 방법인 수식 활용법을 익히면 세 가지 이동 평균을 모두 직접 계산할 수 있습니다.
2. 수식으로 단순 이동 평균(SMA) 계산하기
여기서도 동일한 샘플 데이터를 사용합니다.
1] 4일 SMA를 계산하려면 4일차에 해당하는 5행 셀에 아래 수식을 입력하고 Enter 키를 누릅니다.
=AVERAGE(B2:B5)
2] 이제 커서를 해당 셀의 오른쪽 아래 모서리에 올려 놓습니다. 커서가 더하기(+) 아이콘으로 바뀌면 마우스 왼쪽 버튼을 누른 상태로 E11 셀까지 드래그합니다. 그러면 수식이 나머지 셀에 자동으로 복사됩니다.
3] 모든 셀에 단순 이동 평균 값이 계산되어 표시됩니다.
3. 수식으로 가중 이동 평균(WMA) 계산하기
다음은 가중 이동 평균입니다. 같은 샘플 데이터를 사용하며, WMA를 계산하려면 각 값에 부여할 가중치가 필요합니다. 가중 이동 평균의 기본 공식은 다음과 같습니다.
WMA = [(최신 값 × 가중치) + (이전 값 × 가중치) + ...] / (전체 가중치의 합)
여기서는 최신 값에 70%, 바로 앞 값에 20%, 그 앞 값에 10%의 가중치를 부여하는 3점 WMA를 계산해 보겠습니다. 이 조건에 맞춰 E4 셀에 아래 수식을 입력합니다.
=(0.7*B4+0.2*B3+0.1*B2)/(0.7+0.2+0.1)
3점 WMA를 계산하는 것이므로 처음 두 셀은 비어 있게 됩니다. 이제 SMA 계산 때와 마찬가지로 셀을 마지막 행까지 드래그해 수식을 복사하면 전체 결과를 얻을 수 있습니다.
4. 수식으로 지수 이동 평균(EMA) 계산하기
EMA를 계산하려면 먼저 초기 EMA 값이 필요합니다. 초기값은 SMA를 계산해 구하며, 추가로 가중치 승수 또는 평활 상수(K)가 필요합니다. 지수 이동 평균의 기본 공식은 다음과 같습니다.
EMA = 최신 값(오늘의 값) × K + 어제의 EMA 값 × (1 - K)
1] 앞서 SMA를 계산했던 표를 그대로 사용합니다. 이 예제에서는 K = 0.3으로 설정했으며, 첫 번째 EMA 값은 첫 번째 SMA 값과 같습니다. 여기서는 4일 SMA와 EMA 값을 함께 계산합니다.
2] F6 셀에 아래 수식을 입력합니다.
=B6*$G$1+F5*(1-$G$1)
주의할 점은, 위 수식에서 G열 셀(G1)을 절대 참조($)로 잠갔다는 것입니다. 모든 EMA 계산에서 동일한 K 값을 사용해야 하기 때문입니다.
3] 이제 해당 셀을 드래그해 나머지 셀까지 수식을 복사합니다.
이것으로 엑셀에서 세 가지 유형의 이동 평균을 모두 계산하는 방법을 살펴봤습니다. 데이터 분석 도구를 이용하면 빠르고 간편하게, 수식을 이용하면 가중치와 평활 상수를 자유롭게 조절하며 정교하게 계산할 수 있으니, 용도에 맞게 활용해 보시기 바랍니다.