SUMPRODUCT 함수에 들어가는 인수의 범위는 모두 동일해야 합니다. 그 중 하나라도 범위가 다를경우 SUMPRODUCT 함수는 #N/A 오류를 반환합니다.
예를들어, SUMPRODUCT(A1:A100, B1:B99, C1:C100) 으로 입력시, 두번째 인수인 B1:B99 범위의 넓이가 다르므로 SUMPRODUCT 함수는 #N/A 오류를 반환합니다. 따라서 항상 데이터가 입력되는 날짜열(B열)에서 셀 개수를 참고하여 rng소계정 범위가 확장되도록 수식을 수정하였습니다.
이후 M4셀에 피벗테이블을 삽입한 뒤, [날짜] 필드를 행으로 끌어옵니다. 날짜행을 우클릭하여 [그룹]을 선택한 뒤, [월]에만 체크하고 '확인'을 눌러 마무리합니다.
피벗테이블의 [날짜] 행에서 분기와 연은 제외하고 ‘월’에 해당하는 자료만 행에 표시합니다.이후 아래 표를 참고하여 G4셀부터 L4셀까지는 머릿글을 추가하고, 그 아래에는 각 수식을 넣어 데이터를 완성합니다.
열
머릿글
수식
G
월
A5&"월"
H
수입
B5
I
지출
C5
J
수입클릭
=IF($M$5=G5,H5,0)
K
지출클릭
=IF($M$5=G5,I5,0)
L
선택월
=IF($M$5=G5,A5,0)
G4셀부터 K16셀까지 데이터가 입력된 범위(월~지출클릭)을 선택한 뒤, ‘묶은 세로 막대그래프’를 생성합니다.이후 ‘차트제목’, ‘계열’, ‘눈금선’, ‘세로축’ 은 차트에서 지워줍니다. 차트를 우클릭 한 뒤 [차트종류변경]으로 이동하여 [수입클릭]과 [지출클릭]을 보조축으로 설정합니다.
클릭차트를 만들기 위해 ‘수입클릭’과 ‘지출클릭’은 보조축에 설정합니다.
이후 피벗테이블을 선택한 뒤 [삽입] - [슬라이서]로 이동합니다. 슬라이서 목록에서 '날짜 월'에 해당하는 슬라이서를 추가한 뒤 버튼을 클릭하면 차트에서 선택한 월의 색상만 강조되는 것을 볼 수 있습니다.
가계부 대시보드의 흐름인 '월별 수입/지출' ⇨ '선택된 지출의 계정과목' ⇨ '선택된 계정과목의 소계정상세' 단계에서 마지막 단계로 소계정별 지출상세를 나타내는 차트를 추가합니다.
차트를 만드는 단계는 이전과 동일합니다. 피벗테이블을 생성한 뒤, [구분]을 필터로 추가합니다. 이후 필터에서 '지출'만 선택하면 피벗테이블에는 '지출’에 해당하는 데이터만 표시됩니다.
이후 [소계정]을 행으로 가져오면 지출에 해당하는 소계정목록이 피벗테이블에 나열됩니다. 또한 총합계를 숨기기 위하여 '피벗테이블 선택' - [디자인] - [총합계] - ‘행 및 열의 총합계 해제’를 선택하여 총합계를 숨겨줍니다.
지출에 해당하는 소계정만 나열됩니다.
우리가 피벗테이블로 출력하고 싶은 내용은 ‘선택된 계정과목’의 소계정별 지출상세입니다. 따라서 이전에 생성했던 [계정과목 슬라이서]를 방금 생성한 [소계정 피벗테이블]에 연동합니다. 소계정 피벗테이블을 선택한 뒤 [분석]탭을 클릭합니다.
그럼 좌측 상단에 [피벗테이블 이름]이 나타납니다. 해당 피벗테이블 이름을 기억하세요. (또는 피벗테이블의 이름을 원하는 값으로 변경해도 됩니다.)
피벗테이블을 클릭한 뒤, [분석] 탭으로 이동하면 선택된 피벗테이블의 이름을 확인할 수 있습니다.이후 [계정과목]슬라이서를 우클릭한 뒤, [보고서 연결]을 선택합니다. 그럼 아래와 같이 생성했던 피벗테이블이 나열되며, 피벗테이블 목록중 [소계정]에 해당하는 피벗테이블을 선택한 후 확인을 눌러 창을 닫습니다.
계정과목 슬라이서와 소계정 피벗테이블을 연결합니다.
이후 계정과목 슬라이서에서 버튼을 클릭하면, [계정과목]과 [소계정]에 해당하는 2개의 피벗테이블이 동시에 갱신됩니다.
계정과목 슬라이서를 클릭하면 2개의 연결된 피벗테이블이 동시에 업데이트됩니다.
이제 소계정별 지출상세를 나타내기 위한 차트 데이터 범위를 생성합니다. 아래 수식을 참고하여 이전과 동일하게 데이터범위를 생성합니다. 데이터범위를 만드는 자세한 과정은 영상강의를 참고하세요.
소계정 지출상세를 나타내는 데이터 범위까지 잘 만드셨나요? 고생 많으셨습니다. 이제 정말 마지막단계로, 소계정 지출상세를 나타내는 동적차트를 생성합니다. 이전에 만들었던 2개의 차트는, 차트에 들어가는 데이터의 범위가 일정했습니다. 하지만 소계정차트는 선택된 계정과목에 따라 차트에 들어가는 데이터범위가 달라집니다.
따라서 차트의 데이터범위가 동적으로 변하는 동적차트로 만들어줘야 합니다. 동적차트 만들기에 사용되는 함수 공식 및 자세한 동작원리는 INDEX 동적범위 만들기 강의를 참고하세요.
일반 데이터 범위로 차트를 생성하면 빈 계열값이 차트에 출력되므로, 동적차트를 생성합니다.
우선 이전 단계에서 만든 소계정 지출상세 데이터 범위를 선택한 후, ‘파이형 차트’를 생성합니다. 생성된 차트의 계열을 보면, 값이 입력되지 않은 빈칸의 경우 아래와 같이 ‘0’으로 계열값이 나열된 것을 볼 수 있습니다.
