보고서 작성법 | 목적에 따라 엑셀 그래프 만들기

개념 정보를 정리하는 데 뛰어난 다이어그램과 달리, 그래프는 수치에 따른 통계적인 정보를 나타내는 데 뛰어난 그림입니다. 목적에 따라 3가지 유형의 9가지 기본 그래프가 존재합니다.

첫 번째 유형은 수치의 크기를 정리한 [양 유형]입니다. 길이와 면적을 사용하여 대상의 크기를 표현할 때 사용합니다. 크기를 비교하여 수치에 순위를 매기거나, 비율을 나타냅니다. 해당하는 그래프에는 [막대형], [원형], [면적도]가 있습니다.

두 번째 유형은 수치의 추이를 정리한 [변화 유형]입니다. 수치의 크기보다는 변화하는 부분을 표현할 때 사용합니다. 시간에 따른 변화를 표시하여 변화의 폭을 파악하거나, 장기적인 추세를 확인할 때 좋습니다. 해당하는 그래프에는 [꺾은선형], [영역형]이 있습니다.

세 번째 유형은 수치의 차이를 정리한 [분포 유형]입니다. 어떤 관점이나 범위에서 측정을 통해 분포되어 있는 부분을 확인할 때 사용합니다. 평균 기준보다 뛰어난 점과 부족한 점을 파악할 수 있어 개선할 부분을 확인하기 좋습니다. 해당하는 그래프에는 [히스토그램], [분산형], [방사형]이 있습니다.

각 그래프의 구분법은 아래의 내용을 참조하세요.

 

 

 

크기를 나타내는 그래프 만들기

막대형 그래프는 계열의 크기를 나타내는 막대를 늘어놓은 것입니다. 각 계열의 크기를 나타낼 때 적합하며, 규모의 크기를 한눈에 파악할 수 있습니다.

예를 들어, X사가 영업지원시스템을 강화하는 것으로 예상되는 비용 절감 효과를 연도별로 정리하려고 합니다. 막대형 그래프를 사용하면 아래의 그림처럼 효과 전체가 서서히 증가하는 것을 알 수 있습니다.

아래의 막대형 그래프는 엑셀에서 ‘누적 세로 막대형’ 차트로 그래프를 작성한 후 [도형] 도구에서 ‘텍스트 상자’를 조합하여 작성한 것입니다.

 

막대형 그래프는 각 계열에 수치를 표시할 수 있지만, 강조하고 싶은 것 이외의 정보가 넘쳐나기 때문에 파워포인트나 워드 자료에 붙여넣어 보고할 때 사용한다면 굳이 포함시키지 않고 보고하는 자료에서 숫자를 가져오면 편합니다.

또한, 계열 간의 변화에 대해서도 언급하고자 한다면 계열선을 설정하되 계열 수가 많아질수록 가독성이 떨어지므로 계열이 5개 이하일 때 사용하는 것이 좋습니다.

막대형 그래프에는 세로형과 가로형이 있습니다. 세로형은 항목명(여기에서는 [C2:G2])이 길어지면 보기에 좋지 않으므로 항목명이 10글자를 넘기는 경우에는 가로 막대형 그래프를 사용하는 것이 좋습니다.

또한, 막대형 그래프에는 몇 가지 특수한 종류가 있습니다. 그 가운데 ‘폭포형’ 그래프는 계열 간 차이를 분석할 때 유용합니다.

'폭포형(워터폴)' 그래프는 엑셀의 ‘누적 세로 막대형’ 차트로 그래프를 작성한 후 [데이터 계열]의 [서식 설정]에서 [계열의 중복]을 ‘0%’로 설정하고, [도형] 도구에서 ‘텍스트 상자’를 조합하여 작성한 것입니다.

강조하고 싶은 계열은 [서식]의 [도형 채우기]와 [텍스트 채우기]에서 배경색과 글자색을 설정하여 강조할 수 있습니다.

다만 이 그래프는 마이너스 값을 표현할 수 없습니다. 계열마다 막대 그래프를 만들어 제시할 수도 있지만, 그래프를 위한 전용 표를 작성해야 하므로 [도형] 도구의 ‘사각형, 직사각형’을 조합하여 간단하게 만들 것을 추천합니다.

  

 

 

비율이 큰 순서대로 그래프 만들기

원형 그래프는 계열의 크기를 부채꼴로 나타낸 것입니다. 계열이 적을 때 어떤 시점 계열의 비율을 나타내는 데 적합합니다. 분석 대상 데이터에서 차지하는 상위 몇 개의 계열을 확인하거나 계열 간 비율을 비교하는 데 도움이 됩니다.

예를 들어, X사의 영업 보고서 출력에 관한 비용 절감에 대해 효과가 큰 상위 3개를 파악하고 중점적으로 검증하려고 한다면 대상 데이터를 효과가 큰 순서대로 정렬하고 그것을 원형 그래프로 만들면 아래의 그림처럼 상위 3개 이외의 요소도 포함하여 전체적으로 규모와 비중을 파악할 수 있습니다.

원형 그래프의 계열을 큰 순서대로 정렬하려면 데이터 표를 [데이터] 탭 - [정렬 및 필터] 그룹의 [숫자 내림차순 정렬] 기능을 사용하여 내림차순으로 정렬합니다.

 

 

같은 목적으로 사용할 수 있는 ‘도넛형’ 그래프도 있습니다. 원형 그래프의 가운데에 원형의 여백을 둔 모양으로, 그래프의 총계를 표시하고자 할 때 사용합니다. 중앙 여백의 크기는 사용자가 마음대로 조정할 수 있으므로 사용해야 하는 문자 수에 따라서 조정하면 됩니다.

 

 

비율을 전체적으로 그래프화 하기

면적도는 계열마다 크기가 사각형이 되도록 나열한 것으로, 계열 수가 많을 때 어떤 시점의 비율을 나타내는 데 적합합니다. 원형 그래프와 마찬가지로 분석 대상 데이터에서 차지하는 상위 몇 가지 계열을 확인하거나 계열 간 비율을 비교하는 데 도움이 됩니다.

예를 들어, 비용 절감 대상을 포함한 영업지원시스템이 X사의 IT 비용 중 어느 정도 비중을 차지하는지 파악하려고 한다면 먼저 필요한 것은 분석하고자 하는 관점과 직접 관계되는 것을 대분류로 하여 비용을 분류한 데이터 표를 만드는 것입니다. 이 경우에서는 [시스템]을 대분류로 하여 ‘용역 비용’, ‘제품 비용’, ‘설비 비용’의 계열이 있다고 생각합시다. 이때 아래의 그림과 같이 전체의 비율과 면적도의 너비에 대한 길이를 산출해 둡니다.

 

  

이어서 [도형] 도구를 사용하여 각 시스템의 막대형 그래프를 만듭니다. 각 요소 값에 0.1cm를 곱한 수치를 ‘사각형/직사각형’의 높이로 설정하면 복잡한 계산을 하지 않아도 됩니다.

완성된 막대형 그래프의 계열은 시스템별로 높이가 다르기 때문에 높이를 맞추기 위해서 다음 과정을 진행합니다.

• 그래프의 각 요소를 범위로 선택하고, 마우스 오른쪽 단추를 클릭하여 [그룹화]를 실행합니다.
• 모든 막대에서 같은 작업을 실행합니다.
• 그래프의 높이는 면적도의 높이로 통일합니다.
• 원본 데이터 표의 [막대 그래프 너비]를 바탕으로 각 막대의 너비를 수정합니다.
• 수정한 각 막대를 밀착시켜 하나의 직사각형 모양이 되도록 조정합니다.

여기까지의 과정을 거쳐 아래의 그림과 같은 면적도를 완성했습니다.

면적도를 작성할 때 주의할 점은 같은 값이라도 보는 눈이 다르다는 것입니다. 예를 들어, 영업지원시스템, A 시스템, B 시스템의 제품 비용은 각각 30만원이지만 이 그림의 면적도를 본 사람 중에는 ‘정말로 같은 크기야?’라고 의문을 가지는 사람도 반드시 있을 것입니다. 이를 피하기 위해 면적도를 나타낼 때는 반드시 각 요소에 값을 표기하여 자료를 보는 상대가 오해하지 않도록 해야 합니다.

어필하고 싶은 요소는 배경색이나 테두리, 글꼴을 변경하여 강조할 수 있습니다.

  

 

 

시간에 따른 변화 그래프 만들기

꺾은선형 그래프는 시계형 데이터를 선으로 연결한 것입니다. 시간에 따른 요소의 변화를 나타내는 데 적합합니다. 업무 이익률의 추이를 예상하는 등 대상의 경향을 분석할 때 도움이 됩니다.

예를 들어, X사와 경쟁사인 Y사의 IT 비용에 대해서 과거 5년간의 매출 비율 추이를 분석하고자 합니다. 원본 데이터 표에는 과거 5년치의 양사 ‘매출’과 ‘IT 비용’이 정리되어 있습니다. 후자를 전자로 나눈 값이 ‘판매율’입니다.

판매율 축은 0%가 아닌 0.5%부터 시작해서 1.2%까지 표시할 수 있도록 최소값 : 0.5%, 최대값 : 1.2%로 설정했습니다. 0%부터 표시하면 곡선의 경사가 완만해져 차이가 모호해집니다.

눈금선에서 생략된 부분이 있을 경우 요소의 크기를 상대방이 오해하지 않도록 축이 있는 눈금선에 생략 기호를 반드시 기재해야 합니다. [도형] 도구를 사용하는 방법도 있지만, 모양새도 좋고 많은 노력을 기울이지 않아도 되는 =(등호)를 입력하는 방법도 있습니다. 이때는 그래프 배경색과 같은 색의 텍스트 상자에 =(등호)를 입력합니다.

꺾은선형 그래프의 이해를 돕기 위해 각 요소의 양을 막대 계열로 표시하기도 합니다.

아래 그림의 판매율 곡선만으로는 Y사의 매출이 좋지 않다고 생각할 수 있지만, 매년 매출도 제시하면 ‘매출 확대에 따른 건전한 것’이라고 설명할 수 있습니다.

아래의 꺾은선형 그래프는 엑셀에서 [묶은 세로 막대형 - 꺾은선형, 보조 축] 기능으로 그래프를 작성한 다음 [도형] 도구의 ‘텍스트 상자’를 조합하여 작성한 것입니다.

  

 

양의 변화를 그래프로 만들기

영역형 그래프는 시간별 데이터를 쌓아 선으로 연결한 것입니다. 시간 축에 따른 총량의 변화를 나타내는 데 적합합니다. 시스템 자원의 사용량 측정 등 상한값을 의식한 경향 분석을 할 때 도움이 됩니다.

예를 들어, X사의 영업지원시스템으로 고객용 전표 데이터를 저장하는 외부 디스크의 사용량을 예측해 봅시다. 영역형 그래프로 표시하면 아래 그림과 같이 되므로, 이대로는 8월~9월 사이에 디스크 용량이 꽉차서 사용 불가능으로 표시됩니다.

상한값을 기준선으로 표시하여 어느 시점에서 그 기준선에 도달하는지(영역형 그래프와의 접점)를 명시해야 대응하는 시점을 논의할 수 있습니다. 그리고 각 영역에 대해 데이터의 증가를 억제해야 하는 것이 [전표 데이터]와 [고객 데이터]인 것을 알 수 있습니다.

이 그래프는 데이터 증가량의 기세를 어필하기 위해 데이터 사용량의 눈금에서 생략된 부분이 있습니다. 그러나 사용량에서 차지하는 비중에서 [전표 데이터]의 크기를 어필하고자 한다면, 눈금을 생략하지 않는 것이 볼륨의 크기를 나타내기가 쉽습니다.

영역형 그래프는 추세와 양의 양면을 설명할 수 있습니다. 양의 많고 적음을 어필하고 싶은 경우에는 생략선을 활용하지 않는 것이 좋습니다.

영역형 그래프에는 같은 순서로 이용할 수 있는 ‘100% 기준 누적 영역형’ 그래프가 있습니다. 보기에는 면적 그래프와 비슷하지만 가로축 길이가 일정한 간격으로 되어 있습니다. 총량(100%) 중 사용량의 추이를 확인할 필요가 있을 때 영역형 그래프가 도움이 됩니다.

 

 

 

양의 차이를 그래프로 만들기

히스토그램은 계층별 요소 수를 막대형 그래프로 표시한 것입니다. 양의 차이를 표시할 때 적합합니다. 이용자 수의 격차 등 요소의 동일한 분포를 파악하는 데 도움이 됩니다.

예를 들어, X사의 영업지원시스템으로 영업자 한 명이 일주일에 얼마나 안건을 등록하는지 알고 싶다면 과거 실적을 가지고 도수 분포표를 만들 수 있기 때문에 이를 히스토그램으로 나타내면 아래의 그림과 같습니다.

히스토그램으로 표시하면 등록 안건 수의 추세를 한눈에 파악할 수 있습니다. 단순한 그래프이지만, 볼륨 영역이 명확하므로 표준 레이어를 판별하기가 쉽습니다.

단순하게 ‘총 안건 수 ÷ 영업자 수’로 이용 추세를 파악한다면 [3~4건까지]가 볼륨 영역이라고 오인하지만, 히스토그램을 사용하면 등록 안건 수가 [0~2건까지]와 [4~6건까지]의 두 영역이 볼륨 영역임을 알 수 있습니다.

히스토그램은 엑셀의 분석 도구에서 [히스토그램]으로도 만들 수 있습니다. 도구의 설치 작업이 필요하지만, 다른 통계 처리를 지원하는 기능도 사용할 수 있으므로 단순 집계 이상의 분석에 유용합니다.

분석 도구를 이용하려면 [파일] 탭 - [옵션]을 선택한 후 [Excel 옵션] 창이 나타나면 [추가 기능] 탭에서 [분석 도구]를 지정합니다.

 

 

편차의 관계성을 그래프로 만들기

분산형 그래프는 각각의 데이터를 점으로 기록한 것으로, 요소끼리의 관련성을 나타내는 데 적합합니다. 안건의 수익성과 금액 규모의 관계성을 파악하는 등 요소 차이에서 특정 관점에 따른 추이를 찾을 때 도움이 됩니다.

예를 들어, 작년 IT 시스템 개발 안건에 대해 ‘금액 규모’와 ‘영업 활동 비용’ 두 축으로 만든 그래프에 모든 계약 성립 안건과 계약 해지 안건을 등록한다고 했을 때, 이를 분산형 그래프로 표시하면 아래의 그림과 같습니다.

데이터 표에서는 세로축에 ‘안건명’, 가로축에 ‘금액 규모, 영업 비용, 계약 성립/계약 해지’ 플래그가 있지만, 분산형 그래프에서는 세로축은 ‘영업 비용’, 가로축은 ‘금액 규모’로 설정하고, ‘계약 성립/계약 해지’의 구분은 표식으로 지정합니다.

데이터 표를 보면 안건 9(금액 규모 : 134백만 원, 영업 비용: 15백만 원)가 있지만 분산형 그래프에서는 영업 비용 눈금이 9백만 원 이하이기 때문에 표시되지 않았습니다. 이것은 이 표식을 포함해 버리면 분산형 그래프에서 추세를 나타내기가 어렵기 때문입니다.

분산형 그래프의 데이터에는 통계에는 참고가 되지 않는 값이 가끔 포함되어 있습니다. 엄밀하게 숫자상의 판단보다 상대를 설득하기 위한 근거로 데이터를 사용하고 싶은 경우에는 표준화에서 많이 벗어나는 값은 자의적으로 배제하여 결론을 나타내는 것도 필요합니다.

다음의 분산형 그래프는 엑셀에서 ‘분산형’ 차트로 그래프를 작성한 후 [도형] 도구의 ‘텍스트 상자’, ‘직선’, ‘직각 삼각형’, ‘사각형/직사각형’을 조합하여 작성한 것입니다. 

 

 

최대와 최소의 넓이를 그래프로 만들기

오차 막대형 그래프는 양, 변화, 분포를 나타내는 그래프에 오차 막대를 추가한 것입니다. 평균, 최대값, 최소값을 나타낼 때 적합합니다. 집계 결과에 대한 모범 사례 및 최악의 경우를 파악하는 데 도움이 됩니다.

예를 들어, IT 시스템 개발 안건에서 계약 안건의 금액 규모와 영업 비용의 추세에 대해 평균뿐만이 아니라, 영업 비용이 가장 높은 경우와 가장 낮은 경우도 파악하고자 합니다.

오차 막대는 양을 표시하는 그래프나 변화를 나타내는 그래프에 모두 표시할 수 있습니다. 평균을 계열로 표시하는 그래프에서 그 편차 폭을 쉽게 파악할 수 있습니다.

그러나 오차 막대가 표시하는 것은 최대값과 최소값, 그리고 평균에서 유추할 수 있는 요소의 편향 추세입니다. 이 경우 0~50의 금액 규모는 평균이 최소값의 성향의 추세에서 4%~5.8%로 절반 이상의 안건이 포함되어 있는 것을 알 수 있습니다. 한편 101~150의 금액 규모에서는 격차가 매우 크기 때문에 평균치가 도움이 되지 않는다고 추측할 것입니다.

다음 오차 막대형 그래프는 엑셀에서 ‘묶은 세로 막대형’과 ‘꺾은선형’ 차트로 작성한 것입니다.

 

 

다면적인 평가를 그래프로 만들기

방사형 그래프는 여러 평가 기준에 도달하는 값을 선으로 연결한 것입니다. 표준값에 대한 차이를 여러 관점으로 나타낼 때 사용합니다. 성과 기록표에 의한 지표값의 측정 결과를 파악하는 데 도움이 됩니다.

예를 들어, X사의 영업 활동에 대한 고객 만족도 조사를 실시했다고 가정한다면 조사 결과에서 집계된 관점은 처음에 결정하고, 조사는 ‘나쁘다(1)~ 좋다(5)’의 5단계로 평가하여 몇 가지의 응답을 더한 결과가 각 관점의 점수가 됩니다.

여러 질문의 결과를 합하여 점수를 계산하는 경우 조사 결과를 정리한 표에서 직접 방사형 그래프를 만드는 것이 아니라, 이 그래프가 참조할 데이터 표를 만듭니다.

이 데이터 표에 포함시키는 평가 구분과 그 값을 산출하기 위해 조사 결과 데이터를 집계합니다. 같은 평가 등급에 속하는 수치의 합계를 구할 때는 SUMIF 함수, 개수를 구할 때는 COUNTIF 함수를 사용합니다.

수식 중 $(절대 참조)가 붙은 셀은 값이 변하지 않고, 그 이외(상대 참조)의 셀은 복사한 셀의 위치에 따라 함께 변하므로 수식을 복사하여 다른 셀에 붙여넣어도 그 결과를 제대로 구할 수 있습니다.

아래의 방사형 그래프는 엑셀의 ‘방사형’ 차트로 작성한 것입니다.

  

보고서 작성 원리 70 (엑셀&파워포인트&워드를 이용한) 요시와 준토쿠 지음. 윤신례 역 전문 컨설턴트가 알려주는 자료 정리 + 문서 작성 기술