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

ttps://lsw3210.tistory.com/569

 

위 글로 엑셀에서 Filter와 Sort 함수를 이용해

① 읍면동을 입력하면

② 그것을 포함하는 법정동 목록을 표시하고

③ 해당 법정동을 선택하면

④ 법정동코드, 최종적으로는 해당 법정동 및 지번의 PNU를 구해 봤는데,

 

이 파일을 구글 스프레드 시트로 가져와서 실행해 보겠습니다.

 

1. 구글 스프레드시트로 가져오기

파일 - 가져오기를 누른 후 위 URL에 첨부된 "법정동 선택 후 PNU만들기(filter_sort)(완성).xlsx"를 가져오면

 

아래와 같이 C2셀, I2셀에서 에러가 납니다.

 

2. 수식 수정하기

가. 읍면동 포함 법정동 구하기 

엑셀의 수식은 =SORT(FILTER(법정동코드!B2:B49860,ISNUMBER(FIND(A2,법정동코드!B2:B49860))*(법정동코드!C2:C49860="존재")))인데,

 

구글 스프레드시트 형식으로 변환된 수식은

=ARRAY_CONSTRAIN(ARRAYFORMULA(_xlws.SORT(_xlws.FILTER('법정동코드'!B2:B49860,ISNUMBER(FIND(A2,'법정동코드'!B2:B49860))*('법정동코드'!C2:C49860="존재")))), 10, 1)

입니다.

 

엑셀의 결과가 10개라 Array_Constrain의 행수가 10이고, 열수는 1입니다.

또한 배열수식으로 이해해서 ArrayFormula가 있고,

그 안에 Sort와 Filter 함수가 있는데, 엑셀 함수를 변환하기 위해 앞에 _xlws.이 추가되었습니다.

 

VBA에서 엑셀 함수를 사용할 때 WorksheetFunction.을 붙이는 것과 유사합니다.

 

그런데, 엑셀 수식과 동일하게 만들기 위해 Array_Constrain과 ArrayFormula, 그리고, _xlws.을 지워도

잘 작동합니다. 굳이 붙일 필요가 없는데 쓸데없이 붙이네요.

 

'청계동'으로 수정해도 잘 작동합니다.

 

엑셀 처럼 범위를 절대 참조 형식으로 지정하지 않아도 됩니다.

다만 Microsoft 365와 다른 점은 결과 영역 부분을 파란선으로 둘러쌓지 않는다는 것입니다.

 

나. '법정동 선택' 수정

법정동 옆의 콤보 상자 버튼을 누르니, 청계동을 포함한 법정동이 목록으로 표시되지 않고 에러가 발생합니다.

 

https://lsw3210.tistory.com/571

에서는 같은 구글 스프레드시트인데도 문제없이 '데이터 유효성 검사'가 잘 되는 것과 다릅니다.

 

구글 스프레드시트의 '데이터 유효성 검사'는

데이터 메뉴아래 '데이터 확인' 메뉴입니다.

 

D2셀을 선택한 상태에서 데이터 - 데이터 확인을 선택하면

 

'데이터 확인 규칙' 창이 열리는데, D2셀만 표시되고 규칙이 안보입니다.

이 때 D2셀이라고 쓰인 부분을 클릭하면

 

아래에 규칙이 표시되는데,

기준이 '드롭다운(범위)'이고,

그 아래 원본 범위가 =INDIRECT("c2:c"&COUNTA(C1:C30))라는 수식으로 되어 있습니다.

 

구글 스프레드시트에도 indirect 함수가 있고 셀에서 수식 적용시에는 아래와 같이 문제가 없지만

 

유효성 검사시에 제대로 작동하지 않아 오류가 발생하는 것입니다.

 

 

따라서, indirect와 CountA 등 함수와 &(결합 연산자) 등을 모두 지우고, 순수하게 c2:c30부분만 남겨두고 닫기(X) 아이콘을 누릅니다(완료 버튼을 누르지 않아도 됩니다).

 

 

 

그러면 C2셀에서 C30셀까지의 법정동이 목록으로 표시되는데, 엑셀과는 달리 맨 아래에 공백 셀이 표시되지 않습니다.

따라서, Indirect와 CountA 함수를 조합해 데이터가 있는 범위만 지정할 필요가 없습니다.

 

 

 

위 화면에서 '잘못됨'이라는 오류 메시지는 D2셀의 값이 목록에 없어서 그런 것입니다.

 

A2셀 값이 수정됐을 때 D2셀 값을 초기화하는 것은

https://lsw3210.tistory.com/572

글을 참고 바랍니다.

 

다. 열 너비 조절

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

 

라. PNU 구하기 수식 수정

i2셀의 값이 오류로 표시되는 것은 Concat 함수는 2개의 인수를 받는데 범위 하나만 인수로 입력됐기 때문으로 

 

Concatenate로 수정하면 됩니다.

 

완성된 파일은 아래를 참고 바랍니다.

https://docs.google.com/spreadsheets/d/1tAQLlTvsFb3WwjyWmamruPbdgZ0Dd_s-5eeUJ6ILZpE/edit?usp=sharing