이번 강의에서는 피벗테이블과 슬라이서를 활용하여 실시간으로 업데이트 되는 차트를 제작합니다. 단, 피벗테이블에서 바로 생성되는 피벗차트로는 혼합차트를 만드는데에 제약이 있으므로, 피벗테이블에서 생성된 데이터를 참조하는 범위를 별도로 생성한 뒤, 그 범위를 참조하여 성별 그래프 & 연령대별 그래프를 제작합니다.
예제파일의 [게임사용설문조사] 시트로 이동한 뒤, 전체 범위를 선택합니다. 이후 [삽입] - [피벗테이블]을 클릭하거나 또는 단축키 ALT - N - V 로 피벗테이블 만들기 대화상자를 실행합니다.
삽입 - 피벗테이블로 피벗테이블을 추가합니다.
'기존 워크시트'를 선택한 뒤, 피벗테이블을 삽입할 범위로 '나이성별그래프' A1셀을 선택한 뒤 [확인] 버튼을 클릭하여 피벗테이블을 삽입합니다.
기존 워크시트 - 나이성별그래프 시트의 A1셀에 생성합니다.
아래 표와 같이 피벗테이블 필드를 설정합니다. 이후 사용여부를 "O"로 필터링하면 각 나이별 사용자 현황이 집계됩니다.
필드
항목
필터
사용현황
열
성별
행
나이
값
이름
위 설정과 동일하게 피벗테이블을 생성한 뒤, 사용여부를 O로 필터링합니다.
나이를 각 연령대별(20~29, 30~39, 40~49, ..)로 묶어줍니다. 나이열을 우클릭한 뒤, [그룹]으로 이동합니다. 이후 그룹의 옵션을 아래와 같이 설정하면 나이가 각 연령대별로 그룹화됩니다.
나이를 그룹화합니다.
F4:H6 범위의 성별 사용율을 계산합니다.
항목
수식
남자사용율
B9/D9 (남자사용자합계/총합계)
여자사용율
C9/D9 (여자사용자합계/총합계)
전체
100%
각 성별 사용율을 계산합니다.
동일한 방법으로 연령대별 사용율을 계산합니다.
연령대별 사용율을 계산합니다.
IF 함수와 MAX 함수를 활용하여 연령대별 사용율 중 최대값과 나머지 값을 서로 다른 열에 표시합니다. 각 셀에 아래 함수를 입력한 뒤, 아래로 자동채우기 합니다.
셀
수식
L5셀
=IF(K5=MAX($K$5:$K$8),K5,0)
M5셀
=IF(L5=0,K5,0)
연령대별 사용율의 최대값과 나머지 값을 구분합니다.
0%는 차트 위에 표시되지 않아도 되는 값이므로, L4:M8 범위를 선택한 뒤 우클릭 - [셀 서식]으로 이동하여 사용자지정 표시형식을 아래와 같이 변경합니다. 엑셀 사용자 지정 서식은 엑셀 기초 입문강의에서 자세히 다뤄드렸습니다.
감사합니다.
근데 성별비율에서 성별이 남자만 있거나 여자만 있는경우가 있는데요
이럴경우는 어떻게 하는게 좋을까요?
답변이 도움이 되셨길 바랍니다.
워라벨 실현이 곧 눈앞이네요
선택범위를 그룹으로 묶을 수 없다는 오류메세지가 뜹니다 ㅠ
연령대에 작성된 데이터가 모두 '숫자'로 올바른지 한번 확인해보시길 바랍니다.
감사합니다.