앞에서 문자(숫자)를 추출하는 배열함수에 대해서 알아보았습니다.
이번에는 주민등록번호를 추출하고 주민등록번호 뒷자리를 이용하여 오류가 있는지 검증하는 배열수식을 만들어 보겠습니다.
배열수식을 만들기 전에 아래 링크의 내용을 반드시 확인 바랍니다.
배열수식 이해하기
☞ 배열수식 이해하기
- 주민등록번호 추출 : MID 함수와 ROW 함수를 사용하여 주민번호를 순서대로 추출하였습니다.(문자추출 배열함수 보기)
- 곱할 값 생성
- 곱할 값은 어떤 값에서 추출한것 아니라 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"} 입니다.(아래표 참조)
- SUM(주민번호 * 곱할 값) = 170
주민등록번호 검증 배열수식
☞ 배열수식 설명
- 합계 170을 만들어 내는 방법은 위에서 설명하였습니다.
- MOD 함수 : 합계를 11로 나눈 나머지를 반환합니다.
- RIGHT 함수 : 나머지의 마지막 자리의 값을 반환합니다. (나머지가 10이 나오는 경우에는 0을 추출하기 위함)
- 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자리)가 아닌 다른 엄청 긴 문자를 추출하여야 한다고 가정해보세요. 두 수식의 차이는 아주 크게 나타날 것입니다.
사용자마다 활용의 범위가 다르다 보니 서로 잘 알고 있는 주민번호를 가지고 배열수식을 연습 해보았습니다.
){kind=link}
댓글 쓰기