Excel大厂自动化报表实战(互联网金融-数据分析周报制作上)

这是Excel大厂自动化报表实战第二期--互联网金融-数据分析周报制作上
数据资源已经与这篇博客捆绑，有需要者可以下载通过网盘分享的文件：2.4自动化报表-8月成交数据.xlsx，2.4自动化报表-8月获客数据.csv等2个文件
链接: https://pan.baidu.com/s/16nzy1ImOdWzmyHgKiuhiVQ?pwd=0623 提取码: 0623

作者建议
强烈建议，在学习制作互联网金融周报制作前，先去看Excel大厂自动化报表实战第一期--高级函数与数据连接，许多知识将在制作过程中直接运用，看完第一期接下来制作才会理解更加深刻！

在接下来三期，将带大家制作只需下拉选择日期以及区域就可以筛选数据并且智能识别字段的自动化报表

前言：
在Excel大厂自动化报表实战第一期--高级函数与数据连接，我们已经将获客数据导入到成交数据里，打开8月成交数据，发现安全警告：已禁止自动更新链接启用内容
这是什么东西呢？
它提示说已经禁止自动更新链接，在Excel大厂自动化报表实战第一期--高级函数与数据连接这一期我们做成交数据的时候，我们是把获客表格的数据直接连接到了我们的成交数据上面，那么这是一个跨表格连接，像这种连接的话，如果我们不打开另外一个表格的话，那么是没有办法同步另外一个表格的数据更新的。同时一般来说会默认禁止这个更新链接的内容。所以它提示已经禁止自动更新了。如果我们要启用这个链接，就是说我们打开另外一个表格，另外一个表格有数据变动，这边要跟着更新的话，我们就要点击启用内容了。在我们未启用内容之前的话，那么之前的这个数据是不会自动更新的。当然了。我们接下来三期的制作，只需要把它叉掉即可。注意接下来引用数据的时候，我们都从纯数值表格进行引用(纯数值表格的创建详细见Excel大厂自动化报表实战第一期--高级函数与数据连接)，因为成交数据里面我们做了大量的vlookup函数，并且有跨表连接，虽然跨表连接不会启用，但是我们选中用xloopup函数制作的数据时，双击拖拽都要消耗巨量的计算资源。因此如果我们自动化周报再继续基于一个也就是xloopup函数去智能识别的这个数据然后匹配过来的这么一张的表格的话，那么计算资源的消耗将相当大，很多电脑将直接会卡死。所以我们就复制一张纯数值表格过来。

一、周报制作准备阶段
1.新建一个窗口，把我们新建的窗口放到右侧(窗口的创建详细见Excel大厂自动化报表实战第一期--高级函数与数据连接),后面写函数的时候，我们就可以直接在新建的右侧窗口查看，并且选择这个列，左边窗口切回自动化周报这个表。

二、周报制作构思阶段
1.在做任何周报前，第一步肯定是要构思，周报肯定要有一个标题，那么我们的标题首先就留在第一行，在A1单元格上写：互联网金融-数据分析周报，格式统一最后调。一份报表肯定要有它的数据的时间的，在A2单元格写上数据时间。一个数据时间肯定是有起始日期，然后再到我们一个结束日期。
核心要点1：
那么起始日期怎么设置呢？
当然我们可以直接手动填起始日期，但这样手动填肯定不高级，选日期肯定更高级。那么怎么去实现日期的筛选呢？
通过制作一个日期的辅助列去实现,我们在做第一版报表的时候，可以先把这些辅助列都放到一个报表里，然后之后再去把它单独放到别的工作表隐藏起来

核心要点1解决方法:
首先我们要它打开后能够选很多日期，首先要给Excel提供这些日期，在稍微往后面一点的列去做辅助列，在U1单元格写上辅助列-日期(前面会放很多很多字段，先留好区域)，U2单元格写上2020/8/1，U3单元格就是U2单元格+1(日期本身就是一个数字可以直接进行加减乘除),然后直接下拉拖拽到2020/8/31(列表里只有8月数据，所以拖拽到8月)

2.设置开始日期，点击B2单元格，点击数据选项卡-->数据验证,允许选择序列，来源我们选择我们制作的辅助列-日期，不包括列名。这里就出现了下拉按钮，这样就可以对数据进行选择了。(这样比你直接填日期方便多了).注意这里B2单元格格式改为短日期。

3.因为是要做周报，数据结束日期肯定就是我们选择的开始日期+6

4.做好日期之后，接下来我们去想一想有一个战区筛选的筛选器，每个大区对应自己的数据。A3单元格写上战区，同样我们这里用数据验证的下拉列表，允许选择序列，来源我们既可以做辅助列-战区，也可以手动输入。记住这里手动输入战区名称的时候，用英文逗号隔开。这样我们用来筛选的两个单元格就做好了，后面我们是要把这两个单元格作为我们的筛选条件的

5.战区做完之后，一般一个周报日报最重要的就是监控公司的利润情况，营收情况，还有就是目标的达成程度，所以这里肯定需要有一个月目标和一个周目标。A4单元格输入月目标，A5单元格输入周目标。后面会补充月目标和周目标，再加上这个进度条。
核心要点2：
如何让月目标跟随着我们现在筛选进行变动，也就是改变战区名称，选择总和是总和目标，选择东部战区是东部战区的目标,选择西部战区是西部战区的月目标，那么这要用一个什么函数进行判断和实现呢？
显然用IFS函数进行多条件判断，当然可以直接把月目标都写下去，也可以像日期列一样做个辅助列

核心要点2解决方法:
这里我们选择用辅助列方式,然后直接用IFS函数进行多条件判断

=IFS(B3="总和",W2,B3="东部战区",W3,B3="西部战区",W4,B3="北部战区",W5,B3="南部战区",W6)
周目标一般都是月目标按照时间进度进行一个修改，周目标在我们这里等于我们月目标直接除以4就好。记得将月目标和周目标改为千位分隔样式

6.做完战区的目标之后，接下来就是周报具体数据的列。首先是日期和星期。在A7和B7分别输入日期和星期，日期对应的第一天肯定就是我们数据起始日期选择的第一天B2，后面日期跟辅助列逻辑一样+1就好了即B2+1，依次向下拉拖拽总共7天。再加上总计。星期的话，直接引用日期这一列的日期=A8,日期这列格式改为长日期,星期这列设置其它数据格式，在日期里面改为周几。

7.按照业务逻辑梳理字段(详细见tableau专栏里tableau 实战工作场景专业仪表盘的搭建(整体思路)详解)
流量:注册人数、戳额人数、给额人数、戳额率、给额率
成交:成交额、成交人数、给额成交率、注册成交率
资本:资金成本、获客成本
风险:逾期金额
利润:应收利息、前台毛利

三、周报制作数据填充阶段
核心要点3
首先看注册人数，怎么让它根据我们这里的战区和对应日期这一天进行筛选对应的注册人数？也就是这里的一天A8，比如2020/8/1这一天和筛选的战区，比如东部战区，也就是东部战区2020/8/1这一天对于我们筛选的注册人数有多少
核心要点3解决方法:
用SUMIFS函数首先筛选战区和日期
=SUMIFS('8月成交数据-纯数值'!P:P,'8月成交数据-纯数值'!$C:$C,$B$3,'8月成交数据-纯数值'!$A:$A,$A8)

核心要点4
筛选完战区和日期后，那么总和对应的数据又怎么算呢，因为成交数据里面战区列只有东部战区、西部战区、北部战区、南部战区，没有总和战区
核心要点4解决方法:
这里就要用IF函数做一个嵌套了，就是如果选总和的话，我们是不需要筛选战区的，直接基于日期进行计算的
用SUMIFS函数不筛选战区直接算总和
=SUMIFS('8月成交数据-纯数值'!P:P,'8月成交数据-纯数值'!$A:$A,$A8)

最后我们做一个嵌套，当我们B3单元格即战区筛选器选择总和的时候，直接返回用sumifs函数不筛选战区直接算总和的值，当我们B3单元格即战区筛选器选择总和之外数据的时候，直接返回sumifs函数筛选对应的战区和日期的值。
IF条件判断嵌套
=IF($B$3="总和",SUMIFS('8月成交数据-纯数值'!P:P,'8月成交数据-纯数值'!$A:$A,$A8),SUMIFS('8月成交数据-纯数值'!P:P,'8月成交数据-纯数值'!$C:$C,$B$3,'8月成交数据-纯数值'!$A:$A,$A8))
注意：记得B3单元格战区筛选要上锁以及A8单元格列要上锁

核心要点5
但是这样改列名如注册人数改为戳额人数，它是不会随着你改列名对应列的数据进行这个变动的，它没有起到自动识别的这么一个功能，让我们SUMIFS求和的列，根据我们的列名进行判断
核心要点5解决方法:
XLOOKUP函数
=XLOOKUP(X1,'8月成交数据-纯数值'!$1:$1,'8月成交数据-纯数值'!$A:$U)

发现报错，因为区域其实是没有统一的，这里面的话，其实是不能选择第一行的，我们要选择的是对应的8月成交金额这里面表头精确所在的，就是XLOOKUP它的查找区域和它的返回值的这个区域，它的这个要么宽度，要么长度它得是一致的，也就是XLOOKUP函数这里边查找区域如果选了整个一行，它会按照整个一行去找，比如'8月成交数据-纯数值'!$1:$1，U列之后虽然都是空的，但它也会参与运算，但我们选择返回的区域里面，也就是'8月成交数据-纯数值'!$A:$U是没有空对应返回的区域，那么其实也就是如果U列后面，比如V列出现了一个新的值，那后面出现了一个新的列，那后面的值就不会返回，就会报错。因此XLOOKUP函数的计算逻辑返回区域如果是A到U列，那么一开始查找的区域，那么也得是A到U，这里要查找表头，所以是A1到U1

=XLOOKUP(X1,'8月成交数据-纯数值'!$A$1:$U$1,'8月成交数据-纯数值'!$A:$U)

发现溢出了，那么这里溢出是什么问题呢？因为我们返回的区域是一整列，但是我们这里的话，我们这一列注册人数已经被占用了，即X1注册人数占用了返回的列，我们先把注册人数移开，再把函数移上去。

其实它会把这一列整个的数全部展示出来，后面0它一直到EXCEL的最后一个单元格，因此这里你多占了一个单元格，即X1注册人数占用了返回的列，它就是溢出错误。但是这样做的话很浪费计算资源，我们发现一直到下面都是0

核心要点6
如果我们只返回注册人数的话，我们XLOOKUP函数返回区域完全不用选A到U，我们直接选A2到U867(8月成交金额数据有867行)，就是我们可以选具体的区域，不用把所有的这个列全部选上，那为什么现在要全部选上呢?
核心要点6解决方法:
因为如果我们要用SUMIFS函数对它进行判断和计算的话，那么我们放进去进行计算的列，如果是XLOOKUP函数返回给它的，那么它必须是一个完整的一列。因为SUMIFS函数本质上看我们的筛选条件对应的是哪些行，然后把行的数值返回来进行计算，因此说XLOOKUP函数返回区域给SUMIFS函数的行必须是完整的，也就是从头到尾都覆盖到，把XLOOKUP函数返回区域改成是区域的，比如A2到U867，不是完整的，SUMIFS函数是会报错的。
=XLOOKUP(C$7,'8月成交数据-纯数值'!$A$1:$U$1,'8月成交数据-纯数值'!$A:$U)这就是基于我们的字段去自动识别我们就完成了

1.复制我们的字段自动识别表达式替换SUMIFS函数里的求和区域(本来我们是制定了P列也就是注册人数这一列，现在我们不指定P列了，现在我们让SUMIFS函数根据C7的这个列名字段名称到另一张到数据报表去找到它到底是哪一列),然后向下向右拖拽，复制粘贴函数，修改对应的XLOOKUP函数查找的值所对应的列头，自动填充注册人数、戳额人数、给额人数、成交额、成交人数、逾期金额、应收利息
=IF($B$3="总和",SUMIFS(XLOOKUP(C$7,'8月成交数据-纯数值'!$A$1:$U$1,'8月成交数据-纯数值'!$A:$U),'8月成交数据-纯数值'!$A:$A,$A8),SUMIFS(XLOOKUP(C$7,'8月成交数据-纯数值'!$A$1:$U$1,'8月成交数据-纯数值'!$A:$U),'8月成交数据-纯数值'!$C:$C,$B$3,'8月成交数据-纯数值'!$A:$A,$A8))