ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 5303|回复: 10

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

[复制链接]

TA的精华主题

TA的得分主题

发表于 2020-1-7 15:14 | 显示全部楼层 |阅读模式
本帖最后由 流浪铁匠 于 2020-1-7 15:20 编辑

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

在数次尝试后成功实现
aaa1.png aaa2.png (数据源支持增删且图表自动排序,累计占比自动变化)
(注意折线从0开始)


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

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

自动排序的帕累托图.rar

15.09 KB, 下载次数: 497

评分

10

查看全部评分

TA的精华主题

TA的得分主题

 楼主| 发表于 2020-1-7 15:18 | 显示全部楼层
本帖最后由 流浪铁匠 于 2020-1-7 15:43 编辑

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行均录入数据下才更新内存数组


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



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

评分

1

查看全部评分

TA的精华主题

TA的得分主题

发表于 2020-1-8 10:30 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2020-4-27 09:18 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2020-4-27 10:10 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
谢谢分享!

TA的精华主题

TA的得分主题

发表于 2020-4-28 21:17 | 显示全部楼层
本帖最后由 wb_1027 于 2020-4-28 21:20 编辑

試試做個,學習一下!
因為要反映工序与累計問題率,
工序需要有序銜接,所以不做排序處理,
因為主要問題也可能出現在中間,
或最后。
]{F(WK47]XE2U4U9JMSVN84.png

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


TA的精华主题

TA的得分主题

发表于 2020-5-10 18:54 | 显示全部楼层
想请教一下楼主降序排练这个动作是在哪里实现的?我下载看了也没找到加权公式在哪里。谢谢!

TA的精华主题

TA的得分主题

发表于 2020-6-4 16:12 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
马一下,膜拜一下曾经的大神

TA的精华主题

TA的得分主题

发表于 2020-11-4 18:43 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2021-7-16 09:40 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
关键技术点 :名称公式没有.....
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-12-25 15:35 , Processed in 0.040215 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表