엑셀에서 주민번호로 만나이계산, 생년월일 추출 정리

원본자료는 아래 링크를 방문하시면됩니다.

쓸것만 따로 정리한거라..총내용은 아래링크에 있어용~

http://naver.me/G3JzaXNg

[엑셀 TIP #117] 엑셀 나이 계산 방법 총정리!

 

 

<DATEDIF 함수>
수식: =DATEDIF(날짜1, 날짜2, "Y")
기능: 세 번째 인수를 위와 같이 Y로 설정한 경우, 두 날짜의 년 단위의 간격을 구합니다. 이 수식을 이용하여 만 나이를 구할 수 있습니다. 단, 날짜1이 날짜2보다 나중 날짜이면 오류가 발생합니다.

 

 

 

주민등록번호를 날짜로 바꿔야 하는데, 어떻게 하면 좋을까요? 주민등록번호에서 연도, 월, 일에 해당하는 부분의 숫자를 추출하는 것이 핵심 아이디어입니다. 이를 위해 LEFT, MID 함수에 대해 알아봅시다.

<LEFT 함수>
수식: =LEFT(텍스트, N)
기능: 텍스트의 왼쪽에서 N개의 문자(숫자, 알파벳, 한글 등)를 반환합니다.

<MID 함수>
수식: =MID(텍스트, A, B)
기능: 텍스트의 왼쪽에서 A번째 문자부터 B개의 문자(숫자, 알파벳, 한글 등)를 반환합니다.

위와 같이 수식을 작성하면, DATE 함수에 입력되는 연도, 월, 일은 다음과 같습니다.

수식: =DATE(LEFT(B3, 2), MID(B3, 3, 2), MID(B3, 5, 2))
연도: LEFT(B3, 2), 즉 주민등록번호에서 연도에 해당하는 맨 앞 2자리(98)를 나타냅니다.
월: MID(B3, 3, 2), 즉 주민번호의 3번째 숫자부터 2자리(12)를 나타냅니다.
일: MID(B3, 5, 2), 즉 주민번호의 5번째 숫자부터 2자리(31)를 나타냅니다.

엑셀에서는 연도를 '98'과 같이 입력하면 자동으로 1900을 더해서 1998로 인식하므로 위와 같이 잘 되는 것 같은데, 문제는 00년생과 01년생이 각각 1900년생, 1901년생이 된다는 것입니다.

해결 방법은 주민번호의 맨 앞 2자리가 00~30이면 2000을 더하고, 31~99이면 1900을 더해서 1931년~2030년생을 인식할 수 있게 하는 것입니다. 이를 위해서는 IF 함수를 사용해야 합니다.

<IF 함수>
수식: =IF(조건식, A, B)
기능: 조건식이 참이면 A, 거짓이면 B를 반환합니다.

따라서 연도에 해당하는 LEFT(B3, 2)를 위와 같이 IF(LEFT(B3, 2)>"30", LEFT(B3, 2)+1900, LEFT(B3, 2)+2000)으로 바꾸면 됩니다.
이 수식을 해석하면, LEFT(B3, 2)>"30"이면, LEFT(B3, 2), 즉 연도 부분이 "30"보다 크면, 즉 31~99이면 LEFT(B3, 2)+1900, 즉 1900을 더하고, 그렇지 않으면 LEFT(B3, 2)+2000, 즉 2000을 더합니다.

위와 같이 2000년과 2001년으로 정상적으로 표시된 것을 볼 수 있습니다.

마지막으로 만 나이와 세는 나이 부분을 채워 주면 나이 계산이 끝납니다.