网站开发环境集成安装包,google官网下载,厦门公司注册网址,世纪兴seo公司自动化周报的数据引用来源于8月成交数据-纯数值表格#xff0c;因为8月成交数据表格中部分单元格中有vlookup函数#xff0c;且存在跨表连接。 对于跨表连接的解释和说明#xff1f; 首先打开我们之前做好的成交数据。打开后我们可以看到这上面出现了一个安全警告#xff0…自动化周报的数据引用来源于8月成交数据-纯数值表格因为8月成交数据表格中部分单元格中有vlookup函数且存在跨表连接。 对于跨表连接的解释和说明 首先打开我们之前做好的成交数据。打开后我们可以看到这上面出现了一个安全警告提示说已经禁止自动更新链接。因为在做成交数据的时候我们是把获客数据直接连接到了我们的成交数据上那么这是一个跨表格的连接。一般来说如果你不打开另外一个表格的话那么是没有办法同步另外一个表格的数据更新一般来说会默认啊禁止更新链接的内容。那么如果你要启用这个链接啊就是说你打开另外一个表格当另外一个表格数据有变动那么这边也跟着变的话。在你未启用内容之前呢那么数据是不会自动更新的。 虽然在原表中做了大量的vlookup和xlookup函数计算并且关闭了跨表连接但是双击拖拽过程需要消耗巨量的计算资源。如果我们自动化周报再继续基于一个这个套娃也是xlookup函数去智能识别这个数据然后匹配过来的这么一张表格的话那么计算资源的消耗将会相当大可能会导致电脑卡死。 自动化周报标题互联网金融数据分析周报位置在第一行 数据时间起始日期 至 结束日期位置在第二行 注意 先不要调整它的格式比如居中单元格、修改字体这些工作可以最后再调起始日期和结束的形式 手动输入(不高级)下拉菜单来选择高级 通过制作一个日期的辅助列可以放在表格的偏后的字段前面的字段区域需要制作自动化报表去实现辅助列也可以单独放到别的工作表里再隐藏起来最终实现打开下拉菜单后就能够去选很多日期实现下拉操作 操作顺序选择空白单元格–数据工具栏–数据工具–数据验证–允许序列来源辅助列的数据区域不包含字段名–确定 结束日期起始日期6周报且日期本身也是数值可以进行四则运算 战区筛选器每个大区对应自己的数据 实现下拉操作 操作顺序选择空白单元格–数据工具栏–数据工具–数据验证–允许序列来源总和,东部战区,西部战区,南部战区,北部战区 注意 来源的内容手动输入逗号为英文格式此时有5个选项总和选项方便看全国范围的数据 周报日报最重要的工作:公司的利润情况营收情况目标的达成程度月目标和周目标、进度条 我们希望月目标的数据要跟随着我们现在的这个筛选器中筛选值的变动而变动需要用到ifs函数的多条件判断,也可以做一个辅助列有两列一列是战区另一列是目标 法一ifs函数IFS(B3“总和”,K2,B3“东部战区”,K3,B3“西部战区”,K4,B3“北部战区”,K5,B3“南部战区”,K6)其中B3为战区筛选器筛选出的值K2、K3、K4、K5和K6是辅助列的对应的值 法二vlookup函数VLOOKUP(B3,N1:O6,2,0)其中B3为战区筛选器单元格值N1:O6为下图的战区和目标辅助列的数据区域2表示匹配数区域的第二列的值0表示精确匹配 周目标月目标/4 周报的必要模块和字段要根据战区名字和日期来变化先筛选日期和战区 日期 数据起始时间开始6天 星期X 将日期的时间设置单元格格式–日期–类型–周三 总计 流量模块 注册人数获客人数/用户数目要根据战区名字和日期来变化先筛选日期和战区 4个战区注册人数要根据战区名字和日期来变化 公式SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!C:C,$B$3,‘8月成交数据-纯数值’!A:A,A8) ‘8月成交数据-纯数值’!P:P求和区域这里是8月成交数据-纯数值表格中的注册人数列 ‘8月成交数据-纯数值’!C:C:判断区域1这里是8月成交数据-纯数值表格中的战区列 $B$3判断准则1这里是自动化报表表格中的战区值单元格这个单元格不想让它变化需要用美元符号来锁定 ‘8月成交数据-纯数值’!A:A判断区域2这里是8月成交数据-纯数值表格中的日期列 A8判断准则2这里是自动化报表表格中的日期值单元格 注意 如果筛选器的战区值为总和的话下面的注册人数等字段值是为0因为8月成交数据-纯数值表格中是没有总和战区的值的总和的字段值需要用sumifs另外计算此时判断条件只有日期没有战区名字了 总和注册人数要根据日期来变化 公式SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!A:A,A8)‘8月成交数据-纯数值’!P:P求和区域这里是8月成交数据-纯数值表格中的注册人数列 ‘8月成交数据-纯数值’!A:A判断区域2这里是8月成交数据-纯数值表格中的日期列A8判断准则2这里是自动化报表表格中的日期值单元格 结合四个战区总和嵌套下的注册人数计算 需要条件判断四个战区 OR 总和 公式1ifsumifsIF($B$3“总和”,SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!A:A,$A8),SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!A:A,$A8,‘8月成交数据-纯数值’!C:C,$B$3))无法自动识别 $B$3“总和”“总和”:判断的条件筛选器战区的值是否是总和(锁住单元格不想让这个值变化后面的字段值都是基于这个计算的)SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!A:A,$A8):若筛选器战区的值是总和则注册人数字段的计算公式上面总和注册人数写好的公式同一行的字段值都是根据A8单元格的值来计算同一列只是日期变化需要将行锁住只能用A:A,用其他的会报错SUMIFS(‘8月成交数据-纯数值’!P:P,‘8月成交数据-纯数值’!A:A,A8,‘8月成交数据-纯数值’!C:C,$B$3)若筛选器战区的值不是总和则为四个战区的名字则注册人数字段的计算公式上面四个战区注册人数写好的公式(锁住单元格不想让这个值变化所有的字段值都是基于这个计算的) 公式2ifxlookupIF($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月成交数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) (XLOOKUP(C$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U):在8月成交数据-纯数值表格中的第一行有字段值的单元格$A$1:$U$1中查找C7单元格的值并8月成交数据-纯数值表格的数据区域$A:$U不然用$A1:$U867会报错中在把注册人数列摘出来了 需要锁定的区域和单元格筛选器战区的值、日期列的列号、xlookup搜索和返回的区域 、if参数中的两个判断区域 注意 xlookup的第二个参数不能是’8月成交数据-纯数值’!1:1,要保证查找区域第二个参数和返回值区域第三个参数的长度或者宽度一致 如果单独在某个单元格里另计算xlookup只能在某列的第一个单元格里计算否则会报spill溢出错误因为xlookup返回的区域是整列行数1-100w如果xlookup计算的单元格前面有占用的单元格比如列名则无法返回整列数据 戳额人数多少人申请额度比如花呗 方法一修改注册人数公式计算中的列名无法起到自动识别的功能 自动识别对于有多个行字段或列字段或者两者都有的话向左或向右拉动填充柄可以完成字段填充。 方法二使用xlookup函数 IF($B$3“总和”,SUMIFS(XLOOKUP(D$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP(D$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–D7 给额人数申请额度未通过人数 方法一修改注册人数公式计算中的列名无法起到自动识别的功能 自动识别对于有多个行字段或列字段或者两者都有的话向左或向右拉动填充柄可以完成字段填充。 方法二使用xlookup函数 IF($B$3“总和”,SUMIFS(XLOOKUP($E$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP($E$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–E7由于下一个计算的而字段是戳额率8月成交数据-纯数值表格中没有这个字段不能自动识别需要将字段名锁死$E$7 戳额率戳额人数/注册人数 公式D8/C8 D8为戳额人数C8为注册人数 给额率给额人数/戳额人数 公式E8/D8 D8为戳额人数E8为给额人数 成交板块 成交额 公式IF($B$3“总和”,SUMIFS(XLOOKUP($H$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP($H$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–H7 成交人数 公式IF($B$3“总和”,SUMIFS(XLOOKUP($I$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP($I$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–I7 给额成交率 成交人数/给额人数公式I8/E8 I8:成交人数;E8:给额人数 注册成交率 成交人数/注册人数公式I8/8 I8:成交人数;C8:给额人数 成本板块 资金成本 获得资金的成本这里令为成交额的3个点 公式H8*0.03 H8成交额 获客成本 注册人数*0.5这里令为一个流量5毛公式C8*0.5 C8:获客人数 风险板块 逾期金额 公式IF($B$3“总和”,SUMIFS(XLOOKUP($N$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP($N$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–N7 利润板块 应收利息 借出去的钱实际应该回收回来赚的钱 公式IF($B$3“总和”,SUMIFS(XLOOKUP($O$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,$A8),SUMIFS(XLOOKUP($O$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成数据-纯数值’!$A:$A,$A8,‘8月成交数据-纯数值’!$C:$C,$B$3)) 思想跟注册人数一样只是想字段名字横移一个C7–O7 前台毛利 得到的资金-资金成本-获客成本-逾期风险可能会有部分人不还钱或者不能还完全部的钱 公式O8-L8-M8-N8 O8:应收利息L8:资金成本M8获客成本N8逾期成本 总计 快捷键选择求和数据区域–alt等号对字段的日期所有值 注意 求和对百分率的数据不适应后面需要单独修改暂且修改为面板最后一天的值资金成本和获客成本可以横移拖拽也可以上下拖拽他的公式都是同一行前面的字段*某个比例 关键数值 目标进度完成情况 周目标完成进度 成交额总计/周目标此例中H15/B5 H15成交额总计B5周目标 月目标完成进度 当前截止这个看板里面的最后一天一直到8月第一天之间所有的成交额再除以你的目标需要用到if和sumifs函数不用考虑锁定问题 IF($B$3“总和”,SUMIFS(XLOOKUP(H$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,“”EOMONTH($A$8,-1),‘8月成交数据-纯数值’!$A:$A,“”$A$14),SUMIFS(XLOOKUP(H$7,‘8月成交数据-纯数值’!$A$1:$U$1,‘8月成交数据-纯数值’!$A:$U),‘8月成交数据-纯数值’!$A:$A,“”EOMONTH($A$8,-1)1,‘8月成交数据-纯数值’!$A:$A,“”$A$14,‘8月成交数据-纯数值’!$C:$C,$B$3))/B4 sumifs函数中当月第一天到面板所显示最后一天的判断区域和判断准则‘8月成交数据-纯数值’!$A:$A,“”EOMONTH($A$8,-1),‘8月成交数据-纯数值’!$A:$A,“”$A$14 EOMONTH($A$8,-1)A8单元格的值前一个月的最后一天比较符合与函数之间要用连接且比较符号要用双引号扩起 B4月目标单元格值‘8月成交数据-纯数值’!$C:$C:判断区域3为成交数据-纯数据表格的战区一列$B$3判断条件3为战区筛选器的名称 当前进度的差值 计算公式月目标完成进度/周目标完成进度-1 正值表示已经超过计划进度负值表示未超过计划进度0.61是截止到面板这周为止完成月目标的情况0.89是面板这周的成交额/周目标 字段的观看逻辑老板关注的字段 对以下字段的变动进行可视化柱形图、折线图并引用数值 前台毛利 上面一个是这周的毛利和下面一个是上一周的毛利和 逾期金额 成交额 成交人数 注册人数 注册成交率 注意 整个逻辑跟一开始计算的顺序相反还要包括上周的字段数据。因为一般来说周报是看相比于上周的环比变化 制作上个月的字段数据 日期 这周对应星期几的日期-6 环比 当前时间单位值 − 上一个时间单位值 上一个时间单位值 \frac{当前时间单位值-上一个时间单位值}{上一个时间单位值} 上一个时间单位值当前时间单位值−上一个时间单位值这周数值/上周数值-1 看板的美化操作字体微软雅黑列宽除日期列其他列宽度保持一致 将之前计算字段中的中间计算操作在其他单元格上进行可以隐藏掉同时也可以将有中间操作的表格按住ctrl复制将中间操作的表格删除命名为最终版原表格不做改动 在第一列前面插入一列新的 改变字段排列位置将流量板块移到后面区域并将前台毛利放在第一个其次是应收利息、成交额总共赚的钱、逾期金额接下来是成本模块依次是资金成本和获客成本接下来是成交人数、给额人数和戳额人数 条件格式设置 进度完成情况和进度差值设置为百分比形式条形图格式开始-格式-条件格式-数据条-渐变填充)/差值显示 对于条形图格式可以修改最大值开始-格式-条件格式-管理规则-编辑规则-将最小值、最大值类型改为数字最大值的值对其进行修改 差值可以用不同颜色的图案来可视化开始-格式-条件格式-图标集–形状–三色交通灯并将大于0设置为绿色等于0设置为黄色小于0设置为红色 对单元格进行颜色上的设置 数值0字体颜色设置为绿色数值0字体颜色设置为红色 操作顺序1选中单元格–选择规则类型只为包含以下内容的单元格个设置格式-编辑规则说明–单元格值大于或等于0–格式选择绿色 操作顺序2选中单元格–选择规则类型只为包含以下内容的单元格个设置格式-编辑规则说明–单元格值小于0–格式选择红色 之后需要修改规则可以直接在管理规则那里改 运用格式刷对其他单元格进行设置按ESC退出对金额类型、人数类型的百分比数字可以将图案设置为上下箭头 修改盈亏的迷你图标记最高点和最低点 操作顺序同时选择多个迷你图–迷你图工具栏–标记颜色–高点–颜色橙色–低点–颜色橙色-迷你图颜色–金色–标记–金色 资金加上千分号不要小数率数据改为百分比小数点后一位 前台毛利和应收利息加数据条 观察相对于赚的应收利息而言前台毛利也是亏得怎么样若单独给前台毛利绘制数据条数据条红色会拉的很长会做的很难看要根据公司要求 同一量纲量级差不多的的数据圈在一起设置数据条 成交额逾期金额资金成本获客成本都是成本和风险项成交人数给额人数戳额人数注册人数为每一列百分比数据加上色阶 操作顺序开始-格式-条件格式-色阶-白绿色阶–管理规则–编辑规则–颜色改为金色数值越小越金越大越白按住Ctrl对其他单元格进行格式刷对这一组单元格同时运用格式刷就不会每一个单元格都是显示白色了 成交额低于平均数值上加个下划线 法一开始-格式-条件格式-管理规则-选择规则类型仅对高于或低于平均值的数值设置格式–格式–下划线单下划线 法二:开始-格式-条件格式-管理规则-选择规则类型选中E8单元格–使用格式确定要设置格式的单元值–为符合此公式的值设置格式$E8average($E$8:$E$14)–格式–下划线单下划线–拖拽填充柄将$E8变为$E9、$E10…,从管理规则上是无法看出来这个变化的) 字体大小和加粗上的改动 对于本周和上周的前台毛利、逾期金额、成交额、成交人数、注册人数、注册成交率加粗显示 增加边框线外边框线 对象本周各字段详细数据和上周各字段详细数据、字段名、总计行、日期列和周期列、数据时间行、战区行月目标行周目标行 增加填充颜色 对象数据时间、字段名 调整行高 标题居中放置方法字体 取消网格线 操作顺序视图栏–显示–取消网格线 Ctrl展示全部公式 字体大小和加粗上的改动 对于本周和上周的前台毛利、逾期金额、成交额、成交人数、注册人数、注册成交率加粗显示 增加边框线外边框线 对象本周各字段详细数据和上周各字段详细数据、字段名、总计行、日期列和周期列、数据时间行、战区行月目标行周目标行 增加填充颜色 对象数据时间、字段名 调整行高 标题居中放置方法字体 取消网格线 操作顺序视图栏–显示–取消网格线 Ctrl展示全部公式
