거래처가 늘어나면 매달 청구한 금액과 실제 입금된 금액을 확인하는 일이 복잡해집니다.
특히 한 거래처가 여러 번 나누어 입금하거나 지급기한이 지나도 돈이 들어오지 않으면 미수금 관리가 어려워집니다.
예를 들어 거래처에 250만 원을 청구했는데 100만 원과 50만 원이 따로 입금되었다면 아직 받아야 할 금액은 얼마일까요?
정답은 100만 원입니다.
이번 글에서는 엑셀 SUMIF 함수와 TODAY 함수를 활용해 이러한 계산을 자동으로 처리하는 거래처 미수금 관리표를 만들어 보겠습니다.
1. 미수금 관리가 어려워지는 이유
미수금은 상품이나 서비스를 제공했지만 아직 받지 못한 금액을 의미합니다.
간단한 거래라면 청구액에서 입금액을 빼면 됩니다.
하지만 거래처마다 입금일이 다르고 한 건의 청구에 여러 번 입금되는 경우에는 단순 계산만으로 관리하기 어렵습니다.
그래서 이번 실습 파일은 다음 두 가지 자료를 분리했습니다.
- 청구 내역: 거래처명, 청구번호, 청구액, 입금기한
- 입금 내역: 입금일, 청구번호, 입금액
두 자료를 청구번호로 연결하면 입금이 여러 번 발생해도 자동으로 합산할 수 있습니다.
2. 실제 미수금 계산 사례
다음은 학습을 위한 가상 거래 자료입니다.
| 거래처 | 청구액 | 입금액 | 미수금 |
|---|---|---|---|
| 한빛상사 | 2,500,000 | 1,500,000 | 1,000,000 |
| 미래유통 | 1,800,000 | 1,800,000 | 0 |
| 새봄기획 | 1,200,000 | 300,000 | 900,000 |
| 다온테크 | 3,200,000 | 1,500,000 | 1,700,000 |
이 표만 보면 미수금 계산이 간단해 보입니다.
하지만 한빛상사가 100만 원과 50만 원을 다른 날짜에 입금했다면 먼저 두 금액을 합산해야 합니다.
이 작업을 처리하는 함수가 SUMIF입니다.
3. SUMIF 함수로 입금액 자동 합산하기
이번 파일의 '입금내역' 시트에는 B열에 청구번호, D열에 입금액이 있습니다.
'미수금 관리표' 시트의 A9 셀에는 청구번호가 입력되어 있습니다.
F9 셀에 다음 수식을 입력합니다.
=SUMIF('입금내역'!$B$6:$B$105,A9,'입금내역'!$D$6:$D$105)
이 수식은 입금내역에서 A9와 같은 청구번호를 찾아 해당 입금액을 모두 합산합니다.
예를 들어 INV-001에 100만 원과 50만 원이 입금되었다면 결과는 150만 원입니다.
청구번호가 같으면 입금이 여러 번 발생해도 자동으로 더해집니다.
실무 포인트: 거래처명보다는 청구번호를 기준으로 계산하는 것이 안전합니다. 같은 거래처에 여러 건을 청구할 수도 있기 때문입니다.
4. 아직 받지 못한 미수금 계산하기
이제 청구액에서 입금 합계를 빼겠습니다.
E9 셀에는 청구액, F9 셀에는 입금 합계가 있습니다.
G9 셀에 다음 수식을 입력합니다.
=MAX(0,E9-F9)
MAX 함수는 계산 결과가 음수일 경우 0을 표시하도록 합니다.
청구액이 250만 원이고 입금 합계가 150만 원이라면 미수금은 100만 원입니다.
다만 실제 입금액이 청구액보다 많다면 초과 입금이 발생한 것이므로 별도로 확인해야 합니다. 이 수식은 초과 입금액을 표시하지 않습니다.
5. 입금기한이 지난 거래처 자동 확인
미수금 관리에서는 금액뿐 아니라 지급기한도 중요합니다.
이번에는 TODAY 함수와 IF 함수를 이용하겠습니다.
H9 셀에 다음 수식을 입력합니다.
=IF(A9="","",IF(G9=0,"입금완료",IF(D9<TODAY(),"입금지연","입금대기")))
수식의 판단 순서는 다음과 같습니다.
- 청구번호가 없으면 빈칸 표시
- 미수금이 0원이면 '입금완료'
- 미수금이 남아 있고 입금기한이 지났으면 '입금지연'
- 나머지는 '입금대기'
TODAY 함수는 현재 날짜를 기준으로 계산하기 때문에 시간이 지나면 입금 상태도 변경될 수 있습니다.
이번 실습 파일에서는 입금지연과 입금완료 상태를 색상으로 구분해 쉽게 확인할 수 있도록 했습니다.
6. 미수금 전체 현황 한눈에 확인하기
거래처별 내역을 계산했다면 전체 미수금도 확인해야 합니다.
총 미수금은 다음 수식으로 계산합니다.
=SUM(G9:G28)
지급기한이 지난 미수금만 합산하려면 다음 수식을 사용합니다.
=SUMIF(H9:H28,"입금지연",G9:G28)
이렇게 하면 전체 미수금과 기한이 지난 미수금을 구분해서 확인할 수 있습니다.
특히 월말 결산이나 거래처 입금 확인 업무에 유용합니다.
7. 실무에서 주의할 사항
미수금 관리표를 사용할 때는 세 가지를 확인해야 합니다.
첫째, 청구번호를 중복 발급하지 않아야 합니다.
청구번호가 중복되면 다른 거래의 입금액까지 합산될 수 있습니다.
둘째, 입금내역에 정확한 청구번호를 입력해야 합니다.
번호가 틀리면 실제 입금된 금액도 미수금 계산에 반영되지 않습니다.
셋째, 초과 입금과 환불 내역은 별도로 검토해야 합니다.
이번 파일은 기본적인 미수금 계산을 배우기 위한 실습용입니다. 실제 회계 업무에서는 취소, 수정 세금계산서, 선수금 등도 함께 확인해야 합니다.
8. 거래처 미수금 자동 관리표 무료 다운로드
직접 연습할 수 있도록 엑셀 파일을 준비했습니다.
파일에는 청구 내역과 입금 내역을 입력하는 시트, 자동 계산 결과, 함수 사용 안내가 포함되어 있습니다.
청구액과 입금액을 직접 변경하면서 미수금이 어떻게 바뀌는지 확인해 보세요.
첨부 파일: GS10_거래처_미수금_자동관리표.xlsx
※ 실제 회계장부나 세금 신고 자료에 사용하기 전에는 별도의 검증이 필요합니다.
마무리
이번에는 SUMIF, IF, TODAY 함수를 조합해 거래처 미수금 관리표를 만들어 보았습니다.
중요한 것은 함수 하나를 외우는 것이 아니라 여러 함수를 연결해 실제 업무 문제를 해결하는 것입니다.
청구 내역과 입금 내역을 분리하고 청구번호로 연결하는 구조만 이해해도 반복적인 확인 작업을 상당 부분 줄일 수 있습니다.
다음 글에서는 직원별 연차 사용 내역과 잔여 연차를 계산하는 연차·휴가 관리표 만들기를 다루겠습니다.
'엑셀 실무 마스터' 카테고리의 다른 글
| 직원 연차·휴가 관리표 만들기 | 엑셀 SUMIF·IF 함수 실무 예제 (0) | 2026.10.30 |
|---|---|
| 엑셀 FILTER 함수 사용법｜조건에 맞는 데이터만 자동으로 추출하는 방법 (2026) (0) | 2026.10.30 |
| 엑셀 SEQUENCE 함수 사용법｜일련번호·자동 번호 매기기·날짜 생성 총정리 (2026) (0) | 2026.10.29 |
| 직원 급여 자동 계산표 만들기 | 엑셀 SUM·IF 함수 실무 예제 (0) | 2026.10.28 |
| 엑셀 순위 매기기｜RANK.EQ 함수로 직원별 매출 순위 자동 계산 (0) | 2026.10.28 |
