구글 스프레드시트 데이터 분석 업무에 가장 많이 쓰이는 핵심 함수 5가지를 소개합니다. 계산을 한 번에 해주는 ARRAYFORMULA 함수부터 VLOOKUP, FILTER, QUERY와 같은 조건 검색 함수, 그리고 여러 파일을 연결시켜 주는 IMPORTRANGE 함수까지 설명합니다.
기본적으로 구글 스프레드시트에서 제공하는 함수들은 아래 사이트에서 전부 확인해 볼 수 있습니다. 본 블로그에서는 실무에서 가장 많이 사용하는 '조건에 맞는 데이터를 가져와 분석하는 함수'를 다루겠습니다.
1. 구글 스프레드시트 효율 끝판왕 함수 : ARRAYFORMULA
함수 : ARRAYFORMULA(배열 수식)
ARRAYFORMULA 함수는 배열 연산을 수행하기 위해 설계된 함수입니다. 이 함수는 여러 개의 데이터를 한 번에 처리하고 계산합니다. 엑셀에서는 지원하지 않고 구글 스프레드시트에서 흔히 사용되는 가장 강력한 함수 중 하나입니다.
활용 예시
적용 함수 : DATEDIF(시작일, 종료일, 단위)
예를 들어, 시작일과 마감일을 지정해 놓고, DATEDIF 함수를 사용해서 이 두 날짜의 차이가 며칠인지 계산해 보겠습니다. 이때, 기간이라는 새로운 열에 계산된 값을 반환해 보겠습니다.
DATEDIF함수만 적용했을 때는 행 하나하나를 전부 계산해줘야 합니다. 따라서 C3, C4, C5,... 셀까지 같은 함수를 입력해주어야 하죠. 위 이미지에서 파란색 점을 더블 클릭하면 한 번에 입력을 해줄 수 있지만, 이때 문제가 있습니다. 이렇게 하면, A14, B14 셀부터 데이터를 새로 입력했을 때, 또다시 C14 셀도 수동으로 함수를 다시 입력해주어야 한다는 것이죠. 그럼 ARRAYFORMULA함수를 적용했을 때 어떻게 달라지는 게 보여드리겠습니다.
ARRAYFORMULA 함수를 적용하면, 일일이 함수를 입력해주지 않아도 A열과 B열을 전부 한꺼번에 계산하여 C열에 반환해 줍니다. 다만 이 경우에도 여전히 문제가 남아있습니다. 위 이미지와 같이 A열과 B열에 데이터가 없을 때, 0이라는 값이 반환된다는 것이죠. 이를 해결하기 위해서는 if함수를 추가시키면 됩니다.
적용 함수 : if(논리 표현식, TRUE인 경우 값, FALSE인 경우 값)
if함수를 적용해서 계산된 값이 0일 때, ""을 반환하고, 0이 아닐 때, DATEDIF 함수에 적용된 값은 반환하게끔 하면 위 이미지와 같이 0이 사라집니다. 이제는 A열과 B열에 시작일과 마감일을 입력하면 자동으로 C열에 계산된 값이 출력됩니다.
ARRAYFORMULA 함수 주의사항
- SUM, TEXTJOIN 함수와 같이 함수내 계산 또는 검색 범위가 배열일 경우엔 함께 사용하지 못한다.
- 단일 셀에 입력되어야한다. 셀 아래 다른 값이 입력되어 있으면 에러가 발생한다.
2. 조건 검색 함수 : VLOOKUP
함수 : VLOOKUP(검색 키, 범위, 색인, [정렬됨])
VLOOKUP 함수는 지정한 값에 해당하는 다른 범위의 데이터를 검색하여 가져오는 역할을 합니다. 주로 데이터 결합과 검색에 활용되며, 특정 값을 기준으로 다른 테이블에서 연관된 정보를 찾을 때 유용합니다.
활용 예시
위 예시에서 만들었던 테이블을 참조해서 이번엔 검색할 시작일을 입력하면 그에 일치하는 마감일을 출력해 보겠습니다. VLOOKUP 함수를 적용하여 첫 번째 인수에는 검색할 셀을 입력하고, 두 번째 인수에는 참조할 테이블을 지정합니다. 세 번째 인수에는 테이블 중에서 몇 번째 열을 기준으로 출력할 건지 지정합니다. 위 예시에서 사용한 테이블은 첫 번째 열이 시작일, 두 번째 열이 마감일, 세 번째 열이 기간이었기 때문에 2라고 지정해 주었습니다. 그리고 정렬을 하지 않을 것이기 때문에 마지막 인수에는 0을 입력합니다.
이렇게 VLOOKUP 함수를 활용하면, 다른 시트에 있는 셀을 참조해서 원하는 값을 검색해서 찾을 수 있습니다. 그러면 만약 여러 개의 값을 동시에 검색하려면 어떻게 해야 할까요?
적용 함수 : IFERROR(값, [오류인 경우 값])
이번에도 ARRAYFORMULA 함수를 적용해 보겠습니다. 마찬가지로 검색 키를 A열 전체로 지정한 후에 ARRAYFORMLA 함수를 적용하면, 검색할 시작일을 입력하면 자동으로 마감 일이 출력됩니다.
위 이미지에서는 A5부터는 검색 키가 존재하지 않습니다. 따라서 B5부터는 에러가 발생하게 됩니다. 이때, IFERROER함수로 감싸주면 에러 일 경우 빈 값이 반환됩니다.
주의할 점
범위를 지정하면, 항상 첫 번째 열을 기준으로 검색을 하게 됩니다. 그래서 만약 두 번째 열이나, 세 번째 등의 열을 기준으로 검색을 하고 싶다면, 중괄호를 활용해야 합니다.
중괄호를 활용하면 위 이미지와 같이 A열과 B열의 위치가 서로 바뀐 값을 반환하게 만들 수 있습니다. 이를 VLOOKUP 함수에 적용하면, 두 번째 혹은 세 번째 열을 기준으로 검색이 가능해집니다.
이번에는 마감일을 검색하면, 그에 해당하는 시작일을 출력하는 함수를 작성해 봤습니다. 이 처럼 중괄호를 활용하면, 원하는 열을 기준으로 검색을 할 수 있습니다.
3. 조건 검색 함수 : FILTER
함수 : FILTER(범위, 조건1, [조건2, ...])
FILTER 함수는 데이터 범위에서 특정 조건을 충족하는 행만을 선택하여 결과를 반환하는 함수입니다. 이를 통해 데이터를 필터링하여 원하는 정보를 추출할 수 있습니다.
활용 예시
| 이름 (A1) | 나이 (B1) | 성별 (C1) |
| 홍길동 | 30 | 남성 |
| 김영희 | 25 | 여성 |
| 이철수 | 28 | 남성 |
| 박지민 | 22 | 여성 |
위와 같은 데이터가 있을 때, 나이가 25세 이상이고 성별이 여성인 데이터를 필터링해 보겠습니다.
=FILTER(A2:C5, B2:B5 >= 25, C2:C5 = "여성")이렇게 함수를 적용하면 아래와 같은 결과가 출력됩니다. 이 경우는 AND 조건을 사용한 경우입니다.
| 이름 (A1) | 나이 (B1) | 성별 (C1) |
| 김영희 | 25 | 여성 |
OR 조건 사용하여, 나이가 25세 이상이거나 성별이 여성인 데이터를 필터링하려면 아래와 같이 함수를 작성하면 됩니다.
=FILTER(A2:C5, (B2:B5 >= 25) + (C2:C5 = "여성"))| 이름 (A1) | 나이 (B1) | 성별 (C1) |
| 홍길동 | 30 | 남성 |
| 김영희 | 25 | 여성 |
| 이철수 | 28 | 남성 |
| 박지민 | 22 | 여성 |
이렇게 되면 기존의 모든 테이블이 그대로 출력됩니다. 나이가 25세 이상인 사람은 '홍길동', '김영희', '이철수' 세 사람이고, 성별이 여성에 해당하는 사람은 '김영희', '박지민' 이기 때문에 모든 사람이 조건에 부합하게 됩니다.
4. 조건 검색 함수 : QUERY
함수 : QUERY(데이터, 검색어, [헤더])
'검색어'에 해당하는 부분에 쿼리 조건문을 사용할 수 있습니다. QUERY 함수는 SQL과 비슷하지만, 수식 입력하는 방법이 조금 차이가 있습니다. 아래 포스팅에서 자세하게 설명하고 있으니, 참고해 보시는 걸 추천드립니다.
[구글 스프레드시트] QUERY 함수(1) - 기본구성 및 사용법
이 외에도 조검 검색 함수에는 HLOOKUP, INDEX(MATCH), XLOOKUP, DGET 함수가 있습니다. 현업에서는 위 3가지만 사용하므로 여기서는 다루지 않겠습니다.
5. 파일 연결 함수 : IMPORTRANGE 함수
함수 : IMPORTRANGE(스프레드시트 URL, 범위 문자열)
IMPORTRANGE 함수는 구글 스프레드시트에서 다른 스프레드시트의 데이터를 가져올 때 사용하는 함수입니다.
활용 예시
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/스프레드시트ID", "Sheet1!A1:B5")가져올 데이터가 있는 스프레드시트의 URL과 함께 범위 문자열을 인수로 넣어줍니다. 가져올 범위를 지정하는 "Sheet1!A2:B6" 또는 "A2:B6" 형식의 문자열입니다. 후자와 같이 시트명 없이 셀 범위만 지정할 경우 해당 파일의 첫 번째 시트가 적용됩니다. 불러온 데이터는 실시간으로 업데이트됩니다.
마찬가지로 중괄호를 통해서 여러 파일을 한꺼 번에 연결시키는 것도 가능합니다.
주의 사항
- IMPORTRANGE 함수를 처음 사용하면 오류가 뜹니다. 액세스 허용을 클릭해야 데이터가 불러와집니다.
- 원본시트에서 워크시트 명을 바꿔버리면 오류가 뜹니다.
이 외에도 파일 연결 함수에는 IMPORTDATA, IMPORTFEED, IMPORTHTML, IMPORTXML 함수가 있습니다. 현업에서는 주로 IMPORTRANGE 함수만 사용하므로 여기서는 다루지 않겠습니다.
여기까지 구글 스프레드시트 데이터 분석 업무 핵심 함수 5가지를 알아보았습니다. 감사합니다.
관련 글
ARRAYFORMULA - Google Docs 편집기 고객센터
VLOOKUP - Google Docs 편집기 고객센터
IMPORTRANGE - Google Docs 편집기 고객센터
'데이터분석 > 구글 스프레드시트' 카테고리의 다른 글
| 앱시트 + 구글 스프레드시트로 개인용 앱 만들기 1시간 컷 (0) | 2023.11.06 |
|---|---|
| 구글 스프레드시트 SPARKLINE : 100% 누적 바 그래프 함수로 그리기 (0) | 2023.09.18 |
댓글