Shulou(Shulou.com)11/24 Report--

Original title: "what on earth is Pareto in Excel?" After learning, efficiency can actually dump colleagues in the street! "

Voiceover from Qiu Xiao E: the chart introduced today is a little difficult. Let me guess which students can make it to the end.

Hi, everyone. I'm your old friend Xiao Shuang.

Recently I came across such a question: how to draw a Pareto map?

Pareto map is actually a quality management tool, which is mainly used to find the main factors that affect product quality.

An economist once proposed a 28 principle (also known as Pareto rule).

If we apply the 2008 principle to quality management, it means that 80% of the problems are caused by 20% of the key factors.

Simply put, Pareto can help us find those 20% of the problems.

Pareto chart ranks all influencing factors from high to low (you can use histogram or histogram) to indicate the frequency of each factor.

And use the cumulative percentage (line chart) to find out what are the main factors causing 80%.

Let's first look at the effect of the Pareto map:

Looking closely at the chart, we can see that:

There are 16 influencing factors in the chart, of which 80% of the problems account for 9 reasons, accounting for 63% of the total causes.

Under normal circumstances, we should keep it within 20%. 63% is obviously unreasonable in the manufacturing industry.

This shows that there are still many problems that have not been effectively solved, and the next step should be to give priority to solving the problems that can be controlled.

Having explained so much before, everyone should have a general idea of the role of Pareto at this point.

The Pareto rule (the 28 principle) is widely used:

In economics, for example, it is widely believed that 80% of the world's wealth is in the hands of 20%.

For example, in the enterprise analysis scenario, it is considered that 20% of the products / customers generate 80% of the economic income, so it is very important to find these 20% of the products / customers in the enterprise.

For example, when we learn Excel functions, 80% of the function problems mainly involve 20% of the functions.

So in learning the Excel function, we give priority to learning the main functions of 20%.

So the question is, how do you do this picture in Excel?

The following is explained by a case of product analysis.

By looking at the chart, it is not difficult to find that this Pareto chart is actually a chart composed of a column chart and a line chart.

The column chart is sorted from large to small, while the line chart is the cumulative percentage, starting at 0% and ending at 100%.

We know that it is actually a combination chart by taking it apart. Next, let's take a look at what the specific operation is like.

The specific operations are as follows:

❶ ranks sales from large to small.

❷ creates secondary columns with cumulative percentages.

C2 cell input 0%

Enter the formula for the C3 cell and fill it down to the C13 cell.

The formula is as follows:

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

❸ inserts a column chart.

Select the area from A1 to B12 and hold down the [Alt+F1] shortcut key to quickly insert the bar chart.

Or select the area from A1 to B12 and click the insert tab-Select a column chart.

❹ inserts a cumulative line chart.

① adds data.

Right-click the chart-click "Select data"-click add

Select C1 cells for the series name, C2 to C13 cells for the series values, and A2 to A12 coordinate values for the horizontal axis.

② changes the line chart whose chart type is a point data marker.

The processing of ❺ chart.

① right click on the column chart-Select data series format-change the gap width to 15%.

Data modification of the secondary ordinate axis of the ② polygonal graph.

Right-click the secondary ordinate axis and select to set the axis format-the maximum value is changed to 1 and the minimum value is changed to 0

The maximum value of the unit was changed to 0.8 Mill-to 0.8 in order to add the major horizontal gridlines of the secondary axis later.

③ opens the secondary Abscissa.

④ right-click on the secondary Abscissa axis-click [format coordinates]-Axis location click [on the tick line], and set the label position to "none".

⑤ add data labels and check the major horizontal grid lines of the secondary axes in the grid lines.

At this point, the rudiment of the basic Pareto diagram is complete.

If you are in Office 2013 or above, you can directly select the area and click on the recommended chart. The first one is the type of chart we need. After that, simply do some beautification and finish it.

All right, to sum up briefly, this article introduces:

The usage scenario of ❶ Pareto diagram

The specific method of ❷ Pareto map.

At the end, do you think Pareto is very professional and a bit brain-burning? However, it is one of the sharp tools for us to analyze data!

Whether you are in sales, operations, or HR, as long as you encounter data at work, you are likely to encounter problems with data analysis:

How to count by annual, monthly, quarterly and other different cycles? How to count the distribution by age and score? How to calculate the growth trend of the data?

If you only know how to make tables by hand, and rely on one function formula after another, you will not be far away from staying up late to work overtime.

The real master knows that as long as you learn a table, you can change flexibly and get any statistical results you want.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang, Editor: Zhu Lan

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.