Module 8. 해찾기 기능 활용 - (3)비선형 함수 Fitting

해찾기를 이용해 단순한 수학문제 풀기 이외에도 목표값만 정확하게 설정하면, 함수 fitting도 가능해집니다.

예전 글에서는 선형 또는 다항식 fitting 문제에서는 데이터의 거듭제곱값들을 구하고, 행렬을 만든 후 다항식의 계수를 계산해냈습니다. 다항식의 경우에는 1개의 유일한 fitting 결과가 가능하지만, 비선형 함수는 함수들마다 fitting하는 알고리즘이 달라집니다. 그럼에도 불구하고, 오차 제곱합을 최소화하는 것은 동일합니다.

비선형 함수의 대표적인 사례로 Peak fitting을 예로 들어보겠습니다. 사실 이 부분을 이해하신다면, 직선이나 다항식, 기타 함수로의 fitting은 쉽게 해결할 수 있습니다. Peak fitting으로 사용되는 함수 중 Gaussian 함수가 있습니다. Gaussian 함수는 A*exp(-((x-μ)/σ)^2) 형태입니다. 

아래 그림과 같이 2개의 Gaussian peak과 상수값의 합으로 되어있는 (x, y) 데이터가 있다고 할 때, 상수, y1_fit, y2_fit으로 나눠서 함수를 만들어 줄 수 있고, 각각의 상수들은 별도의 목록으로 오른쪽 셀에 나타내었습니다. y1_fit 함수는 아래 그림처럼 입력해두고, y2_fit과 상수항도 동일하게 둘 수 있습니다.

마지막으로 상수와 2개의 Gaussian 함수의 합을 y_sum열에 입력해둡니다.

Fitting을 위해서는 실측 데이터 y와 계산된 데이터 y_sum의 차가 최소가 되도록 해야하므로, 상수 목록 맨 하단에 Err 항을 만들고, '=sumxmy2(y 범위, y_sum 범위)'를 입력해줍니다. sumxmy2 함수는 해찾기로 함수 fitting 할 때 많이 사용하게 될 함수이며, 두 범위의 값에 대하여 각 셀값의 차이의 제곱합을 구하라는 의미입니다. (=∑(x-y)^2)

끝으로 각 상수들의 초기값을 적절하게 입력하여 peak의 모양을 대략적으로 잡아줍니다.

Err 값을 최소화하기 위해 아래와 같이 오차 제곱합을 목표셀로 지정하고, 변경할 상수 목록을 변경 셀로 지정한 후 해찾기를 실행합니다.

해찾기를 완료하면 아래 그림과 같이 측정 데이터와 fitting된 함수가 거의 일치하게 되며, 2개의 Gaussian으로 peak가 분리되었습니다.

비선형 함수에 대해 해찾기를 실행하게 되면, 반복계산 횟수를 초과하더라도 결과가 완전히 수렴하지 않을 수 있습니다. 목표 셀의 숫자가 더이상 변하지 않을 때까지 해찾기를 반복해서 실행하면 다른 공학 소프트웨어로 계산한 결과와 동일한 결과를 얻을 수 있습니다.

해찾기 결과가 완전히 수렴하도록 하려면, 해찾기 옵션에서 반복 횟수를 충분히 크게 해줄 수도 있습니다. 반복횟수가 100번이 기본값이지만, 만약 1000번으로 바꿨다해도 중간에 값이 수렴하면 엑셀이 반복 계산을 중단하기 때문에 시간 낭비는 발생하지 않습니다.

엑셀에 이렇게 훌륭한 계산 툴이 있음에도 불구하고, 해찾기의 단점은.. 목표셀, 변경셀, 조건식 등을 일일이 수동으로 지정해야합니다. 만약 위와 같은 fitting을 수십개의 데이터에 대해 작업하고자 한다면, 매우 귀찮은 일일 수 있습니다. 만약 데이터 배열이 반복되어 있고, 동일한 포맷으로 해찾기를 반복할 수 있다면, 공학적 계산용으로 활용도가 충분히 높아질 수 있을 것입니다.

반복 해찾기가 가능하다면 할 수 있는 일에 대해서는 다음 글에 소개하도록 하겠습니다.