Advanced Excel Charts 简明教程

Advanced Excel - Pareto Chart

帕累托图被广泛应用于决策的统计分析。它体现了帕累托原则，也被称为“80/20法则”。

Pareto chart is widely used in Statistical Analysis for decision-making. It represents the Pareto principle, also called the 80/20 Rule.

Pareto Principle (80/20 Rule)

帕累托原则也被称为“80/20法则”。这意味着80%的结果是由20%的原因造成的。例如，80%的缺陷可归因于关键的20%的原因。它也被称为“重要的少数和琐碎的多数”。

Pareto principle, also called the 80/20 Rule means that 80% of the results are due to 20% of the causes. For example, 80% of the defects can be attributed to the key 20% of the causes. It is also termed as vital few and trivial many.

维尔弗雷多・帕累托通过调查和观察发现，大多数国家80%的收入流向20%的人口。

Vilfredo Pareto conducted surveys and observed that 80% of income in most of the countries went to 20% of the population.

Examples of Pareto Principle (80/20 Rule)

帕累托原则或“80/20法则”可应用于多种情境 −

The Pareto principle or the 80/20 Rule can be applied to various scenarios −

  1. 80% of customer complaints arise from 20% of your supplies.

  2. 80% of schedule delays result from 20% of the key causes.

  3. 80% of a company profit can be attributed to 20% of its products.

  4. 80% of a company revenues are produced by 20% of the employees.

  5. 80% of the system problems are caused by 20% of causes of defects.

What is a Pareto Chart?

帕累托图是柱状图和折线图的组合。帕累托图按频率的降序排列显示“柱”，线则描绘类别的累积总数。

A Pareto chart is a combination of a Column chart and a Line chart. The Pareto chart shows the Columns in descending order of the Frequencies and the Line depicts the cumulative totals of Categories.

帕累托图如下所示 −

A Pareto chart will be as shown below −

pareto chart

Advantages of Pareto Charts

您可以使用帕累托图进行以下分析 −

You can use a Pareto chart for the following −

  1. To analyze data about the frequency of problems in a process.

  2. To identify the significant causes for problems in a process.

  3. To identify the significant areas of defects in a product.

  4. To understand the significant bottlenecks in a process pipeline.

  5. To identify the largest issues being faced by a team or an organization.

  6. To know the top few reasons for employee attrition.

  7. To identify the topmost products that result in high profit.

  8. To decide on the significant improvements that increase the value of a company.

Preparation of Data

考虑以下数据，其中给出了缺陷原因和相应计数。

Consider the following data, where the defect causes and the respective counts are given.

preparing pareto chart data

Step 1 −按缺陷计数列从大到小对表格进行排序（从最大到最小）。

Step 1 − Sort the table by the column - Defect Count in descending order (Largest to Smallest).

sort table

Step 2 −创建一个累积计数列，如下所示：

Step 2 − Create a column Cumulative Count as given below −

create column cumulative count

这将导致以下表格：

This would result in the following table −

column cumulative count result

Step 3 −对缺陷计数列求和。

Step 3 − Sum the column Defect Count.

Step 4 −创建一个累积百分比列，如下所示。

Step 4 − Create a column Cumulative % as given below.

create column cumulative

Step 5 −将累积百分比列格式化为百分比。

Step 5 − Format the column Cumulative % as Percentage.

format column cumulative

您将使用此表格创建帕累托图。

You will use this table to create a Pareto chart.

Creating a Pareto Chart

通过创建帕累托图，您可以得出缺陷的主要原因。在 Excel 中，您可以将帕累托图创建为柱形图和折线图的组合图。

By creating a Pareto chart, you can conclude what are the key causes for the defects. In Excel, you can create a Pareto chart as a combo chart of Column chart and Line chart.

以下是创建帕累托图的步骤：

Following are the steps to create Pareto chart −

Step 1 −选择表格中的缺陷原因和缺陷计数列。

Step 1 − Select the columns Defect Causes and Defect Count in the table.

Step 2 - 插入一个簇状柱形图。

Step 2 − Insert a Clustered Column chart.

clustered column chart

Step 3 −如您所见，表示原因的列以降序排列。按如下方式设置图表格式。

Step 3 − As you can see, the columns representing causes are in descending order. Format the chart as follows.

  1. Right click on the Columns and click on Format Data Series.

  2. Click SERIES OPTIONS in the Format Data Series pane.

  3. Change the Gap Width to 0 under SERIES OPTIONS.

  4. Right click on the Columns and select Outline.

  5. Select a dark color and a Weight to make the border conspicuous.

您的图表将如下图所示。

Your chart will be as shown below.

pareto chart shown

Step 4 −按如下方式设计图表。

Step 4 − Design the chart as follows.

  1. Click on the chart.

  2. Click the DESIGN tab on the Ribbon.

  3. Click Select Data in the Data group. The Select Data Source dialog box appears.

  4. Click the Add button.

design pareto chart

编辑系列对话框出现。

The Edit Series dialog box appears.

Step 5 -在单元格上单击 - 系列名称的累积 %。

Step 5 − Click on the cell – Cumulative % for Series name.

Step 6 -选择累积 % 列中的数据，以获得系列的值。单击确定。

Step 6 − Select the data in Cumulative % column for Series values. Click OK.

select data in cumulative

Step 7 -在选择数据源对话框中单击确定。您的图表将如下所示。

Step 7 − Click OK in the Select Data Source dialog box. Your chart will be as shown below.

click ok in select data

Step 8 -在功能区上单击设计选项卡。

Step 8 − Click the DESIGN tab on the Ribbon.

Step 9 -在类型组中单击更改图表类型。

Step 9 − Click Change Chart Type in the Type group.

click change chart type

Step 10 -更改图表类型对话框出现。

Step 10 − Change Chart Type dialog box appears.

  1. Click the All Charts tab.

  2. Click the Combo button.

  3. Select Clustered Column for Defect Count and Line for Cumulative %.

  4. Check the box – Secondary Axis for Line chart. Click OK.

change chart type dialog box

正如您所见，80% 的缺陷是由两个原因造成的。

As you can observe, 80% of the defects are due to two causes.

pareto chart