엑셀 버튼 클릭 자동 업데이트 차트 만들기 :: 직장인 필수팁
버튼 클릭 자동 업데이트 차트 목차 바로가기
강의 요약
엑셀로 보고서를 작성할 때, 여러개의 항목이 한 차트에 들어갈 경우 보고서의 가독성이 떨어지고 보고를 받는 사람 입장에서 어느 것이 중요한 내용인지 그 포인트를 종종 놓치게 됩니다. 이럴 경우, 피벗테이블과 슬라이서를 사용하여 버튼을 클릭할 때 실시간으로 업데이트 되는 자동 업데이트 차트 를 만들어 보고하면, 대시보드 형태로 더욱 간결한 보고서를 작성할 수 있는데요.
이번 강의에서는 엑셀 대시보드 제작에 가장 핵심 스킬인 엑셀 자동 업데이트 차트 만들기 방법을 단계별로 알아보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [차트강의] 버튼으로 클릭하는 자동화차트 만들기예제파일[차트강의] 버튼으로 클릭하는 자동화차트 만들기완성파일
엑셀 버튼 클릭 자동 업데이트 차트 만들기
- 차트를 생성하기 위한 백데이터를 취합합니다. 예제파일을 다운받으신 뒤, 차트를 위한 백데이터로 아래 3개의 열을 생성합니다.
열 이름 설명 판매점 판매점의 고유값이 나열된 열입니다. 총 매출액 각 판매점별 총 매출액입니다. 총 매출액 클릭 버튼으로 해당 판매점을 클릭하면, 해당 열에만 총 매출액이 출력됩니다. - 판매점 열을 입력합니다. 예제시트의 B열을 복사하여, J열에 붙여넣기 한 뒤, [데이터] - [중복된 항목 제거]로 판매점의 고유값만 필터링합니다.
판매점 열을 복사하여 J열에 붙여넣기 한 뒤, 중복된 항목을 제거합니다. - SUMIF 함수를 사용하여 판매점별 총 매출을 계산합니다. 총 매출액 열에 아래 수식을 복사하여 붙여넣기 한 뒤, 아래로 자동채우기 합니다.
=SUMIF(B:B,J2,C:C)
판매점 별 매출액 합계를 계산합니다. - '총 매출액 클릭' 열의 값을 '0'으로 채워줍니다.
총 매출액 데이터를 모두 '0'으로 채워줍니다. - '총 매출액'과 '총 매출액 클릭' 열을 선택한 뒤, '셀 서식'을 변경합니다. (단축키 Ctrl + 1) 아래 서식을 복사한 뒤, '표시형식' - '사용자지정'에 붙여넣기하여 마무리합니다.
#,##0;;
셀서식에 대한 내용은 기초입문강의에서 자세히 설명드렸습니다.
총 매출액과 총매출액 클릭의 셀 서식을 변경합니다.
- 취합된 데이터 범위를 선택한 후, '삽입' - '추천차트'를 클릭하여 '세로막대형 그래프'를 삽입합니다.
취합된 데이터 범위를 선택한 후, 세로막대형 차트를 추가합니다. - 차트를 우클릭한 뒤, '차트종류변경' - '혼합'으로 이동합니다. 이후 '총매출액 클릭' 항목을 '보조축'으로 체크하고, 차트종류도 '세로막대형 그래프'로 선택합니다.
총 매출액 클릭을 차트의 보조축으로 설정합니다. - 차트 서식을 변경합니다.
- 차트제목, 세로축(보조축 포함), 눈금선, 범례를 삭제합니다.
- 데이터레이블을 추가합니다.
- 데이터레이블의 글꼴 크기는 작게 변경합니다.
- '총매출액'의 글꼴색상은 회색, '총매출액 클릭'의 글꼴색상은 남색으로 변경합니다.
- 각각의 세로막대 채우기 색상도 글꼴색상과 동일한 색상으로 변경합니다.
차트의 레이아웃을 변경합니다.
- 원본데이터(A~C열)을 선택한 뒤, '삽입' - '피벗테이블'을 추가합니다. (단축키 Alt - N - V) 생성위치는 기존 워크시트의 'E1'셀로 지정합니다.
원본데이터(A~C열)을 선택 후 피벗테이블을 생성합니다. - 피벗테이블을 클릭한 뒤, '판매점' 항목을 '행 필드'로 이동합니다.
피벗테이블을 선택한 후, '판매점' 항목을 행필드로 이동합니다. - 피벗테이블이 선택된 상태에서, '삽입' - '슬라이서'를 선택한 뒤, '판매점'에 대한 슬라이서를 추가합니다.
새로운 슬라이서를 추가합니다. - 슬라이서를 선택한 뒤, '슬라이서 도구' - '디자인'에서 원하는 디자인을 선택한 뒤, '중복'을 클릭합니다.
새로운 슬라이서 디자인을 생성합니다. - 이후 '전체슬라이서' - '서식' - '글꼴' 을 8 로 변경합니다. 또한 '테두리' - '테두리 없음'으로 변경합니다.
새로운 슬라이서의 디자인을 설정합니다. - 새롭게 생성된 슬라이서 스타일로 변경합니다.
슬라이서를 선택한 후, 새로운 슬라이서 스타일을 적용합니다. - 슬라이서를 우클릭한 뒤, '슬라이서 서식'에서 '머릿글 표시' 체크박스를 비활성화 합니다. '데이터가 없는 항목 숨기기' 는 체크박스를 활성화합니다.
슬라이서 설정을 변경합니다.
- '1-4' 단계에서 추가한 '총매출액 클릭' 열에 CONTIF 함수를 사용하여, 아래와 같은 조건으로 값이 출력되로록 수식을 추가합니다.
' - 피벗테이블로 출력된 목록에 해당 판매점이 있을 경우 : 총 매출액
' - 판매점이 목록에 없을 경우 : 0=IF(COUNTIF(E:E,J2)>0,K2,0)
'총 매출액 클릭' 값을 계산하기 위한 수식을 추가합니다.
- 원본데이터 (A~C열)을 선택한 뒤, 피벗테이블을 추가합니다. 기존 차트에 추가한 뒤, 아래와 같이 피벗테이블 필드를 이동합니다.
- 열필드 : [판매점]
- 행필드 : [판매일]
- 값필드 : [매출액] '// 우클릭 후 - '값 필드 설정' 에서 '표시형식'을 [ #,##0;; ] 으로 변경합니다.
새로운 피벗테이블 생성 후, 필드를 그림과 같이 이동합니다. - 추가된 피벗테이블을 선택한 후, '삽입' - '추천차트'로 보조 차트를 추가합니다. 이번에는 '꺾은선형 그래프'를 추가해줍니다.
새로추가된 피벗테이블에서 '꺾은선형 그래프'를 추가합니다. - 차트 서식을 아래와 같이 변경합니다.
- 범례, 세로축, 계열, 눈금선을 제거합니다.
- 차트위 버튼를 우클릭하여, '모든 필드단추 숨기기' 로 차트의 모든 피벗필드 단추를 제거합니다.
- 데이터레이블을 추가합니다.
- 차트제목과 데이터레이블의 글꼴 크기 및 색상을 알맞게 변경합니다.
차트가 더욱 간결해지도록 레이아웃을 변경합니다.
- 슬라이서를 우클릭한 뒤, '보고서 연동'을 선택합니다.
슬라이서를 우클릭한 뒤, '보고서 연동'을 선택합니다. - 새롭게 추가한 피벗테이블을 체크한 뒤, '확인'을 눌러 슬라이서에 2개의 피벗테이블을 모두 연동시켜줍니다.
2개의 피벗테이블을 모두 슬라이서에 연동시킵니다. - 1단계에서 생성한 표를 우클릭한 뒤, '데이터' - '필터'를 추가합니다. 이후 판매점을 '오름차순'으로 정렬합니다.
취합 된 데이터에 필터를 추가한 뒤, '판매점' 기준 오름차순으로 정렬합니다.
7. 버튼 클릭 자동업데이트 차트 완성
- 슬라이서를 클릭한 뒤, '슬라이서' - '단추' 의 열 개수를 9개로 변경합니다.
슬라이서의 단추 개수를 9개로 변경합니다. - 버튼을 클릭하면 자동으로 업데이트 되는 차트가 완성되었습니다.
자동 업데이트 차트가 완성되었습니다.
