주민등록 번호는 맨 마지막 자리의숫자로 검사를 합니다. 주민번호의 체계가 맞는가만 체크하지 실명과의 관계는 체크하지 않습니다. 주민번호의 각자리의 숫자에다 차례대로 2,3,4,5,6,7,8,9,2,3,4,5 를 곱한다음 그 값을 모두 더합니다. 추출된 값에다 11을 나눠서 나머지만 추출합니다. 11 에서 추출된값을 빼줍니다.
위에서 추출된 값중 마지막 자리의 숫자만 취합니다. 이값으로 주민번호의 맨 마지막 자릿수의 값과 비교하여 같으면 OK 를 반환합니다.
다음 수식으로 해 보세요.
일단 주민번호를 Substitute 함수를 써서 하이픈문자를 없애고, 또 Text 함수를 써서 13자리의 문자열로 만듭니다(2000년 이후의 출생자 때문에)
TEXT(SUBSTITUTE(A1,"-",""),"0000000000000")
COLUMN($A:$L) 는 1,2,3,4,5,6,7,8,9,10,11,12 를 반환합니다. 수식을 카피하더라도 같은 값을 가지도록 절대주소를 쓴 것입니다.
Mid 함수와 위의 수식을 써서 각 자릿수의 값을 추출합니다.
MID(TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),COLUMN($A:$L),1)
배열함수인 Sumproduct 함수를 써서 각 자리의 숫자에다 위 기본숫자를 곱합니다.
SUMPRODUCT(MID(TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),COLUMN($A:$L),1)*{2,3,4,5,6,7,8,9,2,3,4,5})
위수식에서 추출되는 값을 Mod 함수를 써서 나머지만 취합니다.
MOD(SUMPRODUCT(MID(TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),COLUMN($A:$L),1)*{2,3,4,5,6,7,8,9,2,3,4,5}),11)
11 에서 위에서 추출된 값을 빼 준후 추출한 값에서 마지막 한자리숫자만 취하기 위하여 다시 Mod 함수를 써서 값을 추출합니다.
MOD(11-MOD(SUMPRODUCT(MID(TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),COLUMN($A:$L),1)*{2,3,4,5,6,7,8,9,2,3,4,5}),11) ,10)
추출된 값이 주민번호의 마지막 숫자와 일치하면 OK 를 아니면 Err 을 반환합니다. VALUE(RIGHT(A1,1)) 는 마지막 숫자를 추출한다음 숫자값으로 변환시키는 것입니다.
아래는 완성된 수식입니다. (주의 : 아래 수식은 생일의 날짜가 맞고 틀리고는 체크하지 않습니다.)
=IF(VALUE(RIGHT(A1,1))=MOD(11-MOD(SUMPRODUCT(MID(TEXT(SUBSTITUTE(A1,"-",""),"0000000000000"),COLUMN($A:$L),1)*{2,3,4,5,6,7,8,9,2,3,4,5}),11),10),"OK","Err")