차트 평균선, 중간값까지 한번에! :: 자동 업데이트 차트

평균값과 중간값을 자동으로 계산하여 출력하는 '평균선 출력' 차트 제작방법을 단계별로 알아봅니다.

홈페이지 » 차트 평균선, 중간값까지 한번에! :: 자동 업데이트 차트

엑셀 차트 평균선, 중간값이 자동 업데이트 되는 차트 만들기 

엑셀 평균선 차트 만들기 목차 바로가기
강의안내

차트의 평균값과 중간값이 지동으로 계산되는 차트를 제작합니다. 이번 강의에서는 OECD 중요국의 물가상승률 데이터를 예제로 각 년도별 물가상승률의 평균값 또는 중간값이 자동으로 계산되는 차트를 만들어보겠습니다.

차트 평균선 OECD 중요국 물가상승률 예제 GIF
이번 강의에서는 국가별 OECD 물가상승률 차트를 제작합니다
영상강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

  • [차트강의] 평균선 자동 업데이트 차트 만들기
    예제파일
  • [차트강의] 평균선 자동 업데이트 차트 만들기
    완성파일

관련 기초함수

1. 차트 원본 데이터 편집

데이터 유효성 목록상자 만들기

연도 및 출력값의 종류(평균값 또는 중간값)을 입력할 데이터유효성 목록상자를 생성합니다. '차트' 시트로 이동한 뒤, P2셀과 P3셀에 데이터유효성 목록상자를 생성합니다.

  1. '차트' 시트에서 P2셀을 클릭합니다. 상단의 [데이터] - [데이터 유효성 목록상자]로 이동합니다.

    1. 데이터 유효성 목록상자 만들기
    P2 셀을 선택한 후, 데이터유효성검사로 이동합니다.

  2. '제한대상' 에서 [목록]을 선택한 뒤, '원본' 범위로 '데이터-편집전' 시트의 년도범위 [ A2:A17 ]를 선택하여 입력합니다.

    2 차트 데이터 유효성 목록상자 선택
    제한 대상으로 '목록'을 선택한 후, 년도범위를 입력합니다.

  3. 마찬가지로 P3셀을 선택한 뒤, 데이터 유효성 목록상자로 이동합니다. 이번에도 '제한대상'으로 [목록]을 선택한 뒤, '원본'에는 “평균값, 중간값"을 항목으로 넣어줍니다.

    3 평균값 중간값 항목 입력
    평균값과 중간값을 항목으로 직접 입력합니다

차트를 출력할 원본 데이터 만들기

  1. '데이터-편집전' 시트로 이동합니다. 국가명이 입력된 머릿글범위 B1:M1 범위를 복사한 뒤, B19셀에 그대로 붙여넣기 합니다.

    4 차트 평균값 국가명 머릿글 복사 붙여넣기
    머릿글을 복사하여 B19셀에 붙여넣기 합니다

  2. A20셀은 '차트'시트의 C2셀, A21셀은 '차트'시트의 C3셀을 참조하도록 수식을 입력합니다.

    4 차트 데이터 입력
    '데이터-편집전' 시트의 A20, A21셀에 각 수식을 입력합니다.

  3. VLOOKUP 함수를 사용하여 각 국가의 년도별 물가상승률을 계산합니다. 아래 수식을 복사한 뒤, '데이터-편집전' 시트 B20셀에 붙여넣기 한 후, M20셀까지 오른쪽으로 자동채우기합니다.
    =VLOOKUP($A$20,$A$2:$M$17,COLUMN(),0)

    5 차트 국가별 물가 상승률 계산 VLOOKUP 함수
    수식을 붙여넣기 한 뒤, 오른쪽으로 자동채우기 합니다.

  4. IF 함수AVERAGE 함수, 그리고 MEDIAN 함수를 응용하여 평균값과 중간값을 계산합니다. 출력할 값의 종류가 '평균값'일 경우 AVERAGE 함수로 평균을 계산하고, '중간값'일 경우 MEDIAN 함수로 중간값을 계산합니다. 아래 수식을 복사한 뒤, '데이터-편집전' 시트 B21셀에 붙여넣기 한 후, M21셀까지 오른쪽으로 자동채우기하여 마무리합니다.
    =IF($A$21="평균값",AVERAGE($B$20:$M$20),MEDIAN($B$20:$M$20))
더욱 간결한 데이터 출력을 위한 셀서식 변경
  1. B20:M20 셀을 선택한 후, 우클릭한 뒤 [셀 서식]으로 이동합니다.

    7 차트 깔끔한 표시를 위한 셀 서식 변경
    범위를 선택한 후 우클릭하여 '셀서식'으로 이동합니다.

  2. '사용자 지정' 을 선택한 후, 형식을 [ 0.0 ] 으로 변경합니다.
    7 사용자 지정 서식 변경
    사용자지정서식을 [ 0.0 ] 으로 변경합니다
  3. 동일한 원리로, B21:M21 범위를 선택한 후 사용자 지정 서식을 [ 0.0"%" ] 로 변경합니다.
    7 차트 평균값 중간값 표시형식 변경
    사용자 지정서식을 [ 0.0"%" ] 으로 변경합니다.

2. 혼합 차트 만들기

  1. A19:M21 까지 데이터 범위를 선택한 후, 상단의 [삽입] – [추천차트]를 선택합니다.
    8 범위 선택 후 차트 삽입
    범위를 선택한 후, [삽입] - [추천차트]로 이동합니다.
  2. [모든차트] 를 선택한 후, [혼합차트] 로 이동합니다. '년도'는 세로막대형차트로, '평균값/중간값'은 꺾은선형차트를 선택한 뒤, '확인'을 눌러 차트 생성을 마무리합니다.

    8 차트 혼합 차트 만들기
    '년도'는 묶은세로막대형, '평균값'은 꺾은선형으로 혼합차트를 생성합니다.

3. 평균선 차트 레이아웃 편집

영상강의에서는 더욱 깔끔한 레이아웃을 위해 원본데이터 양 옆에 '보조열'을 추가하여 제작했습니다. 자세한 내용은 영상강의 5분 55초 이후를 참고해주세요.

연도가 자동으로 업데이트되는 차트제목 만들기

  1. '차트' 시트로 이동한 뒤, C4셀에 아래 수식을 복사하여 붙여넣기 합니다.
    ="OECD 중요국 물가 상승률 - ["& C2&"년]"
  2. 연도를 선택하면 C4셀에 값이 "OECE 중요국 물가상승률 - [2020년]" 형식으로 출력됩니다.

    차트제목 자동 업데이트 GIF
    년도를 선택하면 값이 업데이트 됩니다.

  3. '데이터-편집전' 시트로 이동한 뒤, 차트를 잘라낸 후 (단축키 : CTRL + X) , '차트' 시트에 붙여넣기 합니다.

    8 차트 잘라서 붙여넣기
    차트를 잘라낸 뒤, '차트'시트로 붙여넣기 합니다.

  4. 차트 제목을 클릭한 후, '수식입력줄'로 이동합니다. 이후 C4셀을 참조하도록 수식을 입력하면 차트 제목이 C4셀과 동일한 값으로 자동업데이트 됩니다.

    9 차트 제목 자동 업데이트
    차트 제목을 C4셀과 연동하면 제목이 자동으로 업데이트 됩니다.

차트 레이아웃 꾸미기

  1. 세로축과 눈금선을 삭제합니다.

    9 차트 세로축 및 눈금선 제거
    차트의 세로축과 눈금선을 삭제합니다.

  2. 가로축과 제목의 글꼴을 원하는 형태로 변경합니다.

    9 차트 가로축과 제목 글꼴 변경
    차트의 가로축과 제목 글꼴을 변경합니다.

  3. 가독성을 높이기 위하여, 차트에 '데이터레이블'을 추가합니다. 세로막대그래프를 선택한 후, 우클릭 한 뒤 '데이터 레이블 추가'를 클릭합니다.

    9 세로막대 차트 데이터 레이블 추가
    세로막대 그래프에 데이터레이블을 추가합니다

  4. '꺾은선 그래프' (평균선) 에는 좌측끝의 값만 클릭한 후 데이터레이블을 추가합니다.

    9 꺾은선 그래프 맨 좌측 끝 차트 데이터레이블 추가
    꺾은선 그래프 맨 좌측 끝에 데이터 레이블을 추가합니다

  5. 차트 데이터에 음수가 있을 경우 차트가 가로축 레이블과 겹쳐서 출력됩니다.

    9 차트 데이터 음수, 가로축 겹침
    음수 데이터가 있을 경우 가로축과 겹쳐서 출력됩니다

  6. 가로축을 클릭하여 '축서식'으로 이동한 뒤, '레이블 항목에서 '레이블 위치'를 [낮은쪽]으로 변경해줍니다.

    9 차트 레이블 위치 낮은쪽 변경
    가로축 레이블 위치를 '낮은쪽'으로 변경합니다

  7. 차트의 색상을 변경합니다.

    9 차트 색상 변경
    차트 색상을 원하는 색으로 변경합니다

  8. 평균선 자동 업데이트 차트가 완성되었습니다.

    차트 평균선 차트 완성 GIF
    평균선 자동 업데이트 차트가 완성되었습니다

링크] MS 홈페이지 AVERAGE 함수 사용법 바로가기 (평균값 계산)
링크] MS 홈페이지 MEDIAN 함수 사용법 바로가기 (중간값 계산)

0 0 투표
게시글평점
guest
24 댓글
Inline Feedbacks
모든 댓글 보기
우공이산
우공이산
2020년 3월 17일 4:24 오후
게시글평점 :
     

안녕하세요~
예제파일 다운로드만 클릭하면
이 페이지에 연결할 수 없음 이라는 메세지가 나오면서 오류가 납니다.
다운 받을 수 있는 경로가 따로 있는 건가요?
감사합니다~

leesh0630
leesh0630
2020년 3월 26일 1:43 오후
게시글평점 :
     

우와!!!!! 항상 손으로 평균값 구해서 선 만들고 눈대중으로 맞췄는데 자동화되면 정말 편하겠네요
앞으로도 좋은 강의 만들어주세요
좋은 강의 감사합니다

크리슈
크리슈
2020년 3월 30일 6:47 오전
게시글평점 :
     

너무 좋은 강의입니다!! 감사합니다!!

추억나무
추억나무
2020년 4월 7일 9:06 오전
게시글평점 :
     

꼭 필요한 강의였어요 감사합니다!!

갓쩌리
갓쩌리
2020년 4월 9일 7:08 오후
게시글평점 :
     

감사합니다 !!

주꿈이
주꿈이
2020년 4월 10일 5:21 오후
게시글평점 :
     

정말 유용합니다 감사합니다.!!

엑린이
엑린이
2020년 4월 14일 11:22 오후
게시글평점 :
     

잘 배우고 갑니다

엘린꼬마
2020년 4월 26일 4:50 오후
게시글평점 :
     

오빠두 엑셀 짱짱 입니다 덕분에 열공모드 돌입이에요~

뭉이
뭉이
2020년 6월 5일 6:27 오후
게시글평점 :
     

차트 정말 중요했었는데 필요한 자료 올려주셔서 감사합니다!

JakeH
2020년 6월 8일 2:47 오후
게시글평점 :
     

이해하게 쉽게 설명해주셔서 감사합니다!!

sp5
sp5
2020년 6월 22일 10:36 오전
게시글평점 :
     

구글독스에서도 되는지 해보겠습니다 감사합니다!

imeani
imeani
2020년 7월 16일 2:19 오후
게시글평점 :
     

감사합니다.

망고
망고
2020년 7월 26일 5:25 오후
게시글평점 :
     

어렵지만 열심히 배워보겠습니다

ki72****
ki72****
2020년 9월 1일 11:14 오전
게시글평점 :
     

덕분에 엑셀 챠트 공부 다시 시작합니다.

딱밤한대
딱밤한대
2020년 9월 1일 11:54 오전
게시글평점 :
     

감사합니다! 매번 좋은 자료를 올려주셔서.

딱밤한대
딱밤한대
2020년 9월 1일 11:54 오전
게시글평점 :
     

이런 자료들이 모여서 정말 좋은 사이트가 될껍니다 ㅎㅎ 화이팅이에요~

박상현
박상현
2020년 9월 10일 10:06 오전
게시글평점 :
     

선생님 강의에 감동받았습니다 ㅠㅠ
하나 여쭤보고싶은데 엑셀은 버전 몇짜리 사용하시는건가요.. 전 추천차트가 안나와서요 ㅠ

박상현
박상현
2020년 9월 12일 12:50 오후
답글 남기기  오빠두엑셀

진심으로 감사합니다!!!!

미성
미성
2020년 11월 2일 2:32 오후
게시글평점 :
     

잘 배우고 갑니다
감사합니다

Vagabond
Vagabond
2020년 12월 4일 10:20 오후
게시글평점 :
     

강의 감사합니다.

프랑켄
프랑켄
2020년 12월 12일 9:42 오후
게시글평점 :
     

오늘 배운 차트 만들기는 실무에서도 많이 도움될것같습니다 계속 따라하니 잘 되네요 감사합니다 ㅎㅎ

방랑시인쮸
방랑시인쮸
2021년 1월 24일 8:18 오후
게시글평점 :
     

잘 배우고 갑니다.

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