회귀분석의 이차방정식 (+가중치) 계산 내용을 엑셀로 만들어보고,
비선형 검정 곡선 (Non-linear Calibration Curve)에 적용해보자.
표준물질 측정 결과와 농도의 상관성 수식을 회귀분석으로 확인할 수 있다.
많은 실험 결과에서 선형 상관성 $(y=ax+b)$이 나타나지만,
일부 실험 결과에서는 비선형 상관성을 보이기도 한다.
다양한 비선형 상관성 중에서 이차방정식$(y=ax^2+bx+c)$의 계산 방법을 알아보고,
추가적으로 이차방정식의 가중치 계산 방법도 확인하였다.
# 이차방정식$(y=ax^2+bx+c)$의 회귀분석 결과 계산
검정곡선의 실험 결과에 적합한 이차방정식을 생성하기 위해서는 수식의 상수값을 찾아야 한다.
회귀분석으로 적합한 수식을 찾는 수식은 다음과 같다.
상수 a, b, c 를 계산하는 수식에 사용되는 항목들의 배치는 다르지만,
동일한 항목 $ \sum{x_i}, \sum{y_i}, \sum{x_i}{y_i}, \sum{x_i^2}, \sum{x_i^3}, \sum{x_i^4}, \sum{x_i^2}{y_i}$ 이 사용된다.
해당 항목들은 미리 계산하고 상수 수식에 대입하는 방식을 사용하였다.
엑셀 함수로는 각 배열의 곱의 합을 계산하는 "=sumproduct(배열1, 배열2, ......)" 를 사용하였다.
수식 내용은 아래 참고 자료를 통해서 확인하였다.
참고: Quadratic Regression Calculator. (2024). Good Calculators (Free online calculators).
Retrieved from https://goodcalculators.com/quadratic-regression-calculator/
상관계수는 이차방정식의 상수값을 계산한 후에
상관계수 $r$은 다음과 같은 수식으로 계산된다.
$$r=\sqrt{ 1 - \frac{ \sum{ ( y_i - (ax_i^2 + bx_i + c) ) ^2 }}{\sum{(y_i - \bar{y})^2}} }$$
상관계수 수식은 y 값의 전체 제곱합과 잔자(residual) 제곱합을 이용해서 계산하는 방식을 사용하였다.
상관계수의 다른 계산방식은 아래 내용을 참고하면 확인할 수 있다.
[Excel] 단순 선형 회귀분석 (Simple Linear Regression) : 검정곡선 (Calibration Curve) 검증
단순 선형 회귀분석 (Simple Linear Regression) 결과를 이해하고,검정 곡선 (Calibration Curve) 검증에 필요한 내용을 알아보자.표준물질 측정결과를 바탕으로 농도와 장비의 상관성 수식을 회귀분석을 통
analchem.tistory.com
확인된 회귀분석 공식을 이용해서
엑셀에서 검정곡선 실험 결과로 이차방정식을 확인하였다.
검정곡선 실험 결과는 다음과 같다.
| n | X | Y |
| 1 | 0.1 | 0.02 |
| 2 | 0.5 | 0.15 |
| 3 | 1.0 | 0.43 |
| 4 | 2.0 | 1.52 |
| 5 | 5.0 | 5.08 |
| 6 | 7.0 | 10.13 |
농도 (X)와 측정 결과 (Y)를 이용해서 이차방정식을 얻기 위해서는
먼저 계산에 필요한 공통 항목들을 계산하였다.
| $\sum{x_i}$ | 15.60 |
| $\sum{y_i}$ | 17.33 |
| $\sum{x_i}{y_i}$ | 99.86 |
| $\sum{x_i^2}$ | 79.26 |
| $\sum{x_i^3}$ | 477.13 |
| $\sum{x_i^4}$ | 3043.06 |
| $\sum{x_i^2}{y_i}$ | 629.92 |
| $n$ | 6 |
그 다음에 각 상수 값을 계산하는 수식에 대입해서 이차방정식의 모든 상수 값과 결정계수를 얻었다.
| $a$ | 0.1760 |
| $b$ | 0.1834 |
| $c$ | 0.0867 |
| $R^2$ | 0.9967 |
엑셀에서 분산형 그래프를 작성하고 추세선을 추가해서
다항식 옵션을 선택해서 차수를 2로 설정하면 이차방정식을 얻을 수 있다.
해당 결과와 동일한지 확인하였다.
수식으로 계산된 상수값과 엑셀의 추세선으로 확인된 상수값에 차이가 없는 것이 확인되었다.
# 이차방정식$(y=ax^2+bx+c)$의 회귀분석 가중치 적용 계산
가중치 적용 계산은 이전에 발행한 내용을 참고하였다.
2023.12.24 - [기기분석 데이터] - [Excel] 검정 곡선 (Calibration Curve) 교정 : 가중치 (Weighting)
[Excel] 검정 곡선 (Calibration Curve) 교정 : 가중치 (Weighting)
엑셀로 선형 검정 곡선 (Calibration curve)의 가중치 (Weighting) 설정하기 선형의 검정 곡선 (y = ax +b)은 농도 (concentration)와 장비 반응 (response)의 회귀분석 결과로 만들어진다. 농도와 장비 반응의 상관
analchem.tistory.com
이전 내용은 일차방정식의 가중치 설정 내용이지만,
이차방정식의 가중치 설정 내용도 동일하게 적용되기 때문에
이차방정식 상수 계산 에서 필요한 공통 항목에 가중치 항목을 추가하면 된다.
가중치 계수 (weighting factor)는 총 4가지 ($1/x, 1/x^2, 1/y, 1/y^2$)를 추가해서 계산식에 적용하였다.
| Weighting factor | 1/x |
| $\sum{w_i}{x_i}$ | 6.00 |
| $\sum{w_i}{y_i}$ | 4.15 |
| $\sum{w_i}{x_i}{y_i}$ | 17.33 |
| $\sum{w_i}{x_i^2}$ | 15.60 |
| $\sum{w_i}{x_i^3}$ | 79.26 |
| $\sum{w_i}{x_i^4}$ | 477.13 |
| $\sum{w_i}{x_i^2}{y_i}$ | 99.86 |
| $\sum{w_i}$ | 13.84 |
가중치 계수를 선택할 수 있도록 엑셀의 "데이터 유효성 검사" 메뉴를 사용하였다.
계수를 선택하면 계수에 맞는 가중치 ($w_i$)를 가져와서 계산하도록 되어있다.
가중치 적용 계산에서는 $n$ 대신 $\sum{w_i}$으로 교체되어 계산된다.
| $a$ | 0.1551 |
| $b$ | 0.3295 |
| $c$ | -0.0176 |
| $R^2$ | 0.9962 |
가중치가 적용되지 않은 결과와 비교해서 상수 a, b, c 모두 변경된 것을 확인할 수 있고,
상수 변경으로 곡선에 변형이 발생하기 때문에 결정계수도 차이가 나타나는 것이 확인된다.
적용된 가중치 계수는 낮은 농도(x) 보정에 사용되는 것으로
엑셀에 계산된 정확도 내용을 가중치 계수에 따라서 비교해 보면
낮은 농도에서 변화되는 내용을 확인할 수 있다.
적합한 가중치 계수 설정 방법은 이전에 작성된 내용을 참고하시면 확인할 수 있다.
2023.12.24 - [기기분석 데이터] - [Excel] 검정 곡선 (Calibration Curve) 교정 : 가중치 (Weighting)
# 이차방정식$(y=ax^2+bx+c)$의 농도 계산
검정곡선으로 적합한 이차방정식이 설정되면
시료 분석으로 확인된 측정 결과 (Y) 값을 이용해서
시료의 농도 (X) 값을 확인할 수 있다.
Y 값을 이차방정식에 대입해서 X 값은
이차방정식 근의 공식을 이용하여 아래와 같은 수식으로 계산한다.
$$x=\frac{-b + \sqrt{b^2-4a(c-y)}}{2a}$$
실험에서 X 값은 음의 값이 발생되지 않기 때문에
양의 값을 얻기 위해서 "±" 대신 "+" 를 적용한다.
근의 공식의 이차방정식은 $ax^2+bx+c=0$ 에서 유도한 수식으로
검정곡선의 이차방정식은 $ax^2+bx+c=y$ 에서 계산해야 하므로
$c$ 값을 $c-y$ 로 변경해서 계산하면 X 값을 얻을 수 있다.
자세한 계산 방법 및 결과들을 확인하려면 첨부된 엑셀 파일을 확인하면 자세한 내용을 확인할 수 있다.
실험 데이터를 X, Y 항목 아래에 입력하면 자동으로 계산되도록 작성되어 있다.
실제 실험 데이터를 입력해서 결과를 확인할 수 있다.
'기기분석 데이터' 카테고리의 다른 글
| 검정곡선 (Calibration Curve) 농도 설정과 반복 측정 (2) | 2024.09.23 |
|---|---|
| [Excel] 단순 선형 회귀분석 (Simple Linear Regression) : 검정곡선 (Calibration Curve) 검증 (0) | 2024.08.19 |
| 검정 곡선 (Calibration Curve) 평가와 가중치 (Weighting) 적용 예시 (2) | 2024.01.08 |
| [Excel] 검정 곡선 (calibration Curve) 평가 : 등분산성 (Homoscedasticity) (0) | 2023.12.31 |
| [Excel] 검정 곡선 (Calibration Curve) 평가 : 잔차도 (Residual Plot) (0) | 2023.12.26 |
