엑셀로 주민등록번호 추출하고 검증하기(배열수식)

앞에서 문자(숫자)를 추출하는 배열함수에 대해서 알아보았습니다.

이번에는 주민등록번호를 추출하고 주민등록번호 뒷자리를 이용하여 오류가 있는지 검증하는 배열수식을 만들어 보겠습니다.

배열수식을 만들기 전에 아래 링크의 내용을 반드시 확인 바랍니다.

1. 문자추출(배열수식) : 클릭

2. 주민등록번호 검증 : 클릭

배열수식 이해하기

☞ 배열수식 이해하기

1. 주민등록번호 추출 : MID 함수와 ROW 함수를 사용하여 주민번호를 순서대로 추출하였습니다.(문자추출 배열함수 보기)

2. 곱할 값 생성

① 곱할 값은 어떤 값에서 추출한것 아니라 ROW 함수를 이용해서 만들 보았습니다.

② MOD 함수 : ROW(1:12) 즉 1~12까지의 수를 9로 나눈 나머지로  배열은 {1 ; 2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 0 ; 1 ; 2 ; 3} 입니다.

③ QUOTIENT 함수 : 1~12까지의 수를 9로 나눈 몫으로 배열은 {0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 0 ; 1 ; 1 ; 1 ; 1} 입니다.

④ MOD + QUOTIENT + 1 : 배열은 {2 ; 3 ; 4 ; 5 ; 6 ; 7 ; 8 ; 9 ; 2 ; 3 ; 4 ; 5} 입니다.(아래표 참조)

3. SUM(주민번호 * 곱할 값) = 170

주민등록번호 검증 배열수식

☞ 배열수식 설명

1. 합계 170을 만들어 내는 방법은 위에서 설명하였습니다.

2. MOD 함수 : 합계를 11로 나눈 나머지를 반환합니다.

3. RIGHT 함수 : (11 - 나머지)의 마지막 자리의 값을 반환합니다. (검증코드가 11 과 10이 나오는 경우에는 1 과 0을 추출하기 위함)

4. IF 함수 : 주민번호의 마지막 숫자와 배열수식에 의한 검증코드가 일치하면 정상, 아니면 오류로 나타 냅니다.

수식비교

위에서 링크한 주민번호검증에서 사용한 수식과 배열수식을 비교한 것입니다.

중첩함수 : =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),"정상","오류")

배열수식 : {=IF(RIGHT(B3,1)=RIGHT(11-MOD(SUM(MID(B3 , ROW(1:12) , 1) * (MOD(ROW(1:12) , 9) + QUOTIENT(ROW(1:12) , 9)+1)) , 11)) , "정상" , "오류")}

중첩함수를 사용하는 것 보다 배열 수식을 사용하는 것이 수식이 더 간단하죠?

그런데 수식길이의 차이가 좀 있기는 하지만 머리 아프게 배열수식을 사용할 필요가 있을까요? 라고 물으신다면... 

주민번호(13자리)가 아닌 다른 엄청 긴 문자를 추출하여야 한다고 가정해보세요. 두 수식의 차이는 아주 크게 나타날 것입니다.

사용자마다 활용의 범위가 다르다 보니 서로 잘 알고 있는 주민번호를 가지고 배열수식을 연습 해보았습니다.

첨부파일 보기

주민번호검증(배열수식).xlsx