[엑셀/한셀] 주민등록번호 → 생년월일/나이/성별 변환 함수
일단 주민등록번호의 구성부터.
|
ㄱㄴㄷㄹㅁㅂ - ㅇ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는 주민등록번호가 있는 셀의 위치다. 왜 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일 경우 남자를 출력하고 아닐 경우 여자를 출력하는 함수. 뒤의 "남자", "여자"는 다른 글자 ("남" "녀" 라든지)로 바꿔도 무방
