Ver. 2.0/일하고 써보고 찾아보고

[엑셀/한셀] 주민등록번호 → 생년월일/나이/성별 변환 함수

mazefind 2020. 11. 30. 01:09
728x90
728x90

일단 주민등록번호의 구성부터.

ㄱㄴㄷㄹㅁㅂ - ㅇXXXXXX

여기에서 ㄱㄴ 는 연도, ㄷㄹ는 월, ㅁㅂ는 이리, 그리고 ㅇ의 경우 성별을 의미한다. 참고로 ㅇ 칸은 대부분 1, 2, 3, 4 중 하나인데 아닌 것들도 있음. 9나 0의 경우 실제 해당 번호가 찍힌 주민등록증을 볼 가능성은 없다고 보는게 맞고, 제적등본(호적등본)같은걸 발급해보면 자기 조상님들 중에 이 번호가 부여된 경우가 있음.

구분 7번째 자리 숫자 출생연도 성별
주민등록번호 9 1800~1899 남성
0 1800~1899 여성
1 1900~1999 남성
2 1900~1999 여성
3 2000~2099 남성
4 2000~2099 여성
외국인등록번호
거소신고번호
5 1900~1999 남성
6 1900~1999 여성
7 2000~2099 남성
8 2000~2099 여성

이후 XXXXXX 6자리는 지역코드와 고유번호(출생신고순서순), 그리고 검증번호인데 이건 어차피 2020년 10월 이후 지역번호 삭제로 인해 이후는 랜덤 부여되고 또 우리가 변환하고 싶은 생년월일, 나이, 성별과는 무관한 정보이므로 굳이 몰라도 됨.

주민등록번호가 있는 셀의 형식은 주민번호가 다 들어가도 되고 (*이나 X 등으로) 마스킹이 되어 있어도 사용할 수 있다. 다른 블로그를 찾아보면 보통 주민등록번호만 사용할 수 있게 되어 있는데 사무실에서 일해보니 가끔 외국인이 섞인 경우가 있음. 이를 대비해 함수를 약간 수정해서 거소번호나 외국인번호도 쓸 수 있게 고쳐두었다.

주민등록번호, 외국인등록번호 변환 함수

 

1. 생년월일

=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="2"),19&TEXT(LEFT(B2,6),"00-00-00"),20&TEXT(LEFT(B2,6),"00-00-00"))

=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="2")
B2셀의 8번째 글자가 1 또는 2인지 판별해서
19&TEXT(LEFT(B2,6),"00-00-00")
1 또는 2에 해당한다면 19를 먼저 출력한 다음 B2셀의 처음 6자리 글자를 00-00-00 형태로 출력하고
,20&TEXT(LEFT(B2,6),"00-00-00"))
그렇지 않다면 20을 먼저 출력한 다음 B2 셀의 처음 6자리 글자를 00-00-00 형태로 출력할 것

외국인등록번호가 포함되어 있다면 다음과 같이 사용
=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="2",MID(B2,8,1)="5",MID(B2,8,1)="6"),19&TEXT(LEFT(B2,6),"00-00-00"),20&TEXT(LEFT(B2,6),"00-00-00"))

여기서 B2는 주민등록번호가 있는 셀의 위치다. 왜 7번째가 아니라 8번째냐면 엑셀이 보기에 7번째 글자는 "-"이기 때문

해석은 그냥 내가 넣은거고 이것저것 따지기 싫다면 위 함수를 복붙한 다음에 B2 부분만 주민등록번호 있는 셀로 바꿔주면 됨.

 

2. 나이

만나이 계산: =DATEDIF(C2,TODAY(),"Y")
C2 셀에서 오늘까지의 기간을 계산해 연도만 출력
한국나이 계산시: =DATEDIF(C2,TODAY(),"Y")+1

여기서 C2는 생년월일이 들어간 셀이다. 셀의 형식은 '일반'이라도 되고 '날짜'로 되어 있어도 가능.

 

3. 성별구분

=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="3"),"남자","여자")
외국인등록번호가 포함되어 있다면 다음과 같이 사용
=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="3",MID(B2,8,1)="5",MID(B2,8,1)="7"),"남자","여자")

마찬가지로 B2는 주민등록번호가 들어간 셀이다. 주민등록번호의 8번째 글자(숫자)가 1, 3, 5, 7일 경우 남자를 출력하고 아닐 경우 여자를 출력하는 함수. 뒤의 "남자", "여자"는 다른 글자 ("남" "녀" 라든지)로 바꿔도 무방

 



-mazefind (트위터 / 인스타 / 유튜브 / 카카오)

728x90
728x90