엑셀 월말 재고금액이 ERP와 안 맞을 때: SUMIFS 날짜조건·출고부호·평가단가 검산법
엑셀 월말 재고금액이 ERP와 안 맞을 때, 의외로 단가 문제가 아니라 날짜조건과 출고 수량 부호에서 틀어지는 경우가 많습니다. 특히 입출고 내역을 필터로 걸어서 눈으로 더하고, 품목별 단가는 VLOOKUP으로 대충 붙인 뒤 금액을 계산하면 한두 품목은 꼭 차이가 납니다.
이번 예시는 월말 재고수량과 재고금액을 맞추는 실무형 방식입니다. 기존에는 필터, 복사, 붙여넣기로 처리하던 파일을 SUMIFS, COUNTIFS, XLOOKUP, INDEX MATCH, IF, IFERROR, TEXT, DATE, EOMONTH, ROUND 조합으로 바꿔 보겠습니다.
Before/After로 보는 월말 재고 정산 방식 차이
먼저 문제 상황부터 잡고 가겠습니다. 아래처럼 입출고내역 시트의 A열부터 H열까지 데이터가 있고, 5행부터 실제 거래가 입력되어 있다고 가정합니다. 데이터 범위는 A5:H2000입니다.
| 열 | 열 이름 | 예시 | 주의할 점 |
|---|---|---|---|
| A열 | 전표일 | 2026.07.03 | 텍스트 날짜일 수 있음 |
| B열 | 문서번호 | IN-260703-01 | 중복 여부 확인용 |
| C열 | 품목코드 | PD1001 | 마스터 코드와 정확히 일치해야 함 |
| D열 | 품목명 | 스틸브라켓 | 조회용, 집계 기준은 코드 |
| E열 | 구분 | 입고 / 출고 / 반품입고 / 조정출고 | 수량 부호가 갈림 |
| F열 | 수량 | 120 | 원본은 양수로 입력 |
| G열 | 입출고단가 | 2350 | 평가단가와 다를 수 있음 |
| H열 | 로트번호 | LOT260701-A | 필요 시 제조월 확인 |
품목마스터는 같은 시트의 N열부터 Q열까지 두겠습니다. N5:Q200 범위에 N열 품목코드, O열 표준품명, P열 기본단위, Q열 재고평가단가가 들어 있습니다. 그리고 월말 재고표는 S열부터 AA열까지 작성합니다.
| 기존 방식 | 개선 후 방식 |
|---|---|
| 전표일을 필터로 7월만 선택 | DATE와 EOMONTH로 월초·월말 기준일 생성 |
| 입고와 출고를 따로 복사해서 합산 | IF로 부호수량을 만든 뒤 SUMIFS로 집계 |
| 품목명을 보고 단가를 수동 확인 | XLOOKUP 또는 INDEX MATCH로 평가단가 연결 |
| 차이가 나면 전체를 다시 필터링 | COUNTIFS와 검증식으로 마스터 누락, 수량 차이 확인 |
예시 데이터: ERP와 3,760원이 안 맞는 흔한 구조
입출고 내역이 아래처럼 섞여 있다고 보겠습니다. 겉으로 보면 단순하지만, 날짜가 텍스트로 들어온 행과 조정출고가 섞이면 월말 재고금액이 쉽게 틀어집니다.
| A 전표일 | C 품목코드 | E 구분 | F 수량 | G 단가 | H 로트번호 |
|---|---|---|---|---|---|
| 2026.06.28 | PD1001 | 입고 | 100 | 2350 | LOT260628-A |
| 2026.07.03 | PD1001 | 출고 | 30 | 2350 | LOT260628-A |
| 20260708 | PD1001 | 입고 | 50 | 2380 | LOT260708-B |
| 2026.07.12 | PD1002 | 입고 | 80 | 4120 | LOT260712-A |
| 2026.07.19 | PD1002 | 조정출고 | 5 | 4120 | LOT260712-A |
| 2026.08.01 | PD1001 | 출고 | 10 | 2380 | LOT260708-B |
기준월은 P2 셀에 2026-07 형태로 입력합니다. P3에는 기준월의 월초, P4에는 기준월의 월말을 계산해 두면 SUMIFS 조건식이 훨씬 안정적입니다.
P3 셀 월초일
=DATE(LEFT($P$2,4),RIGHT($P$2,2),1)
P4 셀 월말일
=EOMONTH($P$3,0)P2를 2026/07/01 같은 날짜로 넣어도 되지만, 실무에서는 보고서 제목이나 파일명과 맞추려고 2026-07 텍스트를 많이 씁니다. 이때 LEFT, RIGHT, DATE로 월초일을 만들어 두면 월별 조건이 흔들리지 않습니다.
원인부터 잡기: SUMIFS가 0 나오거나 금액이 틀어지는 지점
월말 재고 집계에서 가장 흔한 원인은 세 가지입니다. 첫째, A열 전표일이 날짜처럼 보이지만 실제로는 텍스트입니다. 둘째, 출고 수량을 빼야 하는데 양수 그대로 합산했습니다. 셋째, 품목코드는 맞는데 평가단가 마스터에 중복 또는 누락이 있습니다.
그래서 원본 옆 I열부터 L열까지 작업용 열을 추가합니다. I열은 변환일자, J열은 월키, K열은 부호수량, L열은 로트월 확인용입니다. 원본을 지우지 않고 옆에 검산 열을 두는 것이 안전합니다.
| 열 | 이름 | 목적 |
|---|---|---|
| I열 | 변환일자 | 텍스트 날짜를 실제 날짜로 통일 |
| J열 | 월키 | yyyy-mm 형식으로 월 확인 |
| K열 | 부호수량 | 입고는 +, 출고는 - 처리 |
| L열 | 로트월 | 로트번호에서 제조월 확인 |
I5 셀에는 아래 수식을 넣고 I2000까지 내려 복사합니다. A열에 실제 날짜가 들어 있으면 그대로 쓰고, 20260708처럼 8자리 텍스트이면 DATE로 다시 만듭니다. 2026.07.08이나 2026-07-08처럼 구분자가 있는 날짜도 처리합니다.
=IF(ISNUMBER(A5),A5,IF(LEN(A5)=8,DATE(LEFT(A5,4),MID(A5,5,2),RIGHT(A5,2)),DATE(LEFT(A5,4),MID(A5,6,2),RIGHT(A5,2))))J5 셀에는 월 확인용 키를 만듭니다. 이 열은 최종 집계에는 꼭 필요하지 않지만, 필터로 특정 월만 빠르게 확인할 때 좋습니다.
=TEXT(I5,"yyyy-mm")K5 셀에는 입출고 구분에 따라 수량 부호를 바꿉니다. 원본 F열 수량은 모두 양수라고 가정하고, 입고와 반품입고는 더하고 출고와 조정출고는 빼는 방식입니다.
=IF(OR(E5="입고",E5="반품입고",E5="조정입고"),F5,-F5)L5 셀은 선택 사항이지만, 로트번호가 LOT260701-A처럼 들어오는 회사라면 제조월 확인에 꽤 유용합니다. 로트번호의 4번째 글자부터 6자리를 꺼내 날짜로 바꾼 뒤 yyyy-mm으로 표시합니다.
=IFERROR(TEXT(DATE(2000+LEFT(MID(H5,4,6),2),MID(MID(H5,4,6),3,2),RIGHT(MID(H5,4,6),2)),"yyyy-mm"),"")여기까지 해두면 Before 방식처럼 필터를 반복할 필요가 줄어듭니다. 특히 I열 변환일자가 실제 날짜인지 확인하려면 셀 서식을 일반으로 바꿨을 때 46200대 숫자처럼 보이면 정상입니다.
After 집계표 만들기: 기준월, 품목코드, 평가단가까지 한 번에
이제 S열부터 월말 재고표를 만듭니다. S5:AA5에 제목을 넣고, S6부터 품목코드를 입력합니다. 품목코드는 마스터의 N열 코드와 정확히 일치해야 합니다.
| S | T | U | V | W | X | Y | Z | AA |
|---|---|---|---|---|---|---|---|---|
| 품목코드 | 품명 | 전월이월 | 당월입고 | 당월출고 | 월말수량 | 평가단가 | 재고금액 | 검증 |
T6 셀에는 품목명을 가져옵니다. Microsoft 365나 Excel 2021 이상이면 XLOOKUP을 쓰는 편이 읽기 쉽습니다.
=IFERROR(XLOOKUP($S6,$N$5:$N$200,$O$5:$O$200),"코드없음")XLOOKUP을 쓰기 어려운 버전이라면 INDEX와 MATCH 조합으로 같은 결과를 만들 수 있습니다. 아래 수식을 T6에 넣어도 됩니다.
=IFERROR(INDEX($O$5:$O$200,MATCH($S6,$N$5:$N$200,0)),"코드없음")U6 셀은 전월이월 수량입니다. 기준월 월초인 P3보다 이전의 부호수량을 모두 더합니다. 여기서 C열 품목코드가 아니라 K열 부호수량을 더하는 것이 핵심입니다.
=SUMIFS($K$5:$K$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,"<"&$P$3)V6 셀은 당월 입고 수량입니다. 입고, 반품입고, 조정입고를 모두 입고 성격으로 보고 더합니다. 구분값이 회사마다 다르다면 마지막 조건의 문자만 자기 파일에 맞게 바꾸면 됩니다.
=SUMIFS($F$5:$F$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,">="&$P$3,$I$5:$I$2000,"<="&$P$4,$E$5:$E$2000,"입고")
+SUMIFS($F$5:$F$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,">="&$P$3,$I$5:$I$2000,"<="&$P$4,$E$5:$E$2000,"반품입고")
+SUMIFS($F$5:$F$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,">="&$P$3,$I$5:$I$2000,"<="&$P$4,$E$5:$E$2000,"조정입고")W6 셀은 당월 출고 수량입니다. 출고와 조정출고를 양수로 집계한 뒤, 월말수량 계산에서 빼는 구조로 만들겠습니다.
=SUMIFS($F$5:$F$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,">="&$P$3,$I$5:$I$2000,"<="&$P$4,$E$5:$E$2000,"출고")
+SUMIFS($F$5:$F$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,">="&$P$3,$I$5:$I$2000,"<="&$P$4,$E$5:$E$2000,"조정출고")X6 셀에는 월말수량을 계산합니다. 전월이월에 당월입고를 더하고 당월출고를 빼면 됩니다.
=U6+V6-W6Y6 셀에는 평가단가를 붙입니다. 입출고 내역의 G열 단가는 거래 당시 단가일 수 있으므로, 재고평가용 금액은 마스터의 Q열 단가를 쓰는 것으로 통일합니다.
=IFERROR(XLOOKUP($S6,$N$5:$N$200,$Q$5:$Q$200),0)역시 XLOOKUP 대신 INDEX MATCH를 쓰려면 아래처럼 바꾸면 됩니다.
=IFERROR(INDEX($Q$5:$Q$200,MATCH($S6,$N$5:$N$200,0)),0)Z6 셀은 재고금액입니다. 수량과 단가를 곱한 뒤 ROUND로 원 단위 반올림합니다. ERP가 원 단위 절사나 반올림 중 어느 기준인지에 따라 ROUND 부분만 조정하면 됩니다.
=ROUND(X6*Y6,0)검증식으로 차이 원인을 바로 표시하기
마지막으로 AA6 셀에 검증 문구를 넣습니다. 품목마스터에 코드가 없으면 마스터누락, 마스터에 중복 코드가 있으면 마스터중복, 수량이 부호수량 기준 합계와 맞으면 OK로 표시합니다.
=IF(COUNTIFS($N$5:$N$200,$S6)=0,"마스터누락",IF(COUNTIFS($N$5:$N$200,$S6)>1,"마스터중복",IF(ROUND($X6,3)=ROUND(SUMIFS($K$5:$K$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,"<="&$P$4),3),"OK","수량확인")))이 검증식의 장점은 단순히 금액만 보여주는 것이 아니라 어디를 먼저 봐야 하는지 알려준다는 점입니다. ERP와 금액이 다를 때 무작정 전체 내역을 다시 필터링하지 말고, AA열에서 마스터누락과 수량확인을 먼저 보면 시간이 많이 줄어듭니다.
SUMIFS 0 나옴, VLOOKUP 안됨을 줄이는 확인 순서
SUMIFS 결과가 0으로 나오면 가장 먼저 I열 변환일자를 확인합니다. A열이 20260708처럼 보이는데 실제로는 숫자 20,260,708로 들어온 경우에는 날짜 변환식이 의도와 다르게 작동할 수 있습니다. 이런 파일은 A열을 텍스트로 받은 것인지, 숫자로 받은 것인지부터 구분해야 합니다.
두 번째는 품목코드 앞뒤 공백입니다. S6의 품목코드가 PD1001이고 C열도 PD1001처럼 보여도, 실제로 C열 값 뒤에 공백이 있으면 SUMIFS가 못 찾습니다. 이때는 원본 코드 열을 정리한 보조열을 하나 더 만들어 쓰는 편이 안전합니다.
=IFERROR(LEFT(C5,6),C5)품목코드가 항상 6자리라면 위처럼 LEFT로 앞 6자리만 쓰는 방식도 가능합니다. 다만 회사 코드 체계가 PD1001-A, PD1001-B처럼 뒤 코드까지 의미가 있다면 무조건 자르면 안 됩니다. 이때는 마스터 코드 기준과 원본 코드 기준을 먼저 맞춰야 합니다.
세 번째는 출고 부호를 두 번 빼는 실수입니다. K열 부호수량은 이미 출고가 음수입니다. 그런데 월말수량을 계산할 때 K열로 당월출고를 집계해놓고 또 빼면 출고가 더해지는 이상한 결과가 나옵니다. 이번 예시처럼 V열과 W열은 원본 F열 양수 수량으로 집계하고, 최종 X열에서 한 번만 빼는 구조가 헷갈림이 적습니다.
실무 응용: 특정 창고, 특정 품목군까지 조건 추가하기
입출고 내역에 M열 창고코드가 추가되어 있고, S3 셀에 조회할 창고코드가 들어 있다고 가정해 보겠습니다. 그러면 전월이월 수식에 창고 조건만 하나 더 붙이면 됩니다.
=SUMIFS($K$5:$K$2000,$C$5:$C$2000,$S6,$I$5:$I$2000,"<"&$P$3,$M$5:$M$2000,$S$3)품목코드 앞 두 글자가 품목군을 의미한다면 LEFT로 품목군을 분리할 수도 있습니다. 예를 들어 PD는 제품, RM은 원재료라면 M5 셀에 아래 수식을 넣어 품목군을 만들고, COUNTIFS나 SUMIFS 조건으로 활용하면 됩니다.
=LEFT(C5,2)보고용으로 기준월을 예쁘게 표시하고 싶다면 TEXT를 쓰면 됩니다. P2가 2026-07일 때 보고서 제목 셀에 아래처럼 넣으면 2026년 07월 월말재고처럼 표시됩니다.
=TEXT($P$3,"yyyy년 mm월")&" 월말재고"차이가 날 때는 금액보다 수량을 먼저 맞추세요
월말 재고금액 차이를 잡을 때 바로 단가표부터 보는 경우가 많지만, 실제로는 수량이 먼저입니다. 수량이 1개라도 다르면 ROUND를 아무리 맞춰도 금액은 계속 어긋납니다.
이번 구조에서는 I열 변환일자, K열 부호수량, AA열 검증만 제대로 잡아도 대부분의 오류 위치가 보입니다. 그다음 평가단가가 마스터와 맞는지 확인하면 ERP 대사 시간이 훨씬 줄어듭니다.
기존 방식처럼 월마다 필터를 다시 걸고 복사해서 합산하던 파일이라면, 이번 수식을 한 번 붙여 두는 것만으로도 다음 달 정산이 편해집니다. 기준월 P2만 2026-08로 바꾸면 P3, P4, SUMIFS 결과가 같이 바뀌기 때문에 월말 재고표를 반복해서 만들 때 특히 효과가 큽니다.