139 엑셀 데이터유효성 검사 주민등록 번호

139_엑셀 데이터유효성 검사– 데이터 관리 요령 (13)

엑셀에서 날짜와 시간을 일정 서식으로 제한 할 수 있듯이 기존의 좀 특별한 문자들이나 숫자들도 역시 일정 서식이 아니면 입력이 안되도록 막을 수 있습니다. 가장 흔한 것이 주민 등록 번호인데 주민 번호는 13자리의 번호로 되어있죠? 만약 실수로 12자리가 입력된다면 입력 자체가 안되도록 막을 수 있습니다. 또 역시 날짜나 시간과 마찬가지로 사용자 서식을 이용하면 여러 개의 조건을 동시에 걸어서 서식 제한을 할 수 있습니다. 예를 7번째 숫자는 항상 1이나 2가 되야 하죠. 이번 포스팅은 주민 번호를 가지고 문자열 서식 제한에 대하여 포스팅 하겠습니다.

139연습파일.xlsx

139연습파일_완성본.xlsx

1. 주민 등록 번호 입력하기

   주민등록 번호는 자주 사용하는 형태 이므로 엑셀의 기본 데이터 형식이 들어가 있습니다. 간단히 복습을 하고 가겠습니다.

    

   주민 등록 번호에 –를 넣게 되면 문자로 바뀝니다. 일단 숫자만 13자리를 입력 하자 고요.

   

    

    

   이렇게 표시 되도 당황 하지 않는 것 알죠?

   

    

   셀 서식 창 열기

   컨트롤 + 1

    

   혹은 아무 셀이나 우클릭으로 셀 서식을 클릭 하세요.

   

    

    

   표시 형식에서 기타 >> 주민 등록번호를 골라 주면 됩니다. 열은 전체를 선택 해야하죠. 여기서 주민 번호가 표시 되는 이유는 로캘이 한국어로 되어있기 때문에 한국에서 자주 쓰는 형식들이 표시 되는 것 입니다.

   

    

2. 데이터 유효성 검사로 문자 길이 제한하기

   주민 번호는 항상 13자리 입니다. 데이터 유효성 검사로 주민 번호 숫자만 13자리로 제한해도 많은 오류를 막을 수 있습니다.

    

   A열을 선택하고 데이터 유효성 검사를 클릭 합니다.

   

    

    

   유효성 조건을 텍스트 길이로 하시고 길이는 13으로 합니다.

   

    

    

   이제 12자리 숫자는 입력이 안됩니다.

   

    

    

    

    

3. 여러가지 제한 조건 걸기

   지금은 단순히 숫자 길이로만 제한을 걸었지만 누구나 주민 번호는 더 까다롭게 생성된다는 것을 알고 있습니다. 지금 한가지 조건만 더 걸어서 7번째 숫자가 1과 2만 되도록 하겠습니다.

    

   LEN은 LENGTH로서 문자 길이를 반환합니다.

   

    

    

   MID함수로 7번째 숫자를 뽑습니다. MID 함수를 모르시면 함수 중급편을 참고 하세요.

   

    

    

   7번째 숫자가 1이죠?

   

    

    

   이번에도 AND함수의 조합 입니다.

   

    

   LEN은 주민 번호 13자리를 제한합니다.

   MID<=는 7번째 주민번호가 2와 1만을 갖도록 합니다. VALUE는 MID로 뽑은 텍스트를 숫자로 형식으로 바꾸어 줍니다.

    

   제한 대상을 사용자 지정으로 하고 수식을 복사하여 붙였습니다. 여기서 A1은 A1셀이 아니라 A1부터 하나하나 셀을 계산하는 배열이라고 했고 오류는 머리 행 때문에 나는 것이니 무시하면 된다고 했습니다.

   

    

    

   이제 7번째 자리에 3이 들어가면 입력이 되질 않습니다.

   

    

   주민 번호는 훨씬 복잡한 룰을 거쳐서 생성 되지만 이처럼 중첩의 원리를 알면 주민번호의 정확한 룰을 엑셀에 적용 할 수 있습니다.