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

 

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

 

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

 

一、柏拉图的用途3>

柏拉图又名排列图。

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

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

 

二、用透视图做柏拉图

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

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

 

步骤:插入—表格。

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

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

 

2、设置数据透视表

(1)插入数据透视图

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

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

 

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

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

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

 

(3)设置透视表:行标签修改,累计比例转换成百分比

步骤

修改行标签:超领数量→→超领数量,超领数量2→→累计比例

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

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

 

3、绘制柏拉图

(1)绘制柏拉图

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

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

 

(2)设置柏拉图的格式

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

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

 

(3)源数据表更新时,刷新柏拉图的数据,与最新数据一致

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

步骤:分析→→刷新

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

 

 

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

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

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

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

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

没有车,自己造车。

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

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

 

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

1、从源数据创建好图表

步骤:插入→→组合图→→进行格式设置(线型,颜色,标题等)

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

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

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

 

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

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

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

 

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

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

    C)-1)

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

 

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

设置的参数:

  • 名称: “计划数量”
  • 范围: “计划”
  • 引用位置: =OFFSET(计划!d4,0,0,COUNTA(计划!d:d)-1)
  • 名称: “完成数量”
  • 范围: “计划”
  • 引用位置: =OFFSET(计划!e4,0,0,COUNTA(计划!e:e)-1)
  • 名称: “达成率”
  • 范围: “计划”
  • 引用位置: =OFFSET(计划!f4,0,0,COUNTA(计划!f:f)-1)
Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动
Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动

 

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

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

 

步骤:点中图表,右键→→选择数据→→系列/类别(如,计划数量)→→修改→→系列值→→修改为“动态区域”→→确定。
Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动
Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动

 

修改系列值为动态区域

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

 

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

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

 

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

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

 

5、总结一下实现的步骤

一、用源数据,画好图表
二、将会引用的各数据列,用Offset+CountA函数设置为动态区域
三、重新设置图表中各线条引用的数据区域,改成步骤二中的动态区域

 

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

希望能给大家提高效率!

 

 

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

 

 

Add a Comment

Your email address will not be published.

Time limit is exhausted. Please reload CAPTCHA.