지루한 작업을 몇 시간씩 줄여줄 구글 시트 함수 5가지

들어가기 전에

구글 시트를 선호하는 사람들이 있는가 하면, 엑셀을 고집하는 사람들도 있습니다. 어떤 작업을 하느냐에 따라 어떤 도구가 다른 도구보다 더 적합할 수 있으므로 선택적으로 사용할 수 있습니다. 구글 시트 파일을 열 때마다 거의 매번 사용하는 다섯 가지 수식을 소개합니다.

권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가

chatgpt / iexceller

---

이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.

• 원문: 5 Google Sheets formulas that will save you hours of boring work
• URL: https://www.makeuseof.com/google-sheets-formulas-that-will-save-you-hours-of-boring-work/

---

1. QUERY

muo

 

시간을 절약해주는 수식이 단 하나뿐이라면, 바로 QUERY 함수를 사용한 수식일 것입니다. 이 강력한 함수를 사용하면 스프레드시트에서 SQL 스타일의 쿼리를 실행하여 데이터를 정확하게 필터링, 정렬 및 집계할 수 있습니다.

스프레드시트에서 완료된 모든 작업을 가져와 팀원별로 그룹화하고 합계를 계산해야 하는 경우, QUERY를 사용하면 간단한 구문으로 단 몇 분 만에 이 모든 작업을 수행할 수 있습니다.

=QUERY(data, query, [headers])

 

• data: 분석하려는 범위
• query: Google 시각화 API 쿼리 언어(기본적으로 SQL Lite)로 작성
• headers: 데이터 세트의 헤더 행 수(옵션)

 

이제 쿼리 인수 내에서 select, where, group by, pivot, limit 등의 절을 사용할 수 있습니다. Google은 Google Charts 웹사이트 에서 지원되는 절의 전체 목록을 제공하므로 , QUERY를 자주 사용할 계획이라면 이 웹사이트를 북마크해 두는 것이 좋습니다. 다음은 실제 데이터 세트를 사용한 예입니다.

=QUERY(A1:G50, "SELECT C, SUM(D) WHERE F = 'Done' GROUP BY C", 1)

 

여기서는 QUERY 함수를 사용하여 팀원 각자가 완료한 작업량을 확인하고 있습니다. 이 수식은 팀원 이름이 포함된 C열을 가져온 후, 소요 시간인 D열의 값을 합산하고, 상태가 “완료”인 행만 포함하도록 필터링한 다음, 결과를 팀원별로 그룹화합니다. 일반적으로 여러 보조 열과 피벗 테이블이 필요했던 작업을 이제 단 한 줄의 수식으로 처리할 수 있습니다.

 

2. IMPORTRANGE

muo

 

여러 스프레드시트를 사용할 때, 이 함수를 사용하지 않는 대안은 일반적으로 탭을 계속 전환하고 너무 많은 복사 붙여넣기 작업을 반복하는 것입니다. IMPORTRANGE 함수를 사용하면 이러한 번거로움을 없애고 다음 구문을 통해 다른 Google Sheets의 데이터를 현재 작업 중인 스프레드시트로 직접 가져올 수 있습니다.

=IMPORTRANGE(spreadsheet_url, range_string)

 

소스 스프레드시트 URL을 따옴표 안에 넣고, 가져올 시트 이름과 범위를 지정하면 됩니다. 일반적인 예시는 다음과 같습니다.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1tVlRJHVcv1uoG0kOZ6pmGysBMX3hZvdpgNE8VoQfG1U/", "Sheet1!A1:I10")

 

두 스프레드시트를 처음 연결할 때 Google에서 안전을 위해 ' 액세스 허용'을 클릭하라는 메시지가 표시될 수 있습니다.

 

muo

 

한 번만 권한을 부여하면 데이터가 자동으로 동기화되어 별도의 작업 없이 지속적으로 업데이트됩니다. 저는 여러 데이터 소스에서 데이터를 가져오는 대시보드를 구축하거나 여러 팀의 정보를 통합하는 마스터 보고서를 만들 때 이 기능이 매우 유용하다고 생각합니다.

 

3. ARRAYFORMULA

muo

 

ARRAYFORMULA는 한 번에 전체 열에 걸쳐 단일 계산을 적용하며 , 새 행을 추가해도 자동으로 계속 작동합니다. 시트를 최신 상태로 유지하기 위해 수백 또는 수천 개의 행에 걸쳐 수식을 드래그해야 했던 경험이 있다면 이 기능의 차이를 확연히 느낄 수 있을 것입니다.

구문 또한 매우 간단합니다.

=ARRAYFORMULA(array_formula)

 

함수 안에 넣는 계산식은 전체 범위에 적용되므로, 여러 표준 수식을 수동으로 반복 입력하지 않고도 열을 따라 확장하여 사용할 수 있습니다. 다음은 간단한 예시입니다.

=ARRAYFORMULA(D2:D*E2:E)

 

각 행마다 근무 시간에 시간당 임금을 곱하는 대신, 이 단일 수식을 사용하면 한 번에 전체 열을 채울 수 있습니다. 데이터에 새 행을 추가하는 즉시 계산이 자동으로 업데이트되므로, ARRAYFORMULA는 데이터가 지속적으로 증가하거나 여러 사람이 업데이트하는 스프레드시트에 특히 유용합니다.

 

4. LET

muo

 

복잡한 수식 내에서 중간 단계나 결과에 이름을 지정하기 위해 LET 함수를 사용합니다. 이렇게 하면 동일한 계산을 여러 번 반복할 필요가 없습니다. 수식에서 표현식을 한 번 정의하면 같은 수식 전체에서 해당 이름을 재사용할 수 있으므로 가독성과 유지 관리성이 향상됩니다. 언뜻 복잡해 보일 수 있지만 구문을 보면 훨씬 이해하기 쉽습니다.

=LET(name1, value_expression1, [name2, …], [value_expression2, …], formula_expression)

 

이 구조는 이름, 값, 이름, 값 등의 간단한 패턴을 따르며, 마지막 인수는 실제로 Sheets에서 실행하려는 계산입니다. 즉, 보조 셀이나 추가 열 없이 수식에 변수를 직접 만들 수 있습니다.

예를 들어, 총비용을 계산한 다음 1,000달러를 초과하는지 여부에 따라 분류하려면 다음과 같은 수식을 작성할 수 있습니다.

=LET(total_cost, D2:D*E2:E, IF(total_cost > 1000, "High Value", "Standard"))

 

LET 함수를 ARRAYFORMULA로 감싸서 계산이 자동으로 열을 따라 내려가도록 했습니다. 여기서 중요한 점은 하나의 표현식에 이름을 지정하고, 그 표현식의 값은 수식이라는 것입니다. 이렇게 하면 total_cost를 원하는 만큼 여러 번 참조해도 다시 계산할 필요가 없습니다.

=ARRAYFORMULA(LET(total_cost, D2:D*E2:E, IF(total_cost > 1000, "High Value", "Standard")))

 

표현식 이름을 하나만 지정할 수 있는 것은 아니며, 할당하는 값도 항상 수식일 필요는 없습니다. 이 예시에서처럼 상수나 텍스트 값에도 이름을 지정할 수 있습니다.

=LET(amount, SUM(H2:H50), tax, 12%, amount+tax)

 

muo

 

모든 경우에 구글 시트는 수식에 해당 식이 몇 번 반복해서 나타나거나 수식이 몇 개의 셀을 채우는지에 관계없이 각 명명된 식을 한 번만 평가합니다. 결과적으로 스프레드시트 실행 속도가 빨라지고 메모리 사용량이 줄어들며, 문제가 발생했을 때 디버깅이 훨씬 쉽고 빨라집니다.

5. XLOOKUP

muo

 

XLOOKUP은 VLOOKUP의 최신 버전이며, Google Sheets에서 조회 작업을 수행하는 가장 좋은 방법입니다. 기본 구문은 다음과 같습니다.

=XLOOKUP(search_key, lookup_range, result_range)

 

이 세 가지 인수만 있으면 Google Sheets에서 빠르고 안정적인 조회를 수행할 수 있습니다. 누락된 값 처리, 일치 유형 및 검색 방향을 위한 선택적 인수도 있지만, 이러한 인수 없이도 XLOOKUP 함수는 기존 조회 함수보다 이미 여러 면에서 개선되었습니다.

 

기존 함수는 왼쪽에서 오른쪽으로만 검색이 제한되었지만, XLOOKUP 함수는 어느 방향으로든 검색할 수 있고, 기본적으로 정확히 일치하는 값을 찾으며, 값을 찾을 수 없을 때는 N/A를 반환하여 유용한 오류 메시지를 제공합니다.

실제로 어떻게 작동하는지 알아보려면, H2 셀에 작업 ID를 입력하고 I2 셀에 해당 작업을 수행한 담당자가 자동으로 표시되도록 하려면 다음과 같은 수식을 사용하면 됩니다.

=XLOOKUP(H2,A2:A50,C2:C50,"Task Not Found")

 

이 수식은 A2~A50 셀에서 작업 ID(H2 셀에 지정됨)를 찾고 C2~C50 셀에서 해당 이름을 반환합니다. 열 개수를 세거나 데이터 구조를 변경할 필요가 없으므로 기존 조회 수식에서 흔히 발생하는 오류 원인을 제거할 수 있습니다. 스프레드시트에 검색할 내용, 검색 위치, 결과를 반환할 위치만 알려주면 스프레드시트가 정확하게 수행합니다

 

마치며

이 다섯 가지 수식을 활용하면 구글 시트가 여러분의 하루에서 불필요하게 뺏어가는 시간을 되찾을 수 있습니다. 사용법을 익히는 데에는 어느 정도 시간이 필요하며, 각 수식의 작동 방식을 이해하는 데에도 약간의 노력이 요구되지만 숙달하면 지루하고 반복적인 작업을 자동으로 처리해주는 스프레드시트를 만들 수 있습니다.

멤버십 안내 "최고" 가성비로 "최신" Office 활용 정보를 보내드립니다.

추천 도서 심혈을 기울여 집필한 책들입니다. 자신있게 추천합니다.

강의 & 개발 최고 전문가의 실무 노하우를 아낌 없이 전수해 드립니다.

---