재고 실사 차이가 안 잡힐 때: 입출고 원장으로 기준일 재고 역산하는 법
재고 실사를 하고 나면 꼭 몇 개 품목에서 장부 수량과 실사 수량이 맞지 않습니다. 문제는 차이가 난다는 사실보다, 그 차이가 어디서 생겼는지 설명하기 어렵다는 데 있습니다. 창고에서는 “분명히 출고했다”고 하고, 영업팀에서는 “그 건은 아직 취소 처리 전”이라고 하고, 구매팀에서는 “입고일은 어제지만 전표 반영은 오늘”이라고 말하는 식입니다. 이럴 때 단순히 현재고 셀 하나만 맞추려고 하면 다음 달에 같은 문제가 반복됩니다.
실무에서는 기준일을 정해 놓고, 입고·출고·반품·조정 내역을 한 줄 원장으로 모은 뒤 ‘기초재고 + 입고 - 출고 ± 조정 = 장부재고’ 형태로 다시 계산해 보는 방식이 가장 안정적입니다. 오늘은 재고 실사표에서 차이를 잡을 때 바로 써먹을 수 있는 구조를 기준으로, 함수와 표 기능, Power Query 정리 방식, 그리고 반복 새로고침을 편하게 하는 작은 자동화 팁까지 같이 정리해 보겠습니다.
재고 차이는 현재고표보다 거래 원장에서 먼저 봐야 합니다
현재고표는 결과표입니다. 이미 계산된 숫자만 보여 주기 때문에, 어디에서 틀어졌는지 찾으려면 결국 원장으로 돌아가야 합니다. 특히 아래 같은 상황에서는 현재고표만 보고는 원인을 거의 찾기 어렵습니다.
| 상황 | 자주 생기는 문제 | 확인해야 할 기준 |
|---|---|---|
| 월말 실사 | 전표일과 실제 입출고일이 다름 | 재고 기준일을 어떤 날짜로 볼지 |
| 온라인 주문 출고 | 취소·반품이 별도 파일에 있음 | 수량 부호를 통일했는지 |
| 창고 간 이동 | 출고 창고와 입고 창고이 동시에 반영되지 않음 | 창고 기준 재고인지 전체 재고인지 |
| 세트 상품 | 판매 품목과 재고 품목 코드가 다름 | 품목 매핑표가 있는지 |
그래서 저는 재고 차이 검증용 파일을 만들 때 보통 시트를 네 개로 나눕니다. 품목마스터, 기초재고, 입출고원장, 실사검증입니다. 이 네 가지가 분리되어 있으면 원본이 바뀌어도 계산 구조가 크게 흔들리지 않습니다.
입고와 출고를 한 열로 합치면 계산이 훨씬 단단해집니다
재고 파일에서 가장 흔한 실수는 입고수량, 출고수량, 반품수량, 조정수량을 각각 다른 열로 두고 계속 더하고 빼는 방식입니다. 처음에는 보기 편하지만, 나중에 거래 유형이 하나 추가되면 수식이 길어지고 누락이 생깁니다. 대신 입출고 원장에는 수량과 재고반영수량을 구분해서 두는 편이 좋습니다.
| 거래일 | 창고 | 품목코드 | 거래유형 | 수량 | 재고반영수량 |
|---|---|---|---|---|---|
| 2026-06-03 | A창고 | P-1001 | 입고 | 120 | 120 |
| 2026-06-05 | A창고 | P-1001 | 출고 | 35 | -35 |
| 2026-06-07 | A창고 | P-1001 | 고객반품 | 3 | 3 |
| 2026-06-10 | A창고 | P-1001 | 폐기조정 | 2 | -2 |
핵심은 거래유형별 부호를 한 번만 정해 두는 것입니다. 이 부호를 수식으로 직접 넣어도 되지만, 실무에서는 별도의 부호표를 만들어 XLOOKUP으로 가져오는 편이 안전합니다. 거래유형명이 조금 바뀌어도 부호표에서만 관리하면 되기 때문입니다.
아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 일부 동적 배열 함수 지원 여부가 다를 수 있으니, 사용 중인 버전에서 함수 지원 여부를 먼저 확인해 주세요.
=IFERROR([@수량] * XLOOKUP([@거래유형], 부호표[거래유형], 부호표[부호]), 0)여기서 IFERROR를 넣는 이유는 거래유형이 부호표에 없을 때 계산이 멈추지 않게 하기 위해서입니다. 다만 오류를 0으로 덮어버리면 누락을 놓칠 수 있으니, 별도 확인 열도 같이 두는 것이 좋습니다.
=IF(COUNTIF(부호표[거래유형], [@거래유형])=0, "부호표 확인", "")기준일 재고는 SUMIFS로 역산하면 설명이 쉬워집니다
실사검증 시트에서는 품목코드와 창고를 기준으로 기초재고, 기간 입출고, 장부재고, 실사수량, 차이를 나란히 배치합니다. 이 표가 있으면 “왜 7개 차이 나는지”를 회의 자리에서 바로 설명할 수 있습니다.
| 창고 | 품목코드 | 기초재고 | 기간입출고 | 장부재고 | 실사수량 | 차이 |
|---|---|---|---|---|---|---|
| A창고 | P-1001 | 80 | 86 | 166 | 164 | -2 |
| A창고 | P-1002 | 40 | -15 | 25 | 25 | 0 |
기초재고는 창고와 품목코드가 모두 맞는 값을 가져와야 합니다. 품목코드만으로 찾으면 여러 창고 재고가 섞이는 사고가 자주 납니다. XLOOKUP 하나로 처리하려면 보조키를 만들거나, SUMIFS를 쓰는 편이 깔끔합니다.
=SUMIFS(기초재고[수량], 기초재고[창고], [@창고], 기초재고[품목코드], [@품목코드])기간 입출고는 기준 시작일과 종료일을 같이 걸어 줍니다. 월말 실사라면 시작일은 월초, 종료일은 실사 기준일로 두면 됩니다.
=SUMIFS(입출고원장[재고반영수량], 입출고원장[창고], [@창고], 입출고원장[품목코드], [@품목코드], 입출고원장[거래일], ">="&$B$1, 입출고원장[거래일], "<="&$B$2)장부재고와 차이는 단순합니다.
장부재고 = 기초재고 + 기간입출고
차이 = 실사수량 - 장부재고이 구조의 장점은 기준일을 바꾸면 결과가 즉시 바뀐다는 점입니다. 예를 들어 6월 30일 실사인 줄 알았는데 실제 마감 기준이 6월 29일 18시였다면, 종료일 조건만 바꿔서 다시 계산할 수 있습니다. 출고 전표 하나를 손으로 빼고 더하는 방식보다 훨씬 추적이 쉽습니다.
차이가 나는 품목만 보는 보고서 형태로 바꾸면 회의 시간이 줄어듭니다
실사검증표가 완성되면 모든 품목을 다 볼 필요는 없습니다. 차이가 0이 아닌 품목, 또는 차이 금액이 큰 품목부터 확인하는 편이 효율적입니다. 단가가 있다면 차이수량에 표준단가를 곱해 차이금액을 만들고, 조건부 서식으로 강조해 두면 재고회의용 보고서가 됩니다.
=IF([@차이]=0, "일치", IF(ABS([@차이금액])>=100000, "우선확인", "확인"))여기서 실무 팁이 하나 있습니다. 재고 차이를 수량 기준으로만 정렬하면 중요도가 낮은 소모품이 위로 올라올 때가 많습니다. 반대로 차이금액 기준으로만 보면 수량 차이가 큰 품목을 놓칠 수 있습니다. 그래서 저는 보통 상태, 차이금액 절댓값, 차이수량 절댓값 순으로 정렬합니다.
=ABS([@차이금액])피벗테이블을 붙이면 창고별, 담당자별, 거래유형별로 차이를 압축해서 볼 수 있습니다. 다만 피벗으로 원인을 찾기 전에 반드시 원장 표의 날짜 형식과 품목코드 형식부터 확인해야 합니다. 날짜가 텍스트로 들어가 있으면 기간 조건이 엉뚱하게 계산되고, 품목코드 앞자리 0이 사라지면 마스터와 매칭되지 않습니다.
원본 파일이 여러 개라면 Power Query로 먼저 모아 두는 편이 안전합니다
입고 내역은 구매팀 파일, 출고 내역은 물류팀 파일, 반품 내역은 CS팀 파일에 따로 있는 경우가 많습니다. 이 파일들을 복사해서 한 시트에 붙이면 한 달은 버틸 수 있지만, 다음 달부터는 누락과 중복이 생기기 쉽습니다. 이럴 때는 Power Query에서 각 원본을 불러온 뒤 열 이름을 맞추고, 추가 쿼리로 한 원장으로 합치는 방식이 좋습니다.
Power Query에서 맞춰야 할 핵심 열은 많지 않습니다. 거래일, 창고, 품목코드, 거래유형, 수량 정도만 일단 통일하면 됩니다. 이후 엑셀 표에서 부호표를 연결해 재고반영수량을 계산해도 되고, 쿼리 안에서 조건 열로 부호를 붙여도 됩니다.
| 원본 파일 | 원본 열 이름 예시 | 통일할 열 이름 |
|---|---|---|
| 입고파일 | 입고일자 | 거래일 |
| 출고파일 | 출하일 | 거래일 |
| 반품파일 | 반품접수일 | 거래일 |
| 조정파일 | 처리일 | 거래일 |
Power Query를 쓸 때 흔한 실수는 열 이름을 원본 그대로 둔 채로 추가 쿼리를 하는 것입니다. 입고일자와 출하일은 사람 눈에는 같은 날짜 열처럼 보이지만, 쿼리에서는 서로 다른 열입니다. 추가한 뒤 날짜 열이 여러 개 생겼다면 대부분 이 문제입니다. 추가 전에 열 이름을 먼저 통일해야 합니다.
실사 차이를 확인할 때는 이 순서로 의심하면 빠릅니다
재고 차이가 났을 때 무작정 원장을 내려보면 시간이 오래 걸립니다. 아래 순서로 확인하면 원인이 꽤 빨리 좁혀집니다.
- 날짜 기준: 거래일이 기준일 안에 들어오는지, 전표일과 실제 이동일이 섞였는지 확인합니다.
- 창고 기준: 전체 재고를 봐야 하는데 특정 창고만 계산했거나, 반대로 창고 이동을 전체 입출고처럼 계산했는지 확인합니다.
- 품목코드 기준: 세트 상품, 대체 코드, 앞자리 0 누락, 공백 문자를 확인합니다.
- 부호 기준: 반품이 플러스인지 마이너스인지, 폐기와 재고조정의 방향이 맞는지 확인합니다.
- 중복 기준: 같은 전표번호가 두 번 들어왔는지, 취소 건과 원거래가 동시에 살아 있는지 확인합니다.
특히 전표번호가 있다면 중복 확인 열을 하나 추가해 두는 것이 좋습니다. 거래일, 창고, 품목코드, 전표번호가 같은 행이 여러 번 들어오면 의심 표시를 띄우는 식입니다.
=IF(COUNTIFS(입출고원장[전표번호], [@전표번호], 입출고원장[품목코드], [@품목코드], 입출고원장[창고], [@창고])>1, "중복의심", "")품목코드 공백도 생각보다 많이 나옵니다. 겉으로는 P-1001로 보여도 뒤에 공백이 붙어 있으면 마스터와 매칭되지 않습니다. 원장에 정리용 품목코드 열을 하나 두고 TRIM과 CLEAN을 같이 쓰면 이런 문제를 줄일 수 있습니다.
=TRIM(CLEAN([@품목코드]))매달 반복되는 검증은 새로고침 버튼 하나로 줄일 수 있습니다
원본을 Power Query로 연결해 두었다면, 매달 할 일은 새 파일을 폴더에 넣고 새로고침하는 정도로 줄어듭니다. 보고서 파일을 여러 사람이 쓰는 환경이라면 리본 메뉴를 찾지 않아도 되도록 간단한 매크로 버튼을 하나 만들어 두면 편합니다.
Sub 재고검증_전체새로고침()
ThisWorkbook.RefreshAll
MsgBox "재고 원장과 검증표 새로고침이 완료되었습니다.", vbInformation
End Sub단, 매크로를 쓰는 파일은 저장 형식을 xlsm으로 해야 합니다. 또한 쿼리 새로고침이 끝나기 전에 피벗이나 수식 결과를 확인하면 잠깐 이전 값이 보일 수 있으니, 데이터 양이 많다면 새로고침 후 상태 표시줄이 멈췄는지 확인하는 습관이 필요합니다.
다음 달에도 같은 방식으로 확인하려면
재고 실사 차이를 줄이는 핵심은 ‘이번 달 숫자를 억지로 맞추는 것’이 아니라, 다음 달에도 같은 기준으로 다시 검증할 수 있는 표를 만드는 것입니다. 입출고를 한 줄 원장으로 모으고, 거래유형별 부호를 관리하고, 기준일 조건으로 장부재고를 역산하면 차이가 난 품목을 설명하기가 훨씬 쉬워집니다.
처음 세팅할 때는 품목마스터와 부호표를 만드는 시간이 조금 걸리지만, 한 번 만들어 두면 월말마다 복사·붙여넣기와 눈대중 검산에 쓰던 시간을 크게 줄일 수 있습니다. 재고 차이가 반복되는 파일이라면 현재고표부터 고치기보다, 입출고 원장의 열 구조와 부호 기준부터 점검해 보세요. 숫자가 틀린 위치보다 숫자가 만들어지는 길을 잡는 것이 훨씬 오래갑니다.