Office中国论坛/Access中国论坛

标题: 【Excel 2016】——动态旭日图 [打印本页]

作者: roych    时间: 2020-4-10 18:22
标题: 【Excel 2016】——动态旭日图
闲话少说,先上图表。我们可以根据不同的维度,查看不同部门的各维度占比。
[attach]63697[/attach]
这个是Excel 2016新增的旭日图。旭日图其实并不难,但需要先构造好数据源。
[attach]63698[/attach]
感兴趣的版友可以看2楼的讲解。由于涉及的offset,match等函数对不少人来说,可能比较难以理解,所以讲得有些啰嗦。
作者: roych    时间: 2020-4-10 18:22
现在开始讲解下这个图表的设计思路。旭日图有个特点,就是除了最右侧的数值列之外,其它列必须按层级分好。如下图所示:[attach]63700[/attach]
如果还有子公司,或者区域什么的,左侧还可以细分。例如:亚太区>大中华区>华南区>广东分公司>部门…当然层级太多,其实也不是什么好事,数据可读性相对差一些,这时候可以考虑矩形树图。如果层级更多的话,则牺牲图例的值,改为树状图,通过节点粗细进行呈现。——这些都是后话了。
回到我们的问题,如何改造数据:
[attach]63701[/attach]
我们先看右边这4个维度,每个大的维度(例如:职级)下面都有3个小的维度,相对位置是固定的。因此,我们定位好一个大维度后,即可根据大维度去定位小维度。具体操作如下:

1、为了动态展示,我们可以先把大维度部分复制出来,选择性粘贴,转置行为列,然后取消单元格合并,按下Ctrl+G,点击定位条件,选择“空值”,右击,删除,在弹出的菜单中选择“整行”,即得到维度参数。
[attach]63702[/attach]
2、任选一个单元格,例如,图表!J1,点击“数据”功能区,在“数据工具”窗格上点选“数据验证”,在弹出的对话框中,“验证条件”选择“序列”,“来源”里选择前面设置的参数。
[attach]63703[/attach]
3、接下来就是重头戏了,写公式完成数据的引用。


a、先制表头,第一个字段为部门,这个就固定下来好了。第二个是大维度,跟随下拉菜单。所以,直接引用图表!J1就好了。第三个占比字段直接输入即可。


b、接下来写第一个公式。引用部门。在这里,为了美观,在参数表上重新写了部门简称。我们知道,每个大维度下有3个小维度,因此,我们可以根据参数表的第一个定位,例如,然后每三行下移一个单元格,即可一一对应了。因此,将行数除以3,向下取整(相当于rounddown),大体可以实现这个功能。那么哪些行需要下移,哪些不需要下移呢?这时候可以通过mod(模函数)取余即可,因为每个数除以3的余数都是0到2,整除完一圈又重来。于是公式如下:
=IF(MOD(ROW(),3)=2,OFFSET(参数表!$A$1,INT(ROW()/3)+1,),"")

MOD(ROW(),3)=2………………………………………这部分表示,当行号除以3余2,例如2,5,8,11……我们知道,这个数列的差刚好是3。也就满足了3个小维度的需求。
OFFSET(参数表!$A$1,INT(ROW()/3)+1,)………而这部分则表示,将参数表A1,向下移动“行号除以3取整后的下一格”。

根据if函数:if(表达式,表达式为真的结果,表达式为假的结果)。
假设行号是2,那么条件为真,于是执行offset部分,把行号=2,int(2/3)=0.667代入,取整后=0,因此int(2/3)+1=1,也就是等于参数表!$A$1向下移动一个格子,因此是参数表!$A$2,恰好对应的是总裁办。再如,当行号为3时,3除以3刚好整除,因此MOD(ROW(),3)=0而不是等于2,条件为假,因此返回后半部分,即空字符串。同样地,当行号=4时,MOD(ROW(),3)=1,结果也为假。


c、下面讲解第二个公式:
=OFFSET(Sheet1!$A$1,2,MATCH($G$1,Sheet1!$2:$2,0)-1+MOD(ROW()-2,3))
offset部分就不多说了。和前面一样,offset(起点,下移的行数,右移的列数)。由于小维度一直都是3个,没出现空值,因此这里就省去了if判断。
我们可以先定位Sheet1!$A$1,下移2行,也就是小维度的那一行【Sheet1!$A$3】。接下来设置移动的列数。
我们知道,小维度有3个,因此,每移动三列就轮回1次,是不是很熟悉?对,上面的MOD函数就施施然地来了。这不是重点,重点是,我们如何确定它只在这三个位置移动,而不会偏移到其它单元格呢?于是,这里就要讲一下MATCH函数了。
MATCH($G$1,Sheet1!$2:$2,0)
这个函数表示的是,在sheet1的第二行匹配G1,看看它对应的数据第一次出现在哪一列。这里的列数将用数字表示,例如,A列对应1,B列对应2,如此类推。
当MATCH和MOD加起来的时候,那么它将固定在一定的范围内了。为了保证不偏移,这里的MATCH应该用绝对引用。由于起点是Sheet1!$A$1,因此在使用MATCH时还需要减去1。于是:
MATCH($G$1,Sheet1!$2:$2,0)-1+MOD(ROW()-2,3),当G1=职级,行号=2时,MATCH部分得到的是5-1,MOD部分得到的是0,结合OFFSET,也就是相当于Sheet1!$A$1下移2行,再右移4列,得到的是:Sheet1!$E$3的数据,也就是“主管级”。


d、讲解第三个公式:
=OFFSET(Sheet1!$A$1,3+INT((ROW()-2)/3),MATCH($G$1,Sheet1!$2:$2,0)+MOD(ROW()-2,3)-1)
这和上面那个公式差不多。match部分的列偏移是一样的,这里就略过了。唯一的不同是,这里的行偏移。我们知道,每个大维度下有3个小维度,因此,行偏移量就需要通过行标取整偏移,得到正确的行。和前面一样,我们先下移3行,得到第一行的值,是否继续下移,则根据行号减去2后除以3取整而定。

至此,罗里吧嗦地总算把公式讲解完毕。希望大家能够理解它,并根据实际情况掌握改造数据表的技能。

作者: xuwenning    时间: 2020-4-13 15:01
还没用过2016
作者: 方漠    时间: 2020-6-4 14:55
图表很不错, 辛苦码字了, 谢谢.
作者: 阿朗树    时间: 2021-1-26 16:09
谢谢!下载了观摩。哈哈!




欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/) Powered by Discuz! X3.3