엑셀 버튼 클릭 자동 업데이트 차트 만들기 :: 직장인 필수 스킬

버튼을 클릭하면 색상이 변하면서 자동으로 업데이트 되는 차트를 제작합니다.

홈페이지 » 엑셀 버튼 클릭 자동 업데이트 차트 만들기 :: 직장인 필수 스킬

엑셀 버튼 클릭 자동 업데이트 차트 만들기 :: 직장인 필수팁

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

강의 요약

엑셀로 보고서를 작성할 때, 여러개의 항목이 한 차트에 들어갈 경우 보고서의 가독성이 떨어지고 보고를 받는 사람 입장에서 어느 것이 중요한 내용인지 그 포인트를 종종 놓치게 됩니다. 이럴 경우, 피벗테이블과 슬라이서를 사용하여 버튼을 클릭할 때 실시간으로 업데이트 되는 자동 업데이트 차트 를 만들어 보고하면, 대시보드 형태로 더욱 간결한 보고서를 작성할 수 있는데요.

엑셀 클릭 자동 업데이트 차트 완성
버튼을 클릭하는 형태의 자동 업데이트 차트로 보고서를 작성해보세요!

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

영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [차트강의] 버튼으로 클릭하는 자동화차트 만들기
    예제파일
  • [차트강의] 버튼으로 클릭하는 자동화차트 만들기
    완성파일

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

1. 데이터 취합 후 기본 차트 만들기

  1. 차트를 생성하기 위한 백데이터를 취합합니다. 예제파일을 다운받으신 뒤, 차트를 위한 백데이터로 아래 3개의 열을 생성합니다.
    열 이름 설명
    판매점 판매점의 고유값이 나열된 열입니다.
    총 매출액 각 판매점별 총 매출액입니다.
    총 매출액 클릭 버튼으로 해당 판매점을 클릭하면, 해당 열에만 총 매출액이 출력됩니다.
  2. 판매점 열을 입력합니다. 예제시트의 B열을 복사하여, J열에 붙여넣기 한 뒤, [데이터] - [중복된 항목 제거]로 판매점의 고유값만 필터링합니다.

    자동 업데이트 차트 원본 데이터
    판매점 열을 복사하여 J열에 붙여넣기 한 뒤, 중복된 항목을 제거합니다.

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

    판매점별 합계
    판매점 별 매출액 합계를 계산합니다.

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

    클릭 차트 데이터
    총 매출액 데이터를 모두 '0'으로 채워줍니다.

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

    셀서식에 대한 내용은 기초입문강의에서 자세히 설명드렸습니다.

    셀 서식 변경
    총 매출액과 총매출액 클릭의 셀 서식을 변경합니다.

2. 기초 차트 만들기

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

    세로막대형 차트 만들기
    취합된 데이터 범위를 선택한 후, 세로막대형 차트를 추가합니다.

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

    자동 업데이트 차트 보조축
    총 매출액 클릭을 차트의 보조축으로 설정합니다.

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

    차트 레이아웃 변경
    차트의 레이아웃을 변경합니다.

3. 피벗테이블 및 슬라이서 만들기

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

    피벗테이블 생성
    원본데이터(A~C열)을 선택 후 피벗테이블을 생성합니다.

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

    피벗테이블 필드 변경
    피벗테이블을 선택한 후, '판매점' 항목을 행필드로 이동합니다.

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

    슬라이서 추가
    새로운 슬라이서를 추가합니다.

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

    슬라이서 꾸미기
    새로운 슬라이서 디자인을 생성합니다.

  5. 이후 '전체슬라이서' - '서식' - '글꼴' 을 8 로 변경합니다. 또한 '테두리' - '테두리 없음'으로 변경합니다.

    슬라이서 디자인 변경
    새로운 슬라이서의 디자인을 설정합니다.

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

    슬라이서 디자인 적용
    슬라이서를 선택한 후, 새로운 슬라이서 스타일을 적용합니다.

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

    슬라이서 설정 변경
    슬라이서 설정을 변경합니다.

4. 총 매출액 클릭 데이터 추가하기

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

    자동 업데이트 차트 클릭 데이터
    '총 매출액 클릭' 값을 계산하기 위한 수식을 추가합니다.

5. 판매점별 월별 매출액 출력을 위한 보조 차트 만들기

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

    피벗테이블 추가 생성
    새로운 피벗테이블 생성 후, 필드를 그림과 같이 이동합니다.

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

    보조 자동 업데이트 차트 생성
    새로추가된 피벗테이블에서 '꺾은선형 그래프'를 추가합니다.

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

    보조 차트 만들기
    차트가 더욱 간결해지도록 레이아웃을 변경합니다.

6. 슬라이서와 새로운 피벗테이블 연동하기

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

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

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

    보고서 연결 완료
    2개의 피벗테이블을 모두 슬라이서에 연동시킵니다.

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

    자동 업데이트 차트 원본 데이터 정렬
    취합 된 데이터에 필터를 추가한 뒤, '판매점' 기준 오름차순으로 정렬합니다.

7. 버튼 클릭 자동업데이트 차트 완성
  1. 슬라이서를 클릭한 뒤, '슬라이서' - '단추' 의 열 개수를 9개로 변경합니다.

    슬라이서 열 개수 변경
    슬라이서의 단추 개수를 9개로 변경합니다.

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

    엑셀 클릭 자동 업데이트 차트 완성
    자동 업데이트 차트가 완성되었습니다.

5 26 투표
게시글평점
guest
40 댓글
Inline Feedbacks
모든 댓글 보기
박상하
박상하
2020년 3월 11일 1:58 오전
게시글평점 :
     

안녕하세요 세번쨰 피벗 테이블을 만들떄 행 항목에 판매일을 넣을경우 달로 영상에 나오는것처럼 달로 정렬되지 않고 일별로 정렬이 됩니다. 월로 정렬하는 방법이 있을까요. 기존 표에서 mid함수나 이런것으로 월을 따로 빼서 하는… 더보기 »

sksnadl
sksnadl
2020년 3월 22일 6:51 오전
게시글평점 :
     

챠트 많이 사용은 하지만 이강의는 유듀브에서 반복 보곤 하네요. 정말 좋은 강의 입니다.

전상용
전상용
2020년 3월 24일 11:18 오전
게시글평점 :
     

최고입니다!

JY
JY
2020년 3월 25일 11:19 오전
게시글평점 :
     

차트를 이런식으로 사용하여 강조할수있다고 생각지 못했는데 좋은정보 정말 감사합니다.

갓쩌리
갓쩌리
2020년 4월 3일 2:21 오후
게시글평점 :
     

오피스2010을 사용하는 사람으로써 디자인이 참 부럽습니다.

제러드
제러드
2020년 4월 8일 8:47 오전
게시글평점 :
     

너무 좋다....

남궁완
남궁완
2020년 4월 9일 3:11 오후
게시글평점 :
     

슬라이서를 만들어야 하는데 슬라이서 삽입이 전혀 안되네요... 삽입 메뉴에 들어가도 슬라이서는 클릭 자체가 안되는데 왜 그런걸까요

흑룡
흑룡
2020년 4월 13일 8:25 오후
게시글평점 :
     

한번 봐선 도저히 ㅋㅋ 대단하시네요

주꿈이
주꿈이
2020년 4월 15일 11:49 오후
게시글평점 :
     

예제파일이 차트1강 파일로 다운받아지는데 제노트북이 이상한걸까요?ㅠ

Philip
Philip
2020년 4월 19일 10:40 오후
게시글평점 :
     

많은 도움 되었어요. 엑셀이 너무 재미있어짐 ㅎㅎ

그레이스
그레이스
2020년 4월 23일 1:59 오후
게시글평점 :
     

대박입니다..

김지현
김지현
2020년 5월 2일 5:06 오전
게시글평점 :
     

설명 너무 깔끔하게 잘 해주셔서 잘 따라왔는데 왜 저는 슬라이서 '보고서 연결' 을 누르면 새로 만든 피벗테이블 옵션이 아예 뜨지가 않는 걸까요? ㅜㅜ 원인이 뭘까요? ㅜㅜ 영상 정말 잘 보고있습니다!… 더보기 »

루카대디
루카대디
2020년 5월 15일 6:50 오후
게시글평점 :
     

안녕하세요~
슬라이서에서 중복 선택을 하여 2개 이상의 수치를 비교하고 싶습니다.
필터 해제 부분은 자동으로 뜨는데... 다중선택은 자동으로 안뜨네요.
다중선택 아이콘이 안뜰때는 어떻게 하나요?

진리*
진리*
2020년 5월 16일 1:14 오후
게시글평점 :
     

감사합니다

daef****
2020년 5월 26일 10:01 오후
게시글평점 :
     

정말 감사한 마음으로 잘 배우고 있습니다. 해당 영상을 어떻게 응용하면 두 개 이상 중복 클릭해서 보고 싶은 데이터를 한 차트에서 비교해 볼 수 있게끔 할 수 있을까요..?

daef****
2020년 6월 4일 4:49 오후
답글 남기기  오빠두엑셀

답변 정말 감사드립니다. 피벗테이블-피벗차트 생성 시 차트에 보고싶은 계열만 사용자가 쉽게 선택하는 방법은 없을까요? (피벗 차트 필드에서 값(항목)을 변경하는 방법 말고 버튼 식으로 만들고 싶습니다) (일반차트에서는 필터에서 계열을 선택할수있는데 피벗차트는… 더보기 »

Last edited 1 년 전 by daef****
Yene
Yene
2020년 5월 28일 3:30 오후
게시글평점 :
     

강의 감사합니다.
혹시 사업진행상황을 나타낸 가로누적그래프에서도 이러한 효과를 줄 수 있을까요?
가로누적그래프는 데이터가 시작일, 끝난일, 총소요기간 이렇게 3개 데이터를 가지고 나타낸거라 가능할지 모르겠습니다

미스터릭
미스터릭
2020년 7월 2일 10:06 오후
게시글평점 :
     

좋은 강의 정말 감사합니다^^

김우너기
김우너기
2020년 9월 8일 4:14 오후
게시글평점 :
     

안녕 하세요, 언제나 좋은 강의 감사 드립니다. 혹시 이렇게 만든 대쉬보드를 다른 사용자들과 함께 볼때 차트나 슬라이서등을 부분적으로 잠궈 사용자들이 슬라이서를 클릭 할수 있거나 그래프 색이 바뀌는 것은 볼수 있지만… 더보기 »

켄타로
켄타로
2020년 10월 6일 12:50 오전
게시글평점 :
     

안녕하세요. 판매점별로 데이터를 나열하는게 아니라, 응용해서 날짜별로 판매수량을 나열하고 싶은데, 예를 들어 2020-10-01 로 슬라이서가 만들어지는데, 이것을 1일,2일,3일 식으로 표기형식을 바꿀 수 있을까요?

엑셀차트 공부중
엑셀차트 공부중
2020년 10월 22일 10:18 오전
게시글평점 :
     

안녕하세요, 항상 좋은 정보 감사드립니다. 보여주신 방법대로 따라서 만들었는데, 보여주신 샘플과 다르게 제가 한 작업은 그래프 색의 움직임이 부자연스럽습니다. (샘플에서는 슬라이서를 클릭할때 색이 자연스럽게 감소하는 느낌으로 색상이 표현되는데, 제가한 작업은… 더보기 »

rebenzv****
rebenzv****
2020년 10월 27일 4:59 오후
게시글평점 :
     

안녕하세요 선생님, 강의 정말 재밌고 신기하고 감탄하면서 늘 잘 보고있습니다. 궁금한게 있어서 댓글드립니다. 지금 이 강의에서 매출을 월,판매점 별로 구분하셨는데 혹시, 슬라이서 판매점을 누르면 상품별 총 출고량 그리고 판매 구분을… 더보기 »

rebenzv****
rebenzv****
2020년 10월 27일 5:02 오후
게시글평점 :
     

아 그리고, 선생님 판매일 서식은 0월0일로 되어있고 그걸 피벗테이블에서 테이블 필드에 월을 넣으면 이쁘게 그룹으로 묶이던데, 제가 가지고 있는 서식은 YYYY-MM-DD 를 그룹으로 묶으면 7월이면 7월로 묶이는게 아니라, 7월1일로 묶이고… 더보기 »

꿈은이루워진다
꿈은이루워진다
2020년 12월 3일 1:46 오후
게시글평점 :
     

많은 도움 되었습니다.

비스톤
비스톤
2020년 12월 7일 6:14 오후
게시글평점 :
     

안녕하세요.
좋은 자료 잘 보고 있습니다.
혹시나 슬라이서와 차트에서 값이 "0"인 항목을 제외하고 정렬하는 방법은 없는건가요?
월별 실적이 매번 변동하는데 목록이 너무 많다보니 0이 제외되지 않고는 데이터를 깔끔하게 볼수가 없네요.

ba****
ba****
2021년 1월 23일 9:04 오후
게시글평점 :
     

오늘도 배움 한가지 추가했습니다
감사합니다

박천호
박천호
2021년 2월 10일 1:17 오전
게시글평점 :
     

와우... 신기합니다.

hiz99
hiz99
2021년 2월 24일 2:20 오후
게시글평점 :
     

오 신기하네요 ㅎㅎ

현돌이
현돌이
2021년 8월 14일 11:12 오후
게시글평점 :
     

너무 훌륭한 내용입니다. ^^

40
0
여러분의 생각을 댓글로 남겨주세요.x