Excel 請款與收款核對表：合計分批收款並找出差額
엑셀 청구·입금 대조표: 나눠 받은 입금 합산하고 확인할 차액 찾기
作者提供一套 Excel 請款與收款核對表，透過兩張工作表及 SUMIF、COUNTIF 公式，合計同一請款 ID 的分批收款、檢查 ID 並計算差額。範例涵蓋 4 筆請款與 6 筆收款，修正一處 ID 後，四筆差額為 0、10,000、60,000 和 −10,000 韓元。
청구는 한 건인데 입금이 두 번에 나뉘어 들어오면, 청구 금액 옆에 마지막 입금액만 적어서는 맞는지 알기 어렵습니다. 청구 목록과 입금 기록을 나눠 두고 같은 청구ID의 입금을 합산하면, 금액이 맞는 건과 원자료를 다시 볼 건을 구분할 수 있습니다.
아래 두 시트를 만들면 청구 네 건과 입금 여섯 줄을 대조할 수 있습니다. 예제의 잘못 입력한 청구ID 한 곳을 원자료와 대조해 고친 뒤에는 차액이 0원, 10,000원, 60,000원, −10,000원으로 나옵니다. 입력 표, 수식, 완성 결과와 재사용 순서를 본문에 모두 넣었습니다.
모든 업무명·ID·금액은 합성 자료입니다. AI의 도움을 받아 표와 설명을 작성했고, 수식의 참조 범위와 예제 계산은 Python으로 검산했습니다. 실제 Excel 앱에서 붙여 넣기나 실행을 시험한 것은 아닙니다. 아래 결과는 이 예제의 계산값이며 실제 거래에 대한 확인이나 처리 지시가 아닙니다.
1. 청구는 한 줄, 입금은 들어온 기록마다 한 줄
새 통합문서에 이름이 정확히 ‘청구’, ‘입금’인 두 시트를 만듭니다. ‘청구’에는 청구ID 하나를 한 줄만 적습니다. ‘입금’에는 입금ID를 줄마다 다르게 적고, 어느 청구에 해당하는지 청구ID로 연결합니다.
한 청구를 나눠 받은 경우에는 같은 청구ID가 입금 시트에 여러 번 나오는 것이 정상입니다. 반복되면 확인할 것은 입금ID입니다. 여기서 입금ID는 이 표에서 한 기록을 구별하기 위해 붙인 고유 ID이며, 은행 거래번호의 진위를 판정하는 기능은 없습니다. 같은 입금을 다른 입금ID로 두 번 적은 경우도 수식만으로 찾아내지 못합니다.
ID 열은 붙여 넣기 전에 셀 표시 형식을 텍스트로 지정합니다. 이 예제처럼 영문 대문자·숫자·하이픈만 쓰고, 앞뒤 공백이나 *, ?, ~는 넣지 않습니다. COUNTIF와 SUMIF의 조건에는 와일드카드가 쓰일 수 있고, COUNTIF는 대소문자를 구별하지 않으므로 대소문자만 다른 ID를 별개로 만들지 않습니다.
금액은 원 단위의 양의 정수를 숫자로 입력합니다. ‘100000원’이나 앞에 작은따옴표가 붙은 텍스트 금액은 쓰지 않습니다. 채운 행의 A~C열을 모두 입력하고, 사용하지 않는 행은 A~C열을 모두 비웁니다. 아래 검사는 ID 관계를 보는 검사이므로 금액의 숫자 형식·누락·원자료 일치는 사용자가 먼저 확인해야 합니다.
2. 예제 입력 표를 A1에 붙여 넣기
다음 첫 번째 블록을 ‘청구’ 시트 A1에, 두 번째 블록을 ‘입금’ 시트 A1에 붙입니다. 항목 구분자는 세로선 |입니다. 먼저 제목을 포함한 각 줄이 A열의 한 행씩 차지하는지 확인합니다. 청구는 A1:A5, 입금은 A1:A7을 선택하고 데이터 → 텍스트 나누기 → 구분 기호로 분리에서 기타에 |를 넣습니다.
다른 구분 기호와 ‘연속된 구분 기호를 하나로 처리’ 옵션은 해제하고, 미리보기에서 열을 확인합니다. 청구 A열과 입금 A·B열의 열 데이터 서식은 텍스트로, 두 시트의 C열은 일반으로 지정합니다. 메뉴 이름과 위치는 Excel 버전에 따라 다를 수 있습니다.
분리한 결과는 청구 A1:G5, 입금 A1:E7이어야 합니다. C열 값이 숫자로 읽히는지, ID 앞뒤에 공백이 생기지 않았는지 확인합니다. D열 이후의 빈 계산 칸은 다음 단계에서 채웁니다.
청구ID|업무명|청구액|입금합계|차액|대조결과|청구ID확인
C-001|자료 제작 A|100000||||
C-002|자료 제작 B|80000||||
C-003|자료 제작 C|60000||||
C-004|자료 제작 D|100000||||
입금ID|청구ID|입금액|입금ID확인|청구ID확인
P-001|C-001|40000||
P-002|C-001|60000||
P-003|C-002|50000||
P-004|C-004|60000||
P-005|C-004|50000||
P-006|C-999|20000||
입금 시트 7행의 C-999는 일부러 넣은 입력 오류입니다. 이 예제의 합성 원자료에는 “P-006 / 대상 청구 C-002 / 입금액 20000”이라고 적혀 있다고 가정합니다. 처음에는 C-999를 그대로 두고, 검사 열이 이 행을 찾아내는지 확인합니다. 실제 자료에서는 금액이 비슷하다는 이유로 청구ID를 추정해 바꾸지 않습니다.
3. 검사와 합산 수식 넣기
각 수식은 표시된 셀에 한 줄 전체를 넣습니다. 셀 주소 설명은 복사하지 않습니다. 수식이 문자로 표시되면 수식 칸의 표시 형식을 일반으로 바꾸고 다시 입력합니다. 지역 설정에 따라 함수 인수 구분자가 세미콜론인 환경에서는 아래 쉼표를 해당 구분자로 바꿔야 할 수 있습니다.
먼저 ‘입금’ 시트에 다음 두 수식을 넣습니다. D2:E2를 선택한 뒤 D101:E101까지 아래로 채웁니다. D열은 입금ID가 비었거나 중복인지, E열은 청구ID가 청구 목록에 정확히 한 번 존재하는지 검사합니다.
입금!D2 셀
=IF(COUNTA(A2:C2)=0,"",IF(A2="","확인",IF(COUNTIF($A$2:$A$101,A2)=1,"정상","확인")))
입금!E2 셀
=IF(COUNTA(A2:C2)=0,"",IF(B2="","확인",IF(COUNTIF(청구!$A$2:$A$101,B2)=1,"정상","확인")))
다음은 ‘청구’ 시트입니다. D2~G2에 각각 넣고 D2:G2 전체를 D101:G101까지 아래로 채웁니다. D열은 같은 청구ID의 입금액을 합산하고, E열은 청구액에서 입금합계를 뺍니다. F열은 전체 ID 검사에 문제가 남아 있으면 금액 판단을 보류합니다.
청구!D2 셀
=IF(A2="","",SUMIF(입금!$B$2:$B$101,A2,입금!$C$2:$C$101))
청구!E2 셀
=IF(A2="","",C2-D2)
청구!F2 셀
=IF(COUNTA(A2:C2)=0,"",IF($I$2>0,"ID 확인 먼저",IF(E2>0,"잔액 확인",IF(E2=0,"금액 일치","초과 확인"))))
청구!G2 셀
=IF(COUNTA(A2:C2)=0,"",IF(A2="","확인",IF(COUNTIF($A$2:$A$101,A2)=1,"정상","확인")))
마지막으로 ‘청구’ 시트 I1에 ‘ID 확인 표시 수’를 적고 I2에 아래 수식을 넣습니다. I2는 아래로 채우지 않습니다. 청구 G열과 입금 D·E열에 나온 ‘확인’ 표시의 수를 더합니다. 한 행에 두 문제가 있으면 두 표시를 세므로 거래 건수는 아닙니다.
=COUNTIF($G$2:$G$101,"확인")+COUNTIF(입금!$D$2:$E$101,"확인")
사용 범위는 두 시트 모두 2~101행, 최대 100행입니다. SUMIF의 조건 범위인 입금 B2:B101과 합계 범위인 C2:C101은 같은 크기로 맞췄습니다. 102행 이후에 기록을 추가하려면 모든 수식의 마지막 행 101을 함께 늘리고 검사·계산 수식도 그 행까지 채워야 합니다. 범위 밖 기록은 계산되지 않습니다.
4. C-999를 찾아 고친 뒤 완성 결과 읽기
처음에는 입금 E7이 ‘확인’, 청구 I2가 1이어야 합니다. 청구 F2:F5는 모두 ‘ID 확인 먼저’가 됩니다. D·E열에는 숫자가 보이지만, 미등록 청구ID의 입금이 빠진 잠정 계산이므로 이때 금액 차이를 해석하지 않습니다.
입금 E열의 확인 표시를 보고 P-006을 원자료와 대조합니다. 이 합성 예제에서는 원자료가 C-002를 가리키므로 입금 B7만 C-999에서 C-002로 고칩니다. 그러면 입금 D2:E7과 청구 G2:G5가 모두 ‘정상’, 청구 I2는 0이 됩니다. I2가 0이어도 실제 거래 누락이나 잘못 쓴 금액까지 검증됐다는 뜻은 아닙니다.
청구ID | 청구액 | 입금합계 | 차액 | 대조결과
C-001 | 100000 | 100000 | 0 | 금액 일치
C-002 | 80000 | 70000 | 10000 | 잔액 확인
C-003 | 60000 | 0 | 60000 | 잔액 확인
C-004 | 100000 | 110000 | -10000 | 초과 확인
C-001의 40,000원과 60,000원은 더해서 100,000원입니다. C-002는 50,000원과 수정한 20,000원이 합쳐져 70,000원이 됩니다. C-003은 연결된 입금 기록이 없어 0원이며, C-004는 60,000원과 50,000원이 더해져 청구액보다 10,000원 많습니다.
차액이 양수이면 이 표에 적힌 청구액보다 입금합계가 적고, 0이면 두 금액이 같습니다. 음수이면 입금합계가 더 큽니다. ‘잔액 확인’에는 입금 기록이 전혀 없는 경우도 포함합니다. 입금 누락·다른 청구에 잘못 연결한 기록·청구액 변경 등을 원자료와 대조한 뒤 담당자가 다음 행동을 정해야 합니다. 이 표가 미수금 확정, 납부 완료 승인, 환불 지시를 대신하지 않습니다.
보조 검산으로 청구액 합계는 340,000원, 수정 후 입금액 합계는 280,000원, 차액 합계는 60,000원입니다. 다만 총합만 같아도 서로 다른 청구에 잘못 연결됐을 수 있습니다. 합계가 맞는지와 각 ID의 연결이 맞는지를 함께 봅니다.
5. 내 자료로 다시 쓰는 빈 양식
예제를 보관한 뒤 복사본을 만들어 씁니다. 복사본에서 두 시트의 A2:C101 입력값만 지우고, 1행 제목과 D열 이후 수식, 청구 I1:I2는 남깁니다. 이렇게 하면 검사와 계산이 있는 빈 양식이 됩니다. 아래는 입력 칸을 다시 확인하기 위한 빈 머리글입니다. 처음부터 새로 만들 때는 A1에 붙여 2절처럼 |로 열을 나누고, 3절의 수식도 다시 넣어야 합니다.
청구ID|업무명|청구액|입금합계|차액|대조결과|청구ID확인
입금ID|청구ID|입금액|입금ID확인|청구ID확인
[재사용 순서]
1) 이번에 비교할 자료의 기준일과 포함 범위를 정하고 원자료를 준비한다.
2) 청구 A~C열에 청구ID·업무명·청구액을 입력한다.
3) 입금 A~C열에 고유한 입금ID·대상 청구ID·입금액을 입력한다.
4) 금액이 숫자인지, 채운 행의 필수 입력이 빠지지 않았는지, 원자료와 같은지 확인한다.
5) 청구 G열과 입금 D·E열의 ‘확인’을 원자료와 대조해 해결한다. 임의의 ID를 붙여 경고만 없애지 않는다.
6) 청구 I2가 0이고 입력 검사가 끝난 뒤 D~F열을 읽는다.
7) 차이가 나는 행의 원자료와 확인 내용을 별도로 남기고, 담당자가 다음 조치를 정한다.
6. 이 표가 처리하지 않는 것
한 입금이 여러 청구를 함께 결제한 경우에는 원자료의 배분 기준부터 확인해야 합니다. 이 양식은 한 입금 행을 한 청구ID에만 연결합니다. 여러 청구와 여러 입금이 서로 얽힌 배분, 상계·환불·수수료·여러 통화는 처리하지 않습니다. 단순히 입금ID를 바꾸어 금액을 나누면 원거래와의 연결을 잃을 수 있으므로 이 표만으로 배분을 확정하지 않습니다.
날짜 열과 자동 마감 기능도 없습니다. 정한 기준일까지의 자료만 넣는 일은 직접 해야 합니다. 부분 취소나 금액 변경 등으로 청구 자체가 바뀌었다면 원자료 기준을 먼저 정리합니다. ID 검사는 연결과 중복 표시를 돕는 기능이고, 거래의 진실성·회계 처리·세무 처리를 판정하는 기능이 아닙니다.
처음 사용할 때는 위 합성 예제를 그대로 입력해 검사 표시와 네 줄의 결과를 비교한 뒤 자신의 자료로 바꿔 보세요. 결과가 다르면 ID·금액 형식·시트 이름·수식 범위부터 확인하고, 맞지 않는 상태에서 실제 업무 판단에 쓰지 않습니다.
공식 자료와 확인 범위
Microsoft 공식 문서에서 SUMIF의 조건별 합산과 범위 사용, COUNTIF의 조건별 개수 계산과 텍스트 조건 주의사항을 확인했습니다(2026-10-19). 이 글의 두 시트 구조와 합성 데이터, 상태 문구는 해당 문서의 예제를 옮긴 것이 아니라 별도로 구성했습니다.
來源：DEV Community · dev.to