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/)