엑셀 항목별 평균 차트 만들기 :: 함수 하나로 간단 해결!

각 항목별 평균을 자동으로 계산하여 출력하는 항목별 평균 자동화 차트 제작방법을 단계별로 살펴봅니다.

홈페이지 » 엑셀 항목별 평균 차트 만들기 :: 함수 하나로 간단 해결!

엑셀 항목별 평균 차트, 실시간으로 출력되는 다재다능 차트 만들기

항목별 평균 차트 목차 바로가기
강의 요약

엑셀로 보고서 작성시, 아래의 표와 같이 각 항목별로 평균을 작성하는 경우가 종종 있습니다.

엑셀 항목별 평균 표
각 항목별 평균을 보여주는 표는 보통 이렇게 제작됩니다.

평균은 총량에 대한 값으로, 데이터의 전반적인 현황을 요약해서 보여줄 수 있습니다. 하지만 위와 같이 표 형식으로 보고할 경우, 데이터 평균이 나오게 된 원인, 즉 흐름을 나타내기에는 많이 부족한게 현실인데요.

엑셀 브랜드별 점유율 차트
항목별 평균 차트를 이용하면 평균의 흐름까지 한번에 보여줄 수 있습니다.

위와 같이 항목별 평균을 나타내는 차트를 제작하면, 균의 원인과 흐름을 쉽게 파악할 수 있어 더욱 효과적인 보고서를 작성할 수 있습니다. 이번 강의에서는 엑셀 항목별 평균 차트 제작방법을 단계별로 알아보겠습니다.

영상강의

예제파일 다운로드

관련 기초함수
지정한 범위나 숫자의 평균(산술평균)을 계산합니다.

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

항목별 평균차트 출력용 데이터 만들기
  1. 예제파일의 '전국주택가격동향조사' 범위를 선택한 뒤, '삽입' - '피벗테이블'을 클릭하여 피벗테이블을 생성합니다. (단축키 Alt - N - V)

    항목별 평균 출력용 피벗테이블 만들기
    항목별 평균 계산을 위한 피벗테이블을 생성합니다.
  2. 피벗테이블의 필드를 아래와 같이 설정합니다.
    필드항목
    날짜
    지역
    평균전세가격

    피벗테이블 필드 설정
    피벗테이블의 필드를 설정합니다.
  3. 피벗테이블의 날짜를 우클릭한 뒤, [그룹]을 선택합니다. 이후 '분기'와 '연'만 활성화 한 뒤 [확인]을 눌러 날짜를 그룹화합니다.

    피벗테이블 날짜 그룹화
    피벗테이블의 날짜를 연과 분기로 그룹화합니다.
  4. '년도 요약'을 우클릭한 뒤, '연 부분합'을 체크해제합니다.

    피벗테이블 항목별 연도 부분합 해제
    연도 부분합을 체크 해제합니다.
  5. 피벗테이블을 선택한 뒤, [삽입] - [슬라이서]를 클릭합니다. 이후 '연'을 선택하여 '연도' 슬라이서를 삽입합니다.

    피벗테이블 연도 슬라이서 삽입
    연도 슬라이서를 삽입합니다.
  6. 슬라이서를 우클릭한 뒤, [슬라이서 설정]으로 이동합니다. 이후 '데이터가 없는 항목 숨기기'를 체크합니다.

    슬라이서 데이터 없는 항목 숨기기
    슬라이서 설정에서 '데이터가 없는 항목 숨기기'를 활성화합니다.
  7. 슬라이서의 연도를 선택하면 선택한 년도의 항목별 데이터로 피벗테이블의 변경됩니다.

    슬라이서 항목별 평균 표 업데이트
    슬라이서를 클릭하면 해당 년도의 데이터로 피벗테이블이 업데이트됩니다.
  8. 예제파일의 H5셀을 선택한 뒤, 피벗테이블 지역의 첫번째 값인 A6셀을 불러옵니다.

    데이터 연동
    피벗테이블 데이터 첫번째 셀을 불러옵니다.
  9. 범위를 피벗테이블과 동일한 넓이로 자동채우기합니다. 피벗테이블과 동일한 값이 출력됩니다.

    항목별 평균 피벗테이블 표 복사
    피벗테이블과 동일한 넓이로 셀을 자동채우기합니다.
  10. 피벗테이블의 머릿글을 복사하여, 옆에 새로운 범위의 머릿글로 붙여넣기합니다.

    피벗테이블 머릿글 붙연허기
    붙여넣기한 범위에 머릿글을 추가합니다.
  11. 붙여넣기한 머릿글 옆 셀을 선택한 후, 피벗테이블의 '연도'를 불러오도록 셀을 연결합니다.

    피벗테이블 연도 연동
    피벗테이블의'연도'를 머릿글로 불러옵니다.
  12. 표 우측으로 '평균' 데이터를 추가합니다. AVERAGE 함수를 사용하여 항목별 평균을 계산한 뒤,, 아래로 자동채우기 합니다.

    항목별 평균 계산
    평균을 계산한 뒤, 아래로 자동채우기합니다.
  13. 데이터의 숫자 단위가 십만단위로 높아, 천단위를 절삭하여 데이터를 간소화합니다. 추가한 범위 전체를 선택한 뒤, 우클릭하여 '셀서식' 으로 이동합니다. '사용자지정'에서 형식을 아래와 같이 변경합니다. (단축키 Ctrl + 1)

    사용자 지정서식 변경
    셀 서식을 변경하여 천단위를 절삭합니다.
항목별 평균 차트 만들기
  1. 범위를 선택한 뒤, [삽입] - [추천차트]를 선택합니다. 아래 그림과 같이 차트가 자동으로 추천됩니다. [확인]을 눌러 차트를 삽입합니다.

    항목별 평균 차트 생성
    범위를 선택한 뒤, 새로운 차트를 생성합니다.
  2. 만약 차트가 자동으로 추천이 안될경우, 차트를 우선 생성한 뒤, 차트를 우클릭하여 '차트종류변경'으로 이동합니다.

    차트 종류 변경하기
    차트가 자동으로 추천되지 않을 경우, 생성된 차트를 우클릭하여 '차트종류변경'으로 이동합니다.
  3. '혼합'에서 각 분기별 데이터는 '묶은 세로 막대형' 차트로, 평균은 '꺾은선형' 차트를 선택하여 혼합차트로 변경한 뒤, 확인을 눌러 차트를 생성합니다.

    혼합 차트 설정
    차트를 혼합차트로 변경합니다.
  4. 차트의 레이아웃을 변경합니다. 빠르게 진행하기 위해 오피스 테마를 변경하여 차트를 꾸며줍니다. [페이지레이아웃] - [테마]로 이동한 뒤, [패싯]을 선택합니다.

    페이지 레이아웃 변경
    페이지 레이아웃에서 테마를 '패싯'으로 변경합니다.
  5. 차트의 머릿글을 선택한 연도와 연동시켜주겠습니다. 범위의 연도 머릿글 위에 아래 수식을 입력합니다. 연도를 변경하면 "xxxx년 분기별 전세 현황" 으로 텍스트가 변경됩니다.
    =H5&" 분기별 전세 현황"

    차트 제목 연동 데이터
    차트제목과 연동할 문자열을 추가합니다.
  6. 차트의 제목을 선택한 뒤, '수식입력줄'을 클릭합니다. 이후 수식을 입력한 셀을 차트제목과 연동합니다.

    차트 제목을 셀과 연동하기
    차트제목을 다른 셀과 연동합니다.
  7. 평균을 표시할 선을 추가합니다. [삽입] - [도형] 에서 선을 추가합니다. 키보드 Shift 키를 누른채 삽입하면 가로모양으로 도형을 쉽게 삽입할 수 있습니다.

    항목별 평균 차트 표시용 도형 추가
    항목별 평균을 표시할 선을 생성합니다.
  8. 도형 서식을 변경합니다. 선의 색상, 두께, 선 모양을 차트 색상에 맞춰 변경합니다.

    도형 서식 변경하기
    선의 도형 서식을 변경합니다.
  9. 선을 복사한 뒤, 차트의 꺾은선 그래프 꼭지점을 선택합니다. 그리고 Ctrl + V 로 선을 붙여넣기합니다.

    엑셀 항목별 평균 차트 평균 추가
    꺾은선 그래프 위로 선을 붙여넣기 합니다.
  10. 꺾은선 차트를 우클릭한 뒤, '윤곽선'을 '윤곽선 없음'으로 변경합니다.

    차트 윤곽선 없음 설정 변경
    꺾은선차트의 윤곽선을 '없음'으로 설정합니다.
  11. 엑셀 '항목별 평균 차트'가 완성되었습니다.

    엑셀 항목별 평균 차트 완성 GIF
    엑셀 항목별 평균 차트가 완성되었습니다.

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

5 4 votes
게시글평점
현재 페이지 댓글알림 신청
알림 설정
guest
8 Comments
Inline Feedbacks
View all comments
GoodDay2u
GoodDay2u
2020년 3월 13일 1:42 오후
게시글평점 :
     

아이디어가 정말 남다르세요 ^^

험퍼딩크
험퍼딩크
2020년 3월 29일 9:18 오후
게시글평점 :
     

예제 다운로드가 안되네용 초보라서 그런가

주꿈이
주꿈이
2020년 4월 13일 8:29 오후
게시글평점 :
     

예제파일의 완성파일 내 시트 "부서별 목표달성률" 강의는 어디서 볼수있나요? 차트에 초과달성률을 못하겠어요 ㅠ

주꿈이
주꿈이
2020년 4월 14일 1:16 오후

아 데이터가 아닌 목표액 항목에 셀값으로 들어갔군요ㅠ 근데 셀서식(감소시 빨강, 증가시 파랑)이 데이터레이블 서식-표시형식에서 변경해도 차트에 안먹히는데 어떻게해야하나요?

ibi****
2020년 5월 21일 1:15 오후
게시글평점 :
     

차트와 그래프의 만남이 이런 효과가 있었다니~ 또 하나 배우고 갑니다.

8
0
여러분의 생각을 댓글로 남겨주세요.x