[설명]엑셀을 데이터 입력 툴로 사용하시는 분들 중에서 주민등록번호를 셀에
입력할 때, 미성년자일 경우 특별한 서식을 적용하고 싶을 때에는 어떻게 해야 할까요?
한 단계씩 답에 접근해 보도록 하겠습니다.
먼저, 미성년자를 체크하기 위해서는
주민등록번호로 나이를 계산할 수 있어야 합니다.
그렇다면 주민등록번호로 나이를 어떻게 유추할 수 있을까요? 주민등록번호의 앞 2자리가 년도를 표기한다는 사실은 모두
잘 알고 계시는 내용일겁니다. 단, 1800, 1900, 2000 년대인지 구분이 애매하다는 것만 빼고요.
년도를 완성시키기 위해서는 주민등록번호 뒷부분의 첫 번째 자리 숫자가 필요합니다. 첫
번째 자리에는 0, 9, 1, 2, 3, 4 라는 번호가 쓰이는데.. 다음과 같은 의미를 갖습니다.
| 번호 |
의미 |
| 0, 9 |
1800년대 주민등록번호 |
| 1, 2 |
1900년대 주민등록번호 |
| 3, 4 |
2000년대 주민등록번호 |
이렇게 주민등록번호로 탄생한 년도를 정확히 계산할 수 있다면 우리는 나이를 계산하기
위해 다음과 같은 수식을 작성할 수 있습니다
=올해년도 – 탄생년도 + 1
위의 공식(?)을 수식으로 표현하면 다음과 같습니다.
| =YEAR(TODAY())
-1*(CHOOSE(INT(CEILING(MID(A1,8,1),2)/2)+1,18,19,20,0,0,18)&LEFT(A1,2))
+ 1 |
CEILING 함수는 배수로 값을 올림 하는 함수이므로 주민등록번호에서 성별 및 년도
구분자로 쓰이는 주민등록번호 뒷자리의 첫 번째 숫자를 2의 배수로 올림 하면 “0,2,2,4,4,…,10” 이런
값을 반환 받게 됩니다.
이 때 다시 2로 나눠주면
“0,1,2,3,4,5” 이렇게 좀 더 짧은 값을 반환 받겠죠. CHOOSE 함수는 1부터 인덱스를 매칭할 수
있으니 +1을 해 준 다음 각각의 년도를 돌려주는 역할을 하도록 했습니다.
[관련 강좌]
배수로 반올림하는 방법
아래와 같이 주민등록번호가 입력되어 있다고 가정하겠습니다.
<1> 미성년자 주민등록번호에 특정 서식을 지정하기 위해 A2:A6 범위를
지정하고 서식 메뉴의 조건부 서식 메뉴를 선택합니다.
<2> 조건부 서식 대화상자가 나타나면 조건 1(1)을 다음과 같이
수식이를 선택하고 수식 입력란에
|
=YEAR(TODAY())-1*(CHOOSE(INT(CEILING(MID(A4,8,1),2)/2)+1,18,19,20,0,0,18)&LEFT(A4,2))+1<19 |
라고 입력합니다. 서식 단추를 클릭하여 조건에 만족하는 셀에 원하는 서식을 지정하고
확인 단추를 클릭합니다.
* 앞서 설명한 나이를 계산하는 수식을
참고하시기 바랍니다.
<3> 다음과 같이 19세 미만인 주민등록번호에만 지정한 서식이 적용되어
표시됩니다.
[참고] CEILING 함수
| 함수명 |
설명 |
| CEILING |
배수로 올림 된 수를 구합니다 |
[형식] CEILING(number,significance)
number: 올림 할 값입니다.
significance: 올림 할 배수입니다. |
[예] 수식: =CEILING(2.5, 1)
해설: 2.5를 1의 배수로 올림 합니다.
결과: 3 |
|
OfficeTutor 365