엑셀 그래프 변곡점 찾기 - egsel geulaepeu byeongogjeom chajgi

공학적인 문제들을 풀다보면

충분하지 않은 데이터의 보정을 위해 필연적으로 수치해석적 기법을 도입해야할 때가 있다.

그 중 엄청나게 많이 쓰이는 방법 중 하나가 커브 피팅(Curve fitting)이다.

평면 위에 흩어진 점에 대하여 알맞는 곡선을 계산하고 산출하는 방법인데

이를 위해서는 Origin(오리진), Matlab(매틀랩), 엑셀 등의 프로그램들을 사용할 수 있다.

이번 포스팅은엑셀을 이용한 3차 다항함수 커브 피팅을 위한 방법이다.

(1) 그래프를 이용한 커브 피팅 

먼저 다음과 같은 데이터가 있다고 하자.

위의 데이터를 3차원 다항함수로 피팅하기 위해서, 일단 "삽입 → 차트→ 분산형"로 차트를 그려준다. 

뭐 본인 입맛에 맞는 그래프를 선택하면 되겠지만...개인적으로는 왼쪽 첫 번째 그래프를 추천한다. 

그래프를 선택한 후, 일반적인 방법과 동일하게 "데이터 선택"을 클릭하고 다음과 같은 방법으로 "계열값"을 선택한다.  

확인을 눌러 그래프를 그린 후,  

다음과 같이 그래프의 점들 중 하나에서 오른쪽 버튼을 눌러 "추세선 추가"를 선택한다. 

세부 설정은 다음과 같이 맞추어 준다. 

그럼 오른쪽 그림과 같이 알흠다훈 피팅 그래프와 피팅 함수를 얻을 수 있다. 

(2) 엑셀 내장함수를 이용한 커브 피팅 

위의 그래프를 이용한 방법으로 피팅하면 그래프의 모양과 피팅 함수를 구할 수 있어 매우 편리하다.  

그러나 많은 경우에 피팅 함수 자체 보다는 계수가 필요한 경우가 있다.  

따라서 이번에는 엑셀의 내장 함수를 이용해 원하는 셀에 피팅 함수 계수를 나타내는 법에 대해서 알아보도록 한다. 

식은 엑셀의 "INDEX" 함수와  "LINEST" 함수를 이용한다.

데이터는 위에서 그래프를 그렸던 것과 동일한 것을 사용하였다.

G3 = INDEX(LINEST($D$3:$D$13,$C$3:$C$13^{1,2,3}),1,F3)

G4 = INDEX(LINEST($D$3:$D$13,$C$3:$C$13^{1,2,3}),1,F4)

G5 = INDEX(LINEST($D$3:$D$13,$C$3:$C$13^{1,2,3}),1,F5)

G6 = INDEX(LINEST($D$3:$D$13,$C$3:$C$13^{1,2,3}),1,F6)

빨간 부분에는 Y계열 값, 초록 부분에는 X계열 값, 파란 부분에는 계수의 순서를 입력해 준다.

그럼 위와 같이 베아우티풀하게 3차 다항식 커브피팅의 계수들을 구할 수 있다.

맨 위의 계수가 최고차항의 계수이고, 맨 마지막이 상수항이다.

그래프를 이용한 커브피팅의 계수와 비교하면 동일한 값을 얻을 수 있음을 알 수 있다.

함수의 $ 표시를 이용해 다양하게 응용이 가능하다.

 -   끝   -

분산형 그래프

표작성

아래의 데이터를 가지고 분산형 그래프를 만들어 보겠습니다.

각각의 값들은 시간에 따른 값의 변화일수도 있고,

특정 조건에 따른 값일 수도 있습니다.

그래프 작성시에는 아래그림처럼 2개의 데이터열만 선택해도 됩니다.

그래프 작성

분산형 그래프 기본형식입니다. X축에는 첫번째 열의 값,

Y축에는 두번째 열의 값에 따라 그래프에 점으로 표시되었습니다.

분산형 그래프의 종류

기본형태인 점으로 표현되는 그래프외에,

각 점들을 직선 혹은 곡선으로 이을 수도 있으며,

점은 표현하지 않고 각 점들을 이은 선으로만 표현하는 것도 가능합니다.

분산형 그래프에 추세선 만들기

그래프에서 데이터계열(점으로 표현된)을 오른쪽 버튼으로

클릭하여 "추세선 추가"를 클릭합니다.

데이터의 변화추세에 맞는 추세선형식을 선택합니다.

기본적으로는 선형 추세선이 선택되지만, 다항식이라든지

거듭제곱함수를 사용한 추세선 등 원하는 추세선을 선택합니다.

추세선 서식의 편집도 가능합니다.

"수식을 차트에 표시"를 선택하면, 선택한 추세선의 수식이

그래프에 표현됩니다.

값 예측

추세선을 이용한 값예측입니다.

아래에서는 "앞으로 20 구간"을 지정한 것인데요.

여기서 구간은 현재 그래프의 축을 기준으로 지정됩니다.

현재 그래프에서는 정수(0,1,2,3.....)단위로 x,y축이 구성되어 있으므로,

20구간은 축단위 20이 되는 것입니다.

아래에서는 원래 노란색으로 칠해진 곳에만 추세선이 있었으나,

20간 예측을 지정하여 추세선이 연장되어 있습니다.

이상입니다.