엑셀 추세선 함수 - egsel chuseseon hamsu

밸런싱을 하다보면 처음부터 공식을 만들어야 하는 경우도 있고

테이블을 공식화 해야 하는 경우도 있고

리뉴얼 작업을 위해 기존 데이터를 공식으로 뽑아야 하는 경우도 있습니다.

물론 후자들의 경우 착실하게 주석을 달거나 하는 경우 별 문제가 없지만....

주석이 안달려 있다면 정말 이전 담당자를 찾아 심도깊은 대화를 나누고 싶어 집니다.

이럴때 쓸 수 있는 엑셀의 기능 중 하나가 바로 추세선 입니다.

 
추세선....  

음... 뭐 주식 하시는 분들은 아실거 같은 용어이지만

중요한건  "변동을 그린 직선 또는 곡선을 말한다." 이죠.

자... 그럼 일단 데이터를 살펴 봅시다.

가장 단순한 테이블인 레벨 - 경험치 테이블 입니다.

저 테이블만 보고 레벨당 경험치 공식을 뽑으라면 난감합니다.
[물론 수학적 감각이 뛰어나신분은 별거 아니겠지만 저같은 비루한 사람에겐 넘사벽이죠]

이 때 필요한게 추세선 입니다.

일단 해당 데이터를 그래프로 만들어 줍니다.

레벨에 따른 경험치 그래프가 나왔습니다.

이제 이 중 아무 데이터나 하나를 잡아 우클릭을 하여 추세선 추가를  실행합니다.

위와같은 창이 뜨는데 대충 다항식에 차수는 2차(혹은 3차)에다가

수식을 차트에 표시와 R-제곱 값을 차트에 표시를 체크 합니다.1

그럼....

이렇게 출력이 됩니다!

수식을 좀 크게 확대 하면...

y = x2 - 2E-13x + 100

이라는 아름다운 공식이 나옵니다.

그럼 저 공식이 맞는지 한번 확인해 보겠습니다.

음.... 깔끔하게 roundup으로 처리를 하면....

같은 값을 출력 하여 줍니다.

이렇게 추세선을 이용하면 간단한 테이블은 쉽게 공식화 처리 할 수 있습니다.

-=

원래 쓰려고 한 건 이게 아닌데 신나게 쓴걸 부산에 놓고 와서 어쩔 수 없이 
간단하면서 실용적인 기능을 소개 하였습니다.

이것도 의외로 모르시는 분들이 많더라고요.

다음에는 굉장히 실용적인.....

엑셀 초보분들을 위한, 엑셀 함수로 만드는 캐릭터 시뮬레이터! 를 쓰도록 하겠습니다.

경제성 분석의 편익 항목 추정이나 시계열 자료의 추세 분석에 엑셀 추세선을 이용합니다. 추세선은 어떻게 구한 걸까요? 회귀식일 거라고 예상되지만 계산해서 확인해 봅니다. 

추세선 추가하기

1) 분산형 차트 삽입

두 종류의 자료를 선택하여 그래프를 그립니다. 추세선을 추가하려면 분산형 차트를 선택해야 합니다. 

메뉴에서 삽입 > 차트 > 분산형을 선택해 차트를  그립니다.

2) 추세선 추가

차트를 선택하고 차트도구 > 차트 추가요소 > 추세선을 선택하거나, 차트 우측의 '+' 버튼을 눌러 추세선을 선택합니다. 

3) 추세선 서식 선택

추세선 서식을 선택하면 설정화면이 뜹니다. 

추세선 유형을 선택하면 해당 유형으로 바뀝니다. 예측은 추세선을 입력값 범위 바깥까지 그려줍니다. 앞으로 10을 설정하면 오른쪽으로 10만큼까지 추세선을 더 그립니다. '수식을 차트에 표시'와 'R-제곱 값을 차트에 표시'를 선택하면 추세선의 식을 차트와 함께 확인할 수 있습니다. 

회귀식과 비교

그럼 추세선과 회귀식을 비교해 보겠습니다. x, y, ln(x)로 대략 선형이 나오는 자료를 만들었습니다.  

추세선을 포함한 x, y 분산 차트와 엑셀에서 회귀분석을 수행한 결과입니다. x  계수 2.444, 절편 -0.2022, 결정계수 0.9752로 같은 결과가 나옵니다. 

추가로 R을 이용해 같은 자료를 회귀분석했습니다. 계수와 절편, 결정계수 모두 같습니다. 

같은 방식으로 로그식도 비교해봅니다. 

분산차트의 추세선과 엑셀을 이용한 회귀분석,  R을 이용한 회귀분석 모두 같은 결과가 나옵니다. 직선, 로그, 지수, 2~3차 함수 회귀분석은 엑셀에서 추세선 추가를 이용해서 쉽게 하면 되겠습니다.