一、柏拉图的用途3>

柏拉图又名排列图。

它的主要作用是，呈现主要问题的类型，将问题发生频率降序排序后，呈现各问题的累计比例。

遵循二八原则，把累计占比80%的问题类型，确定为主要问题，是重点关注解决的对象。

 

二、用透视图做柏拉图

1、原始表格设置为智能表格（或者叫超级表格，可以动态引用的区域）

原始的表格是一个《超领物料》的数据表，将它设置为智能表格。

 

步骤：插入—表格。

这个数据表，是透视图的引用区域，设置成智能表格后，引用区域能随着数据增减，动态调整。

 

2、设置数据透视表

(1)插入数据透视图

步骤：插入→→数据透视表

 

(2)建立一个数据透视表：列标签为“原因类别”，行标签为“超领数量”

步骤：将“原因类别”拖动到→→“行”，“超领数量”拖动到→→“值”

 

（3）设置透视表：行标签修改，累计比例转换成百分比

步骤：

修改行标签：超领数量→→超领数量，超领数量2→→累计比例

累计比例，改成百分百：点中累计比例任一单元格→右键→值显示方式→按某一字段汇总的百分比

 

3、绘制柏拉图

(1)绘制柏拉图

步骤：选中透视表的所有单元格→→插入→→组合图

 

(2)设置柏拉图的格式

步骤：图标工具→→添加元素，依次设置 图标标题，数据标签，数据表，图例等元素，最终结果如下。

 

（3）源数据表更新时，刷新柏拉图的数据，与最新数据一致

当原数据表，数据发生变化时，可以同步柏拉图的数据

步骤：分析→→刷新

 

 

如何让你的Excel图表随数据变更而自动更新？

在excel里，用各种图表可视化数据，是高频操作。实际运用中，有一个bug，就是，每次源数据发生变化，图表是不变的，需要手动调整数据区域，重复工作，挺麻烦的，不爱。

在office里，excel的 超级表格 功能，可以让图表随着源数据的变化而变化，个人感觉是yyds。

wps的excel，也有超级表格，但是不支持让图表随着源数据的变化，自动更新。

没有车，自己造车。

Offset+CountA函数，可以创建动态区域，实现图表和源数据同步的需求。

 

下面来看一下，实现的过程。

1、从源数据创建好图表

步骤：插入→→组合图→→进行格式设置（线型，颜色，标题等）

这样，就创建好了，以原始数据表为源数据的组合图，也设置成自己喜欢的格式了。

这个组合图的形状，不会随着数据表区域一起变化。

 

2、将数列，依次创建为动态区域

数据会变化的数据列：“物料名称”，”计划数量”，“完成数量”，“达成率”。用Offset+CountA函数依次设置为动态区域。

步骤：快捷键 Ctrl+F3→→“新建”→→进行设置→→确定。

 

对 “物料名称” 列进行命名设置，设置的参数如下表。

• 名称： “物料名称”
• 范围： “计划”（写sheet的名称，这个案例里是计划）
• 引用位置： =OFFSET(计划!4,0,0,COUNTA(计划!

  C)-1)

 

“计划数量” ，”完成数量“，”达成率“ 列进行设置，完成后的效果如下图。

设置的参数：

• 名称： “计划数量”
• 范围： “计划”
• 引用位置： =OFFSET(计划!d4,0,0,COUNTA(计划!d:d)-1)
• 名称： “完成数量”
• 范围： “计划”
• 引用位置： =OFFSET(计划!e4,0,0,COUNTA(计划!e:e)-1)
• 名称： “达成率”
• 范围： “计划”
• 引用位置： =OFFSET(计划!f4,0,0,COUNTA(计划!f:f)-1)

 

3、将图表的引用区域，依次修改为动态区域

图表中的数据线 “物料名称”，”计划数量”，“完成数量”，“达成率”，引用的区域，修改为步骤2中设定好的动态区域。

 

步骤：点中图表，右键→→选择数据→→系列/类别（如，计划数量）→→修改→→系列值→→修改为“动态区域”→→确定。

 

修改系列值为动态区域

 

依次将图片中的 “物料名称”，”计划数量”，“完成数量”，“达成率”，都修改好。

 

4、完成，图片能随着源数据的变化，动态更新啦

 

5、总结一下实现的步骤

一、用源数据，画好图表

二、将会引用的各数据列，用Offset+CountA函数设置为动态区域

三、重新设置图表中各线条引用的数据区域，改成步骤二中的动态区域

 

四、功能实现了，可以图和数据，可以动态更新了

希望能给大家提高效率！

 

 

本文：Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动