엑셀 성별 그래프 만들기

by 오빠두엑셀

학습시간 27분
난이도 초급
작성일 2020.10.25

사용자 점유율 데이터 시각화에 최적화 된 엑셀 성별 & 연령대별 그래프 만들기

이 강의에서는 피벗테이블과 슬라이서로 집계한 사용자 점유율 데이터를 바탕으로 성별 그래프와 연령대별 그래프를 제작하는 방법을 다룹니다. 피벗차트로는 만들기 어려운 혼합차트를 보조 데이터 범위와 도형 기능으로 구현하고, 막대 그래프 위에 아이콘을 채워 데이터가 갱신되어도 자동으로 반영되는 대시보드형 차트로 마무리하는 과정을 단계별로 정리합니다.

DOWNLOADS

실습자료를 준비했어요

수업에서 사용한 예제 파일과 보충 자료를 한 곳에 정리했습니다!👇

실습파일.xlsx

로그인 후 다운로드

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행되는 라이브강의 풀영상을 확인하실 수 있습니다.

이번 강의에서는 피벗테이블과 슬라이서를 활용하여 실시간으로 업데이트되는 사용자 점유율 차트를 제작합니다. 피벗테이블에서 바로 만들어지는 피벗차트로는 혼합차트를 구성할 수 없으므로, 피벗 결과를 참조하는 보조 데이터 범위를 별도로 만든 뒤 해당 범위를 기반으로 성별 그래프와 연령대별 그래프를 단계별로 완성합니다.

예제파일의 [게임사용설문조사] 시트로 이동한 뒤 전체 범위를 선택합니다. 이후 [삽입] - [피벗테이블]을 클릭하거나 단축키 ALT - N - V를 눌러 피벗테이블 만들기 대화상자를 실행합니다.
'기존 워크시트'를 선택한 뒤, 피벗테이블을 삽입할 위치로 [나이성별그래프] 시트의 A1셀을 지정합니다. [확인] 버튼을 클릭하면 해당 위치에 피벗테이블이 삽입됩니다.
아래 표와 같이 피벗테이블 필드를 설정합니다. 이후 [사용여부] 필터를 "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 범위를 선택한 뒤 우클릭 - [셀 서식]으로 이동하여 사용자 지정 표시형식을 아래와 같이 입력합니다. 사용자 지정 서식 작성 방법은 아래 엑셀 기초 입문강의에서 자세히 정리해두었습니다.
0%;;;
이제 성별 그래프와 연령대별 그래프 제작에 필요한 데이터 준비가 모두 끝났습니다.

F4:H6 범위를 선택한 뒤 [삽입] - [추천차트] - [묶은 세로막대형]을 클릭하거나, 단축키 ALT - N - R로 추천차트 창을 연 뒤 세로막대형 차트를 선택합니다. (남/여가 가로축으로 구분된 형태의 차트를 골라야 한다는 점에 주의합니다.)
시트 오른쪽에 미리 준비된 사람 모양 아이콘을 각 차트 위로 붙여 넣습니다.

(엑셀 2016 이후 버전 사용자는 [삽입] - [일러스트레이션] - [아이콘] 메뉴에서 원하는 모양의 아이콘을 직접 삽입할 수 있습니다.)

(기존 아이콘은 좌·우로 여백이 들어가 있는 경우가 있으므로, 여백이 두드러질 때는 우클릭 - [도형으로 변환]을 선택해 여백을 정리합니다.)
차트를 우클릭한 뒤 [차트 종류 변경]으로 이동합니다. [혼합] 탭에서 두 계열을 모두 '묶은 세로막대형'으로 변경한 뒤, 사용율 계열을 보조축으로 지정합니다. [확인] 버튼을 클릭하면 혼합차트가 생성됩니다.
색상이 채워진 남자 아이콘 막대를 선택합니다. 이후 우클릭하여 [데이터 요소 서식] - [채우기]로 이동한 뒤, 채우기 옵션을 '다음 배율에 맞게 쌓기'로 변경합니다. 여자 아이콘 막대도 같은 방식으로 적용합니다.
양쪽 세로축의 최대값을 120%(=1.2)로 동일하게 맞춰줍니다. 이후 [레이블] 옵션에서 레이블 위치를 '없음'으로 설정하여 세로축을 숨깁니다.
막대를 우클릭하여 [데이터 계열 서식]으로 이동한 뒤, [전체] 계열과 [사용율] 계열의 간격 너비를 각각 80%로 변경합니다.
회색 막대(전체 계열)를 우클릭한 뒤 [데이터 레이블 추가]를 선택해 레이블을 표시합니다.
추가된 레이블을 우클릭한 뒤 [데이터 레이블 서식]으로 이동합니다. 레이블 내용에서 [셀 값]에 체크하고, 셀 값 범위로 사용율 범위(G5:G6)를 지정합니다. 이후 [값]과 [지시선 표시] 옵션은 체크를 해제합니다.
마지막으로 차트 제목과 눈금선, 범례를 모두 제거하고 글꼴 서식을 정돈하면 엑셀 성별 그래프 제작이 완료됩니다.

[나이성별그래프] 시트의 J4:J8 범위(연령대)를 먼저 선택한 뒤, 키보드 CTRL 키를 누른 채로 L4:M8 범위(최댓값·나머지)까지 동시에 선택합니다. (이번 차트에는 사용율 원본 범위가 필요 없으므로 제외하고 선택합니다.)
[삽입] - [추천차트]로 이동하거나 단축키 ALT - N - R로 추천차트 창을 엽니다. [혼합] 탭에서 두 계열을 모두 세로막대형으로 지정하고, 둘 중 한 계열을 보조축으로 설정한 뒤 차트를 생성합니다.
양쪽 세로축을 각각 우클릭한 뒤 [축 서식]으로 이동합니다. 축의 최대값을 100%(=1)로 통일하고, 레이블 위치를 '없음'으로 설정하여 세로축을 숨깁니다.
차트의 제목, 눈금선, 범례를 모두 제거한 뒤 막대 채우기 색상을 의도한 톤으로 변경합니다.
막대를 우클릭한 뒤 [데이터 레이블 추가]를 선택해 각 막대 위에 레이블을 표시합니다. 이후 레이블 글꼴 서식을 정돈하면 엑셀 연령대별 그래프가 완성됩니다.

앞서 생성한 두 차트를 각각 선택한 뒤, [차트 영역 서식]에서 [채우기]와 [윤곽선]을 모두 '없음'으로 설정합니다.
[삽입] - [도형]에서 직사각형을 그린 뒤 두 차트가 모두 들어가도록 크기를 맞춥니다. 이후 도형의 채우기 색상을 옅은 회색, 윤곽선은 '없음'으로 지정합니다.
도형을 우클릭한 뒤 [맨 뒤로 보내기]를 선택하면 도형이 차트 뒤쪽으로 배치됩니다.
[삽입] - [도형]에서 직선을 추가합니다. 차트 크기와 높이에 맞춰 직선의 위치를 정돈합니다. (키보드 CTRL·SHIFT 키와 정렬 기능을 함께 활용하면 직선을 더 정확하게 배치할 수 있습니다.)
키보드 SHIFT 키를 누른 채로 두 차트를 함께 선택한 뒤, [맨 앞으로 가져오기]를 클릭하면 차트가 도형 앞쪽으로 이동합니다.
엑셀 성별 그래프와 연령대별 그래프가 모두 완성되었습니다. 보고용으로 활용할 경우 차트 제목과 범례, 간단한 설명을 추가하면 한층 완성도 있는 시각화 자료가 됩니다.