엑셀 1차함수 그리기 - egsel 1chahamsu geuligi

 엑셀에는 추세선을 그려주는 기능이 있다. 보통 추세선 수식을 구하고 싶을 때는 LINEST 함수를 직접 쓰는 것이 편리하지만 그냥 대충 보고 싶을 때는 그래프에 있는 추세선 기능을 사용하는 것이 편리하다. 엑셀 추세선 기능에는 여러 가지 함수 모델을 제공하기 때문에 편리하게 골라서 사용할 수 있다.

1. 오차가 포함된 데이터

 다음과 같이 오차가 포함된 데이터가 있다고 해보자. 이 그래프는 어떤 수식 모델을 따르는 것인지 궁금해진다.

2. 엑셀 그래프에서 추세선 그리기

  엑셀 그래프는 꼭 "분산형"으로 그린다. 선도 이어질 필요는 없다. 산점도 그래프를 그리고 데이터 점에 우클릭한 뒤 "추세선 추가"를 선택한다.

3. 추세선 모델

  오른쪽에 추세선 서식이 뿅 나타나면 속성을 선택할 수 있다. <추세선 옵션>에서 모델을 선택할 수 있고 지수, 선형, 로그, 다항식, 거듭제곱, 이동 평균을 선택할 수 있다. <추세선 이름>은 내 맘대로 써주면 범례에 나타난다. <예측>에 값을 넣으면 현재 데이터 앞뒤로 추세선을 연장해서 데이터 밖의 상황을 예측해볼 수 있다. 만약 어떤 데이터로 근사 모델을 만든다면 데이터 범위를 넘어서는 부분은 되도록 사용하지 않는 것이 좋다. <절편>에 값을 넣으면 Y축 절편을 지정하는 것으로 그 값을 강제로 지나도록 모델을 결정한다. <수식을 차트에 표시>를 선택하면 모델 수식을 그래프에 보여주고 <R-제곱 값을 차트에 표시>를 이용하면 결정계수를 보여준다. 

 각 그래프의 수학 모델은 다음과 같다.

2.1. 지수 모델 (Exponential trendline)

  지수 모델을 만들 수 있다. y 방향으로 이동된 모델은 엑셀 추세선 내장 모델로 적합(fitting)할 수 없다. 데이터에 음수가 있어도 사용할 수 없다. 여기에서는 첫번째 값을 1.0으로 변경해 그려봤다. 결정계수가 0.56 수준으로 아주 안 맞는 모델임을 알 수 있다.

$$ y = a e^{bx} $$

2.2. 선형 모델 (Linear trendline)

 선형 모델은 단순한 직선의 방정식으로 적합하는 것으로 가장 많이 쓰이고 가장 중요한 모델이다. 결정계수가 0.88 수준으로 꽤 잘 맞는 모델인 것을 알 수 있지만 느낌적인 느낌으로 조금 부족하다는 생각이 든다.

$$ y = ax + b $$

2.3. 로그 모델 (Natural logarithmic trendline)

 로그 모델은 자연로그 함수로 적합을 하지만 로그 함수의 특성상 상용로그나 밑이 다른 로그 함수로 얼마든지 바꿀 수 있기 때문에 전혀 문제가 되지 않는다. 로그 모델을 선택해보니 아주 잘 맞는 것을 알 수 있다. 로그 모델이 이 데이터의 추세를 잘 표현해주고 있다.

$$ y = a\ln(x) + b $$

2.4. 다항식 모델 (Polynomial trendline)

 다항식 모델은 2차 부터 6차까지 사용 가능하다. 1차는 선형 모델이기 때문에 빠진 듯? 차수를 늘리면 결정계수가 '무조건 증가'하기 때문에 차수를 높이고 '잘 맞는 모델 만들었다~'라고 자랑하면 교수님한테 혼난다. 조심하자.

$$ y = \sum^{n}_{n=0} a_n x^n $$

2.5. 거듭제곱 모델 (Power trendline)

 거듭제곱 모델은 일정한 비율(steady rate)로 증가하거나 감소하는 모델을 표현할 때 사용한다. 데이터에 0이나 음수가 있으면 그릴 수 없다. 여기에서도 첫 데이터를 1로 변경하여 표시해봤다. 결정계수를 보면 그럴 듯 하지만 별로 안 맞는 것 같은데..라는 생각을 지울 수 없다.

$$ y = a x^b $$

2.6. 이동평균 (Moving average trendline)

 이동평균은 정해진 구간 내의 데이터를 평균해서 연결한 것이다. 추세선 위의 한 점이 그 주위 데이터의 평균값이라고 생각하면 된다. 이동평균을 구하면 들쭉날쭉한 잡음은 사라지고 부드러운 그래프를 만들어 추세의 변화를 확인할 수 있다. 흔히들 주식에서 말하는 이평선이 별 게 아니고 이동평균선을 줄여서 부르는 말이다. 20일 이평선, 60일 이평선 이런 것이 다 그냥 어떤 날짜 주위로 20일 평균 내서 점찍고 다음날도 주위의 20일을 평균 내서 점찍고 주욱 연결한 선이다. 그러니까 그냥 장기간의 지글지글한 데이터의 추세를 보자고 그은 것이다. 단기간에는 지글지글 한데 넓은 구간에서 눈을 가늘게 뜨고 보면 우상향 하고 있었구나! 하고 알 수가 있을 것이다. 그래도 내가 가진 데이터를 이용해서 가지고 있지 않은 내부를 예측하는 것은 의미가 있으나 데이터 밖의 미래를 예측한다고 하면.. 그건 좀.. 위험한 일이 될 것이다.

 아무튼 지글지글한 잡음을 날린다는 점에서 저역 통과 필터(low pass filter)로 생각할 수 있다.

3. 데이터는 사실?

 여기에서 사용한 데이터는 상용로그 함수로 아무렇게나 만들고 랜덤 함수로 흔들어 놓은 것이었다. 사용한 파라미터와 모델은 다음과 같다.

$$ a = 100, b = 0.4, c = 10 $$

$$ y = a \log(bx) + c $$

 여기에서 로그의 이런저런 성질을 이용해 다시 써보면 아래와 같다.

$$ y = \frac{a\log b}{\ln10} \ln x + \left[ a\ln b + c \right] $$

$$ A = \frac{a\log b}{\ln10}, B = a\ln b + c $$

 계산해보면 $A = 43.43, B = -29.79 $로 로그 모델 추세선이 잘 설명하고 있는 것을 알 수 있다.

4. 추가 사용 팁

 추세선을 그리고 수식을 표시하게 한 뒤 그 수식을 다시 엑셀 시트에 직접 타이핑해서 사용하는 경우를 종종 봤다. 때로는 잘 동작하겠지만 가끔은 전혀 엉뚱한 값이 나와서 당황하는 경우가 있다. 이는 수식에 사용된 유효숫자 자릿수 때문이며 아래 메뉴에서 유효숫자를 와장창 늘려서 사용하면 원하는 결과를 얻을 수가 있다. 하지만 되도록이면 LINEST 함수를 이용해 직접 구해서 사용하는 것을 추천한다.

4.1. 추세선 유효숫자 늘리기

4.1.1. 추세선 수식에 우클릭 후 <추세선 레이블 서식> 메뉴에 진입

4.1.2. 표시 형식에서 숫자 선택

4.1.3. 유효숫자 자릿수를 와장창 늘린다.

5. 직접 추세선 구하기 (LINEST)

 LINEST 함수를 직접 이용해 추세선을 직접 구하고 엑셀 시트에 표시할 수 있다. 이렇게 하면 그래프에 표시된 추세선 수식을 눈 아프게 옮겨 적을 필요가 없다. 선형 회귀 분석이기 때문에 지수 모델, 로그 모델, 그리고 거듭제곱 모델도 로그를 써서 수식을 조금 변형하면 모두 적용이 가능하다.

2021/01/17 - [분석 코드/Excel+VBA] - [Excel] 선형 회귀분석으로 추세선 직접 구하기 (LINEST)

[Excel] 선형 회귀분석으로 추세선 직접 구하기 (LINEST)

 엑셀을 이용해 간단하게 회귀분석을 할 수 있다. LINEST 함수는 선형 회귀 분석을 할 때 사용하는 함수이다. 이 함수를 이용하면 엑셀 추세선 기능에 있는 모델뿐만 아니라 내가 직접 만든 괴상

satlab.tistory.com