구글 스프레드시트 호환성 - Index + Aggregate(2)

3. 구글 스프레드시트의 수식 분석

 

가. 읍면동을 포함하는 법정동 불러오기

 

(1) 수식

 

엑셀에서의 수식은

=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)),"")

인데,

 

구글 스프레드시트에서 가져오니

앞에 ARRAY_CONSTRAIN과 ARRAYFORMULA가 붙어 있고,

=ARRAY_CONSTRAIN(ARRAYFORMULA(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)),"")), 1, 1)

 

뒤에는 Array_Contrain의 인수로 ,1,1이 추가됐습니다.

 

수식을 살펴보는데, 더블 클릭해서 함수명 전체를 선택하면

Array_Constrain은 문제가 없는데,

 

ArrayFormula는 자신을 설명하는 것이 아니라 Array_Constrain 함수를 설명하므로 

 

함수명 전체를 선택하지 말고, 함수명 안에 커서를 넣어야 합니다.

 

이런 식으로 함수에 대한 설명을 보니

Aggregate에서 아무런 설명이 없습니다.

 

Aggregate 함수가 구글 스프레드시트에는 없는 것입니다.

 

다른 셀에 =agg라고 입력해도 마찬가지로 agg로 시작하는 함수가 없습니다.

 

 

(2) 수식 수정하기

=sm이라고 입력하니 sm으로 시작하는 Small 함수가 표시되고, 함수에 대한 설명과 Tab키를 누르라는 설명이 표시됩니다.

 

Esc키를 누르니 D3열에  #NAME? 에러가 입력되어 있으므로 입력된 내용은 삭제하고,

 

C2셀의 Aggregate를 Small 함수로 바꿔보겠습니다.

Small 함수의 인수는 데이터 범위, 서수(엑셀 도움말에서는 k, 구글 스프레드시트 인라인 도움말에는 n)인데, Aggregate 함수의 경우는 에러를 무시할 수 있는데, Small 함수는 에러일 경우 처리 옵션이 없으므로 if 함수를 이용해서 True일 경우만 값을 반환하도록 해야 합니다.

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

 

Excel에서 Small함수와 Aggregate 함수의 비교는 https://lsw3210.tistory.com/567 참고

 

위 수식의 의미는 A2셀의 값이 법정동코드 시트의 B2셀에서 B49860셀에 있고, C2셀에서 C49860셀의 값이 "존재"라면(행별 And 조건)  법정동코드 시트의 B2셀에서 B49860셀의 행 수를 반환하는데, ROW()-1라는 서수에 따라 1부터 순서대로 값을 가져오는 것입니다.

 

수정하고 엔터 키를 누르면

값이 여러 개 표시되는 것이 아니라 한 개만 표시됩니다.

 

ArrayFormula 함수가 있는데도 그렇습니다.

혹시 Array_Constrain 함수의 인수가 행 수가 1이라서 그런가 하고 30으로 수정해도 하나만 값이 표시되고,

 

Array_Constrain함수를 지워도 마찬가지입니다.

 

Small 함수의 결괏값이 배열이 아니라서 그렇습니다.

 

 

따라서, C2셀의 수식을 C30셀까지 끄는데, ArrayFormula함수도 필요 없으니 지우고 끕니다.

이때 또 다른 점은 엑셀의 경우는 아래에 값 또는 수식이 있더라도 채우기 핸들을 더블 클릭하는 것이 되는데, 구글 스프레드시트의 경우는 복사하려고 하는 범위에 값이 있으면 더블 클릭이 안된다는 것입니다.

 

C30셀까지 복사했지만 A2셀의 읍면동을 포함하는 법정동으로서 폐지여부가 "존재"인 것은 10개이기 때문에 10개만 표시됩니다.

 

나. 법정동코드 구하기

E2셀의 수식도

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

이므로

 

C2셀의 수식을 복사한 후 E2셀에 붙여 넣고,

Index함수의 첫 번째 인수를 B열에서 A열로 수정하고, Find함수의 찾을 값을 A2에서 D2로만 수정하면 됩니다.

=IFERROR(INDEX('법정동코드'!$A$1:$A$49860, small(if((ISNUMBER(FIND($D$2, '법정동코드'!$B$2:$B$49860))*('법정동코드'!$C$2:$C$49860="존재")), ROW('법정동코드'!$B$2:$B$49860)), ROW()-1)), "")

 

열 너비가 좁으므로 자동 조절하기 위해 E열과 F열 사이의 경계선을 더블 클릭합니다.

법정동코드의 열 너비가 코드의 길이에 맞게 조절됐습니다.

 

다. PNU 구하기

F2셀을 클릭하니 "Concat에 대한 인수의 개수가 2개여야 하는데 1개만 입력됐다"라고 합니다.

 

엑셀의 경우는 Concat함수의 인수로 범위를 지정할 수 있는데 그것이 지원되지 않습니다.

따라서, Concatenate함수로 변경해야 합니다.

 

열너비가 좁으므로 마찬가지로 열 경계선을 더블 클릭해서 자동 조절합니다.

 

A2셀의 값을 수정하면 D2셀의 값이 에러로 표시되는데,

 

에러 표시가 나지 않도록 A2셀의 값이 변경되면 D2셀의 값을 지우는 것은 다음 편에서 다루겠습니다.