들어가기 전에
엑셀이나 구글 시트 같은 스프레드시트 프로그램은 시간을 절약해 주는 도구지만 때로는 오히려 일을 더 복잡하게 만들기도 합니다. 수많은 메뉴를 뒤지는 대신, 힘든 작업을 대신해 주는 몇 가지 Google 스프레드시트 수식을 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: 8 Google Sheets Formulas That Make Work So Much Easier (Even If You Hate Spreadsheets
- URL: https://www.makeuseof.com/google-sheets-formulas-make-work-easier/
1. VLOOKUP
VLOOKUP은 범위의 첫 번째 열에서 특정 값을 검색하여 같은 행의 다른 열에서 해당 값을 반환합니다. 방대한 데이터세트를 직접 스크롤할 필요가 없는 조회 도구라고 할 수 있습니다. VLOOKUP의 구문은 다음과 같습니다(Excel의 VLOOKUP 함수와 거의 동일합니다).
VLOOKUP(검색 키, 범위, 인덱스, [정렬 여부])
search_key는 찾으려는 값이고, range는 검색할 테이블입니다. index 매개변수는 VLOOKUP에 반환할 데이터를 알려주고, is_sorted 는 데이터가 정렬되어 있는지 여부를 나타냅니다. [정렬 여부]를 FALSE로 설정하면 정확한 일치 항목이 표시되며, 이는 대부분의 조회 작업에서 원하는 결과입니다.
실제로 VLOOKUP이 작동하는 방식은 다음과 같습니다.
=VLOOKUP("Saniya", B2:J3000, 9, FALSE)
이 수식은 B열에서 "Saniya"를 검색하여 9번째 열에서 해당 값을 반환합니다. 직원 정보, 제품 정보 또는 표로 구성된 데이터를 찾으려는 경우에 적합합니다.
하지만 VLOOKUP 함수는 오른쪽으로만 검색한다는 한계가 있습니다. 검색 열의 왼쪽은 검색할 수 없습니다. 하지만 대부분의 일상적인 작업에서는 데이터 조회를 완벽하게 처리합니다.
2. SUMIF
SUMIF는 사용자가 설정한 특정 기준에 따라 범위의 값을 더합니다. 여러 범주의 합계를 직접 계산하는 대신, 이 수식은 복잡한 작업을 자동으로 처리하며 정확도도 높습니다.
기본 IF 함수는 조건에 따라 TRUE 또는 FALSE를 반환하는 반면, SUMIF 함수는 조건을 충족하는 데이터에 대해 계산을 수행하여 더욱 발전된 결과를 제공합니다. SUMIF 함수의 구문은 다음과 같습니다.
SUMIF(범위, 조건, [합계범위])
여기서 범위는 기준과 비교하려는 셀을 포함하고, 기준은 조건이며, sum_range는 더할 실제 값입니다. sum_range를 생략하면 SUMIF 함수는 범위 자체의 값을 더합니다. 이 함수는 텍스트, 숫자, 그리고 부분 일치를 나타내는 별표(*)와 같은 와일드카드에만 적용됩니다.
=SUMIF(V2:V3000, "<1000", Z2:Z3000)
이 예에서 수식은 V열에서 1000보다 작은 값을 확인한 다음 Z열에서 해당 금액을 합산합니다. 이 수식은 매출 총액, 비용 범주 또는 조건부 합산을 계산하는 데 유용합니다.
여러 조건을 결합할 수도 있지만, 그 정도의 복잡성을 위해서는 SUMIFS 함수가 필요합니다.
3. CONCATENATE
이 함수는 여러 셀의 텍스트를 하나의 셀로 결합합니다. 성과 이름을 병합하거나, 이메일 주소를 만들거나, 사용자 지정 레이블을 만들어야 할 때 수식에서 이 함수를 사용하여 텍스트 조작을 처리할 수 있습니다.
앰퍼샌드(&) 기호를 기본 연결의 단축 기호로 사용할 수도 있지만, Google 스프레드시트의 CONCATENATE 함수를 사용하면 복잡한 조합을 더 유연하게 처리할 수 있습니다. CONCATENATE 함수의 구문은 다음과 같습니다.
CONCATENATE(문자열1, [문자열2, ...])
여기서 '문자열1'은 첫 번째 텍스트 값이며, 문자열은 무제한으로 추가할 수 있습니다. 각 매개변수는 셀 참조, 따옴표로 묶인 실제 텍스트 또는 이 둘을 혼합한 형태일 수 있습니다. 공백은 자동으로 추가되지 않으므로 다음 예에서 볼 수 있듯이 별도의 매개변수로 수동으로 포함해야 합니다.
=CONCATENATE(B2, " ", C2)
이 수식은 B2 셀과 C2 셀의 값을 공백으로 구분하여 결합합니다. 전체 이름, 제품 코드, 서식이 지정된 주소 등을 만드는 데 적합합니다.
CONCATENATE는 매개변수를 많이 사용하면 사용하기 불편할 수 있지만, 텍스트 데이터를 병합하는 가장 안정적인 방법입니다. Excel에서도 CONCATENATE를 사용하여 여러 셀의 텍스트를 병합할 수 있습니다.
4. COUNTIF
COUNTIF 함수는 범위 내에서 특정 기준을 충족하는 셀의 개수를 계산합니다. 완료된 작업 개수나 특정 응답 개수처럼 특정 범주에 속하는 항목의 개수를 추적해야 할 때, 이 함수를 사용하면 수동으로 계산할 때 발생하는 오류를 방지할 수 있습니다.
설문조사 데이터, 재고 추적 및 성과 지표 분석에 효과적입니다. COUNTIF 함수의 구문은 다음과 같습니다.
COUNTIF(범위, 조건)
여기서 범위는 조사할 셀을 지정하고, 기준은 셀 개수를 세기 위해 충족해야 하는 조건을 정의합니다. 기준에는 텍스트, 숫자 또는 논리 연산자가 포함될 수 있습니다. 다음은 예시입니다.
=COUNTIF(K2:K3000, "활성")
이 수식은 K2:K3000 범위에서 "활성"이라는 텍스트가 포함된 셀의 개수를 계산합니다. Google 스프레드시트의 COUNTIF 함수를 사용하여 프로젝트 상태를 추적하고, 특정 제품 유형의 개수를 계산하고, 범주형 데이터를 분석할 수 있습니다.
COUNTIF는 부분 텍스트 일치를 위한 별표(*)와 단일 문자 대체를 위한 물음표(?)와 같은 와일드카드도 지원합니다.
">50" 또는 ">=100"과 같은 비교 연산자를 사용하여 숫자 범위를 계산할 수도 있습니다. 이 수식은 수동으로 계산하기 어려운 대용량 데이터 세트를 다룰 때 유용합니다.
5. 배열 수식
다음은 배열 수식(ARRAYFORMULA)입니다. 배열 수식은 단일 수식을 전체 셀 범위에 자동으로 적용합니다. 수백 행에 걸쳐 수식을 복사하는 대신, 한 번만 작성하면 나머지는 Google 스프레드시트가 처리합니다.
끊임없이 확장되는 대용량 데이터세트를 다룰 때 이 기능은 매우 유용합니다. 새 데이터가 나타날 때마다 수식을 아래로 끌어다 놓을 필요 없이, ARRAYFORMULA를 사용하면 모든 데이터가 자동으로 최신 상태로 유지됩니다. 기본 구문은 일반 수식을 래핑합니다.
=ARRAYFORMULA(여기에 수식을 입력하세요)
하지만 단일 셀이 아닌 전체 열이나 범위를 참조해야 합니다. 예를 들어, A2 대신 A2:A를 사용하면 2행부터 아래쪽으로 전체 열을 참조할 수 있습니다.
성과 이름을 결합한 실제 예는 다음과 같습니다.
=ARRAYFORMULA(B2:B & " " & C2:C)
이 수식은 B열과 C열의 모든 해당 쌍을 동시에 연결합니다. 결과는 데이터가 있는 모든 행에 즉시 표시됩니다. 또한, 새 행이 추가되면 자동으로 조정됩니다.
배열 수식에 대한 개념과 사용 방법이 처음에는 다소 낯설 수 있습니다만, 정기적으로 대량의 데이터를 다룬다면 학습 곡선을 따라가는 것이 분명 가치가 있습니다.
6. FILTER
이 함수는 사용자가 정의한 조건에 따라 데이터세트에서 특정 행을 추출합니다. 행을 수동으로 숨기지 않고 특정 직원, 프로젝트 또는 판매 기록만 확인해야 하는 경우, 이 수식을 사용하면 동적 하위 집합을 즉시 생성할 수 있습니다.
FILTER에서 가장 마음에 드는 점은 수동 필터링과 달리 소스 데이터가 변경되면 결과가 자동으로 업데이트된다는 것입니다. 실시간 대시보드와 보고서에 유용합니다. 구문은 다음과 같은 패턴을 따릅니다.
FILTER(범위, 조건1, [조건2], ...)
범위에는 데이터가 포함되고, 조건1은 첫 번째 기준을 지정합니다. 아래 예와 같이 여러 조건을 중첩하여 더욱 정밀한 필터링을 수행할 수 있습니다.
=FILTER(D2:D3000, R2:R3000="Engineers")
이 수식은 직원 데이터베이스에서 엔지니어링 부서 직원만 표시합니다. 따라서 관련 없는 레코드를 스크롤하지 않고도 필요한 부분 집합만 정확하게 얻을 수 있습니다
FILTER는 텍스트와 숫자 기준을 똑같이 잘 처리하므로 프로젝트 추적, 비용 분석, 클라이언트 관리에 특히 효과적입니다.
이 함수는 보고서와 회의를 위해 다양한 데이터 하위 집합을 끊임없이 가져올 때 수작업에 소요되는 시간을 크게 줄여주는 수식 중 하나입니다.
7. QUERY
QUERY는 SQL과 유사한 언어를 사용하여 Google 스프레드시트에 데이터베이스 방식의 검색 기능을 제공합니다. 처음에는 어렵게 들릴 수 있지만, 이 Google 스프레드시트 기능은 매주 몇 시간을 절약해 줍니다 . 기본 사항을 이해하면 간단하고, 사무실 데이터 분석에 강력한 기능을 제공합니다.
필터링, 정렬, 그룹화를 하나의 수식으로 통합합니다. 여러 함수를 사용하는 대신, 하나의 명령으로 필요한 정보를 정확하게 추출할 수 있습니다. 기본 구문은 다음과 같습니다.
QUERY(데이터, 쿼리, [헤더])
데이터 매개변수에는 범위가 포함되고, 쿼리에는 SQL 유사 명령을 따옴표로 묶고, 헤더에는 포함할 헤더 행의 개수가 지정됩니다.
다음 예에서 QUERY는 직원 성과 데이터를 분석합니다.
=QUERY(A1:AB3000, "SELECT D, Q, AB ORDER BY AB DESC")
이 수식은 직원 이름(D열), 부서(Q열), 성과 점수(AB열)를 검색한 다음 성과 점수를 내림차순으로 정렬합니다. 즉, Excel 이나 Google 스프레드시트의 피벗 테이블을 사용해야 하는 사용자 지정 보고서를 만드는 것입니다.
QUERY는 텍스트 검색, 날짜 범위 및 수학 연산을 처리하므로 판매 분석, 프로젝트 추적 및 예산 보고에 유용합니다.
8. IMPORTRANGE
이 함수는 다른 Google 스프레드시트 파일의 데이터를 현재 스프레드시트로 직접 가져옵니다. 여러 프로젝트 파일, 예산 추적기 또는 부서별 보고서를 관리할 때 IMPORTNAGE를 사용하면 복사-붙여넣기 작업을 할 필요가 없습니다.
파일 간의 실시간 연결을 유지합니다. 따라서 동료가 마스터 판매 스프레드시트를 업데이트하면 대시보드에 변경 사항이 자동으로 반영되므로 수동 업데이트가 필요하지 않습니다. 구문에는 두 가지 핵심 정보가 필요합니다.
IMPORTRANGE("spreadsheet_url", "범위")
spreadsheet_url은 원본 파일의 전체 웹 주소이고, range는 "Sheet1!A1:C10"과 같은 표준 표기법을 사용하여 가져올 셀을 정확하게 지정합니다. 다음은 예시입니다.
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/abc123", "B2:AD50")
이 수식은 다른 부서 파일의 직원 데이터를 통합 보고서로 직접 가져옵니다. 하지만 Google 스프레드시트에서 파일을 연결하라는 메시지가 표시되므로 처음에는 권한을 부여해야 합니다.
IMPORTRANGE는 다른 Google 스프레드시트 파일에서 데이터를 가져오는 가장 쉬운 방법 중 하나로, 임원 대시보드 생성, 팀 보고서 통합, 중앙 집중식 데이터 뷰 관리에 유용합니다. 하지만 IMPORTRANGE 수식을 너무 많이 사용하면 스프레드시트 속도가 느려질 수 있다는 점을 명심하세요.
마치며
이상에서 소개한 8가지 수식은 스프레드시트 작업을 훨씬 더 쉽게 만들어 줍니다. 배우는 데 시간이 걸리지만, 꾸준히 사용하다 보면 시간 절약의 효과를 확실히 느낄 수 있으며, 그만한 가치가 있습니다.
'IT Info' 카테고리의 다른 글
| 챗GPT 활용도를 10배 높이는 5단계 '메타 프롬프트' (0) | 2025.12.01 |
|---|---|
| 중요한 내용을 잃지 않고 Windows 11에서 추가 공간을 확보하는 방법 (0) | 2025.11.27 |
| 노트북LM으로 여는 새로운 글쓰기 시대 (0) | 2025.11.19 |
| [11/19] 최근 공유된 정보 목록 & 뉴스 (0) | 2025.11.19 |
| 꼭 필요한 6가지 오픈소스 PDF 편집기 (0) | 2025.11.18 |
