数据挖掘与分析应用2：大厂制作周报报表制作方法与标准格式，联动使用index和match配合sumif和sumifs函数

数据挖掘与分析应用2：大厂制作周报报表制作方法与标准格式，联动使用index和match配合sumif和sumifs函数

2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
与此同时，既然要考网警之数据分析应用岗，那必然要考数据挖掘基础知识，今天开始咱们就对数据挖掘方面的东西好生讲讲

【1】数据挖掘与分析应用1：Excel表数据分析，sum，sumif,sumifs,vlookup,match,index,几个配合使用

---

数据挖掘：大厂制作周报报表制作方法与标准格式

本文是紧接着上面文章【1】的哦

咱们的目标是根据源数据表，制作标准的大厂周报报表

数据在上面的文章【1】里面下载哦！

下面的练习版本都需要你自己去填写

首先，日期这些，为了方便日后我们稍微变动一个开头的日期，其余所有地方都跟随着变化
下周你做周报的话，是不是就得改为下周的日期
一周的区间开头变了，其余就跟这变，这样的话，很方便，不用一个个格子去改写了

日期为2020-08-10
到2020-08-16
这么一周的周报
你自己写进去

然后拖拽A14，不是A13，将来我们一变动A13，其余格子需要跟着变化的哦
所以拖拽A14下拉

你试试，改变10号位下周的17日，其他的都改变了

这样你就不必一个个改了，这才是Excel基本的骚操作
同样，我们把这个星期也引用A13格子的数据
然后改一下数据单元格格式为星期就行了

改格式


如何，高效吧

还有，数据时间那，是10号到16号，也是引用A13格子就行

你再试试，只要A13一动，其余全部跟着动

那么，下周你再写这个表格，不就很容易了吗
贼爽

按照这个表格，我们把其他的参数自己写好

下面，咱们利用文章【1】讲过的各种Excel操作来填写这个周报，保证日期变动，整个表格其他地方全部变动

同时，我们希望有美团和饿了么的区分展示
也就是需要一个多种筛选的条件
全部
美团
饿了么
在数据——数据验证——序列，写好这仨，中间英文,隔开


这样就可以筛选了
未来作为多个条件玩

如果你要全部平台，那么就是整个日期的gmv求和
否则还有加多条件，控制平台内部的gmv求和

上面这个函数
做两件事
判断平台是否为全部，是就直接求gmv，用sumif函数求
平台不是全部，就用sumifs，控制多条件，一个是日期，一个是平台
这好说
懂？

我们在文章【1】中说过很多了这信息
此时你下拉，肯定不对，都是0，因为我们往下拉，保证引用要正确

除了A13的13需要递增，其余格子统统不能变
所以都给锁住，懂吧

这样就OK了
单选饿了么

看全部呢

我们现在啊，想用index，把gmv弄过来加
懂吧，我不要死用原数据表中的gmv
我还想把日期列也通过index搞出来，
这样我才可以向右拖拽C13格子把所有的数据都搞出来

index很重要懂吧，文章【1】说过了用法

好，我们现在为了不直接选择原数据表中的日期列、平台i列，和求和列gmv
我们用index来定位索引他们
里面match来定位行列
懂？

日期列、通过match找这个日期这个字所在的列，去表头搜
然后通过index就你能倒找源数据表整个区域中所有行，match那个日期列
这样就能定位出日期来

求和列gmv
我们要去定位gmv
那么match要去定位gmv这个值，它在表头的哪个列
通过index去整个源数据表中搜，所有航，match那个gmv的列
这样就把gmv数据倒出来了

平台i列，
match拿着平台i这个值，去表头搜，表头要锁定哦，看看在哪个列
然后index，去整个表搜【锁定】，搜所有行，match固定的平台i的列
这样就把平台i的值倒出来了

OK，这几个，我们不是直接选，而是引用，锁定该锁定的值
我们拿这仨去替换最开始gmv那格子
c12那个地方的函数

点击C12格子
先替换日期列

然后替换求和列
sumif的求和列在最后位置
sumifs的求和列在第一个位置

然后替换平台列
平台i就是sumifs的第四个参数，替换就行了

=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A12,'拌客源数据1-8月'!$1:$1,0)),$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C12,'拌客源数据1-8月'!$1:$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($C12,'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A12,'拌客源数据1-8月'!$1:$1,0)),$A13,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))

整个替换之后的代码
就是这样
SUMIF里面是咱们全部平台的求和方法
要了gmv
sumifs是分开平台多条件配合日期求和的方法
不过找日期，找平台，找gmv，都是通过index和match引用的方法

现在我们可以C12网右下角去拖拽，其他的就一一出来了
再改一下
那个gmv列，里面的C不能锁定哦，因为你要又拉，偏移就要找gmv和其他的商家实收这种值
所以c不能锁定

然后你就可以右拖拽了
刺激

至于报错很简单，是因为到手率=商家实收/GMV
客单价=GMV/有效订单

还有除法要玩
所以我们需要把这个除法的事情搞清楚

首先到手率

然后
客单价重新写

好说
整这个表就很清楚了
easy呗

那么下面这个表不就好说了吗


这好说

看曝光人数
和上面的GMV很相似
我们不要直接用源数据表中的曝光人数
而是用引用的方式，找到对应平台，相应日期的曝光人数
其实就只改match中的gmv这个值为曝光人数这个格子就行了，列不能锁住，行锁住就行

相应的位置都锁定到位之后
就可以生成了
至于需要计算的肯定就不对了
我们改一波就行好说
先看pgc比赛
待会继续

现在销售占比是cpc/GMV
cpc总费用我没得去拿源数据里面的东西：cpc总费用
还是index来引用那个东西
本表没有cpc总费用
那直接写match去匹配这个值就行了

=IF($H$5="全部",SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$24,'拌客源数据1-8月'!$1:$1,0)),$A25,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0))),SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("cpc总费用",'拌客源数据1-8月'!$1:$1,0)),INDEX('拌客源数据1-8月'!$A:$X,0,MATCH($A$25,'拌客源数据1-8月'!$1:$1,0)),$A24,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH("平台i",'拌客源数据1-8月'!$1:$1,0)),$H$5))

直接用c25格子的函数
把我们要求的曝光人数这个东西改为"cpc总费用"
就行了
其余的也不用管的
仅仅是求和目标变为cpc总费用了

done
那么营销占比也就很好求了
直接

是不是灰常简单

咱们来筛选一下平台
比如选择饿了么

下面我算最后一个东西
总计

求和快捷键Alt+=
选定一个区域，直接求和

比如选定gmv的C13–C19
直接求和就行

向右拖拽即可
然后有除号的重新下拉


cpc总费用，一周的话，我那有统计，上面gmv也是c20总gmv出来
好说

其他依次类推
美滋滋
用简洁的方法求cpc，并拿下营销占比，其实不要整太复杂

很容易的

关键就在sumif和sumifs函数与index配合match函数使用
sumif三参数，时间区域，条件，目标求和列
sumifs很多参数，目标求和列，时间区域，条件，平台区域，条件
index函数仨参数，搜索区域，行号，列号
行号列号用match求
match仨参数，格子值，搜索区，0精确匹配

然后统一格式

这仨地方设置单元格格式数值，百分比留下2位小数

下面我们把周累计
直接用下面的结果就行了

很简单

然后添加迷你图
选中曝光人数的一周数据
插入——折线图，标记

这里稍微修改一下
cpc是要搞准的，日期别错锁住
然后cpc求和列改对了才行哦

OK
标记一波

让折线图好看点

学透了这些东西
练习熟悉，多花点时间

下面我们填写周环比的数据
周环比=本周数据/上周数据-1

公式记住了

我想了个办法
把上周的再做一个报表就行了，不要搞太复杂的函数

那么上周的数据不就有了吗
那现在套公式就行了
比如

拿本周数据/上周数据-1
就得了

其他同理……

搞定了
和老师的复杂函数搞出来的事情一样
他太复杂了

至于目标好说
随便定

下面算一个月的gmv
用来除目标
就是进度

好说

你引用一个月的gmv，就要算本月的第一天，用date函数捞
下一月第一天，也是date
用sumifs多条件就把目标gmv求出来了
都是ndex引用的方法
好说

然后求目标进度

然后我们把进度，加一个进度条
点击开始——条件格式，新建规则，把那个数字改为max=1，颜色啥的搞出来，做一个数据条
渐变填充
百分比改为无小数

至于其他的，可以改颜色啥的，也是条件格式
新建规则，根据数值，现实不同的颜色
来

确定之后，你会发现，按照你的想法提示颜色了

你还可以根据数值，展示它上升了还是下降了

图标集加入就美滋滋了
然后格式刷，双击格式刷
全刷了哈哈哈哈

单击不行哦

下面选定一个区域A13–H19
新建规则
使用公式确定要设置格式的单元格
只看gmv中谁小于了平均gmv
整个行都变特定格式
用公式
决定

好处呢，就是你看起来直观明了，哪天除了问题
赚的不多

咱把标题合并单元格，加粗变大

然后把整个表格网格去掉
视图——网格——去掉勾选
是不是高逼格就上来了

然后把该加粗的加粗
没管搞搞
齐活了

下周的话，你只需要把A13改为17号就行了

完美不？？？？
服不服

这就是我们通过2篇文章的学习
花了3个小时学学本课

自己联系
搞清楚index和match
配合sumif和sumifs函数
date函数

理解，练习，就完成了！！！！！！

佩服我自己！！！

今后去大厂面试的话，好说，拿下
还有你要是考网警，数据分析岗，应该说，问题不大了就，上机干就是了！！！！！！

---

总结

提示：重要经验：

1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。