엑셀 항목별 평균 차트 만들기

강의 요약

엑셀로 보고서를 작성할 때 아래의 표처럼 각 항목별로 평균을 표기하는 경우가 많습니다.

평균은 총량을 대표하는 값으로, 데이터의 전반적인 현황을 요약해서 보여줍니다. 다만 표 형식만으로는 평균이 도출된 원인이나 흐름까지 충분히 전달하기에는 한계가 있습니다.

위 그림처럼 항목별 평균을 시각화한 차트를 함께 제공하면 평균의 원인과 변화 흐름을 한눈에 파악할 수 있어, 보다 효과적인 보고서를 작성할 수 있습니다. 이번 강의에서는 엑셀로 항목별 평균 차트를 제작하는 과정을 단계별로 살펴봅니다.

영상강의

엑셀 항목별 평균 차트 제작 단계별 알아보기

1. 예제파일의 '전국주택가격동향조사' 범위를 선택한 뒤, [삽입] - [피벗테이블]을 클릭해 피벗테이블을 생성합니다. (단축키 Alt - N - V)
   
2. 피벗테이블의 필드를 아래와 같이 설정합니다.
   

   필드	항목
   열	날짜
   행	지역
   값	평균전세가격

   

3. 피벗테이블의 날짜 필드를 우클릭한 뒤 [그룹]을 선택합니다. 이어서 '분기'와 '연'만 활성화한 상태로 [확인]을 눌러 날짜를 그룹화합니다.
   
4. '연도 요약' 항목을 우클릭한 뒤 '연 부분합' 체크를 해제해 부분합 행을 제거합니다.
   
5. 피벗테이블을 선택한 상태에서 [삽입] - [슬라이서]를 클릭합니다. 이후 '연' 필드를 선택해 연도 슬라이서를 삽입합니다.
   
6. 슬라이서를 우클릭한 뒤 [슬라이서 설정]으로 이동합니다. 설정 창에서 '데이터가 없는 항목 숨기기'를 체크해 빈 연도가 표시되지 않도록 합니다.
   
7. 슬라이서에서 연도를 선택하면 해당 연도의 항목별 데이터로 피벗테이블이 자동 갱신됩니다.
   
8. 예제파일의 H5셀을 선택한 뒤, 피벗테이블 지역 필드의 첫 번째 값인 A6셀을 참조하도록 수식을 입력합니다.
   
9. 입력한 수식을 피벗테이블과 동일한 범위만큼 자동채우기합니다. 피벗테이블과 같은 값이 출력됩니다.
   
10. 피벗테이블의 머리글을 복사한 뒤, 옆에 만든 새로운 범위의 머리글로 붙여넣기합니다.
    
11. 붙여넣기한 머리글 옆 셀을 선택한 뒤, 피벗테이블의 '연도' 값을 불러오도록 셀을 연결합니다.
    
12. 표 우측에 '평균' 열을 추가합니다. AVERAGE 함수로 항목별 평균을 계산한 뒤, 아래쪽으로 자동채우기를 적용합니다.
    
13. 데이터의 숫자 단위가 십만 단위로 커서, 천 단위를 절삭해 표시를 간결하게 만듭니다. 추가한 범위 전체를 선택한 뒤 우클릭해 [셀 서식]으로 이동합니다. '사용자 지정' 항목에서 형식을 아래와 같이 변경합니다. (단축키 Ctrl + 1)
    #,##0,;
    엑셀 사용자지정서식 실전예제 및 총정리 바로가기

    

1. 준비한 범위를 선택한 뒤 [삽입] - [추천 차트]를 선택합니다. 아래 그림과 같이 적절한 차트가 자동으로 추천되며, [확인]을 눌러 차트를 삽입합니다.
   
2. 차트가 자동으로 추천되지 않는 경우에는 차트를 먼저 생성한 뒤, 차트를 우클릭해 [차트 종류 변경] 메뉴로 이동합니다.
   
3. '혼합' 항목에서 분기별 데이터는 '묶은 세로 막대형'으로, 평균은 '꺾은선형'으로 지정해 혼합 차트로 변경한 뒤, [확인]을 눌러 차트를 생성합니다.
   
4. 차트의 레이아웃을 다듬습니다. 빠른 진행을 위해 오피스 테마를 변경해 차트 디자인을 정돈합니다. [페이지 레이아웃] - [테마]로 이동한 뒤 [패싯]을 선택합니다.
   차트 만들기 5단계 공식 영상강의 바로가기

   

5. 차트의 제목을 선택한 연도와 연동시킵니다. 범위의 연도 머리글 위 셀에 아래 수식을 입력하면, 연도를 변경할 때마다 "xxxx년 분기별 전세 현황" 형식으로 텍스트가 자동으로 갱신됩니다.
   =H5&" 분기별 전세 현황"

   

6. 차트의 제목을 선택한 뒤 '수식 입력줄'을 클릭합니다. 이어서 앞서 수식을 입력한 셀을 차트 제목과 연동합니다.
   
7. 평균을 표시할 선을 추가합니다. [삽입] - [도형]에서 선을 추가합니다. 키보드의 Shift 키를 누른 채 그리면 가로 방향으로 정확하게 선을 삽입할 수 있습니다.
   
8. 도형 서식을 변경합니다. 선의 색상, 두께, 선 모양을 차트 색상에 어울리도록 조정합니다.
   
9. 선을 복사한 뒤 차트의 꺾은선 그래프 꼭짓점을 선택합니다. 그 상태에서 Ctrl + V로 선을 붙여넣어 평균선을 표시합니다.
   
10. 꺾은선 차트를 우클릭한 뒤 윤곽선 옵션을 '윤곽선 없음'으로 변경해 원래의 꺾은선을 숨깁니다.
    
11. 이상으로 엑셀 '항목별 평균 차트'가 완성되었습니다.
    

링크 : MS홈페이지 AVERAGE 함수 사용법 상세설명