[구글스프레드시트] Filter 수식을 이용해 원하는 데이터 추출

본 내용은 엑셀에서도 동일하게 적용됩니다.

표에서 어떠한 조건에 의해 트랜잭션을 가져오려면 표의 헤더에 필터를 만들어 원하는 범례를 선택하여 표에서 직접 원하는 조건에 대당하는 내용을 가져올 수 있습니다.

구글시트의 표에서 필터를 적용한 모습

헌데 직접 표에 필터를 적용하는 것이 아니라 Filter 라는 명령어를 이용하여 위와 같이 원하는 내용을 가져오는 방법도 가능합니다.

Filter의 문법은 아래와 같습니다.

FILTER(출력영역주소,조건1,조건2.....조건n)

위와 같이 FILTER 라는 수식어를 입력한 뒤 출력할 표의 영역을 지정하고 n개의 조건을 설정하면 됩니다.

위 예제 표에서 한번 직급을 선택하여 성명과 연봉을 출력하는 명령어를 만들어본다면 아래와 같이 만들어볼 수 있겠습니다.

FILTER 기능을 이용해 데이터를 가져온 결과

위와 같이 간단한 명령어를 통해 원하는 데이터를 가져올 수 있습니다.

N개의 조건 설정이 가능하니 이번에는 연봉이 7천5백만원을 초과하는 인원만 결과를 가져오는 것으로 조건을 추가해보실까요?

연봉은 E2:E6 영역이니 아래와 같이 수식을 추가하면 되겠습니다.

=FILTER(D2:E6,C2:C6="대리",E2:E6>75000000)

실질적으로 적용시켜보면 아래와 같습니다.

그러면 특정 값을 제외하고 값을 가져오려면 어떻게 해야 할까요?

위에서 대리가 아닌 값을 가져오려면 아래와 같이 표현해주시면 되겠습니다.

=FILTER(D2:E6,C2:C6<>"대리",E2:E6>75000000)

<> 를 사용하시면 해당 값이 아닌 것을 참조하게 됩니다.

이제 원하는 값들을 가져왔습니다만 수정이 가능한지 한번 볼까요?

위 내용을 수정하게 되면 아래와 같이 에러가 납니다.

"배열 결과는 G4에서 데이터를 덮어쓰기 때문에 스프레드 시트에서 펼쳐지지 않습니다" 라는 에러가 나오는군요.

G4 셀을 보면 제가 추가적으로 입력한 수정 이라는 값이 G4 셀에 들어 있군요.

결국 결과 값이 펼쳐지는 영역에 뭔가 데이터를 입력하게 되면 에러가 나는 것은 Importrange, Googlefiance와 동일하게 적용이 되는 것으로 보여집니다.

다만 위에서 보시는 것과 같이 서식의 변경은 가능합니다.

엑셀에서 위 수식을 그대로 구현해보았습니다.

동일하게 결과를 얻어 내실 수 있습니다.

사실 엑셀에서는 이 기능에 대해 advanced filter를 많이 사용하고 계실텐데요, 제가 보기에는 advanced filter 보다 더욱 심플하게 동일한 결과를 얻어 낼 수 있기 때문에 굳이 advanced filter를 사용하시는 것 보다는 filter 명령어를 사용하시는 것을 추천드리고 싶습니다.

 그렇다면 이제 위 구글스프레드시트에서 해당 조건들을 변수화하여 한번 적용해보도록 하겠습니다.

위에서 변수화 할 수 있는 부분은 직급과 연봉이겠죠?

위의 수식을 잘 보시면 C2:C6 [직급] 은 K2 셀을 참조하게 변수를 설정해놓았습니다.

K2 셀은 제일 우측 화면과 같이 드롭박스를 설정하였고 이 드롭박스를 통해 대리/과장/부장 등을 선택할 수 있게 되어 있습니다.

E2:E6 [연봉] 영역은 K3를 참조하고 이 부분은 사용자가 숫자를 입력하게 되면 해당 숫자보다 큰 트랜잭션만 가져옵니다.

2개의 변수를 설정하였고, 이제 값들을 입력해봅시다.

K2 셀에서 대리를 선택하니 원본에서 대리 직급을 선택하여 2개의 트랜잭션을 가져왔습니다.

이제 연봉란에 7천을 입력해 보실까요?

보시는바와 같이 연봉이 7천 이상인 트랜잭션을 참조하여 결과물을 출력합니다.

이와 같이 조건들을 변수화하여 입력을 받고 입력받은 변수에 대해 원본을 참조하여 결과를 출력하는 것이 가능합니다.

위와 같이 변수화를 하게 되면 정보이용자가 해당 보고서와 상호작용을 통해 정보를 얻어낼 수 있다는 장점이 있습니다.

수식 자체가 어렵지 않으니 한번 실습을 해보시면 분명 여러분들의 것으로 만들어 내실 수 있으실 것입니다.

오늘 포스팅은 여기까지 입니다

대단히 감사합니다.