해결하고자 하는 문제 — 주문이나 입출고 같은 트랜잭션에는 제품 코드와 거래처 코드가 따라옵니다. 그런데 마스터 코드표가 항상 앞서 준비되어 있지는 않습니다. 운영 중에 처음 보는 코드가 섞여 들어오면 집계와 조회가 틀어지고, 담당자가 수동으로 추가하기 전까지 오류가 반복됩니다. 이 글은 현장 표준 도구만으로 신규 코드 발견을 자동 탐지하고 추가용 요약 리스트까지 자동 생성하는 실전 공식을 안내합니다. 쉽게 따라 하실 수 있도록 샘플과 결과 파일을 함께 제공합니다.
다운로드
- 마스터 코드표 샘플: B9_codes_master.csv
- 트랜잭션 원본: B9_transactions.csv
- 알림 컬럼 포함 결과: B9_transactions_with_alert.csv
- 신규 코드 요약표: B9_new_codes_summary.csv
샘플 데이터 설명
실습은 두 테이블로 구성됩니다. 마스터 코드표와 트랜잭션. 트랜잭션에는 의도적으로 마스터에 없는 코드가 섞여 있어야 탐지가 가능합니다.
B9_codes_master.csv 필드 사전
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
code |
코드 키 | P012 | 고유 식별자 |
name |
표시명 | Product 012 | 사내 명칭 |
category |
분류 | Office | 예시용 범주 |
active |
사용 여부 | Y | 비활성화 관리 시 사용 |
B9_transactions.csv 필드 사전
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
date |
거래일 | 2025-06-22 | YYYY-MM-DD |
doc_no |
문서 번호 | D20250622-7341 | 중복 없음 가정 |
code |
품목 코드 | P031 | 마스터에 없을 수 있음 |
qty |
수량 | 3 | 정수 |
unit_price |
단가 | 12400 | 정수 |
핵심 구조. 알림 컬럼과 신규 코드 요약
- 알림 컬럼 — 트랜잭션 각 행에서 코드가 마스터에 존재하는지 즉시 판정합니다. 없으면 NEW를 표시합니다.
- 최초 발견 추적 — 같은 신규 코드가 여러 행에 나오더라도 첫 등장 일자와 문서 번호를 하나로 고정합니다.
- 신규 코드 요약표 — NEW만 필터해서 코드별로 하나의 행으로 요약합니다. 담당자는 이 표를 보고 마스터에 추가합니다.
알림 컬럼 수식
Excel 365에서는 XMATCH로 정확히 일치하는 위치를 찾고, 없을 때만 NEW를 세웁니다. 표 이름을 마스터는 Master, 트랜잭션은 Tx로 가정합니다.
=IF(ISNA(XMATCH([@code], Master[code], 0)), "NEW", "")Google Sheets도 동일한 논리입니다. 열 주소만 맞춰 주세요. C열이 code라고 가정합니다.
=IF(ISNA(XMATCH(C2, Master!A:A, 0)), "NEW", "")XMATCH가 없거나 구버전을 쓰신다면 MATCH로 대체해도 됩니다.
=IF(ISNA(MATCH([@code], Master[code], 0)), "NEW", "")최초 발견 일자와 문서 번호
같은 신규 코드가 여러 번 반복될 수 있습니다. 관리 편의를 위해 코드별 첫 발생 일자와 첫 문서 번호를 계산해 열로 보관합니다.
Excel
=IF([@alert_new]="NEW", MINIFS(Tx[date], Tx[code], [@code]), "")=IF([@alert_new]="NEW",
XLOOKUP([@code],
SORTBY(Tx[code], Tx[date], 1, Tx[doc_no], 1),
SORTBY(Tx[doc_no], Tx[date], 1, Tx[doc_no], 1),
""),
"")Google Sheets
=IF(E2="NEW", MINIFS(A:A, C:C, C2), "")=IF(E2="NEW", INDEX(SORT(FILTER(B:B, C:C=C2), 1, TRUE, 1, TRUE), 1), "")신규 코드 요약표 만들기
알림 컬럼이 NEW인 행만 걸러 코드별로 하나의 행만 남기면 됩니다.
Excel
=UNIQUE(FILTER(Tx[code], Tx[alert_new]="NEW"))이 코드를 키로 사용해 VSTACK이나 HSTACK으로 첫 일자와 첫 문서를 붙여 최종 표를 구성하시면 됩니다.
Google Sheets
=UNIQUE(FILTER(C:C, E:E="NEW"))검증 체크리스트
- 마스터에 존재하는 코드는 알림 컬럼이 공백인지 확인합니다.
- 동일 신규 코드 여러 행에서 첫 발생 일자와 문서 번호가 모두 동일한지 확인합니다.
- 요약표에는 코드당 한 행만 남는지 UNIQUE 결과를 재확인합니다.
- 마스터에 신규 코드를 실제로 추가한 뒤에는 알림 컬럼이 자동으로 공백으로 바뀌는지 확인합니다.
샘플 결과 해석 가이드
B9_transactions_with_alert.csv에는 alert_new first_seen_date first_seen_doc 열이 포함되어 있습니다. alert_new가 NEW인 항목들은 마스터에 없던 코드입니다. 별도로 제공하는 B9_new_codes_summary.csv는 코드별 첫 등장 정보를 모아 둔 표로서, 담당자가 마스터에 추가할 때 기준 자료로 쓰면 됩니다.
엑셀과 시트용 수식 모음
위에서 설명한 주요 공식을 한눈에 보실 수 있도록 별도 CSV로 정리했습니다. 파일에서 목적과 수식을 복사해 바로 적용하실 수 있습니다.
- 엑셀 알림 플래그
- 엑셀 최초 발견 일자와 문서
- 시트 알림 플래그
- 시트 최초 발견 일자와 문서
유지 보수 팁
- 마스터는 표로 관리 — 범위를 표로 전환해야 구조화 참조를 안정적으로 사용할 수 있습니다.
- 알림 컬럼은 읽기 전용 — 사람이 덮어쓰지 않도록 별도 시트에서 요약만 편집하도록 흐름을 분리하세요.
- 코드 추가 절차 문서화 — 누가 언제 어떤 정보로 마스터에 추가하는지, 승인 절차를 명문화하면 데이터 유입 품질이 높아집니다.
- 버전 기록 — 마스터 변경 시 변경자와 일시를 기록하는 로그를 두면 회귀 분석이 쉬워집니다.
정리
핵심은 간단합니다. 코드가 마스터에 없으면 NEW를 띄우고, 그 코드가 처음 나타난 시점을 기록해 요약 표를 자동으로 만듭니다. 이렇게 해 두면 미등록 코드가 보고서 품질을 해치기 전에 먼저 발견됩니다. 위의 파일을 내려받아 수식을 그대로 배치해 보세요. 다음번부터는 코드표가 뒤따르더라도 운영 데이터가 먼저 알려 줄 것입니다.