주소를 PNU로 변환하기(1) - VBA
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행까지 구해줍니다.
