函数的高阶应用-(零辅助列)帕累托/瀑布图/正态分布式排列图模版分享

流浪铁匠

声明:本帖为原创作品,模版著作权归作者所有,首发excelhome论坛
仅在eh进行免费分享,仅为学习交流,请勿用于商业目的

首先我没发错版块,分享的是图表模版
但核心是使用函数构造图表数据需要的各种动态的内存数组实现,
论坛的教程很多，但这种做法的动态图表比较极端，资料较少，因此做了几个典型的介绍下
纯函数型的动态图表,
优点是完全利用excel本身功能实现（函数+图表），完全存在于excel生态内，故没有兼容性/版本/插件/启用宏等诸多因素的限制(模版2007起即可,我没用过03,全部是以07角度写的公式)
缺陷一样明显，需要有一定的函数和图表功力，函数至少要了解多维引用，真内存，mmult，加权等等才能按照需求写出需要的公式,图表也要知道如何使用散点构造指定的数据标签（纯函数型动态图表的某些效果因为数据源为内存数组，只能靠这个技巧才能在不使用辅助列前提下实现）

这里做了4个图(最后那个是凑数的)来介绍这类图表能实现的效果

流浪铁匠

1,帕累托图
这是个典型图表，网上的主流教程有3类:
(1)辅助列做法:数据源排序，添加辅助列计算累计占比后，主坐标设置柱形，次坐标设置折线方式实现
(2)使用2016的新图表-排列图实现，这个图的优点是数据源可以自动降序，不用处理数据源，但缺点是无法做成折线以0点开始的效果
(后面有个细节这个做法的也实现不了)
(3)利用某些插件或者代码一键生成

今天分享的模版就是第4种
直接在a/b列录入数据即可,不需要进行排序等等,累计占比会自动计算的
网上的帕累托图效果很多,这个模版的效果是其中最复杂的一种:
柱形图数据自动降序,且折线图从0点开始(折线图数据永远比柱形图多1个值),
且整个柱形图的最大值是以柱形图数据总和值来调控(样本总数与100%持平,使图形更直观),
图内红线就是80%位置的二八线,且这条线的长度也会因为数据个数变化自动调整
正是因为这些自动效果,这个图是很适合做成模版的
(之前帖子有做过这个图,但这次的优化了下,一是整个柱形图的最大值是以柱形图数据总和值来调控，二是数据不足以能体现图表效果时不会因为公式数据不足而报错，而是变成事先指定的提示内容，三是上次用散点模拟的数据标签显示的占比，当时这个细节想复杂了，这次直接用图表的相关设置实现)

流浪铁匠

2:瀑布图
这个图相对简单常见
但模版里里自动显现的"合计"项会让很多新手想破脑袋也想不出来是如何产生的
这就是函数做法的灵活性,在保证数据动态的前提下能灵活重构数据源,创建一些数据源不存在的内容
(本图是仿制2016的瀑布图效果的,但数据源不需要添加合计）
2个相邻柱形图之间的灰色连接线思路取自群友 依梦瑶 的散点误差线思路,在此表示感谢,其他全部细节为独立完成)

流浪铁匠

3:正态分布式排列图
首先这个称呼和数学里的正态分布无关,只是表明这个图表的效果是把数据从中间大两端小的排列方式排列,这个图本身很简单,难点在于数据源即便在排序下,要构造这种按中间大两端小的排列方式的序列也是个麻烦事,更何况数据源是乱序的
而这就是函数的强项了,
另外注意图表内名称会自带排名
当然这图也是支持数据添加和变化的




这图是本帖内唯一需要展示公式的
因为这图的核心是构造一个类似正态分布排列的序列数,中位数为1往两侧逐步递增
3个数为213
4个数为4213或3124
5个数为42135
6个数为642135或531246
依次类推
这个效果以前在群里和朋友聊过，这里列出当时整理的2个解法




我当时想到用text 0 1/2 思路将原数字+0.5,但没想到1/1也能被识别将原数字+1,李逸雪在这个基础上写出了上面的解法
我也得以在这个基础上使用了2/1将原数字+2的思路将公式简化
2种公式的主要差异是数据个数为偶数时的序列是否颠倒
原本在图表内我使用的是自己的公式，但做成图表后发现偶数个数时变成531246序列的话图表更加美观，故这里使用的是李逸雪的序列公式



4,相信很多人看到正态分布就因为是数学上的正态分布效果
所以利用百度的教程步骤和公式顺手把正态分布柱形+折线图也做成模版了，a列直接录数据即可，也是自动生成的

style24366

很好奇你的公式在哪里

流浪铁匠

style24366 发表于 2020-7-22 17:57
很好奇你的公式在哪里

以前的帖子里提过，很多人只是来要模版的，并不打算学习相关公式
所以加了个槛，公式被隐藏了
想学习的，自己就会找方法来查看我的公式
不想学习的，看不看得到公式并没有区别

AbleZeng

铁大威武，献上老男人膝盖

miniku

膜拜 * 9e307

啊笑笑啊

不愧是大师兄。献上我的膝盖

style24366

流浪铁匠 发表于 2020-7-22 18:39
以前的帖子里提过，很多人只是来要模版的，并不打算学习相关公式
所以加了个槛，公式被隐藏了
想学习的 ...

哥们你真误会了，不好意思这里公式有什么难度么？图表类型我就是看了看看，非常基础图表；只是对于公式隐藏功能有点好奇，仅此而已；现在看数据源就明白了，谢谢你