聰明不如鈍筆
총명불여둔필

엑셀 그래프에 평균선 넣는 법


마이크로소프트(MS) 엑셀로 그래프를 그리다 보면 위 그림처럼 평균선을 따로 넣고 싶을 때가 있습니다.


그런데 엑셀에서 차트 종류를 살펴봐도 어떤 걸 써야 저렇게 나오는지 잘 눈에 띄지 않습니다. 


물론 평균이 얼마인지 알고만 있으면 일단 그래프를 그린 다음에 해당 위치에 도형을 넣듯이 직선 하나를 그려주면 그만입니다.


단, 이럴 때는 자료가 변해서 평균이 달라지면 그때마다 다시 선을 넣어야 하는 불편함이 있습니다. 또 정확한 지점에 그래프가 들어간다는 보장도 없지요.


과연 엑셀에서 어떤 그래프 조합을 선택해야 저런 그림을 그릴 수 있을까요?


한국야구위원회(KBO) 홈페이지에 나와 있는 프로야구 연도별 평균 경기시간 데이터를 가지고 엑셀 그래프에 평균선을 넣는 연습을 해보도록 하겠습니다.


우리가 연습에 쓸 엑셀 파일은 일단 아래처럼 생겼습니다. C열에 평균이 나온 게 일단 중요한 힌트입니다.



엑셀에서 평균을 구할 때 AVERAGE 함수를 쓴다는 건 다들 아실 터. 이 파일에서는 C2에 =AVERAGE($B$2:$B$36)를 입력하면 평균이 나옵니다.


엑셀 초보자들께 말씀드리자면 셀 위치($B$2:$B$36)를 쓰면서 $를 넣은 건 이 수식을 다른 셀에 붙여 넣더라도 셀 위치를 바꾸지 말라는 뜻입니다.


원래 엑셀에서는 셀을 복사하면 위치에 따라 수식이 바뀝니다. 예를 들어 C2에 그냥 =AVERAGE(B2:B36)라고 쓴 다음에 이걸 C3에 복사했으면 =AVERAGE(B3:B37)이 됐을 겁니다.


이걸 피하려고 $를 붙여주는 겁니다. 


사실 여기서 우리는 C2에 입력한 걸 C36까지 복사할 거니까 =AVERAGE(B$2:B$36)이라고만 썼어도 충분했습니다. 행만 바뀌지 않으면 되니까요.


마찬가지로 옆으로 복사하는데 열이 바뀌지 않았으면 좋겠다고 할 때는 =AVERAGE($B2:$B36)처럼 쓸 수 있습니다. $는 직접 입력하셔도 되고 F4를 눌러서 원하는 위치를 선택하셔도 됩니다.


입력을 마치셨으면 일단 경기시간 데이터로 막대 그래프를 하나 그립니다. 그다음 그래프 위에서 마우스 오른쪽 버튼을 눌러 '데이터 선택'을 선택합니다. 



화면이 바뀌면 추가를 선택합니다. 그래프를 그릴 때 처음부터 C행까지 함께 선택하는 방법도 있습니다. 어느 쪽이든 편하신 대로 하시면 됩니다. 



어디를 선택해야 할까요? 물론 평균을 입력해 둔 C2:C36입니다. $가 무슨 뜻인지 아직도 모르시는 분은 아니 계시죠?



다음 화면으로 넘어가면 벌써 막대 그래프가 올라와 있습니다. 여기까지 오셨으면 절반은 성공입니다. 이렇게 그리면 원래 있던 경기시간은 계열1이 되고, 평균은 계열2가 됩니다.



확인을 눌러서 막대 그래프가 나오고 나면 다시 마우스 오른쪽 버튼을 누릅니다. 이번에는 데이터 선택 위에 있는 '계열 차트 종류 변경'을 선택합니다.



그러면 다음 같은 화면이 나올 텐데 계열2를 '꺾은선형'으로 바꿔줍니다. 계열2를 고른 건 당연히 평균이 계열2에 들어 있기 때문입니다.



짜잔, 선 그래프로 평균이 들어갔습니다. 사실 여기까지 하셨으면 끝난 거나 마찬가지입니다. 맨 위에 있는 그림처럼 바꾸는 건 그냥 취향 문제일 뿐입니다. 



이걸 좀더 응용하면 아래 그림처럼 평균선을 여러 개 넣는 것도 가능합니다. 각 구간별로 평균을 계산하는 열을 만든 다음 계열을 추가하기만 하면 됩니다.



또 당연히 막대 그래프에만 평균선을 넣을 수 있는 건 아닙니다. 원칙적으로는 모든 그래프에 평균선을 넣는 게 가능합니다. 실제로 그게 의미가 있는지 아닌지를 차치한다면 말입니다.



지난 번에 위도, 경도를 가지고 지도 위에 점을 찍는 걸 소개해 드릴 때도 말씀드렸지만 역시 엑셀은 절대 그리 나쁜 시각화 도구가 아닙니다. 

댓글, 2

  • 댓글 수정/삭제
    2020.09.14 02:27

    비밀댓글입니다

    •  수정/삭제 kini
      2020.09.14 15:56 신고

      지금은 B2~B36 전체 평균을 계산하는 구조인데
      이를 B2~B10, B11~B20… 등으로 구분해서
      각각 평균을 계산하면 된다는 말씀이었습니다.

account_circle
vpn_key
web

security

mode_edit
Kidult/Excelsior | 카테고리 다른 글 더 보기