엑셀 추세선 다항식 함수 - egsel chuseseon dahangsig hamsu

엑셀을 이용하면서 추세선의 식을 이용할 경우 그래프의 추세선 에서 복사, 붙여넣기를 하지 않고

함수를 이용하여 바로 셀에 y = ax+b의 식의 a, b값을 가져오는 방법입니다.

먼저 배열함수인 LINEST 함수를 이용하여 다음과 같이 값을 얻을 수 있습니다.

LINEST함수에 대한 MS의 설명은 링크를 참조하시면 됩니다.

(간단한 설명은 아래에도 있습니다)

H열과 I열의 1, 2, 3, 4 및 1.1, 1.9, 2.9, 4.2값은 임의로 정한 y, x값입니다.

LINEST함수는 LINEST([known_y's], [known_x's], [const], [stats])구문이며 따라서 [known_y's]값에는 H1:H4가, 

[known_x's]에는 I1:14가 들어 갔으며, 

[const]는 b를 0으로 설정 할지 결정합니다. TRUE이거나 생략되면 계산하며  FALSE일 경우 0으로 설정됩니다.

[stats]는 a와 b값 이외에 다른 추가적인 항목을 구할지 정합니다. R^2 값을 얻으려면 TRUE (혹은 1) 로 해야 합니다.

스크린 샷에 보이는 것 처럼 출력 하려면 0.959478342 라고 적힌 I5부터 0.058687이라고 적힌 J9까지 

블럭을 씌운 다음 위의 수식 입력줄에 =LINEST(H1:H4,I1:I4,,1)을 입력하고 Ctrl + Shift + Enter를 누르면 됩니다. 

하지만 이렇게 함수를 이용할 경우 한 세트의 y값에 여러 세트의 x 값이 있을 경우 불편합니다.

이를 SUM함수를 이용하여 해결 할 수 있습니다.

먼저 a, b 값만을 얻을 경우 [stats]를 0으로 두고 셀에

=SUM(LINEST(C1:C4,D1:D4,,0)*{1,0})을 입력할 경우 a값

=SUM(LINEST(C1:C4,D1:D4,,0)*{0,1})을 입력할 경우 b값을 출력합니다.

이렇게 사용하면 배열 함수로 적용 되지 않으며

R^2을 구하기 위해 [stats]를 1로 설정한다면 배열 함수의 행이 늘어나기 때문에

위 스크린 샷 처럼 =SUM(LINEST(C1:C4,D1:D4,,1)*{0,0;0,0;1,0;0,0;0,0}) 이라고 입력해 주어야 합니다.

또한 R^2값이 1이 되는경우 (위 스크린 샷에서 E열) #NUM!오류가 발생 하는것으로 보입니다.

x

엑셀에 추세선 넣기를 설명하기 위해서 강남스타일 유튜브 조회수를 가지고 예제를 구성 하였습니다. 

강남스타일이 7월 중순에 유튜브에 업로드 된 이후로 8월, 9월 아래와 같은 조회수가 증가 하였습니다. 

8월 2일 천만번(0.1억번)의 조회수 였지만, 기하급수적으로 늘어서 9월 28일에는 3억회를 달성하였습니다. (놀랍습니다.)

그럼 언제 5억회를 돌파할까요? 이 질문에 대한 대략의 추세를 확인하기 위해서 추세선을 그을 수 있습니다.

예제는 excel2010으로 보여 드리지만, 경험상 excel2007 이하 버전도 비슷합니다.

강남스타일의 조회수 자료를 입력하고 전체를 선택한 후에, 메뉴의 삽입 의 분산형 챠트를 선택합니다.

 그럼 X 축은 날짜로 Y축은 조회수가 나오게 됩니다. (챠트 내 항목항은 지웠습니다.)

조회수 점들에서 마우스 오른쪽 버튼을 누르면 추세선 추가라는 항목이 보입니다. 클릭합니다.

추세선에 대한 다양한 옵션이 보입니다. 저는 다항식 중 2차 함수를 선택하고, 앞으로 30일 이후에 조회수가 어떻게 될 것인지 궁금하여 [앞으로 30구간]을 선택 했습니다. 챠트에 수식도 표현하도록 체크하였구요.

이제 2차 함수의 곡선을 갖는 추세선이 추가 되었습니다. 대충 이 그래프대로 라면 10월 중순쯤 5억회를 넘겠군요.

(보기 좋게 하려고 선색이나 숫자모양등을 좀 바꿔 봤습니다. ^^) 

※ 참고 : 엑셀의 추세선은 가우스의 최소자승법(최소제곱법)이용한다고 합니다. 다음에 기회가 되면 이 얘기도 하도록 하죠. 

엑셀 중급_차트 – 추세선 수식, 서식, R값 표시, 값 표시

우리가 실생활에서 일어나는 모든 일들은 수학적 모델로 근사화 될 수 있습니다. 예를 들어 해마다 증가하는 판매량은 일정하지는 않지만 직선을 그려서 근사화 모델을 만들 수 있습니다. 어떤 근사화 모델을 만들지는 수학적 모델링을 하는 사람의 판단이고 그 수학적 모델링을 한 값을 엑셀 차트에 표시해 주는 선을 추세선 이라고 합니다. 엑셀에서 축 서식 다음으로 제이킴이 수동 서식을 가장 많이 적용하는 요소가 이 추세선 입니다. 때에 따라서 내가 어떤 모델링을 했는지 강조할 필요가 있습니다. 이번 포스팅에서 추세선의 모든 것을 파헤쳐 드리겠습니다.

1. 추세선 추가

   엑셀의 추세선은 특이하게 마우스 우클릭으로 바로 추가 할 수도 있고 기존의 여러 방법으로도 추가 할 수 있습니다.

   추세선을 추가하고 싶은 아무 계열에나 가서 우클릭으로 추세선 추가를 누릅니다.

   혹은 + 버튼이나 리본을 이용하여 역시 추세선을 추가 할 수 있습니다.

   

   추세선을 더블 클릭하면 추세선의 서식을 자세히 볼 수 있으며 기본 옵션은 역시 선형입니다.

   지수 형태로 바꾸고 좀더 구분을 쉽게 하기 위해 색깔과 점선을 실선으로 바꾸었습니다.

2. 추세선 서식

   추세선을 그리는 모델은 매우 다양 합니다. 선형 분석, 최소 제곱 법, 지수, 로그, 다항식 등 모두 수학적 모델링과 관련된 용어들이며 통계학을 모르시는 분들은 지수 형과 선형만 사용하셔도 무난 합니다. 지수 형은 무리수인 e의 지수로 수학적 모델링을 하는 것을 말합니다.

   이동 평균 옵션은 데이터가 변하는 곳 마다 따라가면서 새로운 수학적 수식을 적용합니다. 주로 주식 시장 분석에 사용되는 옵션입니다.

   다항식 옵션은 중학교 때 배운 다항식을 이용해 수학적 모델링을 하는 과정입니다. 제곱은 하나의 극대와 극소를 가지고 세제곱은 두개, 네 제곱은 3개 이런 식으로 정확도가 점점 올라가지만 엑셀의 계산을 힘들게 해서 시트가 느려 질 수 있습니다.

   R값은 추세선의 정확도를 나타내는 지표로 1에 가까울수록 근사치와 실제 값의 차이가 적습니다. 모르시면 걍 넘어가도 됩니다. 수학 용어 입니다.

   역시 3번째 아이콘에 있는 (저기 막대 그래프 표시가 주로 데이터와 연관됩니다. ) 아이콘을 클릭하면 수식을 차트에 표시하는 옵션을 볼 수 있습니다. 지수 형은 제가 설명한 대로 e로 지수를 표기 합니다.

3. 추세선의 데이터 값을 셀에 표기 하기

   추세선의 수식은 보통 다항식의 경우는 매우 복잡한 경우가 많고 그 수식을 셀에 직접 이용해서 계산을 때리면 값을 얻을 수는 있습니다. 그러나 엑셀에서 자주 사용하는 선형과 지수 형의 추세선 데이터 값은 기본으로 제공하는 함수가 있고 중급 함수 편에서 소개한 배열 함수 TREND와 GROWTH입니다. 복습하고 가겠습니다.

   배열 함수 입력하는 법 안 잊어 버렸죠? 먼저 수식이 들어갈 영역을 전체 선택하고 수식을 입력 후 인수를 드래그 하고 컨트롤 + 쉬프트 + 엔터를 누릅니다. TREND는 선형 추세선의 해당 데이터 값을 반환 합니다.

   요즘 친절한 누구 누구라는 블로그 명이 유행하는 것 같던데 저도 친절한 제이킴 한번 해볼까요?

   배열 입력 단축키

   컨트롤 + 쉬프트 + 엔터

   마찬가지로 GROWTH 함수는 지수형 추세선의 데이터 값을 반환합니다.

   차트에 데이터 추가하는 것 안 잊어 버렸죠? 이렇게 연결된 경우는 그냥 드래그 하면 됩니다. 선형 추세선과 지수형 추세선의 값을 셀에 계산한 후 차트를 그리면 추세선과 일치 합니다.

   만약다항식이나다른복잡한 형태의추세선의값을데이터로뽑고싶다면추세선을표기한후직접수식을수식입력줄에입력하는수밖에 없습니다. 매우많은다항식의모든옵션을엑셀이가지고있을수는없으니기본선형과지수형추세선의함수만제공 합니다.