[엑셀] 주민등록번호 활용하기

엑셀에서 인력관리를 하는 경우 주민등록번호를 입력하는 경우가 많이 있습니다. 인사카드, 노무대장, 급여명세서, 회원정보 등 관리가 필요한 경우 주민번호를 입력합니다. 

주민번호만 입력하면 생년월일이나 성별구분은 따로 입력하지 않고도 정보를 알수 있습니다.

그래서 엑셀에서 주민번호를 활용하는 방법에 대해 몇가지 알아 보겠습니다.

아래 인사카드 이미지는 콤보박스에서 사번과 주민번호를 선택하면 주민번호를 DB에서 가져오고 성별과 생년월일은 주민번호에서 정보를 가져오는 예시입니다.

주민등록번호 정보 이해하기

대한민국에서 사용하는 주민번호 13자리는 아래와 같은 정보가 담겨져 있습니다.

① 앞자리 6자리 : 생년월일

② 7자리 : 성별

1800년대 : 남자-9, 여자-0

1900년대 : 남자-1, 여자-2

2000년대 : 남자-3, 여자-4

③ 8~11자리 : 지역코드

④ 12자리 : 출생순서

⑤ 13자리 : 오류검증코드

여기에서 생년월일과 성별을 활용하는 방법을 알아보겠습니다.

생년월일 알아내기

주민번호 앞자리 6자리는 생년월일입니다. 그래서 주민번호를 이용해서 생년월일로 값을 바꿀수 있습니다.

수식 = DATE(IF(VALUE(MID(주민번호,8,1))>2,"20"&LEFT(주민번호,2),"19"&LEFT(주민번호,2)), MID(주민번호,3,2), MID(주민번호,5,2))

DATE 함수 : 년도, 월, 일의 값을 날짜로 바꾸기

MID 함수, LEFT함수 : 주민번호에서 생년월일을 구분하여 추출

VALUE 함수 :  MID함수로 추출한 성별을 TEXT(문자)에서 VALUE(값)으로 변환

IF 함수 : 성별을 이용해 출생년이 1900년대인지 2000년대인지 판단

성별(남,녀) 구분

주민번호에서 생년월일 이후 첫번째 숫자(7번째)는 남녀를 구분하는 숫자입니다.

그래서 주민번호의  7자리 숫자( - 다음 숫자)를 알면  남녀를 판단 할 수 있습니다.

남녀를 판단하는 숫자 (9, 0, 1 2, 3, 4) 의 판단에는 여러 방법이 있지만 저는 8번째 숫자(13자리 중 7자리)가 홀수이면 "남자", 짝수이면 "여자"로 판단했습니다.

숫자가 홀수인지 짝수인지 판단하는 함수에는   ISODD함수와 ISEVEN함수가 있습니다.

수식  = IF(ISODD(MID(주민번호,8,1)),"남자","여자")

수식  = IF(ISEVEN(MID(주민번호,8,1)),"여자","남자")

MID 함수 : 주민번호에서 성별구분 숫자 추출 

ISODD 함수 : 숫자가 홀수이면 TRUE, 짝수이면 FALSE 반환

IF 함수 : 홀수이면 "남자", 짝수이면 "여자"

뒷자리 * 표시

간혹 청구서등을 받아보면 주민번호 뒷자리에 *표로 주민번호가 전부 표시되지 않게 작성된 문서등을 볼수 있습니다.

다른 시트(또는 DB)에서 주민번호를 불러 올때 주민번호 끝자리를 * 표로 표시하고 싶을때 방법입니다.

요즘에 개인정보 보호법으로 엑셀파일에 주민번호를 암호와 없이 수집해도 되는지는 모르겠습니다.

수식 = LEFT(주민번호,8)&"******"

이외에도 여러 수식을 활용 할 수 있지만 첫번재 것이 가장 간단하네요 ^^

셀서식에서 주민번호 서식 지정

주민번호를 입력할때 하이픈(-)을 입력하지 않고 주민번호 13자리를 입력하면 하이픈(-)이 자동으로 입력되는 방법입니다.

주민번호를 입력하고자 하는 셀에서 [셀서식] - [표시형식] - [기타] - [주민번호] - [확인] 하시면 됩니다.

관련글 보기

주민등록번호 오류 검증

카메라 기능 인사카드 만들기

문자 추출(반환) 하기

첨부파일

주민번호 활용.xlsx