안녕하세요. 일상에서 얻는 정보를 모두 기억할 수 없기 때문에 일상에서 일어나는 에피소드들의 정보들을 기록하여 기억하고자 하는 시점에 기록된 정보를 활용할 수 있도록 글을 쓰고자 하는 이작가입니다.
저 역시 지나간 모든 과거의 경험들을 전부 기억할 수 없기 때문에 경험을 통해 얻은 지식과 정보를 기록함으로써 필요한 시점에 과거의 경험의 정보를 활용해 보기 위해 이 글을 작성합니다.
기록된 정보들이 많은 분들에게 도움이 될 수 있도록 전달될 수 있길 바라겠습니다.
엑셀(excel) 작업으로 나이 계산 또는 기간과 기간 차이를 확인하는 작업을 하는 경우가 생기게 되는데 이번 내용에서는 주민 번호만으로 나이를 계산 하는 수식 작성 방법에 대해 기록을 해보고자 합니다.
주민등록번호는 년, 월, 일, 성별, 일련번호의 구성으로 개인마다 서로 다른 주민등록번호를 갖고 있습니다.
여기서 년, 월, 일, 성별까지만 알 수 있다면 해당 주민등록번호의 현재 나이와 성별을 구분할 수 있는데 주민 등록 번호만으로 생년월일, 나이, 성별을 결과값으로 계산하는 수식을 작성해 보도록 하겠습니다.
1. 주민등록번호로 나이, 생년월일, 성별 계산 수식 작성.
주민등록번호로 생년월일, 나이, 성별을 구분하고 계산하는 수식을 작성하기 위해 A열은 이름, B열은 주민등록번호를 작성하고 C, D, E열에는 각각 생년월일, 나이, 성별을 계산하여 결과값으로 표기 되도록 수식을 작성할 수 있는 예제 서식을 작성해 보았습니다.
각각의 결과값을 표기 할 수 있는 수식을 작성해 보도록 하시죠.
1-1. 주민등록번호 생년월일 수식 작성.
생년월일을 결과값으로 표기 하기 위한 수식은 C2셀에 다음과 같이 수식을 작성하고 필요한 셀까지 수식 복사하여 사용 할 수 있습니다.
| =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")) |
주민등록번호에서 생년월일을 결과값으로 표기 하기 위해서는 1900년대생과 2000년대생으로 우선 구분해야 하기 때문에 주민등록상 성별 구분을 1 또는 2로 구분하는 경우에는 1900년대생이 되겠고, 3 또는 4로 성별이 구분될 경우에는 2000년대생으로 구분해 볼 수 있습니다.
따라서 "=IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="2")" 성별 구분을 1 또는 2로 구분할 경우에는 "19&TEXT(LEFT(B2,6),"00-00-00")" 1900년대생으로 표기하고, 그렇지 않은 경우에는 "20&TEXT(LEFT(B2,6),"00-00-00"))" 2000년대생으로 구분하여 표기 하라는 수식을 작성 하여 결과값을 표기 할 수 있습니다.
mid함수는 문자열의 지정 위치에서부터 문자를 지정한 개수 만큼 결과값으로 반환하는 함수로 MID(B2,8,1)은 B2셀에 입력된 문자중에서 8번째 자리에 입력된 첫자리 문자만 결과값으로 반환하는것으로 주민등록번호를 "-"를 작성해서 입력할경우 8번째 자리에서 성별을 구분할 수 있수 있습니다.
1-2. 주민등록번호 나이 계산 수식 작성.
주민등록번호로 나이를 계산하기 위해서는 1-1에서 작성한 생년월일을 참조하여 D2셀 기준으로 다음과 같이 작성 할 수 있고 필요한 셀까지 수식 복사 하여 사용할 수 있습니다.
| =DATEDIF(C2,TODAY(),"Y") |
위 수식은 날짜와 날짜 사이에 발생하는 기간 차이를 계산하는 함수로 datedif(시작일,종료일,"날짜단위")로 수식을 작성해 주게 되면 시작일과 종료일 사이에 발생하는 기간 차이를 계산하는것으로 날짜단위는 "Y"를 입력해 주어 연도 차이를 계산 할 수 있습니다. 따라서 시작일은 C2셀이 되겠고, 종료일은 today()=현재날짜, 시작일과 종료일 날짜 차이 단위는 연도를 계산 하기 위해서 "Y"를 입력해 주어 나이를 계산할 수 있습니다.
다만 위 수식은 연도와 연도만을 비교하는것이 아니고 연,월,일과 연,월,일을 비교 하기 때문에 만나이로 계산됩니다.
따라서 한국나이로 계산하기 위해서는 =DATEDIF(C2,TODAY(),"Y")+1로 수식을 작성해 주어야 한국난이를 계산할 수 있게 됩니다.
앞으로는 대한민국 나이체계도 만나이를 도입하려는 움직임이 있기 때문에 만나이계산과 연단위 나이계산수식 간단한 차이지만 알아두는것이 좋겠습니다.
1-3. 주민등록번호 성별 구분 수식 작성.
주민등록번호로 성별을 구분하기 위한 수식은 B열에 작성된 주민등록번호를 참조하여 구분할 수 있는데 E2셀 기준으로 수식을 작성하면 다음과 같이 작성 할 수 있고 필요한 셀까지 수식 복사하여 사용할 수 있습니다.
| =IF(OR(MID(B2,8,1)="1",MID(B2,8,1)="3"),"남자","여자") |
1과 3은 남자이기 때문에 주민등록번호 8번째 자리가 1과 3이 아니라면 여자 성별이라는 결과값을 가져 오는 수식을 작성 할 수 있습니다.
2. 주민등록번호 자체만으로 나이 계산 수식 작성 방법.
위에서 나이 계산을 하기 위해서 C열에 생년월일을 계산하는 수식을 작성하여 C열을 참조해서 D열에 나이를 계산하는 수식을 작성했는데 B열에 작성된 주민등록번호 자체를 참조해서 나이를 계산하는 수식은 =DATEDIF(C2,TODAY(),"Y")수식에서 생년월일을 참조하는 C2셀을 C열에 작성된 수식으로 대체 하면 B열에 작성된 주민등록번호 자체를 참조하여 나이를 계산 할 수 있고 수식은 다음과 같이 작성 할 수 있습니다.
| =DATEDIF(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")), TODAY(),"Y") |
마찬가지로 위 수식은 만나이 계산이기 때문에 한국나이를 계산하기 위해서는 위 수식에서 +1을 해주어 한국나이를 계산 할 수 있으니 참고 하시길 바랍니다.
이상으로 "글쓰는이작가의 엑셀(Excel) 시간"은 마무리 하고,
"주민등록번호로 생년월일, 나이, 성별 구분하고 계산하는 수식 작성" 예제 샘플을 하단에 첨부하였으니 필요한곳에서 활용할 수 있는 수식으로 적용해 보시길 바라겠습니다.
긴 글 읽어주신 여러분들께 감사 말씀 드리며 많은 도움이 될 수 있기를 바라겠습니다.
※ 주민등록번호로 생년월일, 나이, 성별 구분하고 계산하는 수식 작성 샘플 예제 서식 다운로드.
'엑셀(Excel)' 카테고리의 다른 글
| 엑셀(Excel) 급여대장 서식과 공제액 계산 수식을 작성해 보자. (0) | 2022.11.22 |
|---|---|
| 엑셀(Excel) 시작일자(날짜)로부터 종료일자(날짜)까지 경과일 계산 하는 수식을 작성해 보자. (0) | 2022.11.22 |
| 엑셀(Excel) 재고 관리 서식과 입고 출고 계산 수식을 작성해 보자. (0) | 2022.11.19 |
| 엑셀(Excel) 참조 영역 범위 내에서의 사이값 또는 구간값의 수식 작성을 해보자. (0) | 2022.11.18 |
| 엑셀(Excel) 조건부 서식 텍스트 변환 if수식 vlookup수식 색깔 음영 폰트 사이즈 굵기등의 수식 결과값 반영하는 기능과 수식을 사용해 보자. (0) | 2022.11.18 |
댓글