법정동을 선택하여 PNU 완성하기(1) - Index + Aggregate

1. 문제

 

① 읍면동명과 지번을 A2셀과 B2셀에 입력하면

 

② 읍면동명을 포함한 법정동명을 검색한 후

③ 유효성 검사 목록에 넣고

④ 법정동명을 선택하면 해당 법정동 코드가 구해지고,

⑤ 지번을 이용해 특지구분과 본번과 부번을 각각 4자리로 만들어

⑥ 이들을 모두 결합해서 19자리 PNU 코드를 완성하려고 합니다.

 

법정동코드 시트는 법정동코드, 법정동명, 폐지여부 3개 열로 구성되어 있습니다.

 

2. 해법

 

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

1.24MB

 

가. A2셀에 입력된 읍면동명을 포함하는 법정동명 구하기

포함이니까 Find 함수와 IsNumber가 필요하고,

포함하는 법정동명을 여러 개 순서대로 구해줘야 하니 Small 또는 Aggregate 함수가 필요하고,

순번에 맞는 법정동명을 구해야 하니 Index 함수,

그리고, 에러 발생 시 공백으로 만들기 위해 IfError 함수가 필요합니다.

 

① IsNumber와 Find 함수

=isnumber(find(a2,법정동코드!B2:B49860))

인데, 법정동코드! B2:B49860은 

법정동코드 시트를 클릭한 후 B2셀을 클릭하고, Ctrl + Shift + ↓키를 누르면 됩니다.

 

그리고, 괄호를 두 번 닫고 엔터 키를 누르면

아래와 같이 파란색 선에 둘러싸여서 False가 계속 보입니다.

이것은 Microsoft 365 버전이라 그렇고 이전 버전이라면 엔터키를 누르면 결괏값이 False로 보이지 않을 것이므로 Ctrl + Shift + ↓키를 눌러야 하고, A2셀과 법정동코드!B2:B49860로 고정하기 위해 F4키를 눌러야 할 겁니다.

 

이후는 Microsoft 365 기준으로 설명하겠습니다.

 

위에는 False만이 보이지만

Ctrl + F(찾기) 키를 누른 후 찾을 내용에 true를 입력하고, '찾는 위치'를 수식에서 값으로 바꾸고 엔터 키를 누르면

6150행이 True이고,

 

다시 엔터키를 누르면 7148행에서 멈춥니다.

 

법정동코드 시트를 선택한 후

Ctrl + G(이동) 키를 누른 후 B6150을 입력하고 엔터키를 누르면 B6150셀의 값이 '경기도 의왕시 청계동'이라고 청계동이 포함되어 있습니다.

 

② 두 가지 조건 모두 만족

이번에는 A2셀과 폐지여부가 존재인 것만 찾아보도록 하겠습니다.

그러면 조건 2개를 *(Shift + 8)로 연결해야 합니다.

 

수식은 

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

입니다.

두 번째 수식은 괄호로 감싸야하며, *로 두 개 수식을 연결했습니다.

*를 해서 값이 False에서 0으로 바뀌었습니다. False는 0, True는 1이기 때문에 True*True인 경우만 1이 반환됩니다,

 

 

③ 조건을 만족하는 행을 순서대로 구하기

청계동이 여러 개이기 때문에 행 수를 구한 후 작은 행부터 순서대로 값을 보여줘야 합니다.

위에서도 얘기한 바와 같이 Small과 Aggregate 함수를 이용해 구할 수 있습니다.

 

㉮ Small 함수를 이용한 수식

=SMALL(IF(ISNUMBER(FIND(A2,법정동코드!B2:B49860))*(법정동코드!C2:C49860="존재"),ROW(법정동코드!B2:B49860)),ROW()-1)로서 첫 번째로 작은 행 수, 다시 말해 가장 작은 행 수 6150이 구해졌습니다.

 

이제 C2셀의 채우기 핸들을 C30행까지 끌면

7148 값이 나와야 하는데, 4가 나왔습니다.

 

이것은 A2셀과 법정동코드 시트의 B열과 C열 지정이 절대참조 형식이 아니라서 그런 것이므로 F2키를 눌러 수식 편집모드로 들어간 후 원하는 곳에서 F4키를 눌러 절대참조형식으로 바꾸고

=SMALL(IF(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"),ROW(법정동코드!$B$2:$B$49860)),ROW()-1)

 

다시 C30행까지 끕니다.

 

그러면 4행까지는 숫자가 구해지는데 5행부터는 청계동이 없어서 #NUM! 에러가 발생합니다.

 

㉯ Aggregate 함수를 이용한 수식

=AGGREGATE(15,3,1/(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))*ROW(법정동코드!$B$2:$B$49860),ROW()-1)

로서

 

Small 함수와 다른 점은 첫 번째 인수가 15로서, 15는 Small 함수를 지정하는 것이고,

 

그다음 3은 숨겨진 행, 오류 값 등을 무시하는 옵션이고,

 

그다음은 array를 만드는 수식입니다.

 

1/(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))*ROW(법정동코드!$B$2:$B$49860)

 

위와 같이 if 함수를 사용하지 않고, 

맨 앞에 '1/'를 써서 0일 경우 오류를 발생시키고,

 

조건에 해당하는

(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))를 괄호로 감싸야하며,

 

행 수를 배열로 만들기 위해 행 수를 곱하기로 연결합니다.

*ROW(법정동코드!$B$2:$B$49860)

 

서수 k는 동일합니다.

첫 번째 값 6150이 구해졌습니다.

 

이제 C30셀까지 끌면

 

같은 값이 구해집니다.

 

④ Index함수로 원하는 법정동명 표시하기

=INDEX(법정동코드!$B$1:$B$49860,AGGREGATE(15,3,1/(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))*ROW(법정동코드!$B$2:$B$49860),ROW()-1))

 

index 다음에 array(배열)를 입력하는데,

 

1행부터 시작하므로

법정동코드!$B$1:$B$49860이라고,

B2셀이 아닌 B1셀부터로 지정해야 합니다.

 

그리고, 쉼표를 입력한 다음, 마지막에 괄호를 닫고 엔터키를 누르면 됩니다.

 

이제 C30셀까지 채우기 핸들을 더블 클릭해서 C30셀까지 입력합니다.

 

 

⑤ IfError 함수 사용하기

이제 에러값을 공백으로 만들기 위해 

=IFERROR(INDEX(법정동코드!$B$1:$B$49860,AGGREGATE(15,3,1/(ISNUMBER(FIND($A$2,법정동코드!$B$2:$B$49860))*(법정동코드!$C$2:$C$49860="존재"))*ROW(법정동코드!$B$2:$B$49860),ROW()-1)),"")라고 입력하고, 채우기 핸들을 더블 클릭합니다.

 

그러면 입력한 읍면동명을 포함하는 법정동이 구해졌습니다.

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

1.25MB