没备案的网站怎么做淘客,网站建设选亿企网络,设置网络的网站,镇江网站建设包括哪些这是Excel大厂自动化报表实战第二期--互联网金融-数据分析周报制作上 数据资源已经与这篇博客捆绑#xff0c;有需要者可以下载通过网盘分享的文件#xff1a;2.4自动化报表-8月成交数据.xlsx#xff0c;2.4自动化报表-8月获客数据.csv等2个文件 链接: https://pan.baidu.co…这是Excel大厂自动化报表实战第二期--互联网金融-数据分析周报制作上 数据资源已经与这篇博客捆绑有需要者可以下载通过网盘分享的文件2.4自动化报表-8月成交数据.xlsx2.4自动化报表-8月获客数据.csv等2个文件 链接: https://pan.baidu.com/s/16nzy1ImOdWzmyHgKiuhiVQ?pwd0623 提取码: 0623 作者建议 强烈建议在学习制作互联网金融周报制作前先去看Excel大厂自动化报表实战第一期--高级函数与数据连接许多知识将在制作过程中直接运用看完第一期接下来制作才会理解更加深刻 在接下来三期将带大家制作只需下拉选择日期以及区域就可以筛选数据并且智能识别字段的自动化报表
前言 在Excel大厂自动化报表实战第一期--高级函数与数据连接我们已经将获客数据导入到成交数据里打开8月成交数据发现安全警告已禁止自动更新链接 启用内容 这是什么东西呢 它提示说已经禁止自动更新链接在Excel大厂自动化报表实战第一期--高级函数与数据连接这一期我们做成交数据的时候我们是把获客表格的数据直接连接到了我们的成交数据上面那么这是一个跨表格连接像这种连接的话如果我们不打开另外一个表格的话那么是没有办法同步另外一个表格的数据更新的。同时一般来说会默认禁止这个更新链接的内容。所以它提示已经禁止自动更新了。如果我们要启用这个链接就是说我们打开另外一个表格另外一个表格有数据变动这边要跟着更新的话我们就要点击启用内容了。在我们未启用内容之前的话那么之前的这个数据是不会自动更新的。当然了。我们接下来三期的制作只需要把它叉掉即可。注意接下来引用数据的时候我们都从纯数值表格进行引用(纯数值表格的创建详细见Excel大厂自动化报表实战第一期--高级函数与数据连接)因为成交数据里面我们做了大量的vlookup函数并且有跨表连接虽然跨表连接不会启用但是我们选中用xloopup函数制作的数据时双击拖拽都要消耗巨量的计算资源。因此如果我们自动化周报再继续基于一个也就是xloopup函数去智能识别的这个数据然后匹配过来的这么一张的表格的话那么计算资源的消耗将相当大很多电脑将直接会卡死。所以我们就复制一张纯数值表格过来。 一、周报制作准备阶段 1.新建一个窗口把我们新建的窗口放到右侧(窗口的创建详细见Excel大厂自动化报表实战第一期--高级函数与数据连接),后面写函数的时候我们就可以直接在新建的右侧窗口查看并且选择这个列左边窗口切回自动化周报这个表。 二、周报制作构思阶段 1.在做任何周报前第一步肯定是要构思周报肯定要有一个标题那么我们的标题首先就留在第一行在A1单元格上写互联网金融-数据分析周报格式统一最后调。一份报表肯定要有它的数据的时间的在A2单元格写上数据时间。一个数据时间肯定是有起始日期然后再到我们一个结束日期。 核心要点1 那么起始日期怎么设置呢 当然我们可以直接手动填起始日期但这样手动填肯定不高级选日期肯定更高级。那么怎么去实现日期的筛选呢 通过制作一个日期的辅助列去实现,我们在做第一版报表的时候可以先把这些辅助列都放到一个报表里然后之后再去把它单独放到别的工作表隐藏起来 核心要点1解决方法: 首先我们要它打开后能够选很多日期首先要给Excel提供这些日期在稍微往后面一点的列去做辅助列在U1单元格写上辅助列-日期(前面会放很多很多字段先留好区域)U2单元格写上2020/8/1U3单元格就是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就好了即B21依次向下拉拖拽总共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:$1U列之后虽然都是空的但它也会参与运算但我们选择返回的区域里面也就是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))
2.戳额率戳额人数/注册人数在F8单元格输入D8/C8,给额率给额人数/戳额人数在G8单元格输入E8/D8
3.给额成交率成交人数/给额人数在J8单元格输入I8/E8,注册成交率成交人数/注册人数在K8单元格输入I8/C8
4.资金成本不是已有的字段这里资金成本成交额*0.03,获客成本注册人数*0.5 前台毛利应收利息-资金成本-获客成本-逾期金额 5.计算总计列可以用快捷键alt等号向右拖拽记得比率的肯定错的还有资金成本、获客成本、应收利息这样的字段需要往下拖。 下一期我们将进行关键指标的计算
