Computer >> 컴퓨터 >  >> 소프트웨어 >> Office

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

데이터를 특정 기간이나 일정한 간격으로 정리해야 하는 경우가 종종 있습니다. 시계열(Time Series)은 일정한 빈도 범위에 따라 배열된 데이터 항목의 집합을 의미하며, 이러한 시계열 데이터를 살펴보고 평가하는 기법을 시계열 분석이라고 합니다. Microsoft Excel은 다양한 데이터 분석 업무를 수행할 수 있는 가장 유용한 도구 중 하나로, 이 글에서는 엑셀에서 시계열 데이터를 분석하는 방법을 단계별로 자세히 소개합니다.

아래 설명된 예제 파일을 내려받아 직접 연습해 보실 수도 있습니다.

시계열이란 무엇일까요?

시계열(Time Series)은 시간의 흐름에 따라 샘플을 기록한 데이터 집합입니다. 예를 들어, 다양한 변수가 여러 상황 속에서 시간에 따라 어떻게 변화하는지 확인할 수 있으며, 특정 자산·상품·재무 지표의 변화 추이를 파악하는 데 활용됩니다.

시계열 분석(Time Series Analysis)은 데이터 항목들을 시간 순서대로 분류하여 다루는 방법으로, 일정 기간에 걸쳐 데이터를 수집하고 그 데이터 포인트들을 통해 변화를 모니터링합니다. 흔히 동일한 출처에서 반복적으로 측정한 값을 포함하며, 특정 간격으로 프로세스 데이터를 기록하면 시계열의 구성 요소를 얻을 수 있습니다.

엑셀에서 시계열을 분석하는 단계별 방법

시계열 분석을 통해 시간의 흐름에 따른 데이터 특성을 살펴볼 수 있습니다. 시계열은 매일, 매년 등 일정한 시간 간격으로 수집되어 시간순으로 정렬된 데이터 포인트들의 집합입니다. 지금부터 엑셀에서 시계열 데이터를 분석하는 절차를 하나씩 따라가 보겠습니다.

1단계: 시계열 데이터 입력하기

시계열 분석을 설명하기 위해 특정 회사의 2개 연도 분기별 매출 데이터를 사용하겠습니다. 먼저 시계열 데이터를 올바르게 입력해야 합니다.

  • 먼저 B열에 연도 데이터를 입력합니다. 이 예제에서는 두 개의 연도만 사용합니다.
  • 다음으로 각 연도의 분기를 입력합니다.
  • 마지막으로 분기별 총 매출액을 입력합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

2단계: 데이터 분석 기능 활성화하기

엑셀의 데이터 분석(Data Analysis) 기능은 데이터를 더 깊이 이해할 수 있게 해주며, 우수한 시각적 인사이트와 통계 정보, 구조화된 결과를 제공합니다. 하지만 이 기능은 기본적으로 비활성화되어 있으므로, 아래 하위 단계에 따라 활성화해야 합니다.

  • 먼저 리본 메뉴에서 파일(File) 탭으로 이동합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 그러면 엑셀 백스테이지 화면으로 전환됩니다. 왼쪽 하단의 옵션(Options) 메뉴를 클릭합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • Excel 옵션 대화상자가 나타나면 추가 기능(Add-ins)으로 이동하고, 추가 기능 목록에서 분석 도구(Analysis ToolPak)를 선택합니다.
  • 이어서 하단의 관리(Manage) 드롭다운 메뉴에서 Excel 추가 기능을 선택한 뒤 이동(Go) 버튼을 클릭합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 추가 기능 창이 열리면 Analysis ToolPak 항목에 체크 표시를 합니다.
  • 마지막으로 확인(OK) 버튼을 누르면 설정이 완료되고, 데이터(Data) 탭에 데이터 분석 버튼이 나타납니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

3단계: 통계 분석 실행하기

통계 분석은 데이터 간의 관계와 상관관계를 찾기 위해 데이터를 수집하고 평가하는 과정으로, 데이터 분석 기능의 한 부분입니다. 이제 통계 분석을 수행하는 절차를 따라 해 보겠습니다.

  • 리본 메뉴에서 데이터(Data) 탭으로 이동합니다.
  • 그런 다음 분석(Analysis) 그룹의 데이터 분석 도구를 클릭합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 데이터 분석 대화상자가 나타나면 목록을 조금 아래로 스크롤하여 지수 평활법(Exponential Smoothing)을 선택합니다.
  • 확인(OK) 버튼을 클릭합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 지수 평활법 대화상자가 표시되면 입력 범위(Input Range) 필드에 셀 범위를 선택합니다. 이 예제에서는 매출 열에 해당하는 $D$5:$D$12 범위를 선택합니다.
  • 필요에 맞게 감쇠 계수(Damping factor)를 지정합니다.
  • 출력 범위(Output Range) 필드에는 $E$5 셀을 지정합니다.
  • 차트 출력(Chart Output)과 표준 오류(Standard Errors) 항목에 체크 표시합니다.
  • 마지막으로 확인(OK)을 클릭하면 절차가 완료됩니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

시계열 분석 최종 결과 확인하기

지수 평활법 대화상자에서 확인 버튼을 클릭하면 워크시트에 새로운 두 개의 열과 그래프 차트가 생성됩니다.

  • 평활 값(Smoothed Level) 열과 표준 오류(Standard Error) 열은 통계 분석의 결과물입니다.
  • 평활 값 열을 자세히 보면 아래와 같은 수식이 적용되어 있는 것을 확인할 수 있습니다.
=0.7*D6+0.3*E6
  • 또한 표준 오류 열에는 SQRT 함수와 SUMXMY2 함수가 결합된 다음 수식이 사용됩니다.
=SQRT(SUMXMY2(D6:D8,E6:E8)/3)

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 아울러 매출 실적과 예측값을 비교하는 그래프도 함께 생성됩니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

엑셀에서 시계열 예측하기

엑셀은 반복적인 시간적 관계와 상관관계를 파악하는 데 자주 활용됩니다. 매출, 서버 사용량, 재고 데이터처럼 시간에 따른 데이터를 분석할 때 유용하죠. 예측 시트를 만들기 전에 먼저 시계열 데이터가 빠짐없이 완전한지 확인해야 합니다. 차트는 시간의 흐름에 따른 시계열을 시각화하며, 시계열 분석에는 수준(level), 추세(trend), 계절성(seasonality)이라는 세 가지 요소가 포함될 수 있습니다.

진행 단계:

  • 먼저 실제 매출 곡선 선을 선택합니다.
  • 마우스 오른쪽 버튼을 클릭하면 바로 가기 메뉴가 열립니다.
  • 여기서 추세선 추가(Add Trendline)를 선택합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 그러면 스프레드시트 오른쪽에 추세선 서식(Format Trendline) 창이 나타납니다.
  • 추세선 옵션에서 다항식(Polynomial) 옵션을 선택합니다.
  • 이후 차트에 수식 표시와 차트에 R-제곱 값 표시 항목에 체크 표시합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 아래 그림을 참고하면 이해에 도움이 됩니다. 예측 모델에서는 다항식 추세선이 오류율이 낮습니다.
  • 결과적으로 그래프에 원하는 추세선이 표시됩니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 반대로 선형 추세선을 원한다면 선형(Linear)을 선택하세요.
  • 그다음 추세 예측(Forecast) 옵션 아래에서 예측 기간을 지정합니다. 이 예제에서는 앞(Forward) 기간을 설정했습니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 그러면 그래프의 실제 데이터 옆에 선형 추세선이 함께 표시됩니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 지수 관계를 예측하고 싶다면 GROWTH 함수를 사용할 수 있습니다. GROWTH 함수는 기존 x값과 y값을 활용하여 지정한 새로운 x값에 대한 y값을 반환하며, 기존 x값과 y값에 지수 곡선을 피팅하는 데에도 사용할 수 있습니다.
  • 따라서 Forecast라는 이름의 새 열을 삽입합니다.
  • GROWTH 함수를 사용한 예측 결과가 표시될 셀을 선택합니다.
  • 선택한 셀에 아래 수식을 입력합니다.
=GROWTH($D$5:$D$12,$C$5:$C$12,C5,TRUE)
  • 마지막으로 Enter 키를 눌러 계산을 완료합니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 이제 채우기 핸들(Fill Handle)을 아래로 끌어 수식을 범위 전체에 복사합니다. 또는 더하기(+) 기호를 더블클릭하여 자동 채우기(AutoFill)할 수도 있습니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

  • 완료되면 매출에 대한 예측 결과를 확인할 수 있습니다.

엑셀에서 시계열 데이터 분석하는 방법 – 초보자도 따라 하는 단계별 가이드

마치며

지금까지 소개한 절차를 따라 하면 엑셀에서 시계열 데이터를 손쉽게 분석할 수 있습니다. 이 글이 도움이 되었기를 바랍니다! 궁금한 점, 제안 사항 또는 피드백이 있다면 댓글로 알려주세요. 블로그의 다른 엑셀 관련 글들도 함께 확인해 보세요!

함께 읽으면 좋은 글

  • 엑셀에서 정성 데이터를 정량 데이터로 변환하는 방법
  • 엑셀에서 설문조사 정성 데이터를 분석하는 방법
  • 엑셀에서 정성 데이터를 쉽게 분석하는 방법
  • 엑셀에서 qPCR 데이터를 분석하는 2가지 방법
  • 엑셀 데이터 분석으로 케이스 스터디 수행하기