엑셀은 데이터 보정 및 최적선 계산에 유용한 내장 기능을 제공합니다. 이러한 기능은 화학 실험 보고서 작성이나 장비 보정 계수 프로그래밍 시 특히 유용합니다.
본 글에서는 엑셀을 활용하여 차트를 만들고, 선형 보정 곡선을 그리고, 곡선의 수식을 표시하며, SLOPE 및 INTERCEPT 함수를 사용하여 보정 방정식을 간편하게 설정하는 방법을 자세히 살펴보겠습니다.
보정 곡선과 엑셀의 유용성
보정 과정은 장치 측정값 (예: 온도계의 온도 표시)과 표준으로 알려진 값 (예: 물의 어는점과 끓는점)을 비교하는 것에서 시작됩니다. 이를 통해 보정 곡선을 생성할 데이터 쌍을 얻을 수 있습니다.
온도계의 2점 보정 (물의 어는점과 끓는점 이용)에는 두 개의 데이터 쌍 (예: 0°C 및 100°C)이 필요합니다. 이 두 쌍을 그래프에 표시하고 그 사이에 선 (보정 곡선)을 그리면, 온도계 응답이 선형이라고 가정할 때 해당 선에서 임의의 점을 선택하여 온도계가 표시하는 값에 상응하는 “실제” 온도를 추정할 수 있습니다.
온도계가 57.2도를 가리킬 때 실제 온도를 추정할 수 있는 근거를 제공하는 것이 보정 곡선입니다. 즉, 알려진 두 점 사이의 정보를 보간하여 측정하지 않은 값에 대한 표준을 예측할 수 있습니다.
엑셀은 데이터 쌍을 차트에 시각적으로 표시하고, 추세선 (보정 곡선)을 추가하고, 그 곡선의 방정식을 보여주는 기능을 제공합니다. 또한 SLOPE 및 INTERCEPT 함수를 사용하여 선의 방정식을 계산할 수 있습니다. 이렇게 얻은 값을 간단한 공식에 대입하면 모든 측정값에 대한 “참”값을 자동으로 계산할 수 있습니다.
예시를 통해 자세히 알아보기
다음 예시는 X값과 Y값으로 구성된 10개의 데이터 쌍을 사용하여 보정 곡선을 만드는 과정을 보여줍니다. X값은 화학 용액 농도부터 대리석 발사기 프로그램의 입력 변수에 이르기까지 다양한 종류의 “표준”값을 나타낼 수 있습니다.
Y값은 각 시료 측정 시 나타나는 장비의 “응답”값 또는 각 입력값으로 발사된 대리석의 착지 거리를 나타냅니다.
보정 곡선을 그래프로 그린 후, SLOPE 및 INTERCEPT 함수를 사용하여 보정선의 방정식을 계산합니다. 이를 통해 장비 판독값을 기반으로 “미지” 용액의 농도를 결정하거나 발사기에서 대리석을 특정 거리에 착지시키는 데 필요한 프로그램 입력값을 찾을 수 있습니다.
1단계: 차트 생성
간단한 예시 스프레드시트는 X값과 Y값으로 구성된 두 개의 열로 이루어져 있습니다.
차트에 표시할 데이터를 선택하는 것으로 시작해 보겠습니다.
먼저 ‘X값’ 열의 셀을 선택합니다.
Ctrl 키를 누른 상태에서 ‘Y값’ 열 셀을 클릭합니다.
‘삽입’ 탭으로 이동합니다.
‘차트’ 메뉴에서 ‘분산형’ 드롭다운의 첫 번째 옵션을 선택합니다.
두 열의 데이터 포인트를 포함하는 차트가 생성됩니다.
차트의 파란 점 중 하나를 클릭하여 데이터 계열을 선택합니다. 엑셀은 선택된 점의 윤곽을 표시합니다.
점 중 하나를 마우스 오른쪽 버튼으로 클릭하고 ‘추세선 추가’ 옵션을 선택합니다.
차트에 직선이 나타납니다.
화면 오른쪽에 ‘추세선 서식’ 메뉴가 나타납니다. ‘차트에 수식 표시’ 및 ‘차트에 R-제곱 값 표시’ 옆의 확인란을 선택합니다. R-제곱 값은 데이터가 선에 얼마나 잘 맞는지 알려주는 지표입니다. R-제곱 값이 1.000이면 모든 데이터 포인트가 선에 정확히 일치함을 의미합니다. 데이터 포인트와 선의 차이가 클수록 R-제곱 값은 감소하며 0.000이 최저 값입니다.
추세선 방정식과 R-제곱 값이 차트에 나타납니다. 이 예시에서 R-제곱 값은 0.988로 매우 높은 상관 관계를 보입니다.
방정식은 ‘Y = Mx + B’ 형태입니다. 여기서 M은 기울기, B는 y절편입니다.
이제 보정이 완료되었으므로 차트 제목을 편집하고 축 제목을 추가하여 차트를 맞춤 설정해 보겠습니다.
차트 제목을 변경하려면 제목을 클릭하여 텍스트를 선택합니다.
차트를 설명하는 새 제목을 입력합니다.
x축과 y축에 제목을 추가하려면 ‘차트 도구 > 디자인’으로 이동합니다.
‘차트 요소 추가’ 드롭다운을 클릭합니다.
‘축 제목 > 기본 가로’로 이동합니다.
축 제목이 나타납니다.
축 제목 텍스트를 선택한 후 새 제목을 입력하여 이름을 바꿉니다.
이제 ‘축 제목 > 기본 세로’로 이동합니다.
축 제목이 나타납니다.
텍스트를 선택하고 새 제목을 입력하여 이 제목의 이름을 바꿉니다.
이제 차트가 완성되었습니다.
2단계: 선형 방정식 및 R-제곱 값 계산
이제 엑셀의 내장 함수인 SLOPE, INTERCEPT 및 CORREL 함수를 사용하여 선형 방정식과 R-제곱 값을 계산해 보겠습니다.
스프레드시트 14행에 이 세 가지 함수에 대한 제목을 추가했습니다. 해당 제목 아래의 셀에서 실제 계산을 수행합니다.
먼저 SLOPE를 계산합니다. A15 셀을 선택합니다.
수식 > 추가 기능 > 통계 > SLOPE로 이동합니다.
함수 인수 창이 팝업됩니다. ‘Known_ys’ 필드에 Y값 열 셀을 선택하거나 입력합니다.
‘Known_xs’ 필드에 X값 열 셀을 선택하거나 입력합니다. SLOPE 함수에서 ‘Known_ys’ 및 ‘Known_xs’ 필드의 순서는 매우 중요합니다.
‘확인’을 클릭합니다. 수식 입력줄에 최종 수식은 다음과 같아야 합니다.
=SLOPE(C3:C12,B3:B12)
A15 셀의 SLOPE 함수에서 반환된 값은 차트에 표시된 값과 같습니다.
다음으로 B15 셀을 선택하고 수식 > 추가 기능 > 통계 > INTERCEPT로 이동합니다.
함수 인수 창이 팝업됩니다. ‘Known_ys’ 필드의 Y값 열 셀을 선택하거나 입력합니다.
‘Known_xs’ 필드에 X값 열 셀을 선택하거나 입력합니다. INTERCEPT 함수에서도 ‘Known_ys’ 및 ‘Known_xs’ 필드의 순서가 중요합니다.
‘확인’을 클릭합니다. 수식 입력줄의 최종 수식은 다음과 같아야 합니다.
=INTERCEPT(C3:C12,B3:B12)
INTERCEPT 함수에서 반환된 값은 차트에 표시된 y 절편과 일치합니다.
다음으로 C15 셀을 선택하고 수식 > 추가 기능 > 통계 > CORREL로 이동합니다.
함수 인수 창이 팝업됩니다. ‘Array1’ 필드에 대해 두 셀 범위 중 하나를 선택하거나 입력합니다. SLOPE 및 INTERCEPT와 달리 순서는 CORREL 함수 결과에 영향을 미치지 않습니다.
‘Array2’ 필드에 대해 다른 셀 범위 중 하나를 선택하거나 입력합니다.
‘확인’을 클릭합니다. 수식은 수식 입력줄에 다음과 같이 표시되어야 합니다.
=CORREL(B3:B12,C3:C12)
CORREL 함수에서 반환된 값은 차트의 ‘R-제곱’ 값과 일치하지 않습니다. CORREL 함수는 ‘R’을 반환하므로 ‘R-제곱’을 계산하려면 이 값을 제곱해야 합니다.
수식 표시줄 내부를 클릭하고 수식 끝에 ‘^2’를 추가하여 CORREL 함수에서 반환된 값을 제곱합니다. 완성된 수식은 다음과 같아야 합니다.
=CORREL(B3:B12,C3:C12)^2
Enter 키를 누릅니다.
공식을 변경한 후 ‘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 값을 입력하면 엑셀에서 해당 Y 값을 자동으로 계산합니다.
두 번째 굵은 테두리 셀에 Y 값을 입력하면 해당 X 값이 제공됩니다. 이 공식은 해당 용액의 농도를 계산하는 데 사용하거나 특정 거리에서 구슬을 발사하는 데 필요한 입력으로 사용할 수 있습니다.
이 경우 장비가 ‘5’를 읽으면 보정 결과 농도는 4.94입니다. 또는 구슬을 5단위 거리로 이동시키려면 구슬 발사기를 제어하는 프로그램의 입력 변수로 4.94를 입력해야 합니다. 이 예시에서 높은 R-제곱 값으로 인해 이러한 결과에 대해 상당히 확신할 수 있습니다.