아별팁(abltip)

[태그:] QUERY

  • 구글 스프레드시트 QUERY + IMPORTRANGE 실무 활용법

    사무직 직장인이라면, 복붙 대신 이 조합부터 익혀야 합니다

    나는 예전에는 엑셀에서 필터 걸고, 피벗 돌리고, VLOOKUP 붙이고,
    안 맞으면 다시 범위 수정하고,
    그러다가 퇴근 시간이 지나버리는 일을 자주 했다.

    그런데 구글 스프레드시트에서는
    이 과정을 훨씬 짧게 줄여주는 조합이 있다.

    바로 IMPORTRANGE + QUERY다.

    쉽게 말하면 이렇다.

    • IMPORTRANGE는 다른 파일의 데이터를 가져오는 기사님
    • QUERY는 가져온 데이터를 내가 원하는 조건대로 정리해 주는 실무 대리

    즉,
    “여러 파일에 흩어진 데이터를 한 번에 불러와서, 필요한 조건만 걸러 보고서처럼 정리하는 것”
    이 가능해진다.

    사무직 실무에서 이게 왜 좋냐면,

    • 매번 다른 시트 열어서 복사할 필요가 없고
    • 필터를 매번 다시 걸 필요도 없고
    • 보고서 형식으로 재가공하는 작업도 훨씬 줄어든다

    오늘은 이 조합을 직장인 실무 관점에서 정리해 보겠다.

    1. 먼저, IMPORTRANGE가 뭐냐

    문법은 아주 단순하다.

    =IMPORTRANGE(구글시트URL, 데이터범위)

    예를 들면,

    =IMPORTRANGE("https://docs.google.com/......", "매출시트!A1:F")

    이렇게 쓰면
    다른 구글시트 파일의 매출시트!A1:F 범위를 현재 파일로 가져올 수 있다.

    처음 연결할 때는 접근 허용 버튼을 한 번 눌러줘야 한다.

    이 함수만 써도 파일 간 연결은 되지만,
    실무에서는 대부분 여기서 끝나지 않는다.

    우리가 진짜 원하는 건 이런 거다.

    • 특정 날짜만 보고 싶다
    • 특정 계정과목만 보고 싶다
    • 공백은 제외하고 싶다
    • 특정 단어가 들어간 행만 보고 싶다
    • 여러 파일 데이터를 한 번에 합치고 싶다
    • 합계나 피벗 형태로 요약하고 싶다

    이럴 때 QUERY가 붙는다.

    2. QUERY 함수는 “필터 + 정렬 + 집계 + 요약”을 한 줄로 하는 함수다

    기본 문법은 아래와 같다.

    =QUERY(데이터범위, "쿼리문", 헤더수)

    예를 들면,

    =QUERY(A1:F, "SELECT A, C, F WHERE B = '완료' ORDER BY F DESC", 1)

    의미는 이렇다.

    • A, C, F열만 가져오고
    • B열이 ‘완료’인 행만 남기고
    • F열 기준으로 내림차순 정렬해라

    엑셀에서라면
    필터 걸고, 열 숨기고, 정렬하고, 복사해서 다른 시트에 붙이는 작업이다.

    그런데 QUERY는 이걸 한 줄로 끝낸다.

    3. 실무에서는 A,B,C보다 Col1, Col2 방식이 더 낫다

    같은 파일 안에서는 A, B, C로 써도 되지만,
    외부 파일을 불러오는 IMPORTRANGE와 같이 쓸 때는 Col 방식이 훨씬 안정적이다.

    예를 들어,

    =QUERY(IMPORTRANGE(C10, D10), "SELECT Col1, Col2, Col3 WHERE Col2='외환차손'", 1)

    이렇게 쓰는 식이다.

    왜 Col 방식이 좋냐면,

    • 외부 파일과 연결할 때 일관성이 좋고
    • 중간에 열이 추가돼도 상대적으로 관리가 편하고
    • 여러 파일을 합쳐서 다룰 때 더 안전하다

    실무에서는 처음부터 Col1, Col2 습관을 들이는 것을 추천한다.

    4. 사무직 실무에서 가장 많이 쓰는 패턴들

    이제부터는 진짜 많이 쓰는 유형만 정리해 보겠다.

    4-1. 특정 날짜만 조회하기

    예를 들어 특정 일자의 전표만 보고 싶다면:

    =QUERY(IMPORTRANGE(C10, D10),
    "SELECT Col1,Col2,Col3,Col4,Col5
     WHERE Col2='외환차손'
       AND Col3 = DATE '2022-01-18'", 1)

    기간 조건으로 바꾸면 이렇게 된다.

    =QUERY(A1:N,
    "SELECT *
     WHERE C >= DATE '2022-04-01'
       AND C <= DATE '2022-12-31'", 1)

    이건 실무에서 이런 상황에 좋다.

    • 월별 매출 집계
    • 특정 기간의 회계 자료 조회
    • 특정 날짜의 거래내역 점검
    • 분기별 보고서용 데이터 추출

    날짜 조건은 반드시 DATE 'yyyy-mm-dd' 형식으로 쓰는 것이 핵심이다.

    4-2. 공백 행 제외하기

    데이터 범위를 넉넉하게 잡아 두면 빈 행이 같이 끌려오는 경우가 많다.

    그럴 때는:

    =QUERY(IMPORTRANGE(C10, D10),
    "SELECT * WHERE Col1 IS NOT NULL", 1)

    반대로 공백만 찾고 싶으면:

    =QUERY(A1:E, "SELECT * WHERE C IS NULL", 1)

    이건 이런 상황에 유용하다.

    • 입력 누락 데이터 찾기
    • 실제 값이 있는 행만 보고 싶을 때
    • 정리되지 않은 원본 데이터에서 빈 줄 제거할 때

    4-3. 특정 문자가 포함된 행만 보기

    예를 들어 “월별”이라는 단어가 들어간 항목만 찾고 싶다면:

    =QUERY(A1:F, "SELECT B WHERE B CONTAINS '월별'", 1)

    포함하지 않는 항목만 보고 싶다면:

    =QUERY(A1:F, "SELECT B WHERE NOT B CONTAINS '재무제표'", 1)

    비슷하게 LIKE도 쓸 수 있다.

    =QUERY(A1:F, "SELECT B WHERE B LIKE '%월별%'", 1)

    이건 실무에서 다음처럼 쓴다.

    • 제목이나 비고에 특정 단어가 포함된 문서 찾기
    • 계정명/프로젝트명 일부만 알고 있을 때 필터링
    • 특정 패턴의 데이터만 분리할 때

    4-4. 숫자 조건으로 필터링하기

    예를 들어 50,000원 이상만 보고 싶다면:

    =QUERY(A1:F, "SELECT E WHERE E >= 50000", 1)

    범위 조건은 이렇게:

    =QUERY(A1:F, "SELECT E WHERE E >= 1000 AND E <= 10000", 1)

    특정 값 제외는 이렇게:

    =QUERY(A1:F, "SELECT E WHERE E != 50000", 1)

    이건 정말 자주 쓴다.

    • 일정 금액 이상 지출만 보기
    • 발주 수량이 일정 기준 이하인 것만 보기
    • 수치 이상치 점검하기
    • KPI 기준 미달 항목만 뽑기

    4-5. 정렬까지 한 번에 하기

    조건으로 걸러온 뒤 정렬까지 같이 하고 싶다면:

    =QUERY(A1:Z, "SELECT * ORDER BY Z ASC", 1)

    내림차순은:

    =QUERY(A1:Z, "SELECT * ORDER BY Z DESC", 1)

    실무에서는 대부분
    “조건으로 걸러서, 최신순 또는 금액 큰 순으로 정렬”
    이 패턴이 많다.

    예를 들어,

    • 미수금 큰 순
    • 매출 높은 순
    • 최근 업데이트 순
    • 재고 부족 우선순위 정렬

    같은 보고서를 만들 때 좋다.

    5. 여러 파일 데이터를 한 번에 합치고 싶다면

    이게 QUERY + IMPORTRANGE 조합의 진짜 강점이다.

    예를 들어 서로 다른 두 파일의 데이터를 합쳐서 조회하려면:

    =QUERY(
     {IMPORTRANGE(C10,D10); IMPORTRANGE(C11,D11)},
     "SELECT Col1,Col2,Col3,Col4,Col6,Col8,Col9
      WHERE Col2='잡손실' AND Col4<>'[ 전 월 이 월 ]'", 1)

    중괄호 { } 안에서 ;로 세로 결합을 한 것이다.

    즉,

    • 상반기 파일 + 하반기 파일
    • 본사 파일 + 지점 파일
    • 부서별 파일 여러 개
    • 월별 파일 누적 취합

    같은 작업을 수작업 없이 처리할 수 있다.

    이 기능 하나만 잘 써도
    “매달 파일 12개 열어서 붙여넣기” 같은 업무가 꽤 줄어든다.

    6. 여러 항목을 한 번에 필터링하려면 MATCHES가 좋다

    예를 들어 특정 담당자만 보고 싶다면:

    =QUERY(A1:F,
    "SELECT Col1, Col5
     WHERE Col1 MATCHES '김과장|이대리|박사원'", 1)

    이건 OR 조건을 많이 붙여야 할 때 아주 편하다.

    또, 포함 조건을 더 유연하게 주고 싶다면 정규표현식을 쓸 수 있다.

    =QUERY(B92:G99,
    "SELECT Col1,Col3
     WHERE Col1 MATCHES '.*(노란|빨간|파란).*'", 1)

    의미는 이렇다.

    • 앞뒤에 어떤 글자가 와도 상관없고
    • ‘노란’, ‘빨간’, ‘파란’ 중 하나가 포함된 값만 찾아라

    실무에서는 이런 데 쓸 수 있다.

    • 특정 담당자 여러 명 묶어서 보기
    • 특정 키워드군이 들어간 항목만 추출
    • 텍스트 패턴으로 세밀하게 필터링하기

    7. 합계와 요약 보고서는 GROUP BY가 핵심이다

    예를 들어 분류별 합계를 보고 싶다면:

    =QUERY(B92:G99,
    "SELECT Col2, SUM(Col3)+SUM(Col4)+SUM(Col5)
     WHERE Col1 IS NOT NULL
     GROUP BY Col2
     LABEL SUM(Col3)+SUM(Col4)+SUM(Col5) '1분기'", 1)

    이 수식은 쉽게 말하면,

    • 분류별로 묶고
    • 1월, 2월, 3월 값을 더해서
    • 1분기 합계를 보여주는 것이다

    즉, 피벗테이블 비슷한 요약표를 수식으로 바로 만들 수 있다.

    이건 실무에서 엄청 좋다.

    • 부서별 비용 합계
    • 카테고리별 매출 요약
    • 담당자별 건수 요약
    • 분기별 누적 실적 정리

    8. 피벗 형태 보고서도 QUERY로 가능하다

    예를 들어 이런 식이다.

    =QUERY($B$50:$F$57,
    "SELECT C, B, SUM(F)
     GROUP BY C, B
     PIVOT E", 1)

    이 수식은
    행/열 방향을 바꿔서 요약표처럼 보여준다.

    즉, 피벗테이블을 새로 만들지 않아도
    수식만으로 보고서 모양을 고정할 수 있다.

    특히 좋은 점은,

    • 원본이 바뀌면 자동 반영되고
    • 보고서 시트 형식이 깨지지 않고
    • 다른 수식과 연결하기 쉽다

    정기 보고서 만들 때 꽤 유용하다.

    9. LABEL, FORMAT, OFFSET까지 쓰면 보고서 완성도가 올라간다

    예를 들어:

    =QUERY(A:AG,
    "SELECT Col2, Col3, Col14
     WHERE Col2 IS NOT NULL
     ORDER BY Col14 DESC
     LABEL Col14 '현재 수량'
     FORMAT Col2 'yy.MM.dd'
     OFFSET 1", 1)

    여기서 할 수 있는 일은:

    • LABEL : 결과 열 제목 바꾸기
    • FORMAT : 날짜/숫자 표시 형식 지정
    • OFFSET : 위쪽 몇 줄 건너뛰기

    즉, 단순 조회를 넘어서
    바로 보고서에 붙여도 되는 형태로 만들 수 있다.

    이런 디테일이 실무에서는 꽤 중요하다.

    10. 실무자가 특히 기억하면 좋은 팁

    ① 외부 파일이면 Col 방식으로 쓰자

    A,B,C보다 Col1, Col2가 관리가 편하다.

    ② 날짜 조건은 DATE 형식을 정확히 쓰자

    DATE '2022-01-18'

    이 형식이 가장 안전하다.

    ③ 빈 행 제거는 IS NOT NULL이 거의 필수다

    데이터 범위를 넓게 잡는 습관이 있다면 특히 유용하다.

    ④ 여러 명, 여러 조건은 MATCHES가 편하다

    OR를 길게 쓰는 것보다 훨씬 깔끔하다.

    ⑤ 여러 파일 취합은 { } 와 IMPORTRANGE 조합으로 끝난다

    월별/부서별 파일 취합 업무에 특히 강하다.

    11. 이런 분들에게 특히 추천한다

    이 조합은 아래 같은 사무직 직장인에게 특히 좋다.

    • 회계/재무 자료를 자주 취합하는 분
    • 영업 실적 보고서를 자주 만드는 분
    • 인사/총무 데이터 정리를 반복하는 분
    • 여러 부서 파일을 합쳐야 하는 분
    • 매번 필터, 복사, 정렬을 반복하는 분

    즉,
    **“반복 조회 + 반복 정리 + 반복 보고”**가 많은 사람일수록
    효과가 크다.

    마무리

    엑셀에서는
    파일 열고 → 복사하고 → 붙여넣고 → 필터 걸고 → 피벗 돌리고 → 다시 정리하는 일이 익숙하다.

    그런데 구글 스프레드시트에서는
    IMPORTRANGE + QUERY 조합만 익혀도
    이 과정을 상당 부분 자동화할 수 있다.

    처음엔 문법이 낯설어 보여도,
    실제로 많이 쓰는 건 아래 몇 가지 패턴이 전부다.

    • 날짜 조건
    • 공백 제외
    • 특정 문자 포함
    • 숫자 조건
    • 정렬
    • 그룹화
    • 다중 파일 결합
    • MATCHES
    • PIVOT

    이 정도만 익혀도
    단순 반복 보고서 작업 속도가 꽤 빨라진다.

    결론은 간단하다.

    복붙으로 버티는 단계는 이제 졸업하고,
    QUERY로 한 줄 보고서를 만드는 단계로 가면 된다.

  • 칼퇴 보장. 구글 시트 QUERY 함수 실전 압축본

    나는 경영전략실장이다.

    매월 초만 되면 각 부서, 각 담당자가 쪼개서 관리하는 수십 개의 엑셀 파일을 하나로 합치는 게 내 주요 업무 중 하나다. 애들한테 취합하라고 시킬까 했는데, 안 그래도 바쁜 팀원들이 속으로 나를 꼰대라고 욕할 게 뻔하다.

    그래서 파일 하나 열지 않고, 자동으로 남의 부서 데이터를 내 입맛대로 가져오고 가공하는 방법을 세팅했다. 처음엔 복잡해 보이지만, 오늘 정리한 구글 시트 QUERY 함수 패턴만 복사해서 붙여 넣으면 지긋지긋한 데이터 취합 노가다는 영원히 끝이다.


    1. 남의 파일 데이터 싹쓸이 (IMPORTRANGE 결합)

    다른 시트나 파일에 있는 데이터를 내 시트로 불러오면서 입맛대로 필터링하고 싶다면 IMPORTRANGE와 QUERY를 결합하면 된다.

    이때 가장 중요한 규칙 하나. 파일 외부에서 데이터를 가져올 때는 A, B, C 열 이름 대신 Col1, Col2, Col3 형식으로 불러야 한다.

    • 기본 형태: =QUERY(IMPORTRANGE("구글시트URL", "시트명!A:Z"), "SELECT Col1, Col2, Col5")(해석: 저 파일에서 1번째, 2번째, 5번째 열만 내 시트로 가져와라)

    파일 두 개를 위아래로 이어 붙이고 싶다면 배열 {}을 쓰면 된다.

    =QUERY({IMPORTRANGE("URL1","범위"); IMPORTRANGE("URL2","범위")}, "SELECT *")


    2. 귀찮은 조건 컷어웨이 (WHERE 조건절)

    데이터를 가져왔으면 불필요한 건 걸러내야 한다. 엑셀에서 일일이 필터 걸고 삭제하던 짓을 수식 한 줄로 끝낼 수 있다.

    • 공백 지우기: 데이터 중간에 텅 빈 줄이 보기 싫다면 IS NOT NULL을 쓴다."SELECT * WHERE Col1 IS NOT NULL"
    • 날짜로 자르기: 특정 기간의 데이터만 필요할 때는 반드시 DATE 키워드를 붙여야 에러가 안 난다."SELECT * WHERE Col3 >= DATE '2022-04-01' AND Col3 <= DATE '2022-12-31'"
    • 특정 단어 포함/제외: ‘사과’가 포함된 것만 찾거나, ‘2022년’으로 시작하는 걸 뺄 수 있다."SELECT * WHERE Col4 CONTAINS '사과'""SELECT * WHERE NOT Col2 starts with '2022년'"


    3. 수식으로 피벗테이블 돌리기 (GROUP BY & PIVOT)

    데이터 범위가 바뀔 때마다 피벗테이블 새로고침을 누르는 것도 귀찮다. QUERY 함수 안에는 피벗 기능이 내장되어 있어서, 원본이 바뀌면 집계표도 실시간으로 돌아간다.

    • 부서별(Col3) 매출(Col5) 합계 내기:"SELECT Col3, SUM(Col5) GROUP BY Col3"
    • 가로세로 피벗테이블 만들기: (세로는 부서, 가로는 월별)"SELECT Col3, SUM(Col5) GROUP BY Col3 PIVOT Col2"


    4. 엑셀엔 없는 궁극기 (MATCHES 정규표현식)

    이게 진짜 꿀팁이다. 체크박스 여러 개를 다중 선택한 것처럼 필터를 걸고 싶을 때가 있다. “노란 사과, 빨간 배, 파란 귤 중에서 하나라도 포함된 거 다 가져와” 같은 명령이다. 이때는 MATCHES를 쓴다.

    기호 | 는 **또는(OR)**을 의미한다. 이거 하나면 복잡한 조건도 한 방에 끝난다.

    • 정확히 일치하는 여러 단어 찾기:"SELECT Col1 WHERE Col1 MATCHES '노란 사과|빨간 배|파란 귤'"
    • 특정 단어가 하나라도 들어간 것 찾기 (앞뒤 글자 무시):"SELECT * WHERE Col1 MATCHES '.*(사과|배|바나나).*'"(해석: 단어 앞뒤로 .*를 붙이면 그 단어가 포함된 모든 문자를 긁어온다.)

    처음에는 SELECT니 WHERE니 하는 영어들이 외계어 같겠지만, 딱 세 번만 복사해서 내 데이터에 맞춰 열 번호(Col)만 바꿔 써보면 신세계가 열린다.

    팀원들 눈치 보면서 “이것 좀 합쳐줘”라고 부탁할 필요 없다. 커피 한 잔 마시면서 구글 시트 켜두고 우아하게 데이터를 뽑아보자. 내일 아침 경영진 회의 보고 자료는 이걸로 끝이다.

  • 구글 스프레드시트 QUERY 함수 완전정복 — IMPORTRANGE부터 PIVOT까지, 이 글 하나면 끝

    나는 회사에서 엑셀만 20년 넘게 쓰다가 구글 스프레드시트로 갈아탄 사람이다. 처음엔 “엑셀이 짱이지” 했는데, QUERY 함수를 알고 난 뒤로는 구글 스프레드시트를 주로 사용한다.

    ( 엑셀 추가기능 아별툴의 단축키가 그리워 가끔씩 엑셀을 키곤하지만, 구글 시트가 대세다.)

    VLOOKUP 하나 걸려고 수식 복사하고, 피벗테이블 만들려고 시트 옮기고, 필터 걸었다 풀었다 하는 그 삽질을 한 방에 해결해주는 녀석이기 때문이다.

    이 글에서는 QUERY 함수의 기본기부터 IMPORTRANGE로 다른 파일 데이터를 끌어오는 방법, 정규표현식 필터링, 피벗테이블 구현까지 실무에서 바로 쓸 수 있는 내용을 총정리한다.

    📌 먼저 알고 가자: QUERY 함수의 기본 구조

    =QUERY( 데이터_범위, "쿼리문", 헤더_수 )
    구성 요소설명예시
    데이터_범위데이터를 가져올 출처 (시트 및 범위)B4:F11
    “쿼리문”어떻게 가져올지 조건과 방법을 기술“SELECT B, SUM(F) GROUP BY B”
    헤더_수범위의 첫 몇 줄이 제목(헤더)인지 지정1

    ⚠️ 열 지정 방식: A,B,C vs Col1, Col2, Col3

    같은 파일 안에서는 SELECT A, B, C처럼 열 알파벳을 써도 동작한다. 하지만 처음부터 Col1, Col2, Col3 방식으로 쓰는 습관을 들이자. 이유는 두 가지다.

    1. IMPORTRANGE로 외부 파일을 참조하면 반드시 Col 방식을 써야 한다. (A,B,C 안 먹힌다.)
    2. 데이터 범위 앞에 열을 추가하면? A,B,C 방식은 쿼리문을 전부 수정해야 하지만, Col 방식은 수정할 필요가 없다.

    💡 Col은 대소문자를 구분한다. col1이나 COL1은 에러가 난다. 반드시 Col1으로 쓸 것.

    아래 설명에서는 이해를 돕기 위해 A,B,C 방식으로 설명하되, 실무에서는 Col 방식을 쓰자.

    🔎 1단계. 데이터를 ‘선택’하고 ‘걸러내기’ — SELECT와 WHERE

    SELECT: 어떤 열을 볼 것인가

    =QUERY(B4:F11, "SELECT *", 1)                     -- 모든 열 다 보기
=QUERY(B4:F11, "SELECT B, D", 1)                   -- B열과 D열만 보기
=QUERY(B4:F11, "SELECT B, C, (D / 100) * 7", 1)   -- 산술 연산도 가능

    합계를 구할 때는 SUM을 쓴다. 단, 반드시 GROUP BY와 함께 써야 한다.

    =QUERY(B4:F11, "SELECT SUM(E)", 1)                 -- E열 전체 합계
=QUERY(B4:F11, "SELECT C, SUM(F) GROUP BY C", 1)   -- 분류별 금액 합계

    WHERE: 어떤 조건으로 걸러낼 것인가

    WHERE는 QUERY의 핵심이다. 엑셀에서 자동필터 걸고, 복사하고, 다시 풀고 하던 그 작업을 한 줄로 해결한다.

    📅 날짜 조건

    -- 특정 일자
WHERE C = DATE '2022-04-01'

-- 기간 범위
WHERE C >= DATE '2022-04-01' AND C <= DATE '2022-12-31'

    날짜는 반드시 DATE '연-월-일' 형식으로 써야 한다. 작은따옴표로 감싸는 것을 잊지 말자.

    📝 문자 조건

    -- 정확히 일치
WHERE B = '외환차손'

-- 공백 제외 (값이 있는 행만)
WHERE C IS NOT NULL

-- 공백인 행만
WHERE C IS NULL

    🔢 숫자 조건

    WHERE E = 50000           -- 정확히 50,000인 것
WHERE E >= 1000           -- 1,000 이상
WHERE E >= 1000 AND E <= 10000   -- 1,000 ~ 10,000 사이
WHERE E != 50000          -- 50,000이 아닌 것

    🔎 2단계. 텍스트 필터링의 세계 — CONTAINS, LIKE, MATCHES

    여기서부터가 QUERY의 진짜 실력이 나오는 구간이다. 엑셀의 기본 필터로는 구현하기 힘든 세밀한 조건을 한 줄로 처리할 수 있다.

    CONTAINS: 특정 문자가 포함된 항목 찾기

    SELECT B WHERE B CONTAINS '월별'          -- '월별'이 포함된 항목
SELECT B WHERE NOT B CONTAINS '재무제표'  -- '재무제표'가 포함되지 않은 항목

    LIKE: 와일드카드(%) 패턴 매칭

    %는 “아무 글자나 0개 이상”이라는 의미다. 엑셀의 *와 같은 역할이다.

    SELECT B WHERE B LIKE '%월별%'         -- '월별'이 어디든 포함
SELECT B WHERE B LIKE 'DB%'            -- 'DB'로 시작
SELECT B WHERE B LIKE '%월별'          -- '월별'로 끝남
SELECT B WHERE NOT B LIKE '2022년%'    -- '2022년'으로 시작하지 않는 것

    starts with / ends with: 직관적인 표현

    LIKE 대신 좀 더 읽기 쉬운 표현도 있다.

    SELECT B WHERE B starts with 'DB'
SELECT B WHERE B ends with '월별'
SELECT B WHERE NOT B starts with '2022년'

    ⭐ MATCHES: 정규표현식으로 초정밀 필터링

    MATCHES는 QUERY 함수의 최종 병기다. 엑셀에서는 VBA를 동원해야 가능하던 정규표현식 필터링을 수식 한 줄로 할 수 있다.

    기본 사용법 — 여러 값 중 하나와 일치하는 항목 찾기

    -- 김과장, 이대리, 박사원의 데이터만 가져오기
SELECT Col1, Col5 WHERE Col1 MATCHES '김과장|이대리|박사원'

    |는 “또는(OR)”을 의미한다. 이게 얼마나 강력하냐면, 엑셀에서 다중 필터 체크박스를 하나하나 클릭하던 그 작업이 파이프(|) 하나로 끝난다.

    정확히 일치 vs 포함 — 이 차이를 반드시 구분하자

    실무에서 가장 많이 실수하는 부분이다.

    목적패턴예시설명
    정확히 일치'^단어$'MATCHES '^사과$'“사과”만 찾음. “빨간 사과”는 안 걸림
    포함'.*단어.*'MATCHES '.*사과.*'“사과”가 어디에 들어있든 다 찾음
    ~로 시작'^단어.*'MATCHES '^서울.*'“서울”로 시작하는 모든 것
    ~로 끝남'.*단어$'MATCHES '.*동$'“동”으로 끝나는 모든 것

    실무에서 자주 쓰는 정규표현식 패턴 총정리

    필터링 유형패턴예시설명
    특정 단어로 시작^단어.*'^서울.*'서울특별시, 서울역 등
    특정 단어로 끝남.*단어$'.*동$'명동, 여의도동 등
    정확히 일치^단어$'^사과$'오직 “사과”만
    여러 단어 중 하나로 시작^(A|B).*'^(김|이|박).*'김철수, 이영희, 박사원 등
    특정 글자 수만^.{숫자}$'^.{3}$'정확히 3글자 (바나나, 토마토)
    숫자 포함 항목.*[0-9].*'.*[0-9].*'아이폰15, 갤럭시S24 등
    한글로만 된 항목^[가-힣]+$'^[가-힣]+$'숫자·영어·기호 없이 한글만
    특정 단어 제외NOT MATCHESNOT ... MATCHES '.*품절.*'“품절” 포함 행 제외

    💡 정규표현식 기호 해독기 — 이 5개만 알면 된다

    기호의미
    ^문장의 시작
    $문장의 끝
    .아무 문자 1개 (줄바꿈 제외)
    *바로 앞 문자가 0개 이상 반복
    |또는 (OR)

    이 다섯 개를 조합한 '.*( 노란|빨강|파란).*'는 **”앞뒤로 무슨 글자가 있든 ‘노란’이나 ‘빨강’이나 ‘파란’이 들어간 모든 텍스트를 찾아라”**라는 뜻이 된다.

    ⚠️ 주의: MATCHES 조건의 텍스트는 반드시 **작은따옴표(‘ ‘)**로 감싸야 한다.

    📊 3단계. 정렬과 그룹화 — ORDER BY, GROUP BY

    ORDER BY: 결과 정렬

    SELECT * ORDER BY C ASC       -- C열 기준 오름차순 (기본값)
SELECT * ORDER BY C DESC      -- C열 기준 내림차순

    GROUP BY: 그룹별 집계

    분류별 합계, 개수 등을 구할 때 쓴다. 엑셀의 피벗테이블을 수식 한 줄로 만드는 셈이다.

    -- 분류별 항목 수와 금액 합계
=QUERY(B50:F57, "SELECT C, COUNT(B), SUM(F) GROUP BY C", 1)

    여러 열의 합계를 하나로 묶기 — 예를 들어 1월~3월 합계를 “1분기”로 묶고 싶다면:

    =QUERY(B92:G99, "SELECT C, SUM(D)+SUM(E)+SUM(F) WHERE B IS NOT NULL GROUP BY C LABEL SUM(D)+SUM(E)+SUM(F) '1분기'", 1)

    💡 LABEL은 결과 헤더의 이름을 바꿔주는 명령어다. 2개 이상 열의 레이블을 바꾸려면 콤마(,)로 구분한다.

    🔗 4단계. 다른 파일 데이터 가져오기 — IMPORTRANGE + QUERY 콤보

    여기서부터가 구글 스프레드시트의 엑셀 대비 압도적 장점이다. 다른 파일에 있는 데이터를 실시간으로 참조해서 원하는 조건으로 가공할 수 있다.

    IMPORTRANGE 기본 사용법

    =IMPORTRANGE("구글시트URL", "시트명!셀범위")

    예시:

    =IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123...", "BS!A1:E")

    IMPORTRANGE + QUERY 콤보

    다른 파일에서 데이터를 가져오면서 동시에 조건 필터링까지 하는 조합이다. 이때 열 지정은 반드시 Col1, Col2, Col3 방식을 써야 한다.

    -- 다른 파일에서 '외환차손' 데이터만 가져오기
=QUERY(IMPORTRANGE(C10, D10), "SELECT Col1,Col2,Col3,Col4,Col5 WHERE Col2='외환차손' AND Col3 = DATE '2022-01-18'", 1)

    헤더 없이 데이터만 가져오기

    쿼리문의 마지막 인수를 0으로 설정하면 헤더를 제외하고 데이터만 가져온다.

    =QUERY(IMPORTRANGE(C11, D11), "SELECT Col1,Col2,Col3,Col4,Col5 WHERE Col2='잡손실'", 0)

    2개 이상의 파일 데이터를 합쳐서 조회하기

    중괄호 { }와 세미콜론 ;으로 여러 IMPORTRANGE를 하나로 합칠 수 있다.

    =QUERY(
  {IMPORTRANGE(C10, D10); IMPORTRANGE(C11, D11)},
  "SELECT Col1,Col2,Col3,Col4,Col6,Col8,Col9 WHERE Col2='잡손실' AND Col4<>'[ 전 월 이 월 ]'",
  1
)

    세미콜론(;)은 세로로 합치는 것이다. 즉, 두 파일의 데이터를 위아래로 쌓는다.

    📐 5단계. QUERY로 피벗테이블 만들기 — PIVOT

    엑셀에서 피벗테이블 만들 때 마우스로 필드를 드래그하고, 값 영역 설정하고, 필터 걸고… 이 작업을 수식 한 줄로 끝내는 방법이다.

    GROUP BY로 간단한 피벗 구현

    -- 분류별, 구분별 금액 합계
=QUERY(B50:F57, "SELECT C, B, SUM(F) GROUP BY C, B", 1)

    PIVOT으로 행/열 전환 피벗 구현

    PIVOT을 쓰면 특정 열의 값을 열 헤더로 변환할 수 있다. 엑셀 피벗테이블의 “열 영역”에 해당한다.

    -- 단가별로 열을 나눠서 피벗테이블 생성
=QUERY(B50:F57, "SELECT C, B, SUM(F) GROUP BY C, B PIVOT E", 1)

    행과 열을 뒤집고 싶으면 TRANSPOSE를 감싸면 된다.

    =TRANSPOSE(QUERY(B50:F57, "SELECT E, SUM(F) GROUP BY E PIVOT C, B", 1))

    💡 참고: QUERY 피벗으로는 “1분기 합산”처럼 여러 열을 묶어서 계산하는 것이 안 된다. 이런 경우에는 위에서 본 SUM(D)+SUM(E)+SUM(F) 방식을 쓰자.

    🎨 6단계. 마무리 다듬기 — LABEL, FORMAT, OFFSET

    LABEL: 결과 헤더 이름 바꾸기

    LABEL Col14 '현재 수량'

    여러 열의 레이블을 한 번에 바꾸려면 콤마로 구분한다.

    LABEL '아마란스 재고' '출처', Col32 '재고 수량'

    FORMAT: 날짜·숫자 표시 형식 지정

    FORMAT Col2 'yy.MM.dd'

    OFFSET: 첫 N줄 건너뛰기

    헤더 다음의 특정 행을 건너뛰고 싶을 때 사용한다.

    OFFSET 1    -- 첫 번째 데이터 행 건너뛰기

    실전 조합 예시:

    =QUERY(
  'DB창고별재고현황'!$A:$AG,
  "SELECT Col2, Col3, Col14
   WHERE Col2 IS NOT NULL
   ORDER BY Col14 DESC
   LABEL Col14 '현재 수량'
   FORMAT Col2 'yy.MM.dd'
   OFFSET 1",
  1
)

    🧩 7단계. 실전 응용 — 배열 수식과 조합하기

    접두사 붙여서 가져오기

    배열 수식과 QUERY를 결합하면 데이터를 가공하면서 가져올 수 있다.

    원본 데이터가 이런 구조라고 하자:

    구분금액
    감자5,000
    사과3,000

    아래 수식을 쓰면 구분 앞에 “🔹썩은 : “이라는 접두사를 붙여서 가져올 수 있다.

    텍스트로 저장된 날짜를 변환하기

    FORMAT 명령어를 활용하면 QUERY 결과의 날짜 표시 형식을 원하는 대로 바꿀 수 있다.

    ✅ 최종 정리: QUERY 쿼리문 핵심 명령어 치트시트

    명령어역할사용 예시
    SELECT열 선택SELECT A, B, C
    WHERE조건 필터링WHERE B = '사과'
    ORDER BY정렬ORDER BY C DESC
    GROUP BY그룹화GROUP BY B
    PIVOT열 헤더 전환PIVOT E
    LABEL헤더명 변경LABEL Col3 '합계'
    FORMAT표시 형식FORMAT Col2 'yy.MM.dd'
    LIMIT결과 행 수 제한LIMIT 10
    OFFSET첫 N행 건너뛰기OFFSET 1
    CONTAINS텍스트 포함 여부WHERE B CONTAINS '월별'
    LIKE와일드카드 매칭WHERE B LIKE '%월별%'
    MATCHES정규표현식 매칭WHERE B MATCHES '사과|배|귤'
    IS NOT NULL빈 값 제외WHERE C IS NOT NULL
    SUM / COUNT / AVG집계 함수SELECT SUM(E)

    마치며

    QUERY 함수를 처음 접하면 SQL 같은 문법이 낯설 수 있다. 하지만 SELECT, WHERE, ORDER BY 이 세 가지만 익히면 실무 보고서의 80%는 커버된다.

    거기에 MATCHES로 정규표현식 필터링, IMPORTRANGE로 다른 파일 연동, PIVOT으로 피벗테이블까지 더하면, 지금까지 엑셀에서 몇 시간씩 걸리던 작업이 수식 한 줄로 끝나는 경험을 하게 될 것이다.

    칼퇴의 비결은 결국 도구를 아는 것이다. 오늘부터 QUERY 함수를 하나씩 써 보자. 한 번 맛보면 VLOOKUP으로 돌아가지 못한다.

  • 구글 스프레드시트에서 QUERY함수 사용하기

    나는 예전에는 엑셀의 신이라고 불렸으나, 요즘은 구글 스프레드시트를 더 많이 쓰는 사람이다.

    구글 스프레드시트를 더 많이 사용하게 된 계기가 QUERY함수다.

    Query함수가 무엇인가?

    필요한 데이터만 뽑아내고 정렬하고 그룹화하고 가공까지 해주는 슈퍼맨 같은 함수다.

    쉽게 설명하자면,

    • VLOOKUP이 줄 서서 김밥 한 줄 사는 거라면,

    • QUERY는 고급 뷔페에 앉아서 내가 원하는 재료들로 셰프한테 주문해서
    새로운 요리를 만들어 달라고 하는 것이다.

    평소에 지시 받은 업무를 처리하는 방식을 보면 이해가 쉬울 것 같다.
    여러 단계를 거쳐야 해결되는 문제가 명령어 한 줄로 해결이 되고, 조건 업데이트도 쉽다.

    지시 받은 업무 🤯기존 방식 (야근 확정) 😭QUERY 마법 (칼퇴 보장) 🚀
    부서별/항목별 지출 합산(A팀 법인카드 내역 뽑기)내역 다운로드 ➔ 피벗테이블 생성 ➔ A팀만 페이지영역으로 넣고, 필터링‘=QUERY(데이터, “SELECT Col1, SUM(Col3) WHERE Col2 = ‘A팀’ GROUP BY Col1”, 1)
    내 업무 대시보드 만들기(담당자가 ‘나’인 진행 중 프로젝트)자동필터 걸고, 내 이름 필터링한 다음, ‘완료’가 아닌 애들만 필터링해서 새로운 시트에 복사해 넣기‘=QUERY(데이터, “SELECT Col1, Col3, Col5 WHERE Col2 = ‘김대리’ AND Col4 = ‘진행중’”, 1)
    부족한 재고 현황 파악(재고 10개 미만, 발주처 순 정렬)조건을 만족하는 계산 열을 추가하고, 피벗테이블 돌리고, 페이지 필드에서 조건 만족하는 값 고르고, 행 필드에 발주처 넣고, 값 필드에 재고 필드 넣어서 피벗테이블 완성‘=QUERY(데이터, “SELECT Col1, Col2 WHERE Col4 < 10 ORDER BY Col5”, 1)
    VIP 고객 집중 관리(이번 달 만료, 금액 큰 순 정렬)날짜 필터 설정 ➔ 내림차순 정렬 ➔ 시트 복사‘=QUERY(데이터, “SELECT Col1, Col2, Col4 WHERE Col3 >= date ‘2025-11-01’ ORDER BY Col4 DESC”, 1)
    특정 다수 실적 묶어 보기(김과장, 이대리 등 특정 인원만)VLOOKUP 무한 반복 or 다중 필터 체크박스 클릭‘=QUERY(데이터, “SELECT Col1, Col5 WHERE Col1 MATCHES ‘김과장|이대리|박사원’”, 1)


    Query함수 사용 방법

    QUERY함수의 사용법은 아래와 같다.

    문법 :  =QUERY(   데이터_범위   ,    "쿼리문"   ,    헤더_수   )
    구성 요소설명예시
    데이터_범위어디서 데이터를 가져올 건지, 그 출처 (원본 데이터 시트 및 범위)A1:F100
    “쿼리문”어떻게 가져올지 방법과 조건을 기술 → SQL 명령어 사용“select Col1, Col3 where Col2 > 100”
    헤더_수범위의 첫 몇 줄이 제목(헤더)인가?1 (대부분 헤더가 1줄 임)
    ※ 쿼리문에서 뽑아낼 열을 지정할 때, 같은 파일 안에서는 A,B,C처럼 열 이름을 써도 되나, 위에 있는 예제처럼 Col을 사용하는 것이 좋다. (Col은 대소문자를 구분한다. col이나 COL로 쓰지 않도록 하자.)
1) 나중에 IMPORTRANGE함수로 외부 데이터를 참조하면 무조건 Col으로 써야 한다. 
2) A,B,C로 하면 데이터 범위를 옮기거나 데이터 앞에 열을 추가할 경우 쿼리문을 다 바꿔줘야 해서 비효율적이다.
"select A, C where B > 100" → → → "select Col1, Col3 where Col2 > 100"

※ 아래 설명에서는 이해를 쉽게 하기 위해 A,B,C처럼 문자로 된 열 이름을 사용하겠다.

    자주 쓰는 ‘쿼리문’ 핵심 명령어 3가지

    아래 세가지만 알아도 웬만한 보고서는 해결이 된다.


    1. 데이터를 ‘선택’하고 ‘숨기기’: SELECT

    SELECT는 말 그대로 **어떤 열(Column)**을 보여줄지 정하는 명령어다.

    • SELECT * : 모든 열을 보여줘. (일단 펼쳐 놓고 볼 때 쓴다. 나중엔 필요한 것만 골라본다.)
    • SELECT A, C, F : A, C, F 열만 보여줘.
    • SELECT A, SUM(C) : A열을 기준으로 C열의 합계를 보여줘.
      (그룹화할 때 쓴다. WHERE절에 GROUP BY A를 넣어줘야 작동한다.)


    2. 조건에 맞는 데이터만 ‘거르기’: WHERE

    WHERE는 특정 조건에 맞는 행(Row)만 필터링할 때 쓴다. 맨날 수동 필터 돌리던 그 작업을 해준다.

    • WHERE D = ‘완료’ : D열의 값이 ‘완료’인 데이터만 가져온다. (문자는 작은따옴표(‘ ‘))
    • WHERE C IS NOT NULL : C열의 값 중 공백이 아닌 값을 가져온다. 데이터 범위를
      넉넉하게 설정 후 실제 값이 있는 행만 가져올 때 많이 사용한다.
    • WHERE E > 50000 : E열의 값이 50,000 초과인 데이터만 가져온다. (숫자는 따옴표 X)
    • WHERE F CONTAINS ‘[샘플]’ : F열의 값에 ‘[샘플]’이 포함된 데이터만 가져온다.
      WHERE F LIKE ‘%[샘플]%’ 처럼 쓸 수도 있다. %는 *의 의미다.
    • WHERE G >= date ‘2026-03-15’ : G열의 날짜가 2026-03-15보다 큰 날짜만 가져온다.


    3. 결과를 ‘정렬’해서 보기: ORDER BY

    ORDER BY는 데이터를 특정 열을 기준으로 오름차순(기본값) 또는 내림차순으로 정렬할 때 쓴다.

    • ORDER BY C : C열을 기준으로 오름차순 정렬.
    • ORDER BY D DESC : D열을 기준으로 내림차순 정렬. (DESC를 붙이면 내림차순!)

    쿼리 함수의 구글의 공식 도움말은 아래 URL에 있다.
    하지만, 지금은 보지 말자. 번역본이 그다지 감동이 없다.

    https://developers.google.com/chart/interactive/docs/querylanguage