금액 범위에 따라 단가 산식이 달라지는 경우

안녕하세요
조건에 따른 결과값을 구하는 문제인데 질문에 대하여 엑셀파일을 올렸습니다
항상 직접 계산을 하다보니 자동으로 산출되게 하고 싶어서 질문 글 올립질문을 요약하면, 총가격 범위에 따라 분배할 결과값을 계산하는 산식이 다를 때 

어떻게 함수를 구성해야 하는가 입니다.

 

총가격 범위에 따른 산식은 아래와 같아요

분배결과값을 하나의 함수로 사용하기위해서

위 수식을 천천히 살펴보니, 하나의 수식으로 나타낼수 있게 되었어요

위 표에서 총가격 구간을 범위, 10만원, 144천원 등 식의 제일 앞에 있는 금액을 기초값, 11, 10 등 1만으로 나누는 숫자를 비율이라고 하고

[기초값+(총가격-범위)*비율/10000]*70%

만약 총가격이 1천만원 이하인 경우 기초값은 10만, 비율은 0으로 하면 기초값(10만) *70%가 될것이고

1천만원 초과인 경우 기초값은 10만, 비율은 11이되겠죠..

그래서 일단 범위와 기초값, 비율을 별도로 작성했구요. 총가격에 따라 해당 행번호를 찾아 범위값, 기초값, 비율을 가져와서 위의 개념적 수식에 대입하도록 작성하려고 해요

 

우선 범위값, 기초값, 적용비율을 가져오기 위해서 아래와 같이 별도의 표를 작성했구요

위 표를 작성할 때 입력된 총금액은 5억 이에요..

그러니까. 적용할 범위값, 기초값, 적용비율은 8행이 되겠지요

만약 1천만원이 안되면 4행이 될 것이구요

이 행번호는 I1셀에 자동으로 찾도록 함수를 작성했어요

작성한 함수는 =MATCH(D4,H1:H18,1)   여기서 D4는총금액이에요

match 함수는 "배열에서 지정된 순서상의 지정된 값에 일치하는 항목의 상대 위치 값을 찾습니다" 라고 설명하고 있는데 지정된 값(D4)가 배열에서 몇번째에 있는지를 반환하는 함수이고,

상대 위치값이라는 것은 배열로 지정한 첫번째 셀부터 몇번째에 있는지를 반환한다는 뜻이에요

 

아무튼, 1행부터 지정했으니까...찾는 위치는 행번호와 동일하게 되겠지요

 

이제는 위에서 적은 개념적 수식을 실제 수식으로 바꿔야 겠죠..

=[I8 + (D4 - H8 + 1)*8/10000]*70%

가 될텐데...총금액이 바뀌면 행번호(위 수식에서는 8행)이 바뀌어야 겠네요

그래서 

여기서 사용할 함수는 INDIRECT함수에요

"엑셀에서는 텍스트 문자열로 지정한 셀 주소를 돌려줍니다." 라고 설명하고 있는데...

이게 좀 개념잡기가 쉽지 않더라구요..

 

아무튼 위 식을 행번호가 자동으로 바뀌도록 INDIRECT함수로 쓰면

=(INDIRECT("I"&I1)+(D4-INDIRECT("H"&I1)+1)*INDIRECT("J"&I1)/10000)*70%

이렇게 되요..I는 고정이고, I1에는총금액에 해당하는 범위를 찾은 행번호가 들어있죠..

그래서 위에 INDIRECT함수를 사용한 수식과 

그 위에 I8로 시작하는 수식은 같은 결과를 가져오게 됩니다.

만약 총금액이 바뀌어서 I1에 다른 행번호가 들어간다고 하면

위 수식에서 I1값이 바뀌게 되니까, 자동으로 해당 행을 찾아서 계산하게 될거에요

 

해당 파일 첨부하니, 참고하세요~

엑셀 누진료 질문 .xlsx

0.01MB