在发布完《程序员眼中的编程语言》一文后,发现网上还有一个关于操作系统的。如下所示。 图片的横轴是三大操作系统。 纵轴是各操作系统的粉丝和信徒。 中间的各个小图片则是,粉丝眼中的操作系统的形象。 本文:粉丝眼中的操作系统
Excel技巧, 用Excel透视表做柏拉图, Excel数据转图标, Excel数据图表联动


一、柏拉图的用途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数据图表联动