엑셀 Excel 데이터 유효성 검사로 생산 제품 코드값 검증하기

데이터 유효성 검사는 아주 다양한 곳에 이용할 수 있습니다. 그 중 하나가 코드값 검증 입니다. 생산된 제품 코드가 규칙에 따라 제대로 입력이 되었는지 검증해 볼 수 있는데 사용자의 입력 실수를 미연에 방지할 수 있어서 많이 이용되고 있습니다. 이것이 가능한 이유는 데이터 유효성 검사에 수식을 입력할 수 있기 때문입니다. 설정한 영역에 입력된 데이터가 수식을 적용했을 때 false 로 판단되면 에러가 발생하고 입력이 안됩니다. 그리고 오류 메시지와 설명 메시지를 통해 규칙에 대한 설명을 할 수 있으므로 사용자가 제대로 된 값을 입력할 수 있도록 유도할 수 있습니다.

 

 

▼ 샘플에서 코드값 검증을 위한 규칙은 다음과 같습니다. 문자열의 제일 앞은 알파벳 C 가 들어가야 하고 총 코드 길이값은 3자리이며, C 문자 뒤부터는 숫자가 되야 합니다. 해당 규칙을 지키지 않은 데이터를 입력하게 되면 오류가 나고 입력을 할 수 없게 됩니다. 먼저 검증을 위해 코드값이 들어갈 셀을 선택합니다. 그리고 [데이터] 탭 > [데이터 도구] 그룹 > [데이터 유효성 검사] 리본을 클릭합니다.

 

▼ 코드 검증을 위한 식은 다음과 같습니다. 제일 앞에 있는 AND 함수는 true/false 를 판단합니다. AND 는 인수에 입력된 3가지 식이 모두 참이 되어야 true 되고 하나라도 거짓이면 false 가 되겠죠. 인수로 들어간 3개의 식에 대한 설명입니다.

 

=AND(LEFT(B3)="C",LEN(B3)=4,ISNUMBER(VALUE(RIGHT(B3,3))))

 

l  LEFT(A2)="C" 첫 글자가 C 알파벳이면 참을 반환합니다.

l  LEN(A2)=4  문자열의 길이가 4 이면 참을 반환합니다.

l  ISNUMBER(VALUE(RIGHT(A2,3))) RIGHT 함수를 이용해서 오른쪽부터 3자리를 잘라 VALUE 함수를 이용해서 문자를 숫자로 바꿉니다. 결과가 숫자인지 아닌지  ISNUMBER 함수 판단합니다. 숫자이면 참을 반환하겠죠.

 

 

설정 탭으로 이동한 뒤 제한 대상을 [사용자 지정]으로 선택합니다. 사용자 지정은 직접 수식을 만들어서 적용할 수 있는 옵션입니다. [수식] 란에 위에서 만든 식을 입력합니다. 

 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 데이터 유효성 검사 기능 간단하게 삭제하기 ▶ 엑셀 Excel 유효성 검사 기능 이용해 메뉴 목록 만들기 ▶ 엑셀 Excel 워크시트 유효성 검사 영역을 쉽게 찾기 ▶ 엑셀 Excel 데이터 유효성 검사를 이용해 숫자값을 제한하기 ▶ 엑셀 Excel 데이터 유효성 검사로 다양한 입력 조건 만들기

 

 

 

 

▼ 다음은 [설명 메시지]와 [오류 메시지]를 입력해야 합니다. 규칙을 정했으면 반드시 사용자에게 설명할 필요가 있기 때문입니다. 어떤 값을 넣어야 오류가 나지 않는지 설명 메시지 란에 입력합니다. 

 

▼ 모든 설정 작업이 끝났다면 창을 닫고 확인해 봐야겠죠. 데이터 유효성 검사가 적용된 셀에 코드값을 넣어 보았습니다. 입력한 C12 는 뒤에 숫자가 2자리 이므로 오류가 나겠죠. 그림과 같이 오류 메시지가 뜬다면 제대로 적용된 것입니다. 

 

▼ 마지막에 첫 번째 셀에 적용한 내용을 다른 셀에도 적용하기 위해서 셀 자동 채우기 점을 드래그합니다. 그럼 드래그한 셀에 적용된 수식의 셀 번호가 자동으로 증가하게 합니다. 그럼 첫 번째 셀에 적용한 규칙을 선택한 모든 셀에 자동으로 적용이 됩니다. 

※ 아래는 참고하면 좋을 만한 글들의 링크를 모아둔 것입니다. ※ ▶ 엑셀 Excel 데이터 유효성 검사 기능 간단하게 삭제하기 ▶ 엑셀 Excel 유효성 검사 기능 이용해 메뉴 목록 만들기 ▶ 엑셀 Excel 워크시트 유효성 검사 영역을 쉽게 찾기 ▶ 엑셀 Excel 데이터 유효성 검사를 이용해 숫자값을 제한하기 ▶ 엑셀 Excel 데이터 유효성 검사로 다양한 입력 조건 만들기