Posted on by SmartAgent

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

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

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

데이터 준비

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

filter00

 

요리유형으로 필터링

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

filter01

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

filter02.png

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

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

Query함수로 필터링

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

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

filter05.png

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

filter06.png

filter07.png

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

filter08.png

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

filter09.png

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

filter10.png

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

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

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

“냉면”으로 필터링

filter12.png

“곰탕”으로 필터링

filter13.png

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

주소로 필터링

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

filter15.png

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

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

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

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

filter16.png

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

 

Query함수로 필터링

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

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

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

filter23.png

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

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

 

 

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

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

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

    좋아요

    답글

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

      좋아요

      답글

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

    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. 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. 감사합니다! 그런데 그래도 ERROR가 나네요 ㅠㅠ 쿼리는 총 3개의 인수만 허용합니다. 현재 인수는 10개입니다. 라고 표기되는데, 구글의 능력초과 문제인걸까요? 태그만큼 글의 행을 늘려서 중복되게 만들면 중복조건을 하지 않아도 되겠지만, 그러면 일이 더 많아져서.. 다시한번 여쭤봅니다. 바쁘실텐데 답 달아주셔서 감사합니다!

        좋아요

      2. 다시 확인해보니 따옴표가 몇개 더 들어간 것같습니다.
        따옴표가 잘못 들어가면 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)

        좋아요

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

    좋아요

    답글

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

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

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

    좋아요

    답글

    1. 안녕하세요. 데이터를 월별로 분리하고 싶으시다면,
      테이블에 MONTH() 함수를 사용하여 날짜 데이터에서 월 숫자만 추출하여 월 열을 만드시고 그 월 열을 기준으로 Group by로 묶어주시면 월별 데이터를 구할 수 있습니다.
      아래 링크를 참고하시기 바랍니다.

      날짜 함수

      구글 스프레드시트 사용법 (17) 날짜 함수

      Group By 그룹으로 묶기

      스프레드시트 Query 사용법 (7) Group By 그룹으로 묶기

      KOFIC 2018년 일별 박스오피스 분석 > 월간 관객수 매출
      https://docs.google.com/spreadsheets/d/1hHSZAg6UzvyIc19z1KvDXLuOSTBWVe4K55EGwoG9Yug/edit#gid=499614011

      좋아요

      답글

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

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

    좋아요

    답글

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

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

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

    좋아요

    답글

    1. 적어주신 수식의 따옴표가 전각문자라서 에러가 발생하는 것 같습니다.
      따옴표를 교체해보았습니다. 아래 수식을 시도해보시기 바랍니다.

      =QUERY(‘리스트’!A:N,”select B,C,D,E,F,G,H where D='”&B1&”‘”,-1)

      좋아요

      답글

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

    좋아요

    답글

댓글 남기기