엑셀(Excel)에서 데이터를 선형, n차 근사 및 피팅 값 구하기(slope, intercept, index, linest)

엑셀에서 실험적으로 얻거나 계산한 데이터를 가지고 1차, 2차 방정식 그래프를 그려보고, 방정식 값을 알아내는 법을 엑셀의 추세선과 수식을 이용하여 나타내봅시다. 두가지 방법 모두 장단점이 존재하는데 추세선을 이용하면 그래프를 그려서 방정식을 나타낼수 있다는 점이 장점이고 수식은 방정식을 구한 후 특정한 x값을 넣기 편하다는 장점이 있습니다.

1. 추세선을 이용하여 그래프 및 식으로 나타내기 (그래프 사용)

2. 수식을 이용하여 방정식을 나타내고 대입해보기 (slope, intercept, index, linest 함수)

2.1. 일차함수 근사 (slope, intercept 함수)

2.2. n차함수 근사 (index, linext 함수)

1. 추세선을 이용하여 그래프 및 식으로 나타내기

엑셀에서 셀에 도수를 입력한 후 함수 그래프, 방정식을 만드는 법을 배우도록 하겠습니다.

먼저 온도(T)에 따른 전압(V),전류(I),전력(P)값이 아래 그림과 같이 나왔을 때 그래프를 그려봅시다.

여기서 X축이 될 변수를 왼쪽에 위치시킵니다. 저는 온도를 X축으로 지정했습니다.

그리고 Y축이 될 전압, 전류, 전력을 왼쪽에 위치 시켰습니다. 먼저 온도를 X축, 전압을 Y축으로하는 그래프를 만들어보겠습니다.

이렇게 X값, Y값에 해당하는 부분을 드래그를 해줍니다.

삽입 - 차트 - 분산형(x,y) 혹은 거품형 차트 삽입 - 분산형을 선택한다.

그래프를 누르고 +항목을 클릭 후 추세선을 눌러주면 점선으로 1차 방정식 그래프가 완성되게 됩니다.

만약 이 그래프의 y에 대한 방정식을 알고싶으면 ▶ 클릭 후 기타옵션을 눌러줍니다.

오른쪽 창에 추세선 서식이라는 항목이 생기는데 하단부의 수식을 차트에 표시, R-제곱 값을 차트에 표시를 눌러 주면

아까 그렸던 그래프 안에 도수분포에 따른 일차방정식이 만들어지게 됩니다. 여기서 R2=1이 존재하는데 오차값입니다. R2이 1에 가까울수록 만들어진 방정식이 정확합니다.

2차 혹은 그 이상의 다차방정식 그래프가 있으셔도 상관이 없습니다.

P=V∙I 공식에 따라서 일차함수 두개의 곱인 2차함수로 예시를 들겠습니다. 이번엔 추세선을 누르지 않고 ▶ 클릭 후 기타옵션으로 들어가줍니다..

추세선 서식에서 그래프 모양을 다항식으로 하시고 차수를 정해주시면 됩니다. 수식을 알고싶다면 마찬가지로 하단부의 수식을 차트에 표시, R-제곱 값을 차트에 표시를 하시면 됩니다.

2. 수식을 이용하여 방정식을 나타내고 대입해보기

다음으로는 그래프 말고 수식으로 일차방정식, 이차방정식을 찾는 것을 해보겠습니다.

2.1. 일차함수 근사 (slope, intercept 함수)

y=ax+ b에 해당하는 일차함수부터 설명하겠습니다.

a에 해당하는 기울기를 알려면 slope 함수를 이용하면 됩니다. =slope(y축을 해당하는 도수들, x축에 해당하는 도수들) 이렇게 식을 만들면

기울기값이 5.00318으로 아까 추세선으로 그린 값과 일치합니다.

b에 해당하는 y절편은 =intercept(y축을 해당하는 도수들, x축에 해당하는 도수들)로 구할 수 있습니다.

y=5.00318x + 9.817921의 식이 나왔습니다.

이제 구한 식에 x값을 대입하는 식을 짜보도록 합시다. x값에 해당하는 온도가 2000이라고 가정하고 식으로 만들어서 대입하면…

온도가 2000일 때 전압이 100016.18의 값이 나왔습니다. 이렇게 일차함수는 구했고 이차함수를 알아봅시다.

2.2. n차함수, 다항식 근사 (index, linest 함수)

이차함수 이상의 다차함수는 index 함수와 linest 함수를 이용하면 구할 수 있습니다.

=INDEX(LINEST(y에 해당하는 값,x에 해당하는 값^{x의 계수의 개수}),1,계수의 순서 a부터 1번) 이렇게 하면 됩니다.

예를 들어 2차 함수의 경우 y=ax2+bx+c이므로 빨간 부분이 a,b 2개의 항이므로 1,2라고 입력하시면 됩니다. 만약 3차 함수 였다면 y=ax3+bx2+cx+d이므로 a,b,c 3개의 항이므로 1,2,3이라고 입력하시면 됩니다.

그리고 함수의 마지막 부분은 y=ax2+bx+c일 때 a의 값을 알고싶으면 1, b의 값을 알고싶다면 2, 상수항(c)의 값을 알고 싶으면 3으로 하시면 됩니다.

최종적으로 이차함수의 값은 y=15.03907x2+148.509x+1726.308으로 나오게 되고 아까 추세선으로 구한 값과 동일합니다.

이상으로 추세선과 수식 2가지 방법으로 방정식을 구하는 방법을 알아보았습니다.

긴글 읽어주셔서 감사합니다.

엑셀(Excel)에서 자주 쓰는 함수 정리 1편 (SUM, SUMIF, AVERAGE, COUNTA)

안녕하세요. 이번에는 MS Office의 엑셀(Excel)에 대해 알아볼 거예요. 다들 알다시피 국가자격증인 ‘컴퓨터 활용능력’에 필요하며, 대부분의 직장 업무에도 활용되고 있는 문서작업 프로그램입

jungwonlab.tistory.com

엑셀(Excel)에서 자주 쓰는 함수 정리 2편 (COUNTIFS, VLOOKUP, CONCATENATE, IF)

안녕하세요. 이번에는 엑셀에서 자주 쓰이는 함수 1편에 이어 2편에 대해 정리해봤습니다. 이번 내용은 엑셀에서 자주 쓰는 함수 4개 입니다. COUNTIFS, VLOOKUP, CONCATENATE, IF 함수 함수를 숙지하기 전

jungwonlab.tistory.com

엑셀(Excel)에서 자주 쓰는 함수 정리 3편 (SEARCH, FIND, SUBSTITUTE, MATCH, INDEX)

안녕하세요. 이번에는 엑셀에서 자주 쓰이는 함수 2편에 이어 3편에 대해 정리해봤습니다. 이번 내용은 함수 5개 입니다. SEARCH, FIND, SUBSTITUTE, MATCH, INDEX 함수 오늘의 함수를 숙지하기 전에 알아야

jungwonlab.tistory.com

엑셀(Excel)에서 자주 쓰는 함수 정리 4편 (TODAY, RANK, SLOPE, INTERCEPT)

안녕하세요. 이번에는 엑셀에서 자주 쓰이는 함수 3편에 이어 4편에 대해 정리해봤습니다. 이번 내용은 함수 2개 + 실험 데이터용 함수 2개로 총 4개입니다. TODAY, RANK, SLOPE, INTERCEPT 함수 1. TODAY 함

jungwonlab.tistory.com

직장/연구실에서 쓰는 기초적인 필수 엑셀(Excel) 함수 3개로 모든 업무 처리하기

안녕하세요. 이번에는 여태껏 알려준 함수 중 가장 자주 쓰이는 함수 3가지를 알려드릴까 해요. 이 3가지만 알아도 웬만한 업무는 처리할 수 있으니 알아두면 좋겠죠? VLOOKUP, COUNTIF, SUMIF 함수 + 피

jungwonlab.tistory.com

엑셀(Excel)에서 데이터를 선형, n차 근사 및 피팅 값 구하기(slope, intercept, index, linest)

엑셀에서 실험적으로 얻거나 계산한 데이터를 가지고 1차, 2차 방정식 그래프를 그려보고, 방정식 값을 알아내는 법을 엑셀의 추세선과 수식을 이용하여 나타내봅시다. 두가지 방법 모두 장단점

jungwonlab.tistory.com

저작자표시 비영리 변경금지

'Office Tips' 카테고리의 다른 글

엑셀 필수 스킬 고급 필터 & 자동 필터 (0)	2022.01.24
반복 업무 필수 스킬!! 엑셀(Excel) 매크로?! (직장/연구실) (0)	2022.01.06
직장/연구실에서 쓰는 기초적인 필수 엑셀(Excel) 함수 3개로 모든 업무 처리하기 (2)	2021.10.29
엑셀(Excel)에서 자주 쓰는 함수 정리 4편 (TODAY, RANK, SLOPE, INTERCEPT) (0)	2021.10.12
엑셀(Excel)에서 자주 쓰는 함수 정리 3편 (SEARCH, FIND, SUBSTITUTE, MATCH, INDEX) (0)	2021.10.10