파레토 법칙이나 20/80 원리에 대해 들어본 적이 있을 것입니다. 19세기 말 이탈리아의 사회학자이자 경제학자인 Vilfredo Pareto는 사회에서 부의 분배가 고르지 않고 일정한 의존성을 가지고 있음을 발견했습니다. 부의 증가와 함께 부자의 수는 일정한 계수로 기하급수적으로 감소합니다( 이탈리아 가구 중 소득의 80%가 20%의 가족에게 있음). 나중에 이 아이디어는 보편적인 "원칙 20/80"의 공식화를 제안한 Richard Koch의 책에서 발전되었습니다(20%의 노력이 결과의 80%를 제공합니다). 실제로 이 법칙은 일반적으로 그렇게 아름다운 숫자로 표현되지는 않지만(Chris Anderson의 "Long Tail" 참조), 자원, 이익, 비용 등의 불균등한 분배를 분명히 보여줍니다.
비즈니스 분석에서 파레토 차트는 종종 이러한 불균일성을 나타내기 위해 작성됩니다. 예를 들어 어떤 제품이나 고객이 가장 많은 이익을 얻는지 시각적으로 표시하는 데 사용할 수 있습니다. 일반적으로 다음과 같습니다.
주요 기능 :
- 히스토그램의 각 파란색 열은 제품의 수익을 절대 단위로 나타내며 왼쪽 축을 따라 표시됩니다.
- 주황색 그래프는 누적 이익 비율(즉, 누적 기준 이익 비율)을 나타냅니다.
- 80%의 조건부 경계에서는 일반적으로 명확성을 위해 임계값 수평선이 그려집니다. 이 선과 누적 이익 그래프가 교차하는 지점의 왼쪽에 있는 모든 상품은 돈의 80%를 가져오고 모든 상품은 오른쪽에 있으며 나머지 20%를 가져옵니다.
Microsoft Excel에서 직접 파레토 차트를 작성하는 방법을 살펴보겠습니다.
옵션 1. 기성 데이터를 기반으로 한 간단한 Pareto 차트
소스 데이터가 유사한 테이블 형태로 제공된 경우(즉, 이미 완성된 형태):
... 그런 다음 다음을 수행합니다.
수익 내림차순으로 테이블 정렬(탭 데이터 – 정렬) 누적 이익 비율을 계산하는 공식이 있는 열을 추가합니다.
이 공식은 목록의 시작 부분부터 현재 항목까지의 총 누적 이익을 전체 테이블의 총 이익으로 나눕니다. 또한 미래 차트에서 수평 임계값 파선을 생성하기 위해 상수가 80%인 열을 추가합니다.
모든 데이터를 선택하고 탭에 일반 히스토그램을 작성합니다. 삽입 – 히스토그램(삽입 – 세로 막대형 차트). 다음과 같이 표시되어야 합니다.
결과 차트의 백분율 시리즈는 보조(오른쪽) 축을 따라 보내야 합니다. 이렇게 하려면 마우스로 행을 선택해야 하지만 큰 수익 열의 배경에 대해 보기가 어렵기 때문에 어려울 수 있습니다. 따라서 탭의 드롭다운 목록을 사용하여 강조 표시하는 것이 좋습니다. 레이아웃 or 형성:
그런 다음 선택한 행을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하십시오. 형식 데이터 시리즈 표시되는 창에서 옵션을 선택하십시오. 보조 축에서(보조 축). 결과적으로 다이어그램은 다음과 같습니다.
누적 이익 공유 및 임계값 시리즈의 경우 차트 유형을 열에서 선으로 변경해야 합니다. 이렇게 하려면 각 행을 클릭하고 명령을 선택하십시오. 시리즈 차트 유형 변경.
남은 것은 임계값 수평 행을 선택하고 데이터가 아닌 절단선처럼 보이도록 형식을 지정하는 것입니다(즉, 마커를 제거하고 선을 빨간색 파선으로 만드는 등). 이 모든 것은 행을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택하여 수행할 수 있습니다. 형식 데이터 시리즈. 이제 다이어그램은 최종 형식을 취합니다.
그것에 따르면 우리는 이익의 80%가 처음 5개 상품에 의해 발생하고 감자 오른쪽에 있는 다른 모든 상품이 이익의 20%만 차지한다고 결론을 내릴 수 있습니다.
Excel 2013에서는 이 작업을 훨씬 더 쉽게 수행할 수 있습니다. 플로팅할 때 즉시 새로운 기본 제공 콤보 차트 유형을 사용하십시오.
옵션 2: 피벗 테이블 및 피벗 파레토 차트
건설을 위한 기성 데이터가 없고 원본 원시 정보만 있는 경우 어떻게 해야 합니까? 처음에 다음과 같은 판매 데이터가 있는 테이블이 있다고 가정해 보겠습니다.
파레토 차트를 작성하고 가장 잘 팔리는 제품을 찾으려면 먼저 소스 데이터를 분석해야 합니다. 이를 수행하는 가장 쉬운 방법은 피벗 테이블을 사용하는 것입니다. 소스 테이블에서 아무 셀이나 선택하고 명령을 사용하십시오. 삽입 – 피벗 테이블(삽입 – 피벗 테이블). 나타나는 중간 창에서 아무 것도 변경하지 않고 다음을 클릭합니다. OK, 그런 다음 오른쪽에 표시되는 패널에서 소스 데이터 필드를 미래 피벗 테이블 레이아웃의 상단 영역에서 하단 영역으로 드래그합니다.
결과는 각 제품의 총 수익이 포함된 요약 테이블이어야 합니다.
활성 셀을 열로 설정하여 수익의 내림차순으로 정렬 수익 필드의 금액 정렬 버튼을 사용하여 От Я до А (Z에서 A로) 탭 Data.
이제 누적 이자 수입이 있는 계산된 열을 추가해야 합니다. 이렇게 하려면 필드를 다시 드래그하십시오. 수익 지역으로 마케팅은: 오른쪽 창에서 피벗의 중복 열을 가져옵니다. 그런 다음 복제된 열을 마우스 오른쪽 버튼으로 클릭하고 명령을 선택합니다. 추가 계산 – 필드의 누계 %(데이터 표시 형식 – 누계 %). 나타나는 창에서 필드를 선택하십시오. 성함, 수익의 백분율이 위에서 아래로 누적됩니다. 출력은 다음 표와 같아야 합니다.
보시다시피, 이것은 기사의 첫 번째 부분에서 거의 기성품 테이블입니다. 미래 다이어그램에서 절단선을 구성하기 위한 임계값이 80%인 열만 완전한 행복을 위해 부족합니다. 이러한 열은 계산된 필드를 사용하여 쉽게 추가할 수 있습니다. 요약에서 숫자를 강조 표시한 다음 탭을 클릭합니다. 홈 – 삽입 – 계산된 필드(홈 – 삽입 – 계산된 필드). 열리는 창에서 필드 이름과 해당 수식(이 경우 상수)을 입력합니다.
클릭 후 OK 세 번째 열은 모든 셀에서 80% 값으로 테이블에 추가되고 마침내 필요한 형식을 취합니다. 그런 다음 명령을 사용할 수 있습니다. 피벗 차트 (피벗 차트) 탭 파라미터 (옵션) or Analysis (분석) 첫 번째 옵션과 정확히 같은 방식으로 차트를 설정합니다.
주요 제품 강조
가장 영향을 미치는 요인을 강조하기 위해, 즉 80%의 수평 절단선과 주황색 누적 이자 곡선의 교차점 왼쪽에 위치한 열을 강조 표시할 수 있습니다. 이렇게 하려면 공식을 사용하여 테이블에 다른 열을 추가해야 합니다.
이 공식은 곱이 교차점의 왼쪽에 있으면 1을 출력하고 오른쪽에 있으면 0을 출력합니다. 그런 다음 다음을 수행해야 합니다.
- 차트에 새 열을 추가합니다. 이를 수행하는 가장 쉬운 방법은 간단한 복사, 즉 열 강조 표시입니다. 백라이트, 복사(Ctrl + C), 다이어그램을 선택하고 삽입(Ctrl + V).
- 추가된 행을 선택하고 위에서 설명한 대로 보조 축을 따라 전환합니다.
- 시리즈 차트 유형 백라이트 열(히스토그램)으로 변경합니다.
- 행 속성에서 측면 여유 공간을 제거합니다(행을 마우스 오른쪽 버튼으로 클릭 조명 – 행 형식 – 측면 간격) 열이 하나의 전체로 병합되도록 합니다.
- 열의 테두리를 제거하고 채우기를 반투명하게 만듭니다.
결과적으로 우리는 최고의 제품에 대한 멋진 하이라이트를 얻습니다.
PS
Excel 2016부터 파레토 차트가 표준 Excel 차트 세트에 추가되었습니다. 이제 빌드하려면 범위를 선택하고 탭에서 끼워 넣다 (끼워 넣다) 적절한 유형을 선택하십시오.
클릭 한 번이면 다이어그램이 준비됩니다.
- 피벗 테이블을 사용하여 보고서를 작성하는 방법
- 피벗 테이블에서 계산 설정
- Excel 2013 차트의 새로운 기능
- 파레토의 법칙에 대한 Wikipedia 기사