[纯函数法]动态图表5-自动排序的帕累托图

流浪铁匠

这是前几天群里一个朋友的需求(我增加了一点限制)
数据源不排序的前提下自动生成帕累托图，支持数据源的添删与自动排序
注意除了a/b列名的称与对应数值外,本图表没有使用包含累计占比等任何辅助列
这是身为函数的死忠党做这个图表的目标所在，而不是讨论这种做法的实用性
在数次尝试后成功实现
(数据源支持增删且图表自动排序,累计占比自动变化)
(注意折线从0开始)


在此把制作这个图表时遭遇的一些问题和解决方案进行分享，希望对大家有所帮助

当然,2016版的图表自带帕累托图,但折线不是0值开始的
这里为了增加难度做的是从0值开始的结构

流浪铁匠

1,纯函数无辅助列方式的首要知识点必然是函数构造内存数组与定义名称的使用
定义名称功能本身对于论坛群友来说不算难点，一般都有所了解，但这里的几个定义名称里有几个较为烧脑，特别是下文介绍的折线图从0开始后原数据从大到小累加的占比，恐怕是本图里最难的公式

2,数据源(柱形图)的自动降序,作为图表需要解决2个问题
(1)图表数据自动降序
(2)图表数据个数与数据源吻合，不出现多余错误值或0值等等
第1个问题使用加权公式难度不大(前提是对数组公式有一定了解)
第2个问题使用了row+indirect动态构造指定个数1-N的内存数组
这2个问题对于数组公式比较了解的群友来说不算难点，但如果公式不熟悉会有些吃力

3,折线图数据的构造
帕累托图的难点恐怕就是这个数据
因为帕累托图的折线图是从0开始,之后的数据是原数据从大到小排列后累加的占比值
由于多了1个0值，所以尝试后使用mmult的内存数组累加公式解决动态构造问题
这是本图最难的公式，必须对mmult有一定了解

4,折线图的百分比值显示
纯函数型内存数组的问题之一在于，内存数组不能控制格式，折线图的百分比值全部显示为对应小数数值，影响图表数据展现
解决方案，折线图的数值套嵌text后作为次坐标横坐标轴的标签名，折线图体现标签名而不是数值来解决这个问题(好久没做图了忘了这里完全能直接用修改数据标签格式解决
这里的方法适合图表数据与需要显示的数值不一致的情况,可以使用这个方法解决)

5,二八线(按照朋友的需求是80%的位置的红线)
对于图表熟悉的群友来说这个问题不难，添加1个散点利用误差线解决
唯一的问题也是保证误差线的长度，最后也是利用定义名称的公式动态解决

6,柱形图的对应数据名称
这个的动态和4的问题类似
利用公式动态引用数据源的对应名称作为水平轴标签名即可

7,添加数据报错问题
一开始时该图表公式里判断数据个数全部使用a:a引用，在a列添加新增内容后（b列还未录入数据时）在添加数据时图表会报错
原因在于a/b列数据个数不一致造成各公式的内存数组个数不一致报错
解决方案很简单，使用int(counta(a:b)/2) 来使2行均录入数据下才更新内存数组


很多人只是来拿模版的,所以这个图的定义名称我隐藏了
肯学习的,如何显示定义名称只是加了一点点阻碍
不肯学习只想获得模版的,看不看得到公式区别不大


以上为做这个图的时候的一点心得,欢迎大家关于做法进行交流学习

377798005

很荣幸 ，那个朋友就是我，哇咔咔

qust12345

好厉害，先赞后学！

kook11

谢谢分享！

wb_1027

試試做個，學習一下！
因為要反映工序与累計問題率，
工序需要有序銜接，所以不做排序處理，
因為主要問題也可能出現在中間，
或最后。


图表练习-帕累托图（二八线）.zip (117.53 KB, 下载次数: 85)

2020-4-28 21:12 上传

点击文件名下载附件

AntaresGG

想请教一下楼主降序排练这个动作是在哪里实现的？我下载看了也没找到加权公式在哪里。谢谢！

啊笑笑啊

马一下，膜拜一下曾经的大神

YA——忘记了

这个系列的名称的公式在哪里设置，我看不见

02761752696

关键技术点 ：名称公式没有.....