엑셀 발주수량이 이상할 때: 현재고·미입고·최근 3개월 출고량으로 재고 부족 자동 계산하기

월초 발주 검토표를 만들 때 가장 자주 터지는 엑셀 오류가 있습니다. 현재고는 분명 남아 있는데 발주수량이 과하게 나오거나, 반대로 재고가 부족한 상품인데 발주대상에서 빠지는 증상입니다. 특히 창고별 재고, 입고예정, 최근 출고량을 따로 관리하는 파일에서는 SUMIFS 0 나옴, VLOOKUP 안됨, 날짜 조건 누락이 한꺼번에 섞여서 원인을 찾기 어렵습니다.

이번 예제는 재고 담당자가 매주 만드는 발주 검토표 상황으로 잡아보겠습니다. 목표는 기준일 기준 직전 3개월 출고량을 구하고, 현재고와 미입고수량을 반영해서 추천 발주수량을 자동 계산하는 것입니다. 낯선 기능으로 한 번에 끝내기보다, 실무 파일에서 가장 많이 쓰는 SUMIFS, VLOOKUP, XLOOKUP, INDEX, MATCH, IF, IFERROR, LEFT, DATE, EOMONTH, ROUND 조합으로 차근차근 맞춰보겠습니다.

발주표가 틀어지는 실제 구조부터 확인하기

예제 파일에는 시트가 4개 있다고 가정하겠습니다. 시트 이름은 발주검토, 재고현황, 입고예정, 출고원장, 상품마스터입니다. 발주검토 시트의 B1에는 기준일, B2에는 창고명을 입력합니다.

발주검토 시트는 아래처럼 구성합니다. B1에는 2026-07-05, B2에는 서울센터가 들어가 있다고 가정하겠습니다.

SUMIFS가 0으로 나오는 원인은 날짜와 상품코드가 먼저입니다

출고원장에서 A열 출고일이 날짜처럼 보이더라도 실제로는 20260401 같은 텍스트일 때가 많습니다. 이 상태로 SUMIFS 날짜 조건을 걸면 결과가 0으로 나오는 경우가 생깁니다. 그래서 출고원장 H열에 계산용 날짜를 하나 만들겠습니다.

출고원장 시트의 H3에는 출고일_계산용이라고 적고, H4에 아래 수식을 넣은 뒤 H5000까지 복사합니다. A열에 20260401처럼 8자리 날짜 텍스트가 들어오는 구조를 기준으로 합니다.

=IFERROR(DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)),A4)

상품코드도 비슷합니다. 원장에서는 C열 상품코드가 PRD-001-행사, PRD-001-반품처럼 뒤에 구분값이 붙고, 상품마스터에는 PRD-001만 등록된 경우가 있습니다. 이때 발주검토표 A열의 상품코드와 바로 비교하면 SUMIFS와 VLOOKUP이 빗나갑니다.

출고원장 I열에는 상품코드_정리를 만들고, I4에 아래 수식을 넣습니다. 예제에서는 기본 상품코드가 앞 7자리인 구조입니다. 회사 코드 길이가 8자리나 10자리라면 LEFT의 숫자만 바꾸면 됩니다.

=LEFT(C4,7)

이 두 보조열을 만들어두면 이후 수식이 훨씬 안정적입니다. 실무에서는 수식을 짧게 쓰는 것보다, 기준이 되는 날짜와 코드를 명확히 만들어두는 쪽이 오류를 훨씬 빨리 잡습니다.

상품명은 XLOOKUP, 안 되는 버전은 INDEX MATCH로 처리합니다

발주검토 시트 A5부터 상품코드를 입력합니다. 예를 들어 A5에는 PRD-001, A6에는 PRD-002, A7에는 PRD-003이 들어갑니다. B열에는 상품마스터에서 상품명을 가져오겠습니다.

아래 수식은 Microsoft 365 최신 버전을 기준으로 설명합니다. Excel 2021/2024 등 영구 버전에서는 XLOOKUP 지원 여부가 다를 수 있으니, 사용 중인 버전에서 먼저 확인해 주세요.

발주검토!B5에 입력합니다.

=IFERROR(XLOOKUP(A5,상품마스터!$A$4:$A$500,상품마스터!$B$4:$B$500),IFERROR(INDEX(상품마스터!$B$4:$B$500,MATCH(A5,상품마스터!$A$4:$A$500,0)),"상품코드 확인"))

앞쪽 XLOOKUP이 가능하면 그대로 상품명을 가져오고, 지원되지 않거나 조회가 안 되면 INDEX와 MATCH 조합으로 한 번 더 찾습니다. 그래도 없으면 상품코드 확인이라고 표시합니다. 이 메시지가 나오면 수식 문제가 아니라 A열 코드와 상품마스터 A열 코드가 다르다는 뜻으로 보면 됩니다.

가용재고는 현재고에서 보류재고를 빼야 발주가 과하게 나오지 않습니다

재고현황 시트는 A열 창고, B열 상품코드, C열 현재고, D열 보류재고 구조입니다. 단순히 현재고만 가져오면 불량 보류, 출고대기 보류, 품질검사 보류 수량까지 판매 가능한 재고로 잡힙니다. 그래서 C열 가용재고는 현재고 합계에서 보류재고 합계를 빼서 계산합니다.

발주검토!C5에 입력합니다.

=SUMIFS(재고현황!$C$4:$C$3000,재고현황!$A$4:$A$3000,$B$2,재고현황!$B$4:$B$3000,$A5)-SUMIFS(재고현황!$D$4:$D$3000,재고현황!$A$4:$A$3000,$B$2,재고현황!$B$4:$B$3000,$A5)

여기서 $B$2는 발주검토 시트의 창고 조건입니다. 서울센터만 볼 때는 B2에 서울센터를 넣고, 부산센터를 볼 때는 B2만 바꾸면 같은 수식으로 결과가 바뀝니다. 창고명이 원장에서는 서울 센터처럼 띄어쓰기가 들어가 있으면 합계가 0이 되므로, 재고현황 A열과 발주검토 B2의 글자가 정확히 같은지도 꼭 확인해야 합니다.

미입고수량은 이번 달 말까지 들어올 물량만 더합니다

입고예정 시트는 A열 입고예정일, B열 상품코드, C열 발주번호, D열 미입고수량, E열 상태로 구성합니다. 이미 입고완료된 건까지 더하면 실제보다 재고가 넉넉한 것처럼 보이기 때문에 상태 조건을 같이 걸어야 합니다.

발주검토!D5에 입력합니다.

=SUMIFS(입고예정!$D$4:$D$2000,입고예정!$B$4:$B$2000,$A5,입고예정!$A$4:$A$2000,"<="&EOMONTH($B$1,0),입고예정!$E$4:$E$2000,"<>입고완료")

EOMONTH($B$1,0)은 기준일이 속한 달의 마지막 날짜입니다. 기준일 B1이 2026-07-05라면 2026-07-31까지 들어올 예정 수량만 더합니다. 다음 달 입고분까지 포함하면 이번 주 발주 판단이 흔들리므로, 발주 회의 기준에 맞게 날짜 조건을 명확히 정해야 합니다.

최근 3개월 출고량은 기준월을 빼고 직전 완성월로 계산합니다

발주 검토에서 흔한 실수는 기준일이 7월 5일인데 7월 출고량까지 최근 3개월에 넣는 것입니다. 월초에는 당월 출고가 며칠치밖에 없기 때문에 일평균이 낮아지고, 결과적으로 발주수량이 적게 나올 수 있습니다. 그래서 여기서는 기준일의 직전 3개월 완성월, 즉 2026-04-01부터 2026-06-30까지를 사용하겠습니다.

발주검토!E5에 입력합니다. 출고원장 H열은 앞에서 만든 계산용 날짜, I열은 정리된 상품코드입니다.

=SUMIFS(출고원장!$E$4:$E$5000,출고원장!$B$4:$B$5000,$B$2,출고원장!$I$4:$I$5000,$A5,출고원장!$H$4:$H$5000,">="&EOMONTH($B$1,-4)+1,출고원장!$H$4:$H$5000,"<="&EOMONTH($B$1,-1))

EOMONTH($B$1,-4)+1은 기준일에서 4개월 전 말일의 다음 날입니다. 기준일이 2026-07-05라면 2026-04-01이 됩니다. EOMONTH($B$1,-1)은 직전월 말일인 2026-06-30입니다.

일평균 출고량은 F5에서 계산합니다.

=ROUND(E5/(EOMONTH($B$1,-1)-EOMONTH($B$1,-4)),1)

이 수식은 4월 1일부터 6월 30일까지의 일수로 출고량을 나눕니다. ROUND로 소수 첫째 자리까지 맞추면 보고서에서 보기 좋고, 뒤 계산에서도 지나치게 긴 소수가 따라다니지 않습니다.

필요재고와 추천발주는 리드타임, 안전재고를 같이 봐야 합니다

상품마스터 시트는 A열 상품코드, B열 상품명, C열 카테고리, D열 박스입수, E열 리드타임, F열 안전재고로 구성합니다. 리드타임은 발주 후 입고까지 걸리는 평균 일수이고, 안전재고는 최소로 들고 있어야 하는 수량입니다.

발주검토!G5에는 필요재고를 계산합니다.

=ROUND(F5*IFERROR(VLOOKUP($A5,상품마스터!$A$4:$F$500,5,FALSE),0)+IFERROR(VLOOKUP($A5,상품마스터!$A$4:$F$500,6,FALSE),0),0)

예를 들어 PRD-001의 일평균 출고가 12.5개, 리드타임이 10일, 안전재고가 50개라면 필요재고는 175개입니다. 리드타임 동안 팔릴 수량 125개에 안전재고 50개를 더한 값입니다.

이제 H5에서 실제 추천 발주수량을 계산합니다.

=IF(G5-C5-D5>0,ROUND(G5-C5-D5,0),0)

필요재고에서 가용재고와 미입고수량을 빼고, 부족분이 있을 때만 발주수량을 표시합니다. 부족분이 0 이하이면 이미 재고와 입고예정으로 충분하다는 뜻이므로 0으로 둡니다.

I5에는 판정 문구를 넣어두면 검토가 빨라집니다.

=IF(COUNTIFS(상품마스터!$A$4:$A$500,$A5)=0,"마스터 없음",IF(H5>0,"발주","보류"))

COUNTIFS로 상품마스터 등록 여부를 먼저 확인합니다. 마스터에 없는 상품은 리드타임과 안전재고가 비어 있을 가능성이 높으니, 발주수량을 믿기 전에 코드 등록부터 확인해야 합니다.

결과가 이상할 때는 합계보다 건수부터 검산합니다

발주수량이 이상하면 바로 수식을 고치기보다, 조건에 걸린 행이 실제로 몇 건인지 먼저 세는 편이 빠릅니다. 발주검토 시트 J2 같은 빈 셀에 아래 수식을 넣으면 기준 창고의 직전 3개월 출고 행 수를 확인할 수 있습니다.

=COUNTIFS(출고원장!$B$4:$B$5000,$B$2,출고원장!$H$4:$H$5000,">="&EOMONTH($B$1,-4)+1,출고원장!$H$4:$H$5000,"<="&EOMONTH($B$1,-1))

이 값이 0이면 출고일_계산용 H열이 날짜로 인식되지 않았거나, 창고명이 맞지 않는 경우가 많습니다. 건수는 잡히는데 특정 상품만 0이면 출고원장 I열의 상품코드_정리와 발주검토 A열 코드 길이가 맞는지 봐야 합니다.

또 하나 자주 보는 실수는 반품수량입니다. 출고원장 E열에 반품이 음수로 들어가면 최근3개월출고가 자동으로 차감됩니다. 반대로 반품이 양수이고 F열에 출고, 반품 같은 구분값이 따로 있다면 SUMIFS에 구분 조건을 추가해야 합니다.

=SUMIFS(출고원장!$E$4:$E$5000,출고원장!$B$4:$B$5000,$B$2,출고원장!$I$4:$I$5000,$A5,출고원장!$F$4:$F$5000,"출고",출고원장!$H$4:$H$5000,">="&EOMONTH($B$1,-4)+1,출고원장!$H$4:$H$5000,"<="&EOMONTH($B$1,-1))

이처럼 원장 구조에 따라 조건을 하나 더 넣는 것만으로도 발주수량이 크게 달라집니다. 실무에서는 수식 자체보다 무엇을 출고로 볼 것인지 기준을 맞추는 과정이 더 중요합니다.

상품이 많을 때는 수식 채우기만 VBA로 처리해도 시간이 줄어듭니다

상품코드가 500개, 1000개씩 있는 파일에서는 B5:I5 수식을 매번 아래로 복사하는 것도 은근히 번거롭습니다. 이럴 때는 수식 자체는 위에서 만든 그대로 두고, 복사 작업만 VBA로 처리하면 됩니다.

아래 코드는 발주검토 시트의 A열 마지막 상품코드 행까지 B5:I5의 수식과 서식을 채웁니다. 실행 전에는 파일을 한 번 저장해두세요. VBA 실행 후에는 일반적인 실행 취소가 되지 않을 수 있으니, 잘못 실행했다면 저장하지 말고 파일을 닫은 뒤 다시 여는 방식이 가장 안전합니다.

Sub 발주검토_수식채우기()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("발주검토")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 5 Then
        MsgBox "A5 아래에 상품코드가 없습니다."
        Exit Sub
    End If

    ws.Range("B5:I5").Copy
    ws.Range("B5:I" & lastRow).PasteSpecial xlPasteFormulas
    ws.Range("B5:I" & lastRow).PasteSpecial xlPasteFormats
    Application.CutCopyMode = False

    MsgBox "발주검토 수식 채우기가 완료되었습니다. 마지막 행: " & lastRow
End Sub

이 코드는 발주검토 시트 이름과 시작 행이 예제와 같을 때 바로 사용할 수 있습니다. 만약 상품코드가 A열이 아니라 C열에 있다면 lastRow를 구하는 열 문자만 바꾸면 됩니다. 버튼에 연결해두면 기준일 B1과 창고 B2만 바꾼 뒤 클릭 한 번으로 전체 발주표를 다시 계산할 수 있습니다.

실무에서 바로 적용할 때 체크할 부분

첫째, 기준일 B1은 반드시 날짜 형식이어야 합니다. 겉으로 2026-07-05처럼 보여도 텍스트이면 EOMONTH 계산이 틀어질 수 있습니다. 셀 서식을 날짜로 바꾸는 것만으로 해결되지 않으면 DATE, LEFT, MID, RIGHT로 실제 날짜값을 만들어야 합니다.

둘째, 상품코드 정리 기준을 통일해야 합니다. 앞 7자리로 자를지, 하이픈 앞까지만 볼지, 옵션코드까지 포함할지는 회사마다 다릅니다. 이번 예제에서는 LEFT(C4,7)을 사용했지만, 실제 코드 체계가 다르면 이 부분이 가장 먼저 수정되어야 합니다.

셋째, 최근 3개월 기준을 당월 포함으로 할지 직전 완성월로 할지 정해야 합니다. 발주 회의가 월말에 열린다면 당월 포함이 맞을 수 있고, 월초에 열린다면 직전 완성월 기준이 더 안정적입니다. 수식에서는 EOMONTH($B$1,-4)+1과 EOMONTH($B$1,-1) 부분만 바꾸면 기간 기준을 조정할 수 있습니다.

발주수량 자동 계산은 한 줄짜리 만능 수식보다, 날짜 정리, 코드 정리, 조건별 합계, 마스터 조회, 검산 건수를 나눠서 잡는 방식이 훨씬 안전합니다. 특히 재고 업무는 숫자 하나가 과발주나 품절로 바로 이어지기 때문에, SUMIFS 결과만 믿지 말고 COUNTIFS로 조건 행이 제대로 잡혔는지까지 함께 확인해 보세요.