[기획] Arrayformula문의 활용으로 린하게 데이터마트 구축하기

실제 대행사에서 쓰는 데이터를 기반으로 만든 대시보드이다. 제작기간은 1~2일 남짓 걸렸다.

최대 2년간 모든 매체의 모든 소재별 데이터 조회가 무리없이 가능하다.
어떻게 가능할까? 바로 배열수식과 csv 그리고 구글시트에서 제공하는 importrange 수식 덕분이다.

배열수식의 장점

수식에 사용되는 데이터들을 하나의 배열로 인식하여 결과값을 계산하는 방법을 배열수식이라고 한다.
배열수식을 잘 활용할 수만 있다면, 무수히 많은 중첩함수를 하나의 산식만으로 해결할 수 있다. 당연히 시트의 계산속도는 현저히 빨라진다.

배열수식의 한계

배열수식은 강력하지만 모든 formula문에서 적용 가능한 것은 아니다. 대표적인 예로 다중조건문에서 배열수식 적용이 불가능하다. 다중조건문을 적용할 수 없는 문제는 다음과 같이 해결할 수 있었다.

Arrayformula에서 Ifs 쓰기

보너스 120만원 이상 받으면 A+ 100만원 이상 받으면 A 나머지는 B 처리한다.

배열함수를 안쓴다면, Ifs로 다음과 같이 해결이 가능하다.

IFS문을 쓰기 위해선 IF문을 중첩해서 사용해야한다. 연산속도는 데이터양이 증가할수록 배열수식이 유리하다.

Arrayformula문에서의 문자조합

3개 이상의 문자를 조합해야하는 경우, Arrayformula문에서 Concatenate문을 실행하면 원하는 값을 얻을 수 없다. 이런 경우 Concat을 이중 적용하여 원하는 3개 이상의 문자열을 조합해야 한다. 다음의 예제를 보자.

셀마다 개별 적용할 경우, Concatenate문으로 쉽게 조합할 수 있다.

Arrayformula문에 Concatenate를 사용하면 범위 내 지정한 모든 텍스트의 조합값이 하나의 셀에 표기된다.

Arrayformula문을 쓸 경우, Concat를 이중 적용하여, 각 셀별로 원하는 텍스트를 조합한다.

Arrayformula+Concat문과 Join문을 활용하여 CSV파일 만들기

CSV는 몇 가지 필드를 쉼표(,)로 구분한 텍스트 데이터 및 텍스트 파일을 의미한다. 확장자는 .csv이며 MIME 형식은 text/csv이다. comma-separated variables라고도 한다.
쉼표로만 구분된 텍스트 형태인 CSV는 데이터분석을 위한 raw 파일로서 범용적이라는 장점이 있다.

구글스프레드시트의 꽃 importrange

엑셀에서 구글스프레드시트로 넘어가는 이유는 데이터 연동이 실시간으로 이루어진다는 점에 있다.

스키마 구조를 짜듯이 시트간 데이터 연동을 체계적으로 구축할 수 있다면, 제법 쓸만한 데이터분석툴로 활용할 수 있다.

광고분석을 실시간으로 할 수 있는 크리에이티브 통계, 최대 3년치의 데이터 분석이 가능하도록 설계했다.

이 때 사용하는 주된 함수들이 바로 importrange이다.

importrange("스프레드시트_url","범위_문자열") #지정된 시트의 셀범위를 가져온다.

1. 스프레드시트_URL : 가져올 데이터가 있는 스프레드시트의 URL
2. 범위문자열 : 가져올 범위를 지정하는 형식"[시트이름!]범위"(예: "Sheet1!A2:B6" 또는 "A2:B6")의 문자열

이전 예제로 썼던 데이터를 import하기로 한다.

해당 URL의 데이터를 불러왔다. 2시트간 연동된 데이터는 실시간으로 공유 및 반영된다.

구글스프레드시트에서 CSV파일

구글스프레드시트에서 CSV파일의 성격을 이용하면 보다 빠르고 강력한 데이터 분석이 가능하다.

첨부한 예제를 보면 알 수 있듯이, 쉽고 빠르게 연동이 가능하다.

하지만 예제는 21개의 데이터로만 이루어져있다. 실무에서는 셀 수 없이 많은 데이터의 분석이 필요하다.

importrange함수의 부하는 불러오는 셀의 갯수와 관련이 있기 때문에 분석할 데이터의 양이 많아질수록 연동할 셀의 갯수가 많아지므로 시트가 무거워진다는 단점이 있다.

총 21개의 data를 추출했다. 만약에 추출할 데이터가 1,000,000,000개가 넘는다면?

이럴 때 사용하는 것이 Arrayformula문과 Concat문 및 Join문이다.

구글스프레드시트는 셀 1개에 최대 30000개의 텍스트를 담을 수 있는데, 데이터 유형에 따라 최소 수개월에서 수년의 데이터를 구글스프레드시트의 1셀에 담을 수 있다.

셀 1개에 응축된 CSV형태의 데이터를 담고, 이를 importrange함수를 이용해 빠르게 연동시켜보자.