• 최초 작성일: 2022-03-12
  • 최종 수정일: 2022-03-12
  • 조회수: 1,817 회
  • 작성자: 엑셀러 권현욱
  • 강의 제목: 파레토 차트로 ABC 분석하기

엑셀러 권현욱

들어가기 전에ㅡ분구필합 합구필분

나관중의 <삼국지연의>는 이렇게 시작합니다.

話說, 天下大勢, 分久必合, 合久必分.
무릇 천하의 대세란 나눠진 지 오래되면 반드시 합쳐지고, 합져진 지 오래되면 반드시 나눠진다.

말도 많고 탈도 많았던 제20대 대통령 선거가 끝이 났습니다. 어느 쪽도 과반을 달성하지 못했고 1, 2위 격차가 근소하기에 뜻한 바를 이루지 못한 쪽은 마음을 추스리는데 적지 않은 시간이 걸리리라 생각합니다.

50년 쯤 경험 하다보니, 세상에 절대적으로 좋거나 나쁜 일은 없으며 모든 것이 새옹지마요 전화위복임을 알게 됩니다. 포용과 관용으로 협치하기를, 그래서 그때의 선택이 후회로 남지 않기를 기원해 봅니다.



이번 강의는 아래 동영상 강의 내용을 보완하기 위해 만들었습니다. 동영상을 먼저 보시면 이해하기 쉽습니다.

ABC 분석과 파레토 분석 그리고 80대 20 법칙

ABC 분석이란 '관리할 대상을 세 그룹(예를 들어 A, B, C)으로 나누고, A 그룹에 관리 노력을 집중함으로써 효과를 높이려는 분석 방법'을 말합니다.

예를 들어서 일정 기간 동안 판매된 제품 중에서 상/하위 20%에 해당하는 제품을 파악한 다음, 상위 제품에 대해서는 공격적인 프로모션을 진행하고 매출 하위 품목들은 품목수를 정비하기로 의사결정 할 수 있습니다.

이 분석 방법은 이탈리아 경제학자인 알프레도 파레토 Alfred Pareto에 의해 도출된 분석 방법이기에 '파레토 분석' 또는 '80대 20의 법칙'이라고도 부릅니다.

이번 시간에 만들어 볼 차트입니다. 엑셀 2016 버전부터는 파레토 차트가 엑셀의 기본 차트에 포함되어 있습니다. 물론 그 이전 버전에서도 어렵지 않게 파레토 차트를 만들 수 있습니다.

로딩 중...

완성 예

파레토 차트 만들기(엑셀 2016 이전)

1. 왼쪽과 같은 날 것(?) 그대로의 데이터로는 [완성 예]와 같은 파레토 차트를 만들 수 없습니다. 데이터와 차트의 관계는 물체와 그림자 간의 관계와 비슷합니다. 그림자 모양을 바꾸려면 물체의 모양을 바꿔야 합니다. 그림자를 바꾸는 게 아니고.

로딩 중...

2. '구성비'와 '누적구성비'를 구합니다. D4, E4, E5 셀에는 다음과 같은 수식을 입력하고 채우기 핸들을 이용하여 수식을 아래로 복사합니다.

D4: =C4/$C$10
E4: =D4
E5: =D5+E4
로딩 중...

3. CTRL 키를 이용하여 떨어져 있는 영역인 B3:B9, D3:E9 영역을 범위로 지정하고 [삽입] 탭 - [차트] 그룹 - [추천 차트] 명령을 클릭합니다. [차트 종류 변경] 대화상자에서 [모든 차트] 탭에서 [혼합형] 차트를 선택합니다.

로딩 중...

4. 구성비 계열(파란 막대)을 마우스 오른쪽 버튼으로 클릭하고 [데이터 레이블 추가] 메뉴를 선택합니다.

로딩 중...

5. 누적구성비(꺾은선)에 대해서도 동일한 작업을 진행하여 데이터 레이블을 추가합니다. 여기까지 마치면 차트 모양이 아래와 같은 형태로 됩니다.

로딩 중...

6. '세로 (값) 축'을 마우스 오른쪽 버튼으로 클릭하고 [축 서식] 메뉴를 선택합니다. [축 서식] 작업창에서 최대값을 '1'로 수정합니다.

로딩 중...

7. 구성비 계열(파란 막대)을 마우스 오른쪽 버튼으로 클릭하고 [데이터 계열 서식] 메뉴를 선택합니다. 작업창에서 [계열 겹치기], [간격 너비]를 각각 0으로 지정합니다.

로딩 중...

8. 구성비 계열의 레이블을 마우스 오른쪽 버튼으로 클릭하고 [데이터 레이블 서식] 명령을 선택합니다. 작업창에서 [레이블 위치] - [가운데]로 지정합니다.

로딩 중...

9. 데이터 레이블의 색을 흰색으로 지정합니다. 차트 제목을 적당한 것으로 변경하고 차트의 나머지 서식은 자신의 취향에 따라 적절히 조정하여 완성합니다.

로딩 중...

ABC 분석하기(엑셀 2016)

아래에서 소개하는 내용은 엑셀 2016 버전부터 지원하는 파레토 차트를 이용하였다는 점을 제외한 나머지 방법은 2016 이전 버전에서도 동일하게 사용할 수 있습니다.

1. 예제 파일의 '2' 시트에서 금액을 기준으로 내림차순 정렬합니다. 합계 부분을 제외하고 범위로 지정한 것과 활성화 된 셀이 금액 필드(D3 셀)에 있다는 점에 유의하세요.

로딩 중...

2. '금액구성비'와 '누적구성비'를 추가합니다. E3, F3, F4 셀에 다음 수식을 입력하고 아래로 복사합니다.

E3: =D3/$D$18
F3: =E3
F4: =E4+F3
로딩 중...

3. CTRL 키를 누른 채 B2: B17, E2: E17, F2: F17 영역을 범위로 지정하고 [삽입] 탭 - [차트] 그룹 - [추천 차트] 명령을 선택합니다.

4. [차트 삽입] 대화상자에서 [추천 차트] 탭에서 [파레토] 차트를 선택하고 [확인] 버튼을 클릭합니다.

로딩 중...

5. 파레토 차트가 간단하게 만들어졌습니다.

로딩 중...

6. ABC 분석을 위해 차트 모양을 다듬어 줍니다. 아래 과정은 반드시 해야 하는 것은 아니며, 차트를 좀 더 시각적으로 보여주기 위한 것입니다. 누적 기준 상위 70%를 벗어나는 K를 클릭하고, 잠시 후에 다시 한 번 K를 클릭하면 K 막대(요소)만 선택됩니다.

로딩 중...

7. 이 상태(K 요소만 선택된 상태)에서 K를 마우스 오른쪽 버튼으로 클릭하고 [데이터 요소 서식] 메뉴를 선택합니다.

8. [데이터 요소 서식] 작업창에서 [채우기] - [색]을 '연한 파랑'으로 지정합니다.

로딩 중...

9. 6~8번 과정을 반복하여 B등급과 C등급에 해당하는 품목의 색상을 변경합니다. 차트의 나머지 서식은 [완성 예]를 참고하여 마무리합니다.