메뉴
실무 위키›응용 공식›복수응답 설문 집계 공식

복수응답 설문 집계 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

항목 경계를 지켜 부분 일치 오집계를 막습니다.

예제파일 다운로드
복수응답 설문 집계 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

01보기별 선택 인원 세기

=SUMPRODUCT ( --ISNUMBER ( SEARCH ( "," & 보기 & ",", "," & SUBSTITUTE ( 응답 범위, " ", "" ) & "," ) ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
보기필수선택 인원을 셀 보기 문구가 입력된 셀입니다. 응답에서 공백을 지운 문구와 같아야 합니다.
응답 범위필수응답자별 선택 항목이 콤마로 묶여 있는 범위입니다. 수식이 항목 사이의 공백을 지우므로 콤마 뒤 공백 유무가 섞여 있어도 집계할 수 있습니다.
","필수보기와 응답의 양끝에 붙여 항목 경계를 만드는 고정 구분 기호입니다. 같은 경계로 감싸면 문자를 셀 때 문자메시지는 포함되지 않습니다.
E2=SUMPRODUCT(--ISNUMBER(SEARCH(","&D2&",",","&SUBSTITUTE($B$2:$B$9," ","")&",")))
A
B
C
D
E
F
G
1
응답자
선택한 보기
보기
선택 인원
선택 비율
2
김서준
이메일, 문자, 전화
문자
4
50%
3
이하은
이메일, 문자메시지
문자메시지
3
37.5%
4
박도윤
문자, 전화
이메일
5
62.5%
5
최지호
이메일, 전화
전화
5
62.5%
6
정유진
문자메시지, 전화
7
한지민
이메일, 문자
8
오세훈
이메일, 문자메시지, 전화
9
윤지아
문자
10
보기 이름은 응답에 쓰인 문구와 같아야 합니다. 양끝과 항목 사이를 콤마로 감싸 찾으므로 문자를 집계할 때 문자메시지는 포함하지 않습니다. 선택 비율의 분모는 응답자 8명입니다.

02전체 응답자 대비 선택 비율 구하기

=선택 인원 / COUNTA ( 응답자 범위 )
인수 설명 자세히 보기
인수구분설명
선택 인원필수해당 보기를 선택한 응답자 수가 입력된 셀입니다.
응답자 범위필수전체 응답자 이름이 빈칸 없이 입력된 범위입니다. 한 사람이 한 행을 사용해야 합니다.
F2=E2/COUNTA($A$2:$A$9)
A
B
C
D
E
F
G
1
응답자
선택한 보기
보기
선택 인원
선택 비율
2
김서준
이메일, 문자, 전화
문자
4
50%
3
이하은
이메일, 문자메시지
문자메시지
3
37.5%
4
박도윤
문자, 전화
이메일
5
62.5%
5
최지호
이메일, 전화
전화
5
62.5%
6
정유진
문자메시지, 전화
7
한지민
이메일, 문자
8
오세훈
이메일, 문자메시지, 전화
9
윤지아
문자
10
보기 이름은 응답에 쓰인 문구와 같아야 합니다. 양끝과 항목 사이를 콤마로 감싸 찾으므로 문자를 집계할 때 문자메시지는 포함하지 않습니다. 선택 비율의 분모는 응답자 8명입니다.
이 공식이 사용하는 함수
COUNTA 함수 ISNUMBER 함수 SEARCH 함수 SUBSTITUTE 함수 SUMPRODUCT 함수

동작 원리

01보기별 선택 인원 세기

SUBSTITUTE 함수로 공백을 지운 뒤 SEARCH 함수가 콤마로 감싼 보기의 위치를 찾고, ISNUMBER 함수와 SUMPRODUCT 함수가 찾은 응답의 개수를 셉니다.

예제 시트에서 문자 선택 인원을 계산하는 E2 셀을 추적합니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

항목 경계를 포함한 검색 위치를 찾습니다

SUBSTITUTE 함수가 응답의 공백을 지우고, SEARCH 함수가 각 응답의 양끝에 콤마를 붙여 문자 항목의 위치를 찾습니다. 문자메시지에는 문자 뒤 경계가 없으므로 찾지 않습니다.

=SEARCH ( "," & D2 & ",", "," & SUBSTITUTE ( $B$2:$B$9, " ", "" ) & "," )
= {5;#VALUE!;1;#VALUE!;#VALUE!;5;#VALUE!;1}
2

찾은 위치만 참으로 바꿉니다

ISNUMBER 함수가 검색 위치가 숫자인 응답은 TRUE, 찾지 못한 오류는 FALSE로 바꿉니다.

=ISNUMBER ( {5;#VALUE!;1;#VALUE!;#VALUE!;5;#VALUE!;1} )
= {TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
3

참과 거짓을 1과 0으로 바꿉니다

이중 마이너스 기호가 TRUE는 1, FALSE는 0으로 바꿔 더할 수 있는 숫자 배열을 만듭니다.

=--{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
= {1;0;1;0;0;1;0;1}
4

선택한 응답의 개수를 더합니다

SUMPRODUCT 함수가 숫자 배열을 모두 더하면 문자를 선택한 응답자는 4명입니다.

=SUMPRODUCT ( {1;0;1;0;0;1;0;1} )
= 4
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료