엑셀 미수금 잔액이 안 맞을 때: 청구일 기준 30·60·90일 채권연령 SUMIFS 검산법
월말 채권 대사를 하다 보면 엑셀 미수금 잔액이 ERP와 몇십만 원씩 안 맞는 경우가 있습니다. 특히 거래처별 미수금은 맞는 듯한데, 30일 이내·31~60일·61~90일·91일 이상 채권연령표로 나누면 합계가 틀어지는 일이 자주 생깁니다.
이번 예제는 청구원장과 입금원장이 따로 있고, 계산서번호 앞자리 0이 사라지거나 청구일이 20260701처럼 들어와 SUMIFS가 0으로 나오는 상황을 기준으로 설명하겠습니다. 어려운 기능으로 한 번에 끝내기보다, 실무에서 가장 많이 쓰는 SUMIFS, COUNTIFS, INDEX, MATCH, IF, IFERROR, TEXT, DATE, ROUND 조합으로 차근차근 검산하는 방식입니다.
Q. 어떤 표 구조에서 미수금 잔액이 틀어지나요?
시트 이름은 채권대사라고 가정하겠습니다. 청구원장은 A열부터 M열까지, 입금원장은 O열부터 U열까지, 거래처별 결과표는 W열부터 AF열까지 사용합니다. 기준일은 W2에 입력합니다.
예를 들어 W2에는 2026-07-31을 날짜 형식으로 입력합니다. 청구원장 데이터는 5행부터 5000행까지 있다고 보고 수식을 작성하겠습니다.
| 구역 | 열 | 열 이름 | 설명 |
|---|---|---|---|
| 청구원장 | A:H | 청구일, 거래처코드, 거래처명, 계산서번호, 품목, 공급가, 부가세, 청구액 | 세금계산서 또는 청구 기준 데이터 |
| 청구 보조열 | I:M | 정리청구일, 정리거래처코드, 정리계산서번호, 기준일입금액, 기준일잔액 | SUMIFS 검산용으로 정리한 열 |
| 입금원장 | O:U | 입금일, 거래처코드, 계산서번호, 입금액, 정리입금일, 정리거래처코드, 정리계산서번호 | 입금 내역과 정리용 보조열 |
| 결과표 | W:AF | 거래처코드, 거래처명, 총청구, 총입금, 미수잔액, 구간별 잔액 | 월말 채권현황표 |
샘플 데이터는 아래처럼 생각하면 됩니다. 실제 파일에서는 행 수가 많아도 구조만 같으면 그대로 적용할 수 있습니다.
| A 청구일 | B 거래처코드 | C 거래처명 | D 계산서번호 | H 청구액 |
|---|---|---|---|---|
| 20260620 | 001205 | 가온유통 | 0000457812 | 1,100,000 |
| 2026-07-05 | 1205 | 가온유통 | 457813 | 770,000 |
| 20260515 | 003010 | 라온마트 | 0000457901 | 2,200,000 |
| 20260410 | 003010 | 라온마트 | 457902 | 550,000 |
여기서 눈여겨볼 부분은 거래처코드와 계산서번호입니다. 같은 거래처인데 어떤 행은 001205, 어떤 행은 1205로 들어가 있습니다. 계산서번호도 0000457812와 457812처럼 앞자리 0이 사라져 있으면, 사람 눈에는 같은 번호처럼 보여도 엑셀은 다른 값으로 봅니다.
Q. SUMIFS 미수금이 0으로 나오는 첫 번째 원인은 뭔가요?
대부분은 날짜와 코드 형식 문제입니다. 청구일이 진짜 날짜가 아니라 20260701 같은 숫자 또는 문자로 들어오면, 조건식에서 <=기준일을 걸었을 때 원하는 대로 계산되지 않습니다.
먼저 I5에 정리청구일을 만듭니다. A5가 20260701, 2026-07-01, 실제 날짜 형식 중 무엇으로 들어와도 날짜로 맞추는 목적입니다.
=IFERROR(DATE(LEFT(SUBSTITUTE(A5,"-",""),4),MID(SUBSTITUTE(A5,"-",""),5,2),RIGHT(SUBSTITUTE(A5,"-",""),2)),A5)I5 수식을 I5000까지 복사합니다. 만약 A열이 이미 정상 날짜라면 결과는 그대로 날짜처럼 표시됩니다. 표시가 숫자처럼 보이면 I열 셀 서식을 날짜로 바꿔 확인하면 됩니다.
다음은 거래처코드입니다. 회사마다 거래처코드 자릿수가 다르지만, 여기서는 6자리 코드라고 가정합니다. J5에 아래 수식을 입력합니다.
=IFERROR(TEXT(B5,"000000"),RIGHT("000000"&B5,6))계산서번호는 10자리라고 가정하고 K5에 입력합니다.
=IFERROR(TEXT(D5,"0000000000"),RIGHT("0000000000"&D5,10))이제 청구원장의 날짜, 거래처코드, 계산서번호가 비교 가능한 형태로 정리되었습니다. 이 보조열을 만들지 않고 원본 열에 바로 SUMIFS를 걸면, 앞자리 0과 날짜 형식 때문에 결과가 0 또는 누락으로 나올 수 있습니다.
Q. 입금원장도 똑같이 정리해야 하나요?
네, 반드시 똑같이 정리해야 합니다. 청구원장만 깔끔하게 만들어도 입금원장의 계산서번호가 457812로 남아 있으면 서로 매칭되지 않습니다.
입금원장은 O열부터 U열까지 사용한다고 가정합니다. O열은 입금일, P열은 거래처코드, Q열은 계산서번호, R열은 입금액입니다. S5에는 정리입금일을 입력합니다.
=IFERROR(DATE(LEFT(SUBSTITUTE(O5,"-",""),4),MID(SUBSTITUTE(O5,"-",""),5,2),RIGHT(SUBSTITUTE(O5,"-",""),2)),O5)T5에는 정리거래처코드를 입력합니다.
=IFERROR(TEXT(P5,"000000"),RIGHT("000000"&P5,6))U5에는 정리계산서번호를 입력합니다.
=IFERROR(TEXT(Q5,"0000000000"),RIGHT("0000000000"&Q5,10))이 수식들도 5000행까지 복사합니다. 핵심은 청구원장의 J:K와 입금원장의 T:U가 같은 기준으로 만들어져야 한다는 점입니다.
Q. 계산서별 기준일 잔액은 어떻게 잡나요?
채권연령표를 제대로 만들려면 거래처 총액만 보면 안 됩니다. 청구서별로 기준일까지 얼마가 입금됐고, 얼마가 남았는지 먼저 계산해야 합니다. 그래야 오래된 청구서가 아직 남아 있는지 확인할 수 있습니다.
청구원장 L5에는 해당 계산서에 대해 기준일 W2까지 입금된 금액을 구합니다. 거래처코드와 계산서번호를 동시에 조건으로 걸어야 같은 번호가 다른 거래처에서 반복되는 경우도 피할 수 있습니다.
=SUMIFS($R$5:$R$5000,$T$5:$T$5000,$J5,$U$5:$U$5000,$K5,$S$5:$S$5000,"<="&$W$2)청구원장 M5에는 기준일 현재 남은 잔액을 계산합니다. 청구일이 기준일보다 뒤인 건은 아직 채권에 포함하지 않도록 IF로 한 번 막아줍니다.
=IF($I5>$W$2,0,IF(ROUND($H5-$L5,0)<0,0,ROUND($H5-$L5,0)))여기서 ROUND를 넣은 이유는 공급가, 부가세, 외화 환산, 수수료 차감 과정에서 소수점이 숨어 있는 파일이 있기 때문입니다. 화면에는 0으로 보이는데 실제 값이 0.00001처럼 남아 있으면 COUNTIFS나 SUMIFS 결과가 미묘하게 달라질 수 있습니다.
Q. 거래처별 미수금 현황표는 어떤 수식으로 만들면 되나요?
결과표는 W5부터 AF5까지 제목을 만듭니다. W열은 거래처코드, X열은 거래처명, Y열은 총청구, Z열은 총입금, AA열은 미수잔액입니다. AB열부터 AE열까지는 채권연령 구간입니다.
| 열 | 제목 | 계산 기준 |
|---|---|---|
| W | 거래처코드 | 직접 입력 또는 거래처 목록에서 복사 |
| X | 거래처명 | 코드 기준으로 가져오기 |
| Y | 총청구 | 기준일까지 청구된 금액 |
| Z | 총입금 | 기준일까지 입금된 금액 |
| AA | 미수잔액 | 계산서별 잔액 합계 |
| AB:AE | 30/60/90일 구간 | 청구일 기준 잔액 분류 |
W6에는 확인할 거래처코드를 6자리 형태로 입력합니다. 예를 들어 001205처럼 입력합니다. X6 거래처명은 INDEX와 MATCH로 가져오면 됩니다.
=IFERROR(INDEX($C$5:$C$5000,MATCH($W6,$J$5:$J$5000,0)),"코드확인")Microsoft 365 또는 Excel 2021 이상에서 XLOOKUP을 자주 쓴다면 아래처럼 써도 됩니다.
=IFERROR(XLOOKUP($W6,$J$5:$J$5000,$C$5:$C$5000,"코드확인"),"코드확인")Y6 총청구액은 기준일까지 청구된 금액만 합산합니다.
=SUMIFS($H$5:$H$5000,$J$5:$J$5000,$W6,$I$5:$I$5000,"<="&$W$2)Z6 총입금액은 기준일까지 입금된 금액만 합산합니다.
=SUMIFS($R$5:$R$5000,$T$5:$T$5000,$W6,$S$5:$S$5000,"<="&$W$2)AA6 미수잔액은 단순히 Y6-Z6로 계산할 수도 있지만, 실무에서는 계산서별 잔액을 합산하는 쪽이 더 안전합니다. 과입금이나 선입금이 섞여 있을 때 거래처 총액 기준으로 보면 오래된 채권 구간이 왜곡될 수 있기 때문입니다.
=SUMIFS($M$5:$M$5000,$J$5:$J$5000,$W6)Q. 30일 이내, 31~60일, 61~90일, 91일 이상은 어떻게 나누나요?
채권연령은 기준일 W2에서 청구일 I열을 뺀 일수로 판단합니다. 다만 별도 일수 열을 만들지 않고도 SUMIFS 조건으로 바로 구간을 나눌 수 있습니다.
AB6에는 30일 이내 잔액을 구합니다. 기준일보다 30일 전 이후에 청구된 건만 포함합니다.
=SUMIFS($M$5:$M$5000,$J$5:$J$5000,$W6,$I$5:$I$5000,">"&$W$2-30,$I$5:$I$5000,"<="&$W$2)AC6에는 31~60일 구간을 구합니다.
=SUMIFS($M$5:$M$5000,$J$5:$J$5000,$W6,$I$5:$I$5000,">"&$W$2-60,$I$5:$I$5000,"<="&$W$2-30)AD6에는 61~90일 구간을 구합니다.
=SUMIFS($M$5:$M$5000,$J$5:$J$5000,$W6,$I$5:$I$5000,">"&$W$2-90,$I$5:$I$5000,"<="&$W$2-60)AE6에는 91일 이상 구간을 구합니다.
=SUMIFS($M$5:$M$5000,$J$5:$J$5000,$W6,$I$5:$I$5000,"<"&$W$2-90)AF6에는 미수로 남아 있는 계산서 건수를 확인합니다.
=COUNTIFS($J$5:$J$5000,$W6,$M$5:$M$5000,">0")이제 W6:AF6 수식을 아래 거래처 행까지 복사하면 거래처별 채권현황표가 완성됩니다.
Q. 구간 합계와 미수잔액이 다르면 어디부터 확인하나요?
가장 먼저 확인할 것은 AA6과 AB6:AE6 합계입니다. 같은 거래처라면 아래 두 값이 같아야 합니다.
=AA6-SUM(AB6:AE6)결과가 0이 아니면 보통 세 가지 중 하나입니다. 첫째, 청구일 I열에 빈칸이나 잘못된 날짜가 있습니다. 둘째, 기준일 W2가 문자로 입력되어 있습니다. 셋째, 청구일이 기준일보다 미래인데 잔액 계산에서 제외되지 않았습니다.
날짜가 제대로 잡혔는지 빠르게 보려면 빈 셀에 아래처럼 COUNTIFS 테스트를 해봅니다.
=COUNTIFS($I$5:$I$5000,"<="&$W$2)이 값이 예상보다 너무 작으면 I열 날짜 정리부터 다시 봐야 합니다. W2도 날짜처럼 보이지만 문자일 수 있으니, W2 셀 서식을 날짜로 바꿔 보고 정렬이나 비교가 되는지 확인합니다.
Q. VLOOKUP으로 거래처명을 가져오면 #N/A가 뜨는 이유는요?
거래처코드가 숫자와 문자로 섞여 있기 때문인 경우가 많습니다. 001205와 1205는 표시만 비슷할 뿐, 엑셀에서는 다른 값입니다. 그래서 결과표 W열도 반드시 정리된 코드 기준으로 맞춰야 합니다.
거래처 목록을 따로 AH5:AI200에 두고, AH열에는 6자리 거래처코드, AI열에는 거래처명을 넣었다면 X6에 아래처럼 VLOOKUP을 사용할 수 있습니다.
=IFERROR(VLOOKUP($W6,$AH$5:$AI$200,2,FALSE),"거래처코드 확인")여기서 마지막 인수 FALSE를 빼면 근사값으로 찾으면서 전혀 다른 거래처명이 들어갈 수 있습니다. 거래처코드, 상품코드, 계산서번호처럼 정확히 일치해야 하는 값은 FALSE를 습관처럼 넣는 편이 안전합니다.
Q. 월말 기준으로만 보고 싶으면 EOMONTH를 써도 되나요?
네. 매월 말일 기준으로 채권현황을 뽑는 파일이라면 W2에 직접 날짜를 입력하는 대신, 기준월을 입력하고 월말을 자동 계산해도 됩니다.
예를 들어 V2에 2026-07처럼 기준월을 입력하고, W2에 월말 기준일을 만들고 싶다면 아래 수식을 사용합니다.
=EOMONTH(DATE(LEFT(V2,4),RIGHT(V2,2),1),0)이렇게 해두면 V2만 2026-08, 2026-09로 바꿔도 W2가 해당 월 말일로 자동 변경됩니다. 월말 채권 보고서를 매번 만드는 팀이라면 기준일 입력 실수를 줄이는 데 도움이 됩니다.
Q. 실무에서 자주 놓치는 검산 포인트는요?
첫째, 입금액 R열에 취소나 환불이 음수로 들어오는지 확인해야 합니다. 음수 입금이 섞여 있으면 총입금액은 맞아도 계산서별 잔액이 이상해질 수 있습니다.
둘째, 입금원장에 계산서번호가 비어 있는 건이 있는지 봐야 합니다. 거래처 단위 입금만 있고 계산서번호가 없으면, 청구서별 잔액 배분이 되지 않습니다. 이런 건은 별도 미배분 입금으로 관리하거나, 실제 상계할 계산서번호를 채워야 합니다.
셋째, 같은 계산서번호가 청구원장에 중복으로 들어간 경우입니다. 아래 수식을 빈 열에 넣어 중복 계산서 여부를 확인할 수 있습니다.
=COUNTIFS($J$5:$J$5000,$J5,$K$5:$K$5000,$K5)결과가 2 이상이면 같은 거래처, 같은 계산서번호가 청구원장에 두 번 들어간 것입니다. 수정 계산서, 재발행, 중복 업로드 여부를 확인해야 합니다.
이럴 때 이렇게 쓰면 된다
거래처별 미수금 총액만 필요하면 SUMIFS로 거래처코드와 기준일만 걸어도 충분합니다. 하지만 30일, 60일, 90일 채권연령까지 맞춰야 한다면 계산서별 기준일잔액 M열을 먼저 만든 뒤, 그 잔액을 청구일 구간으로 나누는 방식이 훨씬 안전합니다.
SUMIFS가 0으로 나오거나 VLOOKUP이 #N/A를 뱉는 파일은 대부분 원본 데이터가 틀린 게 아니라, 날짜·코드·계산서번호의 형식이 서로 맞지 않는 경우가 많습니다. 원본을 억지로 고치기보다 정리일자, 정리코드, 정리계산서번호 보조열을 만들어 검산하면 월말마다 같은 방식으로 재사용할 수 있습니다.