주소를 PNU로 변환하기(1) - VBA

PNU만들기(주소분리)(특지구분,본번,부번).xlsm

0.02MB

 

 

 

1. 법정동 코드 가져오기

 

PNU 코드는 법정동 10자리 + 특지 구분 1자리 + 본번 4자리 + 부번 4자리 = 19자리로 되어 있습니다.

 

법정동 코드는 https://www.code.go.kr/ 사이트에서

코드 검색을 누르고, 코드명에 '법정동'이라고 입력하고 검색을 하면

텍스트 파일로 받을 수 있으면 이것을 엑셀 파일로 바꾸면 됩니다.

자세한 것은 https://lsw3210.tistory.com/517 을 참고 바랍니다.

 

텍스트 파일을 연 후 Ctrl + A키를 눌러 전체를 선택한 후 복사해서 시트2에 붙여 넣고 시트명을 '법정동코드'로 바꾸면 아래와 같습니다.

 

 

2. 법정동에 대한 법정동 코드 검색하기

 

매크로로 만들기 위해 

개발도구 - Visual Basic을 누른 후

코드 창에 Sub pnu라고 입력하고 엔터 키를 누릅니다.

 

index와 match 함수를 이용해 법정동(B열)에 대한 법정동 코드를 맨 아래줄까지 구합니다.

 

코드는 아래와 같습니다.

Sub pnu()
    Dim endRow As Long, i As Long
    Dim sht1 As Worksheet, sht2 As Worksheet
    
    Set sht1 = Sheets(1)
    Set sht2 = Sheets(2)
    
    sht1.Select
    
    endRow = Range("a" & Rows.Count).End(xlUp).Row
    
    For i = 2 To endRow
        Range("f" & i).FormulaArray = "=index(" & sht2.Name & "!" & Range("A:A").Address & _
            ", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"
    Next
    
End Sub

 

 

코드에 대한 설명은 아래와 같습니다.

Dim endRow As Long, i As Long     Dim sht1 As Worksheet, sht2 As Worksheet

 

변수 endRow와 i를 Long 형식으로 선언하고,

변수 sht1과 sht2를 Worksheet 형식으로 선언합니다.

 

Long 형식은 Integer 형식에 비해 다룰 수 있는 수의 크기가 훨씬 큽니다.

Integer	-32,768 ~ 32,767
Long	-2,147,483,648 ~ 2,147,483,647

 

본 예제의 경우는 Integer로 충분히 처리할 수 있지만 안정적으로 Long 형식으로 지정했습니다.

 

Set sht1 = Sheets(1)     Set sht2 = Sheets(2)          sht1.Select

 

sht1변수에 첫 번째 시트를 할당하고, sht2에 두 번째 시트를 할당합니다.

정수나 문자 형식 등의 경우는 Set이 필요 없는데,

Object 형식의 경우는 Set을 사용해 변수를 정의해야 합니다.

 

endRow = Range("a" & Rows.Count).End(xlUp).Row

 

A열의 맨 마지막 행을 결합한 셀 주소에 해당하는 셀(Microsoft 365라면 A1048576)에서

End(xlUp)은 맨 위로 올라가라는 의미입니다. Ctrl + ↑를 누르는 것과 같습니다.

 

그리고, 그 행의 값을 endRow 변수에 할당하는 것입니다.

 

본 예제의 경우는 11이 됩니다.

 

 

For i = 2 To endRow         Range("f" & i).FormulaArray = "=index(" & sht2.Name & "!" & Range("A:A").Address & _             ", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"     Next

 

For ~ Next 반복문에 따라 2에서 endRow까지 i값이 1씩 증가하면서

안의 구문, 여기서는 Range("f" & i).FormulaArray = "=index(" & sht2.Name & "!" & Range("A:A").Address & _
            ", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"을 반복 실행합니다.

 

Range("f" & i).FormulaArray는 F열 i행 셀 다시 말해 Fi셀의 수식을 배열형태로 입력한다는 것입니다.

다시 말해 배열 수식처럼 Ctrl + Shift + Enter 키를 눌러 입력한다는 것입니다.

 

"=index(" & sht2.Name & "!" & Range("A:A").Address & _
            ", match(" & Range("b" & i).Address & "," & sht2.Name & "!" & Range("B:B").Address & ",0))"

수식을 정의하는 구문인데,

=index(" & sht2.Name & "!" & Range("A:A").Address는 =index(법정동코드!$A:$A라는 의미이고,

match 함수의 첫 번째 인수인 Range("b" & i).Address는 $B$i라는 것이고, 첫 번째로 실행하면 $B$2가 됩니다.

 

그리고, 두 번째 인수인 sht2.Name & "!" & Range("B:B").Address는 법정동코드!$B:$B라는 의미입니다.

 

그러면 전체 수식은 첫 번째로 실행하면 i가 2이기 때문에

=index(법정동코드!$A:$A, match($B$2,법정동코드!$B:$B,0))

가 됩니다.

 

다시 말해 B2셀의 값을 법정동코드 시트의 B열에서 찾은 다음, 그 행에 해당하는 법정동코드의 A열 값인 법정동 코드를 구하는 것입니다.

 

첫번째 줄 맨 뒤에 있는 _는 아래 줄과 코드가 연결되어 있다는 의미입니다.

 

F5키를 눌러 위 매크로를 실행하면

아래와 같이 B열에 대한 법정동코드를 11행까지 구해줍니다.

 

PNU만들기(법정동코드등 19자리)(법정동코드).xlsm

1.26MB