나는 회사에서 엑셀만 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 방식으로 쓰는 습관을 들이자. 이유는 두 가지다.
- IMPORTRANGE로 외부 파일을 참조하면 반드시 Col 방식을 써야 한다. (A,B,C 안 먹힌다.)
- 데이터 범위 앞에 열을 추가하면? 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 MATCHES | NOT ... 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 |
아래 수식을 쓰면 구분 앞에 “🔹썩은 : “이라는 접두사를 붙여서 가져올 수 있다.
=ARRAYFORMULA(CONCAT("🔹썩은 : ",QUERY($J$70:$M$73,"SELECT K WHERE L > 2000",0)))텍스트로 저장된 날짜를 변환하기
FORMAT 명령어를 활용하면 QUERY 결과의 날짜 표시 형식을 원하는 대로 바꿀 수 있다.
SELECT Col2, Col3, Col14 WHERE Col2 IS NOT NULL ORDER BY Col14 DESC LABEL Col14 '현재 수량' FORMAT Col2 'yy.MM.dd' OFFSET 1✅ 최종 정리: 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으로 돌아가지 못한다.
