거래처코드 뒤에 -01 붙어서 월매출이 빠질 때: LEFT·VLOOKUP·SUMIFS로 본사 기준 집계하기

월마감 매출 집계에서 VLOOKUP은 #N/A가 뜨고, SUMIFS 합계는 일부 거래처만 0으로 나오는 경우가 있습니다. 원장을 열어보면 거래처코드는 분명히 있는데, 보고서에서는 거래처명이 안 붙고 월매출도 빠집니다.

이번 예제는 지점코드가 붙은 거래처코드 때문에 본사 기준 집계가 틀어지는 상황입니다. 기존에는 필터로 지점별 코드를 하나씩 확인하고 수기로 더했지만, 개선 후에는 LEFT로 본사코드만 분리하고 VLOOKUP 또는 XLOOKUP으로 거래처명을 붙인 뒤 SUMIFS로 월매출을 집계합니다.

Before/After: 필터로 더하던 집계를 본사코드 기준 수식으로 바꾸기

먼저 실제 업무 파일 구조를 잡아보겠습니다. 시트 이름은 원장, 거래처마스터, 월별보고로 가정합니다.

원장 시트에는 ERP에서 내려받은 매출 내역이 있고, 데이터 범위는 A1:F5000입니다. A열은 주문번호, B열은 출고일, C열은 거래처코드, D열은 상품코드, E열은 수량, F열은 공급가입니다.

문제 원인: 마스터는 6자리, 원장은 지점코드까지 붙어 있다

아래처럼 원장 C열에는 000123-01, 000123-02처럼 본사코드 뒤에 지점코드가 붙어 있습니다. 그런데 거래처마스터 A열에는 본사코드 6자리만 있습니다.

거래처마스터 시트는 A1:C200 범위로 둡니다. A열은 본사코드, B열은 거래처명, C열은 담당팀입니다.

이 상태에서 원장 C2의 000123-01을 그대로 마스터 A열에서 찾으면 당연히 일치하는 값이 없습니다. 그래서 VLOOKUP은 #N/A가 뜨고, SUMIFS도 본사코드 000123 기준으로 조건을 걸면 원장 C열과 맞지 않아 합계가 빠집니다.

원장 보조열 만들기: 날짜와 본사코드를 먼저 정리한다

원장 시트 G열부터 J열까지 보조열을 추가합니다. 보조열 제목은 G1에 기준일, H1에 본사코드, I1에 거래처명, J1에 월코드를 입력합니다.

먼저 B열 출고일이 2026.07.03 10:12처럼 텍스트로 내려온 경우, SUMIFS 날짜 조건이 제대로 먹지 않을 수 있습니다. G2에 아래 수식을 넣고 G5000까지 복사합니다.

=IFERROR(DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2)),"날짜확인")

이 수식은 B2에서 연도 4자리, 월 2자리, 일 2자리를 잘라 DATE 함수로 실제 날짜값을 만듭니다. 결과가 날짜로 보이지 않으면 G열 표시 형식을 날짜로 바꿔 주세요.

다음은 C열 거래처코드에서 본사코드 6자리만 분리합니다. H2에 입력 후 아래로 복사합니다.

=LEFT(C2,6)

이제 H열의 6자리 본사코드로 거래처명을 붙입니다. Microsoft 365 또는 Excel 2021 이후 버전이라면 I2에 XLOOKUP을 사용할 수 있습니다.

=IFERROR(XLOOKUP(H2,거래처마스터!$A$2:$A$200,거래처마스터!$B$2:$B$200),"마스터 없음")

VLOOKUP이 더 익숙한 파일이라면 아래 수식을 써도 됩니다. 핵심은 C2 전체가 아니라 H2의 본사코드를 찾는다는 점입니다.

=IFERROR(VLOOKUP(H2,거래처마스터!$A$2:$B$200,2,FALSE),"마스터 없음")

월별 검산용 키도 하나 만들어두면 좋습니다. J2에는 기준일과 본사코드를 합친 월코드를 만듭니다.

=TEXT(G2,"yyyymm")&"-"&H2

월별보고 시트: SUMIFS로 기준월·본사코드 조건 집계

이제 보고서 시트를 만듭니다. 월별보고 시트에서 H1에는 기준월을 입력합니다. 예를 들어 2026년 7월 보고서라면 H1에 아래처럼 실제 날짜를 입력합니다.

=DATE(2026,7,1)

H1 셀은 표시 형식만 yyyy-mm으로 바꿔도 됩니다. 셀 값 자체는 2026-07-01 같은 날짜값이어야 SUMIFS가 안정적으로 계산됩니다.

월별보고 시트는 A4:E4에 제목을 만들고, A5부터 본사코드를 입력합니다. 예를 들어 A5에는 000123, A6에는 000205를 입력합니다.

B5에는 거래처명을 붙입니다. 마스터에 없는 코드는 바로 확인할 수 있게 IFERROR를 같이 씁니다.

=IFERROR(VLOOKUP(A5,거래처마스터!$A$2:$B$200,2,FALSE),"마스터 없음")

C5에는 기준월 매출 합계를 구합니다. 조건은 세 가지입니다. 원장 G열 기준일이 H1 이상, 원장 G열 기준일이 해당 월 말일 이하, 원장 H열 본사코드가 A5와 같아야 합니다.

=SUMIFS(원장!$F$2:$F$5000,원장!$G$2:$G$5000,">="&$H$1,원장!$G$2:$G$5000,"<="&EOMONTH($H$1,0),원장!$H$2:$H$5000,$A5)

D5에는 해당 월 매출 행수를 구합니다. 금액 합계가 맞는 것 같아도 행수가 갑자기 줄어 있으면 코드나 날짜가 빠졌을 가능성이 큽니다.

=COUNTIFS(원장!$G$2:$G$5000,">="&$H$1,원장!$G$2:$G$5000,"<="&EOMONTH($H$1,0),원장!$H$2:$H$5000,$A5)

E5에는 행당 평균금액을 계산합니다. 0으로 나누는 오류를 막기 위해 IF를 넣고, 보고서용 숫자는 ROUND로 반올림합니다.

=IF(D5=0,0,ROUND(C5/D5,0))

이제 B5:E5 수식을 아래로 복사하면 본사코드별 월매출 보고서가 완성됩니다. 거래처 지점이 몇 개로 나뉘어 있어도 H열 본사코드가 같으면 한 줄로 합산됩니다.

합계가 맞는지 확인하는 검증 순서

수식을 넣은 뒤에는 반드시 원장 총액과 보고서 총액을 맞춰봐야 합니다. 월별보고 C열 합계가 원장 7월 공급가 합계와 같아야 정상입니다.

예를 들어 월별보고 C20에 보고서 합계를 구합니다.

=SUM(C5:C19)

그리고 원장 기준으로 같은 월 전체 매출을 별도로 계산합니다. 월별보고 C21에 아래 수식을 넣어 C20과 비교합니다.

=SUMIFS(원장!$F$2:$F$5000,원장!$G$2:$G$5000,">="&$H$1,원장!$G$2:$G$5000,"<="&EOMONTH($H$1,0))

C20과 C21이 다르면 대부분 세 가지 중 하나입니다. 월별보고 A열 본사코드 목록에 빠진 코드가 있거나, 원장 H열 본사코드 분리 길이가 틀렸거나, G열 날짜 변환에서 날짜확인이 발생한 경우입니다.

마스터 누락도 바로 세어볼 수 있습니다. 원장 I열에 마스터 없음이 몇 건 있는지 K2 같은 빈 셀에 확인합니다.

=COUNTIFS(원장!$I$2:$I$5000,"마스터 없음")

자주 틀리는 부분: 앞자리 0과 범위 크기

거래처코드가 000123처럼 앞자리 0을 포함한다면 A열, C열, H열 모두 텍스트로 관리하는 것이 안전합니다. 어느 한쪽이 숫자 123으로 바뀌면 눈에는 비슷해 보여도 조회가 안 될 수 있습니다.

또 SUMIFS에서 합계 범위와 조건 범위의 행 수가 다르면 오류가 나거나 계산이 이상해집니다. 예를 들어 합계 범위는 F2:F5000인데 조건 범위가 G:G 전체열이면 파일에 따라 느려지고 검산이 어려워집니다. 실무 보고서에서는 범위를 같은 시작 행, 같은 끝 행으로 맞추는 습관이 좋습니다.

거래처코드 형식이 000123-01이 아니라 KR-000123-01처럼 앞에 국가코드가 붙는 파일이라면 LEFT(C2,6)이 아니라 MID를 써야 합니다. 이 경우 본사코드가 C2의 4번째 글자부터 6자리라면 아래처럼 바꿉니다.

=MID(C2,4,6)

매달 반복한다면 보조열 작성은 매크로로 줄이기

매월 ERP 원장을 새로 붙여 넣고 G:J 보조열을 다시 만드는 작업이 반복된다면, 아래 VBA로 보조열 제목과 수식을 한 번에 넣을 수 있습니다. 적용 대상은 시트 이름이 원장인 파일이며, A열 주문번호 기준으로 마지막 행을 찾습니다.

실행 전에는 파일을 복사본으로 저장해 두세요. 매크로 실행 후에는 일반적인 Ctrl+Z 되돌리기가 되지 않으므로, 잘못 실행했다면 G:J열을 삭제하거나 저장하지 않고 닫는 방식으로 되돌리는 것이 안전합니다.

Sub 원장_월마감_보조열작성()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = Worksheets("원장")
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "원장 데이터가 없습니다."
        Exit Sub
    End If

    ws.Range("G1:J1").Value = Array("기준일", "본사코드", "거래처명", "월코드")

    ws.Range("G2:G" & lastRow).Formula = "=IFERROR(DATE(LEFT(B2,4),MID(B2,6,2),MID(B2,9,2)),""날짜확인"")"
    ws.Range("H2:H" & lastRow).Formula = "=LEFT(C2,6)"
    ws.Range("I2:I" & lastRow).Formula = "=IFERROR(VLOOKUP(H2,거래처마스터!$A$2:$B$200,2,FALSE),""마스터 없음"")"
    ws.Range("J2:J" & lastRow).Formula = "=TEXT(G2,""yyyymm"")&""-""&H2"

    ws.Range("G2:G" & lastRow).NumberFormat = "yyyy-mm-dd"
    ws.Columns("G:J").AutoFit

    MsgBox "보조열 작성이 완료되었습니다."
End Sub

이 방식의 장점은 원장을 억지로 고치지 않는다는 점입니다. ERP 원본은 그대로 두고, 보고서에 필요한 기준일과 본사코드만 옆에서 정리합니다.

VLOOKUP #N/A와 SUMIFS 0원 문제는 함수가 어려워서 생기기보다, 조회 기준과 집계 기준이 서로 달라서 생기는 경우가 많습니다. 원장 코드가 지점 단위인지, 보고서가 본사 단위인지부터 맞추면 월마감 검산 시간이 꽤 줄어듭니다.