Excel) 법정동코드 조회 및 pnu생성(오프라인 버전)

이전글의 오프라인 버전입니다.

오프라인 버전은 실행속도가 빠릅니다. 실무적으로 사용빈도가 높다면 오프라인 버전을 추천합니다.

그러나 법정동코드가 행정구역 통폐합 등으로 변경될 수 있으므로 연 1~2회 업데이트가 필요하며, 업데이트 방법은 엑셀 내에 포함된 "법정동코드 전체자료" 시트의 내용을 code.go.kr(행정표준코드관리시스템)에서 다운로드한 후, 그대로 덮어씌우면 됩니다

사용시 유의사항

1. 엑셀 시트명 Sheet1의 시트명을 바꾸면 작동하지 않습니다. (다른 시트, 다른 파일에서 매크로 실행시 에러방지 위해 vba코드에서 시트이름 고정했으며 이름변경을 하려면 코드내 이름도 변경을 해야 합니다.)
2. Shee1의 기본폼을 변경 하지마세요.(열 삽입 등)
3. "법정동코드 전체자료" 시트의 이름 및 내부 구조변경 하지세요.(시트이름이나 내부 행열 변경시 실행 안됨)
4. "법정동코드 전체자료" 업데이트시 복사붙여넣기(열의 길이가 늘어나는것은 상관없으나, 행이 바뀌면 안됨(참조함수 활용))
5. 코드는 엑셀 2010에서 작성되었고, 해당 버전에서 정상적으로 작동합니다. (일반적으로 엑셀의 상위 버전에서도 호환성이 유지될 것입니다.)
6. 코드를 수정하고 출처를 표시하여 배포하는 것은 자유롭게 가능합니다. 그러나 상업적인 목적으로 이용하는 것은 허용되지 않습니다.

다운로드

코드

Option Explicit

Private Sub generatePnu()

    ' 시트 설정
    Const targetSheetName As String = "Sheet1"
    Dim targetSheet As Worksheet
    Set targetSheet = Worksheets(targetSheetName)
    
    ' 활성 시트가 원하는 시트인지 확인하고, 그렇지 않다면 서브를 종료
    If ActiveSheet.Name <> targetSheetName Then
        MsgBox "This code can only be executed on the " & targetSheetName & " sheet.", vbExclamation
        Exit Sub
    End If

    Const MAX_ROWS As Long = 2000 ' 처리할 최대 행 수
    Const START_ROW As Long = 5   ' 데이터 출력 시작 행
    
    ' 셀 값을 지우고 셀 형식 변경
    targetSheet.Range("C" & START_ROW & ":E" & START_ROW + MAX_ROWS).ClearContents
    targetSheet.Range("C" & START_ROW & ":E" & START_ROW + MAX_ROWS).NumberFormat = "@"
    
    ' "법정동코드 전체자료"시트에 대한 설정
    Dim wsSource As Worksheet
    Set wsSource = ThisWorkbook.Sheets("법정동코드 전체자료")
    
    ' 데이터 범위를 지정 (B2부터 열 B의 마지막 셀까지)
    Dim ldCodeNmSource As Range
    Set ldCodeNmSource = wsSource.Range("B2:B" & wsSource.Cells(Rows.Count, "B").End(xlUp).Row)
    
    ' 열 A에서 ldCodeNmData의 범위를 설정 (A5부터 시작)
    Dim ldCodeNmData As Range
    Set ldCodeNmData = targetSheet.Range("A" & START_ROW & ":A" & Cells(Rows.Count, "A").End(xlUp).Row)
    
    If Not CheckValues(ldCodeNmData) Then
        MsgBox "법정동 입력셀에 입력값이 없거나 범위내 빈셀이 있습니다."
        Exit Sub
    End If
    
    ' 필요한 변수설정
    Dim ldCodeNm As Range
    Dim lookupValue As Variant
    Dim result As Variant
    Dim ldCodeNmValue As String
    
    For Each ldCodeNm In ldCodeNmData
        lookupValue = ldCodeNm.Value
        ' VLOOKUP 함수를 사용하여 데이터를 찾기
        result = Application.VLookup(lookupValue, ldCodeNmSource, 1, False)
        
        ' 찾은 데이터의 열 C의 값이 "존재"인 경우 열 A의 값을 가져옴
        If Not IsError(result) Then
            If Application.WorksheetFunction.Index(wsSource.Range("C2:C" & wsSource.Cells.Item(wsSource.Rows.Count, "C").End(xlUp).Row), Application.WorksheetFunction.Match(lookupValue, ldCodeNmSource, 0), 1).Value = "존재" Then
                ldCodeNmValue = Application.WorksheetFunction.Index(wsSource.Range("A2:A" & wsSource.Cells.Item(wsSource.Rows.Count, "A").End(xlUp).Row), Application.WorksheetFunction.Match(lookupValue, ldCodeNmSource, 0), 1).Value
            Else
                ldCodeNmValue = "확인요망"
            End If
        End If
        
        ldCodeNm.Offset(0, 2).Value = ldCodeNmValue
        
    Next ldCodeNm

    ' 변환할 범위 선택 (B5부터 열 B의 마지막 셀까지)
    Dim jibunRng As Range
    Dim jibunLastRow As Long
    jibunLastRow = targetSheet.Cells.Item(targetSheet.Rows.Count, "B").End(xlUp).Row
       
    ' 검색할 지번이 있는 B열의 범위 확인
    If jibunLastRow >= START_ROW Then
        If ldCodeNmData.Rows.Count <> jibunLastRow - START_ROW + 1 Then
            MsgBox "지번은 선택입력 사항이나 입력시에는 검색할 주소의 입력범위와 일치하여야 합니다.(Either all or none)", vbExclamation
            Exit Sub
        Else
            Set jibunRng = targetSheet.Range("B" & START_ROW & ":B" & jibunLastRow)

            ' 텍스트에서 지번 코드 생성
            Dim jibun As Range
            Dim text As String
            Dim convertedCode As String
            For Each jibun In jibunRng
                text = jibun.Value
                convertedCode = ConvertFormat(text)
                jibun.Offset(0, 2).Value = convertedCode
            Next jibun
            
        End If
    End If
        
    
    ' 결합할 범위 선택 (C5부터 열 C의 마지막 셀까지)
    Dim rng As Range
    Set rng = targetSheet.Range("C" & START_ROW & ":C" & Cells(Rows.Count, "C").End(xlUp).Row)
    
    Dim cell As Range
    Dim pnu As String
    For Each cell In rng
        ' 열 C와 D의 값을 결합
        pnu = cell.Value & cell.Offset(0, 1).Value
        
        ' 결합된 텍스트를 열 E에 출력
        With cell.Offset(0, 2)
            .NumberFormat = "@"
            .Value = pnu
        End With
        
    Next cell
End Sub

Private Function CheckValues(ByRef rng As Range) As Boolean
    Dim cell As Range
    
    For Each cell In rng
        ' 빈 셀인지 확인
        If IsEmpty(cell) Then
            CheckValues = False
            Exit Function
        End If
    Next cell
    CheckValues = True
End Function

Private Function ConvertFormat(ByRef text As String) As String
    Dim convertedCode As String
    Dim mnnmSlnoPart1 As String
    Dim mnnmSlnoPart2 As String
    Dim regstrSeCode As String
    
    text = Replace(text, " ", "")                    ' 공백 제거
    text = Application.WorksheetFunction.Clean(text) ' 출력 불가능한 문자 제거
    
    If Left$(text, 1) = "산" Then
        regstrSeCode = "2"
        If InStr(text, "-") > 0 Then
            mnnmSlnoPart1 = Mid$(text, 2, InStr(text, "-") + 1)
            mnnmSlnoPart2 = Mid$(text, InStr(text, "-") + 1)
        Else
            mnnmSlnoPart1 = Mid$(text, 2)
            mnnmSlnoPart2 = "0"
        End If
    Else
        regstrSeCode = "1"
        If InStr(text, "-") > 0 Then
            mnnmSlnoPart1 = Left$(text, InStr(text, "-") - 1)
            mnnmSlnoPart2 = Mid$(text, InStr(text, "-") + 1)
        Else
            mnnmSlnoPart1 = text
            mnnmSlnoPart2 = "0"
        End If
    End If
    
    convertedCode = regstrSeCode & Format$(Val(mnnmSlnoPart1), "0000") & Format$(Val(mnnmSlnoPart2), "0000")
    ConvertFormat = convertedCode
End Function