수식 한 줄로 끝내는 구글 시트 함수 6가지, 반복 작업은 이제 그만

예제 데이터 소개

먼저, 예제 데이터를 살펴봅니다. 시트 이름은 작업현황이고, 웹 디자인·개발 업무를 맡은 4명의 팀이 작업 한 건마다 한 줄씩 기록해 둔 표라고 가정하겠습니다.

첫째 행은 제목 행이고 데이터는 2행부터 시작합니다. 앞으로 소개하는 수식은 이후에도 작업이 계속 추가될 것을 감안해 A2:A50처럼 넉넉하게 범위를 잡았습니다. 실제 파일에서는 여러분의 데이터 범위에 맞게 바꿔 사용하시면 됩니다.

1. IMPORTRANGE — 다른 파일의 데이터 가져오기

여러 개의 스프레드시트를 다루다 보면, 어느 순간 이런 일을 반복하게 됩니다. 원본 파일을 열고, 범위를 복사하고, 다시 내 파일로 돌아와 붙여넣고, 다음 날 원본이 바뀌면 처음부터 같은 작업을 되풀이하는 것 말입니다. 파일이 두세 개일 때는 할 만하지만, 팀별 파일이 열 개가 넘어가면 상당한 부담이 됩니다.

IMPORTRANGE는 이런 번거로움을 없애 줍니다. 다른 구글 시트 파일의 특정 범위를 현재 시트로 연결해서 가져오고, 원본이 수정되면 가져온 데이터도 자동으로 따라서 바뀝니다. 복사·붙여넣기가 '그 순간의 정적인 도구'라면 IMPORTRANGE는 '살아 있는 도구'라고 할까요.

기본 구문은 다음과 같습니다.

=IMPORTRANGE(스프레드시트_URL, "시트이름!범위")

첫 번째 인수는 가져올 원본 파일의 주소이고, 두 번째 인수는 원본 파일의 어느 시트, 어느 범위를 가져올지 지정합니다. 두 인수 모두 반드시 큰따옴표로 감싸야 합니다. 예를 들어, 다른 팀이 관리하는 '작업현황' 시트의 A1:F50 범위를 가져오려면 다음과 같이 입력합니다.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/파일ID/", "작업현황!A1:F50")

처음 두 파일을 연결할 때는 셀에 #REF! 오류가 나타나면서 [액세스 허용] 버튼이 함께 표시됩니다. 보안을 위해 구글이 연결 여부를 확인하는 과정이므로, 버튼을 눌러 허용해 주시면 됩니다. 이 허용은 파일 쌍마다 한 번만 하면 되고, 이후에는 별다른 작업 없이 데이터가 계속 동기화됩니다.

2. ARRAYFORMULA — 수식 하나로 열 전체 한 번에 계산하기

수식을 한 칸에 입력하고, 아래로 끌어 내리고, 다음 날 새 데이터가 들어오면 다시 끌어 내리고, 그러다 어느 행에서 수식이 빠져 있는 것을 뒤늦게 발견하는 경험을 아마 한 번쯤은 해 보셨을 겁니다. 이런 일을 근본적으로 없애 주는 함수가 ARRAYFORMULA입니다.

ARRAYFORMULA는 하나의 수식을 지정한 범위 전체에 한꺼번에 적용합니다. 최신 엑셀의 동적 배열 개념과 비슷합니다. 수식을 아래로 복사해 둘 필요가 없고, 데이터에 새 행이 추가되면 자동으로 계산에 반영됩니다. 데이터가 계속 늘어나는 시트나, 여러 사람이 번갈아 입력하는 시트에서 특히 유용합니다.

구문은 단순합니다. 괄호 안에 일반 수식을 넣되, 셀 하나가 아니라 범위를 대상으로 쓰면 됩니다.

=ARRAYFORMULA(수식)

예제 데이터에서 작업별 비용(소요 시간 × 시간당 단가)을 G열에 구하고 싶다면, G2 셀에 다음과 같은 수식 하나만 입력하면 됩니다.

G2: =ARRAYFORMULA(IF(D2:D="","",D2:D*E2:E))

수식을 풀어서 설명하면 이렇습니다. D2:D와 E2:E는 2행부터 열 끝까지를 뜻합니다. 앞쪽의 IF(D2:D="","", ...)는 소요 시간이 비어 있는 행에서는 아무것도 표시하지 않도록 하는 장치입니다. 이 부분이 없으면 데이터가 없는 빈 행마다 0이 표시되어 지저분해집니다. 이렇게 입력하면 G2부터 아래로 계산 결과가 자동으로 펼쳐지고, 12행에 새 작업을 추가하는 즉시 G12에도 비용이 계산되어 나타납니다.

3. LET — 수식 안에서 계산 결과에 이름 붙여 재사용하기

수식이 길어질수록 같은 계산이 여러 번 반복되기 쉽습니다. 예를 들어 '소요 시간 × 단가'를 조건 판단에도 쓰고, 결과 표시에도 쓰다 보면 똑같은 식이 수식 안에 두세 번씩 등장하게 됩니다. 이런 수식은 읽기도 어렵고, 나중에 고치려면 같은 부분을 일일이 찾아 모두 바꿔야 합니다.

LET 함수는 이 문제를 해결해 줍니다. 계산 결과에 이름을 붙여 두고, 수식 안에서 그 이름을 필요한 만큼 가져다 쓰는 방식입니다. 프로그래밍의 '변수'와 같은 개념이라고 이해하시면 됩니다. 별도의 보조 열이나 보조 셀을 만들 필요 없이, 수식 하나 안에서 중간 계산을 정리할 수 있습니다.

구문은 처음 보면 복잡해 보이지만, '이름, 값'이 짝을 이루어 반복되고 마지막에 최종 계산식이 온다는 규칙만 기억하면 어렵지 않습니다.

=LET(이름1, 값1, [이름2, 값2, ...], 최종_계산식)

예제 데이터로 연습해 보겠습니다. 작업별 비용을 계산한 뒤, 50만 원을 넘으면 "고비용", 그렇지 않으면 "일반"으로 분류하고 싶다면 다음과 같이 작성할 수 있습니다.

=LET(비용, D2*E2, IF(비용>500000, "고비용", "일반"))

여기서 비용이 바로 붙여 준 이름입니다. 이 수식에서는 비용을 한 번밖에 쓰지 않았지만, 만약 비용을 세 군데에서 참조해야 하는 수식이라면 D2*E2를 세 번 쓰는 대신 이름 하나로 해결됩니다. 앞에서 배운 ARRAYFORMULA로 감싸면 열 전체에 한 번에 적용할 수도 있습니다.

=ARRAYFORMULA(LET(비용, D2:D*E2:E, IF(D2:D="","",IF(비용>500000,"고비용","일반"))))

이름에 연결하는 값이 꼭 계산식일 필요는 없습니다. 이름을 여러 개 만들 수도 있고, '세율'처럼 고정된 숫자를 이름으로 지정해 둘 수도 있습니다. 아래 수식은 전체 비용의 합계에 부가세 10%를 더해 청구 금액을 구합니다.

=LET(공급가액, SUM(G2:G50), 부가세율, 10%, 공급가액*(1+부가세율))

예제 데이터의 비용 합계는 3,650,000원이므로 이 수식의 결과는 4,015,000원입니다. 부가세율이 바뀌면 수식 중간의 10%만 고치면 되므로, 어느 숫자가 무엇을 뜻하는지도 한눈에 알 수 있습니다.

4. XLOOKUP — 열 번호를 세지 않고 원하는 값 찾기

조회 함수라고 하면 대부분 VLOOKUP을 먼저 떠올립니다. 하지만 VLOOKUP에는 오래된 불편함이 있었습니다. 찾을 값이 반드시 범위의 맨 왼쪽 열에 있어야 하고, 결과를 가져올 열의 위치를 '몇 번째 열'이라는 숫자로 직접 지정해야 합니다. 중간에 열을 하나 끼워 넣기만 해도 수식이 엉뚱한 값을 가져오는 사고가 생깁니다.

XLOOKUP은 이런 단점을 모두 덜어 낸 새로운 조회 함수입니다. '무엇을, 어디에서 찾아서, 어디의 값을 돌려줄지'를 각각 별도로 지정하기 때문에 열 번호를 셀 필요가 없습니다. 기본적으로 정확히 일치하는 값을 찾고, 왼쪽이든 오른쪽이든 방향에 상관없이 조회할 수 있습니다. 엑셀의 XLOOKUP 함수와 유사합니다.

필수 인수는 세 개입니다.

=XLOOKUP(찾을_값, 찾을_범위, 결과_범위, [못_찾았을_때_표시할_값])

예제 데이터에서 H2 셀에 작업 ID를 입력하면 I2 셀에 담당자가 자동으로 표시되도록 만들어 보겠습니다.

I2: =XLOOKUP(H2, A2:A50, C2:C50, "없는 작업 ID")

이 수식은 H2의 값을 A2:A50에서 찾은 다음, 같은 행에 있는 C2:C50의 값을 돌려줍니다. H2에 T-007을 입력하면 I2에는 박지훈이 표시됩니다. 존재하지 않는 ID를 입력했을 때는 #N/A 오류 대신 네 번째 인수로 지정한 "없는 작업 ID"가 나타나므로, 사용자가 무엇이 잘못되었는지 바로 알 수 있습니다. 이 네 번째 인수는 생략해도 되지만, 입력용 양식을 만들 때는 넣어 두는 편이 훨씬 친절합니다.

XLOOKUP의 진가는 VLOOKUP이 하지 못하던 '거꾸로 찾기'에서 드러납니다. 담당자 이름으로 그 사람의 첫 번째 작업 ID를 찾는다고 해 보겠습니다. 찾을 범위는 C열이고 결과 범위는 A열, 즉 결과가 왼쪽에 있는 상황입니다.

=XLOOKUP("박지훈", C2:C50, A2:A50)

VLOOKUP이라면 열 순서를 바꾸거나 다른 함수를 조합해야 했겠지만, XLOOKUP은 범위만 바꿔 적으면 됩니다. 결과는 박지훈 담당자의 첫 번째 작업인 T-004입니다.

5. FILTER — 조건에 맞는 데이터 모두 추출하기

XLOOKUP이 '하나만 정확히 찾는' 함수라면, FILTER는 '조건에 맞는 것을 전부 골라내는' 함수입니다. 필터 기능을 사용하는 방법도 있지만, 원본 표의 모양 자체를 바꿔 버리거나, 다른 사람이 같은 파일을 쓰고 있을 때 서로의 화면에 영향을 줄 수도 있습니다. FILTER 함수는 원본은 그대로 두고, 조건에 맞는 행만 다른 위치에 별도의 표로 만들어 줍니다.

=FILTER(범위, 조건1, [조건2, ...])

예제 데이터에서 아직 진행 중인 작업만 따로 뽑아 보겠습니다.

=FILTER(A2:F50, F2:F50="진행 중")

결과로 T-003(이벤트 배너 제작)과 T-005(회원가입 화면 개선) 두 행이 열 전체와 함께 펼쳐집니다. 원본에서 상태를 바꾸면 추출된 표도 바로 갱신됩니다.

조건을 여러 개 이어서 쓸 수 있는데, 이때는 모든 조건을 동시에 만족하는 행만 남습니다(AND 조건). 상태가 "완료"이면서 소요 시간이 10시간 이상인 작업을 찾는 수식은 다음과 같습니다.

=FILTER(A2:F50, F2:F50="완료", D2:D50>=10)

결과는 T-002(12시간)와 T-007(16시간) 두 건입니다.

6. QUERY — 필터링·정렬·집계를 수식 한 줄로 끝내기

지금까지 소개한 함수들이 저마다 한 가지 역할 수행에 충실했다면, QUERY는 그 모든 역할을 한 줄에서 처리하는 유능한 함수입니다. 시간을 아껴 주는 함수를 하나만 꼽으라면 주저 없이 QUERY를 선택할 정도입니다. 스프레드시트에서 SQL과 비슷한 문법으로 원하는 행을 걸러 내고, 정렬하고, 그룹별로 합계를 내는 일을 한 번에 할 수 있기 때문입니다.

예를 들어, "완료된 작업만 골라서, 담당자별로 소요 시간을 합산하라"는 요구를 받았다고 해 보겠습니다. 일반적으로는 보조 열을 만들고, 피벗 테이블을 삽입하고, 필터를 걸어야 하는 작업입니다. QUERY를 쓰면 이 모든 것이 수식 한 줄로 끝납니다.

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

쿼리문 안에서는 SELECT, WHERE, GROUP BY, PIVOT, ORDER BY, LIMIT, LABEL 같은 절을 사용할 수 있습니다. 지원되는 절의 전체 목록은 구글 공식 문서(구글 시각화 API 쿼리 언어 참조)에 정리되어 있으니, QUERY를 자주 쓰실 분은 즐겨찾기에 추가해 두시기 바랍니다.

=QUERY(A1:F50, "SELECT C, SUM(D) WHERE F='완료' GROUP BY C", 1)

수식의 각 부분은 다음과 같은 일을 합니다. SELECT C, SUM(D)는 담당자가 있는 C열과, 소요 시간인 D열의 합계를 가져오라는 뜻입니다. WHERE F='완료'는 상태가 '완료'인 행만 대상으로 삼는다는 조건이고, GROUP BY C는 담당자별로 묶어서 계산하라는 지시입니다. 마지막 인수 1은 제목 행이 한 줄이라는 뜻입니다. 결과는 김민준 8시간, 박지훈 24시간, 이서연 19시간, 최유진 5시간으로 나타납니다.

결과를 소요 시간이 많은 순으로 정렬하고, 완료 건수도 함께 보면서, 열 제목을 한글로 깔끔하게 바꾸려면 절을 이어서 추가하면 됩니다.

=QUERY(A1:F50, "SELECT C, SUM(D), COUNT(A) WHERE F='완료' GROUP BY C ORDER BY SUM(D) DESC LABEL SUM(D) '총 소요 시간', COUNT(A) '완료 건수'", 1)

이 수식은 박지훈(24시간, 2건), 이서연(19시간, 2건), 김민준(8시간, 2건), 최유진(5시간, 1건) 순서로 결과를 보여 줍니다. 수식이 매우 길지만, 절을 하나씩 읽어 가면 거의 일상 언어에 가까워서 익숙해지면 오히려 직관적입니다.

MVP TIP — 한눈에 보는 6가지 함수 비교

지금까지 소개한 함수들을 언제 사용하면 좋은지, 그리고 엑셀에서는 무엇으로 대신하는지 표로 정리했습니다. 두 프로그램을 함께 쓰시는 분이라면 대응 관계를 알아 두시면 도움이 됩니다.

함수	언제 쓰나요?	엑셀에서는?
IMPORTRANGE	다른 파일의 데이터를 복사 없이 연결해서 가져올 때	외부 통합 문서 링크, 파워 쿼리
ARRAYFORMULA	수식 하나로 열 전체를 계산하고, 새 행에도 자동 적용하고 싶을 때	동적 배열(엑셀 365는 자동), 표 기능의 자동 채우기
LET	같은 계산이 반복되는 긴 수식을 짧고 읽기 쉽게 만들 때	LET (엑셀 2021, 365)
XLOOKUP	열 번호 없이 값을 찾고, 왼쪽 방향 조회도 필요할 때	XLOOKUP (엑셀 2021, 365)
FILTER	조건에 맞는 행 전체를 별도 표로 뽑아낼 때	FILTER (엑셀 2021, 365)
QUERY	필터링·정렬·그룹 집계를 한 번에 처리할 때	대응 함수 없음 (피벗 테이블, 파워 쿼리)

자주 묻는 질문 (FAQ)

마치며

이상에서 소개한 6가지 함수를 익혀 두면 많은 시간을 절약할 수 있습니다. 다른 파일에서 데이터를 가져오고(IMPORTRANGE), 수식 하나로 열 전체를 계산하고(ARRAYFORMULA), 긴 수식을 정돈하고(LET), 원하는 값을 찾고(XLOOKUP), 조건에 맞는 행을 뽑고(FILTER), 마지막으로 한 줄에 집계하는(QUERY) 흐름을 따라가 보셨다면, 반복 작업의 상당 부분이 이미 자동화될 수 있다는 것을 느끼셨을 겁니다.

물론 처음에는 각 함수의 문법을 익히는 데 시간이 걸리고, 특히 QUERY와 LET은 낯설게 느껴질 수 있습니다. 하지만 한 번 몸에 익히면 지루하고 반복적인 작업을 스프레드시트가 알아서 처리해 주는 파일을 만들 수 있습니다. 이 중에서 지금 하고 있는 일에 가장 도움이 될 것 같은 함수 하나를 골라, 오늘 바로 실제 업무 파일에 적용해 보세요. 직접 써 보고 결과를 확인하는 것이 가장 빠른 학습법입니다.