INT와 SUMPRODUCT로 지폐·동전 단위별 잔돈 개수 계산하기

질문: 손으로 세지 않고 단위별로 잔돈을 집계할 수 없을까요?

엑셀에 이런 기능이 딱 있는 것은 아니지만 몇 가지 내장 함수를 조합하면 충분히 만드실 수가 있습니다(왠지 만들 수 있어야만 할 것 같은 느낌이 드시지요? ^^).

아래의 금액란(노란색 셀)에 임의의 금액을 입력해 보세요.

금액

667866

단위별로 필요한 개수는 다음과 같이 계산합니다. 첫 번째 단위(10000)는 금액을 그대로 단위로 나눈 정수 부분이고, 두 번째 단위(5000)부터는 앞서 배정된 금액을 SUMPRODUCT로 구해 빼준 나머지를 다시 그 단위로 나눕니다.

10000	5000	1000	500	100	50	10	5	1
=INT(C33/B35)	=INT(($C$33-SUMPRODUCT($B$35:B$35,$B$36:B$36))/C$35)	=INT(($C$33-SUMPRODUCT($B$35:C$35,$B$36:C$36))/D$35)	=INT(($C$33-SUMPRODUCT($B$35:D$35,$B$36:D$36))/E$35)	=INT(($C$33-SUMPRODUCT($B$35:E$35,$B$36:E$36))/F$35)	=INT(($C$33-SUMPRODUCT($B$35:F$35,$B$36:F$36))/G$35)	=INT(($C$33-SUMPRODUCT($B$35:G$35,$B$36:G$36))/H$35)	=INT(($C$33-SUMPRODUCT($B$35:H$35,$B$36:H$36))/I$35)	=INT(($C$33-SUMPRODUCT($B$35:I$35,$B$36:I$36))/J$35)

같은 원리를 세로 방향으로 정리하면 아래와 같습니다.

단위	수식
10000	=INT(C29/B31)
5000	=INT(($C$29-SUMPRODUCT($B$31:B$31,$B$32:B$32))/C$31)
1000	=INT(($C$29-SUMPRODUCT($B$31:C$31,$B$32:C$32))/D$31)
500	=INT(($C$29-SUMPRODUCT($B$31:D$31,$B$32:D$32))/E$31)
100	=INT(($C$29-SUMPRODUCT($B$31:E$31,$B$32:E$32))/F$31)
50	=INT(($C$29-SUMPRODUCT($B$31:F$31,$B$32:F$32))/G$31)
10	=INT(($C$29-SUMPRODUCT($B$31:G$31,$B$32:G$32))/H$31)
5	=INT(($C$29-SUMPRODUCT($B$31:H$31,$B$32:H$32))/I$31)
1	=INT(($C$29-SUMPRODUCT($B$31:I$31,$B$32:I$32))/J$31)

언제나 그렇듯이 함수의 정의만 알아서는 실무에 적용이 어렵습니다. SUMPRODUCT 함수의 경우 "주어진 배열에서 해당 요소들을 모두 곱하고 그 곱의 합계를 구한다"라고만 외우고 있어서는 안 되는 것이지요. 지난 시간과 마찬가지로 SUMPRODUCT 함수를 이용하였는데, 얼핏 보면 복잡해 보이지만 찬찬히 들여다보시면 어떤 규칙을 발견하실 수 있을 것입니다.

그런 의미에서 수식에 대한 자세한 해설은 아래 정리 표로 대신합니다. 어느덧 8월의 끝자락입니다. 마무리는 잘하고 계신가요? ^^

정리 — INT와 SUMPRODUCT로 단위별 잔돈 계산하기

구성요소	역할
INT(금액/단위)	금액을 해당 단위로 나눈 몫(정수)만 취해 필요한 개수를 구함
SUMPRODUCT(단위범위,개수범위)	이미 앞에서 배정된 상위 단위들의 합계 금액을 계산
금액 - SUMPRODUCT 결과	상위 단위로 이미 처리된 금액을 뺀 나머지(잔여 금액)
단위 확장	큰 단위부터 작은 단위까지 같은 패턴을 반복해 전체 구성 완성

자주 묻는 질문 (FAQ)

마치며

단위별 잔돈 계산은 급여 지급, 현금 시재 관리 등 실무에서 자주 마주치는 문제입니다. INT와 SUMPRODUCT의 조합 패턴 한 번만 제대로 이해해 두면, 단위 개수가 몇 개든 똑같은 구조로 확장해서 사용할 수 있습니다.