엑셀 LINEST 함수를 이용하여 추이 분석하기

 

안녕하세요 이번 시간에는 통계 분석을 할 때 유용하게 사용되는 LINEST 함수에 대해 알아보겠습니다. LINEST 함수는 통계 작업이나 시장 추이 분석을 할 때 유용하게 사용할 수 있습니다.

 

LINEST 함수 실습해보기

아래 시트와 같이 판매량이 일정하지 않을 때 나머지 비어있는 기간 동안의 판매량을 예측해보는 실습을 해 보겠습니다. 2021년 1월 1일부터 1월 14일까지 판매량이 일정하게 증가하지 않는 것을 알 수 있습니다. 그래서 2021년 1월 15일부터 1월 20일까지의 판매량을 예측해보겠습니다. 이런 상황에서 사용할 수 있는 함수가 선형 추이 분석을 해주는 LINEST 함수입니다.

 

 

LINEST 함수 수식 이해하기

수식: =LINEST (Y 값, X 값, A)

여기서 A는 TRUE(참) 또는 FALSE(거짓)을 나타내는 논리값입니다. 추이 분석을 한다면 TRUE를 추천드립니다.

아래 그림을 기준으로 각 항목에 대해 설명드리면,
① Y 값 (Known_y’s): 상기 예제에서 판매량에 해당합니다.
② X 값 (Known_x’s): 상기 예제에서 날짜에 해당합니다.
③ Const: 상기 수식에서 “A”에 해당하는 부분이며 가능하면 TRUE를 입력합니다.

 

 

상기 수식을 이용하여 추이 분석을 실행해보겠습니다. 먼저 아래 그림과 같이 셀 안에 “1일 변화 량”이라고 입력하신 후 LINEST 함수를 적용해보겠습니다. 선택 셀에 “=LINEST(“라고 입력합니다.

 

 

첫 번째 값으로 상기 예제의 표에서 판매량에 해당하는 범위를 선택합니다. 상기 그림에서는 D6 셀부터 D19 셀까지이므로 D6:D19가 되겠습니다. 이 값이 수식에서 설명한 Y 값에 해당하는 부분입니다.

 

 

이어서 X 값에 날짜에 해당하는 C6 셀부터 C19 셀까지 범위를 선택합니다. 여기서 주의할 점은 C25 셀까지 범위를 선택하면 오류가 발생합니다. 즉 Y 값에 선택한 범위와 대응하는 개수가 같아야 합니다. 그리고 X 값과 Y 값을 구분하기 위해서는 쉼표를 넣어줍니다. 즉 D6:D19과 C6:C19 사이에 쉼표를 넣어 구분합니다. 그러면 “=LINEST(D6:D19, C6:C19, “가 되겠습니다.

 

 

끝으로 TRUE만 입력하고 괄호를 닫으면 수식이 완성됩니다. 최종 완성된 수식은 “=LINEST(D6:D19, C6:C19, TRUE”입니다. 여기서 D6:D19은 판매량이고 C6:C19는 날짜에 해당되며 이 결과 값이 1일 변화량이 됩니다.

 

 

산출한 값을 이용하여 추이 분석을 해보겠습니다. 아래 그림에서와 같이 2021년 1월 15일에 해당하는 셀에 “=D19+$F$6”이라고 입력합니다. 여기서 $F$6이라고 표시한 것은 값을 고정시켜 자동으로 복사하여 붙여 넣기를 해주기 위함입니다. 그리고 “=D19+$F$6”는 D19 셀의 값과 F6 셀의 값을 합산하라는 의미입니다. 즉, 선형 추이로 예측된 1일 변화량과 1월 14일 판매량의 합산 값이 1월 15일의 예측 판매량이 됩니다. 이렇게 F6 셀의 고정 값을 계속 합산해가면 대략적으로 예측이 가능합니다.

 

 

1월 15일에 구한 값을 복사한 후 나머지 비어 있는 셀에 붙여 넣기를 하면 자동으로 값이 산출 됩니다. 먼저 D20 셀을 복사한 후 나머지 비어 있는 셀들을 모두 선택하고 오른쪽 마우스를 클릭 합니다. 붙여넣기 옵션들이 나타나는데 “fx” 아이콘을 클릭하면 수식만 복사됩니다. 그러면 자동으로 식이 완성되고 값들이 입력되는 것을 볼 수 있습니다.

 

 

아래 그림에서 “자릿수 줄임” 아이콘을 클릭해주면서 자릿수를 맞추어 주면 좀 더 보기가 편하게 나타낼 수 있습니다.

 

 

지금까지 LINEST 함수에 대해 알아보았습니다. 시장분석이나 통계 업무를 하시는 분들은 도움이 될 것 같다는 생각이 듭니다. 다음 시간에도 좋은 정보를 갖고 찾아오겠습니다.