구간별 다른 변수 적용 계산

	B	C	D	E	F	G	H	I	J
2	값	결과		구간	-1000	100	200	300	400
3	50	125	1	곱하기	0.5	0.5	1.5	2	2.7
4	120	380	2	더하기	100	100	200	300	400
5	250	800	3
6	300	1210	4
7	360	1372	4

 

C3 =B3*INDEX(F$3:J$4,1,MATCH(B3,F$2:J$2,1)+1)+INDEX(F$3:J$4,2,MATCH(B3,F$2:J$2,1)+1)

D3 =MATCH(B3,F$2:J$2,1)

 

MATCH함수는 범위 중 찾는 데이타가 몇 번째 위치하는지 알려줍니다. 찾는 방식을 -1, 0, 1로 지정할 수 있는데, 0은 얄짤없이 100% 정확한 것을 찾고 1은 오름차순으로 근사값을 찾습니다. 위 예시의 D열은 B열의 값을 F2:J2에서 근사값으로 찾은 결과입니다. (이 때 F2값이 없으면 (100보다 작은) 50을 찾을 때 에러가 납니다.) 이 위치를 INDEX를 걸어 곱하기, 더하기 따위 구간에 해당하는 상수를 가져와 계산하면 원하는 효과를 얻으실 수 있습니다. 다만 구간 경계를 어떻게 포함할 것인가는 잘 고려하셔야겠지요. 그게 번거로우면 SUMPRODUCT함수로도 조건을 면밀히 걸 수 있어 추천할만 합니다만, 수식은 예시보다 훨씬 길어질 겁니다.