버튼으로 클릭하는 차트 만들기

실습 가이드

버튼 클릭 자동 업데이트 차트 목차 바로가기

강의 요약

엑셀로 보고서를 작성할 때 여러 항목을 하나의 차트에 모두 담으면 가독성이 떨어지고, 보고를 받는 사람이 어떤 수치가 핵심인지 직관적으로 파악하기 어렵습니다. 이런 상황에서는 피벗테이블과 슬라이서를 활용해 버튼을 클릭할 때마다 실시간으로 갱신되는 자동 업데이트 차트를 만들어 두면, 대시보드 형태의 간결하고 명료한 보고서로 발전시킬 수 있습니다.

이번 강의에서는 엑셀 대시보드 제작에서 가장 핵심이 되는 스킬인 자동 업데이트 차트 만들기 방법을 단계별로 살펴보겠습니다.

영상강의

엑셀 버튼 클릭 자동 업데이트 차트 만들기

1. 차트를 생성하기 위한 백데이터를 취합합니다. 예제파일을 다운로드한 뒤, 차트의 원본이 될 다음 3개 열을 생성합니다.
   

   열 이름	설명
   판매점	판매점의 고유값이 나열되는 열입니다.
   총 매출액	각 판매점별 총 매출액이 입력되는 열입니다.
   총 매출액 클릭	버튼으로 특정 판매점을 클릭했을 때, 해당 판매점의 총 매출액만 출력되는 열입니다.

2. 판매점 열을 입력합니다. 예제시트의 B열을 복사해 J열에 붙여넣은 뒤, [데이터] - [중복된 항목 제거]를 통해 판매점의 고유값만 필터링합니다.

   

3. SUMIF 함수를 사용해 판매점별 총 매출액을 계산합니다. 총 매출액 열에 아래 수식을 붙여넣은 뒤, 아래 방향으로 자동 채우기를 적용합니다.
   =SUMIF(B:B,J2,C:C)

   

4. '총 매출액 클릭' 열의 값은 모두 '0'으로 채워줍니다.

   

5. '총 매출액'과 '총 매출액 클릭' 두 열을 함께 선택한 뒤, '셀 서식'을 변경합니다. (단축키 Ctrl + 1) 아래 서식 코드를 복사해 '표시형식' - '사용자지정' 입력란에 붙여넣어 마무리합니다.
   #,##0;;

   셀 서식의 동작 원리에 대한 자세한 설명은 기초입문강의에서 다루었습니다.
   

   

1. 취합된 데이터 범위를 선택한 뒤, '삽입' - '추천차트'를 클릭해 '세로막대형 그래프'를 삽입합니다.

   

2. 차트를 우클릭한 뒤, '차트 종류 변경' - '혼합'으로 이동합니다. 이후 '총 매출액 클릭' 항목을 '보조축'에 체크하고, 차트 종류 역시 '세로막대형 그래프'로 선택합니다.

   

3. 다음 가이드에 따라 차트 서식을 변경합니다.
   - 차트제목, 세로축(보조축 포함), 눈금선, 범례를 삭제합니다.
   - 데이터레이블을 추가합니다.
   - 데이터레이블의 글꼴 크기는 작게 변경합니다.
   - '총매출액'의 글꼴색상은 회색, '총매출액 클릭'의 글꼴색상은 남색으로 변경합니다.
   - 각각의 세로막대 채우기 색상도 글꼴색상과 동일한 색상으로 변경합니다.

   

1. 원본 데이터(A~C열)를 선택한 뒤, '삽입' - '피벗테이블'을 추가합니다. (단축키 Alt - N - V) 생성 위치는 기존 워크시트의 'E1'셀로 지정합니다.

   

2. 피벗테이블을 클릭한 뒤, '판매점' 항목을 '행 필드'로 이동합니다.

   

3. 피벗테이블이 선택된 상태에서 '삽입' - '슬라이서'를 클릭한 뒤, '판매점'에 대한 슬라이서를 추가합니다.

   

4. 슬라이서를 선택한 뒤, '슬라이서 도구' - '디자인'에서 원하는 디자인을 선택하고 '중복'을 클릭합니다.

   

5. 이어서 '전체 슬라이서' - '서식' - '글꼴'을 8 로 변경하고, '테두리' - '테두리 없음'으로 변경합니다.

   

6. 새롭게 생성된 슬라이서 스타일로 변경합니다.

   

7. 슬라이서를 우클릭한 뒤, '슬라이서 서식'에서 '머릿글 표시' 체크박스를 비활성화합니다. 그리고 '데이터가 없는 항목 숨기기' 체크박스는 활성화합니다.

   

1. '1-4' 단계에서 추가한 '총 매출액 클릭' 열에 COUNTIF 함수를 사용해, 다음 조건에 따라 값이 출력되도록 수식을 입력합니다.
   ' - 피벗테이블로 출력된 목록에 해당 판매점이 있을 경우 : 총 매출액
   ' - 판매점이 목록에 없을 경우 : 0
   =IF(COUNTIF(E:E,J2)>0,K2,0)

   

1. 원본 데이터(A~C열)를 선택한 뒤, 피벗테이블을 추가합니다. 기존 차트 영역에 추가한 다음, 아래와 같이 피벗테이블 필드를 배치합니다.
   - 열필드 : [판매점]
   - 행필드 : [판매일]
   - 값필드 : [매출액] '// 우클릭 후 - '값 필드 설정' 에서 '표시형식'을 [ #,##0;; ] 으로 변경합니다.

   

2. 추가된 피벗테이블을 선택한 뒤, '삽입' - '추천차트'로 보조 차트를 추가합니다. 이번에는 '꺾은선형 그래프'를 선택합니다.

   

3. 다음 가이드에 따라 차트 서식을 변경합니다.
   - 범례, 세로축, 계열, 눈금선을 제거합니다.
   - 차트 위 버튼을 우클릭한 뒤, '모든 필드단추 숨기기'를 선택해 모든 피벗필드 단추를 제거합니다.
   - 데이터레이블을 추가합니다.
   - 차트제목과 데이터레이블의 글꼴 크기 및 색상을 알맞게 변경합니다.

   

1. 슬라이서를 우클릭한 뒤, '보고서 연결'을 선택합니다.

   

2. 새롭게 추가한 피벗테이블을 체크한 뒤 '확인'을 눌러 슬라이서에 두 개의 피벗테이블을 모두 연동시켜줍니다.

   

3. 1단계에서 생성한 표를 우클릭한 뒤, '데이터' - '필터'를 추가합니다. 이후 판매점을 '오름차순'으로 정렬합니다.

   

7. 버튼 클릭 자동업데이트 차트 완성

1. 슬라이서를 클릭한 뒤, '슬라이서' - '단추' 의 열 개수를 9개로 변경합니다.

   

2. 버튼을 클릭하면 자동으로 업데이트되는 차트가 완성되었습니다.