[Excel] 비선형 검정곡선 만들기 : 4-Parameter Equation

비선형 검정곡선 4-parameter equation을 엑셀 "해 찾기"로 만들기

엑셀은 기본적으로 선형 회귀분석 (Regression) 기능을 제공하고 있어서 직선의 검정곡선을 만들기에는 어려움이 없다. 

2023.11.24 - [분류 전체 보기] - [Excel] 선형 회귀 분석 (Linear Regression) 수식

직선의 일차방정식은 쉽게 얻을 수 있지만, 비선형의 수식은 지원되지 않는 경우도 있기 때문에 
전문적인 소프트웨어를 이용해야 비선형 수식을 확인할 수 있었다. 

엑셀로 비선형 곡선의 상관성 수식을 얻어내는 방법을 찾던 중에 아래 논문을 확인하게 되었다.  

"Estimating Parameter Precision in Nonlinear Least Squares with Excel’s Solver"

 참고 : de Levie, R. (1999). Estimating parameter precision in nonlinear least squares with Excel's solver. Journal of Chemical Education, 76(11), 1594.

해당 자료는 엑셀 해 찾기 (Solver) 기능을 사용해서 다양한 비선형 공식을 만들었다는 내용이었다. 
엑셀의 해찾기 기능은 일정 기준에 부합하는 최적의 값을 찾아주는 알고리즘이다. 
해 찾기는 비선형 최소 제곱의 문제를 해결하는 대표적인 Levenberg–Marquardt algorithm을 제공하지는 않지만, 
엑셀의 GRG (Generalized Reduced Gradient) 알고리즘과 수식을 평가할 수 있는 잔차 값을 적용하면
Levenberg–Marquardt algorithm의 결과를 얻을 수 있다고 소개되었다.  

엑셀은 시그모이드 함수 (Sigmoid function)에 대한 기능을 제공하지 않기 때문에
시그모이드 함수에서 검정곡선에 주로 사용되는 4-parameter equation을 해 찾기 기능으로 확인해 보았다. 

우선 4-parameter eqaution 에 대해서 알아보자.

$$ y=d+ \dfrac{a-d}{1+\left( \dfrac{x}{c} \right)^b}$$

독립변수 (x) 농도와 종속변수 (y) 측정값의 상관관계를 4개의 상수로 표현한 수식이다.

이 수식의 특징은 그래프의 양 끝 값이 일정 y 값 (a: 최소값 1, d: 최댓값 10) 으로 수렴하고, 
낮은 y 값에서 그래프의 중간으로 갈수록 값이 서서히 증가하다가 급격히 증가한다. (c: 중앙 기울기 6)
그래프의 중앙 x 값 (d: 변곡점 1) 을 지나면 y 값이 증가하는 비율이 감소하다가 일정 값으로 수렴된다. 
4-parameter equation 그래프는 양 끝의 증가하는 곡선과 감소하는 곡선이 대칭적이다. 

검정곡선은 실험 데이터 (점) 를 이용해서 점들의 상관성을 나타내는 수식이다. 
위 그래프의 점 데이터를 기반으로 곡선의 수식을 찾는 과정을 엑셀의 해 찾기 기능으로 확인했다. 
우선 엑셀의 해 찾기 기능부터 활성화해야 한다. 

엑셀 해 찾기 기능 활성화 방법

해 찾기 기능은 엑셀 메뉴에서 "데이터 > 분석 > 해 찾기"에서 확인할 수 있다. 
하지만, 기본 엑셀 메뉴에는 활성화되어 있지 않기 때문에 설정 변경으로 활성화가 필요하다. 

해 찾기 기능으로 수식에 적합한 상수값 a, b, c, d 확인하기

점 데이터와 오차가 적은 최적의 그래프를 얻기 위해서 수식 선택과 상수값 계산이다. 
수식은 4-parameter equation으로 정해졌기 때문에 최적의 상수값을 확인하면 되므로
직선의 검정곡선을 작성하는 원리와 동일하게 잔차 (Residual)가 가장 낮은 값이 되도록 상수값을 찾으면 된다. 
예시는 이론적으로 계산한 데이터를 이용해서 동일한 상수값이 계산되는지 확인해 보았다. 

x	y
0.2	1.001
0.4	1.037
0.8	2.869
1.2	7.742
1.6	9.494
2	9.862

실험 방법과 유사하게 모든 데이터를 사용하지 않고 확인된 범위에서 6개 데이터를 선택적으로 사용했다. 

잔차가 최소가 되도록 상수값을 찾는 과정을 해 찾기 기능으로 확인할 수 있다. 
각 데이터의 잔차를 확인해서 잔차 제곱합을 계산하기 위해서는 가상의 수식이 필요하다. 

가상의 수식이 필요한 이유는
선형의 회귀분석에서는 쉽게 수식으로 잔차가 최소화된 수식의 상수를 얻을 수 있었지만, 
비선형의 곡선은 상수값이 많기 때문에 수식으로 확인하기 어렵다.
그래서 가상의 상수값에서 최적의 값을 찾아가는 알고리즘을 적용해서 상수값을 찾아내는 것이 해 찾기 기능이다.

가상의 수식에 오차가 큰 상수값을 입력하면 알고리즘에서 오류가 발생되는 문제점이 나타난다.
임의의 상수값은 데이터를 기반으로 대략적인 값을 추정할 수 있고, 
데이터에서 가장 낮은 y 값을 상수 a 로 설정하고, 가장 높은 y 값을 상수 d로 설정한다. 
기울기 상수 b는 전체 데이터를 이용해서 대략적인 기울기 값을 계산하고, 
마지막으로 기울기가 가장 큰 부분의 x 값을 상수 c로 설정한다.
이와 같은 방법으로 확인된 임의의 상수값을 적용한 수식은 다음과 같다. 

$$ y=9.9+ \dfrac{1-9.9}{1+\left( \dfrac{x}{1} \right)^{5.77}}$$

위 수식에 데이터  x 값을 적용해서 예측값 Y (Predicted Y) 값을 계산한다.
잔차 (Residual)는 " Predicted Y - Y (Response)"으로 y 값의 차이를 나타낸다.  

X (Concentration)	Y (Response)	Predicted Y	Residuals
0.2	1.001	1.000824694	0.00017531
0.4	1.037	1.044780145	-0.0077801
0.8	2.869	2.924800971	-0.055801
1.2	7.742	7.596305161	0.14569484
1.6	9.494	9.345765115	0.14823488
2	9.862	9.739837701	0.1221623

각 x 값의 잔차를 제곱해서 합한 값이 잔차 제곱합 (Sum of squares Error: SSE)으로 
위 테이블에서 계산된 SSE는 0.061298504 이다. 
SSE 값을 해찾기로 최소가 되도록 상수 4개의 값을 조정할 것이다. 

활성화된 해 찾기 메뉴를 클릭하면 다음과 같은 화면이 나타난다. 

해 찾기 설정창

앞에서 계산한 SSE 값이 계산되는 셀을 "목표설정" 항목에 입력하고, 아래 "최소"를 선택한다. 
목표설정된 셀이 최소가 되도록 변수 셀의 값을 변경하는 것이다. 
변수에 해당하는 상수 값이 입력된 셀을 변수 셀 변경 항목에 입력하고, 해법 선택에 "GRG 비선형"을 선택한다. 
SSE 셀 값은 변수 셀들이 변경되면 자동 계산될 수 있도록 수식이 적용되어 있어야 한다. 
모든 준비가 완료되면 화면 아래 "해 찾기" 버튼을 눌러주면 
목표설정 값이 최소가 되도록 변수 셀 값들이 변경된다. 

해 찾기 실행 과정

 해 찾기로 최적의 상수값을 비교해 보면 다음과 같다. 

상수	참값	초기 설정값	최적값
a	1	1	1.0004
6	10	9.9	10.0004
b	6	5.77	6.0003
c	1	1	1.0001

참값은 데이터를 만들 때 사용한 수식의 상수값이고, 초기 설정값은 최적화 이전에 임의의 설정값이다. 
최적값은 해 찾기를 통해서 확인된 전체 잔차값이 최소가 되도록 만들어진 값이다. 
해 찾기로 확인된 최적값은 참값과 거의 차이가 없는 것이 확인된다. 

초기 설정값이 참값과 큰 차이가 있는 경우에는 
최적값이 참값과 차이가 나타나는 것이 확인된다. 
이런 경우는 동일한 해 찾기 과정을 여러번 반복하면 참값에 가까워지는 것을 확인할 수 있었다. 
하지만, 초기 설정값과 참값의 차이가 어느 한계를 벗어나면 최적값이 계산되지 않는 현상도 나타난다. 
위와 같은 다양한 오류 내용들은 엑셀 사용에 단점으로 생각된다. 

보고된 실험 데이터와 4-parameter equation 결과를 엑셀 해 찾기 결과와 비교하였다.
아래 참고 자료에서 확인된 실험 결과를 이용해서 엑셀 해 찾기 결과를 확인하였다.
해당 실험 결과는 각 농도별 2회 반복 측정한 결과로 측정 재현성이 낮은 실험에서 사용하는 방법이다.
ELISA 분석법에서 자주 볼수 있는 형식이다. 
반복 측정의 데이터를 평균해서 단일 y 값으로 변형해서 해 찾기를 수행했다. 

Concentration (X)	Response (Y)	Y Mean / RSD%
0.1	2.912	2.915 / 0.12%
	2.917
0.05	2.579	2.617 / 2.03%
	2.654
0.025	2.13	2.171 / 2.67%
	2.212
0.0125	1.651	1.645 / 0.56%
	1.638
0.00625	1.073	1.023 / 6.91%
	0.973
0.003125	0.585	0.626 / 9.16%
	0.666
0.0015625	0.463	0.410 / 18.5%
	0.356
0.00078125	0.266	0.250 / 9.05%
	0.234
0.000390625	0.228	0.213 / 10.3%
	0.197
0.000195313	0.176	0.196 / 14.1%
	0.215

참고 : Europ. Pharm., 5th Ed. (2005), Ch. 5.3, 5.4.1.a - Four-parameter logistic curve analysis, A serological assay of tetanus sera. Document-3567 (O130_Permanent_PLA_307)

해 찾기 기능을 수행하기 위해서 앞서 설명드린 것처럼 잔차와 잔차 제곱합을 계산하고,
초기값을 임의로 설정해서 상수 값을 대입한 후에 해 찾기 기능을 적용해서 최적의 상수값을 확인하였다. 
초기값이 최적값 보다 차이가 있었기에 동일한 해 찾기 기능을 2회 이상 수행하였더니 
더 이상 최적값이 변경되지 않는 것을 확인하였다. 
마지막으로 변경되지 않은 값을 최적값으로 결정하였다. 
아래 표는 엑셀 해 찾기로 확인 최적의 상수값과 다른 소프트웨어로 확인한 결과들을 비교하였다. 
참고 자료에 있는 실험 내용은 "PLA3.0" 소프트웨어를 사용해서 얻은 결과이고, 
결과 차이를 확인하기 위해서 다른 소프트웨어 "Myassays"도 함께 비교하였다.
Myassays는 웹에서 로그인 후에 설치 없이 웹에서 무료로 사용 가능한 소프트웨어이다.    

Constant	Excel	PLA 3.0	Myassays
a	0.16194	0.14546	0.1689
d	3.16754	3.19599	3.09
b	1.15812	1.12452	1.197
c	0.01336	0.01348	0.01271
$R^2$	0.99983	0.99902	0.9983

비교한 결과를 보면 엑셀로 확인된 최적의 상수 값은 다소 차이가 있는 것이 확인되지만, 
결정계수 값도 큰 차이가 없는 것으로 보아 농도 계산에 큰 영향을 미칠 정도의 차이는 없는 것으로 확인된다. 

엑셀로 4-parameter equation 을 찾는 방법은 여러 단계를 거쳐서 결과를 봐야하는 불편함이 있지만, 
결과 신뢰도 측면에서는 나쁘지 않은 결과를 보여주었다. 
엑셀 양식을 만들어 사용한다면 별도의 소프트웨어 없이도 계산이 가능한 것이 확인되었다. 
4-parameter equation 계산을 위한 엑셀 파일을 첨부합니다. 
자세한 계산 내용을 참고하세요. 

Regression_4 parameter equation_V1.xlsx

0.04MB