내림차순 보간법 (420 in 431~397)

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=GVDs7MJ5WN23iSd86SbqNMyNZsS5yxkM

 

	A	B	C	D	E	F	G	H	I	J	K
1	x	0.10	0.15	0.20	0.25		답	0.22		0.20	0.25
2	y	500	483	431	397		기준	420	3	431	397

 

데이타가 B1:E2에 있을 때,

 

I2 =MATCH(H2,B2:E2,-1) ; 이 수식의 핵심. 옵션 -1을 눈여겨 보세요. 

J1 =INDEX($B1:$E1,1,$I$2)

K1 =INDEX($B1:$E1,1,$I$2+1)

H1 =J1+(J1-K1)*(H2-J2)/(J2-K2)

 

J1:K1을 복사, J2:K2에 붙여 넣으면 표가 완성됩니다. I2 수식에서 보듯 MATCH함수의 -1옵션은 내림차순으로 정렬된 데이타 범위에서 '그 값 보다 작지 않은 최소값'의 위치를 돌려줍니다. (420을 찾으면 431의 위치인 3을 돌려 줌) 예시의 수식을 모두 H1에 밀어 넣어 한 셀로 표현할 수도 있습니다만 복잡해져서 추천하고 싶지 않습니다. 만일 데이타가 내림차순, 오름차순이 혼재해 있을 수 있다면 B2, E2를 비교하여 MATCH함수의 옵션을 조정할 수 있습니다. (I2 수식의 -1 자리에 SIGN(E2-B2) 를 넣으면 됨)

 

정히 한 셀에 넣고 싶다면,

 

	A	B	C	D	E	F	G	H
1	x	0.10	0.15	0.20	0.25		420	기준
2	y1	500	483	431	397		0.216	답1
3	y2	543	470	389	312		0.181	답2

 

G2 =INDEX(B$1:E$1,1,MATCH(G$1,B2:E2,-1))+(INDEX(B$1:E$1,1,MATCH(G$1,B2:E2,-1))-INDEX(B$1:E$1,1,MATCH(G$1,B2:E2,-1)+1))*(G$1-INDEX(B2:E2,1,MATCH(G$1,B2:E2,-1)))/(INDEX(B2:E2,1,MATCH(G$1,B2:E2,-1))-INDEX(B2:E2,1,MATCH(G$1,B2:E2,-1)+1))

로 입력, G2 잡아 아래로 복사하시면 됩니다. 무쟈게 복잡해 보이는데, 이 수식은 처음의 H1에 I2, J1:K2를 밀어 넣어 정리한 것에 불과합니다. 합치는 건 그럭저럭 하겠지만 나누거나 고치려면 엄두가 안 날 겁니다. 그래서 이럴 때는

 

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	x	0.10	0.15	0.20	0.25		기준	420
2	y1	500	483	431	397		답1	0.216	3	0.2	0.25	431	397
3	y2	543	470	389	312		답2	0.181	2	0.15	0.2	470	389

 

I2 =MATCH(H$1,B2:E2,-1)
J2 =INDEX(B$1:E$1,1,I2)
K2 =INDEX(B$1:E$1,1,I2+1)
L2 =INDEX(B2:E2,1,I2)
M2 =INDEX(B2:E2,1,I2+1)
H2 =J2+(J2-K2)*(H$1-L2)/(L2-M2)

H2:M2 잡아 아래로 데이타 행 만큼 드래그 복사하시면 표가 완성됩니다. 이런 식으로 보조열을 한 행에 다 쓰는 편이 나중 유지보수를 위해 바람직합니다. (결과는 같이 나옵니다.)