[엑셀] 주민등록번호 오류검증 (뒷자리의 의미)

주민등록번호에도 일종의 생성 규칙이 있다는건 잘 아실겁니다.

생년월일과 성별(남녀) 구분 그리고 태어난 지역 정도...??? 

그런데 마지막 숫자가 무슨 기능인지 아시나요?

제목과 같이 오류를 검증하는 숫자입니다.

마지막 숫자는 주민등록번호 13자리 중 12자리의 값에 의해 결정되는 숫자이며 그 규칙은 아래와 같습니다.

오류검증코드 확인방법

주민번호 : ① ② ③ ④ ⑤ ⑥ - ⑦ ⑧ ⑨ ⑩ ⑪ ⑫ ⑬

합계 = 마지막수를 제외한 12자리의 숫자에 2, 3 , 4 , 5, 6, 7, 8, 9, 2, 3, 4, 5 를 순서대로 곱한수의 합

       = ①×2 + ②×3 + ③×4 + ④×5 + ⑤×6 + ⑥×7 + ⑦×8 + ⑧×9 + ⑨×2 + ⑩×3 + ⑪×4 + ⑫×5

나머지 = 합계를 11로 나눈 나머지

검증코드 = 11 - 나머지

여기서 검증코드가 ⑬자리에 들어 갑니다.

엑셀 함수로 오류검증코드 만들기

주민등록번호 입력 셀 : E20 

수식 = IF(RIGHT(E20,1)=RIGHT(11-MOD(SUM(MID(E20,1,1)*2 , MID(E20,2,1)*3 , MID(E20,3,1)*4 , MID(E20,4,1)*5 , MID(E20,5,1)*6 , MID(E20,6,1)*7 , MID(E20,8,1)*8 , MID(E20,9,1)*9 , MID(E20,10,1)*2 , MID(E20,11,1)*3 , MID(E20,12,1)*4 , MID(E20,13,1)*5),11),1),"정상","오류")

수식 해석

합계 = SUM(MID(E20,1,1)*2 , MID(E20,2,1)*3 , MID(E20,3,1)*4 , MID(E20,4,1)*5 , MID(E20,5,1)*6 , MID(E20,6,1)*7 , MID(E20,8,1)*8 , MID(E20,9,1)*9 , MID(E20,10,1)*2 , MID(E20,11,1)*3 , MID(E20,12,1)*4 , MID(E20,13,1)*5)

나머지 = MOD(합계, 11)

검증코드 = RIGHT(11 - 나머지,1)

주민등록번호 마지막 숫자 = RIGHT(E20,1) 

검증 = IF(마지막 숫자 = 검증코드, "정상","오류")

나머지가 0과 1인 경우 검증코드가 11과 10이 되는데 이때는 마지막 숫자 1과 0을 검증코드로 사용합니다.(욱자님의 소중한 의견으로 수정합니다. ^^)

배열수식으로 검증수식 만들기

※ 첨부한 엑셀파일을 참조하세요

엑셀에서 활용

주민등록번호를 입력하다가 잘못 입력(오타 등)하는 경우 오류를 확인 할 때 사용하면 되겠네요.

관련글 보기

01 주민등록번호 활용하기

카메라 기능(인사카드 만들기)

색연필 막대 그래프 그리기

첨부파일 참조

주민번호 오류검증.xlsx