建立自动化数据分析模型，这几个函数就够了（两个例子介绍如何制作自动化数据分析模型）

很多客户对培训的一个重点要求是：想学自动化数据分析。

自动化数据分析有很多工具，例如Excel函数，Power工具，Tabeau等等，作为最最基础工具的Excel函数，不仅仅是可以制作自动化数据分析模型，在日常数据处理中也是频繁使用的。因此，要想学自动化数据分析，首先要彻底掌握Excel的几个最基本函数应用。注意这里的“彻底掌握”，而不是仅仅会基本的应用，或者只会套用。

一、要掌握哪些函数？

数据处理也好，数据分析也好，都是离不开逻辑判断的，因此首先要弄明白并且能熟练嵌套使用的是IF函数（或者以及IFS函数），以及处理错误值的IFERROR函数，前者是根据条件进行判断处理，后者是对错误值如何处理。

如果要使用函数进行计数与求和计算，那么COUNTIF/COUNTIFS、SUMIF/SUMIFS函数就是必须的了，前者是条件计数，后者是条件求和。不过要注意的是，这几个函数（甚至根据条件计算平均值的函数AVERAGEIF/AVERAGEIFS，根据条件计算最大值函数MAXIFS以及根据条件计算最小值函数MINIFS）的基本原理是是什么？适合什么场合？有哪些实用的变形扩展应用？这些都是前面所说的“彻底掌握”的范畴，如果仅仅是会基本应用，还不能说你会使用这几个函数了。

动态数据分析的核心，其实是使用查找函数，也就是把需要分析的数据，使用相应的查找函数从原始报表中查找出来，然后制作动态分析报表和动态图表，制作一键刷新仪表板，这些查找函数并不多，最常用的是VLOOKUP/HLOOKUP、MATCH、INDEX、INDIRECT、OFFSET，如果你仅仅会用VLOOKUP，那是远远不够的，即使是你说会用VLOOKUP函数了，也只是其基本用法，要知道，这个VLOOKUP三叔不是你想象的那么只有一种用法。

当制作分析仪表板时，我们需要对日期或者数字格式进行转换处理，此时TEXT函数就是必须的了，以及需要做文本分行处理的CHAR函数，不知道你对这两个函数究竟了解到什么程度。

在排名分析中，LARGE和SMALL则是最基础的函数了，前者是降序排名（从大到小），后者是升序排名（从小到大）。

二、自动化数据分析模型示例1：基本动态分析

我们先看这样一个简单的表格：要求分析每个地区的的产品此销售情况，也就是可以任选某个地区，自动得到该地区下的每个产品销售大小，并自动从大到小排名，效果如下图所示。

这是一个典型的自动化排名分析模型，可以设计辅助区域，并设计公式来取数、排序等处理，最后再使用排名后的数据绘制柱形图。

这里我们使用组合框表单控件来控制选择地区，因此，设计这个自动化排名分析模型的基本步骤及相应公式如下：

1、组合框控件连接单元格是Q4，地区名称查找公式为：

=INDEX(B3:B9,Q4)

2、取数公式有很多，在本案例中，最简单的公式为：

=HLOOKUP(S5,$C$2:$G$9,$Q$4+1,0)

也可以使用VLOOKUP函数，不过要复杂些了：

=VLOOKUP($Q$5,$B$3:$G$9,MATCH(S5,$B$2:$G$2,0),0)

3、排名涉及到两个内容，一是对取出来的各个产品金额进行降序排序，二是提取匹配出每个金额对应的产品名称。

排名公式为：

=LARGE($T$5:$T$9,V5)

匹配产品名称公式为：

=INDEX($S$5:$S$9,MATCH(X5,$T$5:$T$9,0))

4、图表动态标题公式为：

=IF(Q5='合计','所有',Q5)&' 地区各个产品销售排名'

三、自动化数据分析模型示例2：滚动汇总及动态分析

在实际工作中，原始数据是一个一个工作表保存，例如每个月的费用表，每个月的工资表，并且随着时间推进，工作表个数不断增加，此时，如何建立一个自动化的滚动汇总及分析模型？

例如，下面是各个月的工资表，现在需要建立一个人工成本跟踪分析模型，能够分析指定部门各个月的人工成本变化。

我们首先要设计一个滚动汇总报表，解决各个月、各个部门的人工成本汇总问题，如下图所示，汇总计算公式为：

=IFERROR(SUMIF(INDIRECT(C$2&'!C:C'),$B3,INDIRECT(C$2&'!Q:Q')),'')

然后再根据这个汇总表，制作能够分析指定部门各月人工成本的动态图表，效果如下所示，具体设计方法，跟上一个案例是一样的，这里不再介绍动态图表的公式了，无非就是使用查找函数（例如最简单的是INDEX函数）设计公式。

四、小结

函数是基础，逻辑思路是核心，公式则是逻辑思路的结果。动态数据分析，实际上就是根据具体的问题，根据实际分析的需要，设计最简单、最高效的公式，来完成自动化计算和自动化数据查找。

动态之所以动态，你觉得是因为什么而动？