왜 이 작업이 필요한가 — 현장 데이터에는 분류 코드가 여러 모양으로 섞여 들어옵니다. A01 002처럼 공백이 있는 경우도 있고 A01002처럼 붙어 있는 경우도 있으며 A01-002 A01.002 A01_002처럼 구분 문자가 다를 때도 있습니다. 이런 데이터는 피벗이나 요약에서 같은 그룹끼리 묶이지 않아 중복 합계가 생기거나 누락이 발생합니다. 이 글은 입력 형식이 달라도 한 줄 수식으로 대/중/소 계층을 안정적으로 분리하고 표준화 코드를 만드는 규칙을 제시합니다. 본문에서 바로 실행 가능한 수식과 검증용 CSV를 제공합니다.
이번 글의 표준과 가정
- 표준 코드는 L1 두 글자 영문 대문자 중 앞의 두 글자만 보관 L2 두 자리 숫자 L3 세 자리 숫자입니다. 표기는 L1 L2 L3를 하이픈으로 연결합니다. 예 B 14 285는 B-14-285로 표준화합니다.
- 허용 입력은 대소문자 혼용 공백 하이픈 언더스코어 점 슬래시를 포함한 임의 조합과 숫자 앞의 영문이 한 글자 이상인 경우입니다. 구분 문자가 없는 A14285 같은 연속 문자열도 허용합니다.
- 비상 규칙으로 영문 접두가 없을 때는 L1을 X로 대체합니다. 숫자 자릿수가 부족하면 왼쪽을 0으로 채웁니다. 예 7은 07 23은 023입니다.
파일 내려받기
- 샘플 데이터 CSV: C7_hierarchy_sample.csv
- 결과 데이터 CSV: C7_hierarchy_result.csv
샘플 데이터 설명
샘플은 다양한 입력 패턴을 의도적으로 섞었습니다. 대문자 소문자 구분 문자 유무 자릿수 누락 등 현장에서 흔히 보이는 혼합 사례를 모두 포함합니다. 결과 파일에는 표준화 코드와 대 중 소 계층 열이 추가되어 있습니다.
C7_hierarchy_sample.csv 필드 사전
| 컬럼 | 의미 | 예시 | 비고 |
|---|---|---|---|
code_raw |
원시 분류 코드 | d05-339 | 문자 숫자 구분 문자가 섞인 임의 형식 |
item_name |
품목명 | Basic Paper | 설명 텍스트 |
qty |
수량 | 6 | 정수 |
unit_price |
단가 | 15378 | 정수 |
C7_hierarchy_result.csv 추가 필드
| 컬럼 | 의미 | 예시 | 계산 개념 |
|---|---|---|---|
code_norm |
표준화 코드 | D-05-339 | L1 두 글자 L2 두 자리 L3 세 자리 |
level1 |
대 분류 | D | code_norm의 첫 조각 |
level2 |
중 분류 | D-05 | 대 분류와 L2 결합 |
level3 |
소 분류 | D-05-339 | 표준화 코드 전체 |
amount |
금액 | 29204 | qty와 단가 곱 |
설계 논리. 먼저 정규화 그 다음 자릿수 패딩
- 문자 집합 통일 — 입력에서 영문과 숫자 외의 문자는 모두 제거합니다. 공백 하이픈 언더스코어 점 슬래시는 분리 신호일 뿐 의미가 없으므로 없앱니다. 그리고 대문자로 고정합니다.
- 영문 접두 추출 — 남은 문자열의 맨 앞에서 영문을 모두 읽어 L1로 사용합니다. 글자가 없으면 X로 대체합니다. 두 글자만 취해 규칙을 단순화합니다.
- 숫자 영역 자르기 — 영문 접두 뒤에 이어지는 숫자 영역을 L2 L3으로 나눕니다. 첫 두 자리를 L2 다음 세 자리를 L3으로 취하고 왼쪽에 0을 채워 고정 길이를 만듭니다.
- 출력 포맷 — L1 L2 L3를 하이픈으로 연결해 code_norm을 만들고 여기서 level1 level2 level3를 얻습니다.
Excel 수식 완성본
샘플을 표로 전환하고 표 이름 없이 구조화 참조만 사용합니다. 열 이름이 code_raw라고 가정하고 아래 수식을 code_norm 열에 붙여 넣습니다.
=LET(
t, UPPER(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([@code_raw],CHAR(160)," "),"-",""),"_",""),".",""),"/","")," ","")))),
pos, IFERROR(XMATCH(TRUE, ISNUMBER(--MID(t, SEQUENCE(LEN(t)), 1)), 0), LEN(t)+1),
letters, LEFT(t, pos-1),
nums, RIGHT(t, LEN(t)-LEN(letters)),
l1, IF(letters="", "X", LEFT(letters, 2)),
l2, RIGHT("00"&LEFT(nums,2), 2),
l3, RIGHT("000"&MID(nums,3,3), 3),
TEXTJOIN("-",, l1, l2, l3)
)그 다음 계층 파생 열은 간단합니다.
-- level1
=TEXTBEFORE([@code_norm], "-")
-- level2
=TEXTBEFORE([@code_norm], "-", 2)
-- level3
=[@code_norm]Google Sheets 수식
행 2에 데이터가 있다고 가정합니다. A열이 code_raw입니다. E열에 code_norm을 만든 뒤 F G H로 level1 level2 level3을 구합니다.
=LET(
t, UPPER(REGEXREPLACE(A2,"[^A-Z0-9]","")),
letters, IFERROR(REGEXEXTRACT(t,"^[A-Z]+"), "X"),
nums, REGEXREPLACE(t,"^[A-Z]+",""),
l1, LEFT(letters,2),
l2, RIGHT("00"&LEFT(nums,2), 2),
l3, RIGHT("000"&MID(nums,3,3), 3),
l1&"-"&l2&"-"&l3
)=TEXTBEFORE(E2, "-") -- level1
=TEXTBEFORE(E2, "-", 2) -- level2
=E2 -- level3검증 체크리스트
- 소문자 입력과 대문자 입력이 같은 표준화 코드로 변환되는지 확인합니다. 예 d05 339와 D05-339는 모두 D-05-339가 되어야 합니다.
- 구분 문자의 종류에 상관없이 같은 결과가 나오는지 확인합니다. 점 언더스코어 슬래시는 제거 대상입니다.
- 숫자 자릿수가 모자랄 때 왼쪽이 0으로 채워지는지 확인합니다. 7은 07 23은 023입니다.
- 영문 접두가 아예 없는 경우 level1이 X로 표준화되는지 확인합니다. 이 규칙은 내부 합의에 따라 다른 기본값으로 바꿀 수 있습니다.
실전 팁
- 규칙을 문서화 — 표준 길이와 보정 규칙은 조직 합의가 필요합니다. 이번 글의 2 3 자리 패턴을 그대로 쓰시거나 상황에 맞게 바꾸되 문서화해 모든 파일에서 동일하게 적용하십시오.
- 파생 코드는 원본 옆에 — 원본을 덮지 말고 code_norm과 계층 열을 별도로 유지해야 추적이 쉽습니다.
- 검증용 요약 — level1이나 level2 기준으로 COUNTROWS 합계를 비교하면 표준화가 잘못된 구간을 빠르게 찾아낼 수 있습니다.
결과 해석 예시
C7_hierarchy_result.csv에서 B-14-285처럼 표준화된 코드는 대 중 소 계층을 동시에 제공합니다. level1로 대시보드의 최상단 분류를 만들고 level2로 세부 보드를 구성한 다음 level3로 항목을 연결하면 일관성 있는 탐색이 가능합니다. 또한 code_norm을 키로 사용하면 다른 시트에 흩어진 분류 통계를 쉽게 결합할 수 있습니다.
문제 상황 대응
영문 접두가 세 글자 이상인 코드가 간혹 존재한다면 LEFT로 두 글자만 취하는 부분을 조정하십시오. 숫자 구성이 바뀌는 제품군이 섞여 있다면 IF로 분기해 다른 패턴을 추가하거나 별도의 규칙 맵을 두고 XMATCH로 선택하는 구조를 쓰면 됩니다. Sheets와 Excel 모두 위 수식은 배열을 반환하므로 열 전체로 한 번에 적용할 수 있습니다.
요약
핵심은 두 단계입니다. 첫째 입력을 대문자 영문과 숫자만 남기고 정리합니다. 둘째 자릿수를 고정해 패딩한 뒤 하이픈으로 조립합니다. 이렇게 얻은 code_norm을 기준으로 level1 level2 level3를 파생하면 보고서가 항상 같은 규칙으로 정렬되고 결합됩니다. 제공한 CSV를 내려받아 바로 재현해 보세요.