엑셀 대시보드 :: 10억짜리 ERP 부럽지 않은, 대시보드 차트

엑셀 대시보드 차트 :: 피벗테이블/동적차트 만들기

피벗테이블/동적차트 목차 바로가기

영상강의

예제파일 다운로드

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

패치노트

rng소계정 범위를 COUNTA 함수를 이용한 동적범위로 변경

SUMPRODUCT 함수에 들어가는 인수의 범위는 모두 동일해야 합니다. 그 중 하나라도 범위가 다를경우 SUMPRODUCT 함수는 #N/A 오류를 반환합니다.

예를들어, SUMPRODUCT(A1:A100, B1:B99, C1:C100) 으로 입력시, 두번째 인수인 B1:B99 범위의 넓이가 다르므로 SUMPRODUCT 함수는 #N/A 오류를 반환합니다. 따라서 항상 데이터가 입력되는 날짜열(B열)에서 셀 개수를 참고하여 rng소계정 범위가 확장되도록 수식을 수정하였습니다.

[중요!]데이터가 많아 속도가 느리다면, SUMPRODUCT를 SUMIFS로 대체합니다

많은 분께서 질문해주신 내용인데요. 강의에서 소개해드린 방법으로 대시보드를 만들 경우, 처리속도가 느려진다는 요청을 많이 해주셨습니다.

SUMPRODUCT 함수는 인수로 배열을 받아옵니다.
따라서 1만행 이상 많은 양의 데이터 처리시에 속도가 느려지는 단점이 있습니다.

만약 데이터가 많아 처리속도가 느릴 경우, '차트자료' 시트에 사용한 SUMPRODUCT 함수를 SUMIF 함수로 대체할 수 있습니다. 각 공식은 예제파일 '차트자료'시트에 추가로 적어드렸습니다.

공식의 동작원리는 아래 관련포스트에서 자세히 설명해드렸습니다.

아래 공식을 '차트자료' 시트 각 셀에 붙여넣기 한 뒤, 아래로 자동채우기를 하여 마무리합니다.

대시보드 차트를 만들기 위한 백데이터를 넣어주기 위한 새로운 시트를 추가합니다. (시트명 : 차트자료)

대시보드는 자료를 각 년도별로 구분하여 데이터를 출력합니다. 따라서, 대시보드에 '년도'를 나타낼 '연결된이미지'를 추가해줍니다. '연결된 이미지를 활용한 버튼만들기'에 대한 자세한 내용은 관련 강의를 참고하세요.

시트 위 아무셀에 '2018년'을 입력한 뒤, 셀 서식에서 사용자 지정서식을 [ 0000”년” ] 으로 그리고 테두리는 [ 두꺼운 회색 ] 으로 설정합니다.

이후, 해당 셀을 복사한 뒤, 대시보드 시트로 이동하여 아무곳이나 셀을 [우클릭] - [선택하여 붙여넣기] – [연결된 이미지]로 붙여넣기 합니다.

선택한 년도의 월별 수입 및 지출을 계산하기 위한 백데이터를 생성합니다.

시트 A5셀부터 A16셀까지, 1~12 숫자를 입력합니다. 이후 아래 적어드린 공식으로 동적범위를 생성합니다.

피벗테이블과 슬라이서를 응용하여 버튼을 클릭하면 선택된 월의 수입과 지출이 강조되는 차트를 생성합니다.

첫번째로 ‘월’이 나열된 슬라이서 버튼을 만들기 위하여 피벗테이블을 생성합니다. 이번 강의에서는, 더 나은 사무자동화를 위해 아래 적어드린 동적범위를 참조하여 피벗테이블을 생성합니다.

동적범위로 피벗테이블을 생성하면, 이후 새로운 데이터가 추가될때마다 피벗테이블의 데이터범위가 자동으로 갱신됩니다. 동적범위에 대한 자세한 설명은 OFFSET 동적범위 관련강의를 참고하세요.

키보드 Ctrl + F3 키를 눌러 새로운 이름정의범위를 추가합니다.

이후 M4셀에 피벗테이블을 삽입한 뒤, [날짜] 필드를 행으로 끌어옵니다. 날짜행을 우클릭하여 [그룹]을 선택한 뒤, [월]에만 체크하고 '확인'을 눌러 마무리합니다.

G4셀부터 K16셀까지 데이터가 입력된 범위(월~지출클릭)을 선택한 뒤, ‘묶은 세로 막대그래프’를 생성합니다.이후 ‘차트제목’, ‘계열’, ‘눈금선’, ‘세로축’ 은 차트에서 지워줍니다. 차트를 우클릭 한 뒤 [차트종류변경]으로 이동하여 [수입클릭]과 [지출클릭]을 보조축으로 설정합니다.

이후 피벗테이블을 선택한 뒤 [삽입] - [슬라이서]로 이동합니다. 슬라이서 목록에서 '날짜 월'에 해당하는 슬라이서를 추가한 뒤 버튼을 클릭하면 차트에서 선택한 월의 색상만 강조되는 것을 볼 수 있습니다.

버튼을 클릭하면 강조되는 차트 만들기의 동작원리 및 자세한 설명은 관련 강의를 참고하세요.

이전단계에서는 '월별 수입/지출'을 정리했습니다. 이제부터는 선택된 월의 지출을 계정과목별로 나눠서 보여주는 차트를 추가합니다. 차트를 만드는 과정은 이전과 동일합니다. 차트에 들어갈 데이터를 만드는 자세한 과정은 영상강의를 참고하세요.

조건별 합계를 계산할때에는 SUMIF 함수를 사용하는것이 일반적입니다. 하지만, 이번 강의에서는 더 나은 사무자동화를 위해 SUMPRODUCT 함수를 사용하여 월별 합계를 계산합니다. SUMPRODUCT 함수의 고급사용법에 대한 자세한 내용은 관련 포스트를 참고하세요.

예제파일에 사용된 수식은 아래와 같습니다.

데이터 범위를 선택한 뒤 차트를 추가합니다. 이번에는 ‘가로 막대그래프’로 차트를 생성하겠습니다. 차트를 우클릭한 뒤, [차트종류변경]으로 이동하여 [지출클릭] 계열을 ‘보조축’으로 설정합니다.

마찬가지로 이전과 동일하게, 피벗테이블 선택 - [삽입] - [슬라이서]로 이동하여, [계정과목] 슬라이서를 추가합니다. 이제 각 계정과목 버튼을 클릭하면 아래 그림처럼 선택된 계정과목의 항목이 강조됩니다.

대시보드 차트 만들기의 마지막 단계입니다.

가계부 대시보드의 흐름인 '월별 수입/지출' ⇨ '선택된 지출의 계정과목' ⇨ '선택된 계정과목의 소계정상세' 단계에서 마지막 단계로 소계정별 지출상세를 나타내는 차트를 추가합니다.

차트를 만드는 단계는 이전과 동일합니다. 피벗테이블을 생성한 뒤, [구분]을 필터로 추가합니다. 이후 필터에서 '지출'만 선택하면 피벗테이블에는 '지출’에 해당하는 데이터만 표시됩니다.

이후 [소계정]을 행으로 가져오면 지출에 해당하는 소계정목록이 피벗테이블에 나열됩니다. 또한 총합계를 숨기기 위하여 '피벗테이블 선택' - [디자인] - [총합계] - ‘행 및 열의 총합계 해제’를 선택하여 총합계를 숨겨줍니다.

우리가 피벗테이블로 출력하고 싶은 내용은 ‘선택된 계정과목’의 소계정별 지출상세입니다. 따라서 이전에 생성했던 [계정과목 슬라이서]를 방금 생성한 [소계정 피벗테이블]에 연동합니다. 소계정 피벗테이블을 선택한 뒤 [분석]탭을 클릭합니다.

그럼 좌측 상단에 [피벗테이블 이름]이 나타납니다. 해당 피벗테이블 이름을 기억하세요. (또는 피벗테이블의 이름을 원하는 값으로 변경해도 됩니다.)

이후 계정과목 슬라이서에서 버튼을 클릭하면, [계정과목]과 [소계정]에 해당하는 2개의 피벗테이블이 동시에 갱신됩니다.

이제 소계정별 지출상세를 나타내기 위한 차트 데이터 범위를 생성합니다. 아래 수식을 참고하여 이전과 동일하게 데이터범위를 생성합니다. 데이터범위를 만드는 자세한 과정은 영상강의를 참고하세요.

소계정 지출상세를 나타내는 데이터 범위까지 잘 만드셨나요? 고생 많으셨습니다. 이제 정말 마지막단계로, 소계정 지출상세를 나타내는 동적차트를 생성합니다. 이전에 만들었던 2개의 차트는, 차트에 들어가는 데이터의 범위가 일정했습니다. 하지만 소계정차트는 선택된 계정과목에 따라 차트에 들어가는 데이터범위가 달라집니다.

따라서 차트의 데이터범위가 동적으로 변하는 동적차트로 만들어줘야 합니다. 동적차트 만들기에 사용되는 함수 공식 및 자세한 동작원리는 INDEX 동적범위 만들기 강의를 참고하세요.

우선 이전 단계에서 만든 소계정 지출상세 데이터 범위를 선택한 후, ‘파이형 차트’를 생성합니다. 생성된 차트의 계열을 보면, 값이 입력되지 않은 빈칸의 경우 아래와 같이 ‘0’으로 계열값이 나열된 것을 볼 수 있습니다.

동적차트를 만들기 위해 아래 2개의 동적범위를 생성합니다.

동적범위를 생성하였으면, 이제 차트에 동적범위를 적용해야 합니다. 차트를 우클릭한 뒤, ‘데이터 선택’으로 이동합니다.