엑셀 유통기한 계산이 밀릴 때: 로트번호 제조일자·품목별 보관개월 VLOOKUP 검산법

냉장·식품·화장품·소모품 재고 파일을 다루다 보면 “이번 달 유통기한 임박 재고가 왜 안 잡히지?”라는 질문이 자주 나옵니다. 특히 로트번호 안에 제조일자가 들어 있고, 품목마다 보관개월이 다른 파일에서는 VLOOKUP #N/A, 날짜 밀림, SUMIFS 0 나옴이 한꺼번에 터지는 경우가 많습니다.

이번 예제는 입출고현황 시트에서 로트번호를 보고 제조일자를 뽑은 뒤, 품목마스터의 보관개월을 붙여 유통기한을 계산하고, 기준월별 만료예정수량과 폐기충당금을 검산하는 흐름입니다. 함수는 최대한 실무에서 많이 쓰는 TEXT, MID, DATE, EOMONTH, VLOOKUP, INDEX, MATCH, IFERROR, SUMIFS, COUNTIFS, ROUND 조합으로 풀어보겠습니다.

Q. 제 파일 구조는 이런데, 어디부터 봐야 하나요?

입출고현황 시트에는 A3:M3000 범위에 재고 데이터가 있다고 가정하겠습니다. 3행은 제목이고, 실제 데이터는 4행부터 시작합니다. 품목마스터는 같은 시트의 O3:S100 범위에 따로 있습니다.

여기서 자주 생기는 문제는 B열 품목코드입니다. 어떤 행은 001205처럼 앞자리 0이 살아 있고, 어떤 행은 4207처럼 숫자로 들어와 앞자리 0이 사라져 있습니다. 품목마스터의 O열 품목코드가 6자리 텍스트라면 이 상태로 VLOOKUP을 걸었을 때 일부 행에서 #N/A가 납니다.

Q. VLOOKUP #N/A가 나는 품목코드는 어떻게 맞추나요?

먼저 H열에 ‘정리품목코드’를 하나 만듭니다. H3에는 정리품목코드라고 입력하고, H4에 아래 수식을 넣은 뒤 H3000까지 복사합니다.

=IFERROR(TEXT(B4,"000000"),B4)

이 수식은 B4가 4207처럼 숫자로 들어온 경우 004207로 맞춰줍니다. B4가 이미 001205처럼 보이더라도 실제 값이 숫자 1205인 경우가 있으니, 품목마스터가 6자리 코드라면 이 보조열을 만들어두는 편이 안전합니다.

주의할 점은 품목코드가 A1205처럼 문자와 숫자가 섞인 구조라면 이 방식이 맞지 않습니다. 이번 예제처럼 품목코드가 원래 6자리 숫자 코드인데 앞자리 0만 사라지는 파일에 적용하면 됩니다.

Q. 로트번호 FD-240715-B07에서 제조일자는 어떻게 뽑나요?

이번 로트번호는 앞 2글자가 구분코드, 하이픈 다음 6자리가 제조일자입니다. 예를 들어 FD-240715-B07은 2024년 7월 15일 제조라는 뜻입니다.

I3에는 제조일자라고 입력하고, I4에 아래 수식을 넣습니다.

=IFERROR(DATE(2000+MID(D4,4,2),MID(D4,6,2),MID(D4,8,2)),"로트확인")

MID(D4,4,2)는 연도 24를 뽑고, MID(D4,6,2)는 월 07, MID(D4,8,2)는 일 15를 뽑습니다. DATE 함수로 묶으면 텍스트 조각이 실제 날짜값으로 바뀝니다.

만약 결과가 로트확인으로 나오면 로트번호 자릿수가 다르거나, 중간에 공백이 있거나, 날짜로 만들 수 없는 값이 들어간 것입니다. 이때는 D열에서 해당 로트번호를 먼저 확인해야 합니다.

Q. 품목별 보관개월은 VLOOKUP으로 붙이면 되나요?

네. J3에는 보관개월이라고 입력하고, J4에 아래 수식을 넣습니다. 조회 기준은 방금 만든 H열 정리품목코드입니다.

=IFERROR(VLOOKUP($H4,$O$4:$S$100,3,FALSE),"마스터없음")

VLOOKUP의 세 번째 인수 3은 O:S 범위에서 세 번째 열인 Q열 보관개월을 가져오라는 뜻입니다. 여기서 FALSE를 빼면 비슷한 코드가 잘못 붙을 수 있으니, 품목코드처럼 정확히 일치해야 하는 값은 반드시 FALSE를 넣습니다.

같은 결과를 INDEX와 MATCH로 쓰고 싶다면 아래 수식도 가능합니다. 품목코드가 마스터의 맨 왼쪽에 있지 않은 파일에서는 INDEX, MATCH 방식이 더 다루기 편합니다.

=IFERROR(INDEX($Q$4:$Q$100,MATCH($H4,$O$4:$O$100,0)),"마스터없음")

Microsoft 365 또는 Excel 2021 이상을 쓰고 있다면 XLOOKUP도 쓸 수 있습니다. 다만 거래처나 현장에서 구버전 엑셀 파일을 주고받는 경우가 있다면 VLOOKUP 또는 INDEX, MATCH 수식을 알아두는 편이 안전합니다.

=IFERROR(XLOOKUP($H4,$O$4:$O$100,$Q$4:$Q$100),"마스터없음")

Q. 유통기한이 한 달씩 밀리는 이유는 뭔가요?

가장 흔한 원인은 보관개월을 더할 때 단순히 일수로 계산하는 것입니다. 예를 들어 제조일자에 365를 더하거나, 보관개월에 30을 곱하면 월말 기준에서 날짜가 조금씩 어긋납니다.

실무에서는 “제조일로부터 24개월이 속한 달의 말일”처럼 관리하는 경우가 많습니다. 이때는 K3에 유통기한을 입력하고, K4에 EOMONTH 수식을 넣습니다.

=IFERROR(EOMONTH(I4,J4),"")

I4가 2024-07-15이고 J4가 24라면 결과는 2026-07-31이 됩니다. 제조일자에 정확히 24개월을 더한 뒤 그 달의 말일로 맞추는 방식이라, 월별 유통기한 집계에 잘 맞습니다.

만약 회사 기준이 “제조일자 기준 정확히 24개월 뒤 전일”이라면 기준이 달라집니다. 그 경우에는 EOMONTH가 아니라 별도 규칙으로 계산해야 하므로, 품질팀이나 물류팀의 관리 기준을 먼저 확인하는 것이 좋습니다.

Q. 이번 달 유통기한 임박 재고 합계가 0으로 나옵니다. SUMIFS 조건은 어떻게 잡나요?

보고 영역은 T2:W3에 둔다고 하겠습니다. T2에는 기준월을 입력합니다. 예를 들어 2026년 7월을 보고 싶다면 T2에는 2026-07-01처럼 그 달의 첫날을 날짜로 입력합니다. U2에는 특정 품목코드를 입력하거나, 전체 품목을 보려면 빈칸으로 둡니다.

V2에는 이번 달 유통기한에 걸리는 현재고 합계를 구합니다. U2가 비어 있으면 전체 품목, U2에 코드가 있으면 해당 품목만 집계합니다.

=IF($U$2="",SUMIFS($G$4:$G$3000,$K$4:$K$3000,">="&$T$2,$K$4:$K$3000,"<="&EOMONTH($T$2,0)),SUMIFS($G$4:$G$3000,$H$4:$H$3000,TEXT($U$2,"000000"),$K$4:$K$3000,">="&$T$2,$K$4:$K$3000,"<="&EOMONTH($T$2,0)))

SUMIFS가 0으로 나온다면 K열 유통기한이 진짜 날짜인지 먼저 확인합니다. 셀 서식만 날짜처럼 보이고 실제로는 텍스트인 경우, 날짜 조건에 걸리지 않습니다. K열을 선택했을 때 수식 입력줄에 2026-07-31 같은 날짜값이 보이는지 확인해보세요.

Q. 폐기충당금까지 같이 계산하려면 어떻게 하나요?

L3에는 재고단가, M3에는 폐기충당금이라고 입력합니다. L4에는 품목마스터의 R열 재고단가를 가져옵니다.

=IFERROR(VLOOKUP($H4,$O$4:$S$100,4,FALSE),0)

M4에는 현재고 × 재고단가 × 폐기율을 계산합니다. 원 단위로 반올림하려면 ROUND를 함께 씁니다.

=IFERROR(ROUND($G4*$L4*VLOOKUP($H4,$O$4:$S$100,5,FALSE),0),0)

이제 W2에는 기준월의 폐기충당금 합계를 구합니다.

=IF($U$2="",SUMIFS($M$4:$M$3000,$K$4:$K$3000,">="&$T$2,$K$4:$K$3000,"<="&EOMONTH($T$2,0)),SUMIFS($M$4:$M$3000,$H$4:$H$3000,TEXT($U$2,"000000"),$K$4:$K$3000,">="&$T$2,$K$4:$K$3000,"<="&EOMONTH($T$2,0)))

Q. 결과를 믿기 전에 어떤 순서로 검산하면 좋나요?

첫째, 마스터 누락부터 확인합니다. J열 보관개월에 마스터없음이 하나라도 있으면 유통기한 계산에서 빠집니다. 빈 셀 하나 때문에 합계가 달라지는 일이 꽤 많습니다.

=COUNTIFS($J$4:$J$3000,"마스터없음")

둘째, 로트번호 오류를 셉니다. I열 제조일자에 로트확인이 있으면 해당 행은 유통기한을 만들 수 없습니다.

=COUNTIFS($I$4:$I$3000,"로트확인")

셋째, 품목마스터의 중복코드를 확인합니다. VLOOKUP은 중복코드가 있어도 첫 번째 값을 가져오기 때문에, 마스터가 중복되어 있으면 보관개월이나 단가가 엉뚱하게 붙을 수 있습니다. T4에 아래 수식을 넣어 마스터 끝까지 복사해보세요.

=IF(COUNTIFS($O$4:$O$100,$O4)>1,"중복","")

넷째, 기준월 T2가 날짜인지 봅니다. T2에 2026.07 또는 202607처럼 입력해두면 날짜 조건이 제대로 작동하지 않을 수 있습니다. 가장 안전한 입력은 2026-07-01처럼 해당 월의 첫날을 날짜로 넣는 방식입니다.

Q. 실무에서 자주 하는 실수는 뭔가요?

품목코드를 눈으로만 확인하는 실수가 가장 많습니다. 셀에는 001205로 보이지만 실제 값은 1205일 수 있고, 반대로 텍스트 001205일 수도 있습니다. 그래서 H열처럼 정리품목코드를 별도로 만들어 집계 기준을 통일하는 것이 좋습니다.

또 하나는 유통기한 월 집계에서 TEXT(K4,"yyyy-mm") 결과를 기준으로 SUMIFS를 거는 방식입니다. 월 표시용으로는 괜찮지만, 합계 조건은 날짜의 시작일과 말일로 잡는 편이 훨씬 안정적입니다.

마지막으로 현재고가 0 이하인 행도 체크해야 합니다. 이미 모두 출고된 로트까지 유통기한 임박 목록에 남기면 보고서가 부풀려집니다. 이번 수식은 G열 현재고를 합산하므로, 원본의 현재고 계산이 먼저 맞아야 합니다.

Q. 이 방식은 언제 쓰면 가장 잘 맞나요?

로트번호 안에 제조일자가 일정한 자리수로 들어 있고, 품목별 보관개월이 마스터에서 관리되는 재고 파일이라면 이 방식이 잘 맞습니다. 특히 월말에 유통기한 임박 재고, 폐기 예상금액, 품목별 만료수량을 반복해서 뽑는 업무에 그대로 응용하기 좋습니다.

정리하면, 품목코드는 TEXT로 6자리 기준을 맞추고, 로트번호는 MID와 DATE로 실제 제조일자로 바꾼 뒤, 보관개월은 VLOOKUP 또는 INDEX, MATCH로 붙입니다. 유통기한은 EOMONTH로 월말 기준을 만들고, 보고서는 SUMIFS로 기준월 첫날부터 말일까지 집계하면 됩니다. “VLOOKUP은 되는데 SUMIFS가 0”인 파일이라면 날짜값과 코드값을 이 순서로 확인해보면 원인을 훨씬 빨리 찾을 수 있습니다.