[EXCEL][COUNTIF함수] 설문지 코딩하고 자동 통계 내기

학교에서 EXCEL을 사용하다가 '이런 기능 있으면 참~ 좋겠는데, 잘 모르겠네!'하는 것들이 있는데, 그 중 하나가 학생들을 대상으로 작성하는 설문지를 통계낼 때 뭔가 편리한 방법이 있지 않을까 하는 것입니다.

 

 

현장체험학습을 다녀온 후 학생 만족도를 조사하거나 각종 설문지를 학급별 혹은 학년별로 통계를 내서 제출해야 할 때가 있습니다. 이 때 학생들이 작성한 설문지에서 문항별, 응답별로 몇 명이 응답했는지 세기 위한 방법은 여러 가지가 있습니다.

 

A교사(설문 후 학생 실셈) : 1번에 ① 표시한 사람 손 들어봐! 하나, 둘, 셋, 넷, ……, 아~ 너는 왜 이제서야 손 드니? 다시! 하나, 둘, 셋,…

B교사(Plicker 애플리케이션 활용) : 우리는 스마트하게 Plicker 앱으로 세겠다. 응답지 바르게 들어라. 아~ 누가 똑바로 안 들었어? 다시! 이것도 은근 힘드네.

 

C교사(Google 설문지 활용) : 설문지 Goolge 설문지로 만들었으니까 스마트폰 꺼내서 응답해라. (선생님, 전 스마트폰 아닌데요.)

 

D교사(엑셀에 코딩 후 자동 통계) : 설문지에 표시해서 선생님한테 제출하세요.

 

이 글을 읽고 계신 분들은 A, B, C, D교사 유형 중 어디에 속하시나요?

물론 이것 말고도 다른 방법을 적용하시는 분들도 계시겠죠?

 

저는 초임 때는 A교사 같이 했습니다. 그래서 학생들이 설문지 작성하는 데 10분(여러 장일 경우는 1시간도 걸렸습니다.), 다시 문항별, 보기별 몇 명이 응답했는지 확인하는데 1시간이 걸려서 수업을 못 하고 넘어가는 경우가 많았습니다. 설문지 통계와 수업이 주객전도 되어 '이건 아니다!' 싶은 방법이었습니다.

 

B와 C 방법은 한창 스마트교육이 학교에 침투했을 때 써 본 방법입니다.

Plicker라는 애플리케이션은 오프라인에서도 가능한 앱으로 1~40명의 참가자가 ABCD가 적힌 응답지를 방향을 돌려가며 보여주었을 때, 스마트폰 카메라로 스캔하면서 그 응답 결과를 자동으로 세어 보여주는 애플리케이션입니다.

필요하신 분들은 아래 이미지를 클릭하면 Google Play로 넘어갑니다.

 

Plickers

 

그리고 C유형은 Google 문서도구 중 설문지에 응답하는 방식으로 동시에 여러 학생이 접속 가능하지만 문항 제작과 배포에 있어서 제한사항이 몇 가지 있습니다.

일단 문항 작성을 어려워하시는 분들이 많고, 학생들이 개인별 디바이스(스마트폰이나 태블릿, 크롬북 등)를 갖고 있어야 합니다. 그런데 아직 수업 중 스마트폰을 꺼내는 걸 꺼려하시는 분들이 많아서 기술적 문제보다 정서상 극복해야 할 점이 좀 있습니다.

고학년이라면 Google 설문지도 제법 괜찮은 방법입니다. 하지만 스마트폰 사용이 어려운 중저학년 학생을 대상으로 하기엔 무리가 있죠.

 

 

 

사설이 길었습니다. 오늘 안내하려는 방법은 D교사가 사용한 EXCEL을 이용한 설문지 코딩으로 통계내기입니다. 여기에 들어갈 함수는 COUNTIF입니다.

=COUNTIF(범위, 검색 대상)

 

EXCEL에서 COUNTIF 함수 위치

 

 

다음은 실제 우리 반 학생들의 현장체험학습 후 만족도 조사를 코딩한 내용입니다.

 

 

(B4:R13)까지는 학생들이 설문지에 작성한 응답값을 그대로 코딩한 것입니다.

가로열은 학생 번호이며, 세로행은 문항 번호입니다. 응답값은 매우만족 5점, 만족 4점, 보통 3점, 불만 2점, 매우불만 1점으로 해당 값을 학생 번호에 따라 그대로 셀에 넣었습니다.

여기까지는 아직 통계가 아니라 설문지 코딩입니다.

 

실제 통계는 우측에 있는 (T4:X13) 부분입니다.

T4에 있는 셀을 클릭하면 다음과 같이 함수가 기록돼 있습니다.

=COUNTIF(B4:R4,5)

이 함수를 설명하면 "B4부터 R4까지의 범위 중에, 숫자 5가 몇 번 있는지 세어서 나타내소."입니다. 어렵지 않습니다.

 

그렇다면 그 옆에 있는 U4셀은 뭐라고 수식을 입력했을까요?

=COUNTIF(B4:R4,4) 입니다.

B4부터 R4까지의 범위 중에서 숫자 4가 몇 번 있는지 세어보는 것이기 때문입니다.

 

COUNTIF는 범위 내에서 검색대상을 찾는 함수이기 때문에 함수가 있는 셀에서 자동 끌기를 하다보면 범위가 옆으로 밀릴수 있습니다. 그러므로 셀에 입력한 데이터의 성격에 맞게 셀을 위아래 혹은 좌우로 한 방향으로만 끌어서 쓰든지 절대참조($)를 사용하는 방식으로 작성해야 합니다.

 

COUNTIF 함수를 이용하여 제가 속한 2학년 1, 2, 3반 학생들의 체험학습 만족도를 퍼센트로 계산하여 나타내면 다음과 같습니다.

 

 

우리 학교 2학년은 세 반만 있는 관계로 엑셀을 만들기는 어렵지 않았습니다.

마지막 시트인 통계는 문항별로 각 반 시트의 자료를 종합하여 비율로 계산한 것입니다.

 

저도 엑셀을 잘 아는 편은 아니지만 학교에서 유용하게 쓰일 만한 자료들을 지속적으로 업데이트 해 보도록 하겠습니다.

 

 

COUNTIF+예시.xlsx

0.01MB

만족도+조사+결과(학생).xlsx

0.02MB

만족도+조사+결과(교사).xlsx

0.02MB

COUNTIF_50명_보기6개.xlsx

0.01MB