주민등록번호를 기준으로 생년월일, 나이, 성별을 구분해야 하는 작업을 하는 경우가 있습니다.
주민등록번호의 구성을 먼저 확인해 보면
앞번호 여섯자리, 뒷번호 일곱자리로 구성되어 있으며
앞번호는 생년, 월, 일이며
뒷번호 첫자리는 성별을 구분합니다.
1900년대 출생자의 경우 1은 남자, 2는 여자
2000년대 이후 출생자의 경우 3은 남자, 4는 여자입니다.
뒷번호에는 출생 지역 등의 정보도 있으나
나이와 성별 구분하는데 필요가 없기에 패쓰~~
고객명, 주민등록번호를 기준으로 우선 생년월일을 구하고 그 이후에 나이와 성별을 계산했습니다.
1. 생년월일
생년월인은
뒷번호 첫자리가 1,2이면 1900년대 출생자이고
3, 4이면 2000년대 출생자이기에
IF, TEXT, LEFT 함수를 사용해서
=IF(OR(MID(C3,8,1)="1",MID(C3,8,1)="2"),19&TEXT(LEFT(C3,6),"00-00-00"),20&TEXT(LEFT(C3,6),"00-00-00"))
수식을 지정했습니다.
뒷번호 첫자리가 1 또는 2인 경우에는
19를 붙여주고,
뒷번호 첮자리가 3 또는 4인 경우에는
20을 붙여줘서 생년월일을 불러왔습니다.
2. 나이1
나이1은
DATEDIF함수를 이용해서 생년월일에서 오늘일자 기준으로 나이를 계산했습니다.
=DATEDIF(D3,TODAY(),"y")
연도의 경과를 계산하기에 한국나이와 차이가 있으며
2004년 1월 12일 출생자의 경우 (오늘 2019년 1월 11일)
14년 1일로 계산이 되며 년도는 14년이 됩니다.
3. 나이2
YEAR 함수를 이용해서 나이를 구하는 방법이 있고
년도를 기준으로 해당 년도를 포함해서 계산을 합니다.
=YEAR(TODAY())-YEAR(D3)
오늘일자를 기준으로 해당하는 년도에서 생년월일의 년도를 차감해서 계산합니다.
한국나이를 기준으로 하자면 +1을 해줘야겠네요.
주민등록번호 뒷번호 첫자리가
1, 3이면 남자
2, 4면 여자이며
해당 숫자를 기준으로 남자, 여자의 성별을 구분할 수 있습니다.
주민등록번호에서
MID함수로 뒷번호 첫자리를 지정해서
1 또는 3인 경우에 남자, 그렇지 않은 경우에 여자로 표시하도록 수식을 지정했습니다.
=IF(OR(MID(C29,8,1)="1",MID(C29,8,1)="3"),"남자","여자")
"남자","여자"로 결과 값이 출력되게 지정했고
남으로 표시하고자 하면 "남" 이런 식으로 수정하면 됩니다.
주민등록번호로 직접 나이를 계산하는 방법도 있습니다.
생년월일을 먼저 구분해주고 하는 방식과 동일하나 여러 수식을 병행해서 사용하기에
다소 복잡하게 보일 수 있으나 차근차근 하나씩 적용해 보면 크게 어렵지 않습니다.
1. 나이3
나이3은
=DATEDIF(TEXT(MID(C10,1,6),"00-00-00"),TODAY(),"y")
주민등록번호중 생년월일인 앞자리를 분리해서
생년월일에서 오늘일자를 기준으로 나이를 계산합니다.
몇년 몇일(개월)으로 나이를 계산하며 실제 한국나이와는 차이가 있습니다
해당 수식은 뒷자리의 출생년도(1900년도, 2000년도) 구분이 안되기에
041206-4111113
041206-2111114
출생년도가 다름에도 나이를 동일하게 계산합니다
또한, 기준년도(2019년)을 초과하는 경우 오류가 발생합니다.
100세를 넘어가는 경우는 거의 없기에 불필요할 수는 있으나
이러한 오류를 해결하기 위해서 나이4의 수식을 사용하면 됩니다.
2. 나이4
주민등록번호 뒷자리 첫번호가 1 또는 2인 경우에는
1900년도 출생자이며
3 또는 4인 경우에는 2000년도 출생자이기에
해당 번호를 기준으로
1 또는 2인 경우에는 19를 붙여서 1900-00-00 형식으로 만들어 준 후
DATEDIF를 사용해서 년도를 계산해줬습니다.
=DATEDIF(IF(OR(MID(B127,8,1)="1",MID(B127,8,1)="2"),19&TEXT(LEFT(B127,6),"00-00-00"),20&TEXT(LEFT(B127,6),"00-00-00")),TODAY(),"y")
B127은 주민등록번호입니다.
주민등록번호를 이해하고
엑셀함수를 활용할 수 있다면
성별구분, 나이 계산 등을 손쉽게 할 수 있습니다.
본 포스팅은 쿠팡파트너스 활동을 통해 일정액의 수수료를 제공받을 수 있습니다.
'엑셀스터디' 카테고리의 다른 글
| 가계부 양식 엑셀로 우리 집 수입 지출을 관리해보세요. (3) | 2021.01.27 |
|---|---|
| 엑셀 데이터 유효성 검사로 가계부 항목 엑셀 선택 박스 만들기 (0) | 2021.01.27 |
| 견적서 양식 엑셀로 편리하게 견적서를 작성해 보세요. (2) | 2021.01.27 |
| 경력증명서 양식 엑셀 파일로 간단하게 발급하기 (0) | 2021.01.25 |
| 엑셀 SUMPRODUCT 함수로 다양한 조건의 통계 값 계산하기 (0) | 2021.01.25 |
댓글