엑셀에서도 선형회귀를 활용하여 예측을 할 수 있을까? FORECAST.LINEAR 함수 알아보기

엑셀은 간단한 통계 기능만 가능할 것 같은데..

혹시 선형회귀 같은 분석 기법을 활용하여 모델을 만들 수도 있을까?

놀랍지만 엑셀에서도 이런 기능이 있다.

엑셀에서도 선형회귀를 활용하여 예상 매출액을 추정한다던지, 예상 판매량을 예측한다던지 분석 모델을 돌릴 수 있다.

바로 FORECAST.LINEAR 함수를 활용해서말이다!

 

[목차여기]

 

엑셀 FORECAST.LINEAR 함수 

엑셀 FORECAST.LINEAR 함수는 선형회귀를 사용하여 기존의 데이터 범위를 참조함으로써 특정 값을 추정하고 예측하는 함수이다.

엑셀 2016 이전 버전에서는 그냥 FORECAST 함수였는데,

이후 버전에서는 FORECAST.LINEAR 함수로 바뀌었다.

참고로 시계열 변화에 따른 미래의 값을 예측하려면 FORECAST.ETS 함수를 사용하면 된다.

엑셀 최신 버전에서도 그대로 FORECAST 함수를 써도 무방하다.

결과는 FORECAST.LINEAR 함수와 동일하게 나온다.

 

선형회귀란?        

그럼 선형회귀는 무엇일까?

영어로는 Linear Regression 이라고 하는 통계학 분석기법이다.

종속 변수 y와 한 개 이상의 독립 변수 x와의 선형 상관관계를 모델링하는 회귀분석 기법이다.

 

수학적으로 접근하면 어려우니 쉽게 설명한다면,

아래 그림과 같이 여러 값들로부터 추세를 설명할 수 있는 1차식을 만드는 것이다.

빨간색 점이 여러 값들이고 이 하나 하나의 값들을 가지고 x와 y의 1차식, 즉 파란색 선을 만드는 것이다. 

선형회귀

 

이렇게 파란색 선, 선형회귀 모델을 만들면 새로운 x 값을 넣어 y 값을 예측해볼 수 있는 것이다.

예측이라는 것이 굉장히 여러 수학적 기법들을 활용하여 추정할 수 있는 것인데,

대표적인 방법 중 하나라고 보면 된다.

 

엑셀 FORECAST 함수 사용법  

그럼 FORECAST.LINEAR 함수 사용은 어떻게 하면 될까?

처음 사용할 때는 생각보다 어렵고 헷갈릴 수 있으니 잘 확인해보자

 

=FORECAST(예측할값, 결과값 범위, 예측값 범위)
=FORECAST(x, known_y's, known_x's)

 

• 예측할값 : 선형회귀 설명에서 새로운 x에 해당한다. 값을 직접 입력할 수도 있고 셀을 지정할 수도 있다.
• 결과값 범위 : 기존 값들 중 x에 따른 y의 값들을 말한다. 결과값이 입력된 기존 범위이다.
• 예측값 범위 : 기존 값들의 x에 해당한다. 기존 범위를 말한다.  

 

설명만 들어서는 다른 엑셀 함수와 달리 어떻게 써먹어야 할 지 도통 감이 안올 것 같다.

쉬운 예제를 통해 어떻게 쓰면 되는지 알아보자!

 

사용 예제

FORECAST 함수를 활용하는 대표적인 예제가 매출액을 예측하거나 판매량을 예측하는 것이다.

이해하기 쉽도록 커피 여러 메뉴들의 1~6월 판매량을 기반으로 7월 판매량을 FORECAST.LINEAR 함수로 예측해보도록 하겠다.

FORECAST.LINEAR 함수 예제

 

B~G 컬럼이 각각 1~6월까지의 커피 메뉴별 판매량 실적이다.

여기서 필드명을 왜 1월, 2월, 3월 이런식으로 안쓰고 1, 2, 3 이렇게 숫자로만 썼는지가 매우 중요하다.

FORECAST 함수의 예측할 값은 반드시 숫자여야만 한다.

1월, 2월, 3월 이렇게 문자가 들어갈 경우 #VALUE! 오류가 반환되기 때문에 1, 2, 3 이렇게 숫자로만 표현했다.

오류값에 대해서는 아래 따로 모아서 설명해보겠다.

 

아무튼 1~6월 판매량 실적 데이터를 바탕으로 7월의 판매량을 예측해보려고 한다.

예측할 값은 새로운 x라고 설명을 했는데 1~6월, 월에 따른 판매량 값이 있는 것이니

1~6월이 독립 변수, 판매량이 종속 변수 또는 설명 변수가 된다.

단순히 쉽게 이해해보자면 내가 7월의 판매량을 예측해보려고 하는 것이니 예측할 값은 7월이 되는 것!

FORECAST 함수로 하나 FORECAST.LINEAR 함수로 하나 결과는 동일하지만,

최신 버전인 FORECAST.LINEAR로 표현을 해보겠다.

 

=FORECAST.LINEAR(I2

 

7월을 그냥 "7" 이라고 써줘도 무방하지만 7월을 나타내는 셀인 I2 셀을 참조해주었다.

그 다음은 결과값 범위이다.

기존에 1~6월 동안의 판매량이 결과값이 될 것이다.

영어 표현으로 known_y's 라고 표현하는 것이 결국 종속 변수인 기존 y값들을 의미한다는 것이다.

 

=FORECAST.LINEAR(I2, B3:G3 

 

아메리카노의 1~6월 판매량 결과값이 있는 B3:G3를 참조해주었다.

그 다음은 예측값 범위이다.

우리는 7월을 예측하고 싶은 것이니 기존에 1~6월인 필드값을 참조해주는 것이다.

역시 known_x's 라고 표현하는 것이 독립 변수 x값들을 의미하는 것이다.

 

=FORECAST.LINEAR(I2, B3:G3, B2:G2)

 

이렇게 함수를 작성해주면 1~6월 판매량 실적을 기반으로 7월의 예상 판매량을 예측해준다.

아메리카노를 보면 1월에서 6월로 가는 동안 조금씩 판매량이 줄어드는 것을 볼 수 있는데,

이 값들을 통해 선형회귀 모델은 우하향 선으로 그려졌을 것이고,

그렇기에 7월은 직전 6월보다 조금 더 판매량이 줄어든 38 이 반환된 것이다.

 

아래 다른 메뉴들까지 모두 수식 붙여넣기를 하기 위해서는

예측할 값인 7월과 예측값 범위인 1~6월은 고정되어야 하므로 두 값들에는 단축기 F4를 통해 절대값을 씌워주었다.

 

오류 값 이유

정확하게 이해하지 못하고 FORECAST 함수를 사용한다면 이유를 모르는 오류들이 많이 발생할 수 있을 것이다.

위에서 하나는 이미 설명했었지만 다시 한번 오류 값 이유들을 정리해보겠다.

 

1. 예측할 값은 반드시 숫자로 입력해야 하며 숫자가 아닌 문자가 들어갈 경우 #VALUE! 오류가 반환된다.
2. 결과값범위, 예측값범위가 비어있는 경우 #N/A 오류가 반환된다.
3. 결과값범위의 개수와 예측값범위의 개수는 동일해야 한다.
   선형회귀에서 x값과 y값이 다르다는 것은 어떤 한 값은 비어있는 경우가 생긴다는 의미이다.
4. 예측값범위의 분산이 0이면 #DIV/0! 오류가 반환된다.
   다시 말해 예측값범위의 값들이 모두 같으면 안된다.    

 

오늘은 조금 어려울 수 있는 FORECAST.LINEAR 함수를 알아보았다.

Linear Regression 이라고 하는 선형회귀 분석기법을 활용하여 예측을 할 때 사용하는 함수이다.

꼭 파이썬 같은 개발툴로만 이런 분석모델을 만들 수 있는 것은 아니다.

엑셀에서도 생각보다 많은 함수와 기능들이 제공되기 때문에 잘 활용해보도록 하자!