Gaussian curves, normal curves and bell curves are synonymous. Each represents how statistical data with normal distribution plots on a graph. Normal distribution describes a particular way statistics deviate from the mean, or center point, of the data, where most results vary little from the mean, and extremes are rare. The result, when plotted on a graph, often have a bell shape. If you know your data mean and its standard deviation, you can use the random number generator from the Excel Analysis ToolPak add-in or your own statistical data to chart a Gaussian curve.
Create Your Worksheet
1.
Enter the following titles in cells A1 to D1, respectively: "MEAN," "BIN," "RANDOM" and "HISTOGRAM". In cell A3, enter "STDEV" to represent "standard deviation." Enter your data mean in cell A2 and your standard deviation value in cell A4. For example, enter "2000" as the mean and "500" as the standard deviation.
2.
Enter the formula "=A2-(3*A4)" in cell B2 to establish the low limit for your data bin, the discrete intervals to which you will compare data. This formula sets three standard deviations below mean for use with the example. Enter the formula "=B2+$a$4" in cell B3. Highlight cell B3, and then click-and-drag the cell handle down to cell B8. This creates the remaining bin intervals.
3.
Create random data upon which to base your curve in this example. Click the "Data" tab at the top of the Excel window, and then click "Data Analysis" at the upper right. Select "Random Number Generation" from the dialog, and then click "OK." Enter "1" for "Number of Variables" and "5000" for "Number of Random Numbers." Select "Normal" from the "Distribution" drop-down list, and then enter "2000" for Mean and "500" for Standard deviation. Click the "Output Range" button, and then enter "C2" in the parameter box. Click "OK" to see 5,000 random numbers generate in column C.
4.
Click on "Data Analysis" again, select "Histogram" from the dialog, and then click "OK." Enter "C2:C5001" for the Input Range and "B2:B8" for the Bin Range. Click the button beside "Output Range," and then enter "D2" in the parameter box. Click "OK" to calculate your histogram data.
5.
Click and drag to highlight cells D2 to E10. Click the "Insert" tab, click on the scatter chart icon in the Charts section, and then select the "Scatter with Smooth Lines" chart. Excel creates your Gaussian curve in chart form.
By Sunil Bhardwaj
4057 Views
Curve fitting is the process of constructing a curve or mathematical function, that has the best fit to a series of data points. Fitted curves can be used as an aid for data visualization, to conclude values of a function where no data are available, and to summarize the relationships among two or more variables.
Equation for Gaussian Curve is, $$ Y = A.{ e }^{ -\left( \frac { { \left( x-B \right) }^{ 2 } }{ 2{ C }^{ 2 } } \right) } $$ Where \(A\) is Height,
\(B\) is Position and
\(C\) is FWHM.
반응형
엑셀에서 실험적으로 얻거나 계산한 데이터를 가지고 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값에 해당하는 부분을 드래그를 해줍니다.
분산형 차트 만들기삽입 - 차트 - 분산형(x,y) 혹은 거품형 차트 삽입 - 분산형을 선택한다.
추세선으로 1차 방정식 만들기그래프를 누르고 +항목을 클릭 후 추세선을 눌러주면 점선으로 1차 방정식 그래프가 완성되게 됩니다.
기타옵션 클릭만약 이 그래프의 y에 대한 방정식을 알고싶으면 ▶ 클릭 후 기타옵션을 눌러줍니다.
수식 표현과 오차 값 표현오른쪽 창에 추세선 서식이라는 항목이 생기는데 하단부의 수식을 차트에 표시, R-제곱 값을 차트에 표시를 눌러 주면
아까 그렸던 그래프 안에 도수분포에 따른 일차방정식이 만들어지게 됩니다. 여기서 R2=1이 존재하는데 오차값입니다. R2이 1에 가까울수록 만들어진 방정식이 정확합니다.
2차 혹은 그 이상의 다차방정식 그래프가 있으셔도 상관이 없습니다.
기타옵션 클릭2P=V∙I 공식에 따라서 일차함수 두개의 곱인 2차함수로 예시를 들겠습니다. 이번엔 추세선을 누르지 않고 ▶ 클릭 후 기타옵션으로 들어가줍니다..
차수 설정추세선 서식에서 그래프 모양을 다항식으로 하시고 차수를 정해주시면 됩니다. 수식을 알고싶다면 마찬가지로 하단부의 수식을 차트에 표시, R-제곱 값을 차트에 표시를 하시면 됩니다.
2. 수식을 이용하여 방정식을 나타내고 대입해보기
다음으로는 그래프 말고 수식으로 일차방정식, 이차방정식을 찾는 것을 해보겠습니다.
2.1. 일차함수 근사 (slope, intercept 함수)
y=ax+ b에 해당하는 일차함수부터 설명하겠습니다.
slope 함수 사용하기a에 해당하는 기울기를 알려면 slope 함수를 이용하면 됩니다. =slope(y축을 해당하는 도수들, x축에 해당하는 도수들) 이렇게 식을 만들면
기울기 나타내기기울기값이 5.00318으로 아까 추세선으로 그린 값과 일치합니다.
intercept 함수 사용하기b에 해당하는 y절편은 =intercept(y축을 해당하는 도수들, x축에 해당하는 도수들)로 구할 수 있습니다.
y=5.00318x + 9.817921의 식이 나왔습니다.
이제 구한 식에 x값을 대입하는 식을 짜보도록 합시다. x값에 해당하는 온도가 2000이라고 가정하고 식으로 만들어서 대입하면…
x값 대입하기전압값 나타내기온도가 2000일 때 전압이 100016.18의 값이 나왔습니다. 이렇게 일차함수는 구했고 이차함수를 알아봅시다.
2.2. n차함수, 다항식 근사 (index, linest 함수)
이차함수 이상의 다차함수는 index 함수와 linest 함수를 이용하면 구할 수 있습니다.
=INDEX(LINEST(y에 해당하는 값,x에 해당하는 값^{x의 계수의 개수}),1,계수의 순서 a부터 1번) 이렇게 하면 됩니다.
index, linest 함수로 다항함수 표현하기예를 들어 2차 함수의 경우 y=ax2+bx+c이므로 빨간 부분이 a,b 2개의 항이므로 1,2라고 입력하시면 됩니다. 만약 3차 함수 였다면 y=ax3+bx2+cx+d이므로 a,b,c 3개의 항이므로 1,2,3이라고 입력하시면 됩니다.
이차함수 a값 구하기그리고 함수의 마지막 부분은 y=ax2+bx+c일 때 a의 값을 알고싶으면 1, b의 값을 알고싶다면 2, 상수항(c)의 값을 알고 싶으면 3으로 하시면 됩니다.
이차함수 b값 구하기2차함수 c값 구하기방정식 계수 구하기최종적으로 이차함수의 값은 y=15.03907x2+148.509x+1726.308으로 나오게 되고 아까 추세선으로 구한 값과 동일합니다.
이상으로 추세선과 수식 2가지 방법으로 방정식을 구하는 방법을 알아보았습니다.
긴글 읽어주셔서 감사합니다.
관련글
엑셀(Excel)에서 자주 쓰는 함수 정리 1편 (SUM, SUMIF, AVERAGE, COUNTA)
엑셀(Excel)에서 자주 쓰는 함수 정리 2편 (COUNTIFS, VLOOKUP, CONCATENATE, IF)
엑셀(Excel)에서 자주 쓰는 함수 정리 3편 (SEARCH, FIND, SUBSTITUTE, MATCH, INDEX)
엑셀(Excel)에서 자주 쓰는 함수 정리 4편 (TODAY, RANK, SLOPE, INTERCEPT)
직장/연구실에서 쓰는 기초적인 필수 엑셀(Excel) 함수 3개로 모든 업무 처리하기
엑셀(Excel)에서 데이터를 선형, n차 근사 및 피팅 값 구하기(slope, intercept, index, linest)
Jungwon Lab 카카오 채널공유하기
게시글 관리
구독하기JungwonLab저작자표시 비영리 변경금지
- 카카오스토리
- 트위터
- 페이스북
'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