做折线图位置引用无效_用Excel做排列图

排列图，又叫帕累托图、主次图，是按照发生频率大小顺序绘制的直方图，是QC中不可获取的工具。这里详细介绍一下用Excel做规范的排列图的方法。案例中用的是Excel2016.

比较规范的排列图应该是这样子，也是我们的目标。注意几个点：折线图从0开始；折线图与柱状图的交点；坐标轴；

一、原始数据和准备工作

首先，对于一组想要做排列图的数据，要有类别和频数这两个字段，然后是准备工作，对频数一列降序排列，求频数的频率和累积频率。如果这一步你已经很精通了可以跳到第二步画图。

1、对频数一列降序排列后，对频数求和。

sum函数搞定

2、求频数对应的频率。

很简单，频数除以总和就可以，这里需要注意的是绝对引用”$”这个符号的运用。$B$7表示B和7都是不变的，往下拉是这样的：B2/B7，B3/B7，以此类推。$B7表示B不变，7会变，往下拉：B2/B7，B3/B8，以此。B$7表示B会变，7不变，往下拉和$B$7是一样的，但横着拉就不一样了，会成：B2/B7，C2/C7，等。这是绝对引用相对引用的一些小内容。

3、求累积频率

累积频率对类别A来说，就是C2的占比，对类别B来说，是B的占比加上前面A的占比，对类别C来说是C+B+A的占比，以此类推。那么我们只要对于D2=D2，D3=D2+D3，D4=D2+D3+D4，D5=D2+D3+D4+D5即可，但这数量级还算少，如果有100个数要做排列图呢，1000个呢，手动这样加就算是输公式显然也是不合适的，所以请看下方公式：sum($C$2:C2)，把这个公式往下拉就能看明白了，sum是求和我们都知道，这个公式表示的是聪C2到C2就和，往下拉就成了sum($C$2:C3)，表示从C2到C3求和，是不是很明显了，别忘了绝对引用。

二、画图

1、对类别和频数画柱状图

选中类别和频数中的数据，插入柱状图。

2、调整宽度

选中图表右键设置数据系列格式将间隙宽度调整成0

3、将折线图添加进来

首先在表格表头的下方插入一行，在插入新行的累积频率处填0(即D2处为0)，如下图。

右键柱状图，选择数据——添加，系列名称为系列2，系列值为D2：D7，

右键图表，更改图表类型，将系列2选成带标记的折线图，并勾选次坐标轴。

在图表的+处勾选坐标轴——次要坐标轴，把次要坐标轴勾上，让其显示出来。

双击次要坐标轴可设置次要坐标轴格式，在坐标轴位置处，将其改为在刻度线上。

同时在标签处，选择标签位置为无。这样次要坐标轴即设置好了，也给隐藏起来了。

三、微调

1、调整坐标，使其符合排列图标准

双击右坐标轴，使其最小值最大值在0-1之间，尤其是最大值一般设成1，也就是100%。

2、排列图折线图第二个点一般要与柱状图第一个柱子在一条水平线上。

双击左坐标轴，设置坐标轴选项，最小值为0，最大值一般选择频数的总数就可以实现排列图正规的要求了。

双击右坐标轴，在数字处将类别改成百分比。

添加数据标签，在设置数据标签格式里数字处将类别改成百分比

OK！完成，撒花！

@文章属原创，转载请联系作者，侵权必究

@作者：虾壳，在数据分析的道路上努力奔跑

@微信公众号：可乐的数据分析之路

往期文章精选：

我的2018年终总结分析

数据处理

数据分析方法论

入门数据分析

如何成为高效的学习高手-摘要