엑셀 93강] 유효성검사 - 주민등록번호 입력 제한하기

 

엑셀 유효성 검사 기능을 소개하면 자주 질문 받는 내용들이 있습니다.

여러 사람이 입력한 데이터를 가져와 업무에 필요한 자료로 가공할 때 날짜와 주민등록번호 같이 입력 형식이 달라 데이터를 처리하는데 애를 먹은 적이 많다며 어떻게 제한하는 방법이 없냐는 질문입니다.

 

주민등록번호를 예를 들어보겠습니다.

주민번호는 가운데 하이픈(-)을 포함하여 총 14자리를 입력 받아야 하고 그 외 하이픈(-)이 빠졌거나 자릿수가 다르면 입력할 수 없도록 제한을 해보겠습니다. 

 

유효성 검사는 데이터를 입력할 때 잘못된 데이터가 입력되지 않도록 제한하는 기능입니다.

유효성 검사도 단순하게 기능을 사용할 수도 있지만 함수식을 어떻게 사용하느냐에 따라 활용이 다양해 집니다. 

 

 

먼저 주민등록번호를 입력할 범위를 블록지정합니다.

[데이터]-[데이터 유효성 검사] 메뉴를 선택하고 설정 탭에서 제한 대상을 사용자 지정으로 선택합니다.

 

 

수식 입력창에 =AND(LEN(C4)=14,FIND("", C4)=7) 이라고 입력합니다.

1. And 함수를 사용하여 조건 2개가 모두 일치할 때 주민등록번호 입력되도록 합니다.

2. 첫 번째 조건은 Len 함수를 사용하여 입력된 데이터 길이를 구합니다. 구한 글자수가 14개인지 비교하는 수식입니다.

LEN(c4)=14

3. 두 번째 조건은 Find 함수를 사용하여 입력된 데이터에서 하이픈을 찾습니다. Find 함수는 하이픈(-)이 입력된 자릿수를 구합니다. 

자릿수가 7이라는 건 입력한 주민번호 7번째 위치에 -(하이픈)이 있다는 말이겠죠.

FIND("-",C4)>=7

 

 

 

처음 입력한 주민등록번호는 하이픈을 포함하여 14자리를 입력 했더니 정상적으로 입력이 됩니다.

두 번째 입력한 주민등록번호는 하이픈을 빼고 입력 해보니 오류 메시지를 띄우네요.

그리고 -(하이픈)의 위치가 7번째 위치에 입력이 안되는 경우에도 오류 메시지를 띄우게 됩니다. 

 

 

오류 메시지를 작성하는 것도 좋은 방법입니다. 

[오류 메시지] 탭을 눌러 오류 메시지 내용을 입력하고 [확인] 버튼을 누릅니다. 

 

 

강좌가 도움이 되길 바라며 마치겠습니다. 

즐거운 주말 보내세요~