대량의 데이터를 다룰 때는 분석 과정이 복잡해지기 쉽습니다. 이럴 때 특정 기준으로 데이터를 그룹화하면 작업이 한결 수월해집니다. 특히 일정한 시간 간격으로 기록된 결과값을 확인할 수 있으면, 연속해서 발생하는 사건의 흐름을 파악하기가 훨씬 쉬워집니다. 이처럼 시간 축을 기준으로 기록된 데이터를 시계열 데이터(Time-Scaled Data)라고 부르며, 엑셀(MS Excel)은 이러한 데이터 분석에 가장 널리 활용되는 도구 중 하나입니다. 이번 글에서는 엑셀에서 시계열 데이터를 분석하는 방법을 단계별로 자세히 알아보겠습니다.
엑셀에서 시계열 데이터를 분석하는 단계별 방법
시계열 데이터는 주식 가격, 환율, 판매량, 생산량 등 실생활의 다양한 분야에서 유용하게 활용됩니다. 여기서는 회사의 연간 매출 데이터를 예시로 들어 설명하겠습니다. 데이터에는 연도(Years)와 해당 연도의 순매출(Net Sales)이 입력되어 있습니다. 아래 단계를 차근차근 따라 하며 엑셀에서 시계열 데이터를 분석하는 방법을 익혀 보세요.
1단계: 엑셀에 시계열 데이터 입력하기
- 먼저 엑셀 워크시트에 분석할 데이터를 정확하게 입력합니다.
- 아래 이미지를 참고하면 더 쉽게 이해할 수 있습니다.
2단계: 데이터 분석 버튼 추가하기
데이터 분석(Data Analysis) 버튼은 기본 설정에서는 데이터 탭에 표시되어 있지 않을 수 있습니다. 이 경우 다음 절차에 따라 버튼을 추가하세요.
- 먼저 파일 ➤ 옵션으로 이동합니다.
- 그러면 Excel 옵션 대화 상자가 나타납니다.
- 여기서 추가 기능 탭을 클릭합니다.
- 다음으로 관리 드롭다운 목록에서 Excel 추가 기능을 선택합니다.
- 이후 이동(Go) 버튼을 누릅니다.
- 추가 기능 대화 상자가 열리면 Analysis ToolPak 항목에 체크합니다.
- 확인을 누르면 데이터 탭 아래에 데이터 분석 버튼이 생성됩니다.
3단계: 통계 분석 실행하기
이 단계에서는 통계 분석을 수행하기 위한 세부 설정을 지정합니다. 다음 과정을 순서대로 살펴보세요.
- 먼저 데이터 ➤ 데이터 분석을 선택합니다.
- 데이터 분석 대화 상자가 나타나면 분석 도구 목록에서 지수 평활법(Exponential Smoothing)을 선택하고 확인을 누릅니다.
- 지수 평활법 대화 상자가 열리면 분석할 입력 범위(Input Range)를 지정합니다.
- 이 예시에서는 C5:C9 범위의 순매출 데이터를 선택합니다.
- 다음으로 감쇠 계수(Damping factor)를 설정합니다. 일반적으로 0~1 사이의 값을 가지며, 값이 작을수록 최근 데이터에 더 큰 가중치가 부여됩니다.
- 그리고 출력 범위(Output Range)를 지정합니다.
- 추가로 차트 출력과 표준 오차 항목에 체크합니다.
- 마지막으로 확인을 누릅니다.
분석 결과 확인하기
확인을 누르면 워크시트에 새로운 열 2개와 그래프가 자동으로 생성됩니다.
- 아래 이미지에서 평활 값(Smoothed Level)과 표준 오차(Standard Error) 열이 통계 분석의 결과입니다.
- 또한 실제 순매출과 예측값을 비교하는 그래프도 함께 얻을 수 있습니다.
엑셀에서 시계열 데이터 예측하는 방법
그래프에 추세선을 추가하면 미래 값을 예측할 수도 있습니다. 아래 단계를 따라 해 보세요.
실행 방법:
- 먼저 그래프에서 실제 순매출 곡선을 선택합니다.
- 마우스 오른쪽 버튼을 클릭하고 컨텍스트 메뉴에서 추세선 추가를 클릭합니다.
- 워크시트 우측에 추세선 서식 창이 나타납니다.
- 여기서 다항식(Polynomial) 옵션을 선택합니다. 다항식 추세선은 예측 모델에서 오차율이 더 낮은 편입니다.
- 이후 수식 표시와 R 제곱(R-squared) 값 표시 항목에 체크합니다.
- 그러면 그래프에 원하는 추세선이 표시됩니다.
- 만약 선형(Linear) 추세선을 선호한다면 선형 옵션을 선택하고 예측할 기간을 입력합니다.
- 그러면 실제 값과 함께 선형 추세선이 그래프에 나타납니다.
- 또한 GROWTH 함수를 사용하면 지수적으로 증가하는 데이터의 추이를 예측할 수 있습니다.
- 셀 F5를 선택하고 아래 수식을 입력합니다.
=GROWTH($C$5:$C$9,$B$5:$B$9,B5,TRUE)- Enter 키를 누르면 예측값이 계산됩니다.
- 이후 자동 채우기(AutoFill) 도구를 활용해 나머지 셀도 완성합니다.
- 마지막으로 매출액에 대한 전체 예측값이 반환됩니다.
마무리
지금까지 소개한 절차를 따라 하면 엑셀에서 시계열 데이터를 손쉽게 분석할 수 있습니다. 지수 평활법으로 추세를 파악하고, 추세선과 GROWTH 함수로 미래 값을 예측해 보세요. 더 좋은 활용 방법이 있다면 의견을 공유해 주시고, 궁금한 점이나 제안 사항이 있다면 언제든지 알려주세요.