Excel에는 보정 데이터를 표시하고 최적선을 계산하는 데 사용할 수 있는 기본 제공 기능이 있습니다. 이것은 화학 실험실 보고서를 작성하거나 장비에 보정 계수를 프로그래밍할 때 유용할 수 있습니다.
이 기사에서는 Excel을 사용하여 차트를 만들고, 선형 교정 곡선을 그리고, 교정 곡선의 공식을 표시하고, Excel에서 교정 방정식을 사용하기 위해 SLOPE 및 INTERCEPT 함수로 간단한 공식을 설정하는 방법을 살펴보겠습니다.
목차
보정 곡선이란 무엇이며 Excel을 만들 때 Excel이 어떻게 유용합니까?
보정을 수행하려면 장치의 판독값(예: 온도계가 표시하는 온도)을 표준이라고 하는 알려진 값(예: 물의 어는점 및 끓는점)과 비교합니다. 이렇게 하면 보정 곡선을 개발하는 데 사용할 일련의 데이터 쌍을 만들 수 있습니다.
물의 어는점과 끓는점을 사용하는 온도계의 2점 교정에는 두 개의 데이터 쌍이 있습니다. 또는 100°C). 이 두 데이터 쌍을 점으로 표시하고 그 사이에 선(교정 곡선)을 그린 다음 온도계의 응답이 선형이라고 가정하면 온도계가 표시하는 값에 해당하는 선에서 임의의 점을 선택할 수 있습니다. 해당하는 “실제” 온도를 찾을 수 있습니다.
따라서 온도계가 57.2도를 가리키고 있을 때 실제 온도를 추정할 때 합리적으로 확신할 수 있도록 선은 두 개의 알려진 점 사이의 정보를 본질적으로 채우고 있지만 해당하는 “표준”을 측정한 적이 없을 때 그 독서.
Excel에는 차트에 그래픽으로 데이터 쌍을 표시하고 추세선(교정 곡선)을 추가하고 차트에 교정 곡선의 방정식을 표시할 수 있는 기능이 있습니다. 이것은 시각적 표시에 유용하지만 Excel의 SLOPE 및 INTERCEPT 함수를 사용하여 선의 공식을 계산할 수도 있습니다. 이 값을 간단한 공식에 입력하면 모든 측정을 기반으로 “참” 값을 자동으로 계산할 수 있습니다.
예를 살펴보겠습니다.
이 예에서는 각각 X 값과 Y 값으로 구성된 일련의 10개 데이터 쌍에서 교정 곡선을 개발합니다. X 값은 우리의 “표준”이 될 것이며 과학 기기를 사용하여 측정하는 화학 용액의 농도에서 대리석 발사기를 제어하는 프로그램의 입력 변수에 이르기까지 모든 것을 나타낼 수 있습니다.
Y값은 ‘response’가 되며, 각 약액을 측정할 때 제공되는 계측기의 판독값을 나타내거나 각 입력값을 사용하여 런처에서 마블이 착지한 거리를 측정한 거리를 나타냅니다.
보정 곡선을 그래픽으로 그린 후 SLOPE 및 INTERCEPT 함수를 사용하여 보정 라인의 공식을 계산하고 기기의 판독값을 기반으로 “알 수 없는” 화학 용액의 농도를 결정하거나 프로그램에 어떤 입력을 제공해야 하는지 결정합니다. 대리석은 발사기에서 일정 거리 떨어진 곳에 착지합니다.
1단계: 차트 만들기
간단한 예제 스프레드시트는 X-값과 Y-값의 두 열로 구성됩니다.
차트에 표시할 데이터를 선택하는 것부터 시작하겠습니다.
먼저 ‘X-값’ 열 셀을 선택합니다.
이제 Ctrl 키를 누른 다음 Y-값 열 셀을 클릭합니다.
“삽입” 탭으로 이동합니다.
“차트” 메뉴로 이동하여 “분산” 드롭다운에서 첫 번째 옵션을 선택합니다.
파란색 점 중 하나를 클릭하여 시리즈를 선택합니다. 선택하면 Excel에서 포인트의 윤곽을 표시합니다.
포인트 중 하나를 마우스 오른쪽 버튼으로 클릭한 다음 “추세선 추가” 옵션을 선택합니다.
차트에 직선이 나타납니다.
화면 오른쪽에 “추세선 서식” 메뉴가 나타납니다. “차트에 수식 표시” 및 “차트에 R-제곱 값 표시” 옆의 확인란을 선택합니다. R-제곱 값은 선이 데이터에 얼마나 가깝게 맞는지 알려주는 통계입니다. 최고의 R-제곱 값은 1.000이며, 이는 모든 데이터 포인트가 선에 닿는다는 것을 의미합니다. 데이터 포인트와 선 사이의 차이가 커짐에 따라 r-제곱 값이 떨어지고 0.000이 가능한 가장 낮은 값입니다.
추세선의 방정식과 R-제곱 통계가 차트에 나타납니다. 데이터의 상관 관계는 이 예에서 R-제곱 값이 0.988로 매우 좋습니다.
방정식은 “Y = Mx + B” 형식입니다. 여기서 M은 기울기이고 B는 직선의 y축 절편입니다.
이제 보정이 완료되었으므로 제목을 편집하고 축 제목을 추가하여 차트를 사용자 정의하는 작업을 해 보겠습니다.
차트 제목을 변경하려면 차트 제목을 클릭하여 텍스트를 선택합니다.
이제 차트를 설명하는 새 제목을 입력합니다.
x축과 y축에 제목을 추가하려면 먼저 차트 도구 > 디자인으로 이동합니다.
이제 축 제목 > 기본 가로로 이동합니다.
축 제목의 이름을 바꾸려면 먼저 텍스트를 선택한 다음 새 제목을 입력합니다.
이제 축 제목 > 기본 수직으로 이동합니다.
축 제목이 나타납니다.
텍스트를 선택하고 새 제목을 입력하여 이 제목의 이름을 바꿉니다.
이제 차트가 완성되었습니다.
2단계: 선형 방정식 및 R-제곱 통계 계산
이제 Excel의 기본 제공 SLOPE, INTERCEPT 및 CORREL 함수를 사용하여 선형 방정식과 R-제곱 통계를 계산해 보겠습니다.
시트(14행)에 이 세 가지 기능에 대한 제목을 추가했습니다. 해당 제목 아래의 셀에서 실제 계산을 수행합니다.
먼저 SLOPE를 계산합니다. A15 셀을 선택합니다.
수식 > 추가 기능 > 통계 > SLOPE으로 이동합니다.
“Known_xs” 필드에서 X-값 열 셀을 선택하거나 입력합니다. ‘Known_ys’ 및 ‘Known_xs’ 필드의 순서는 SLOPE 함수에서 중요합니다.
“확인”을 클릭합니다. 수식 입력줄의 최종 수식은 다음과 같아야 합니다.
= 경사(C3:C12,B3:B12)
A15 셀의 SLOPE 함수에서 반환된 값은 차트에 표시된 값과 일치합니다.
그런 다음 B15 셀을 선택한 다음 수식 > 추가 기능 > 통계 > 가로채기로 이동합니다.
“Known_xs” 필드에 대한 X-값 열 셀을 선택하거나 입력합니다. ‘Known_ys’ 및 ‘Known_xs’ 필드의 순서는 INTERCEPT 함수에서도 중요합니다.
“확인”을 클릭합니다. 수식 입력줄의 최종 수식은 다음과 같아야 합니다.
= 인터셉트(C3:C12,B3:B12)
INTERCEPT 함수에서 반환된 값은 차트에 표시된 y 절편과 일치합니다.
그런 다음 C15 셀을 선택하고 수식 > 추가 기능 > 통계 > CORREL로 이동합니다.
“Array2” 필드에 대해 두 셀 범위 중 다른 하나를 선택하거나 입력합니다.
“확인”을 클릭합니다. 수식은 수식 입력줄에 다음과 같이 표시되어야 합니다.
=코렐(B3:B12,C3:C12)
CORREL 함수에서 반환된 값은 차트의 “r-제곱” 값과 일치하지 않습니다. CORREL 함수는 “R”을 반환하므로 “R-제곱”을 계산하려면 이를 제곱해야 합니다.
함수 표시줄 내부를 클릭하고 공식 끝에 “^2″를 추가하여 CORREL 함수에서 반환된 값을 제곱합니다. 완성된 수식은 이제 다음과 같아야 합니다.
=코렐(B3:B12,C3:C12)^2
엔터 키를 치시오.
공식을 변경한 후 “R-제곱” 값이 이제 차트에 표시된 값과 일치합니다.
3단계: 값을 빠르게 계산하기 위한 공식 설정
이제 이 값을 간단한 공식에서 사용하여 “알 수 없는” 솔루션의 농도 또는 구슬이 특정 거리를 날 수 있도록 코드에 입력해야 하는 입력을 결정할 수 있습니다.
이 단계는 X 값 또는 Y 값을 입력하고 보정 곡선을 기반으로 해당 값을 얻는 데 필요한 공식을 설정합니다.
최적선의 방정식은 “Y-값 = SLOPE * X-값 + INTERCEPT” 형식이므로 “Y-값”에 대한 풀이는 X-값과 SLOPE를 곱한 다음 INTERCEPT를 추가합니다.
예를 들어 X 값으로 0을 넣습니다. 반환된 Y 값은 가장 잘 맞는 선의 INTERCEPT와 같아야 합니다. 일치하므로 공식이 올바르게 작동하고 있음을 알 수 있습니다.
Y-값을 기반으로 하는 X-값에 대한 풀이는 Y-값에서 INTERCEPT를 빼고 결과를 SLOPE로 나누어 수행됩니다.
X-value=(Y-value-INTERCEPT)/SLOPE
예를 들어 INTERCEPT를 Y 값으로 사용했습니다. 반환된 X 값은 0과 같아야 하지만 반환된 값은 3.14934E-06입니다. 값을 입력할 때 실수로 INTERCEPT 결과를 잘랐기 때문에 반환된 값은 0이 아닙니다. 수식의 결과가 0.00000314934이고 본질적으로 0이기 때문에 수식은 올바르게 작동합니다.
두꺼운 테두리가 있는 첫 번째 셀에 원하는 X 값을 입력하면 Excel에서 해당 Y 값을 자동으로 계산합니다.
두 번째 두꺼운 테두리 셀에 Y 값을 입력하면 해당 X 값이 제공됩니다. 이 공식은 해당 용액의 농도를 계산하는 데 사용하거나 특정 거리에서 구슬을 발사하는 데 필요한 입력입니다.
이 경우 기기는 “5”를 읽으므로 보정은 농도 4.94를 제안하거나 구슬이 5단위 거리를 이동하기를 원하므로 보정은 구슬 발사기를 제어하는 프로그램의 입력 변수로 4.94를 입력하도록 제안합니다. 이 예에서 높은 R-제곱 값으로 인해 이러한 결과에 대해 합리적으로 확신할 수 있습니다.