엑셀 담당자 변경 이력 때문에 월매출 달성률이 틀릴 때: 날짜조건 INDEX MATCH와 SUMIFS 검산법

엑셀에서 월매출 달성률을 만들 때 은근히 자주 틀어지는 부분이 있습니다. 거래처별 담당자가 중간에 바뀌었는데, 주문원장은 그대로 거래처코드만 있고 현재 담당자만 VLOOKUP으로 붙여서 집계하는 경우입니다.

겉으로 보면 SUMIFS 수식도 맞고, 기준월도 맞고, 매출액도 숫자인데 담당자별 합계가 ERP나 영업관리 파일과 다르게 나옵니다. 특히 7월 중간에 거래처 담당자가 바뀐 회사라면 “김대리 매출이 너무 많고, 박과장 매출이 빠지는” 식으로 차이가 납니다.

이번 예제는 작은 주문원장 하나를 기준으로 끝까지 고쳐보겠습니다. 핵심은 거래처코드만으로 담당자를 찾지 않고, 주문일이 담당자 이력의 시작일과 종료일 사이에 있는지까지 같이 보는 것입니다.

증상: VLOOKUP으로 담당자를 붙였더니 과거 주문까지 현재 담당자로 잡힘

아래처럼 주문원장 시트의 A열부터 G열까지 원자료가 있다고 가정하겠습니다. 데이터는 5행부터 500행까지 있고, A열 주문일은 ERP에서 내려받아 20260703 같은 8자리 텍스트로 들어온 상태입니다.

그리고 L열부터 Q열에는 담당자 이력표를 둡니다. 거래처 담당자가 바뀐 날짜를 관리하는 표입니다.

여기서 흔한 실수는 거래처코드만 기준으로 현재 담당자 표를 만들어 놓고, 주문원장 I열에 아래처럼 VLOOKUP을 넣는 것입니다.

=IFERROR(VLOOKUP(C5,$L$5:$O$100,4,0),"확인필요")

이 수식은 거래처코드가 맞으면 담당자를 가져오긴 합니다. 하지만 001245 거래처처럼 7월 15일에 담당자가 바뀐 경우, 7월 3일 주문도 박과장으로 붙거나, 반대로 첫 번째 이력만 잡혀 계속 김대리로 붙을 수 있습니다. 즉, 거래처코드만으로는 부족하고 주문일 조건이 반드시 들어가야 합니다.

먼저 주문일을 진짜 날짜로 바꿔야 SUMIFS 날짜조건이 먹습니다

주문원장 H열을 보조열로 사용하겠습니다. H4에는 주문일_날짜라고 적고, H5에 아래 수식을 넣은 뒤 H500까지 복사합니다.

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

이 수식은 A5의 20260703에서 왼쪽 4자리는 연도, 가운데 2자리는 월, 오른쪽 2자리는 일을 잘라 DATE 함수로 실제 날짜를 만듭니다. 이미 A열이 진짜 날짜라면 H열 없이 A열을 바로 써도 되지만, 실무 파일에서는 날짜처럼 보이는 텍스트가 워낙 많아서 H열을 따로 만드는 편이 안전합니다.

확인 방법은 간단합니다. H5 셀 서식을 일반으로 바꿨을 때 46206 같은 숫자가 보이면 날짜로 인식된 것입니다. 반대로 20260703이 그대로 보이면 아직 텍스트입니다.

담당자 이력표의 빈 종료일을 9999-12-31로 바꿔 비교하기

이력표에서 현재 담당자는 종료일이 비어 있습니다. 그런데 날짜 비교 수식에서는 빈칸이 있으면 조건이 애매해질 수 있습니다. 그래서 Q열에 검사용 종료일을 하나 만들어 둡니다.

Q5 셀에 아래 수식을 넣고 Q100까지 복사합니다.

=IF(N5="",DATE(9999,12,31),N5)

종료일이 비어 있으면 아주 먼 미래 날짜로 처리합니다. 이렇게 해두면 “주문일이 시작일보다 크거나 같고, 종료일검사용보다 작거나 같다”는 조건을 깔끔하게 사용할 수 있습니다.

INDEX MATCH로 주문일에 맞는 담당자 찾기

이제 주문원장 I열에 실제 적용 담당자를 붙이겠습니다. I4에는 적용담당자라고 적고, I5에 아래 수식을 입력합니다.

=IFERROR(INDEX($O$5:$O$100,MATCH(1,($L$5:$L$100=$C5)*($M$5:$M$100<=$H5)*($Q$5:$Q$100>=$H5),0)),"이력확인")

Microsoft 365나 Excel 2021 이상에서는 보통 그대로 입력해도 계산됩니다. 구버전 엑셀에서는 입력 후 Ctrl + Shift + Enter로 확정해야 할 수 있습니다.

수식의 판단 기준은 세 가지입니다. 첫째, 이력표 L열 거래처코드가 주문원장 C5와 같아야 합니다. 둘째, 이력표 M열 시작일이 주문일 H5보다 빠르거나 같아야 합니다. 셋째, 이력표 Q열 종료일검사용이 주문일 H5보다 늦거나 같아야 합니다.

조건을 모두 만족하는 행이 1이 되고, MATCH가 그 위치를 찾은 뒤 INDEX가 O열 담당자명을 가져옵니다. 수식이 길어 보여도 구조는 단순합니다. 코드 일치 + 시작일 이전 + 종료일 이후입니다.

XLOOKUP을 사용할 수 있는 버전이라면 I5에 아래처럼 써도 됩니다.

=IFERROR(XLOOKUP(1,($L$5:$L$100=$C5)*($M$5:$M$100<=$H5)*($Q$5:$Q$100>=$H5),$O$5:$O$100),"이력확인")

다만 여러 사람이 함께 쓰는 파일이라면 INDEX MATCH 방식이 더 무난할 때가 많습니다. 회사마다 엑셀 버전이 섞여 있는 경우가 생각보다 많기 때문입니다.

부서도 같은 방식으로 붙이고, 누락 여부를 COUNTIFS로 검증

J열에는 부서를 붙여보겠습니다. J4에는 적용부서라고 입력하고 J5에 아래 수식을 넣습니다.

=IFERROR(INDEX($P$5:$P$100,MATCH(1,($L$5:$L$100=$C5)*($M$5:$M$100<=$H5)*($Q$5:$Q$100>=$H5),0)),"이력확인")

그리고 K열에는 이력표에서 조건에 맞는 행이 정확히 몇 개인지 검사합니다. K4에는 이력건수라고 적고 K5에 아래 수식을 넣습니다.

=COUNTIFS($L$5:$L$100,C5,$M$5:$M$100,"<="&H5,$Q$5:$Q$100,">="&H5)

K열 결과가 1이면 정상입니다. 0이면 해당 주문일에 맞는 담당자 이력이 없는 것입니다. 2 이상이면 이력 기간이 겹친 것입니다. 담당자별 매출이 틀어지는 파일은 이 K열에서 이미 원인이 드러나는 경우가 많습니다.

기준월별 담당자 매출 합계는 SUMIFS로 깔끔하게 집계

이제 오른쪽에 작은 집계표를 만들겠습니다. S2에는 기준월을 날짜로 입력합니다. 예를 들어 2026년 7월을 보고 싶다면 S2에 2026-07-01을 입력합니다.

U5에는 담당자별 주문건수를 구합니다. 주문원장 I열의 적용담당자와 H열 주문일_날짜를 기준으로 셉니다.

=COUNTIFS($I$5:$I$500,T5,$H$5:$H$500,">="&$S$2,$H$5:$H$500,"<="&EOMONTH($S$2,0))

V5에는 매출합계를 구합니다.

=SUMIFS($G$5:$G$500,$I$5:$I$500,T5,$H$5:$H$500,">="&$S$2,$H$5:$H$500,"<="&EOMONTH($S$2,0))

여기서 중요한 점은 기준월을 텍스트로 “2026-07”처럼 쓰지 않는 것입니다. S2는 반드시 실제 날짜인 2026-07-01로 넣고, 종료일은 EOMONTH로 월말을 계산해야 합니다. 그래야 7월 1일부터 7월 31일까지 정확히 잡힙니다.

목표표까지 연결해서 달성률 1원 차이 없이 보기

목표표는 AA열부터 AC열에 둔다고 하겠습니다. AA4는 기준월, AB4는 담당자, AC4는 목표입니다. 데이터는 AA5:AC20 범위에 입력합니다.

W5에는 담당자별 목표를 가져옵니다. 숫자 목표이므로 VLOOKUP보다 SUMIFS가 오히려 편합니다.

=SUMIFS($AC$5:$AC$20,$AA$5:$AA$20,$S$2,$AB$5:$AB$20,T5)

X열에는 달성률을 계산합니다. X4에 달성률이라고 적고 X5에는 아래 수식을 넣습니다.

=IFERROR(ROUND(V5/W5,4),0)

셀 서식을 백분율로 바꾸면 0.8532가 85.32%로 표시됩니다. ROUND를 넣는 이유는 보고서에서 소수점 표시가 들쭉날쭉해지는 것을 막기 위해서입니다.

SUMIFS가 0으로 나올 때 확인할 순서

이 예제에서 SUMIFS가 0으로 나온다면 수식보다 데이터 상태를 먼저 봐야 합니다. 첫 번째는 S2 기준월입니다. S2가 실제 날짜인지 확인하고, 셀 서식을 일반으로 바꿨을 때 숫자로 바뀌는지 봅니다.

두 번째는 H열 주문일_날짜입니다. H열이 텍스트면 날짜조건이 맞아도 합계가 0으로 나올 수 있습니다. 세 번째는 I열 적용담당자입니다. 집계표 T열의 이름과 주문원장 I열의 이름에 공백이 섞여 있으면 서로 다른 값으로 봅니다.

네 번째는 C열 거래처코드입니다. 원장에는 1245로 들어오고 이력표에는 001245로 들어오면 담당자를 못 찾습니다. 거래처코드는 숫자가 아니라 코드이므로 앞자리 0을 유지하는 편이 안전합니다.

월마다 내려받는 원장은 VBA로 날짜와 거래처코드만 먼저 정리해도 편합니다

매달 ERP에서 같은 형태로 주문원장을 내려받는다면 A열 주문일과 C열 거래처코드를 손으로 고치는 일이 반복됩니다. 이럴 때는 아래 매크로로 주문원장 시트의 5행부터 마지막 행까지 A열 날짜를 실제 날짜로 바꾸고, C열 거래처코드를 6자리 텍스트로 맞출 수 있습니다.

실행 전에는 반드시 파일을 다른 이름으로 저장해 두세요. 매크로 실행 후에는 Ctrl+Z로 되돌릴 수 없습니다. 되돌리려면 백업 파일을 열거나, 실행 전 저장한 복사본으로 돌아가는 방식이 안전합니다.

Sub 주문원장_날짜코드_정리()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim r As Long
    Dim rawDate As String
    Dim rawCode As String

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

    For r = 5 To lastRow
        rawDate = Replace(CStr(ws.Cells(r, "A").Value), "-", "")
        rawDate = Replace(rawDate, ".", "")
        rawDate = Replace(rawDate, "/", "")

        If Len(rawDate) = 8 Then
            ws.Cells(r, "A").Value = DateSerial(Left(rawDate, 4), Mid(rawDate, 5, 2), Right(rawDate, 2))
            ws.Cells(r, "A").NumberFormat = "yyyy-mm-dd"
        End If

        rawCode = Trim(CStr(ws.Cells(r, "C").Value))
        If rawCode <> "" Then
            ws.Cells(r, "C").NumberFormat = "@"
            ws.Cells(r, "C").Value = Right("000000" & rawCode, 6)
        End If
    Next r

    MsgBox "주문일과 거래처코드 정리가 끝났습니다.", vbInformation
End Sub

이 코드는 시트명이 정확히 주문원장일 때 작동합니다. 시트명이 다르면 코드의 Worksheets("주문원장") 부분을 실제 시트명으로 바꿔야 합니다. 또한 C열 거래처코드가 6자리가 아닌 회사라면 Right("000000" & rawCode, 6)의 6도 실제 자리수에 맞게 조정하세요.

이 파일에서 꼭 남겨야 할 검산 포인트

담당자 변경 이력이 들어간 집계표는 결과만 맞추면 끝이 아닙니다. 나중에 담당자가 또 바뀌었을 때도 흔들리지 않게 검산 열을 남겨두는 것이 좋습니다.

주문원장 K열의 이력건수는 숨기더라도 삭제하지 않는 편을 추천합니다. 월말에 K열을 필터로 열어 0 또는 2 이상인 행만 확인하면, 담당자 누락과 기간 겹침을 빠르게 잡을 수 있습니다.

또 하나는 전체 매출 검산입니다. 기준월 전체 매출과 담당자별 매출 합계가 같은지 비교해 보세요. 예를 들어 Z5에 아래 수식을 넣으면 차이를 확인할 수 있습니다.

=SUMIFS($G$5:$G$500,$H$5:$H$500,">="&$S$2,$H$5:$H$500,"<="&EOMONTH($S$2,0))-SUM($V$5:$V$20)

결과가 0이면 기준월 전체 매출이 담당자별로 빠짐없이 배분된 것입니다. 0이 아니면 적용담당자가 “이력확인”으로 남아 있거나, 집계표 T열 담당자 목록에 누락된 사람이 있을 가능성이 큽니다.

이번 방식은 담당자뿐 아니라 영업부서, 수금담당, 대리점 등 기간별로 담당 주체가 바뀌는 자료에 그대로 응용할 수 있습니다. 거래처코드와 날짜 조건을 같이 보는 구조만 잡아두면, 현재 기준표 하나로 과거 실적을 덮어쓰는 실수를 크게 줄일 수 있습니다.