[구글 시트] 구글 설문 체크박스 데이터 전처리/시각화

구글 설문의 체크박스 응답내용을 구글시트에서 차트로 표현하고 싶을 때가 있다.

 

구글 설문 응답내용을 구글시트에서 관리하고 있어서, 체크박스 응답내용도 같이 관리하고 싶을 때가 이런 경우다.

 

체크박스 응답내용의 특징은 객관식과 다르게 복수 응답이 가능하다는 것이다.

 

 

복수 응답을 하면 구글 시트에는 다음과 같이 ","로 구분되어 하나의 셀에 표시된다.

 

 

이처럼 복수응답이 가능한 체크박스 문항의 경우

각 응답항목별로 수치가 어떻게 되는지 확인하기 위해서는 약간의 전처리가 필요하다.

 

전처리 후에는 구글설문에서 바로 확인가능한 차트를 구글시트에서도 구현할 수 있다. 

 

구글설문에서 확인할 수 있는 시트

1. 보조시트를 만들기

 

구글설문 응답결과를 스프레드시트로 확인하는 경우 설문 응답용 시트가 만들어진다. (시트 명은 "설문지 응답 시트1")

하지만 열 추가, 데이터 전처리는 이 시트에서 직접 하는 것 보다 보조시트에서 진행하는 것이 

원래 응답내용을 건드리지 않는 안전한 방법이다.

 

보조시트를 만들고 importrange 함수를 이용해서 관련 데이터를 가져온다.

importrange 함수에서는 시트의 url를 입력해야 하는데 시트 url은 주소 창에서 spreadsheets/d/ 다음으로 나타나는 ㅁ문자열이다. 

이 문자열을 그대로 복사해서 넣어두면 된다.

 

2. 각 체크박스 응답내용으로 새로운 열 생성

 

위 예시에서 체크박스 응답항목으로 3가지가 있는데 (마녀, 이태원 클라쓰, 그해 우리는)

각 항목을 이름으로 하는 열을 각각 생성해둔다.

3. Regexmatch 함수로 각 응답항목이 있는지 확인하기

 

이제는 Regexmatch라는 함수를 이용해서 응답내용에 각 개별 응답항목이 있는지 없는지를 확인하는 작업을 진행한다.

 

Regexmatch는 간단히 말해서, A라는 셀에 특정 표현이 있는지 확인하고 있으면 True, 없으면 False를 내보낸다.

첫번째 응답 행을 기존으로 보면,

"마녀, 이태원 클라쓰"라는 셀에 대해서 

"마녀"열은 True,

"이태원 클라쓰" 열도 True,

"그해 우리는" 열은 False이다.

4. Arrayformula (배열함수) 를 이용해서 범위 전체에 적용해주기

 

첫 행의 함수를 마지막 행까지 적용해주는 방법으로 2가지가 있다.

 

1번 : 셀을 선택한 후 아래로 드래그해주기 

- 이 때 각 응답항목을 나타내는 셀의 경우 행 번호가  고정이 되어 있는지 (절대 참조가 되어있는지) 반드시 확인해야 한다.

절대참조가 되어야지 셀을 아래로 드래그해도 마녀, 이태원 클라쓰, 그해 우리는 참조는 그대로 유지되기 때문이다.

이 때 드래그는 현재 응답결과가 있는 행까지만 하는 것이 아니라 마지막 행까지 해줘야 새로운 응답이 추가되더라도 함수가 바로 반영된다.

 

그런데 지금 있는 시트가 더 늘어나면 함수가 적용이 안된다.

행이 신규로 추가되면 함수는 적용되지 않는다.

이런 문제를 해결하기 위해서는 Arrayformula 함수를 이용해서 범위 전체에 적용해준다.

 

 

 

5. Countif를 이용해서 응답의 개수의 합계를 구하기

 

이제 비워둔 가장 상단의 행에 countif 함수를 이용해서 각 응답항목의 갯수를 구한다.

셀의 값이 TRUE인 경우 응답항목에 포함되는 것이기 때문에 셀의 값이 TRUE인 경우만 세주면 된다.

countif에서 첫번째 부분은 참조할 범위인데, D3:D 처럼 뒷 부분의 셀은 숫자를 생략해서 행이 늘어나더라도 전체 범위에 포함되도록 한다.

countif에서 두번째 부분은 세는 조건을 의미하는데, TRUE()를 입력한다.

 

countif 함수와 각 응답항목은 오른쪽의 새로운 열에 추가를 해둔다. 

응답항목이 위에 있고, 그 아래에 countif 함수가 잇도록 배치를 해야 다음 단계에서 차트를 생성할 때 각 응답항목 레이블을 볼 수 있다. 

6. 차트 생성하기

 

아래와 같이 6개의 셀을 선택해서 차트를 생성한다. 그리고 유형에서는 우측 막대차트를 선택한다.

 

 

 

만약 여기서 값이 큰 순서대로 차트를 표시하고 싶으면 약간의 추가 전처리가 필요하다.

sort라는 함수를 이용해서 범위를 재정렬해야 하는데, sort는 행 형태로 밑으로 쌓이는 범위에 대해서만 적용되는 함수이기 때문에 행열을 전환해주는 transpose 함수를 먼저 쓰고 순서를 바꾼 후, 다시 원래 데이터 형태로 돌아오는 방식이다.