주민등록번호로 만 나이 연령대 수 구하기

	B	C	D	E	F	G	H	I
2	주민등록번호	나이		2007-07-09	현재 나이
3	400506-150629	67		15 미만	1		0	14
4	980421-251230	9		15~19	0		15	19
5	771213-121098	29		20~24	0		20	24
6	530914-223909	53		25~29	3		25	29
7	300228-212984	77		30~34	0		30	34
8	800710-112492	26		35~39	0		35	39
9	820228-238652	25		40~44	0		40	44
10				45~49	0		45	49
11	생년	40		50~54	1		50	54
12	생년 (as 19**)	1940		55~59	0		55	59
13	생월*100+생일	506		60~64	0		60	64
14	현재년	2007		65~69	1		65	69
15	현월*100+현일	709		70~74	0		70	74
16	만 나이	67		75 이상	1		75
17				합계	7

 

B3:B9에 7명의 주민번호가 있다고 할 때 (몇 명이라도 상관 없습니다) 표E2:F16 작성 순서는, (위 표를 긁어 엑셀에 붙여 넣고 작업하면 이해하기 쉽습니다)

 

1. C3 =(YEAR(NOW())-(2000-N(RIGHT(YEAR(NOW()),2)<LEFT(B3,2))*100+LEFT(B3,2)))-N(MONTH(NOW())*100+DAY(NOW())<MID(B3,3,2)*100+MID(B3,5,2))

    ; C3을 복사, 아래 데이타 만큼 붙여 넣기

2. H3:I5에 0, 14, 15, 19, 20, 24 여섯 개 숫자 입력, H4:I5 잡아 아래로 75가 나올 때 까지 복사

3. E4 =H4&"~"&I4           ; E4를 아래로 E15까지 복사

3. F3 =SUMPRODUCT(N(C$3:C$9>=H3),N(C$3:C$9<=I3))

    ; F3을 F4:F15에 복사

4. F16 =SUMPRODUCT(N(C$3:C$9>=H16))

 

간단한 설명 : 주민번호로 작성일 현재 나이 계산. (생일이 지났는지 판별, 만 나이를 구함)

SUMPRODUCT로 다중조건을 만족하는 데이타 갯수 구하여 출력물 완성함. 수식 입력 때는 위 수식을 복사해서 수식 창에 붙여 넣고 이 예시의 범위 C$3:C$9를 실전 범위로 바꿔 넣어주기만 하면 됩니다.

 

주민번호로 만 나이 계산 수식 (실전과는 관계없지만 혹여 프로그래밍에 흥미가 있다면 B11:B16을 따라가 보세요)

C11 =LEFT(B3,2)

C12 =2000-N(RIGHT(YEAR(NOW()),2)<C11)*100+C11

C13 =MID(B3,3,2)*100+MID(B3,5,2)

C14 =YEAR(NOW())

C15 =MONTH(NOW())*100+DAY(NOW())

C16 =(C14-C12)-N(C15<C13)     이 C16을 위의 C11:C15 수식을 다 넣어 짜깁으면 변수는 B3만 남아 한 셀로 표현 가능합니다. 그 결과물이 아래와 같고 C3에 그대로 갖다 붙인 것이지요.

C16 =(YEAR(NOW())-(2000-N(RIGHT(YEAR(NOW()),2)<LEFT(B3,2))*100+LEFT(B3,2)))-N(MONTH(NOW())*100+DAY(NOW())<MID(B3,3,2)*100+MID(B3,5,2))

 

(2008.3.18 추가. 엑셀2003 이상이라면 C16 =DATEDIF(TEXT(LEFT(B3,6),"00-00-00"),TODAY(),"y") 로 훨씬 간단히 만나이 계산이 가능함)

 

(2008.3.27 추가. 위 수식은 00~29로 시작하는 번호는 2000년대로 간주됨. 따라서,

=DATEDIF(20-N(LEFT(B3,2)>LEFT(YEAR(NOW()),2))&TEXT(LEFT(B3,6),"00-00-00"),TODAY(),"y") 로 해야 완벽히 잡아 냄.)

 

(2010.8.24 추가. 위 수식의 에러가 있어 바로 잡음)

=DATEDIF(LEFT(YEAR(NOW()),2)-N(LEFT(B3,2)>RIGHT(YEAR(NOW()),2))&TEXT(LEFT(B3,6),"00-00-00"),TODAY(),"y") 로써 현재가 몇 년이든 100살 까지 모두 잡아 냄.