엑셀 누적 차트에 합계 자동 표시하기

엑셀 누적 차트 사용할 때, 합계를 맨 위에 표시하고 싶은데, 기본 기능으로 제공하지 않아서 아쉬움이 많습니다. 제일 무식한 방법은 해당 자리에 도형-텍스트를 만들어서 여기에 숫자를 입력하는 것입니다만, 이러면 차트 데이타가 바뀌었을 때, 일일이 다 수정해줘야 합니다. 아래 방법을 사용하면, 합계는 자동으로 변경되게 할 수 있습니다.

 

[ 일반 테이블 ]

 

1. 데이타를 구성합니다.

 

	A	B	C	D
		그룹1	그룹2	그룹3
1	대상1	1	2	3
2	대상2	4	5	6
3	대상3	7	8	9
4	합계	12	15	18

 

2. 차트를 구성합니다.

 

A1~D4까지를 선택해서 누적 세로 막대형 차트를 만듭니다.

만들어진 차트를 클릭 후 '차트 종류 변경'기능을 누릅니다.

차트 종류를 '콤보'로 변경 후 '합계'를 '꺽은선형'으로 변경합니다. 다른 계열들은 누적 세로 막대형을 유지합니다.

 

3. '합계' 계열을 정리합니다

 

레이블 위치를 '위'로 변경합니다.

선/테두리/표식 색을 투명하게 변경합니다.

 

 

[ 피봇 테이블 ]

 

1. 피봇 리포트를 구성합니다.

 

	A	B	C	D
		그룹1	그룹2	그룹3
1	대상1	1	2	3
2	대상2	4	5	6
3	대상3	7	8	9
4	총합계	12	15	18

 

총합계는 피봇테이블에서 자동으로 생성합니다.

 

2. 총합계를 참조하는 외부 셀을 구성합니다.

 

	A	B	C	D
		그룹1	그룹2	그룹3
1	대상1	1	2	3
2	대상2	4	5	6
3	대상3	7	8	9
4	총합계	12	15	18
...
10	계	=B4	=C4	=D4

 

3. 차트를 구성합니다.

 

피봇 리포트에서 누적 세로 막대형 차트를 만듭니다. 리본메뉴-디자인-차트요소 추가에 들어가도 각 항목의 값을 레이블로 사용할 수 있을 뿐, 합계를 표시하는 기능은 없습니다.

 

4. 그룹별로 합계를 표시할 텍스트를 차트에 삽입합니다.

 

먼저 차트를 마우스로 클릭해서 테두리가 선택된 상태로 표시되게 합니다. 그리고 도형 삽입에 가서 텍스트를 선택하고, 이를 위 선택된 차트에 만듭니다. 이러면 텍스트는 차트안에 존재하는 개체가 되어, 차트와 함께 움직이게 됩니다.

 

5. 차트내 텍스트를 B10, C10, D10칸을 참조하게 합니다.

 

셀에서 다른 셀을 참고하게 하려면 '=셀번호'를 입력하면 됩니다. 마찬가지로, 3번에서 삽입한 텍스트를 선택 후, 함수입력란에 '=B4'를 입력합니다. 그러면 텍스트 값이 B4셀에 있는 값을 가져와 표시하게 됩니다. 마찬가지로 그룹 2, 그룹3에 대해서도 동일한 작업을 수행하면, 각 그래프의 합계를 데이타에 연동해서 보여줄 수 있습니다.

텍스트가 직접 B4를 참조할 수 있다면 2단계가 필요 없지만, getpivotdata()함수로도 '=B4' 참조로도 '내부 데이타 참조 불가' 오류를 발생시키며 입력이 취소됩니다.

 

아쉬운 점...

 

이렇게 하면 합계 숫자는 자동으로 가져올 수 있는데, 텍스트 위치는 자동으로 변경되지 않습니다. 숫자가 미세하게 바뀌는 경우에 사용해보세요.