법정동을 선택하여 PNU 완성하기(3) - Filter + Sort

이번에는 최신 함수인 Filter와 Sort 함수를 이용해 읍면동을 입력한 후 입력한 읍면동을 포함하는 법정동을 추출한 후 유효성 검사를 이용해 법정동을 선택한 후 그것에 맞는 법정동코드를 추출하고, 지번에서 특지 구분과 본번 부번을 추출한 후 결합해서 최종적인 PNU를 만드는 것을 해보겠습니다.

 

Index와 Aggregate함수를 이용하는 것은 아래 글을 참고 바랍니다.

https://lsw3210.tistory.com/567

https://lsw3210.tistory.com/568

 

Microsoft 도움말 사이트를 살펴보면 Filter와 Sort 함수 모두 Excel 2021 이상에서 사용 가능한 것으로 되어 있습니다.

https://support.microsoft.com/ko-kr/office/filter-%ED%95%A8%EC%88%98-f4f7cb66-82eb-4767-8f7c-4877ad80c759

 

아래 설명은 Microsoft 365 버전을 기준으로 한 것이니 이전 버전이라면 다를 수 있습니다.

 

1. Filter 함수 적용

법정동 선택 후 PNU만들기(filter_sort).xlsx

1.24MB

 

아래와 같이 A2셀에 읍면동, B2셀에 지번을 입력합니다.

 

그러면 C열에 Filter 함수를 이용해 입력한 동, 여기서는 남산동을 포함하는 법정동을 표시할 수 있습니다.

수식은 =FILTER(법정동코드!B2:B49860,ISNUMBER(FIND(A2,법정동코드!B2:B49860))*(법정동코드!C2:C49860="존재"))로서

 

법정동코드!B2:B49860는 필터를 적용할 법정동명의 전체 범위이고(복사해서 다른 곳에 붙여 넣을 것이 아니므로 절대참조 형식으로 지정하지 않음),

 

ISNUMBER(FIND(A2,법정동코드!B2:B49860))*(법정동코드!C2:C49860="존재")는 필터 조건으로서

법정동명이 입력된 법정동코드!B2:B49860에 A2셀의 값이 포함되고,

폐지여부가 입력된 법정동코드!C2:C49860에서 값이 "존재"인 두 가지 조건을 만족해야 한다는 것입니다.

 

이 결과는 아래와 같이 법정동명코드순이어서 가나다순으로 정렬되어 있지 않습니다.

 

2. Sort 함수 적용

따라서, Filter 함수 앞에 Sort 함수를 추가하여 가나다순으로 정렬합니다.

=SORT(FILTER(법정동코드!B2:B49860,ISNUMBER(FIND(A2,법정동코드!B2:B49860))*(법정동코드!C2:C49860="존재")))

 

 

3. 데이터 유효성 검사 만들기

 

가. 데이터 유효성 검사 

D2셀을 선택한 후

데이터 탭의 데이터 도구 그룹에서 '데이터 유효성 검사'의 윗부분을 누릅니다.

 

그리고, 제한 대상으로 목록을 선택하고,

원본에 =c2:c30이라고 입력한 다음 확인 버튼을 누릅니다.

 

 

그리고, D2셀 오른쪽의 콤보 상자 버튼을 누르면 빈 셀이 있어서 맨 아래에 빈 셀이 하나 보입니다.

 

나. 원본의 범위를 데이터 있는 범위로 수정하기

C2셀에서 C30셀에서 데이터가 있는 셀의 개수는 CountA함수를 사용해서 구할 수 있으며,

이것을 문자열로 결합한 후 범위를 반환하기 위해서는 Indirect 함수를 이용해야 합니다.

 

따라서, 데이터 유효성 검사의 원본에

=indirect("c2:c"&counta(c1:c30))로 수정하고 확인 버튼을 누릅니다.

 

 

그리고, D2셀 오른쪽의 콤보 상자 버튼을 누르면 맨 아래 빈 셀이 없어졌습니다.

 

수식 ="c2:c"&counta(c1:c30)의 결과는 c2:c11이라고 원하는 범위의 셀 주소를 지칭하지만 문자열이기 때문에, 이 문자열을 참조(범위)로 변경하기 위해 Indirect 함수가 필요한 것입니다.

 

3. 법정동코드 구하기

법정동코드는 Match 함수를 이용해 D2셀의 값을 찾은 후

그 행에 해당하는 법정동코드를 Index함수를 이용해 구하면 됩니다.

 

따라서, 아래와 같은 수식이 됩니다.

=INDEX(법정동코드!A1:A49860,MATCH(D2,법정동코드!B1:B49860,0))

 

위 수식에서 index의 첫 번째 인수가 A1이라고 1행부터 시작하면 Match함수의 두 번째 인수도 1부터 시작하고,

index의 범위가 2행부터 시작하면 Match 함수의 범위도 2로 맞춰야 합니다.

 

4. 특지 구분 구하기

특지는 지번에서 왼쪽 글자가 '산'이면 2이고, 아니면 1이므로

=if(left(b2,1)="산",2,1)

 

 

5. 본번을 네 자리 숫자로 만들기

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

Substitute(b2,"산","")을 여러 번 사용해서 아래와 같이 복잡했는데,

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

 

다시 생각해 보니 아래와 같이 간단하게 할 수 있습니다.

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

 

위 수식은 먼저 하이픈이 있는지 여부를 판단한 후 하이픈이 있다면 그 위치-1까지 왼쪽 문자를 가져와서 "산"을 ""으로 대체하고, 아니라면 B2셀에서 "산"을 ""로 대체하는 것입니다.

 

그리고, 4자리 숫자로 만들기 위해

앞에 "000"을 결합한 후 오른쪽에서 숫자 4개를 가져오면 됩니다.

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

 

 

6. 부번을 네 자리 숫자로 만들기

부번은 법정동을 선택하여 PNU 완성하기(2) - Index + Aggregate에서 사용한 수식과 같습니다.

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

 

수식은 하이픈이 있다면 하이픈의 위치+1부터 숫자 4개를 가져오고(공백은 제거됨), 하이픈이 없다면 부번이 없는 것입니다.

 

그리고, 부번이 없는 경우도 있으므로 네 자리 숫자로 만들기 위해 위에서 구한 값과 "0000"과 결합한 후 오른쪽 4개 숫자를 가져오는 것입니다.

 

7. PNU 완성하기

=concat(e2:h2)로 법정동 코드부터 부번까지 결합하면 됩니다.

 

8. 검증

혹시 잘못된 부분은 없는지 '산'도 지웠다 썼다 하고, 하이픈도 없앴다 추가했다 해보기 바랍니다.

 

'산'을 지울 때는 '(작은따옴표)를 추가해야지 안 하면 날짜 06월 23일로 변경되니 주의해야 합니다.

 

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

1.25MB