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

나는 경영전략실장이다.

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

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

다른 시트나 파일에 있는 데이터를 내 시트로 불러오면서 입맛대로 필터링하고 싶다면 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 *")

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

공백 지우기: 데이터 중간에 텅 빈 줄이 보기 싫다면 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년'"

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

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

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

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

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

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

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

코멘트