단가 크기별로 절사자리 수 달리하는 수식

실행내역 또는 하도급 내역서 등 내역서를 작성하다보면 여러가지 여건으로 도급단가나 업체에서 견적해온 단가를 참조(도급단가에 몇%)하여 내역서를 작성해야 할 경우가 있습니다.

 

많은 분들이 ROUND함수나 TRUNC함수를 사용하여 자리수를 10원, 100원 또는 1,000원 단위에서 절사하는 방법을 사용할 것입니다. 

 

그런데 금액의 크기에 따라 절사하는 자리수를 달리하고 싶은 경우

많은 분들이  IF함수를 사용합니다.

 

=IF(단가*비율>=1000,TRUNC(단가*비율,-2),TRUNC(단가*비율,0))

 

위와 같이 IF함수를 사용해서 1,000원 이상인 경우는 100원 단위에서 절사를 하고 아닌 경우는 원단위 미만에서 절사를 하는 방법을 사용합니다.

 

어떤 분은 1,000원 이상일때는 10원 단위에서 10,000원 이상일때는 100원 단위에서 100,000원 이상일때는  1,000원 단위에서 절사를 하고 싶어하는 경우가 있습니다. 이럴때 IF함수를 사용하면 함수가 아주 길어지는데 이를 간단히 하는 함수를 알려 드리겠습니다.

 

=TRUNC(단가*비율,3-(LEN(INT(ABS(단가*비율))))

 

수식에 대해 설명 드리자면

1) 100,000원 이상인 경우 LEN(100000)의 결과는 6이 됩니다.

2) 그래서 절사할 자리수가 "3-6"이 되어 -3 이되고 1000원 단위 미만은 절사가 됩니다.

3) INT함수를 사용하는 이유 : LEN 함수를 사용하여 금액의 크기를 판단하려고 하는데 소수점까지 나오면 곤란하겠죠?

4) ABS함수를 사용하는 이유 : 만약 (-)단가인 경우는 (-)때문에 자리수가 하나더 늘어납니다. 그래서 절대값으로 (-)를 제거 해주는 기능을 합니다.

 

첨부파일에 일반함수, LEN함수, IF함수, 배열함수, 사용자정의 함수를 비교해 보았습니다. 참고하면 더욱 도움이 되시리라 생각이 듭니다.

 

함수비교 첨부파일 : truncif.xls

 

아래 그림을 참조바라며(ABS함수는 빼먹었네요^^)

 

 

 

도움이 되시길...

 

사용함수 : TRUNC, LEN, INT, ABS