엑셀 그래프 선 추가 - egsel geulaepeu seon chuga

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

access_time 2017. 3. 18. 01:53

마이크로소프트(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에 들어 있기 때문입니다.

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

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

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

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

요즘은 erp를 쓰지 않는 회사가 거의 없지만, 그럼에도 불구하고 기능의 한계 때문에 데이터를 내려 받아 엑셀 작업을 하는 경우가 많이 있습니다. 엑셀을 많이 이용해서 분석을 하는 편인데요.

독자분들께 소개할 만한 쉬우면서도 유용한 엑셀 팁이 없을까 고민하던 중 옛날에 차트 그리면서 막대그래프와 꺾은선 그래프를 하나의 차트에 표시하지 못해서 쩔쩔 매던 기억이 나서 오늘은 그것을 소개드려볼까 합니다.

참고로 이 글은 엑셀2007 버전입니다.

먼저 간단하게 테이블(표)를 하나 작성합니다.

차트를 그리기 위해서 차트를 그릴 영역을 선택한 다음 <삽입>-><차트>-><꺾은선그래프>를 선택합니다.

그러면 아래와 같은 꺽은선그래프가 생성이 됩니다.

그런 다음에 막대그래프를 생성시켜야 하는데, 이를 위해서 막대그래프로 넣을 데이터를 추가해 보겠습니다. 매출 정도로 하면 되겠네요.

막대그래프를 추가하기위해 아래와 같이 차트를 오른 클릭 -> 데이터선택을 해 줍니다.

그런 다음에 아래의 데이터 원본 선택 창에서 추가 버튼을 눌러 주고 계열 편집 창이 뜨면 계열 이름에는 "매출"이라고 적힌 셀을 선택하고 계열 값에는 매출 숫자 영역 전체를 선택합니다.

그리고 확인을 눌러줍니다. 그랬더니...

어럽쇼... 이런 모양이 되어 버렸네요. 이것은 매출액 숫자 단위와 배, 사과의 매출수량 단위 차이가 너무 커서 발생한 것입니다. 이 문제를 해결하기 위해서는 두 데이터가 참조하는 숫자단위가 달라야 하는데, 이를 위해 왼쪽 축이 아닌 오른쪽에 보조축을 생성시켜서 매출데이터가 보조축을 참조하도록 하면 됩니다.

먼저 녹색의 꺽은 선을 오른 클릭 -> 데이터계열서식을 클릭해 줍니다.

그리고 데이터계열 지정을 보조 축으로 바꿔주면 그래프가 아래와 같이 바뀝니다.

이렇게 보니까 그래프가 너무 난잡해 보입니다. 여기서 매출 그래프를 막대로 바꾸겠습니다.

방법은 매출그래프를 오른 클릭 -> 계열 차트 종류 변경 입니다. 세로 막대로 바꿔보겠습니다.

이렇게 하면 아래와 같은 막대그래프+꺾은선그래프의 차트가 만들어집니다.

혹시 사과와 배의 꺽은선이 변동이 너무 심하게 보인다고 느껴지시면 왼쪽 축 부분을 오른 클릭->축 서식으로 들어가서 눈금 값의 최소, 최대치를 조정해 주면 됩니다.

내용은 길지만 실제로 해 보시면 5분 만에 마스터할 수 있습니다.

이 차트를 읽고 의미를 부여할 수 있다면 더 유능한 직원이 되겠지요. 예를 들면 "배의 판매 수량이 늘어날 때 매출이 늘고, 사과 판매수량은 대체로 상관이 없다." 이 정도 일까요?

사실 이 부분은 엑셀로 간단하게 활용할 수 있는 분석도구인 "상관분석"과 "회귀분석"을 이용하면 검증을 할 수 있습니다만 여기서는 설명을 생략하도록 하겠습니다.

감사합니다.