엑셀 미수잔액 연령표가 안 맞을 때: 계산서번호 앞자리 0·중복 품목행·SUMIFS 검산법

엑셀 미수잔액 연령표를 만들었는데 ERP 채권잔액과 몇십만 원씩 안 맞는 경우가 있습니다. 특히 세금계산서번호 앞자리 0이 사라졌거나, 한 계산서에 품목행이 여러 줄로 들어간 파일에서 SUMIFS 결과가 이상하게 튀는 일이 많습니다.

오늘 예시는 매출원장과 입금내역을 계산서번호로 맞춰서 거래처별 미수잔액을 30일 이하, 31~60일, 61~90일, 91일 이상으로 나누는 상황입니다. 단순히 거래처별 청구금액에서 입금액을 빼면 쉬워 보이지만, 실무 파일에서는 날짜 텍스트, 계산서번호 형식, 중복 품목행 때문에 결과가 틀어집니다.

미수잔액 연령표가 ERP와 안 맞는 실제 표 구조

예제 파일에는 시트가 3개 있다고 가정하겠습니다. 매출원장 시트에는 A열부터 H열까지 원천 데이터가 있고, 입금내역 시트에는 입금 자료가 따로 있습니다. 정산 시트에서는 기준일과 거래처코드를 넣고 결과를 확인합니다.

예를 들어 매출원장!D2:D5000의 계산서번호가 어떤 행은 0001234567처럼 텍스트이고, 어떤 행은 1234567처럼 숫자로 들어와 있다고 해보겠습니다. 입금내역에서는 같은 번호가 0001234567로 들어와 있으면 VLOOKUP이나 SUMIFS가 보기에는 같은 번호처럼 보여도 실제로는 다른 값으로 처리됩니다.

또 하나의 함정은 품목행 중복입니다. 계산서번호 0001234567에 상품 A, 상품 B, 배송비가 각각 한 줄씩 들어가면 매출원장에는 같은 계산서번호가 3행 존재합니다. 이 상태에서 각 행마다 입금액을 빼면 입금액이 3번 차감되어 미수잔액이 실제보다 작아집니다.

원인은 대부분 날짜 형식, 번호 형식, 중복 차감입니다

미수잔액 연령표 오류는 수식 자체가 틀렸다기보다 기준이 되는 열이 정리되지 않아 발생하는 경우가 많습니다. 특히 SUMIFS는 조건 범위와 조건값이 정확히 같은 형식이어야 안정적으로 계산됩니다.

그래서 바로 정산표에 긴 수식을 넣기보다, 매출원장과 입금내역에 보조열을 만들어 원인을 눈으로 확인할 수 있게 만드는 편이 안전합니다. 보조열이 조금 늘어나더라도 월말 마감 파일에서는 검산이 쉬운 구조가 훨씬 유리합니다.

매출원장 보조열로 계산서번호와 날짜를 먼저 통일하기

먼저 매출원장 시트에서 H열 청구금액이 비어 있다면 H2에 공급가액과 부가세를 더해 청구금액을 만듭니다. 금액 계산에서 1원 차이가 남지 않도록 ROUND를 같이 사용합니다.

=ROUND(F2+G2,0)

이 수식을 매출원장!H2:H5000까지 내려 채웁니다. 이미 H열에 청구금액이 들어 있는 파일이라면 이 과정은 건너뛰어도 됩니다.

이제 I열에는 계산서번호 정리값을 만듭니다. 매출원장!I1에는 계산서번호키라고 적고, I2에 아래 수식을 입력합니다.

=RIGHT("0000000000"&D2,10)

이 수식은 D2가 1234567처럼 숫자로 들어와도 앞에 0을 붙여 10자리로 맞춥니다. 계산서번호가 회사마다 8자리, 12자리라면 RIGHT의 자리수만 자기 파일 기준에 맞게 바꾸면 됩니다.

J열에는 날짜 정리값을 만듭니다. 매출원장!J1에는 전표일_날짜라고 입력하고 J2에 아래 수식을 넣습니다.

=IFERROR(IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)))

이 수식은 A2가 실제 날짜이면 그대로 쓰고, 20260731 같은 8자리 텍스트이면 DATE, LEFT, MID, RIGHT로 날짜를 다시 만듭니다. 만약 2026-07-31처럼 하이픈이 들어간 텍스트도 섞여 있다면 뒤쪽 DATE 수식으로 한 번 더 처리합니다.

K열에는 청구월을 표시해 두면 월별 검산에 좋습니다. 매출원장!K2에는 다음 수식을 넣습니다.

=TEXT(J2,"yyyy-mm")

이제 가장 중요한 중복 품목행 처리를 합니다. O열에는 같은 거래처코드와 계산서번호키 조합에서 첫 번째 행인지 표시합니다. 매출원장!O1에는 첫행여부, O2에는 아래 수식을 입력합니다.

=IF(COUNTIFS($B$2:B2,B2,$I$2:I2,I2)=1,1,0)

COUNTIFS의 범위가 $B$2:B2처럼 시작점은 고정되고 끝점은 내려가면서 움직이는 형태입니다. 이 방식이면 같은 계산서번호가 여러 품목행으로 반복되어도 첫 행만 1, 나머지는 0으로 표시됩니다.

입금내역도 같은 기준으로 맞춰야 SUMIFS가 살아납니다

입금내역 시트에서도 계산서번호와 입금일을 같은 방식으로 정리합니다. 입금내역!F1에는 계산서번호키, F2에는 아래 수식을 넣습니다.

=RIGHT("0000000000"&C2,10)

입금내역!G1에는 입금일_날짜라고 쓰고, G2에는 다음 수식을 넣습니다.

=IFERROR(IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)))

여기까지 해야 매출원장과 입금내역이 같은 언어로 대화할 수 있습니다. 계산서번호는 10자리 텍스트 키, 날짜는 실제 날짜값으로 맞춰졌기 때문입니다.

계산서별 청구합계와 입금합계를 첫 행에만 올리기

이제 매출원장으로 돌아와 P열부터 R열까지 계산서 단위 금액을 만듭니다. 핵심은 같은 계산서번호가 여러 줄이어도 청구합계와 입금합계는 첫 행에만 표시하는 것입니다.

매출원장!P1에는 계산서청구합계라고 쓰고, P2에 아래 수식을 입력합니다.

=IF($O2=1,SUMIFS($H$2:$H$5000,$B$2:$B$5000,$B2,$I$2:$I$5000,$I2),0)

이 수식은 현재 행이 첫 행일 때만 같은 거래처코드와 계산서번호키의 청구금액을 합산합니다. 첫 행이 아니면 0이므로 품목행 중복으로 금액이 부풀지 않습니다.

매출원장!Q1에는 기준일입금합계라고 쓰고, Q2에는 아래 수식을 넣습니다. 여기서는 정산!B2에 입력한 기준일까지의 입금만 반영합니다.

=IF($O2=1,SUMIFS(입금내역!$D$2:$D$4000,입금내역!$B$2:$B$4000,$B2,입금내역!$F$2:$F$4000,$I2,입금내역!$G$2:$G$4000,"<="&정산!$B$2),0)

매출원장!R1에는 계산서미수잔액이라고 쓰고, R2에는 아래 수식을 넣습니다.

=ROUND(P2-Q2,0)

마지막으로 S열에는 기준일 기준 경과일을 계산합니다. 매출원장!S1에는 경과일, S2에는 아래 수식을 입력합니다.

=IF(R2>0,정산!$B$2-J2,"")

R2가 0 이하인 계산서는 이미 입금 완료 또는 초과입금 상태이므로 연령표에서 제외하기 위해 빈칸으로 둡니다. 이제 정산 시트에서는 R열 미수잔액과 S열 경과일만 보면 됩니다.

정산 시트에서 거래처별 미수잔액 연령표 만들기

정산 시트는 다음처럼 구성합니다. B2에는 기준일, B3에는 조회할 거래처코드를 입력합니다. 예를 들어 B2는 2026-07-31, B3는 C1045입니다.

정산!A7에는 조회 거래처코드를 그대로 가져옵니다.

=$B$3

정산!B7에는 거래처명을 표시합니다. Microsoft 365 또는 Excel 2021 이상이라면 XLOOKUP을 사용할 수 있습니다.

=IFERROR(XLOOKUP(A7,매출원장!$B$2:$B$5000,매출원장!$C$2:$C$5000),"거래처코드 확인")

XLOOKUP이 없는 버전이라면 INDEX와 MATCH 조합으로 바꿔도 됩니다.

=IFERROR(INDEX(매출원장!$C$2:$C$5000,MATCH(A7,매출원장!$B$2:$B$5000,0)),"거래처코드 확인")

정산!C7에는 기준일까지 발생한 계산서 청구합계를 구합니다.

=SUMIFS(매출원장!$P$2:$P$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$J$2:$J$5000,"<="&$B$2)

정산!D7에는 기준일까지 반영된 입금합계를 구합니다.

=SUMIFS(매출원장!$Q$2:$Q$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$J$2:$J$5000,"<="&$B$2)

정산!E7에는 전체 미수잔액을 계산합니다.

=ROUND(C7-D7,0)

이제 연령구간을 나눕니다. 정산!F7은 30일 이하입니다.

=SUMIFS(매출원장!$R$2:$R$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$S$2:$S$5000,">=0",매출원장!$S$2:$S$5000,"<=30")

정산!G7은 31~60일입니다.

=SUMIFS(매출원장!$R$2:$R$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$S$2:$S$5000,">=31",매출원장!$S$2:$S$5000,"<=60")

정산!H7은 61~90일입니다.

=SUMIFS(매출원장!$R$2:$R$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$S$2:$S$5000,">=61",매출원장!$S$2:$S$5000,"<=90")

정산!I7은 91일 이상 장기미수입니다.

=SUMIFS(매출원장!$R$2:$R$5000,매출원장!$B$2:$B$5000,$A7,매출원장!$S$2:$S$5000,">=91")

결과가 맞는지 확인하는 검산 순서

정산표를 만들고 나면 가장 먼저 정산!J7에 차이검산을 넣어보세요. 전체 미수잔액과 연령구간 합계가 일치해야 합니다.

=E7-SUM(F7:I7)

정상이라면 J7은 0이어야 합니다. 0이 아니라면 경과일이 빈칸인 미수 건이 있거나, 기준일보다 이후 전표가 섞여 있을 가능성이 큽니다.

두 번째로 계산서번호 중복이 제대로 제어되는지 확인합니다. 매출원장!O열에서 첫행여부가 1인 행의 P열 합계와, 실제 H열 청구금액 합계가 거래처별로 맞는지 비교합니다.

=SUMIFS(매출원장!$H$2:$H$5000,매출원장!$B$2:$B$5000,$A7)-SUMIFS(매출원장!$P$2:$P$5000,매출원장!$B$2:$B$5000,$A7)

이 값도 0이 나와야 합니다. 만약 차이가 난다면 계산서번호키가 빈칸이거나, 거래처코드가 같은 계산서 안에서 다르게 들어간 행이 있는지 확인해야 합니다.

세 번째로 입금 연결 여부를 확인합니다. 입금내역!F열 계산서번호키와 매출원장!I열 계산서번호키가 같은 자리수인지, 앞자리 0이 유지되는지 필터로 직접 봅니다. SUMIFS가 0을 반환하는 대부분의 원인은 여기서 발견됩니다.

반복 마감 파일이면 VBA로 보조열 수식 내려쓰기

매월 파일을 새로 받는 업무라면 보조열 수식을 매번 손으로 내리는 것도 번거롭습니다. 아래 코드는 매출원장과 입금내역 시트의 마지막 행을 찾아 위에서 설명한 보조열 수식을 자동으로 채웁니다.

실행 전에는 파일을 반드시 복사해 두고, 매크로 사용 통합문서 형식으로 저장하세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않으므로, 잘못 적용했다면 저장하지 않고 닫거나 H:S, F:G 보조열을 지운 뒤 다시 실행하면 됩니다.

Sub ApplyReceivableHelperFormulas()
    Dim wsS As Worksheet
    Dim wsP As Worksheet
    Dim lastSales As Long
    Dim lastPay As Long

    Set wsS = Worksheets("매출원장")
    Set wsP = Worksheets("입금내역")

    lastSales = wsS.Cells(wsS.Rows.Count, "A").End(xlUp).Row
    lastPay = wsP.Cells(wsP.Rows.Count, "A").End(xlUp).Row

    wsS.Range("H2:H" & lastSales).Formula = "=ROUND(F2+G2,0)"
    wsS.Range("I2:I" & lastSales).Formula = "=RIGHT(""0000000000""&D2,10)"
    wsS.Range("J2:J" & lastSales).Formula = "=IFERROR(IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)))"
    wsS.Range("K2:K" & lastSales).Formula = "=TEXT(J2,""yyyy-mm"")"
    wsS.Range("O2:O" & lastSales).Formula = "=IF(COUNTIFS($B$2:B2,B2,$I$2:I2,I2)=1,1,0)"
    wsS.Range("P2:P" & lastSales).Formula = "=IF($O2=1,SUMIFS($H$2:$H$5000,$B$2:$B$5000,$B2,$I$2:$I$5000,$I2),0)"
    wsS.Range("Q2:Q" & lastSales).Formula = "=IF($O2=1,SUMIFS(입금내역!$D$2:$D$4000,입금내역!$B$2:$B$4000,$B2,입금내역!$F$2:$F$4000,$I2,입금내역!$G$2:$G$4000,""<=""&정산!$B$2),0)"
    wsS.Range("R2:R" & lastSales).Formula = "=ROUND(P2-Q2,0)"
    wsS.Range("S2:S" & lastSales).Formula = "=IF(R2>0,정산!$B$2-J2,"""")"

    wsP.Range("F2:F" & lastPay).Formula = "=RIGHT(""0000000000""&C2,10)"
    wsP.Range("G2:G" & lastPay).Formula = "=IFERROR(IF(ISNUMBER(A2),A2,DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))),DATE(LEFT(A2,4),MID(A2,6,2),MID(A2,9,2)))"

    MsgBox "미수잔액 보조열 수식 입력이 완료되었습니다."
End Sub

이 코드는 시트명이 정확히 매출원장, 입금내역, 정산일 때 동작합니다. 시트명이 다르면 코드의 Worksheets 안 이름을 먼저 바꿔야 합니다.

실무에서 적용할 때 볼 것

미수잔액 연령표는 함수 하나로 끝내기보다 데이터 정리, 중복 제어, 기준일 검산이 함께 들어가야 안정적입니다. 계산서번호 앞자리 0을 맞추고, 날짜를 실제 날짜값으로 바꾸고, 중복 품목행에서는 첫 행에만 청구와 입금을 올리는 구조를 만들면 ERP와의 차이를 훨씬 빨리 좁힐 수 있습니다.

정산 결과가 이상할 때는 수식을 바꾸기 전에 계산서번호키, 전표일_날짜, 첫행여부, 기준일입금합계 순서로 확인해 보세요. 대부분의 차이는 SUMIFS 자체가 아니라 조건으로 쓰는 값의 형식과 중복 차감에서 나옵니다.