엑셀 파레토 차트(Pareto chart)

Excel의 파레토 분석

파레토 분석은 이탈리아 경제학자 빌프레도 파레토(Vilfredo Pareto)의 이름을 딴 파레토 법칙을 기반으로 합니다.

그리고 이 원칙은 많은 사건에서 효과의 약 80 %가 원인의 20 %에서 비롯된다는 것을 나타냅니다. 그렇기 때문에 파레토 법칙을 80/20 법칙이라고도 합니다.

 

다음은 파레토 법칙의 몇 가지 실용적인 예입니다.

• 경제에서는 세계 인구의 가장 부유한 20%가 세계 소득의 약 80%를 통제합니다.
• 의학에서는 환자의 20%가 의료 자원의 80%를 사용하는 것으로 보고됩니다.
• 소프트웨어에서 버그의 20%는 오류 및 충돌의 80%를 유발합니다.

집중해야 할 가장 중요한 요소를 식별하기 위해 Excel 워크시트에 파레토 차트를 그릴 수 있습니다.

 

Excel의 파레토 차트

파레토 다이어그램이라고도 하는 파레토 차트는 파레토 법칙을 기반으로 하는 그래프입니다. Microsoft Excel에서는 세로 막대와 수평선을 모두 포함하는 정렬 된 히스토그램입니다.

내림차순으로 표시된 막대는 값의 상대적 빈도를 나타내고 선은 누적 총 백분율을 나타냅니다.

 

일반적인 Excel 파레토 차트는 다음과 같습니다.

보시다시피 Pareto 그래프는 데이터 세트의 주요 요소를 강조 표시하고 합계에 대한 각 요소의 상대적 중요도를 보여줍니다. 아래에서 다양한 버전의 Excel에서 파레토 다이어그램을 만드는 방법에 대한 자세한 지침을 찾을 수 있습니다.

 

Excel 2016 - 365에서 파레토 차트를 만드는 방법

최신 버전의 Excel에서 파레토 다이어그램을 그리는 것은 기본 제공 파레토 차트 종류가 있기 때문에 쉽습니다. 한 열에는 항목(문제, 요인, 범주 등) 목록이 있고 다른 열에는 항목 수(빈도)만 있으면 됩니다.

예를 들어, 이 데이터 세트를 기반으로 소프트웨어에 대한 일반적인 사용자 불만에 대한 파레토 분석을 수행할 것입니다.

Excel에서 파레토 그래프를 만들려면 다음의 간단한 단계를 따르십시오.

1. 테이블을 선택합니다. 대부분의 경우 하나의 셀만 선택하면 충분하며 Excel은 전체 표를 자동으로 선택합니다.
2. 삽입 탭의 차트 그룹에서 추천 차트를 클릭합니다.
3. 모든 차트 탭으로 전환하고 왼쪽 창에서 히스토그램을 선택한 다음 파레토 썸네일을 클릭합니다.
4. 확인을 클릭합니다.
   

   

 

Pareto 차트가 워크시트에 즉시 삽입됩니다. 유일하게 개선할 수 있는 방법은 차트 제목을 추가/변경하는 것입니다.

 

 

Excel 파레토 그래프 사용자 지정

Excel에서 만든 파레토 차트는 완전히 사용자 지정할 수 있습니다. 색상과 스타일을 변경하고, 데이터 레이블을 표시하거나 숨기는 등의 작업을 수행할 수 있습니다.

 

원하는 대로 파레토 차트 디자인

파레토 차트의 아무 곳이나 클릭하면 리본 메뉴에 차트 도구가 나타납니다. 디자인 탭으로 전환하고 다양한 차트 스타일과 색을 실험해 보세요.

 

데이터 레이블 표시 또는 숨기기

기본적으로 Excel의 파레토 그래프는 데이터 레이블 없이 만들어집니다. 막대 값을 표시하려면 차트 오른쪽에 있는 차트 요소 단추를 클릭하고 데이터 레이블 확인란을 선택한 다음 레이블을 배치할 위치를 선택합니다.

 

동일한 값을 표시하는 기본 세로 축이 불필요 해져서 숨길 수 있습니다. 이를 위해 차트 요소 버튼을 다시 클릭한 다음 축 옆에 있는 작은 화살표를 클릭하고 기본 세로 축 상자를 선택 취소합니다.

결과 파레토 차트는 다음과 유사합니다.

 

Excel 2013에서 파레토 차트를 만드는 방법

Excel 2013에는 파레토 그래프에 대해 미리 정의된 옵션이 없으므로 필요한 것과 가장 가까운 콤보 차트 유형을 사용합니다. 최신 Excel이 백그라운드에서 수행하는 모든 조작을 수동으로 수행해야하기 때문에 몇 가지 단계가 더 필요합니다.

 

파레토 분석을 위한 데이터 구성

아래 설명에 따라 데이터 세트를 설정합니다.

1. 누적 총 백분율 계산

데이터 집합에 열을 하나 더 추가하고 여기에 누적 총 백분율 수식을 입력합니다.

=SUM($B$2:B2)/SUM($B$2:$B$11)

여기서 B2는 첫 번째 셀이고 B11은 Count 열에 데이터가 있는 마지막 셀입니다.

배당수에는 현재 셀과 그 위의 모든 셀에 있는 숫자를 더하는 누적 합계 공식을 입력합니다. 그런 다음 부분을 합계로 나누어 백분율을 얻습니다.

첫 번째 셀에 위의 수식을 입력한 다음 열 아래로 복사합니다. 결과를 백분율로 표시하려면 열의 백분율 형식을 설정합니다. 백분율을 정수로 표시하려면 소수 자릿수를 0으로 줄이십시오

 

2. 카운트를 기준으로 내림차순으로 정렬

파레토 차트의 막대는 내림차순으로 그려져야 하므로 개수 열의 값을 위쪽에서 아래쪽으로 정렬합니다. 이를 위해 셀을 선택하고 정렬 및 필터 그룹의 데이터 탭에서 A-Z를 클릭합니다. Excel에서 선택 영역을 확장하라는 메시지가 표시되면 정렬하는 동안 행을 함께 유지합니다.

또는 나중에 데이터를 더 빠르게 다시 정렬할 수 있도록 자동 필터를 추가합니다.

이 시점에서 원본 데이터는 다음과 유사해야 합니다.

 

파레토 차트 그리기

원본 데이터가 제대로 구성되면 1-2-3처럼 쉽게 파레토 그래프를 만들 수 있습니다. 말 그대로 3단계만 거치면 됩니다.

1. 표 또는 표 안의 셀을 선택합니다.
2. 삽입 탭의 차트 그룹에서 추천 차트를 클릭합니다.
3. 모든 차트 탭으로 전환하고 왼쪽에서 콤보를 선택한 후 다음과 같이 수정합니다.

• Count 계열의 경우 Clustered Column(기본 유형)을 선택합니다.
• 누적 % 계열의 경우 선 유형을 선택하고 보조 축 상자를 선택합니다.

 

Excel에서 워크시트에 삽입하는 차트는 다음과 같습니다.

 

파레토 차트 개선

차트는 이미 파레토 다이어그램과 매우 유사해 보이지만 다음과 같은 몇 가지 사항을 개선할 수 있습니다.

 

1. 최대 백분율 값을 100%로 설정합니다.

기본적으로 Excel은 보조 세로 축의 최대값을 120%로 설정했지만 원하면 100%입니다.

이를 변경하려면 오른쪽의 Y축에 있는 백분율 값을 마우스 오른쪽 버튼으로 클릭하고 축 서식을 선택합니다. 축 서식 창의 경계 아래에 있는 최대값 상자에 1.0을 설정합니다.

 

2. 막대 사이의 추가 간격 제거

고전적인 파레토 그래프에서는 막대가 콤보 차트보다 서로 더 가깝게 그려집니다. 이 문제를 해결하려면 막대를 마우스 오른쪽 버튼으로 클릭하고 데이터 계열 형식... 형식 데이터 계열 창에서 원하는 간격 너비(예: 5%)를 설정합니다.

 

마지막으로 차트 제목을 변경하고 필요에 따라 차트 범례를 숨깁니다.

현재 가지고 있는 것은 완벽한 Excel 파레토 차트처럼 보입니다.

 

Excel 2010에서 파레토 차트를 그리는 방법

Excel 2010에는 파레토 차트와 콤보 차트 종류가 없지만 그릴수 있습니다. 

 

1. 앞에서 설명한 대로 데이터를 구성하고, 개수를 내림차순으로 정렬하고 누적 총 백분율을 계산합니다.
2. 테이블을 선택하고 삽입 탭 > 차트 그룹으로 이동한 다음 2차원 클러스터형 세로 막대형 차트 유형을 선택합니다.
   

   

3.  
4. 이렇게 하면 2개의 데이터 시리즈(개수 및 누적 백분율)가 있는 세로 막 대형 차트가 삽입됩니다.
5. 누적 % 막대를 마우스 오른쪽 단추로 클릭하고 차트 계열 유형 변경을 클릭합니다. (막대가 매우 작기 때문에 가장 까다로운 부분일 수 있습니다. 계열 "누적 %" 힌트가 표시될 때까지 막대 위로 마우스를 가져간 다음 마우스 오른쪽 단추를 클릭합니다.
   

   

6.  
7. 차트 종류 변경 대화 상자에서 선을 선택합니다
   

   

8.  
9.  
10. 이 시점에서 가로 축을 따라 평평한 선이 있는 막대 차트가 있습니다. 곡선을 만들려면 오른쪽에 보조 수직축을 놓아야 합니다. 이를 위해 누적 % 라인을 마우스 오른쪽 버튼으로 클릭 한 다음 Format Data Series...
    

    

11.  
12. 데이터 계열 서식 지정 대화 상자의 계열 옵션에서 보조 축을 선택하고 대화 상자를 닫습니다.
    

    

13.  
14. 마무리 작업: 보조 세로 축의 최대값을 100%로 설정하고 막대를 더 넓게 만들고 선택적으로 범례를 숨깁니다. 단계는 기본적으로 위에서 설명한 Excel 2013과 동일합니다.

 

Excel 2010의 파레토 차트가 준비되었습니다.