엑셀 설문지 통계 복수응답 처리 방법

예전에 블로그와 유튜브에 엑셀 설문지 통계 양식을 만드는 방법을 올렸었는데요. 많은 분들이 글과 영상을 시청해 주시고 계신데요. 기존 엑셀로 설문지 통계를 낼때 복수응답 처리와 시트 추가시 복잡한 수식으로 어려움이 많았었습니다.


이번에는 설문지 통계를 낼때 복수응답과 시트 추가시 어려웠던 부분을 개선해서 엑셀 설문지 통계 양식을 만들어 보았습니다.


엑셀 설문지 통계 복수응답 처리 방법

예전 설문지 통계 양식에서는 전체 통계에서 모두 계산이 되었지만 계산식이 조금 복잡하고 많았었는데요. 이번에는 해당 시트에서 간단하게 1차 통계를 내고 최종 통계에서는 1차 통계 자료를 합치는 방식으로 만들었습니다.


전체 통계 자료


설문지 응답자 수와 각 항목에 대한 해당 문항을 수를 먼저 설정을 하고 작업을 하시면 쉽게 작업을 하실 수 있습니다. 아래 영상을 보시면 보다 쉽게 이해하실 수 있으실 것입니다.


엑셀 설문지 양식 :  엑셀 설문지 2 복수응답.xlsx

(복수응답을 적을 때에는 쉼표","를 사용해주세요)


설문지 응답자 수를 추가하고 싶으신 분들은 L열을 오른쪽 마우스를 클릭한 다음 "삽입" 클릭하여 응답자를 열을 추가해 줍니다.


 K1셀을 선택한 다음 오른쪽으로 드래그하여 추가 시켜줍니다.



설문지 통계 및 복수응답 처리하기

이번에는 해당 문항에 대한 통계 처리와 복수응답 처리를 해보도록 하겠습니다. P2셀을 선택한 다음 아래 함수를 입력해 줍니다.

=COUNTIF($B2:$N2,P$1)+COUNTIF($B2:$N2,"*"&P$1&"*")




여기서 Countif 함수를 두번 사용했는데요. 첫번째는 단일 응답을 했을 때 갯수를 세는 식이고 두번째는 복수응답을 했을 때에는 숫자가 아니라 텍스트로 인식을 하기 때문에 "*" 와일드카드를 사용하여 셀 안에 있는 값을 인식하게 만드는 식입니다.


그래서 마지막에 두 식을 합하게 만들어 복수응답한 문항까지 통계를 내도록 만드는 것입니다. 그리고 열이나 행에 "$" 주소값을 넣어 준 이유는 나머지 값을 보다 쉽게 넣기 위해서 절대 주소를 적용시켜 준 것입니다.



P2셀에 입력이 끝났으면 오른쪽으로 드래그하여 나머지 값을 채워줍니다.



그리고 아래로 드래그 하여 나머지 값도 모두 채워줍니다.



첫번째 시트에서 모두 작업이 되었다면 A반 시트를 복사하여 B반, C반 시트를 추가해 줍니다. 그리고 통계에서 A반, B반, C반 시트에 있는 각 항목에 대한 합게를 구해줍니다.



이제 나머지 부분도 드래그하여 값을 채워 주면 모든 시트에 있는 합계를 구할 수 있습니다.



이제 간단하게 테스를 해보겠는데요. 첫 C2셀에 복수응답을 넣어 보겠습니다.


오른쪽 통계를 확인해 보면 문항1에 1번이 2개로 복수응답 한 값이 적용된 것을 보실 수 있습니다.



지금까지 엑셀 설문지 통게 복수응답 처리 방법을 알아보았는데요. 지금까지 엑셀로 통계를 내시는 분들에게 조금이라도 도움이 되었으면 좋겠습니다.

댓글(10)

  • lyw
    2019.04.10 14:09

    응답(답변)이 13까지만있을때 15까지늘리려면 어떻게 해야하나요

    • 2019.04.10 14:45 신고

      O열에 오른쪽 마우스를 클릭하시고 "삽입"을 클릭하면 응답자를 추가할 수 있어요~

  • 2019.04.10 17:01 신고

    엑셀 설문지 통게 복수응답 처리 방법이군요?
    엑셀로 통계를 내시는 분들에게 많은 도움이 될 것 같습니다^^

  • 2019.04.11 02:13 신고

    알면 알수록 정말 편리하네요.

  • 대현
    2019.04.12 13:59

    대디님자꾸 질문드려서 정말 죄송합니다...
    마지막 응답문항이 22개나 되는데 이거를 어떻게 해야 할까요 ㅠㅠ...

    • 2019.04.12 22:19 신고

      응답 문항이 20개라고 한다면 가장 쉬운 방법은 첫번째 A시트에서 응답문항을 더 추가해주시면 되여 A시트에 있는 Q열을 선택하고 오른쪽 마우스 눌러서 "삽입"을하여 10개의 열을 더 추가시켜 준 다음 A시트에 있는 응답문항 20열을 같은 방법을 수식을 넣어주고 A시트를 복사하여 B시트 C시트를 만들어 줍니다. 그리고 통계 시트에도 같은 방법으로 열을 추가해주고 수식을 넣어주면 해결이 될 것 같아요~^^

    • 대현
      2019.04.19 11:07

      정말 친절하게 알려주시는데 자꾸 질문드려 죄송할따름입니다.
      만나서 차라도 대접하고싶은심정입니다. ㅠㅠ...
      응답문항 10문항에서 22문항으로 늘린뒤 수식을 입력했는데 제가 원하는 건
      1,3,14 을 입력했을 때 1,3,14만 체크되기를 원하는데 1,3,4,14 가 체크되어버려서 같은 숫자가 들어가면 그 숫자도 같이 체크되어버리는 현상이 발생해서 머리가 터질것같네요 ㅠㅠ 직접 엑셀까지 만들어주셧는데 정말 죄송할따름입니다...

    • 2019.04.20 20:33 신고

      10단위가 넘어갔을 때 그런 일이 발생하는 걸 생각하지 못했네요~ 12번을 1,2, 12 이렇게 인식할 줄 몰랐습니다. 그래서 지금 방법을 알아보고 있는데요. 이것 저것 알아보고 있는데 ^^;; 어렵네요.. 저도 방법을 찾아보겠습니다. ^^;;

  • soo
    2019.04.30 11:46

    마지막 통계 탭에서 잘못 된게 맞는건지 궁금합니다.
    제가 초보라서 잘은 모르겠지만 통계탭 c2 셀 부분이 오류가 나서 보니까
    A반!B2+B반!P2+C반!P2 로 되어있어서 제가 셀 지정이 잘못된 듯해서
    A반!P2+B반!P2+C반!P2 로 바꾸었는데요. 이게 맞는건지 궁금합니다. ㅠ.ㅠ

    • 2019.04.30 14:10 신고

      soo 님이 맞습니다. 제가 확인해 보니 잘못 되어 있네요~ A반!P2가 맞습니다. ^^

Designed by JB FACTORY