엑셀 생산실적 불량률이 안 맞을 때: 작업일 텍스트·품목코드 분류·ROUND 검산 체크리스트

엑셀 생산실적 불량률이 ERP나 MES 집계와 안 맞을 때, 의외로 수식 자체보다 작업일 텍스트, 품목코드 분류 기준, 반올림 위치에서 차이가 나는 경우가 많습니다. 특히 월별 불량률 보고서를 만들 때 SUMIFS 결과가 0으로 나오거나, 품목군별 불량률이 현장 보고서와 0.1%씩 어긋나는 증상이 자주 보입니다.

이번 예시는 생산팀에서 내려받은 일별 생산실적 원장을 기준으로, 월·라인·품목군별 생산수량과 불량수량을 집계하고 불량률까지 검산하는 흐름입니다. 어려운 기능으로 한 번에 끝내기보다, 실무 파일에서 가장 많이 쓰는 SUMIFS, COUNTIFS, LEFT, MID, DATE, EOMONTH, XLOOKUP, INDEX, MATCH, IF, IFERROR, ROUND 조합으로 안정적으로 맞추는 방식으로 정리해 보겠습니다.

어떤 파일에서 문제가 생기나?

원장 시트 이름은 생산원장이라고 가정하겠습니다. 데이터는 4행부터 5000행까지 있고, 열 구조는 아래와 같습니다.

요약표는 월별집계 시트에 만든다고 하겠습니다. B2에는 기준월을 입력합니다. 예를 들어 2026-07-01처럼 해당 월의 1일을 날짜 형식으로 넣습니다. B4에는 조회할 라인, B5에는 조회할 품목군을 입력하고, B8부터 결과를 표시합니다.

SUMIFS가 0으로 나오면 작업일 열부터 의심해야 할까?

네, 월별 SUMIFS가 0으로 나오는 가장 흔한 원인은 날짜 조건입니다. 눈으로는 2026-07-05처럼 보여도 실제 값이 텍스트이면, >=2026-07-01, <=2026-07-31 조건에 걸리지 않습니다.

먼저 생산원장 H열에 보조열을 하나 만듭니다. H3에는 작업일_변환이라고 적고, H4에 아래 수식을 입력한 뒤 H5000까지 복사합니다. A열 값이 20260705처럼 8자리 숫자이거나, 2026.07.05처럼 점으로 구분된 텍스트인 경우를 함께 처리합니다.

=IFERROR(IF(LEN(A4)=8,DATE(LEFT(A4,4),MID(A4,5,2),RIGHT(A4,2)),DATE(LEFT(A4,4),MID(A4,6,2),RIGHT(A4,2))),"")

여기서 핵심은 DATE 함수에 연, 월, 일을 따로 넣어 진짜 날짜값으로 바꾸는 것입니다. 사용 중인 파일에서 작업일이 A열이 아니라면 A4 부분만 실제 작업일 셀로 바꾸면 됩니다.

변환 결과가 숫자처럼 보이면 셀 서식을 날짜로 바꿔 확인해 보세요. H열에 빈칸이 나온 행은 작업일 형식이 섞여 있거나, 작업일에 불필요한 문자가 들어간 행입니다.

품목코드에서 품목군을 뽑아야 하는 이유는?

생산원장에는 품목명이 있지만, 품목명으로 집계하면 띄어쓰기나 표기 차이 때문에 결과가 흔들립니다. 실무에서는 품목코드에서 분류 코드를 뽑고, 별도 분류표에서 품목군을 가져오는 방식이 더 안전합니다.

예를 들어 C열 품목코드가 FG-A1001-RED 형태라면, 가운데 A1001이 분류 기준 코드라고 가정하겠습니다. 생산원장 I열에는 분류코드를 만들고, J열에는 품목군을 가져옵니다.

I3에는 분류코드, I4에는 아래 수식을 입력합니다.

=MID(C4,4,5)

C4의 네 번째 문자부터 5글자를 가져오므로 FG-A1001-RED에서 A1001만 남습니다. 만약 코드 형식이 다르다면 LEFT, RIGHT, MID 위치를 실제 코드 구조에 맞게 조정해야 합니다.

분류표는 코드표 시트에 있다고 하겠습니다. A열은 분류코드, B열은 품목군입니다. 데이터 범위는 A2:B100입니다.

J4에는 XLOOKUP으로 품목군을 가져옵니다.

=IFERROR(XLOOKUP(I4,코드표!$A$2:$A$100,코드표!$B$2:$B$100),"코드확인")

XLOOKUP을 쓸 수 없는 환경이라면 INDEX와 MATCH 조합으로도 가능합니다.

=IFERROR(INDEX(코드표!$B$2:$B$100,MATCH(I4,코드표!$A$2:$A$100,0)),"코드확인")

여기서 "코드확인"이 뜨는 행은 품목코드 분리 결과가 코드표에 없다는 뜻입니다. 이 상태에서 바로 SUMIFS를 걸면 품목군 조건에 포함되지 않으니, 먼저 코드표 누락인지 원장 코드 오타인지 확인해야 합니다.

월·라인·품목군 조건으로 생산수량과 불량수량을 집계하기

이제 월별집계 시트에서 조건별 합계를 계산해 보겠습니다. 기준월은 월별집계!B2, 라인은 B4, 품목군은 B5입니다. 생산원장 H열은 변환된 작업일, B열은 라인, J열은 품목군, E열은 생산수량입니다.

월별집계!B8에 생산수량 합계를 구하는 수식을 입력합니다.

=SUMIFS(생산원장!$E$4:$E$5000,생산원장!$H$4:$H$5000,">="&$B$2,생산원장!$H$4:$H$5000,"<="&EOMONTH($B$2,0),생산원장!$B$4:$B$5000,$B$4,생산원장!$J$4:$J$5000,$B$5)

불량수량은 합계 범위만 F열로 바꾸면 됩니다. 월별집계!B9에 입력합니다.

=SUMIFS(생산원장!$F$4:$F$5000,생산원장!$H$4:$H$5000,">="&$B$2,생산원장!$H$4:$H$5000,"<="&EOMONTH($B$2,0),생산원장!$B$4:$B$5000,$B$4,생산원장!$J$4:$J$5000,$B$5)

날짜 조건은 반드시 시작일과 말일을 함께 넣는 것이 좋습니다. B2에 2026-07-01을 입력했다면 EOMONTH($B$2,0)은 2026-07-31을 반환합니다. 이렇게 하면 7월 데이터만 정확히 묶을 수 있습니다.

불량률 0.1% 차이는 ROUND 위치에서 생긴다

불량률은 불량수량을 생산수량으로 나눕니다. 단, 생산수량이 0인 조건에서는 나누기 오류가 나므로 IF와 IFERROR를 같이 쓰면 보고서가 안정적입니다.

월별집계!B10에 아래 수식을 입력합니다.

=IFERROR(IF(B8=0,0,ROUND(B9/B8,4)),0)

셀 서식을 백분율로 바꾸고 소수점 둘째 자리까지 표시하면 1.50%처럼 보입니다. 여기서 ROUND(B9/B8,4)는 비율 기준 네 자리에서 반올림한다는 뜻입니다. 백분율 표시로 보면 소수점 둘째 자리까지 관리하기 좋습니다.

주의할 점은 행별 불량률을 먼저 반올림한 뒤 평균 내면 ERP 집계와 달라질 수 있다는 것입니다. 월별 보고서는 보통 불량수량 합계 ÷ 생산수량 합계 방식이 기준입니다. 행별 비율의 평균과 전체 비율은 결과가 다릅니다.

COUNTIFS로 작업건수까지 같이 검산하기

합계만 보면 어떤 행이 빠졌는지 바로 보이지 않습니다. 그래서 작업건수도 같이 세어 두면 좋습니다. 월별집계!B11에 아래 수식을 넣어 같은 조건에 해당하는 원장 행 수를 확인합니다.

=COUNTIFS(생산원장!$H$4:$H$5000,">="&$B$2,생산원장!$H$4:$H$5000,"<="&EOMONTH($B$2,0),생산원장!$B$4:$B$5000,$B$4,생산원장!$J$4:$J$5000,$B$5)

예를 들어 ERP에서는 7월 1라인 샴푸 작업건수가 42건인데, 엑셀 B11이 39건이라면 수량 계산 전에 빠진 행부터 찾아야 합니다. 이때는 작업일 변환 실패, 라인명 공백, 품목군 코드확인을 먼저 보는 것이 빠릅니다.

오류 원인을 빨리 찾는 체크리스트

아래 표는 생산실적 집계가 안 맞을 때 실제로 가장 많이 확인하는 순서입니다. 수식을 고치기 전에 원장 열 상태를 먼저 보면 시간을 꽤 줄일 수 있습니다.

VLOOKUP으로 코드표를 쓰고 있다면 이것도 가능

회사 파일이 아직 VLOOKUP 중심으로 만들어져 있다면 굳이 전부 바꾸지 않아도 됩니다. J4 품목군 수식은 아래처럼 작성할 수 있습니다.

=IFERROR(VLOOKUP(I4,코드표!$A$2:$B$100,2,0),"코드확인")

다만 VLOOKUP은 찾는 값이 코드표의 첫 번째 열에 있어야 합니다. 코드표에서 품목군이 A열, 분류코드가 B열처럼 되어 있으면 VLOOKUP이 바로 동작하지 않습니다. 이 경우에는 코드표 열 순서를 바꾸거나 INDEX, MATCH 조합을 쓰는 편이 안전합니다.

여러 시트 작업일을 매번 고치기 번거롭다면 VBA로 일괄 변환

월별로 생산원장 시트가 여러 개 있고, 매번 A열 작업일을 날짜로 고쳐야 한다면 VBA가 어울립니다. 아래 코드는 현재 통합문서의 모든 시트에서 A4:A5000 범위의 8자리 날짜 또는 점 구분 날짜를 실제 날짜로 바꿉니다.

실행 전에는 반드시 파일을 다른 이름으로 저장해 두세요. 매크로 실행 후에는 일반적인 실행 취소가 되지 않을 수 있으므로, 원본 파일 백업이 가장 확실한 되돌리기 방법입니다.

Sub ConvertWorkDate_AllSheets()
    Dim ws As Worksheet
    Dim rng As Range
    Dim c As Range
    Dim s As String

    For Each ws In ThisWorkbook.Worksheets
        Set rng = ws.Range("A4:A5000")
        For Each c In rng
            s = Trim(CStr(c.Value))
            If s <> "" Then
                s = Replace(s, ".", "")
                s = Replace(s, "-", "")
                If Len(s) = 8 And IsNumeric(s) Then
                    c.Value = DateSerial(Left(s, 4), Mid(s, 5, 2), Right(s, 2))
                    c.NumberFormat = "yyyy-mm-dd"
                End If
            End If
        Next c
    Next ws

    MsgBox "작업일 변환이 완료되었습니다."
End Sub

이 코드는 A열 원본 값을 직접 바꾸는 방식입니다. 원본 작업일을 보존해야 한다면 A열을 복사해 H열 같은 보조열에서 실행하거나, 앞에서 설명한 수식 방식으로 변환하는 편이 좋습니다.

적용 전에 확인할 것

생산실적 불량률 보고서는 수식 하나보다 기준 통일이 더 중요합니다. 기준월은 실제 날짜인지, 작업일은 변환됐는지, 품목코드는 같은 방식으로 잘렸는지, 불량률은 합계 후 나누는지 순서대로 확인해 보세요.

특히 월별집계!B8 생산수량, B9 불량수량, B10 불량률, B11 작업건수를 한 세트로 두면 오류를 훨씬 빨리 찾을 수 있습니다. 합계가 안 맞을 때는 B11 건수부터 보고, 건수가 맞는데 수량만 다르면 E열과 F열의 숫자 형식을 확인하는 식으로 접근하면 됩니다.

현장 데이터는 코드, 날짜, 수량 형식이 조금씩 섞여 들어오는 일이 많습니다. 보조열을 아깝게 생각하지 말고 작업일_변환, 분류코드, 품목군을 만들어 두면 다음 달 보고서도 같은 구조로 훨씬 안정적으로 이어갈 수 있습니다.