입금액은 맞는데 정산이 안 맞을 때, ‘대사키’로 누락 거래 잡는 법
월말 정산을 하다 보면 이상한 순간이 있습니다. 통장 입금 합계는 얼추 맞는 것 같은데, 매출 원장 기준으로 보면 특정 거래처가 미입금으로 남아 있거나, 반대로 입금은 있는데 어떤 전표와 연결해야 할지 애매한 경우입니다. 이때 한 건씩 눈으로 맞추기 시작하면 시간이 오래 걸리고, 더 큰 문제는 ‘비슷해 보이는 금액’을 잘못 연결하는 실수가 생긴다는 점입니다.
실무에서는 매출표와 입금내역을 바로 비교하기보다, 먼저 비교 기준을 정리한 뒤 대사표를 만드는 편이 안전합니다. 핵심은 거래처명, 날짜, 금액을 그대로 믿지 말고 정산용 기준값으로 한 번 정리한 다음 비교하는 것입니다. 저는 이 기준값을 보통 ‘대사키’라고 부릅니다. 어려운 기능이라기보다, 엑셀이 헷갈리지 않게 비교용 이름표를 붙여 주는 작업에 가깝습니다.
한 건씩 찾기 전에 비교 단위부터 정해야 합니다
정산 대사에서 가장 먼저 결정할 것은 ‘무엇과 무엇을 맞출 것인가’입니다. 주문 1건과 입금 1건이 항상 일치하면 간단하지만, 실제 업무에서는 그렇지 않은 경우가 훨씬 많습니다.
| 상황 | 흔한 예 | 추천 비교 단위 |
|---|---|---|
| 전표 1건 = 입금 1건 | 거래처가 주문별로 정확히 입금 | 거래처 + 전표일 + 금액 |
| 전표 여러 건 = 입금 1건 | 한 거래처가 주간 단위로 합산 입금 | 거래처 + 정산기간 + 합계금액 |
| 입금자명이 거래처명과 다름 | 대표자명, 약칭, 지점명으로 입금 | 입금자 매칭표 적용 후 비교 |
| 수수료 또는 절사 차이 있음 | PG 수수료, 원단위 절사 | 허용 차이 기준 포함 |
여기서 실수하기 쉬운 부분은 ‘금액이 같으면 같은 거래’라고 생각하는 것입니다. 55,000원, 110,000원 같은 금액은 여러 거래처에서 자주 반복됩니다. 금액만으로 찾으면 속도는 빠르지만, 정산표 신뢰도는 떨어집니다.
거래처명은 사람이 보기 좋게가 아니라 엑셀이 비교하기 좋게 정리합니다
매출 원장에는 ‘ABC상사’, 입금내역에는 ‘ABC 상사’, 또 다른 파일에는 ‘(주)ABC상사’처럼 들어오는 일이 많습니다. 눈으로 보면 같은 거래처인데, 엑셀 입장에서는 전부 다른 값입니다. 그래서 비교 전에 정리용 열을 하나 추가하는 것이 좋습니다.
예를 들어 매출 원장을 표로 만들고 이름을 tblSales, 입금내역 표 이름을 tblBank라고 해 보겠습니다. 매출 원장에 정리거래처 열을 추가하고 아래 수식을 넣습니다.
=SUBSTITUTE(TRIM(SUBSTITUTE([@거래처],CHAR(160)," "))," ","")이 수식은 일반 공백뿐 아니라 웹이나 다른 시스템에서 복사할 때 섞이는 특수 공백까지 어느 정도 정리해 줍니다. 입금내역에도 같은 방식으로 정리입금자 열을 만들면 비교 정확도가 올라갑니다.
다만 회사명 앞뒤의 ‘주식회사’, ‘(주)’, 지점명처럼 규칙이 다양한 경우에는 수식 하나로 전부 해결하려고 하지 않는 편이 낫습니다. 그럴 때는 별도 시트에 매칭표를 만듭니다.
| 입금자 표시명 | 표준 거래처명 |
|---|---|
| ABC상사 | ABC상사 |
| ABC 상사 | ABC상사 |
| 홍길동 | 길동유통 |
| 길동유통 본점 | 길동유통 |
매칭표를 쓰면 처음에는 손이 조금 가지만, 다음 달부터는 같은 오류를 반복해서 고치지 않아도 됩니다. 정산 업무에서는 이런 기준표가 쌓일수록 시간이 줄어듭니다.
대사키는 ‘찾기용 꼬리표’라고 생각하면 편합니다
거래처명과 날짜, 금액이 정리되었다면 이제 비교용 키를 만들 수 있습니다. 전표 1건과 입금 1건이 거의 일치하는 업무라면 아래처럼 매출 원장에 대사키 열을 추가합니다.
=[@정리거래처]&"|"&TEXT([@전표일],"yyyymmdd")&"|"&TEXT(ROUND([@청구금액],0),"0")입금내역에도 같은 기준으로 키를 만듭니다.
=[@정리입금자]&"|"&TEXT([@입금일],"yyyymmdd")&"|"&TEXT(ROUND([@입금액],0),"0")그다음 매출 원장에서 입금내역에 같은 키가 있는지 확인합니다.
=COUNTIF(tblBank[대사키],[@대사키])결과가 1이면 일단 매칭 후보가 있다는 뜻이고, 0이면 입금내역에서 같은 조건을 찾지 못했다는 뜻입니다. 2 이상이면 더 주의해야 합니다. 같은 거래처, 같은 날짜, 같은 금액이 여러 번 있을 수 있기 때문입니다. 이 경우에는 주문번호, 메모, 승인번호 같은 보조 기준이 필요합니다.
입금일이 하루 이틀 밀리는 업무라면 SUMIFS 방식이 더 현실적입니다
온라인몰, 카드 정산, 대리점 입금처럼 전표일과 입금일이 정확히 일치하지 않는 업무에서는 날짜를 딱 맞춰 비교하면 미입금이 과하게 잡힙니다. 이럴 때는 입금 허용 기간을 두고 금액 합계를 확인하는 방식이 더 좋습니다.
예를 들어 전표일로부터 3일 안에 같은 거래처에서 입금된 금액을 확인하려면 매출 원장에 입금확인액 열을 만들고 아래 수식을 사용할 수 있습니다.
=SUMIFS(tblBank[입금액],tblBank[정리입금자],[@정리거래처],tblBank[입금일],">="&[@전표일],tblBank[입금일],"<="&[@전표일]+3)그리고 차이금액과 상태를 나눕니다.
=[@청구금액]-[@입금확인액]=IF(ABS([@차이금액])<=10,"확인",IF([@입금확인액]=0,"미입금 또는 누락","부분입금 또는 금액차이"))여기서 10은 허용 차이입니다. 회사마다 원단위 절사, 수수료, 환불 반영 방식이 다르므로 0원으로 엄격하게 볼지, 10원 또는 100원까지 허용할지 기준을 먼저 정해야 합니다. 기준 없이 수식만 만들면 나중에 정산표를 설명할 때 곤란해집니다.
정산표가 틀어질 때 자주 보이는 원인은 따로 있습니다
수식이 맞는데도 결과가 이상하다면 원본 데이터 쪽을 먼저 확인하는 것이 빠릅니다. 특히 아래 항목은 정산 대사에서 자주 문제를 일으킵니다.
| 확인 항목 | 증상 | 처리 방법 |
|---|---|---|
| 날짜에 시간이 숨어 있음 | 같은 날짜처럼 보이는데 비교 실패 | =INT([@입금일])로 날짜만 분리 |
| 금액이 텍스트임 | 합계가 0 또는 일부만 계산 | 쉼표, 공백 제거 후 숫자로 변환 |
| 입금액 부호가 반대 | 취소, 환불이 매출처럼 합산 | 거래구분별 부호 기준표 사용 |
| 거래처명이 일부만 다름 | 미입금으로 잘못 표시 | 매칭표로 표준명 관리 |
| 중복 키 존재 | 입금확인 건수가 2건 이상 | 주문번호, 메모, 승인번호 추가 확인 |
날짜 문제는 특히 많이 놓칩니다. 셀에는 2026-06-23처럼 보이지만 실제 값은 2026-06-23 14:35:20일 수 있습니다. 표시 형식만 날짜로 되어 있으면 육안으로는 구분이 어렵습니다. 날짜 비교가 이상하다면 별도 열에 =INT(날짜셀)을 넣어 실제 비교용 날짜를 따로 만드는 것이 안전합니다.
결과표는 ‘확인’보다 ‘예외’가 먼저 보이게 만듭니다
정산 대사표의 목적은 모든 정상 건을 예쁘게 보여주는 것이 아니라, 확인해야 할 예외 건을 빨리 찾는 것입니다. 그래서 상태 열을 만들었다면 조건부 서식을 걸어 두는 것이 좋습니다.
예를 들어 상태가 ‘미입금 또는 누락’이면 연한 빨강, ‘부분입금 또는 금액차이’이면 노랑으로 표시합니다. 금액 차이가 큰 순서로 정렬하면 담당자가 확인할 순서도 자연스럽게 정해집니다.
추가로 피벗 테이블을 하나 붙여 두면 보고용으로도 바로 쓸 수 있습니다.
| 행 | 값 | 필터 |
|---|---|---|
| 담당자, 거래처 | 청구금액 합계, 입금확인액 합계, 차이금액 합계 | 상태, 정산월 |
이렇게 만들면 팀장에게는 전체 차이금액을 보여주고, 실무자는 거래처별 미확인 건만 내려받아 확인할 수 있습니다. 정산표 하나로 보고와 실무 확인을 동시에 처리할 수 있는 구조입니다.
여러 건 합산 입금은 원장 요약표를 먼저 만든 뒤 비교합니다
거래처가 여러 전표를 한 번에 입금하는 업무에서는 개별 전표 대사보다 요약 대사가 더 정확할 때가 많습니다. 예를 들어 거래처와 정산주차 단위로 매출 원장을 먼저 합산하고, 입금내역도 같은 단위로 합산한 뒤 비교합니다.
매출 원장에 정산주차 열을 만들 수 있습니다.
=YEAR([@전표일])&"-W"&TEXT(WEEKNUM([@전표일],2),"00")그다음 거래처와 정산주차별로 피벗 테이블을 만들거나, SUMIFS로 요약표를 구성합니다. 이 방식은 전표 하나하나가 입금 건과 1:1로 맞지 않아도 전체 정산 차이를 안정적으로 잡아낼 수 있습니다. 다만 어떤 전표가 실제로 포함되었는지 추적해야 하는 업무라면, 요약 대사 후 차이가 나는 거래처만 상세 대사로 내려가는 방식이 좋습니다.
대사표를 만들고 나서 꼭 확인할 순서
정산표가 어느 정도 완성되면 바로 제출하지 말고, 아래 순서로 한 번 더 점검해 보세요. 이 과정에서 대부분의 오류가 잡힙니다.
원본 행 수 확인은 가장 기본입니다. 표로 변환하거나 필터링하는 과정에서 일부 행이 빠지지 않았는지 확인합니다. 원본 매출 건수와 대사표 건수가 다르면 수식이 맞아도 결과는 믿기 어렵습니다.
전체 합계 비교도 필요합니다. 매출 원장의 청구금액 합계, 대사표의 청구금액 합계가 일치해야 합니다. 입금내역도 원본 통장 입금 합계와 대사표에서 참조한 입금액 합계가 맞아야 합니다.
중복 키 확인은 마지막에 꼭 해 봐야 합니다. 대사키 기준으로 COUNTIF 결과가 2 이상인 건은 자동 매칭 결과를 그대로 믿지 말고 별도 검토 대상으로 빼는 것이 좋습니다. 중복 키는 정산 사고의 출발점이 되는 경우가 많습니다.
차이금액 상위 건 확인은 보고 전에 유용합니다. 전체 차이가 크지 않더라도 특정 거래처에서 큰 차이와 반대 방향의 차이가 섞여 있을 수 있습니다. 합계만 보면 0에 가까운데 실제로는 오류가 두 개 숨어 있는 상황입니다.
조금 더 편하게 쓰는 응용 팁
매달 같은 형식의 매출 원장과 입금내역을 받는다면, 표 이름과 열 이름을 고정해 두는 것이 좋습니다. 수식이 구조화 참조로 작성되어 있으면 다음 달 데이터를 붙여 넣어도 범위가 자동으로 늘어납니다. A:A 전체 열을 참조하는 방식보다 계산 속도와 관리 측면에서 훨씬 안정적입니다.
또한 매칭표는 정산 파일 안에 숨겨 두지 말고 별도 시트로 분리해 관리하는 편이 좋습니다. 새로운 입금자명이 발견되면 그때그때 표준 거래처명을 추가합니다. 이 매칭표가 쌓이면 월말마다 반복하던 거래처명 정리 시간이 확 줄어듭니다.
입금 누락 건을 담당자에게 공유해야 한다면 상태 열에서 ‘확인’이 아닌 값만 필터링한 뒤 별도 시트로 복사합니다. 이때 원본 행 번호나 주문번호를 함께 남겨 두면, 나중에 누가 어떤 건을 확인했는지 추적하기 쉽습니다.
정산 대사는 화려한 수식보다 기준을 일정하게 잡는 것이 더 중요합니다. 거래처명을 정리하고, 날짜와 금액을 비교용 값으로 바꾸고, 대사키나 기간 합계로 확인하는 흐름만 만들어 두면 월말마다 반복되는 확인 작업이 훨씬 차분해집니다. 처음 한 번은 손이 가지만, 다음 달부터는 예외 건만 보는 정산표로 바뀌는 차이가 꽤 큽니다.