엑셀 항목별 평균 차트, 실시간으로 출력되는 다재다능 차트 만들기
항목별 평균 차트 목차 바로가기
강의 요약
엑셀로 보고서 작성시, 아래의 표와 같이 각 항목별로 평균을 작성하는 경우가 종종 있습니다.
평균은 총량에 대한 값으로, 데이터의 전반적인 현황을 요약해서 보여줄 수 있습니다. 하지만 위와 같이 표 형식으로 보고할 경우, 데이터 평균이 나오게 된 원인, 즉 흐름을 나타내기에는 많이 부족한게 현실인데요.
위와 같이 항목별 평균을 나타내는 차트를 제작하면, 평균의 원인과 흐름을 쉽게 파악할 수 있어 더욱 효과적인 보고서를 작성할 수 있습니다. 이번 강의에서는 엑셀 항목별 평균 차트 제작방법을 단계별로 알아보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 모든 강의 예제파일은 회원 여러분께 무료로 제공됩니다.
- [차트강의] 항목별 평균 차트 만들기예제파일[차트강의] 항목별 평균 차트 만들기완성파일
엑셀 항목별 평균 차트 제작 단계별 알아보기
- 예제파일의 '전국주택가격동향조사' 범위를 선택한 뒤, '삽입' - '피벗테이블'을 클릭하여 피벗테이블을 생성합니다. (단축키 Alt - N - V)
항목별 평균 계산을 위한 피벗테이블을 생성합니다. - 피벗테이블의 필드를 아래와 같이 설정합니다.
필드 항목 열 날짜 행 지역 값 평균전세가격 
피벗테이블의 필드를 설정합니다. - 피벗테이블의 날짜를 우클릭한 뒤, [그룹]을 선택합니다. 이후 '분기'와 '연'만 활성화 한 뒤 [확인]을 눌러 날짜를 그룹화합니다.
피벗테이블의 날짜를 연과 분기로 그룹화합니다. - '년도 요약'을 우클릭한 뒤, '연 부분합'을 체크해제합니다.
연도 부분합을 체크 해제합니다. - 피벗테이블을 선택한 뒤, [삽입] - [슬라이서]를 클릭합니다. 이후 '연'을 선택하여 '연도' 슬라이서를 삽입합니다.
연도 슬라이서를 삽입합니다. - 슬라이서를 우클릭한 뒤, [슬라이서 설정]으로 이동합니다. 이후 '데이터가 없는 항목 숨기기'를 체크합니다.
슬라이서 설정에서 '데이터가 없는 항목 숨기기'를 활성화합니다. - 슬라이서의 연도를 선택하면 선택한 년도의 항목별 데이터로 피벗테이블의 변경됩니다.
슬라이서를 클릭하면 해당 년도의 데이터로 피벗테이블이 업데이트됩니다. - 예제파일의 H5셀을 선택한 뒤, 피벗테이블 지역의 첫번째 값인 A6셀을 불러옵니다.
피벗테이블 데이터 첫번째 셀을 불러옵니다. - 범위를 피벗테이블과 동일한 넓이로 자동채우기합니다. 피벗테이블과 동일한 값이 출력됩니다.
피벗테이블과 동일한 넓이로 셀을 자동채우기합니다. - 피벗테이블의 머릿글을 복사하여, 옆에 새로운 범위의 머릿글로 붙여넣기합니다.
붙여넣기한 범위에 머릿글을 추가합니다. - 붙여넣기한 머릿글 옆 셀을 선택한 후, 피벗테이블의 '연도'를 불러오도록 셀을 연결합니다.
피벗테이블의'연도'를 머릿글로 불러옵니다. - 표 우측으로 '평균' 데이터를 추가합니다. AVERAGE 함수를 사용하여 항목별 평균을 계산한 뒤,, 아래로 자동채우기 합니다.
평균을 계산한 뒤, 아래로 자동채우기합니다. - 데이터의 숫자 단위가 십만단위로 높아, 천단위를 절삭하여 데이터를 간소화합니다. 추가한 범위 전체를 선택한 뒤, 우클릭하여 '셀서식' 으로 이동합니다. '사용자지정'에서 형식을 아래와 같이 변경합니다. (단축키 Ctrl + 1)
#,##0,;
엑셀 사용자지정서식 실전예제 및 총정리 바로가기
셀 서식을 변경하여 천단위를 절삭합니다.
- 범위를 선택한 뒤, [삽입] - [추천차트]를 선택합니다. 아래 그림과 같이 차트가 자동으로 추천됩니다. [확인]을 눌러 차트를 삽입합니다.
범위를 선택한 뒤, 새로운 차트를 생성합니다. - 만약 차트가 자동으로 추천이 안될경우, 차트를 우선 생성한 뒤, 차트를 우클릭하여 '차트종류변경'으로 이동합니다.
차트가 자동으로 추천되지 않을 경우, 생성된 차트를 우클릭하여 '차트종류변경'으로 이동합니다. - '혼합'에서 각 분기별 데이터는 '묶은 세로 막대형' 차트로, 평균은 '꺾은선형' 차트를 선택하여 혼합차트로 변경한 뒤, 확인을 눌러 차트를 생성합니다.
차트를 혼합차트로 변경합니다. - 차트의 레이아웃을 변경합니다. 빠르게 진행하기 위해 오피스 테마를 변경하여 차트를 꾸며줍니다. [페이지레이아웃] - [테마]로 이동한 뒤, [패싯]을 선택합니다.
페이지 레이아웃에서 테마를 '패싯'으로 변경합니다. - 차트의 머릿글을 선택한 연도와 연동시켜주겠습니다. 범위의 연도 머릿글 위에 아래 수식을 입력합니다. 연도를 변경하면 "xxxx년 분기별 전세 현황" 으로 텍스트가 변경됩니다.
=H5&" 분기별 전세 현황"
차트제목과 연동할 문자열을 추가합니다. - 차트의 제목을 선택한 뒤, '수식입력줄'을 클릭합니다. 이후 수식을 입력한 셀을 차트제목과 연동합니다.
차트제목을 다른 셀과 연동합니다. - 평균을 표시할 선을 추가합니다. [삽입] - [도형] 에서 선을 추가합니다. 키보드 Shift 키를 누른채 삽입하면 가로모양으로 도형을 쉽게 삽입할 수 있습니다.
항목별 평균을 표시할 선을 생성합니다. - 도형 서식을 변경합니다. 선의 색상, 두께, 선 모양을 차트 색상에 맞춰 변경합니다.
선의 도형 서식을 변경합니다. - 선을 복사한 뒤, 차트의 꺾은선 그래프 꼭지점을 선택합니다. 그리고 Ctrl + V 로 선을 붙여넣기합니다.
꺾은선 그래프 위로 선을 붙여넣기 합니다. - 꺾은선 차트를 우클릭한 뒤, '윤곽선'을 '윤곽선 없음'으로 변경합니다.
꺾은선차트의 윤곽선을 '없음'으로 설정합니다. - 엑셀 '항목별 평균 차트'가 완성되었습니다.
엑셀 항목별 평균 차트가 완성되었습니다.
