엑셀차트3-목표값대비 초과/미달 표현하기

 

엑셀차트에는 종류가 많은데요. 그중 막대그래프는 간단하면서도, 직관적으로 값의 비교 혹은 추이를 파악할 수 있게 해줍니다. 오늘은 이 막대그래프를 이용해서 목표값대비 초과 혹은 미달하는 값을 표현해 보겠습니다. 엑셀차트의 기본기능에는 없고, 몇가지 꼼수를 부려야 가능하답니다. 언뜻 보기엔 쉬워보이기도 하지만, 막상 하려면 아리송할텐데요. 한번만 따라해 보시면, 별거 아니구나라는 생각을 하게 되실 겁니다.^^

 

누적 막대 그래프 생성

기본 데이터 작업

 ▼ 아래의 네사람(John, Smith, Jack, Ted)의 주간 근무시간을 기준시간 대비 비교를 할 수 있는 막대그래프를 만들어 볼텐데요. 일단, 아래의 표처럼 차트를 만들기 위한 테이블을 하나 만들어야 합니다.

 

 

 ▼ 사용된 수식은 아래와 같습니다. 기준시간 대비 초과/미달시간을 따로 계산한 것입니다.

누적 막대그래프 생성

▼ 작업한 테이블을 모두 선택한 후 삽입탭에서 누적 세로 막대형 차트를 선택합니다.

 

 

▼ 누적 세로 막대형 차트가 생성되었습니다. 

 

차트 행렬 전환 

▼ 혹시 아래처럼 차트가 생성되신 분은 차트 아무곳이나 오른쪽 버튼을 클릭한 다음, 데이터선택 - 행열전환 을 하시면 됩니다.

 

 

 

 

기준선 생성하기

▼ 기준선을 생성해 볼텐데요. "기준시간" 데이터 레이블을 "계열 차트 종류 변경"을 선택하여 꺽은선 그래프로 변경합니다.

 

 

▼ "기준시간"데이터 레이블을 다시 오른쪽 버튼을 클릭하여, 선형 추세선을 추가합니다. 

 

▼ 추세선이 차트의 가로축 전체에 대해 표현되도록, 추세선 옵션에서 앞으로, 뒤로 0.5씩 예측구간을 추가합니다.

 

초과/미달시간에 대한 데이터 레이블 추가하기

레이블추가

▼ 추가한 추세선의 서식을 수정하고 나니, 기준시간을 표현한 기준선처럼 되었습니다. 이제 추세선과, 기준시간에 대한 범례를 삭제하고 초과시간과 미달시간에 대한 데이터레이블을 표현합니다. 아래 처럼 미달시간과 초과시간이 숫자로 표현되어 기준시간에 대한 초과/미달 근무시간이 명확해 졌습니다.

▼ 그런데, 값이 0인 근무시간은 보이지 않으면 더 깔끔해 질것 같네요.

 

데이터 레이블 표시형식 변경

▼ 초과 및 미달시간에 대한 데이터 레이블 서식을 수정합니다. 데이터 레이블 서식창에서 "표시형식" - "사용자 지정" 을 선택합니다. "서식 코드"를 입력하는 곳에 아래와 같이 입력합니다. 유의하실 점은 초과시간에 대한 표시형식을 입력후 추가버튼을 누르시고 서식창을 닫은 후 미달시간에 대한 표시형식을 입력하실때는 데이터 레이블 서식창을 다시 열어서 하셔야 합니다.

▼ 초과시간 : ##0;-##0;" "

▼ 미달시간 : -##0;-##0;" "

 

 

 ▼ 데이터 계열 및 레이블 서식을 수정한 완성본 차트입니다.

 

읽어 주셔서 감사합니다.

 

다음에는 좀 더 응용한 두개의 기준선과 떠다니는 막대그래프에 대해 얘기해 보겠습니다. 아래는 120/80 (수축기 혈압/이완기 혈압) 이란 두개의 기준에 대한 혈압측정치의 시간별 추이에 대해 파악할 수 있는 월별 혈압측정표입니다. 이를 이용하면12월에 가까워지면서 혈압이 조금 높아진것을 한눈에 알수 있습니다.