엑셀 차트 평균선, 중간값이 자동 업데이트 되는 차트 만들기
엑셀 평균선 차트 만들기 목차 바로가기
강의안내
차트의 평균값과 중간값이 지동으로 계산되는 차트를 제작합니다. 이번 강의에서는 OECD 중요국의 물가상승률 데이터를 예제로 각 년도별 물가상승률의 평균값 또는 중간값이 자동으로 계산되는 차트를 만들어보겠습니다.
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [차트강의] 평균선 자동 업데이트 차트 만들기예제파일[차트강의] 평균선 자동 업데이트 차트 만들기완성파일
관련 기초함수
1. 차트 원본 데이터 편집
연도 및 출력값의 종류(평균값 또는 중간값)을 입력할 데이터유효성 목록상자를 생성합니다. '차트' 시트로 이동한 뒤, P2셀과 P3셀에 데이터유효성 목록상자를 생성합니다.
- '차트' 시트에서 P2셀을 클릭합니다. 상단의 [데이터] - [데이터 유효성 목록상자]로 이동합니다.
P2 셀을 선택한 후, 데이터유효성검사로 이동합니다. - '제한대상' 에서 [목록]을 선택한 뒤, '원본' 범위로 '데이터-편집전' 시트의 년도범위 [ A2:A17 ]를 선택하여 입력합니다.
제한 대상으로 '목록'을 선택한 후, 년도범위를 입력합니다. - 마찬가지로 P3셀을 선택한 뒤, 데이터 유효성 목록상자로 이동합니다. 이번에도 '제한대상'으로 [목록]을 선택한 뒤, '원본'에는 “평균값, 중간값"을 항목으로 넣어줍니다.
평균값과 중간값을 항목으로 직접 입력합니다
- '데이터-편집전' 시트로 이동합니다. 국가명이 입력된 머릿글범위 B1:M1 범위를 복사한 뒤, B19셀에 그대로 붙여넣기 합니다.
머릿글을 복사하여 B19셀에 붙여넣기 합니다 - A20셀은 '차트'시트의 C2셀, A21셀은 '차트'시트의 C3셀을 참조하도록 수식을 입력합니다.
'데이터-편집전' 시트의 A20, A21셀에 각 수식을 입력합니다. - VLOOKUP 함수를 사용하여 각 국가의 년도별 물가상승률을 계산합니다. 아래 수식을 복사한 뒤, '데이터-편집전' 시트 B20셀에 붙여넣기 한 후, M20셀까지 오른쪽으로 자동채우기합니다.
=VLOOKUP($A$20,$A$2:$M$17,COLUMN(),0)
수식을 붙여넣기 한 뒤, 오른쪽으로 자동채우기 합니다. - IF 함수와 AVERAGE 함수, 그리고 MEDIAN 함수를 응용하여 평균값과 중간값을 계산합니다. 출력할 값의 종류가 '평균값'일 경우 AVERAGE 함수로 평균을 계산하고, '중간값'일 경우 MEDIAN 함수로 중간값을 계산합니다. 아래 수식을 복사한 뒤, '데이터-편집전' 시트 B21셀에 붙여넣기 한 후, M21셀까지 오른쪽으로 자동채우기하여 마무리합니다.
=IF($A$21="평균값",AVERAGE($B$20:$M$20),MEDIAN($B$20:$M$20))
더욱 간결한 데이터 출력을 위한 셀서식 변경
- B20:M20 셀을 선택한 후, 우클릭한 뒤 [셀 서식]으로 이동합니다.
범위를 선택한 후 우클릭하여 '셀서식'으로 이동합니다. - '사용자 지정' 을 선택한 후, 형식을 [ 0.0 ] 으로 변경합니다.
사용자지정서식을 [ 0.0 ] 으로 변경합니다 - 동일한 원리로, B21:M21 범위를 선택한 후 사용자 지정 서식을 [ 0.0"%" ] 로 변경합니다.
사용자 지정서식을 [ 0.0"%" ] 으로 변경합니다.
- A19:M21 까지 데이터 범위를 선택한 후, 상단의 [삽입] – [추천차트]를 선택합니다.
범위를 선택한 후, [삽입] - [추천차트]로 이동합니다. - [모든차트] 를 선택한 후, [혼합차트] 로 이동합니다. '년도'는 세로막대형차트로, '평균값/중간값'은 꺾은선형차트를 선택한 뒤, '확인'을 눌러 차트 생성을 마무리합니다.
'년도'는 묶은세로막대형, '평균값'은 꺾은선형으로 혼합차트를 생성합니다.
3. 평균선 차트 레이아웃 편집
영상강의에서는 더욱 깔끔한 레이아웃을 위해 원본데이터 양 옆에 '보조열'을 추가하여 제작했습니다. 자세한 내용은 영상강의 5분 55초 이후를 참고해주세요.
- '차트' 시트로 이동한 뒤, C4셀에 아래 수식을 복사하여 붙여넣기 합니다.
="OECD 중요국 물가 상승률 - ["& C2&"년]"
- 연도를 선택하면 C4셀에 값이 "OECE 중요국 물가상승률 - [2020년]" 형식으로 출력됩니다.
년도를 선택하면 값이 업데이트 됩니다. - '데이터-편집전' 시트로 이동한 뒤, 차트를 잘라낸 후 (단축키 : CTRL + X) , '차트' 시트에 붙여넣기 합니다.
차트를 잘라낸 뒤, '차트'시트로 붙여넣기 합니다. - 차트 제목을 클릭한 후, '수식입력줄'로 이동합니다. 이후 C4셀을 참조하도록 수식을 입력하면 차트 제목이 C4셀과 동일한 값으로 자동업데이트 됩니다.
차트 제목을 C4셀과 연동하면 제목이 자동으로 업데이트 됩니다.
- 세로축과 눈금선을 삭제합니다.
차트의 세로축과 눈금선을 삭제합니다. - 가로축과 제목의 글꼴을 원하는 형태로 변경합니다.
차트의 가로축과 제목 글꼴을 변경합니다. - 가독성을 높이기 위하여, 차트에 '데이터레이블'을 추가합니다. 세로막대그래프를 선택한 후, 우클릭 한 뒤 '데이터 레이블 추가'를 클릭합니다.
세로막대 그래프에 데이터레이블을 추가합니다 - '꺾은선 그래프' (평균선) 에는 좌측끝의 값만 클릭한 후 데이터레이블을 추가합니다.
꺾은선 그래프 맨 좌측 끝에 데이터 레이블을 추가합니다 - 차트 데이터에 음수가 있을 경우 차트가 가로축 레이블과 겹쳐서 출력됩니다.
음수 데이터가 있을 경우 가로축과 겹쳐서 출력됩니다 - 가로축을 클릭하여 '축서식'으로 이동한 뒤, '레이블 항목에서 '레이블 위치'를 [낮은쪽]으로 변경해줍니다.
가로축 레이블 위치를 '낮은쪽'으로 변경합니다 - 차트의 색상을 변경합니다.
차트 색상을 원하는 색으로 변경합니다 - 평균선 자동 업데이트 차트가 완성되었습니다.
평균선 자동 업데이트 차트가 완성되었습니다
링크] MS 홈페이지 AVERAGE 함수 사용법 바로가기 (평균값 계산)
링크] MS 홈페이지 MEDIAN 함수 사용법 바로가기 (중간값 계산)
