엑셀 자동 서식을 만들때 나이를 계산해야 하는 경우를 종종 볼 수 있습니다.
이때 나이 계산은 입력된 주민등록번호를 기준으로 나이를 계산하는 경우 어떤 함수를 사용해서 수식을 작성하는지 확인해 보도록 하겠습니다.
예제.
예제 서식에서 직접 입력한 B열의 주민등록번호로 C열에는 생년월일, D열과 F열은 나이, E열에는 성별을 결과값으로 계산하는 수식을 작성합니다.
D열의 나이는 C열 생년월일을 참조 하는 수식이고, F열의 나이는 B열의 주민등록번호 자체를 참조하는 수식으로 C열과 D열의 수식이 작동하는 방식을 이해 한다면 조금만 응용해서 F열의 수식을 작성할 수 있습니다.
예제 서식 다운로드.
| 주민등록번호 나이 계산 예제 서식 |
나이 계산.
주민등록번호로 성별, 나이 계산이 어려운것은 아니지만 수식을 작성하기 위해서 계산식으로 표현해 보고 넘어가도록 하겠습니다.
- 나이 = 현재 년도 – 태어난년도.
- 만나이 = (현재년도 – 태어난년도)에서 생일이 지나면 +1, 아니면 +0.
- 한국나이 = 현재년도 – 태어난년도 +1.
사실 우리는 년도를 기준으로 나이를 계산하는것이 편하지만 세계적으로 년도를 기준으로 나이를 계산하는 국가는 그리 많지 않고, 일제의 잔재에서 비롯된것이라고도 합니다.
그래서 2023.06월부터는 우리나라도 만나이로 계산하는 방식으로 바뀌긴 했지만 이러한 방식이 당연하게 받아들이기까지는 기간이 조금은 필요로 해 보이기도 합니다.
주민등록번호 나이 계산 수식.
이제 예제 서식에서 입력한 주민등록번호로 생년월일, 나이, 성별을 계산하여 결과값으로 표기 하는 수식을 작성해보고 F열에는 주민등록번호 자체로 나이를 계산하는 수식을 작성해 보도록 하겠습니다.
=IF(OR(MID(B2,8,1)=”1″,MID(B2,8,1)=”2″),
=DATEDIF(C2,TODAY(),”Y”)
=IF(OR(MID(B2,8,1)=”1″,MID(B2,8,1)=”3″),”남자”,”여자”)
=DATEDIF(IF(OR(MID(B2,8,1)=”1″,MID(B2,8,1)=”2″), |
주민등록번호로 생년월일, 나이, 성별을 계산 하기 위한 기준으로 주민등록번호 8번째 숫자가 1~4 어떤 숫자로 시작하는지에 따라 남,여 성별을 구분할 수 있습니다.
또 1,2로 시작하는 경우에는 1900년대생으로 분류할 수 있고, 3,4로 시작하는 경우에는 2000년대생으로 구분할 수 있겠습니다.
생년월일 계산 수식.
- =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″))
생년월일을 계산 하는 수식의 조건은 주민등록번호 8번째 숫자가 1 혹은 2일 경우에는 주민등록번호 앞자리 6개 숫자 앞에 19를 붙여서 년,월,일 형식으로 결과값을 가져오고, 3혹은 4의 경우에는 주민등록번호 앞자리 6개 숫자 앞에 20을 붙여서 년,월,일 형식으로 결과값을 가져오도록 할 수 있습니다.
수식으로 작성할때 조건은 2가지 조건으로 1혹은2일때 참값, 3혹은4일때 참값으로 1혹은2일때 참값, 그렇지 않을 경우의 참값으로 작성할 수 있기 때문에 if함수 조건은 OR(MID(B2,8,1)=”1″,MID(B2,8,1)=”2″)와 같이 한가지만 작성해 줄 수 있습니다.
생년월일을 참조하는 나이 계산 수식.
- =DATEDIF(C2,TODAY(),”Y”)
나이를 계산하는 계산식은 해당년도-태어난년도로 기간의 차이를 나이로 계산할 수 있기 때문에 C열의 생년월일을 참조하여 DATEDIF함수로 년도의 기간 차이를 계산할 수 있습니다.
성별 계산 수식.
- =IF(OR(MID(B2,8,1)=”1″,MID(B2,8,1)=”3″),”남자”,”여자”)
성별을 구분하는 기준은 주민등록번호 (하이픈 기호를 포함해서) 8번째 숫자가 1 혹은 3의 경우에는 남자, 2 혹은 4의 경우에는 여자로 구분할 수 있기 때문에 IF함수로 주민등록번호 8번째 숫자가 1 혹은 3의 경우에는 “남자”, 그렇지 않은 경우에는 “여자”로 결과값을 가져올 수 있습니다.
주민등록번호 전체를 참조하는 나이 계산.
- =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”)
D열에서 생년월일을 참조해서 나이를 계산하는 수식을 작성해 보았는데, D열의 수식은 C열에 생년월일 데이터값을 참조하기 때문에 수식을 2번 작성해야 하는 번거로움이 생기게 됩니다.
B열의 주민등록번호 전체를 참조해서 한번에 나이를 계산한다면 C열의 생년월일의 수식은 작성하지 않아도 되기 때문에 서식에서 필요없는 공간을 줄일 수 있습니다.
나이를 계산 하는 수식은 =DATEDIF(태어난 년도,해당 년도,”Y”)로 작성할 수 있기 때문에 태어난 년도에는 생년월일 수식을 인수로 작성해 주면 주민등록번호 전체를 참조해서 생년월일 수식을 거치지 않고 바로 나이를 계산할 수 있겠습니다.
태어난 년도 – 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()
위와 같이 별도의 값을 가져오는 수식들을 DATEDIF함수의 인수로 작성해 줌으로써 나이를 계산하는 수식을 작성할 수 있습니다.
글을 마치며…[행아아빠]
예제 서식에서 주민등록번호로 나이를 계산하는 수식은 결국 F열의 수식 하나로 해결이 될 수 있습니다.
하지만 수식이 길어지고, 함수의 인수가 결과값을 계산하는 수식으로 작성하는 경우 수식의 시작과 종료의 괄호에 주의해서 작성하도록 하고, 한번에 수식을 작성하기 어렵다면 이번 글에서와 같이 하나의 수식을 인수로 사용하고자 할때 각각의 수식을 작성한 후에 그 수식을 작성해야할 인수에 넣어주면 어렵지 않게 수식을 작성할 수 있습니다.
