필지고유번호(PNU)에 해당하는 주소 구하기

법정동 선택 후 PNU만들기(완성)3.xlsm

1.25MB

 
위 파일은 법정동과 지번을 결합해서 필지고유번호(PNU)를 구하는 매크로 파일인데,

 
여기서 구한 PNU를 이용해 역으로 법정동과 지번을 구해보겠습니다.
 

1. PNU의 구성

PNU는 법정동에 해당하는 코드 10자리와 지번을 특지 1자리와 본번, 부번 각 4자리를 결합해서 만들어진 19자리 숫자입니다.
 

2. 문제 푸는 방법

PNU중 앞 10자리에 해당하는 법정동을 구하고,
 
11번째 자릿수로 특지, 다시 말해 1이면 일반이므로 빈칸으로 만들고, 2이면 '산'이 되며
 
12번째부터 4자리 본번은 4자리 숫자 중 앞의 0을 없애고,

16번째이후 4자리 부번도 4자리 숫자 중 앞의 0을 없애는데,
부번이 0이면 본번만 남기고,
부번이 0보다 크면 -으로 연결해 "본번-부번"으로 만들면 됩니다.
 

3. 풀이

가. 법정동코드에 해당하는 법정동명 구하기

법정동코드 시트를 보면
A열이 법정동코드,  B열이 법정동명이므로
VLookup 함수를 이용하면 간단히 해당 법정동명을 구할 수 있습니다.

 
검증도 할 겸 PNU 시트를 클릭한 후 C11셀에 =i2라고 수식을 입력해서 법정동코드를 넣겠습니다.

 
그리고, D11셀에 VLookup 함수를 이용한 수식을 입력하는데
 
첫 번째 인수로 PNU가 들어 있는 C11셀을 지정하는데, 10자리 수만 가져와야 하므로 left(c11,10)이라고 수식을 수정합니다.
=VLOOKUP(LEFT(C11,10),

 
두 번째 인수인 찾을 범위로 법정동코드 시트의 A열과 B열 전체를 지정해도 되고(법정동코드!A:B),
PC가 느리다면 A2셀을 누르고, Shift + Ctrl + End키를 눌러 C49860셀까지로 지정하고, F4키를 눌러 절대 참조형식으로 바꿉니다.
=VLOOKUP(LEFT(C11,10),법정동코드!$A$2:$C$49860

 
세 번째 인수로 순번에 해당하는 2를 입력하고, 정확한 값을 찾아야 하므로 False 또는 0을 입력합니다.

 
이제 엔터 키를 누르면 법정동명이 표시돼야 하는데, #N/A라고 표시됩니다.

 
 
에러가 나는 이유는 찾는 값과 찾을 범위의 값이 다르기 때문입니다.
찾는 값과 찾을 범위의 값이 어떻게 다른지 확인하기 위해 LEFT(C11,10)를 마우스로 끌어서 선택한 후 F9키를 누르면 "4159013000"라고 큰따옴표안에 법정동코드가 표시되고,

Microsoft 365라서 그런지 F9키를 누르지 않아도 값이 구해집니다.

 
이번에는 법정동코드!$A$2:$C$49860를 선택하고 F9키를 누르면

Microsoft 365라서 그런지 F9키를 누르지 않아도 값이 구해집니다.

 
법정동코드가 큰따옴표가 없이 숫자로 표시됩니다.

 
그렇다면 Left(C11,10)의 결괏값이 문자열이 아닌 숫자가 되도록
수식 앞에 Value를 추가하면 됩니다.
 
Esc키를 눌러 계산 결과를 없앤 다음
VALUE(LEFT(C11,10))라고 수식을 수정하고 엔터키를 누르면
=VLOOKUP(VALUE(LEFT(C11,10)),법정동코드!$A$2:$C$49860,2,0)

 
'경기도 화성시 청계동'이 구해졌습니다.
 

 

나. 특지구분 구하기

11번째 자릿수가 2이면 '산', 1이면 ""으로 구분하면 됩니다.
 
그런데, MID 함수로 구한 값이 문자열이 되므로 2가 아니라 "2"로 입력해야 하므로 
수식은
=if(mid(c11,11,1)="2","산","") 
이 됩니다.
 
그런데 법정동과 특지 사이에 공백 한 칸이 있어야 하고, 법정동명과 특지구분을 연결하기 위해 결합 연산자 &를 사용합니다.
 
따라서, 지금까지의  전체 수식은
=VLOOKUP(VALUE(LEFT(C11,10)),법정동코드!$A$2:$C$49860,2,0) & " " & IF(MID(C11,11,1)="2","산","")
가 됩니다.
 

다. 본번 구하기

본번은 12번째부터 4자리 수를 숫자로 바꾸기 위해 Value함수를 연결하면 되므로
=value(mid(c11,12,4))로 구할 수 있습니다.
 
따라서, 지금까지의 전체 수식은
=VLOOKUP(VALUE(LEFT(C11,10)),법정동코드!$A$2:$C$49860,2,0) & " " & IF(MID(C11,11,1)="2","산","") & VALUE(MID(C11,12,4))
이 됩니다.
 

라. 부번 구하기

부번은 16번째부터 4자리 수를 숫자로 바꾸면 되므로
수식은
=value(mid(c11,16,4))
가 됩니다.
 
그런데 부번이 0보다 큰 경우에만 -과 부번을 연결해야 하므로
=if(value(mid(c11,16,4))>0,"-"& value(mid(c11,16,4)),"")
이 됩니다.
 
그러므로 전체 수식은
=VLOOKUP(VALUE(LEFT(C11,10)),법정동코드!$A$2:$C$49860,2,0) & " " & IF(MID(C11,11,1)="2","산","") & VALUE(MID(C11,12,4)) & IF(VALUE(MID(C11,16,4))>0,"-"& VALUE(MID(C11,16,4)),"")
이 됩니다.
 
이런 식으로 하나씩 해결해 나가면 됩니다.
 
PNU에 해당하는 '경기도 화성시 청계동 산6'이 잘 구해졌습니다.

 

마. 검증하기

D2셀을 클릭한 후 동을 '경상남도 창원시 마산합포구 청계동'으로 바꾸겠습니다.

 
그런데 법정동 목록 맨 아래에 '4159013000200060000'이 표시되는 것은
데이터 유효성검사의 목록 범위가 C2셀에서 C30셀까지로 지정되어 있어서 그런 것입니다.

 
그러면 '경상남도  창원시 마산합포구 청계동 산6'에 해당하는 PNU '4812515400200060000'가 구해지고,

 
PNU에 해당하는 주소 '경상남도  창원시 마산합포구 청계동 산6'이 D11셀에 구해졌습니다.
 
이런 식으로 수식이 맞는지 검증해 보기 바랍니다.

법정동 선택 후 PNU만들기와 해당 주소 거꾸로 구하기(완성).xlsm

1.25MB