动态取值_进阶动态图表(1)

图表可以直观化地展示数据表达的结果，但简洁地展示繁多的数据有时就需要一些手段了。其中选择性地动态展示就是其中一个方法，今天我将向大家揭秘动态图表实现的背后逻辑。

现在假使你是公司的总裁助理，各销售分区汇总了销售成绩单给你(下图)。老板想要比较每个月各销售区利润及利润率的情况。

你会选择这样呈现吗？

闹呢！肯定不行。这么搞，指定下课！

那这样展示汇报呢？

老板肯定爱不释手

上面这个图是怎样做出来的呢？

就上述实例而言，需要选择性展示的根本原因是“数据的结果展示有多个维度，且不同维度间相互影响”。那实现动态展现的思路就是“控制变量法”。

道理阐述很清晰，那落实到实操上该如何实现呢。

这个时候就需要介绍一个新朋友给大家认识了----OFFSET 函数。OFFSET函数可以实现参照某一单元格根据一定位移要求进行取值或者选取区域。

公式结构：

reference-参照单元格，根据地；

rows-移动行数，正数向下移动、负数向上移动；

cols-移动列数，正数向有移动，负数向左移动；

height-取值高度，即行数；

width-取值宽度，即列数；

例：=offset($F$1,5,-2,1,1)，代表以F1单元格为基础，向下移动5行，向左移动2列，向下取1行，1列。

另外，制作动态图表还需要使用到控件作为控制按钮。因此需要先将开发工具调出来，具体方法如下：

我们今天要用的是表单控件第二行第三个，选择确认后，调出“设置控件格式”进行调试。

现在准备工作就算做完了。

下面我们就来思考思考老板的问题，“比较每个月各销售区域利润及利润率的情况”。此问题中存在“每个月”、“各销售区域”两个变量，但显然“不同月份”是定语，老板最终要看的是“不同区域的利润情况”。基于上述分析，我们先可以简化老板的要求，先看一个月的情况。

根据公式：

利润=销售额-成本

利润率=销售额/成本-1

现在我们可以完成一个月的情况了。那不同月份怎样实现自动更新呢。这个时候就需要我们拿起刚刚准备的“控件”和“新朋友”了。

1. 自动更新

   控件能实现对应单元格数值变化是能够实现不同月份情况展示的核心。

2.根据控件结果实现对应数据填充

利润落实数据公式为：

=OFFSET($A$1,MATCH($G4,$B:$B,0)-2+$F$6,2,1,1)-OFFSET($A$1,MATCH($G4,$B:$B,0)-2+$F$6,3,1,1)

利润率落实数据公式为：

=OFFSET($A$1,MATCH($G4,$B:$B,0)-2+$F$6,2,1,1)/OFFSET($A$1,MATCH($G4,$B:$B,0)-2+$F$6,3,1,1)-1

表头的显示方式为：

="2020年"&$F$6&"月数据情况"(注："" 英文输入模式下半角引号。)

这样我们就得到这样的一个可自动更新的表格

在这样的基础上，制作复合图表。

将得到的图表稍作美化，就能得到文章开头展示的成品了。

现在我们对动态图表的实现逻辑有了初步的认识，后续我们将继续深化，敬请关注!

最后感谢您的阅读！

本文学习资料

链接：https://pan.baidu.com/s/1ztHaJN6z2_Q07urwUQiqJw

提取码：qqww