아별팁(abltip)

[태그:] IMPORTRANGE

  • 구글 스프레드시트 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으로 돌아가지 못한다.