구글 스프레드시트 사용법 (8) 데이터 필터링하기

Written by

SmartAgent

Google Workspace

스프레드시트에서 기본으로 제공하는 필터 만들기 기능과, Query함수를 필터처럼 사용하는 방법을 알려드리겠습니다.

미쉐린 가이드 서울 2019의 빕 구르망의 레스토랑 데이터를 요리유형별, 지역별로 필터링해서 보는 시트를 만들어보겠습니다.

데이터 준비

★ 샘플 스프레드시트: data시트에 빕 구르망에 선정된 61개 레스토랑의 요리유형, 레스토랑 이름, 주소, 연락처, 웹사이트, 예약유형의 데이터를 정리했습니다.

filter00

요리유형으로 필터링

헤더 정보가 있는 1행을 선택 후, 데이터 > 필터 만들기를 클릭하면 필터 기능을 이용할 수 있습니다.

filter01

헤더의 ▽를 클릭하면 필터링할 조건을 설정할 수 있습니다.

요리유형 중 “냉면”으로 필터링

필터링할 조건을 여러개 선택 할 수도 있습니다.
“곰탕”, “냉면”으로 필터링

Query함수로 필터링

Query 함수로도 필터 비슷하게 사용할 수 있습니다. 필터 기능과 다른 점은 필터는 데이터가 있는 시트에서만 사용할 수 있지만, Query는 다른 시트에서 사용할 수 있다는 점입니다.

Query로 필터링할 시트를 새로 만듭니다.

A1셀에 데이터 > 데이터 확인… 메뉴로 드롭다운 목록으로 만듭니다.

미리 요리유형을 중복되지 않는 목록으로 정리한 범위를 선택합니다.

범위 선택 후, [저장] 버튼을 클릭합니다.

A1셀에 드롭다운 목록이 추가됩니다.

아래에 A1셀을 참조하는 query문을 넣습니다.

query: “select B, C, D where A=’”&A1&“‘”

select 표시할 데이터
- 레스토랑 : B
- 주소 : C
- 연락처 : D
지정한 요리유형의 데이터만 : where A=’<A1셀의 데이터>‘

“냉면”으로 필터링

“곰탕”으로 필터링

★ 샘플 스프레드시트: 요리 유형별 리스트

주소로 필터링

주소같이 내용이 길고, 전부 다른 데이터는 필터 기능으로 필터링이 조금 번거롭습니다.

필터링할 조건을 직접 입력해서 설정해야합니다.

이 슬라이드 쇼에는 JavaScript가 필요합니다.

필터링하기 좋은 데이터를 만들어주는 것도 한 방법입니다.

REGEXEXTRACT 함수로 “*구”만 추출하여 필터링 조건으로 사용할 수 있습니다.

이 슬라이드 쇼에는 JavaScript가 필요합니다.

Query함수로 필터링

요리유형별 리스트와 같은 방법으로 A1셀에 중복되지 않는 주소가 드롭다운 목록으로 나오도록 설정하면 지역별 레스토랑을 필터링하여 볼 수 있습니다.

query: “select B, C, D where C starts with ‘”&A1&“‘”

select 표시할 데이터
- 레스토랑 : B
- 주소 : C
- 연락처 : D
지정한 주소로 시작하는 데이터만 : C starts with ‘<A1셀의 데이터>‘

이 슬라이드 쇼에는 JavaScript가 필요합니다.

★ 샘플 스프레드시트: 지역별 리스트

구글 스프레드시트의 다른 활용법을 알고싶으시다면!
» G Suite 사용법 #4 스프레드시트 활용 예시 모음

(동영상 강의 광고)

스프레드시트로 만든 가계부인데
스마트폰에서도 쓰기 쉽다고?

스마트폰에서도 쓰기 쉬운 스프레드시트 가계부

“구글 스프레드시트 사용법 (8) 데이터 필터링하기” 글의 댓글 18개

2019년 7월 23일

배코치

정말정말 감사합니다!!!!!!

좋아요좋아요

답글
2020년 9월 9일

강태익

안녕하세요. query함수 관련해서 해결되지 않는 것이 있어 구글링하다가 찾게되었습니다.
query함수를 이용해 다른 시트에서 조건 검색을 하려고 합니다. 예를들어 A시트에서 QUERY 함수와 WHERE 을 이용해 ‘주문내역’ 시트을 내용을 필터링하고 싶습니다.
아래와 같이 했더니 수식 파싱 오류가 발생했다고 합니다.
=QUERY(‘주문내역’!A4:F1003 where J=’”&A3&”‘)
어떻게 해야할지 모르겠습니다… 조언 부탁드립니다.

좋아요좋아요

답글
1. 2020년 9월 9일
  
  SmartAgent
  
  적어주신 수식은 Query함수 구문에 맞지 않습니다. 구문에 맞게 적어보자면 이렇게 됩니다. 조건으로 넣으신 J열이 앞쪽 범위내에 없기때문에 범위를 J열까지 확장했습니다. =QUERY(‘주문내역’!A4:J1003, “Select * where J=’”&A3&”‘”, -1) Query함수에 대한 자세한 사용법은 제 블로그 아래 페이지를 참고하시기 바랍니다. 스프레드시트 Query 문법 목차 https://smartagent.blog/2019/10/27/query_index/
  
  좋아요좋아요
  
  답글
2020년 9월 22일

김누리

안녕하세요. 쿼리로 데이터 재정렬을 하는데 문자열이 아닌 특정셀을 기준으로 검색하려고 하니 방법을 몰라 이틀 내내 검색하고 책 보고 안되어서 포기하다가 포스팅 보고 해결했는데요!!! 감사합니다!! 그런데, 동일 방법으로 조건을 추가하여 특정셀이 여러 열에 해당하게는 안되는 걸까요? 오류가 나서 조언 구하고자 글 남겨봅니다. 감사합니다.

QUERY(‘아세안 주간 동향(가안)/샘플’!A1:I46,”select A, B, C, D, E, F, G, H, I where E=’”&E3&”‘”, 1) -> 포스팅 보고 해결!!

=QUERY(‘아세안 주간 동향(가안)/샘플’!A1:I46,”select A, B, C, D, E, F, G, H, I where E=’”&E3&”‘” or where F=’”&E3&”‘” or where G=’”&E3&”‘” or where H=’”&E3&”‘” or where I=’”&E3&”‘”) -> 에러발생

글마다 태그가 여러개 달려있고, E3에 태그를 드롭다운으로 만들어 검색하는데, 여러개의 태그가 달려있어 여러 열의 조건을 모두 충족하는 것을 구현해보고자 합니다.

좋아요좋아요

답글
1. 2020년 9월 22일
  
  SmartAgent
  
  where는 한번만 쓰시면 됩니다.
  댓글의 쿼리문을 수정해보았습니다. 참고하시기 바랍니다.
  
  =QUERY(‘아세안 주간 동향(가안)/샘플’!A1:I46,”select A, B, C, D, E, F, G, H, I where E=’”&E3&”‘” or F=’”&E3&”‘” or G=’”&E3&”‘” or H=’”&E3&”‘” or I=’”&E3&”‘”, -1)
  
  좋아요좋아요
  
  답글
  1. 2020년 9월 22일
    
    김누리
    
    감사합니다! 그런데 그래도 ERROR가 나네요 ㅠㅠ 쿼리는 총 3개의 인수만 허용합니다. 현재 인수는 10개입니다. 라고 표기되는데, 구글의 능력초과 문제인걸까요? 태그만큼 글의 행을 늘려서 중복되게 만들면 중복조건을 하지 않아도 되겠지만, 그러면 일이 더 많아져서.. 다시한번 여쭤봅니다. 바쁘실텐데 답 달아주셔서 감사합니다!
    
    좋아요좋아요
  2. 2020년 9월 22일
    
    SmartAgent
    
    다시 확인해보니 따옴표가 몇개 더 들어간 것같습니다.
    따옴표가 잘못 들어가면 select 구문이 이상해져서 ERROR가 납니다.
    
    =QUERY(‘아세안 주간 동향(가안)/샘플’!A1:I46,”select A, B, C, D, E, F, G, H, I where E=’”&E3&”‘ or F=’”&E3&”‘ or G=’”&E3&”‘ or H=’”&E3&”‘ or I=’”&E3&”‘”, -1)
    
    좋아요좋아요
2021년 3월 11일

감사합니다

감사합니다

좋아요좋아요

답글
2021년 6월 1일

Jolie Ahn

안녕하세요, Query 함수를 사용해서 데이터 정렬까지는 했는데 조건을 참조해서 필터 하는건 자꾸 에러가 나네요. 공유해주신 구문을 복사해서 필요한 내용만 수정했는데 ‘수식파싱오류가 발생했습니다’란에러가 나네요
=query(원본 최근 순 정렬!A1:L,”select a,b,c,d,e,f,g,h,i,j,k,l where c=’”&c1&”‘”, 1)

좋아요좋아요

답글
1. 2021년 6월 3일
  
  SmartAgent
  
  적어주신 select문은 문제가 없어보입니다. 앞쪽의 범위가 잘못 지정된 것으로 보입니다. A:L 로 범위를 수정해보시기 바랍니다.
  
  좋아요좋아요
  
  답글
2021년 7월 10일

궁금맨

에이전트님 글보고 많은걸 배우고 있습니다. 너무 감사합니다.
이것저것 시도해보다 도저히 안되는게 있어서 질문드립니다

https://docs.google.com/spreadsheets/d/1PSvf-uEtcLejc4dbBWHY5DPIdc6Ib4_mhCHGKc6AkAo/edit?usp=sharing

위에 공유시트에 질문내용이 있습니다 도와주세요 ㅜㅜ

좋아요좋아요

답글
1. 2021년 7월 11일
  
  궁금맨
  
  답변 보았습니다. 신경써주셔서 감사드립니다!
  
  좋아요좋아요
  
  답글
2022년 5월 16일

문의드려요

안녕하세요 우선 유용한 정보 남겨주셔서 감사합니다.
스프레드시트에서 쿼리를 활용해 월별 데이터를 정리하려고 하는데요,
A열에는 거래처 정보 B~ 이후 열에는 월별 매출표를 기입하려고 합니다.
이때 총계 기준으로 TOP 10개의 거래처만 나열하려고 하는데 방법을 몰라 문의드립니다.

현재 거래처의 매출 총계 기준 TOP 10으로는 나열하였는데 월별로 분리하는 방법이 있을까요?

답변주시면 몹시 감사하겠습니다.

좋아요좋아요

답글
1. 2022년 5월 16일
  
  SmartAgent
  
  안녕하세요. 데이터를 월별로 분리하고 싶으시다면,
  테이블에 MONTH() 함수를 사용하여 날짜 데이터에서 월 숫자만 추출하여 월 열을 만드시고 그 월 열을 기준으로 Group by로 묶어주시면 월별 데이터를 구할 수 있습니다.
  아래 링크를 참고하시기 바랍니다.
  
  날짜 함수
  
  구글 스프레드시트 사용법 (17) 날짜 함수
  
  Group By 그룹으로 묶기
  
  스프레드시트 Query 사용법 (7) Group By 그룹으로 묶기
  
  KOFIC 2018년 일별 박스오피스 분석 > 월간 관객수 매출
  https://docs.google.com/spreadsheets/d/1hHSZAg6UzvyIc19z1KvDXLuOSTBWVe4K55EGwoG9Yug/edit#gid=499614011
  
  좋아요좋아요
  
  답글
2022년 5월 18일

랄라

수식파싱 오류가 발생하여 문의드립니다..!

=QUERY(‘리스트’!A:N,”select B,C,D,E,F,G,H where D=’”&B1″‘”)
으로 B1란에 드롭다운을 했는데, 수식파싱오류가 발생합니다.. 무슨문제인지 찾지 못해 문의드립니다 ㅠ

좋아요좋아요

답글
2022년 5월 18일

랄라

위 문의에 재질문 드립니다..!

=QUERY(‘리스트’!A:N,”select B,C,D,E,F,G,H where D=’”&B1&”‘”,-1)
헤더를 -1 혹은 없애도 똑같이 헤드값만 나오고 상세내용이 나오질 않습니다.

어떤 이유로 결과값이 나오지 않는 걸까요…. 너무 고통스럽습니다..ㅠ

좋아요좋아요

답글
1. 2022년 5월 19일
  
  SmartAgent
  
  적어주신 수식의 따옴표가 전각문자라서 에러가 발생하는 것 같습니다.
  따옴표를 교체해보았습니다. 아래 수식을 시도해보시기 바랍니다.
  
  =QUERY(‘리스트’!A:N,”select B,C,D,E,F,G,H where D=’”&B1&”‘”,-1)
  
  좋아요좋아요
  
  답글
2022년 5월 30일

매너배너

=QUERY(‘삭제할것’!A:I,”select A, B, C, D where B=’”&A1&”‘”,1) 수식의 결과 값이 나오긴하는데 왜 where =A 가 아닌 B 로했을때 나오는지 모르겠습니다.

좋아요좋아요

답글