지난 편에 이어서 이번 편에는 5. Deep Dive 대시보드를 살펴볼 예정이다.
4편에서 확인한 'Overview'대시보든가 RE100 기업들의 전체적인 현황을 살펴보기 위한 목적이라고 한다면,
5편의 Deep Dive 대시보드는 각종 필터를 통해서 유저가 원하는 데이터를 선별적으로 확인할 수 있는 인터렉티브 대시보드이다.
인터렉티브 대시보드를 통해서 아래의 질문들에 대해서 답을 구할 수 있다.
2020년 한 해동안만 RE100에 가입한 기업들은 어떻게 될까?
2021년 가입한 총 기업들 중에 한국 기업의 비중은 얼마나 될까?
한국의 경우 어떤 산업들이 주로 RE100에 가입을 했을까?
서비스 산업의 경우 어떤 국가의 기업들이 가입을 했을까?
구글시트로 인터렉티브 대시보드 제작을 위한 3가지 요소
구글 시트로 인터렉티브 대시보드를 만들기 위해서는 아래 3가지 요소가 중요하다
1. 원본데이터 (raw data) :
대시보드의 근간이 되는 데이터이다. 1 ~ 3편에서는 이 원본 데이터의 수집, 가공 및 업데이트에 대해서 알아보았다. 인터렉티브 대시보드에서 제일 중요한 쿼리데이터도 결국 모두 원본데이터에서 특정 요건으로 일부의 데이터를 발췌한 데이터다.
2. 컨트롤러 (controller) :
유저가 연도를 2020으로 선택하는 순간, 시작연도는 2020으로 설정이 되는데 이처럼 유저의 선택에 따라 값이 바뀌는 변수를 컨트롤러(controller)라고 한다.
이번 대시보드에서는 크게 3가지 유형의 컨트롤러가 있다.
1) 날짜 컨트롤러 (시작연도, 종료연도)
2) 산업 컨트롤러
3) 국가 컨트롤러
3. 쿼리데이터 (query data) :
유저가 컨트롤러를 조작함에 따라서 변경되는 데이터이다.
① 유저가 컨트롤러를 조작하면, ② 이 조작에 따라서 쿼리문이 변경되고, ③ 변경된 쿼리문에 따라서 쿼리데이터가 변경된다. (쿼리문에 대해서는 아래에서 보충 설명할 예정)
쿼리데이터는 원본데이터에서 쿼리문을 통해 발췌된 데이터이기 때문에, 항상 원본데이터의 일부라고 볼 수 있다.
(원본 데이터에 없는 데이터는 쿼리 데이터에도 없다)
인터렉티브 대시보드 작동 원리 (작동 Flow)
1~3에서는 원본데이터의 가공, 그리고 트리거 설정을 통한 업데이트 자동화를 살펴보았다.
이렇게 수집하고 가공된 원본데이터와 컨트롤러, 그리고 쿼리 데이터라는 3가지가 요소가 어떻게 상호작용하는지, 대시보드의 사용자 관점에서 작동 Flow를 살펴보면 아래와 같다.
1. 대시보드의 사용자는 연도, 산업, 국가 등 컨트롤러를 통해서 원하는 연도, 원하는 산업 등을 선택한다.
2. 사용자의 선택에 따라서 컨트롤러의 변수가 바뀐다.
3. 쿼리데이터 시트의 데이터는 queryfunction 함수를 통해서 컨트롤러의 값 변동에 그대로 반응한다. 변수의 값이 바뀌면 쿼리데이터 시트도 바뀐다.
4. Deep Dive의 차트의 모양도 변경이 된다. 차트의 모양이 변경되는 이유는, 차트의 데이터 범위가 원본데이터 전체가 아니라 쿼리데이터이기 때문이다.
인터랙티브 대시보드 제작 과정
컨트롤러(Controller) 설정
'controller'시트를 생성하고, 아래와 같이 식을 입력한다.
dashboard 시트의 N6셀은 대시보드에서 시작 연도를 입력하는 칸이다.
대시보드에서는 연도만 입력해도, date 함수를 통해서 컨트롤러는 날짜 서식이 되도록 설정을 하였다.
컨트롤러 이름 지정으로 관리하기
밑에서 등장할 쿼리시트 및 쿼리함수에서도는 여러 범위들이 등장하는데, 각 컨트롤러 범위에 이름을 지정해서 쿼리문을 만들 때 실수를 줄일 수 있다.
데이터 → 이름이 지정된 범위를 클릭해서 컨트롤러 범위에 이름을 설정해주거나, 셀 클릭 후 Ctrl+J로 바로 이름을 지정하면 된다.
아래와 같이 대시보드에서는 start_date로 이름을 지정해 주었다.
이제 대시보드에서 사용자가 연도를 바꿀 때 마다, start_date라는 컨트롤러 (혹은 변수)의 값이 매번 바뀌는 것을 확인 할 수 있다.
드롭다운 박스를 통해서 사용자가 입력할 수 있는 항목을 한정하기
이번 대시보드의 연도 항목에서는 2014, 2015와 같이 4자리 숫자로 된 연도 숫자만 입력이 되어야 한다.
"2018년" 같이 한글이 포함된 문자도 입력이 되면 안되고, "2019-01-01"와 같은 날짜 형식도 입력이 되면 안된다.
이렇게 입력이 되는 경우 위에서 설정한 start_date의 값에서 오류가 발생하기 때문이다.
이처럼 사용자가 값을 바꿀 수 있는 컨트롤러 범위에서는 '데이터 범위'를 별도로 설정해서 사용자가 입력할 수 있는 항목을 제한하는 것이 바람직하다.
대시보드의 컨트롤러 범위를 클릭한 후 데이터 → 데이터 확인을 클릭한다
아래와 같이 항목 목록을 클릭하고, 연도를 입력하고 확인을 누르면, 셀에서는 해당 연도에 대한 입력만 가능하다.
하지만 이렇게 연도를 수동으로만 목록을 입력할 경우, 연도가 바뀔 때 마다 매번 범위를 바꿔줘야 하는 번거로움이 있다.
지난 1 ~ 3편에서도 원본데이터의 수집, 가공 및 업데이트를 자동화하는 방법을 다루었는데, 여기에서도 드롭다운 목록을 원본데이터 범위에 따라서 자동으로 바꿔주도록 할 수 있다.
controller 시트에서 아래와 같이 수식을 이용한다.
max_year = max(UNIQUE(data!C2:C)
* 원본데이터의 연도 범위 (data!C2:C) 에서 중복되지 않는 값들만 가져온 후, 그 중에서 가장 큰 값
min_year = min(UNIQUE(data!C2:C)
* 원본데이터의 연도 범위 (data!C2:C) 에서 중복되지 않는 값들만 가져온 후, 그 중에서 가장 작은 값
year_range = arrayformula(SEQUENCE(max_year-min_year+1) + min_year -1)
그 다음 연도가 나열된 목록을 year_range라는 이름으로 지정하고, 데이터 확인에서도 year_range라고 범위를 입력해주면 된다.
이와 같은 방식으로 국가, 산업 컨트롤러도 설정을 해주면 컨트롤러 준비는 완성이 된다.
Queryfunction을 이용한 쿼리데이터 시트 제작
원본데이터와 컨트롤러가 준비되었으면 쿼리데이터 시트를 제작할 수 있다.
쿼리데이터 시트에서 데이터는 쿼리함수에 의해서 동적으로 호출되는 데이터시트이다.
(쿼리함수에 대한 부연 설명은 여기를 참고해도 된다.
RE100 대시보드의 쿼리시트는 아래와 같이 되어 있다.
위에서 정의해준 start_date, end_date 등의 컨트롤러는 여기에 그대로 삽입이 된다.
그래서 사용자가 연도,산업 또는 국가를 변경하면, 쿼리함수 안의 쿼리문에도 그대로 반영이 되고 데이터도 동적으로 변경이 된다.
이렇게 1 ~ 5편까지 RE100 기업의 현황을 볼수 있는 인터렉티브 대시보드에 대해서 알아보았다.
대시보드의 구성 요소 설명부터 제작 과정까지 주요 내용에 대해서는 빠짐없이 내용을 적어보려고 하였다.
구글의 Query Function 및 Apps script를 결합하면, 데이터를 자동으로 업데이트하고, 사용자의 상황에 따라 자동으로 변화하는 강력한 대시보드를 제작할 수 있다.
구글 시트 기반으로 대시보드를 구축하면 무엇보다 무료라는 점, 그리고 유지보수가 수월하다는 점 (웹 상의 Apps script 및 구글 시트를 통해서 가능)이 또다른 강점이다.
이번 글을 통해서 더욱 많은 사람들이 구글시트의 위력과 활용가능성에 대해서 알 수 있게 되면 좋을 것 같다.
'Projects > Renewable Energy' 카테고리의 다른 글
| 구글 스프레드시트로 RE100 대시보드 만들기 (4. Overview 대시보드 만들기) (0) | 2022.03.01 |
|---|---|
| 구글 스프레드시트로 RE100 대시보드 만들기 (3. 트리거 설정 및 업데이트 자동화) (0) | 2022.02.28 |
| 구글 스프레드시트로 RE100 대시보드 만들기 (2. 데이터 수집) (0) | 2022.02.15 |
| 구글 스프레드시트로 RE100 대시보드 만들기 (1. 대시보드 소개) (1) | 2022.02.14 |
| [파이썬] 일본 종목토론방 살펴보기 (ft. 전력회사 이렉스) (0) | 2021.07.03 |
