평균선 자동 업데이트 차트

강의안내

이번 강의에서는 평균값과 중간값이 자동으로 계산되어 표시되는 차트를 만들어보겠습니다. OECD 주요국의 연도별 물가상승률 데이터를 예제로 활용하여, 사용자가 선택한 연도와 출력값(평균값/중간값)에 따라 차트가 실시간으로 업데이트되는 동적 평균선 차트를 단계별로 제작합니다.

관련 기초함수

1. 차트 원본 데이터 편집

연도와 출력값의 종류(평균값 또는 중간값)를 선택할 수 있도록 데이터 유효성 목록상자를 생성합니다. '차트' 시트로 이동한 뒤, P2셀과 P3셀에 각각 데이터 유효성 목록상자를 만들어줍니다.

1. '차트' 시트의 P2셀을 선택한 뒤, 상단 메뉴에서 [데이터] - [데이터 유효성 검사]로 이동합니다.

   

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

   

3. 같은 방식으로 P3셀을 선택한 뒤 데이터 유효성 검사로 이동합니다. '제한 대상'으로 [목록]을 선택한 뒤, '원본'에는 "평균값, 중간값"을 직접 입력하여 항목으로 등록합니다.

   

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

   

2. A20셀은 '차트' 시트의 C2셀을, A21셀은 '차트' 시트의 C3셀을 참조하도록 수식을 입력합니다. 이렇게 하면 사용자가 차트 시트에서 선택한 값이 데이터 영역에 그대로 반영됩니다.

   

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

   

4. IF 함수와 AVERAGE 함수, 그리고 MEDIAN 함수를 조합하여 평균값과 중간값을 계산합니다. 출력 값이 '평균값'이면 AVERAGE 함수로 평균을, '중간값'이면 MEDIAN 함수로 중간값을 반환합니다. 아래 수식을 복사한 뒤, '데이터-편집전' 시트 B21셀에 붙여넣은 후, M21셀까지 오른쪽으로 자동 채우기하여 마무리합니다.
   =IF($A$21="평균값",AVERAGE($B$20:$M$20),MEDIAN($B$20:$M$20))

더욱 간결한 데이터 출력을 위한 셀 서식 변경

1. B20:M20 범위를 선택한 후 우클릭하여 [셀 서식]으로 이동합니다.

   

2. '사용자 지정'을 선택한 뒤, 형식을 [ 0.0 ]으로 변경합니다.
   
3. 같은 방식으로 B21:M21 범위를 선택한 뒤, 사용자 지정 서식을 [ 0.0"%" ]로 변경합니다. 이렇게 하면 평균값/중간값이 백분율 형태로 깔끔하게 표시됩니다.
   

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

   

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

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

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

   

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

   

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

   

1. 세로축과 눈금선을 삭제하여 차트를 깔끔하게 정리합니다.

   

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

   

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

   

4. '꺾은선 그래프'(평균선)에는 좌측 끝의 값만 클릭한 뒤 데이터 레이블을 추가합니다. 평균선 시작 지점에만 값을 표시하면 차트가 한결 깔끔해집니다.

   

5. 차트 데이터에 음수 값이 포함된 경우, 가로축 레이블이 막대와 겹쳐서 출력되는 문제가 발생합니다.

   

6. 가로축을 클릭하여 '축 서식'으로 이동한 뒤, '레이블' 항목에서 '레이블 위치'를 [낮은 쪽]으로 변경해줍니다. 이렇게 하면 가로축 레이블이 차트 아래쪽으로 이동하여 막대와 겹치지 않습니다.

   

7. 차트의 색상을 원하는 형태로 변경합니다.

   

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

   

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