보간법으로 값 추정하기

질문 하나

질문 : 보간법에 관한 질문입니다.

표1

표 1

단면적	계수
143	0.74
98	0.92
75	1.09
58	1.31
42	1.68

표1과 같은 테이블이 있습니다.

이 때 주어지지 않은 단면적(예, 82)일 때의 계수값을 구하는 방법을 알고 싶습니다.

<그림1> 단면적 대 계수

그림1과 같은 그래프를 작성하여 단면적 82에 해당하는 계수값을 눈으로

읽을 수도 있겠지만 매번 이 작업을 하려면 엑셀의 의미가 없는듯 하고

다른 방법으로 추세선을 만들어 82에 해당하는 값을 구하는 것을 생각해 봤는데

추세선의 종류(다항식, 지수, 로그…) 선택을 매번 바꿔주어야 하는 것도 문제고

그 추세선 식을 눈으로 확인하여 매번 답을 구하는 것도 문제입니다.

표를 선형(직선)으로 간주하여 간략히 계산하는 방법도 생각해 봤는데

표2

표 2

단면적	계수
143	0.74
98	0.92
75	1.09
58	1.31
42	1.68
82	=IF(B64>B59,C59,IF(B64>B60,C59+(C60-C59)/(B60-B59)*(B64-B59),IF(B64>B61,C60+(C61-C60)/(B61-B60)*(B64-B60),"아!!복잡해")))

표2와 같이 if문을 사용하여

IF(B50>B45,C45,IF(B50>B46,C45+(C46-C45)/(B46-B45)*(B50-B45),IF(B50>B47,C46+(C47-C46)/(B47-B46)*(B50-B46),…

이런 식으로 구할 수 도 있겠지만

이건 답의 오차(1.051과 1.038)는 무시하더라도

테이블이 길어지면 식만들기도 복잡하고 웬지 엑셀스럽지 못해서…

뭔가 좋은 방법이 없을까요?

단순히 선형으로만 간주하여 단면적을 입력하면

입력값보다 바로 윗단계의 큰값(예에서는 98)과 바로 아래단계의 작은값(75)를 읽어 즉, (98, 0.92)와 (75, 1.09)을 읽어

직선으로 간주하여 단면적 82에 해당하는 계수값을 구하는 방법

즉, IF(B50>B45,C45,IF(B50>B46,C45+(C46-C45)/(B46-B45)*(B50-B45),IF(B50>B47,C46+(C47-C46)/(B47-B46)*(B50-B46),…

을 수정한 엑셀스러운 개선된 방법이라도 알고 싶습니다.

고수님들의 지도 부탁드립니다.

보간법으로 해결하기

보간법(補間法, interpolation)이란, 두 개의 치수 사이에 있는 특정 위치에 있는 값을

찾아내는 방법입니다. 실험이나 관측에 의하여 얻은 관측값으로부터 관측하지 않은

점에서의 값을 추정하는 경우 등에 사용할 수 있습니다.

예를 들어, 자동차 견인비용을 계산할 때, 10km는 100,000원, 30km는 300,000원이라면,

20km를 견인할 경우 견인비용은,

=(300000-100000)*(10/20)+100000

이렇게 계산할 수 있을 것입니다.

문제는, 이런 원리를 이용해서 어떻게 엑셀에서 쉽게 해결할 수 없겠느냐

하는 것입니다.

보간법 계산용 데이터

배열 수식으로 계산하기

추세선의 형태에 따라 결과는 달라질 수 있겠습니다만, 일단 추세선이 직선 형태인

경우, 결과값은 위와 같이 나오는군요. 빨간색 셀에는 다음과 같은 수식이 들어 있습니다.

{=(INDEX(계수,MATCH(MIN(IF(단면적>=F101,단면적)),단면적,0))

아시겠지만 위 수식은 배열 수식으로, 수식 앞뒤의 {}는 Ctrl + Shift + Enter 키를 함께

누르면 자동으로 생깁니다.

수식은 무지하게 길고 복잡합니다만, 앞서 설명드린 자동차 견인비용 산출 방법을

잘 떠올려 보시면 이해하실 수 있을 것입니다. 여기서 계수, 단면적 등은

B98:B102, C98:C102 영역에 사전에 이름을 정의해 둔 것입니다. 만약 단면적이

이것 말고 더 추가가 될 수 있다면 그동안 수도 없이 설명드린 '동적 이름 정의'

방법을 이용하시면 되겠지요?

다음 시간에…

마치며

보간법의 기본 원리를 살펴보고, 두 값 사이를 직선으로 가정하여 원하는 값을 계산하는 방법을 배열 수식으로 구현해 보았습니다.