엑셀 미수금 관리대장 만드는 법｜부분입금·미입금 구분

엑셀 미수금 관리대장은 청구액만 기록하는 표가 아니라, 거래처별 청구 건과 실제 입금 건을 연결해 미입금·부분입금·완납을 구분하는 표로 만들어야 합니다. 청구번호 또는 거래처·청구일·금액을 기준으로 입금내역과 대조하고, 일치하지 않는 건은 자동 확정하지 말고 검토 대상으로 남기는 것이 안전합니다.

미수금 관리대장에 필요한 열

구분	필수 열	용도
청구정보	청구번호, 거래처, 청구일, 청구액	받아야 할 금액의 기준
입금정보	입금일, 입금자명, 입금액, 계좌	실제로 받은 금액 확인
관리정보	누적입금액, 미수잔액, 상태, 확인메모	완납·부분입금·미입금 구분

청구번호를 사용할 수 있다면 가장 안정적입니다. 입금자명이 거래처명과 다르거나 여러 청구 건을 한 번에 입금하는 경우가 있으므로 상호명만으로 자동 연결하면 오판하기 쉽습니다.

기본 계산식

청구액이 D열, 누적입금액이 H열이라면 미수잔액은 다음처럼 계산할 수 있습니다.

=MAX(0,D2-H2)

상태는 회사 기준에 맞춰 다음처럼 구분할 수 있습니다.

=IF(H2=0,"미입금",IF(H2<D2,"부분입금",IF(H2=D2,"완납","초과입금 확인")))

초과입금은 완납으로 덮지 말고 별도 확인 상태로 남기세요. 다른 청구 건과 합산됐거나 환불·선수금 처리가 필요한 상황일 수 있습니다.

입금내역을 대조하는 순서

1. 기준일 현재의 청구목록을 한 시트에 모읍니다.
2. 은행 거래내역에서 입금일, 입금자명, 금액만 별도 시트로 가져옵니다.
3. 청구번호가 입금메모에 있으면 우선 연결합니다.
4. 청구번호가 없다면 거래처·금액·입금일을 함께 비교합니다.
5. 같은 금액이 여러 번 있거나 거래처명이 다르면 검토 대상으로 표시합니다.
6. 분할입금은 같은 청구번호에 여러 입금 행을 연결해 누적합니다.
7. 마감 전 미수잔액 합계와 거래처별 상세 합계를 대조합니다.

같은 금액이라고 자동으로 연결하면 안 되는 이유

110,000원처럼 반복되는 금액은 여러 거래처에서 동시에 발생할 수 있습니다. 입금액 하나만 보고 자동 매칭하면 다른 거래처의 입금을 잘못 차감할 수 있습니다. 자동화할 때도 ‘확정 매칭’과 ‘후보 매칭’을 나누는 것이 좋습니다.

• 확정 후보: 청구번호와 금액이 모두 일치
• 검토 후보: 거래처명과 금액은 같지만 청구번호가 없음
• 미확인: 금액만 같거나 입금자명이 전혀 다름

거래처별 잔액을 확인하는 방법

관리대장을 표로 만든 뒤 피벗 테이블에서 거래처를 행, 청구액·입금액·미수잔액을 값으로 배치하면 거래처별 잔액표를 만들 수 있습니다. 부분입금과 장기 미입금을 따로 보려면 상태와 청구월을 필터에 추가합니다.

거래명세서 자료가 여러 파일로 흩어져 있다면 먼저 거래처별 엑셀·CSV 분리 도구로 전달 자료를 정리하고, 내부 관리용 통합 원본은 별도로 유지하세요.

마감 전 체크리스트

• 청구액 합계와 원장 합계가 일치하는가
• 부분입금을 완납으로 처리하지 않았는가
• 입금자명과 거래처명이 다른 건을 확인했는가
• 초과입금과 미확인 입금을 별도 상태로 남겼는가
• 미수잔액 음수가 숨겨지지 않았는가

반복되는 입금대사를 줄이려면

매주 같은 청구목록과 계좌내역을 대조한다면 두 파일의 열을 연결해 확정 후보·검토 후보·미입금을 자동 분류할 수 있습니다. 회사의 청구번호 규칙과 입금자명 예외가 중요하므로, 자동 결과도 원장과 최종 대조해야 합니다. 반복업무 한 건의 자동화 가능성은 업무 자동화 도와드림에서 확인할 수 있습니다.

자주 묻는 질문

입금일이 청구일보다 빠르면 오류인가요?

선수금이나 선입금일 수 있으므로 자동 삭제하지 말고 별도 확인 상태로 둡니다.

한 번의 입금으로 여러 청구서를 결제한 경우는 어떻게 하나요?

입금 한 건을 청구 건별 배분표에 나누어 기록하고 배분 합계가 실제 입금액과 같은지 확인합니다.

미수금 독촉 목록은 어떻게 만드나요?

미수잔액이 0보다 크고 약정기일이 지난 행만 필터링합니다. 연락 전에는 반품·상계·분쟁·입금 미반영 여부를 확인하세요.