좌표와 일치하는 도면 번호찾기(3)

이번에는 구역을 기준으로 도면 번호를 찾는 것이 아니라

실제 좌표를 기준으로 도면 찾는 것을 해보겠습니다.

 

도면 번호 찾기3.xlsx

0.01MB

 

 

아래와 같이 도면별 East Longitude(동경)와 North Latitude(북위) 좌표가 있을 때

북위 G3좌표와 동경 G6좌표에 해당하는 도면을 찾아보겠습니다.

도면이 겹칠 수도 있기 때문에 3개까지 찾아보겠습니다.

 

 

1. Aggregate 함수

순번대로 찾을 때 사용하는 함수가 Aggregate 함수입니다.

Aggregate 함수의 구문은 배열형의 경우 아래와 같습니다.

AGGREGATE(function_num, options, array, [k])

 

가. 첫 번째와 네 번째 인수

함수(function)에 해당하는 번호(num)로 

배열형인 경우 14번부터 시작하는데,

14가 Large함수, 15가 Small 함수입니다.

 

따라서, 14는 네 번째 인수 k값에 따라 첫 번째부터 순서대로 큰 값을 구하는 것이고,

15는 작은 값을 구하는 것입니다.

 

도면번호를 순서대로 구할 것이므로 행 수가 작은 것부터 시작돼야 하므로 Small함수에 해당하는 15를 사용해야 합니다.

 

나. 두 번째 인수

options는 오류값, 숨겨진 행 또는 Subtotal 및 Aggregate 함수를 무시할 것인가에 대한 옵션으로 아래와 같은데,

옵션	동작
0 또는 생략	중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
1	숨겨진 행, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
2	오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
3	숨겨진 행, 오류 값, 중첩된 SUBTOTAL 및 AGGREGATE 함수 무시
4	아무것도 무시 안 함
5	숨겨진 행 무시
6	오류 값 무시
7	숨겨진 행 및 오류 값 무시

 

일치하지 않는 값은 오류를 발생시켜 제외시켜야 하므로 

오류값을 무시하는 옵션인 2,3,6,7중 하나를 사용할 수 있는데, 가장 넓은 범위인 3을 사용하겠습니다. 

 

다. 세 번째 인수

array라고 되어 있는데 조건에 맞는 값들의 모임을 말합니다.

 

위 좌표에서 범위에 맞는 것을 찾아 행 값을 반환하도록 하면 됩니다.

 

 

2. array 만들기

 

아래 표에서 동경은 B열이상 C열이하여야 하며,

북위는 D열이상 E열이하여야 합니다.

 

 

따라서, 이것을 수식을 만드는데 배열 수식을 이용하여 만듭니다.

그러면

=(g6>=b2:b21)*(g6<=c2:c21)*(g5>=d2:d21)*(g5<=e2:e21)

으로 하면 되는데,

*는 and의 의미입니다. 다시 말해 네 가지 조건이 모두 맞아야 True가 반환됩니다.

 

Microsoft 365의 경우에는 배열 수식을 입력하고 엔터키를 치면 아래와 같이 H2셀의 수식하나로 범위 내의 모든 값이 반환되는데,

 

이전 버전은 수식을 입력한 후 CSE라고 Ctrl+Shift+Enter키를 입력해야 합니다.

그리고, 위 수식에는 중괄호 표시가 없는데, 이전 버전은 =다음과 수식의 끝에 중괄호 표시가 생깁니다.

 

위 수식의 결괏값을 보면 1과 0으로 표시되는데, 1은 True이며, 0은 False입니다.

1인 것 중 7행의 좌표를 살펴보면

동경이 559,000으로 B열과 C열 사이에 위치하며,

북위는 678,100으로 D열과 같고, E열보다 작아서

조건에 일치합니다.

 

마찬가지로 11행도 동경은 B열과 C열 사이에 있고,

북위도 D열과 E열 사이에 있습니다.

 

3. 행수 구하기

도면 번호는 A열에 있는데 도면 번호를 가져오려면 행 수를 알아야 합니다.

 

따라서 행수를 구해야 하는데 행수는 Row함수를 이용합니다.

 

수식으로 표현하면 row(a2:a21)이 됩니다.

 

따라서, 2번의 조건 수식과 *로 연결하면

=(G6>=B2:B21)*(G6<=C2:C21)*(G5>=D2:D21)*(G5<=E2:E21)*ROW(A2:A21) 가 되고,

0과 7 그리고 11이 반환됩니다.

 

그런데 문제는 작은 값부터 값을 가져와야 하는데, 0이 있어서 문제이므로

1을 기존 수식으로 나눠서 #DIV/0! 오류, 다시 말해 0으로 나눌 수 없다는 오류를 발생시켜야 합니다.

 

그러면 수식은 =1/(G6>=B2:B21)*(G6<=C2:C21)*(G5>=D2:D21)*(G5<=E2:E21)*ROW(A2:A21)이 되는데,

값을 보니 오류값과 7,11 이외에도 여전히 0이 있습니다.

 

따라서, 1/ 다음의 조건을 괄호로 묶어서 먼저 에러를 발생시키고, row부분과 연결해야 합니다.

 

그러면 수정된 수식은

1/((G6>=B2:B21)*(G6<=C2:C21)*(G5>=D2:D21)*(G5<=E2:E21))*ROW(A2:A21) 이 됩니다.

이제 7과 11을 제외하고는 모두 #DIV/0! 에러입니다.

 

 

4. Aggregate 함수를 이용한 수식 완성하기

array만 완성된 것이므로, 첫 번째 15, 두 번째 인수 3, 네 번째 인수는 1,2,3인데, 이것도 행에 따라 1,2,3이 되도록

row() 함수를 이용해서 도면 번호를 구하는 것이 G7셀, 다시 말해 7행에서 시작하므로 row()-6이라고 하면 수식을 아래로 복사할 때 행 수가 바뀌므로 자동으로 2,3으로 됩니다.

 

이것을 모두 완성해서 아래와 같이 G7셀에  입력하면

=aggregate(15,3,1/((G6>=B2:B21)*(G6<=C2:C21)*(G5>=D2:D21)*(G5<=E2:E21))*ROW(A2:A21),row()-6)

값 7이 나오고,

 

그런데 복사해서 붙여 넣기 전에 아래로 복사할 때 검색범위가 변하면 안 되니까 F4키를 눌러 모두 절대 참조 형식으로 바꿔야 합니다.

 

그러면 수식은 아래와 같이 됩니다.

=AGGREGATE(15,3,1/(($G$6>=$B$2:$B$21)*($G$6<=$C$2:$C$21)*($G$5>=$D$2:$D$21)*($G$5<=$E$2:$E$21))*ROW($A$2:$A$21),ROW()-6)

 

이것을 복사한 후 G8셀과 G9셀에 수식(fx)으로 붙여 넣으면

G8셀은 문제가 없는데, G9셀은 일치하는 값이 없어서 #NUM! 에러가 발생합니다.

 

여기서 ifError함수를 사용하면 5번 작업할 때 에러가 다시 나므로 넣지 않고 넘어가야 합니다.

 

 

5. 도면 번호 가져오기

 

가. indirect 함수

도면 번호를 가져오는데 A열에 해당하는 문자열 "A"와 행수를 &(결합 연산자)로 연결해야 하므로

A7과 같은 문자열이 되므로 indirect함수를 이용해 문자열에 해당하는 셀 값을 반환받아야 합니다.

 

그러면 수식은

=INDIRECT("a"&AGGREGATE(15,3,1/(($G$6>=$B$2:$B$21)*($G$6<=$C$2:$C$21)*($G$5>=$D$2:$D$21)*($G$5<=$E$2:$E$21))*ROW($A$2:$A$21),ROW()-6))

이 됩니다.

 

indirect 함수 안의 '"a"&이하'는 A열과 iferror함수이하 수식으로 구한 행수인 7을 결합하는 것으로

수식에서 indirect안의 수식을 마우스로 끌어서 선택한 후 F9키를 누르면

 

=indirect("a7")이 되며 다시 indirect부터 끝까지 범위를 선택한 후 F9키를 누르면

 

indirect함수가 적용되어 A7셀의 값 dwg_000000006이 반환됩니다.

 

Esc키를 눌러 원래 수식으로 되돌립니다.

 

 

다시 한번 더 G7셀의 수식을 G8셀과 G9셀에 붙여 넣습니다.

 

그런데 세 번째는 일치하는 값이 없어서 에러가 발생하므로

ifError함수를 사용하고, 에러가 발생할 때는 공백으로 처리해야 합니다.

그러면 수식은 아래와 같이 되며

=IFERROR(INDIRECT("a"&AGGREGATE(15,3,1/(($G$6>=$B$2:$B$21)*($G$6<=$C$2:$C$21)*($G$5>=$D$2:$D$21)*($G$5<=$E$2:$E$21))*ROW($A$2:$A$21),ROW()-6)),"")

 

G8셀과 G9셀에 복사한 후 수식으로 붙여 넣으면

이제 문제없이 원하는 도면번호가 잘 표시됩니다.

 

 

나. 셀 주소 Address로 표현하기

위에서는 셀 주소를 "A"와 행 번호를 연결해서 입력했는데,

Address함수를 이용해 행과 열 번호를 입력할 수 있습니다.

 

이 경우 수식은

=IFERROR(INDIRECT(ADDRESS(AGGREGATE(15,3,1/(($G$6>=$B$2:$B$21)*($G$6<=$C$2:$C$21)*($G$5>=$D$2:$D$21)*($G$5<=$E$2:$E$21))*ROW($A$2:$A$21),ROW()-6),1)),"")

이 됩니다.

 

행은 Aggregate함수로 구한 수식이 되며, 열은 A열이기 때문에 1로 입력합니다.

도면 번호 찾기3(완성).xlsx

0.01MB