上篇我们解决了自动化统计周数据概况表。俗话说字不如表，表不如图。这次我们来讲解如何做到图表自动化。

先看成果图


解释下指针的数据标签没有变动是因为电脑excel比较老，直接填的。新版的excel中可以直接设置单元格的值为数据标签，就可以自动变化了。


周报组成：数据概况表、KPI完成进度条和趋势漏斗图。


KPI完成进度条

这个图包含3个数据系列，KPI目标值，KPI完成值，KPI完成进度指针。前两者簇状条形图，位于主坐标轴，指针是一个带直线和数据标记的散点图，位于次坐标轴。


1、数据准备

在KPI统计截止日期中输入日期，新增用户完成数使用sumif函数统计截止3月22日的KPI完成值。指针是用来表示进度的，因此也直接等于KPI完成值即可。

2、绘制簇形条形图

选择三组数据，插入簇形条形图。为了便于区分数据系列，我们先把数据标签加上。

3、得到进度条雏形

设置数据系列重叠100%后，更改图表类型，得到进度条雏形。但是看不出来指针数据系列了。

4、指针数据系列调出来

更改图表类型为带直线和标记的散点图。

5、设置指针进度

指针已经出现了，但是位置不太对。这是因为散点图需要设置X值和Y值。最开始绘制条形图时候是只有一个值，从次坐标轴可以看出这个值现在是Y轴。

设置X轴的值为指针对应的值。同时设置Y轴的值。你可以试试如果Y轴保持不变，会是什么情况。

6、将指针修改为箭头

插入-形状-选择箭头。将箭头修改为想要的大小颜色，然后Ctrl+C复制。选择散点，Ctrl+V即可。


图形美化，修改颜色、线条及坐标轴显示，就可以得到漂亮的KPI进度条了。最新版的excel数据标签可以设置为指定单元格的值了，可以把指针的标签设置为KPI完成的百分比。

图表自动化

图表能够做到自动化的原理是能够动态调用源数据。比如你选择这个双周日期，图表能够自动调用相应周期的数据。这里学两个新函数-OFFSET函数和MATCH函数。

1、OFFSET函数

OFFSET函数以指定的引用为参照系，通过给定偏移量得到新的引用区域。

语法：

OFFSET(reference,rows,cols,height,width)

参数：

Reference：作为偏移量参照系的区域，必须为对单元格或相连单元格区域的引用

Rows：相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数为正数，代表在起始引用的下方；为负数，代表在起始引用的上方。

Cols：相对于偏移量参照系的左上角单元格，左（右）偏移的列数，列数可为正数，代表在起始引用的右边，为负数，代表在起始引用的左边。

Height：高度，即所要返回的引用区域的行数。

Width：宽度，即所要返回的引用区域的列数。

2、MATCH函数

Match函数返回指定数值在指定数组区域中的位置。

语法：

MATCH(lookup_value,lookup_array, [match_type])

参数：

lookup_value需要在lookup_array中查找的值。

lookup_array要搜索的单元格区域。

match_type可选参数，数字 -1、0或1。此参数的默认值为1。

Match_type：1或省略，表示MATCH 函数会查找小于或等于lookup_value的最大值。lookup_array 参数中的值必须按升序排列。

Match_type：0，表示MATCH函数会查找等于lookup_value的第一个值。lookup_array参数中的值可以按任何顺序排列。

Match_type：-1，表示MATCH函数会查找大于或等于lookup_value的最小值。lookup_array参数中的值必须按降序排列。

3、OFFSET函数和MATCH函数组合，动态获取图表区域

在辅助单元格输入match函数，获取日期在源数据中对应的行数。在输入日期栏输入3月9日后，辅助单元格的值变为10。注意此处我们是以A1为参照的。至此我们可以动态获取行了。


用OFFSET函数获取图表数据区

参数ROWS代表偏移的行数，它的取值要跟日期同步变动。动态获取行数直接引用Match函数的取值。参数Cols代表偏移的列数，这次没用到。原理是一样的，懂了前面的就可以融会贯通。

设置完【函数参数】对话框中各个参数后，按下【Ctrl+Shift+Enter】组合键，即可得到动态调用的数据区域。注意这里是一个数组函数，如有要修改其中部分值，需要先取消数组。取消数组的办法是选中数组，然后按F2，最后按Ctrl+enter。

4、选择数据系列插入合适的图表即可

这里不做多的演示。

接下来介绍一个做APP运营日常会用到的漏斗图。

1、首先是数据区，注意占位数据的设置

2、绘制漏斗图

选中数据区域——点击插入——推荐的图表——堆积条形图；

选中坐标轴——设置坐标轴格式——逆序类别；选择数据将占位数据调上来。

将占位数据的系列填充为空，将数据标签和横坐标轴删除。

数据系列之间插入剪头形状，文本填充直接调用转化率值。

将上述图表组合就可以得到文章开始的自动周报了。你学会了吗？


回顾上篇