티스토리 뷰
업무도구365 · 사무실 기본 양식 01
100만원을 청구하고 40만원을 받았다면 남은 미수금은 60만원입니다. 그런데 다른 거래처에서 돈을 더 보냈다면, 전체 청구액에서 전체 입금액을 뺀 숫자만으로는 받을 돈을 정확하게 파악하기 어렵습니다.
이번 글에서는 청구 건별로 미수금과 초과 입금을 구분하는 엑셀 관리표를 제공합니다. 예제가 들어 있는 시트와 바로 입력할 수 있는 빈 시트를 한 파일에 담았습니다.
1. 이 양식은 어떤 상황에 맞을까요?
여러 거래처에 청구한 금액과 지금까지 받은 금액을 한눈에 정리하고 싶을 때 사용할 수 있습니다. 한 번에 전액을 받는 거래와 여러 번 나누어 받는 거래를 함께 관리하는 간단한 양식입니다.
한 행은 ‘거래처 한 곳’이 아니라 ‘청구 건 하나’입니다. 같은 거래처에 두 번 청구했다면 거래ID를 달리하여 두 줄에 입력합니다. 입금이 여러 번 이루어졌다면 해당 청구 건의 누적입금액을 갱신합니다.
청구 100건 입력, 거래처명 20개 수동 요약, 미수금·초과 입금 분리, 입금상태 자동 표시.
포함하지 않는 기능
입금일별 내역 저장, 은행 거래 자동 연동, 지급기한·연체일 계산, 취소·환불 처리.
2. 엑셀 파일 다운로드와 시트 구성
파일명은 01_미수금관리표_예제와실사용.xlsx입니다. 매크로는 없으며, 셀에 입력한 값과 수식으로 계산합니다.
- 예제: 가상의 청구 5건과 입금 상태를 확인하는 시트입니다.
- 실사용: 거래 자료가 비어 있는 시트입니다. 실제 자료는 이곳에 입력하세요.
예제에 등장하는 거래처명과 금액은 모두 설명을 위해 만든 가상 데이터입니다. 실제 거래 실적이나 사용 후기를 뜻하지 않습니다.
3. 노란 입력 칸에 네 가지 정보를 넣으세요
‘실사용’ 시트의 9행부터 입력합니다. 필수 항목은 거래ID, 거래처, 청구금액, 누적입금액입니다. 메모는 필요할 때만 적습니다.
| 열 | 입력할 내용 | 입력 예 |
|---|---|---|
| A · 거래ID | 청구 건마다 중복되지 않는 관리번호 | INV-001 |
| B · 거래처 | 같은 업체는 같은 이름으로 입력 | 가온상사 |
| C · 청구금액 | 해당 건에서 받기로 한 최종 금액 | 1000000 |
| D · 누적입금액 | 해당 건에 배분된 입금의 누계 | 400000 |
| H · 메모 | 입금 확인 내용 등 필요한 기록 | 1차 입금 확인 |
금액은 원 단위 정수로 입력합니다. ‘100만원’, ‘400,000원’처럼 글자를 붙이지 마세요. 숫자만 입력하면 표시 형식이 천 단위 쉼표를 붙여 보여줍니다.
아직 입금되지 않았다면 D열에 숫자 0을 입력하세요. 빈칸은 미입금 확정이 아니라 ‘입금액을 아직 확인하지 않았다’는 뜻으로 처리하여 입력 확인을 표시합니다.
4. 부분 입금은 새 줄을 만들지 않고 누적액을 고칩니다
가온상사에 100만원을 청구하고 먼저 40만원을 받았다면 누적입금액은 400000입니다. 이후 같은 청구 건으로 20만원을 더 받았다면 D열을 600000으로 변경합니다. 남은 미수금은 40만원으로 바뀝니다.
추가 입금 20만원만 적으면 기존 40만원을 덮어쓰게 됩니다. 같은 청구금액 100만원을 새 줄에 다시 쓰면 청구가 두 건으로 집계됩니다. 이 양식에서는 청구 행은 그대로 두고 입금 누계만 변경하는 것이 원칙입니다.
한 번 들어온 돈이 여러 청구 건의 대금이라면 실제 정산 내용을 확인해 각 행에 나누어 반영해야 합니다. 전체 입금액을 여러 행에 반복해서 넣으면 받은 돈이 중복 집계됩니다. 입금일별 원내역은 별도로 보관하세요.
5. 네 가지 입금상태는 어떻게 표시될까요?
| 상태 | 예시: 청구 → 입금 | 확인할 금액 |
|---|---|---|
| 미입금 | 500,000 → 0 | 미수금 500,000원 |
| 부분 입금 | 1,000,000 → 400,000 | 미수금 600,000원 |
| 입금 완료 | 800,000 → 800,000 | 미수금 0원 |
| 초과 입금 | 300,000 → 350,000 | 초과입금 50,000원 |
‘초과 입금’은 청구한 금액보다 많이 입력되었다는 표시입니다. 실제로 더 받은 돈인지, 다른 청구 건에 배분할 돈인지, 숫자를 잘못 입력했는지 확인해야 합니다. 이 양식은 초과 입금을 다른 행의 미수금과 자동으로 상계하지 않습니다.
6. 125만원과 130만원, 왜 차이가 날까요?
첨부 예제의 청구 합계는 280만원이고 누적 입금 합계는 155만원입니다. 두 합계만 빼면 125만원이 나옵니다. 하지만 행별 미수금 합계는 130만원이고, 별도로 확인할 초과 입금이 5만원 있습니다.
라온유통 건에서 청구 30만원보다 5만원을 더 받았기 때문입니다. 이 5만원을 전체 입금에 섞어서 차감하면 다른 거래에서 아직 받아야 할 돈이 작게 보입니다.
양식은 다음 원리로 각 행을 계산한 뒤 합산합니다. 아래 수식은 원리를 보여주는 간단한 예입니다. 첨부 파일에는 빈칸·잘못된 입력·중복 거래ID를 처리하는 조건이 추가되어 있습니다.
미수금 = MAX(청구금액 - 누적입금액, 0)
초과입금 = MAX(누적입금액 - 청구금액, 0)
첫 입력 행의 기본 계산 예
=MAX(C9-D9,0)
=MAX(D9-C9,0)따라서 예제는 ‘청구 280만원 − 입금 155만원 = 미수금 130만원 − 초과 입금 5만원’으로 서로 맞아떨어집니다.
7. 거래처별 합계는 오른쪽에서 확인하세요
시트 오른쪽의 J9~J28에 거래처명을 한 번씩 입력하면 K열은 미수금 합계, L열은 초과 입금 합계를 보여줍니다. 거래처 목록은 자동으로 생기지 않으므로 직접 입력해야 합니다.
예제에서 가온상사는 두 건의 미수금 60만원과 20만원을 합쳐 80만원으로 표시됩니다. ‘가온상사’와 ‘가온 상사’처럼 중간 공백이 다르면 다른 이름으로 집계되므로 표기를 통일하세요. 대소문자는 구분하지 않습니다.
특정 상태의 행만 보고 싶다면 G열 머리글의 필터를 사용하세요. 단, 필터로 행을 숨겨도 상단 합계와 거래처별 요약은 입력 범위 전체를 기준으로 계산합니다.
8. ‘입력 확인’ 또는 ‘ID 중복’이 나오면
거래ID·거래처가 없거나 금액이 빈칸·문자인 경우 ‘입력 확인’이 표시됩니다. 청구금액 0원 이하, 음수 입금액, 원 미만 소수 금액도 이 양식에서는 허용하지 않습니다.
같은 거래ID를 여러 행에 넣으면 ‘ID 중복’이 표시됩니다. 거래처가 같아도 별도의 청구라면 거래ID는 달라야 합니다. 이 두 상태의 행은 미수금·초과입금뿐 아니라 상단 청구·입금 합계에서도 제외됩니다. 상단의 ‘확인 필요’ 건수가 0이 될 때까지 합계를 확정하지 마세요.
E~G열의 수식은 잠겨 있지 않습니다. 실수로 덮어썼다면 즉시 실행 취소하거나, 원본 파일의 같은 위치에서 수식을 복원하세요. 자료를 입력하기 전에 원본을 한 부 남겨두는 것이 좋습니다.
9. 사용 전에 알아둘 범위와 검증 내용
- 입력은 9~108행, 최대 100건입니다. 109행 아래에 적으면 현재 수식 범위에 포함되지 않습니다. 이 버전은 자동 확장을 지원하지 않습니다.
- 부가세를 별도로 계산하지 않습니다. 이 파일에 적는 청구금액과 입금액은 서로 같은 기준으로 맞춰야 합니다.
- 반품·취소·환불이나 마이너스 거래를 처리하는 장부는 아닙니다. 해당 거래는 별도 내역으로 관리하세요.
- 작성 과정에서 빈행, 미입금, 부분·전액·초과 입금, 문자·누락·음수·0원 청구·소수 입력, 중복ID, 마지막 입력 행을 포함한 12가지 경우를 계산 엔진으로 확인했습니다.
- Microsoft Excel 앱에서의 직접 조작 검증은 하지 않았습니다. 실제 자료를 넣기 전 예제 시트의 미수금 1,300,000원과 초과입금 50,000원이 표시되는지 확인하세요.
자주 묻는 질문
같은 거래처를 여러 줄에 적어도 되나요?
네. 서로 다른 청구 건이면 가능합니다. 거래처명은 동일하게, 거래ID는 다르게 입력하면 오른쪽 요약에서 해당 거래처의 미수금이 합산됩니다.
입금액이 바뀌면 이전 금액이 기록되나요?
아니요. 이 파일은 현재 누적액을 관리하는 양식입니다. 값을 덮어쓰면 이전 입금 내역은 별도로 남지 않으므로 날짜별 입금 자료를 함께 보관하세요.
거래처별 요약의 합계가 전체 합계와 다르면요?
J열에 일부 거래처만 적었거나 거래처명이 다르게 입력되었는지 확인하세요. J열에 같은 거래처를 반복 입력하면 요약에도 같은 금액이 반복 표시됩니다. 요약의 숫자를 다시 더할 때는 중복을 먼저 확인하세요.
먼저 예제 시트에서 가온상사의 누적입금액을 400000에서 600000으로 바꿔보세요. 해당 행의 미수금이 400000원으로, 전체 미수금이 1100000원으로 바뀌는 것을 확인한 뒤 실사용 시트에 자료를 입력하면 됩니다.
수식 참고 자료
수식 문법은 Microsoft 공식 도움말을 참고했습니다. 이 글의 양식 구조와 가상 예제는 별도로 제작했습니다.
양식 버전 1.0 · 작성일 2026년 9월 29일