Excel 상품 코드 6행에서 미등록 값과 후행 공백 찾기
다른 파일에서 상품 코드를 붙여넣은 다음 목록 규칙을 걸어도 이미 들어온 잘못된 값이 자동으로 사라지는 것은 아닙니다. 이 글은 가상 코드 6행을 기준 목록 세 개와 대조해 미등록 코드와 후행 공백을 찾는 실습입니다. 새 입력을 돕는 드롭다운과 기존 데이터를 검사하는 수식의 역할을 분리합니다.
기준 목록부터 작게 고정하기
첨부 Codes 시트의 F2:F4에는 P-001, P-010, P-020이 있습니다. 세 코드는 이 실습에서 허용한 기준 목록이며 실제 회사의 상품 원장은 아닙니다. A2:A7은 외부에서 가져왔다고 가정한 여섯 입력입니다. B열은 목록 일치 수, C열은 판정, D열은 최초 입력에 대한 고정 기대값입니다.
이 과업은 상태 건수를 세는 드롭다운 실습과 다릅니다. 상품 코드의 앞자리와 0을 보존하고, 목록에 없는 값이나 공백이 붙은 값을 원본과 대조하는 것이 목적입니다. 이 가상 코드 검사에서는 금액을 계산하거나 주문 행을 삭제하지 않습니다. 작업을 시작하기 전에 첨부를 사본으로 저장해 가져온 값을 보존합니다.
| 행 | 가져온 코드 | 처음 목록 일치 수 | 최초 판정 |
|---|---|---|---|
| 2 | P-001 | 1 | 목록 있음 |
| 3 | P-010 | 1 | 목록 있음 |
| 4 | P-999 | 0 | 확인 필요 |
| 5 | P-001 뒤 공백1개 | 0 | 확인 필요 |
| 6 | P-020 | 1 | 목록 있음 |
| 7 | P-001 | 1 | 목록 있음 |
A5는 설명 문장이 아니라 P-001 뒤에 일반 공백 한 칸이 붙은 문자열입니다. 화면 너비만 보고 A2와 같은 값이라고 판단하지 않습니다. A2와 A7의 P-001 반복은 의도한 입력입니다. 같은 상품을 여러 행에서 주문할 수 있으므로 이 검사에서는 정상으로 남깁니다.
B열에서 이미 들어온 값을 검사하기
B2의 수식은 다음과 같고 B7까지 해당 행 번호로 내려갑니다.
=COUNTIF($F$2:$F$4,A2)기준 목록 F2:F4는 고정하고 비교할 A열 행은 바뀝니다. B4와 B5는0, 나머지 네 행은1이어야 합니다. C2는 이 개수를 읽어 사람이 확인할 판정을 보여줍니다.
=IF(B2=1,"목록 있음","확인 필요")기준 목록에서 같은 코드를 두 번 적으면 결과가2가 될 수 있습니다. 이때도 확인 필요로 남깁니다. 입력값을 고치기 전에 기준 목록 자체가 중복된 것은 아닌지 확인해야 합니다. 이 파일은 기준 목록 세 개를 유일한 값으로 가정하며 원장 관리 절차까지 대신하지 않습니다.
Microsoft COUNTIF 문서는 한 조건에 맞는 셀의 수를 세는 동작을 설명합니다. 이 함수의 문자열 비교는 대소문자를 구분하지 않습니다. 따라서 p-020도 이 조건에서는 P-020과 같은 것으로 셀 수 있습니다. 대문자만 허용하는 형식 검사나 보안용 식별자 비교가 필요한 경우에는 별도의 정확 비교 규칙이 필요합니다.
물음표가 있는 미등록 코드도 1건이 되는 함정
COUNTIF의 조건은 항상 문자 그대로의 코드 비교가 아닙니다. ?는 한 글자, *는 임의 길이 문자열을 뜻할 수 있습니다. 예를 들어 P-00?는 기준 목록의 P-001 한 건과 일치하므로 원래 B열 수식은 1, C열은 목록 있음이 됩니다. P-00?라는 상품 코드가 실제 목록에 등록됐다는 뜻은 아닙니다. Microsoft COUNTIF의 와일드카드 설명
기존 다운로드는 처음 제시한 여섯 입력을 위한 COUNTIF 수식을 그대로 담고 있습니다. 다음은 원본을 건드리지 않고 비교하는 추가 연습이며, 다운로드 파일에 새 검사가 적용됐다는 뜻은 아닙니다. 사본에 빈 시트 코드추가검산을 만들고 A1:D1을 입력 코드·원래 일치 수·문자 그대로 일치 수·판정으로 정합니다. A2에는 P-00?를 입력합니다. 이 시트에는 기존 Codes 시트의 목록 규칙이 없으므로 반례 입력 때문에 원래 규칙을 해제할 필요가 없습니다.
B2: 원래 COUNTIF 방식
=COUNTIF(Codes!$F$2:$F$4,A2)C2: 특수문자를 문자로 처리한 일치 수
=IF(A2="","입력 필요",SUMPRODUCT(--(Codes!$F$2:$F$4=A2)))D2: 정확히 한 항목일 때만 목록 있음
=IF(C2="입력 필요","입력 필요",IF(C2=1,"목록 있음","확인 필요"))C2는 각 기준 코드와 A2를 =로 직접 비교한 TRUE/FALSE를 --로 1/0으로 바꾸고 더합니다. A2의 별표·물음표를 검색 패턴으로 해석하거나 A2가 >로 시작한다고 크기 비교 조건으로 바꾸지 않습니다. Microsoft SUMPRODUCT 설명의 배열 합산을 이 비교 결과에 적용한 추가 수식입니다. 원본 A2 문자열 자체는 수정하지 않습니다.
A2=P-00?에서 B2=1이지만 C2=0·D2=확인 필요여야 합니다. A2를 P-02*로 바꿔도 1·0·확인 필요입니다. A2에 >P-019를 입력해도 C2는 0이어야 합니다. P-010은 1·1·목록 있음, 후행 공백이 있는 P-001은 0·0·확인 필요입니다. A2를 비우면 C2·D2는 입력 필요입니다. 대소문자를 구분하지 않는 기존 정책은 그대로라 p-020도 일치 수 1입니다. 대문자 강제나 대소문자별 서로 다른 코드가 필요한 경우에는 이 수식과 별도로 비교 정책을 정해야 합니다. 원본이나 기준 셀 자체에 실제 오류 값이 있다면 그 오류부터 해결합니다.
새 입력용 목록 규칙은 따로 확인하기
첨부 A2:A7에는 F2:F4를 원본으로 하는 목록 규칙과 중지 오류 알림이 저장되어 있습니다. 하지만 이미 들어 있는 P-999와 후행 공백 입력을 지워 정상 파일처럼 보이게 만들지 않았습니다. 가져온 데이터의 오류를 찾아 복구하는 과정 자체가 이 연습의 대상입니다.
-
A2:A7을 선택하고 데이터 유효성 검사 설정을 엽니다.
-
목록 종류와 원본
=$F$2:$F$4를 확인합니다. -
오류 알림의 중지 스타일을 확인합니다.
-
사본의 한 셀에서 허용 목록을 열고 P-010을 선택합니다.
-
B열 결과가1, C열이 목록 있음인지 확인한 뒤 최초 입력으로 돌아갑니다.
규칙 창을 확인하는 것과 기존 모든 값이 맞는지를 확인하는 것은 다른 점검입니다. 알림이 나타나지 않았다는 이유로 기존 P-999가 등록된 코드라고 판단하지 않습니다. 복사·붙여넣기 후에는 해당 셀에 규칙이 남아 있는지와 B:C 결과를 각각 봅니다. 실제 Excel 알림창은 이 자료 작성 과정에서 직접 실행하지 않았습니다.
두 오류를 원본 근거로 복구하기
이번 가상 원장의 복구 정보는 4행의 의도한 코드가 P-020, 5행은 P-001이라는 것입니다. 사본에서 A4를 P-020으로, A5를 공백 없는 P-001로 고칩니다. B2:B7은 모두1이고 C2:C7은 모두 목록 있음이 되어야 합니다. D열은 최초 오류 입력의 기대값이므로 수정 후 C열과 달라질 수 있습니다.
P-999를 P-020으로 바꾸는 것은 문자열 모양만 보고 추측한 자동 교정이 아닙니다. 이 실습에서 별도로 정한 복구 입력을 적용한 것입니다. 실제 미등록 코드를 만났을 때도 상품 원장이나 원천 주문으로 의도를 확인해야 합니다. 모르는 값을 가장 비슷한 코드로 자동 치환하면 잘못된 상품으로 정상 처리될 수 있습니다.
후행 공백은 원본과 대조한 뒤 해당 셀에서 제거합니다. 모든 코드를 숫자로 변환하거나 하이픈을 한꺼번에 지우지 않습니다. P-001과 P-010의 0은 식별자의 일부이며 계산 대상 숫자가 아닙니다. 원본 코드가 숫자만 있는 경우에도 앞자리0을 보존할지 업무 규칙을 먼저 확인해야 합니다.
검사값이 예상과 다를 때 범위를 좁히기
| 증상 | 먼저 확인할 곳 | 복구 판단 |
|---|---|---|
| 정상 P-010도0 | F2:F4와 수식의 고정 범위 | 기준 원장에 코드가 있는지 확인 |
| 모든 행이 같은 판정 | A행 상대 참조 | B3는 A3을 읽도록 복원 |
| 한 코드의 일치 수가2 | 기준 목록 중복 | 원장 관리자 확인 후 중복 정리 |
| 붙여넣기 뒤 규칙이 없음 | A셀 데이터 유효성 검사 | 규칙 복구와 B:C 감사를 모두 수행 |
| 공백을 지워도0 | 다른 문자·코드 자체 | 원본 근거 없으면 확인 필요 유지 |
기준 목록을 F5 이후로 늘리면 B열 수식과 A열 목록 규칙도 새 범위를 가리켜야 합니다. 한쪽만 늘리면 드롭다운에서는 보이는데 진단은 확인 필요가 되거나 반대가 될 수 있습니다. 확장 전에는 사본을 보존하고, 새 코드 한 개를 넣어 목록 선택과 수식 판정을 함께 검산합니다.
초기 오류 상태를 다시 보고 싶으면 A4=P-999, A5=P-001 뒤 공백 한 칸으로 되돌립니다. B4:B5가0이 되는지 확인합니다. 이전 결과를 메모할 때는 행 번호와 원본 값을 함께 남겨, 코드 목록의 몇 번째 항목과 잘못 연결하지 않도록 합니다.
이 검사가 보장하지 않는 것
처음 여섯 입력에 대한 완료 조건은 오류 두 행을 구분해 설명하고, 정해진 복구 후 여섯 행의 일치 수가 모두 1인지 확인하는 것입니다. 다만 임의의 새 코드를 원래 COUNTIF 수식에 넣으면 와일드카드 패턴 때문에 목록 있음이 나올 수 있습니다. 따라서 이 문구 하나만으로 목록의 실제 문자열과 일치한다고 일반화하지 않습니다.
특수문자가 섞인 외부 입력까지 다루려면 앞의 코드추가검산에서 P-00?와 P-02*가 확인 필요로 남는지 먼저 비교하세요. 보강한 비교식도 기준 목록의 정확성·중복·대소문자 정책을 대신하지 않으며, 상품의 판매 가능 여부·재고·주문 실재를 확인하는 기능은 없습니다. 기존 첨부와 추가 수식의 적용 범위를 구분한 뒤 업무 사본에 옮깁니다.
이 파일의 목록 검사는 상품 코드의 형태·대소문자·입력 권한을 모두 통제하는 시스템이 아닙니다. 기준 목록에 새 잘못된 코드가 추가되면 그 코드도 목록 있음으로 보일 수 있습니다. 원장 변경의 승인과 가져온 데이터의 대조를 별도로 유지해야 합니다. 잘못된 행은 근거가 생길 때까지 확인 필요로 남겨도 됩니다.
Microsoft COUNTIF 문서는 조건에 맞는 셀 수와 대소문자 비교 특성을 설명합니다. Microsoft 유효성 검사 문서는 목록과 오류 알림 설정을 안내합니다. 첨부의 셀·수식·저장 규칙을 확인했으며 Microsoft Excel 앱에서 직접 입력하거나 알림창을 실행한 기록은 없습니다.
Use the COUNTIF function in Microsoft Excel | Microsoft Support는 한 조건에 맞는 셀 수를 세는 함수의 동작을 안내합니다.
Apply data validation to cells | Microsoft Support는 입력할 데이터의 종류나 값을 제한하는 기능을 설명합니다.
출처 원문 수집일: S1 2026-09-23; S2 2026-09-23.
작성자 소개
