스프레드시트에서 거래 내역을 대조하는 방법 (일일이 행을 맞추지 않고)

두 개의 거래 내역을 대조(reconcile)한다는 것은 결국 네 가지 차이점을 찾아내는 작업입니다. 한쪽에서 누락된 행, 다른 쪽에서 중복된 행, 서로 일치하지 않는 금액, 그리고 동일한 결제 건이 서로 다른 기간에 반영된 경우입니다. 그 외의 모든 작업은 이 네 가지를 해결하기 위한 장부 정리에 불과합니다.

본능적으로 두 파일을 나란히 놓고 행을 하나씩 맞추기 시작하게 됩니다. 이 방법은 데이터가 200행 정도일 때까지만 유효하며, 그 이상이 되면 오후 시간을 통째로 날리고도 아무도 검증할 수 없는 결과만 남게 됩니다.

이 가이드에서는 스프레드시트가 왜 이 작업에서 한계를 드러내는지, 사람들이 주로 사용하는 세 가지 우회 방법은 무엇인지, 그리고 각 방법의 한계가 어디까지인지 다룹니다. 이는 데이터 워크플로우에 대한 안내이며, 회계 자문이 아닙니다.

스프레드시트에서 거래 대조가 생각보다 어려운 이유

스프레드시트는 셀을 비교합니다. 반면 거래 대조는 '이벤트(사건)'를 비교하는 작업이며, 동일한 이벤트가 양쪽 장부에 똑같은 모습으로 기록되는 경우는 거의 없습니다.

카드 결제 건이 원장에는 한 번만 기록되어 있지만, 결제 대행사 내역에는 승인 금액과 수수료로 나뉘어 두 번 기록될 수 있습니다. 공급업체 인보이스 번호가 한 시스템에는 INV-0042로, 다른 시스템에는 INV42로 표시될 수도 있습니다. 31일에 시작된 송금이 다음 달 1일에 완료되면 한 달 전체의 수치가 어긋나게 됩니다.

이 중 어느 것도 데이터 오류가 아닙니다. 동일한 사실을 두 개의 서로 다른 시스템이 기록할 때 발생하는 자연스러운 현상이며, 어떤 조회 공식(lookup formula)으로도 이를 단번에 해결할 수는 없습니다.

매달 사람들을 곤경에 빠뜨리는 반올림 함정도 있습니다. 부동 소수점으로 저장된 통화 값은 소수점 넷째 자리에서 미세한 차이가 날 수 있어, 화면상으로는 둘 다 1,204.50으로 보이더라도 실제 일치 여부 테스트에서는 불일치로 판정될 수 있습니다.

데이터의 양에 따라 문제의 성격도 달라집니다. 50행 정도라면 머릿속으로 두 목록을 대조할 수 있습니다. 하지만 5,000행이 되면 수많은 일치 항목 속에 숨겨진 몇 안 되는 예외를 찾아내는 숨바꼭질이 됩니다. 인간의 집중력은 이러한 작업에 그리 적합하지 않습니다.

이로 인해 치러야 하는 대가

월말의 꼬리표 작업. 처음 90%의 행은 몇 분 만에 맞춰집니다. 하지만 남은 몇 개의 행을 해결하는 데 몇 시간이 걸립니다. 각 행이 앞서 언급한 네 가지 차이점 중 어디에 해당하는지 사람이 일일이 파악해야 하기 때문입니다.

감사 불가능한 결과. 눈으로 직접 대조 작업을 하면 파일을 닫는 순간 작업 과정이 사라집니다. 6주만 지나도 왜 그 두 행을 동일한 결제 건으로 처리했는지 아무도 재구성할 수 없습니다.

남아 있는 오류. 중복된 항목이 엉뚱한 상대 항목과 잘못 매칭되면 합계에서는 상쇄되어 대조가 완벽하게 끝난 것처럼 보입니다. 합계가 일치한다고 해서 개별 행들이 올바르게 대조되었다는 증거는 아닙니다.

이 세 가지 비용은 서로 얽혀 눈덩이처럼 불어납니다. 지루한 꼬리표 작업은 피로를 유발하고, 피로는 편법을 낳으며, 편법은 결국 잘못된 매칭을 올바른 매칭으로 기록하게 만듭니다.

사람들이 시도하는 임시방편들

옵션 1: 대조하기 전에 합계부터 비교하기

개별 행을 비교하기 전에 그룹 합계부터 비교해 보세요. SUMIFS 함수를 사용하여 월별, 계정별 또는 거래처별로 양쪽의 합계를 구한 뒤 두 열을 나란히 놓습니다.

이렇게 하면 본격적으로 시간을 쓰기 전에 차이가 발생하는 지점을 좁힐 수 있습니다. 12개월 중 11개월의 수치가 소수점 아래까지 정확히 일치한다면, 1년 치가 아니라 단 한 달 치만 대조하면 됩니다.

이 방법은 확실히 유용하지만 한계가 명확합니다. 그룹 합계는 불일치가 발생하는 '위치'만 알려줄 뿐, 어떤 행 때문에 발생했는지는 알려주지 않습니다. 또한 동일한 그룹 내에서 두 개의 오류가 서로 상쇄되면 눈에 띄지 않고 묻혀버립니다.

옵션 2: 매칭 키를 만들어 조회하기

이벤트를 식별할 수 있는 필드들(일반적으로 날짜, 금액, 정제된 참조 번호 등)을 결합하여 하나의 고유 키를 만듭니다. 그런 다음 양방향으로 XLOOKUP을 사용하여 한쪽에는 존재하지만 다른 쪽에는 없는 행을 찾아냅니다.

중복 항목을 잡아내려면 동일한 키에 대해 COUNTIFS를 추가해야 합니다. 조회 함수는 첫 번째 일치 항목만 반환하고 두 번째 항목은 그냥 무시하기 때문입니다. 또한 금액을 키로 결합하기 전에 ROUND 함수를 사용해 소수점 둘째 자리까지 반올림하면 앞서 언급한 부동 소수점 불일치 문제를 방지할 수 있습니다.

이 방법은 가장 널리 쓰이며 대부분의 경우 잘 작동합니다. 하지만 구조적인 한계가 있습니다. 양쪽 데이터에서 동일한 의미를 갖는 키가 필요하다는 점입니다. 참조 번호 형식이 다르거나 수수료가 두 행으로 나뉘어 있는 경우에는 이 방법이 즉시 무력화됩니다.

옵션 3: 네 가지 차이점 유형을 의도적으로 처리하기

한 번에 대조하려고 하지 말고, 범위를 좁혀 네 가지 검사를 차례로 실행합니다. 누락된 행은 양방향 조회를 통해 찾고, 중복 항목은 키 개수 계산을 통해 찾습니다. 금액 불일치는 참조 번호만으로 먼저 매칭한 후 값을 비교하여 찾아내고, 시점 차이는 정확한 날짜 대신 특정 날짜 범위 내에서 매칭하여 찾아냅니다.

제대로만 수행된다면 이 방법이 가장 확실합니다. 대조되지 않은 각 행이 정체불명의 잔여물로 남는 대신, 명확하게 분류된 카테고리로 들어가기 때문입니다.

하지만 그만큼 손이 가장 많이 갑니다. 네 번의 검사를 수행하려면 양쪽에 각각 네 개의 보조 열이 필요하며, 내보낸 파일의 열 순서가 바뀌기라도 하면 이 모든 구조를 처음부터 다시 만들어야 합니다. 이 방법의 전제 조건인 데이터 준비 단계에 대해서는 데이터 정제 및 중복 제거 가이드를 참고하세요.

공통된 한계. 세 가지 방법 모두 한쪽의 한 행이 다른 쪽의 한 행과 일대일로 대응한다고 가정합니다. 하지만 실제로는 단 하나의 정산 내역에 40개의 거래가 포함되어 있을 수도 있고, 하나의 결제 건이 청구 금액, 수수료, 환불 금액으로 나뉘어 들어올 수도 있습니다. 두 경우 모두 키 매칭 방식으로는 해결할 실마리를 찾을 수 없습니다. 바로 여기서 아까운 오후 시간을 다 허비하게 됩니다.

Powerdrill Bloom으로 거래 대조하는 방법

1단계: 두 파일 업로드하기

원장 내역 파일과 상대방의 명세서 파일을 함께 업로드합니다. Powerdrill Bloom이 두 파일을 분석하므로, 대조를 시작하기도 전에 일치하지 않는 열 이름, 서로 다른 날짜 형식, 일관되지 않은 참조 번호 스타일 등을 바로 확인할 수 있습니다.

2단계: 자연어로 대조 조건 설명하기

네 가지 카테고리를 이름으로 직접 요청해 보세요. 한 파일에는 있지만 다른 파일에는 없는 행과 중복된 참조 번호를 찾아달라고 요청합니다. 그런 다음 설정한 허용 오차를 벗어나는 금액 불일치 항목이나 날짜가 며칠씩 차이 나는 항목을 찾아달라고 요청합니다.

그런 다음 가장 까다로운 문제를 해결할 질문을 던집니다. 한쪽의 여러 행의 합계가 다른 쪽의 단일 행과 일치하는 그룹이 무엇인지 물어보세요. 이는 기존의 키 매칭 방식으로는 해결할 수 없었던 다대일(many-to-one) 케이스입니다.

3단계: 차트, 보고서 또는 프레젠테이션 자료로 내보내기

예외 항목 목록, 카테고리별 미대조 금액 요약, 또는 마감 파일용 간단한 서면 메모를 추출합니다.

매달 대조 작업을 처음부터 다시 만드는 것보다 이 방법이 더 나은 이유

	수동 방식	Powerdrill Bloom
참조 번호 형식이 다른 경우	먼저 양쪽 데이터를 수동으로 정제해야 함	차이점을 설명하고 질문하기
다대일(여러 행 대 한 행) 매칭	수동으로 그룹화	어떤 행들의 합계가 상대 항목과 일치하는지 질문하기
중복 항목	양쪽에 별도의 개수 계산 열 추가 필요	예외 목록에 자동으로 포함됨
다음 달 작업 시	모든 보조 열을 처음부터 다시 생성	새로 내보낸 파일만 업로드

표의 첫 번째 행(참조 번호 형식 불일치)이 바로 가장 많은 시간이 소요되는 부분입니다. 두 시스템의 수치가 일치하도록 참조 번호를 정제하는 작업은 그 자체로는 아무런 결과물도 만들어내지 못하는 사전 준비 작업에 불과합니다. 게다가 파일 내보내기 형식이 바뀔 때마다 이 작업을 매번 새로 해야 합니다.

흔히 하는 실수들

합계가 일치한다고 해서 대조가 끝났다고 생각하는 것. 서로 반대 방향으로 발생한 동일한 크기의 두 오류는 합계를 완벽하게 일치시킵니다. 단순히 합계만 보지 말고, 행 개수와 미대조 금액을 반드시 확인해야 합니다.

금액만으로 대조하는 것. 실제 원장에서는 여러 거래가 동일한 금액을 가질 수 있습니다. 금액만으로 조회하면 엉뚱한 항목끼리 매칭해 놓고도 겉보기에는 완벽하게 매칭된 것처럼 보일 수 있습니다.

반올림 차이를 무시하는 것. 화면에 똑같이 표시되는 값이라도 실제 일치 여부 테스트에서는 실패할 수 있습니다. 비교하기 전에 양쪽 데이터를 동일한 자릿수로 반올림하세요.

검사 방향을 잊어버리는 것. 단방향 조회는 두 번째 파일에서 누락된 행만 찾을 뿐, 첫 번째 파일에서 누락된 행은 절대 찾지 못합니다. 매번 반드시 양방향으로 조회를 실행해야 합니다.

대조된 행을 바로바로 삭제하는 것. 효율적으로 보일 수 있지만 감사 추적(audit trail)을 망가뜨립니다. 대신 상태 열을 추가하여 행에 표시를 남기고 원본 데이터는 그대로 보존하세요.

회계 기간이 마감되기 전에 대조를 시작하는 것. 늦게 반영되는 항목들은 시간이 지나면 알아서 해결될 시점 차이를 만들어냅니다. 기간 도중에 이를 억지로 맞추려고 하면 불필요한 노동이 됩니다.

결론

거래 대조는 매칭의 문제가 아니라 분류의 문제입니다. 대조되지 않은 모든 행을 누락, 중복, 잘못된 금액, 잘못된 기간으로 분류하고 나면, 남은 작업은 아주 적고 명쾌하게 설명할 수 있게 됩니다.

대조 작업의 비용을 높이는 주범은 매달 대조 시스템을 새로 만드는 것입니다. 특히 참조 번호가 일치하지 않거나 하나의 결제가 여러 행에 걸쳐 있는 경우가 그렇습니다. 마감 작업이 매번 이 단계에서 막힌다면, 내보낸 두 파일에 Powerdrill Bloom을 사용해 보세요. 또한 VLOOKUP 없이 두 Excel 파일 병합하기 및 예산 대비 실적 보고서 작성하기 가이드도 참고해 보시기 바랍니다. 관련 워크플로우는 지출 보고서 및 현금 흐름 분석 페이지에서 확인하실 수 있습니다.

자주 묻는 질문

거래 대조(reconciliation)란 무엇인가요?

동일한 활동에 대한 두 개의 기록이 일치하는지 확인하고, 일치하지 않는 모든 차이점에 대해 이유를 규명하는 것을 의미합니다. 차이점의 원인은 누락된 행, 중복, 금액 불일치, 시점 차이의 네 가지 그룹으로 나뉩니다.

Excel에서 두 목록을 자동으로 대조할 수 있나요?

자체적으로는 불가능합니다. Excel은 주로 조회, 개수 계산, 조건부 합계 등의 구성 요소를 제공할 뿐이며, 매칭 로직은 사용자가 직접 구축해야 합니다. 또한 내보낸 파일 형식이 변경되면 로직을 다시 만들어야 합니다.

겉보기에 똑같아 보이는 두 금액이 왜 일치하지 않는 것으로 나오나요?

대개 저장된 정밀도(소수점 자리) 때문입니다. 소수점 둘째 자리까지 표시된 값이라도 실제로는 그 뒤에 더 많은 소수 자릿수가 숨겨져 있어 정확한 비교 테스트에서 실패할 수 있습니다. 양쪽 데이터를 동일한 자릿수로 반올림하면 이 문제가 해결됩니다.

여러 행으로 나뉘어 표시된 하나의 결제 건은 어떻게 처리해야 하나요?

더 작은 단위의 행들을 그룹화한 다음, 그 그룹의 합계를 단일 상대 항목과 비교합니다. 행 수준의 키 매칭 방식으로는 이러한 케이스를 처리할 수 없기 때문에, 이 작업이 수동 업무를 유발하는 가장 흔한 원인이 됩니다.

대조되지 않은 행은 삭제해야 하나요?

아닙니다. 그대로 유지하고 상태 열을 추가하여 카테고리와 사유를 기록해 두어야 합니다. 데이터를 삭제하면 나중에 대조 결과의 정당성을 입증할 수 있는 감사 추적(audit trail)이 사라지게 됩니다.