구글 설문지와 스프레드시트로 만족도 자동 집계 시스템 만들기

매 과정마다 응답파일을 내려받아 평균을 계산하고 있나요? 3개 시트 분리 구조와 AVERAGEIF·COUNTIFS 함수로 4대 영역 만족도와 Top-2 Box, 28번 글의 위험점수까지 자동 계산하는 시스템을 가상 사례(연간 집계시간 88.0% 절감)와 함께 정리했습니다.

위험점수와 만족도 데이터를 수작업 없이 실시간으로 확인할 수 있도록 자동 집계 시스템을 만들겠다고 예고했다. 실제로 많은 담당자들은 4번 글에서 설계한 4대 영역 설문을 운영하면서도, 응답이 모이면 파일을 내려받아 영역별 점수를 하나씩 골라내고, 5번 글에서 다룬 긍정응답비율을 손으로 세어 채우는 작업을 매 과정마다 반복한다. 과정이 20개, 30개로 늘어나면 이 작업만으로도 상당한 시간이 걸리고, 담당자마다 계산 기준이 달라져 데이터의 일관성도 무너진다. 이 글에서는 구글 설문지와 구글 스프레드시트를 연동해, 응답이 들어오는 즉시 4대 영역 평균과 긍정응답비율, 그리고 28번 글에서 소개한 위험점수까지 자동으로 계산되는 집계 시스템을 만드는 방법을 정리한다.

1. 왜 자동화해야 하는가: 수작업의 3가지 병목

수작업 집계에서 반복적으로 발생하는 문제는 시간 소모에 그치지 않는다. 첫째, 전기(轉記) 오류와 수식 파손이다. 데이터를 복사해 붙여넣는 과정에서 행이 밀리거나 참조범위를 매번 수동으로 늘려야 해서 계산 오류가 잦다. 둘째, 실시간 모니터링의 부재다. 과정이 끝나야만 결과를 취합하므로, 심각한 불만이나 낮은 만족도를 교육이 진행되는 도중에는 확인할 수 없다. 셋째, 시계열 비교의 단절이다. 과정마다 파일이 따로 저장되면 14번 글의 예산 효율화나 28번 글의 조기경보처럼 여러 차수를 이어서 비교하는 작업이 사실상 불가능해진다. 해결책은 '설문 제출(입력) → 함수 기반 자동 연산(처리) → 대시보드(출력)'로 이어지는 단일 흐름을 만드는 것이며, 이는 15번 글에서 다룬 IPO 구조를 그대로 적용한 것이다.

2. 시스템 아키텍처: 3개 시트로 분리하기

18번 글에서 강조한 "원본 데이터 보존" 원칙을 시스템 구조 자체에도 그대로 적용해야 한다. 데이터가 쌓이는 곳, 계산되는 곳, 눈으로 보는 곳을 물리적으로 분리하는 것이 안정적인 자동화의 제1원칙이다.

```text
[구글 설문지]
↓ (응답 즉시 자동 전송)
[시트1. Raw_Data] : 원본 응답이 그대로 쌓이는 시트. 절대 수정하지 않는다.
↓ (함수로 참조만 함)
[시트2. Calc_Engine] : 영역별 평균, 긍정응답비율, 위험점수를 계산하는 시트.
↓ (최종 지표만 추출)
[시트3. Dashboard] : 담당자와 경영진이 보는 화면. 조건부 서식으로 신호 표시.
Calc_Engine 시트는 일반 사용자가 실수로 건드리지 않도록 숨김 처리하는 것이 안전하며, 원본이 훼손되면 오류가 발생했을 때 무엇이 잘못되었는지 추적하기 어려워진다는 점을 항상 기억해야 한다.

3. 구글 설문지 문항 설계: 4대 영역 그대로 반영하기

4번 글에서 확정한 4대 영역과 7개 핵심 항목을 설문 문항으로 옮기고, 원본 시트의 열 순서를 문항 순서와 미리 맞춰둔다. 문항을 먼저 만들고 나중에 집계 방법을 고민하면 열 구조가 뒤엉켜 이후 모든 수식이 복잡해진다.

열 영역 문항 코드 문항 내용 척도
A - - 응답 타임스탬프(자동생성) -
B - - 과정명(반드시 객관식으로 고정, 단답형 금지) -
C 콘텐츠 Q1 학습목표와 내용이 일치했다 1

5점
D 콘텐츠 Q2 실무에 유용한 내용이었다 1

5점
E 강사 Q3 전달력이 이해하기 쉬웠다 1

5점
F 강사 Q4 질문에 성실히 답변했다 1

5점
G 운영환경 Q5 일정·장소·교재가 적절했다 1

5점
H 현업전이 Q6 1개월 내 업무에 적용할 수 있다 1

5점
I 현업전이 Q7 개선점·건의사항(서술형) 장문형
과정명 문항은 "단답형"이 아니라 "객관식"으로 만들어야 한다. 단답형으로 두면 "리더십기초"와 "리더십 기초과정"처럼 표기가 조금씩 달라져, 이후 함수가 일부 응답을 조용히 누락시키는 오류로 이어진다.

4. 핵심 함수 1: 영역별 평균을 한 번에 계산하기

Calc_Engine 시트에서 특정 과정의 콘텐츠 영역 평균은 AVERAGEIF 두 개를 AVERAGE로 감싸 계산한다.

Copy=AVERAGE(
AVERAGEIF(Raw_Data!B:B,"리더십기초과정",Raw_Data!C:C),
AVERAGEIF(Raw_Data!B:B,"리더십기초과정",Raw_Data!D:D)
)
강사 영역(E, F열), 운영환경(G열), 현업전이(H열)도 같은 방식으로 만들면 4대 영역 평균이 완성된다. 과정명이 바뀔 때마다 이 수식을 다시 쓸 필요 없이, 요약 시트의 셀 참조("리더십기초과정" 위치)만 바꿔주면 된다.

5. 핵심 함수 2: 긍정응답비율(Top-2 Box) 자동 계산하기

5번 글에서 다룬 긍정응답비율은 4점 이상 응답 수를 전체 유효 응답 수로 나누는 COUNTIFS 조합으로 계산한다.

Copy=COUNTIFS(Raw_Data!B:B,"리더십기초과정",Raw_Data!E:E,">=4")
/ COUNTIFS(Raw_Data!B:B,"리더십기초과정",Raw_Data!E:E,"<>")
검증 예시: "강사 전달력(E열)" 문항에 응답한 8명의 점수가 5, 5, 4, 3, 5, 4, 2, 4라고 가정하면 평균은 (5+5+4+3+5+4+2+4)÷8=4.0점이다. 이 중 4점 이상인 응답은 5, 5, 4, 5, 4, 4로 여섯 개이므로 긍정응답비율은 6÷8×100=75.0%다. 평균 4.0점은 무난해 보이지만 긍정응답비율은 75.0%에 그친다. 5번 글에서 다룬 "평균만 보면 안 되는 이유"가 이 함수 하나로 매번 자동 확인되는 셈이다.

6. 오류를 막는 안전장치: IFERROR와 ARRAYFORMULA

응답자가 아직 없는 신규 과정은 분모가 0이 되어 #DIV/0 오류가 뜬다. IFERROR로 감싸 방지한다.

Copy=IFERROR(COUNTIFS(...)/COUNTIFS(...), "응답없음")
응답이 새로 추가될 때마다 수식을 아래로 복사하는 번거로움을 없애려면 ARRAYFORMULA로 열 전체에 수식을 한 번만 적용해둘 수 있다.

Copy=ARRAYFORMULA(IF(Raw_Data!A2:A="","",(Raw_Data!C2:C+Raw_Data!D2:D)/2))
다만 ARRAYFORMULA는 문법에 익숙하지 않으면 오류를 진단하기 어렵다. 처음에는 개별 수식으로 안정성을 확인한 뒤, 운영이 익숙해지면 도입해도 무방하다.

7. 과정별·부서별 비교: QUERY 함수와 피벗테이블

과정이 10개, 20개로 늘어나면 AVERAGEIF를 매번 따로 입력하기 번거롭다. QUERY 함수를 쓰면 한 번에 과정별 평균을 뽑을 수 있다.

Copy=QUERY(Raw_Data!B:I,
"select B, avg(C), avg(E), avg(H) where B is not null group by B
label avg(C) '콘텐츠평균', avg(E) '강사평균', avg(H) '현업적용평균'")
함수 문법이 낯설다면 [삽입 → 피벗 테이블]에서 행에 "과정명"을, 값에 "C·E·H열"을 평균으로 지정해도 마우스 클릭만으로 동일한 결과를 얻을 수 있다.

8. 28번 글의 위험점수, 같은 시트에서 계산하기

28번 글에서 소개한 종합 위험점수(최근성×0.3+진도×0.4+참여도×0.3) 공식도 Calc_Engine 시트 안에서 그대로 계산할 수 있다. LMS에서 내려받은 최근성·진도·참여도 위험점수를 J, K, L열에 입력해두면 M열에 아래 수식을 넣는다.

Copy=IF(COUNTA(J2:L2)<3,"",J20.3+K20.4+L2*0.3)
28번 글의 예시(최근성 80, 진도 70, 참여도 60)를 그대로 넣으면 80×0.3+70×0.4+60×0.3=24+28+18=70점이 정확히 산출된다. 등급도 같은 기준으로 자동 분류한다.

Copy=IF(M2="","",IFS(M2>=70,"위험군",M2>=40,"관심군",TRUE,"안전군"))
이렇게 하면 만족도 데이터와 위험점수 데이터가 하나의 스프레드시트 안에서 함께 관리되어, 담당자가 두 개의 서로 다른 파일을 오갈 필요가 없어진다.

9. 대시보드와 조건부 서식으로 신호등 만들기

Dashboard 시트에는 숫자를 나열하기보다 신호로 읽을 수 있도록 조건부 서식을 적용한다.

위험(빨강): 위험점수 70 이상, 또는 특정 영역 평균이 3.5 미만
관심(노랑): 위험점수 40~69, 또는 강사 영역이 4.5점 이상인데 현업전이 영역이 3.8점 이하로 격차가 큰 경우(영역 간 불균형 신호)
안전·양호(초록): 위험점수 40 미만이고 전 영역 평균이 4.2 이상
두 번째 기준(영역 간 불균형)은 위험점수와는 별개로, 만족도 데이터 자체에서도 5번 글의 문항 간 격차 문제를 조기에 알려주는 역할을 한다.

Copy========================================================
[ 리더십기초과정 - 3기 종합 현황 ]
응답자: 42명 종합만족도: 4.10/5.0 Top-2 Box: 78.6%
========================================================
영역 평균 Top-2 Box 상태

---

콘텐츠 4.20 81.0% 양호
강사 4.55 90.5% 양호
운영환경 4.05 76.2% 양호
현업전이 3.55 59.5% ★ 관심(불균형)
========================================================

10. 자동화 전 반드시 거쳐야 할 검증 절차

응답 5~10건을 계산기로 직접 평균·비율을 구해 수식 결과와 대조한다.
과정명 표기가 전체 데이터에서 통일되어 있는지 확인한다.
응답이 0건인 신규 과정에서 오류 없이 "응답없음" 등으로 안전하게 표시되는지 확인한다.
학습자 ID가 있는 경우 COUNTIF로 중복 응답이 있는지 확인한다.
원본 Raw_Data 시트를 실수로 직접 수정하지 않았는지 재확인한다.

11. 개인정보 보호와 접근권한 관리(18번 글 연계)

18번 글에서 다룬 원칙은 스프레드시트 운영에도 그대로 적용된다. 첫째, 공유 권한을 최소화한다. 일반 구성원에게는 Dashboard 시트만 "뷰어" 권한으로 공유하고, 편집 권한은 담당자 계정으로 한정한다. 둘째, 시트를 보호한다. Calc_Engine과 Dashboard의 수식 영역을 "범위 보호"로 잠가 실수로 삭제되거나 덮어써지지 않도록 한다. 셋째, 개인식별정보를 분리한다. 서술형 문항(I열)에 이름이나 사번을 적지 않도록 설문 안내에 명시하고, 수집된 의견은 18번 글의 가명처리 원칙에 따라 관리한다.

12. 단계적 도입 순서

문항과 시트 열 구조를 먼저 맞춘다.
응답 5~10건으로 함수를 검증한다.
조건부 서식과 대시보드를 완성한 뒤 실제 운영으로 전환한다.
LMS 활동 데이터를 연결해 위험점수까지 확장한다.
한 번에 모든 기능을 완성하려 하지 않고, 만족도 집계부터 안정화한 뒤 위험점수 연동으로 넘어가는 순서가 안전하다.

13. 가상 사례: (주)청솔제약의 집계 자동화 도입

가상의 제약회사 (주)청솔제약은 연간 24개 직무·법정 교육과정을 운영하며, 과정마다 담당자가 응답 파일을 내려받아 평균과 긍정응답비율을 수작업으로 계산해왔다.

항목 자동화 전 자동화 후
과정 1건당 집계 소요시간 50분 6분
연간 24개 과정 총 소요시간 1,200분(20.0시간) 144분(2.4시간)
저만족·위험군 확인 시점 과정 종료 후 평균 5일 응답 입력 즉시
연간 수식·집계 오류 발생 건수 7건 1건
연간 총 소요시간은 20.0시간에서 2.4시간으로 88.0% 줄었고, 절감된 17.6시간은 서술형 의견을 직접 읽고 원인을 해석하는 작업으로 재투자되었다. 위 수치는 실제 기업의 통계가 아니라 자동화 효과를 보여주기 위한 가상 사례다.

14. 실무 적용 체크리스트

Raw_Data, Calc_Engine, Dashboard 세 시트가 명확히 분리되어 있는가
설문 문항 순서와 시트 열 구조가 4대 영역 기준으로 일치하는가
영역별 평균과 긍정응답비율이 AVERAGEIF·COUNTIFS로 자동 계산되는가
응답 0건 상태에서 오류가 아니라 안전한 문구로 표시되는가
28번 글의 위험점수 공식이 같은 스프레드시트에서 계산되는가
조건부 서식으로 위험·관심·안전 신호와 영역 간 불균형까지 표시되는가
소규모 표본으로 수식 결과를 검증했는가
공유 권한과 시트 보호, 개인식별정보 분리가 되어 있는가

15. 정리

만족도 자동 집계 시스템의 핵심은 화려한 도구가 아니라, 원본·계산·화면을 분리하는 구조와 AVERAGEIF·COUNTIFS·IFERROR라는 몇 가지 함수를 정확히 조합하는 것이다. 여기에 28번 글의 위험점수 공식을 같은 시트에 얹으면, 만족도와 이탈 위험을 하나의 화면에서 함께 관리할 수 있다. 다만 이 시스템이 자동으로 처리할 수 있는 것은 숫자로 표현되는 정량 데이터뿐이며, 서술형 의견(I열)에 담긴 맥락은 여전히 사람이 읽어야 한다. 다음 글에서는 17번·18번 글의 원칙에 따라, 이 서술형 의견을 생성형 AI로 감정 분석하고 핵심 키워드를 추출하는 방법을 다룬다.