법정동을 선택하여 PNU 완성하기(2) - Index + Aggregate

법정동 선택 후 PNU만들기1(완성).xlsx

1.25MB

 

https://lsw3210.tistory.com/567

에서 읍면동명을 포함하는 법정동명을 구했는데,

이번 글에서는 유효성 검사를 이용해 목록을 만드는 것부터 해보겠습니다.

 

나. 데이터 유효성 검사

D1셀을 클릭하고 데이터 탭에서 데이터 도구 그룹의 데이터 유효성 검사 윗부분을 누릅니다.

 

그리고, 제한 대상으로 목록을 선택하고, C2:C30을 마우스로 끌어서 원본을  선택하고 확인 버튼을 누릅니다.

 

그러면 D2셀 오른쪽에 콤보 상자 버튼이 생깁니다.

 

콤보 상자 버튼을 누르면 C열의 법정동명이 표시되므로 원하는 동을 클릭해서 선택하면 됩니다.

 

맨 아래 회색 부분은 공백 때문에 그런 것입니다.

 

다. 법정동코드 구하기

이제 선택된 법정동명에 대한 법정동코드를 구해보겠습니다.

 

C2셀에 사용했던 수식과 비슷한데, index의 array를 법정동코드 시트의 A열로 지정하는 것과 find의 대상이 D2셀이라는 것만 다릅니다.

=IFERROR(INDEX(법정동코드!$A$1:$A$49860,AGGREGATE(15,3,1/(ISNUMBER(FIND($D$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))*ROW(법정동코드!$B$2:$B$49860),ROW()-1)),"")

 

라. 특지 구분 구하기

특지 구분은 '산'이면 2이고, '산'이 아니면 1입니다.

따라서 수식은 =IF(LEFT(B2,1)="산",2,1)이 됩니다.

 

마. 본번 4자리 숫자로 만들기

본번은 '산'이 있다면 두 번째 숫자부터, 아니라면 첫 번째 숫자부터 하이픈이 있기 전까지가 본번입니다.

따라서, '산'이 있는지, 하이픈이 있는지 두 가지 조건을 걸어야 하는데,

Substitute함수를 이용해 B2셀에서 '산'을 빈칸으로 만들어 '산'을 지워버리면 하이픈만 체크하면 됩니다.

두 가지를 모두 해보겠습니다.

 

(1) Substitute 함수를 사용하는 경우

=IF(ISNUMBER(FIND("-",SUBSTITUTE(B2,"산",""))),LEFT(SUBSTITUTE(B2,"산",""),FIND("-",SUBSTITUTE(B2,"산",""))-1),SUBSTITUTE(B2,"산",""))

 

SUBSTITUTE(B2,"산","") 수식을 이용해 "산"을 없앤 다음, 여기서 하이픈을 찾아서 하이픈이 있는데에서 1을 뺀 위치까지가 본번이고, 하이픈이 없다면 "산"을 없앤 숫자가 본번이 됩니다.

 

이제 본번을 4자리 수로 바꿔야 하므로 앞에 "000"을 연결한 다음 오른쪽 4개를 가져오면 네 자릿수 본번이 됩니다.

=RIGHT("000"&IF(ISNUMBER(FIND("-",SUBSTITUTE(B2,"산",""))),LEFT(SUBSTITUTE(B2,"산",""),FIND("-",SUBSTITUTE(B2,"산",""))-1),SUBSTITUTE(B2,"산","")),4)

 

(2) Substitute 함수를 사용하지 않는 경우

아래 수식으로 본번을 구합니다.

=IF(ISNUMBER(FIND("산",B2)),IF(ISNUMBER(FIND("-",B2)),MID(B2,2,FIND("-",B2)-2),MID(B2,2,4)),IF(ISNUMBER(FIND("-",B2)),MID(B2,1,FIND("-",B2)-1),MID(B2,1,4)))

 

녹색으로 된 수식이 "산"이 있을 경우의 값(value_if_true)으로 하이픈이 있으면 두 번째 위치부터 하이픈이 있는 위치에서 2를 뺀 개수가 본번이고, 아니면 두 번째부터 4개의 숫자(길이를 몰라서 본번이 네 자릿수이므로 4라고 함)가 본번입니다.

 

빨간색으로 된 수식은 "산"이 없을 경우의 값(value_if_false)으로 하이픈이 있으면 첫 번째 위치부터 하이픈이 있는 위치에서 1을 뺀 개수가 본번이고, 아니면 첫 번째부터 4개의 숫자가 본번입니다.

 

검증하기 위해 '산'글자를 지울 때 그대로 지우면 06월 23일이 되고 본번이 4546이 되므로 

 

'산'을 지운 다음 '(작은따옴표)를 붙여서 문자라는 것을 알려줘야 합니다.

'6-23으로 입력하니 본번 6이 제대로 구해졌습니다.

 

그다음 네 자릿수로 바꾸는 것은 Substitute함수를 사용할 때와 같습니다.

 

바. 부번 4 자리 숫자로 만들기

부번은 "산"이 있든 없든 상관없이, 하이픈이 있으면 하이픈이 있는 위치+1부터 4개를 가져오는 것이 본번이고, 하이픈이 없다면 ""이 됩니다.

=RIGHT("0000"&IF(ISNUMBER(FIND("-",B2)),MID(B2,FIND("-",B2)+1,4),""),4)

 

그리고, 부번이 없을 경우에도 0이 4개 있어야 하므로 "0000"으로 연결해야 합니다.

 

사. PNU 구하기

PNU는 법정동코드, 특지 구분, 본번, 부번을 연결하면 됩니다.

 

따라서, =concat(e2:h2)라고 하면 됩니다.

 

물론 =E2&F2&G2&H2라고 해도 됩니다.

 

아. 검증하기

남산동을 입력하면 C열은 남산동이 포함된 법정동이 표시되는데, D2셀의 값은 바뀌지 않고,

 

D2셀의 콤보 상자 버튼을 눌러야 남산동이 표시된 법정동이 목록으로 표시됩니다.

 

그리고, 아무 거나 클릭하면 해당 법정동에 대한 법정동코드가 구해지고, PNU가 바뀝니다.

 

산6으로 바꿔도 200060000으로 잘 표시됩니다.

여러 가지 형태로 지번을 바꿔서 검증해 보기 바랍니다.

 

법정동 선택 후 PNU만들기2(완성).xlsx

1.25MB