구글 스프레드시트 사용법: 원하는 데이터만 쏙쏙 뽑아 분석하기 오늘의 정보 | 실속노트 대표 이미지

구글 스프레드시트 사용법: 데이터 분석 6가지 핵심 기능

작성자:
구글 스프레드시트 사용법 요약 도식
검수한 본문을 바탕으로 만든 자체 요약 도식입니다. 실제 서비스 화면은 아닙니다.

구글 스프레드시트에서 조건에 맞는 자료를 추출하고, 합계 및 피벗 기능을 활용하며, 외부 파일까지 연결하여 데이터를 종합적으로 분석하는 방법을 알고 싶으신가요?

구글 스프레드시트 사용법: 데이터 집계 및 분석 시작하기

구글 스프레드시트의 다양한 기능을 활용해 데이터를 체계적으로 집계하고 분석하는 방법을 알아봅니다. 복잡한 데이터를 다루거나 필요한 정보를 정확히 찾아야 할 때 스프레드시트는 유용합니다.

이 가이드에서는 데이터 추출, 조건부 합계 계산, 고급 쿼리 작성, 외부 데이터 연동, 오류 처리 등 실무에서 자주 쓰이는 핵심 기능들을 단계별로 설명합니다.

특정 조건에 맞는 데이터 추출하기: FILTER 함수

구글 스프레드시트에서 특정 조건에 맞는 데이터를 추출하려면 FILTER 함수를 사용하세요. 이 함수는 지정한 조건에 맞는 행이나 열만 따로 모아 보여줍니다.

FILTER 함수의 구문은 다음과 같습니다: FILTER(범위, 조건1, [조건2, ...]).

함수의 각 인수는 다음과 같습니다. ‘범위’는 필터링할 데이터 영역을 의미합니다.

‘조건1’은 필터링 기준이 되는 참(TRUE) 또는 거짓(FALSE) 값이나 배열 수식입니다. ‘[조건2, …]’는 선택 사항으로, 추가 필터링 기준을 지정할 때 사용합니다.

여러 조건을 사용할 경우, 모든 조건은 행 기준 또는 열 기준 중 하나로 통일해야 하며, 혼합할 수 없습니다.

중요한 점은 모든 조건이 필터링할 ‘범위’와 길이가 같아야 한다는 것입니다. 예를 들어, 범위에 100개의 행이 있다면, 각 조건도 100개의 참/거짓 값을 가져야 합니다.

FILTER 함수를 사용하는 방법을 예시로 알아보겠습니다. FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)는 A열 값이 5보다 크고 D열 값이 10보다 작은 행만 A2:B26 범위에서 추출합니다.

FILTER(A2:C5, {TRUE; TRUE; FALSE; TRUE})는 A2:C5 범위의 1, 2, 4번째 행을 포함하고 3번째 행을 제외합니다. 이 조건은 실제 시트의 2, 3, 5번째 행에 해당하는 데이터를 반환합니다.

마지막으로 FILTER(A2:B10, NOT(ISBLANK(A2:A10)))는 A열이 비어 있지 않은 행만 A2:B10 범위에서 추출합니다. 이처럼 FILTER 함수를 활용하면 원하는 데이터를 정확하게 찾아낼 수 있습니다.

조건에 맞는 데이터 합산하기: SUMIF 및 SUMIFS 함수

데이터를 분석하다 보면 특정 조건에 맞는 값들만 골라 합산해야 할 때가 있습니다. 예를 들어, 특정 지역의 매출 총액이나 특정 날짜 이후의 판매량 합계를 계산해야 할 때 유용합니다.

구글 스프레드시트에서는 이러한 조건부 합계를 위해 SUMIF와 SUMIFS 함수를 제공합니다. SUMIF는 하나의 조건으로, SUMIFS는 여러 조건을 동시에 만족하는 데이터의 합계를 계산하여 더 유연한 분석을 가능하게 합니다.

SUMIF 함수는 지정한 범위에서 특정 조건 하나를 만족하는 셀의 값을 합산합니다. 예를 들어, ‘서울’ 지역 매출만 합산하거나 ‘완료’ 상태인 작업의 수량만 더할 때 사용합니다.

구문은 SUMIF(criteria_column, criterion, [sum_column])입니다. 여기서 criteria_column은 조건을 검사할 셀 범위, criterion은 해당 범위에 적용할 조건입니다. sum_column은 합산할 실제 값을 포함하는 셀 범위이며, 이 인수를 생략하면 criteria_column의 값이 합산됩니다.

실제 데이터에 SUMIF 함수를 적용하는 예시는 다음과 같습니다. SUMIF(A1:A10, ">20")는 A1부터 A10까지의 범위에서 값이 20보다 큰 셀들의 값을 합산합니다. sum_column이 지정되지 않았으므로, 조건 범위인 A1:A10의 값이 직접 합산됩니다.

SUMIF(A1:A10, "Paid", B1:B10)는 A1부터 A10까지의 범위에서 값이 “Paid”인 경우, 해당 행의 B1부터 B10까지의 값을 합산합니다. 이는 ‘Paid’ 상태인 모든 항목의 금액을 합산하는 것과 같습니다.

SUMIFS 함수는 두 개 이상의 조건을 동시에 만족하는 데이터의 합계를 계산할 때 사용합니다. 예를 들어, ‘서울’ 지역이면서 ‘가전제품’ 카테고리에 속하는 매출만 합산하는 등 더 복잡한 분석이 가능합니다.

SUMIFS 함수의 구문은 SUMIFS(sum_column, criteria_column1, criterion1, [criteria_column2, criterion2, ...])입니다. SUMIFS에서는 합산할 값의 범위인 sum_column이 가장 먼저 옵니다.

이후 criteria_column1, criterion1처럼 조건 범위와 조건을 순서대로 지정합니다. SUMIFS 함수 사용 시 가장 중요한 점은, 합계를 구하려면 모든 지정된 조건이 해당 행에서 충족되어야 한다는 것입니다.

실제 데이터에 SUMIFS 함수를 적용하는 예시는 다음과 같습니다. SUMIFS(A1:A10, B1:B10, ">20")는 B1부터 B10까지의 범위에서 값이 20보다 큰 경우, 해당 행의 A1부터 A10까지의 값을 합산합니다.

SUMIFS(A1:A10, B1:B10, ">20", C1:C10, "<30")는 B1:B10의 값이 20보다 크면서 동시에 C1:C10의 값이 30보다 작은 경우에만 A1:A10의 값을 합산합니다. SUMIFS(C1:C100, E1:E100, "Yes")는 E1:E100 범위의 값이 “Yes”인 경우, 해당 행의 C1:C100 값을 합산합니다.

SUMIF와 SUMIFS 함수를 사용할 때 주의할 점이 있습니다. SUMIFS 함수에서 모든 조건 범위(criteria_column)는 반드시 동일한 크기여야 합니다. criterion에는 텍스트, 숫자, 날짜 또는 비교 연산자를 포함한 표현식을 사용할 수 있으며, 텍스트는 따옴표로 묶어야 합니다.

SUMIF는 단일 조건만 처리하므로, 여러 조건이 필요하다면 SUMIFS를 사용해야 합니다.

고급 데이터 조작 및 분석: QUERY 함수

QUERY 함수는 구글 스프레드시트에서 복잡한 데이터 조작 및 분석을 수행할 수 있게 해주는 매우 강력한 도구입니다. 이 함수는 SQL과 유사한 Google Visualization API Query Language를 사용하여 데이터 범위에서 검색, 필터링, 정렬, 그룹화, 집계, 피벗 작업을 수행할 수 있습니다.

FILTER나 SUMIF 같은 함수가 특정 기능에 특화된 반면, QUERY 함수는 데이터베이스처럼 원하는 형태로 데이터를 자유롭게 조작하고 추출하는 유연성을 제공합니다.

QUERY 함수의 기본 구문은 QUERY(data, query, [headers])입니다. data는 쿼리를 실행할 셀 범위이며, query는 Google Visualization API Query Language로 작성된 쿼리 문자열입니다. 이 문자열 안에 어떤 데이터를 선택하고 어떻게 처리할지에 대한 명령을 정의합니다. headers는 data 범위에 포함된 헤더 행의 개수를 지정하며, 생략 시 자동으로 추정됩니다.

QUERY 함수는 열에 여러 데이터 유형이 혼합된 경우 가장 흔한 유형을 기본으로 처리하고 나머지는 null로 간주하므로 데이터 일관성이 중요합니다.

데이터를 선택하고 필터링하기 위해 SELECT와 WHERE 절을 사용합니다. SELECT 절은 결과로 반환할 열을 지정하며, 열은 알파벳 문자(예: A, B) 또는 ColN 표기법(예: Col1, Col2)으로 지정할 수 있습니다.

WHERE 절은 특정 조건을 만족하는 행만 필터링하는 역할을 합니다. 예를 들어, QUERY(A2:E6, "SELECT A, B WHERE C > 10")는 A2부터 E6 범위에서 C열의 값이 10보다 큰 행들의 A열과 B열 데이터를 반환합니다.

WHERE 절에서는 비교 및 논리 연산자를 사용하여 복잡한 필터링 규칙을 만들 수 있습니다.

데이터를 그룹화하고 집계 정보를 계산하려면 GROUP BY 절을 사용합니다. GROUP BY는 특정 열의 값이 같은 행들을 하나의 그룹으로 묶습니다.

이 절은 보통 SELECT 절의 집계 함수(SUM, AVG, COUNT 등)와 함께 사용되어 각 그룹별 요약 정보를 제공합니다. 예를 들어, QUERY(A2:E6, "SELECT B, SUM(A) GROUP BY B")는 B열의 고유한 값별로 데이터를 그룹화하고, 각 그룹에 대해 A열 값들의 합계를 계산하여 반환합니다.

데이터를 재구성하여 피벗 테이블과 유사한 형태로 만들려면 PIVOT 절을 사용합니다. PIVOT 절은 특정 열의 고유한 값들을 새로운 열의 헤더로 변환하는 역할을 합니다.

SELECT 절의 집계 함수와 함께 사용하면, 특정 기준에 따라 데이터를 집계하고 그 결과를 열 형태로 펼칠 수 있습니다. 예를 들어, QUERY(A2:E6, "SELECT SUM(A) PIVOT B")는 B열의 각 고유 값을 새 열 헤더로 만들고, 해당 열에 대한 A열 값들의 합계를 계산하여 표시합니다.

작은 데이터셋으로 꾸준히 연습하며 활용 범위를 넓혀가세요.

피벗 테이블로 데이터 요약 및 분석하기

구글 스프레드시트 피벗 테이블을 사용하면 대량의 데이터에서 필요한 정보를 쉽게 찾고 항목 간 관계를 파악할 수 있습니다. 복잡한 데이터를 요약·분석해 숨겨진 패턴이나 추세를 발견하도록 돕습니다.

예를 들어, 특정 달의 최고 수익 영업 사원을 찾는 데 활용할 수 있습니다.

피벗 테이블은 원본 데이터를 행과 열로 재배열하고 합계, 평균, 개수 등의 함수를 적용하여 특정 기준별 집계를 보여줍니다. 원본 데이터는 그대로 유지하면서 제품별 판매량, 지역별 매출 총액, 월별 고객 수 변화 등을 한눈에 파악할 수 있습니다.

외부 스프레드시트 데이터 연결하기: IMPORTRANGE 함수

IMPORTRANGE 함수를 사용하면 다른 Google 스프레드시트의 데이터를 현재 시트로 가져와 통합 분석할 수 있습니다. 여러 소스의 데이터를 모아 더 넓은 범위의 분석을 하거나, 다른 파일의 필요한 데이터를 불러올 수 있습니다.

함수의 기본 구문은 IMPORTRANGE(spreadsheet_url, range_string)입니다. 여기서 spreadsheet_url은 데이터를 가져오려는 원본 스프레드시트의 전체 URL을 지정하며, 따옴표 안에 직접 입력하거나 URL이 포함된 셀을 참조할 수 있습니다.

두 번째 인수 range_string은 가져올 데이터의 범위를 나타내며, ‘시트이름!셀범위’ 형식(예: “Sheet1!A1:C10”)으로 지정합니다. range_string의 ‘시트이름’ 부분은 선택 사항이며, 생략 시 IMPORTRANGE는 기본적으로 첫 번째 시트의 지정된 범위를 가져옵니다.

IMPORTRANGE 함수는 다양한 방식으로 범위를 지정할 수 있습니다. 예를 들어, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "sheet1!A1:C10")와 같이 원본 스프레드시트의 URL과 가져올 범위를 직접 지정할 수 있습니다.

또한, IMPORTRANGE(A2, "B2")처럼 URL은 A2 셀에 입력하고 범위 문자열은 리터럴 값 “B2″로 지정하는 경우를 참조할 수도 있습니다. 또한, Google 스프레드시트에서 미리 정의된 이름 범위나 표 이름을 사용해 데이터를 가져올 수도 있습니다.

예를 들어, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "DeptSales[Sales Amount]")처럼 표 이름을 사용하거나, IMPORTRANGE("https://docs.google.com/spreadsheets/d/abcd123abcd123", "Sales_total")처럼 이름 범위를 사용할 수 있습니다.

IMPORTRANGE 함수를 사용할 때는 몇 가지 기술적인 고려사항과 권장사항을 따르는 것이 좋습니다. 원본 스프레드시트의 데이터가 업데이트되면, IMPORTRANGE 함수를 사용하는 모든 대상 스프레드시트도 새로고침됩니다.

성능 최적화를 위해, 대량의 데이터를 직접 가져오기보다는 원본 시트에서 미리 데이터를 집계하거나 압축한 후 그 결과값만 가져오는 것이 유리합니다. 예를 들어, 100만 행의 합계를 구해야 한다면, 원본 시트에서 합계를 계산한 후 그 단일 결과값만 IMPORTRANGE로 가져오는 것이 훨씬 빠릅니다.

또한, 여러 스프레드시트를 순환 참조하는 구조(예: A가 B를 참조하고 B가 A를 참조하는 경우)는 무한 루프를 발생시켜 데이터 업데이트를 방해하므로 피해야 합니다. IMPORTRANGE는 문서를 처음 열거나 지난 5분 이내에 열었을 때 새로고침되므로, 업데이트 지연이 발생할 수 있음을 인지해야 합니다.

IMPORTRANGE 함수를 사용하려면 원본 스프레드시트에 대한 명시적인 권한 부여가 필요합니다. 대상 스프레드시트에서 처음 데이터를 가져올 때, 연결을 허용할지 묻는 메시지가 표시됩니다.

자신이 소유한 스프레드시트의 데이터를 가져오려 할 때 #REF! 오류와 함께 ‘이 시트를 연결해야 합니다. 액세스를 허용하세요.’라는 메시지가 나타나면, ‘액세스 허용’을 클릭하여 권한을 부여해야 합니다. 만약 자신이 소유하지 않은 스프레드시트의 데이터를 가져오려면, 원본 스프레드시트 소유자에게 액세스 권한을 요청해야 합니다.

한번 권한이 부여되면 해당 스프레드시트의 모든 편집자가 데이터를 가져올 수 있으며, 이 권한은 원본 시트의 공유 사용자 제한(600명)에 포함됩니다. 주의할 점은, 원본 스프레드시트에서 ‘다운로드, 인쇄, 복사 옵션 사용 중지’ 설정이 활성화된 경우, 새로운 IMPORTRANGE 수식을 통해 데이터를 내보낼 수 없습니다.

하지만 이 설정이 활성화되기 전에 생성된 기존 수식은 계속 정상 작동합니다.

오류 발생 시 대체 값 표시하기: IFERROR 함수

스프레드시트에서 수식 계산 결과 #N/A, #REF!, #VALUE!와 같은 오류 메시지가 표시되면 당황스러우셨을 겁니다. 이런 오류는 데이터가 없거나, 잘못된 값을 입력했거나, 접근 권한이 부족한 경우 등 다양한 원인으로 발생할 수 있습니다.

IFERROR 함수를 사용하면 이런 오류를 감지하고, 대신 사용자가 지정한 값을 표시하여 스프레드시트의 가독성과 안정성을 높일 수 있습니다.

IFERROR 함수의 기본 구문은 IFERROR(값, [오류인_경우_값])입니다.

여기서 ‘값’은 오류가 아닌 경우 반환되는 값입니다. 이 수식이 오류를 반환하면, ‘오류인_경우_값’으로 지정한 내용이 대신 표시됩니다.

‘오류인_경우_값’은 필수는 아니며, 생략하면 오류 발생 시 빈 칸이 나타납니다.

예를 들어, IFERROR(A1, "데이터 없음")처럼 사용하면 A1 셀에 오류가 있을 때 ‘데이터 없음’이라고 표시됩니다. 오류가 없다면 A1 셀의 원래 값이 그대로 표시됩니다.

이처럼 IFERROR는 오류 상황을 사용자 친화적으로 처리하여 데이터를 더 보기 좋게 만들어 줍니다.

실무 데이터 분석을 위한 함수 조합 예시

실제 업무에서는 여러 함수를 조합해 복잡한 데이터를 분석합니다. 예를 들어, 여러 지역의 판매 데이터를 분석해 특정 지역의 특정 제품군 매출 합계를 구하고, 데이터 가져오기 과정에서 발생할 수 있는 오류까지 처리하는 방법을 알아보겠습니다.

먼저, IMPORTRANGE 함수를 사용하여 다른 Google 스프레드시트에 저장된 원시 판매 데이터를 현재 분석 시트로 가져옵니다. 이 함수는 원본 스프레드시트 URL과 가져올 범위를 지정합니다.

처음 사용할 때는 데이터 접근 권한을 허용해야 합니다. 가져온 데이터는 현재 시트의 데이터처럼 활용할 수 있습니다.

다음으로, 가져온 데이터에서 특정 지역(예: ‘서울’)의 판매 기록만 추출하기 위해 FILTER 함수를 적용합니다. FILTER 함수는 지정한 조건(예: ‘지역’ 열이 ‘서울’인 행)을 만족하는 데이터 행들만 반환하여 분석 범위를 좁힙니다.

이제 ‘서울’ 지역 데이터만 남았으므로, 이 중에서 특정 제품군(예: ‘노트북’)의 총 판매액을 계산합니다. 이때 SUMIF 함수를 사용하여 ‘제품군’ 열이 ‘노트북’인 경우에 해당하는 ‘판매액’ 열의 값을 합산합니다.

만약 날짜 범위 등 추가적인 조건이 필요하다면 SUMIFS 함수를 사용할 수 있습니다.

마지막으로, IMPORTRANGE를 통한 데이터 가져오기 실패와 같이 실제 오류가 발생할 수 있는 경우를 대비하여 전체 수식을 IFERROR 함수로 감싸는 것을 고려해 보세요. SUMIF 함수에서 조건에 맞는 데이터가 없어 결과가 0으로 나오는 것은 오류가 아니므로, 이 경우에는 IFERROR로 처리하지 않아도 됩니다.

이처럼 여러 함수를 단계적으로 조합하고 IFERROR로 마무리함으로써, 강력하고 안정적인 데이터 분석 워크플로우를 구축할 수 있습니다.

공식 출처와 확인일

함께 읽을 글

댓글 남기기