엑셀 주민번호 생년월일, 성별, 나이 추출 방법 (2000년대 출생 포함)

 

이번시간에는 엑셀에서 주민번호(주민등록번호)를 활용해 나이와 성별을 구하는 방법을 알아보도록 하겠습니다.

간단히 엑셀 함수를 적용해 주민번호 만으로 나이와 성별을 추출할 수 있습니다.

 

외국인이 포함된 경우 밑에 링크를 첨부해 두도록 하겠습니다.

필요하신 분께서는 하단 링크를 참고하시기 바랍니다.

엑셀 나이 계산하기

 

나이를 계산할 때 올해 연도에서 앞자리 연도를 빼서 계산합니다.

이 방법으로 수식을 만들어 보면 아래와 같습니다.

 

아래 보이는 "D4" 만 수정하시면 됩니다.

 

=YEAR(TODAY())-IF(OR(MID(D4,8,1)="1",MID(D4,8,1)="2"),19&LEFT(D4,2),IF(OR(MID(D4,8,1)="3",MID(D4,8,1)="4"),20&LEFT(D4,2),""))+1

 

① YEAR(TODAY()) : 오늘 날짜에서 연도를 산출

② IF(OR(MID(D4,8,1)="1",MID(D4,8,1)="2"),19&LEFT(D4,2) : 주민번호(D4)의 8번째 자리 첫 번째 글자가 1 또는 2 이면 주민번호( D4) 앞자리 두 숫자에 19를 붙임 <결괏값 : 1991>

③ IF(OR(MID(D4,8,1)="3",MID(D4,8,1)="4"),20&LEFT(D4,2),"")) : 주민번호(D4)의 8번째 자리 첫 번째 글자가 3 또는 4 이면 주민번호(D4) 앞자리 두 숫자에 20를 붙임

 

정리하자면, 주민번호 뒷자리가 1~4 중 어떤 숫자로 시작하냐에 따라 태어난 연도를 설정하고

올해 연도에서 태어난 연도를 빼고 1을 더하면?!

일반적으로 우리가 몇 살이다 얘기하는 한국 나이가 나오게 됩니다!

 

엑셀 성별 구별 방법

 

성별 계산은 위에서 설명드린 나이 계산에 사용되었던 방법과 비슷합니다.

아래 보이는 "D4" 만 수정하시면 됩니다.

 

=IF(OR(MID(D4,8,1)="1",MID(D4,8,1)="3"),"남자",IF(OR(MID(D4,8,1)="2",MID(D4,8,1)="4"),"여자",""))

 

[참고 : 주민번호 뒷자리 구별방법] 내국인 1900년대 출생 | 남자 1, 여자 2 내국인 2000년대 출생 | 남자 3, 여자 4

 

① IF(OR(MID(D4,8,1)="1",MID(D4,8,1)="3"),"남자" : 주민번호(D4)의 8번째 자리 첫 번째 글자가 1 또는 3 이면 남자

② IF(OR(MID(D4,8,1)="2",MID(D4,8,1)="4"),"여자" : 주민번호(D4)의 8번째 자리 첫 번째 글자가 2 또는 4 이면 여자

 

엑셀 주민번호 앞자리 : 텍스트 나누기

 

텍스트 나누기 기능을 활용해 주민번호 앞 생년월일 7자리를 추출해 보도록 하겠습니다.

먼저

주민번호 앞자리를 추출하고자 하는 셀 전체를 선택 후

데이터 -> 텍스트 나누기를 클릭해 주세요.

 

'구분 기호로 분리됨'을 클릭합니다.

주민번호는 대부분 " - " 구분 기호를 함께 써 놓기 때문에 구분하기 쉬워요.

만약 구분 기호 없이 13자리가 붙어 있다면 '너비가 일정함'을 선택하면 됩니다.

 

기타 선택 -> " - "를 입력해 주세요.

데이터 미리 보기를 확인해 보면 구분 기호를 기준으로 주민번호가 나눠진 걸 확인할 수 있습니다.

 

데이터 서식을 선택하고 지정하는 창인데요.

원하는 서식에 맞게 선택하시면 선택한 서식으로 텍스트가 나눠집니다.

데이터 미리 보기 확인 후 마침을 클릭해 주세요!!

 

주민번호 앞자리와 뒷자리로 나뉜 걸 확인할 수 있습니다 ^^

 

2010년도 이후 출생한 사람들도 마찬가지로

위 코드를 활용하면 오류 없이 나이와 성별을 계산할 수 있습니다.

 

엑셀 외국인 성별, 나이 추출 방법

 

https://thedayhascom.tistory.com/203

엑셀 주민번호 생년월일, 성별, 나이 추출 방법 (2000년대 출생 포함)