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

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

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

本文设计的数据资料文件，请点击本链接下载：

---

数据挖掘，Excel表数据分析

如何做出完美的数据分析周报呢

Ctrl+shift+L
使得表格进入筛选模式
这样的话，你的字段下面有多少不同的数据，都会展示出来

你下拉表头上面的三角就知道

这总空格是因为他们业务，关店重新开

商品交易总额 [1] （Gross Merchandise Volume，简称GMV）是成交总额（一定时间段内）的意思。多用于电商行业，一般包含拍下未支付订单金额

创建数据透视表

然后默认确定就行

门店名称拖到右下角，行
数值型的gmv拖到右下角求和
这样左边显示的就是各个门店的gmv

双击字段，可以改名字

在透视表 的分析中
你可以添加新的字段
在分析的项目
你得选中按个gmv总和字段
才有分析

你去算算单均实收

然后再修改一波
这时候单均实收已经添加到你的字段里面了

再加一个cpc单次点击费用

然后你看左边

透视表中的切片器

你选择这个切片器瞅瞅
当你选择平台这个字段
只看饿了么
左边透视表就只给你看饿了么的那些数据

美滋滋

清楚筛选
就全部看了

很直观吧，美滋滋

你也可以不用切片器筛选
用右下角拖动平台方式筛选

舒服
看左上角就有筛选的按钮了
这种方法呢，只能在本透视表里面筛选

但是切片器的好处就在，你可以复制它，到任何工作表去，但是你这个透视表，仍然可以联动筛选

数据透视图，各种图形展示你的数据

分析——数据透视图

默认是柱状图，你可以选择各种图像展示你的数据

看得出武宁路问题不大

商家实际收益很少
还可以用组合图展示

一个柱状图
一个折线图
他们都可以通过切片器联动筛选

这些都比较容易

对于Excel，最重要的还是，咱们得学它的函数
用函数来筛选计算

Excel常用函数：sum

咱们在这练习
你怎么告诉Excel你要输入函数了呢？
写一个=

它就可以解析了
否则它认为你这是字符串呢

然后你可以选中你想要求和的那些行，或者列，反正Excel基本都是基于位置的计算
先列字母，搭配行数
C15就是C列，15行
:到
C21就是C列，21行
好说

选中他们回车，自动就给你计算了

这种方式
可以求一个区域
一个行
一个列
反正矩阵列行确定就可以搞定的事情

一个格子就是Dx
比如下面求1月和8月，俩格子，就是逗号，并列的关系
一个格子C

一整列就是C:C列

多列C:D

扩区域Dx：Fy
左上角格子，右下角格子

所以求和，函数啥的都能控制通过各种位置，你选定就行
不行就输入位置到位置的信息即可

当你别的列变化了，这些结果也会联动变化的

Excel不仅可以在本表搞计算
它还可以扩工作表去计算哦！！！！

你可以写好函数，然后去另一个表选定你要计算的列，或者啥的
都可以

比如选定



''里面是表的名字，
!应该是一个标识符标识，表示非，非本表的数据哦！！！！骚
J:J列是我们选定的一整列计算区域

这，很麻烦！！！！

我们将这个做法简化一下，单独做一个视图
只看1-8的gmv
把它放在计算结果那个表格里面去
这有，就可以在当地选择列了

做视图，这有方面我们左右分屏选择数据
选择视图，新建窗口，就会创建一个和本图一样的东西

这只是view，仅此而已

然后你就可以win±->
分屏了

这样的话，视图就去左边了，而我们要操作的东西来了右边
你就可以写函数了

为了我们能准确选择1月和8月的数据
还要找到gmv
所以我们冻结首行，首列
选择B2这格子
动机窗口的话，是左上角所有都冻结

这样，我们就可以滑到右边看gmv
左边的日期也不会动的哦

中间用,隔开
代表你选定了2个区域
这样1月和8月的数据求和就搞定了
美滋滋

sumif单条件求和

下面我单看某几个日期呢？
要条件控制

只求日期为7月1日的结果呢
用sumif(range,criteria,[sum_range])
range求哪个字段，你可以直接选定表格中的日期那个字段
criteria，代表你字段中你要求哪个日期，选定7月1日就行
sum_range是问你要求什么参数，gmv呗
这样仨已选择
结果就出来了

sumif
第一步选定日期这个这个列
第二步选定它等于7月1日这个参数
第三步选择gmv
这样回车结果就出来了

第二个参数有，不论你选别的表格里面的7月16日，还是本表的7月16日
只要是日期对了就行

俩结果都一样

当然，你别一直这么玩，很慢！！！！！

有一个超级快的方法
你选择已经有函数那个格子比如6001.38
看到那个十字
往下拖，整个偏移往下，都是这样的函数，全部都同样的算法
牛逼得很

这样一下子就把所有的格子全部填好了
你就不需要一个个写了

当然，你要不想自动变，那就可以加$
或者ctrl拖

文本这样拖拽是copy

数字的话
按Ctrl是累加
不按Ctrl是复制

复制的是你选中的格子
如果你选12
那就是121212
反正你配合ctrl或者没有ctrl加下来拖拽，各种骚操作就知道咋回事了

注意
Excel中的数字是1900年1月1日那天
往后加多少天就是多少个日

单独写=代表引用这个单元格

如果你往右拖拽
你看看E15等于啥
其实就是引用C15+1列

你要是从D15往左拉呢？
实际上就是C15-1是B15
引用的是日期

它是一个数字
从1900年1月1日那天开始算的天数

sumifs多条件计算

sumif是先判断条件区域，判断条件，求和区域
而sumifs是求和区域，判断条件区域，判断条件，判断条件区域，判断条件

比如，我们要求美团gmv
求gmv，
第一个条件日期列，日期条件是7月1日，
第二个条件，是平台列，条件是美团
回车
‘美团’

竟然得到了0

是因为美团GMV这个汉字跟美团不匹配，平台
我们把H14那个格子直接用汉字’美团’替换
就OK了

要注意，你选平台不要选平台，而是平台i
因为平台里面是英文………………
平台i是汉语
你整错了啥也算不出来
哈哈哈哈哈

往下来拖拽，就是变行B30,B31,B31啥的，去引用不同的日期
整个表就简单的填写好了

可见sumifs的强大之处
多条件，and的关系

sumifs和sum可以互用，好说

多学一点，锻炼自己

啥是日环比，日同比

环比，跟之前一个月比
同比，跟之前一年来比

环比，2020年7月和6月比
同比，2020年7月和2019年7月比

这样少写一个数

同比=本期/同期-1
环比=本期/上期-1

举例：
2021年8月的同比=2021年8月/2020年8月-1【本期/同期-1】
2019年1月的环比=2019年1月/2020年12月-1【本期/上期-1】

同理
日同比=本日/上个月本日-1
日环比=本日/昨日-1

懂？

当天的gmv咋求，好说，我们之前求过了

前一天的话，我们用上面那个求7月1日的日期-1天，就是之前的那天
这样改

本日B30
上一日B30-1天就是了
然后带入环比公式

同理
咱们求日同比，跟上一个月的同比
然后我们调整格式
把这些格子变为百分比格式

保留小数点几位
好说

日同比有
周同比=当日/上周同日-1【相当于天是7天】
月同比=当日/上个月同日-1【月数要谨慎】

咱们来看这个月咋倒回去减一个月呢？

提取year

month，提取月

day，提取天

date函数组合，date(year,month,day)

如果month-1，不就上个月吗？
刺激

这样的话，上个月今天的gmv，把日期搞一下就得了

这样的话，
月同比，不就是今日/上个月今日-1

这不就得了
美滋滋

实际上就要把各种日期搞清楚
date转年月日为标准时间格式
year提取年，month求月，day求天
好说
同比，环比一定要搞清楚

注意，2020年1月，的上一个月，2020年是不对的哦！！

下面我们看月的同比，环比

一个月，可以用条件来控制
首先我们看年月的日期格式yyyy-mm

单元格设置数据格式就行
2020年1月就是B39这个格子
一会我们可以用这个算本月

每个月的第一天？怎么算呢，匹配格式，把日搞为1

每个月的最后一天，是下个月的第一天-1天就是了哈哈哈哈

错误的算法是，你直接把1改为30,31
可是2月只有28天啊，4月也是
不对劲，你看看

机智的算法是，用下一个月的第一天-1天
不就得了？？？

美滋滋吧
所以，一个月实际就上没月开头的第一天–每个月结束的最后一天之间

相当于sumifs多条件，多了一个月条件，俩月条件一个>=
一个<=
注意Excel不能直接用这个符号
需要加**“”&拼你要算的日期**
注意这个细节哦

多自己写写，写两次就熟悉了

别忘了加美团平台的条件哦

你要了解sumifs的第一个参数gmv就行
前其他的条件一个条件，一个取值范围
联合起来
配合日期来求，非常舒服

好，那我们就可以来求月环比了=本月gmv/上月gmv-1
上月的gmv，跟本月就是一个月的差，所以在条件那个地方控制一下就行了

这样就得了
你会发现19年没有数据，那就报错，无所谓

这节最重要的就是这个日期骚操作
sumifs里面配合使用这个条件
同时计算同比，环比，这些都要会

subtotal，可以集中搞好多函数

sum是直接就选一个列作和

subtotal，第一个参数，选一个函数1–9
9种

你点击一下subtotal
微软自动给你跳出来介绍

这样的话，subtotal实现的功能多一些

而且
当你筛选数据表之后

sum是无法改变的
而subtotal是会自动跟着变的

只看美团的话，你瞅瞅subtotal变了

if函数条件判断

IF 函数是 Excel 中最常用的函数之一，它可以对值和期待值进行逻辑比较。

因此 IF 语句可能有两个结果。 第一个结果是比较结果为 True，第二个结果是比较结果为 False。

例如，=IF(C2=”Yes”,1,2) 表示 IF(C2 = Yes, 则返回 1, 否则返回 2)。

我们来写一下

这就是一个三目运算符
当条件x达标，返回第二个参数
没达标，返回第三个参数

这就和其他程序语言一模一样的
美滋滋

注意，if条件如果有俩条件，可以套娃展示
比如A条件满足的情况下，你再去判断B【B可能满足可能不满足】
如果A条件不满足，则再去判断B【B可能满足可能不满足】

咱写一下第一个
比如A条件满足的情况下，你再去判断B【B可能满足可能不满足】
第一个条件，只需要看A
然后你第一个if的2目条件1，需要写if判断B，if的3目条件2写需要同样的if判断B

这样才能搭配出来

搞定啰

逻辑搞清楚很简单

好，我们来练习

非常简单，就一种情况达标，其余gg

稳得一笔

vlookup函数，寻找id对应的名称

【第一个参数】要查找的lookup_value，这个具体id号
比如，就下面2001104355这个id耗

【第二个参数】查找的id号，对应的列，与你要返回的数据的列，俩放一个区域！！！！！！！！要谨慎哦
2001104355号在哪呢？在下面D这列，同时，要返回的数据门店名称在E列里面
列一起选定哦

注意：这里vlookup的那个value，必须在你这个选定区域的第一列，这样，它可以让你第一列的东西做主键primary key

【第三个参数】你查找id号对应名称所在的区域的列号，一般挨着的话，就是第2列
上面要返回的门店名称就在我们选定的区域中的第2列

好说
这样就都出来了哦

【第四个参数】精确匹配0,1近似匹配
默认0

你看精确匹配之后就不一样了哦

咱们来看看精确匹配可能会报错的地方
没用匹配到就会
你看看下面我们找a对应的下标

但是没有，为啥呢
因为你全名那没有a
所以报错
模糊匹配呢？

也没有，所以模糊匹配其实Excel做得很差劲的

最好模糊匹配用正则化表达式来搞

我们可以加**&“星”**
星代表任意字符，可以模糊匹配
?代表占位符，匹配一个字符
比如我们写星

再来看看占位符？

b配上?
第一个就是bc
所以返回了6
俩??呢

所以呢，咱们需要整个模糊匹配才行
否则你需要让你要查找的那个值，一模一样才行，精确匹配出来

如果匹配到很多，就只返回首个找到的那个答案

利用vlookup函数来引用咱们sum求过的结果或者数据透视表引用的结果

咱们先建一个数据透视表，计算门店ID和他们的gmv总和

透视表放在我们的练习表中间哦

然后我们利用vlookup函数，把这个结果，带入到右边那个报表中
试试

发现下面很多不对
是因为偏移问题，咱们从96这行开始用，下面依次顺位递增，找下面的区域
这样的话，你要保证上面那个起始行位置不能变动
加$
这样就不会变了

选定这个区域那个坐标，按F4快捷键，就自动给你加了位置固定了
剩下的我们只会从o96往下递增，匹配id

这里很重要，就是我们左边你要查找的区域，Excel可能会自动给你增，要保证这个区域固定不变时
行列都要加$固定住

这里很重要，就是我们左边你要查找的区域，Excel可能会自动给你增，要保证这个区域固定不变时
行列都要加$固定住

这里很重要，就是我们左边你要查找的区域，Excel可能会自动给你增，要保证这个区域固定不变时
行列都要加$固定住

OK，vlookup聚合链接的使用方法，咱们就讲完了

上面所有的知识都是铺垫！！！
下面是高级Excel数据分析师的知识，学完你就是超级牛逼的大佬了，和普通Excel人才完全不是一个档次！！！！！！！！！！！
下面是高级Excel数据分析师的知识，学完你就是超级牛逼的大佬了，和普通Excel人才完全不是一个档次！！！！！！！！！！！

强大的index和match函数，Excel顶级用法

match函数

仨参数

lookup_value,你要查找的格子值
它在你准备选定的这个区域lookup_array中的哪个行或者列
match_type一般选0，代表精确匹配你这个格子值

比如我们看看
用match查一下，下面B114这个格子箭头对应的格子值lookup_value，它在你大括号选定的区域lookup_array的第几行？一看就是第三行

我们写一下：

没错就是第3个

再来看看

你瞅瞅品牌名称，它在我们选定的111行的哪个列呢？
没错就是第4个列

这就是match函数的作用

查x，你要选定的区域array中的第几个位置

index函数

仨参数

第一个，你手动选择一块区域
第二个，你指定的行位置，
第三个，你指定的列位置
比如
索引出下面这个区域的第一行第一列，是啥值？


竟然是索引出这个东西来

看见了没
index是索引某个区域的行列那个格子的值

而我们match函数，是拿这个格子的值，去算，它在你选定区域的哪个行列

有意思
俩互通了

现在，我们利用这个区域，

我们要做啥呢？
我们先用match函数，把门店id的行列捞出来


门店id在门店这一列的第1行
门店id在111这一行的第3列
其实也就是这大区域的第一行，第三列

然后，我们再用这个行列13，用index函数去定位区域中这个行和列它索引出的格子值【其实就是门店id】

得到了门店id，看下面

这绕来绕去，你用match定位门店id的行列13
又用13定位这个大区域的格子值是门店id
你想问我脑子有问题吗？？？？？？？？？？？？？

并没有问题

现在，请你把C129这个格子，往右拖拽，你看看得到什么？？？
这

看见了没，上面那些东西自动跟过来了
这就是我们学习Excel高级骚操作的地方

我们继续，待会这玩意有大用处
现在，基于门店名称，去找门店ID，品牌名称啥的
我们先看基于门店名称，如何揪出来这个门店id？？？

这波你就拿着门店名称，去源数据表中，找
不急，慢慢写函数，你慢慢就能理解这事的优点

你要
找名店名称对应的id，那么请问你这个id在第几行？？
源数据表里面id和名称格子值，是同一行
比如下面五角场店，它的id8184590，俩一定是同一行

所以，待会咱们拿index去整个源数据表区域中定位门店id的行，就用match去定位门店名称格子值的行
就行

懂了吗？不懂没关系，继续

那么要找门店id，门店id，应该在哪列呢？
也好说，应该用match直接去找门店id在首行的门店id在哪列

这样的话，行列，都知道了，自然index就能索引出你这个门店id在哪里了

看代码

我们要去源数据表中搜整个大区A:X整个区域

我们要找哪个行呢？
门店名称对应的行，就是门店id的行
写代码
用match匹配这名称的行

懂？
然后我们看门店id在哪个列，match代码如下

这里面刚刚的门店名称，改为平台门店名称哦

这样的话
门店id定位自己在源数据表中的列
就完事了
index的仨参数
1个就是整个源数据表
2个就是平台门店名称的行在哪？
3个就是门店id的列在哪
有了行列
就知道id是啥了，格子值

看懂了吗？
代码
从match定位谁在那个行，哪个列
然后index拿着这行列去找搜索区域的那个格子值

美滋滋不
自己多写写就知道了

OK，这里有报错
那我们排查一下问题在哪

大体思路我们都很清楚了

首先，我们把D122往右拖的话，其他的字段也能玩哦！！！！

显然有错，是因为我们要查找的区域向右移动了
要注意，我们要把源数据表里面总体查A:X列
这得锁住

由于往右拖拽的话，我们得让这个平台门店这个列的数据不动
那么就要把B112这个位置的行B 要锁住，但是行要递增，不能锁住【你看它百年C112是错误的，我们要的不是前面这个空格子，而是要平台门店名称哦】
你看看

锁住按F4

还有你看match中那个I:I列，是源数据表中对应的平台门店名称
这个也要锁住，这样前面的名字才能锁住

第二个match需要锁住谁吗？
因为门店id列，依次往下是递增的关系，D不能锁住
但是往右走的时候，从左边算起，这时候行111要锁住，这样才是从头递增
然后我们看源数据表的1:1也是要锁住，因为我们一直都在这个表头里面找门店id
所以经过上面一波锁操作
往右往下拉都OK了

这里一定要自己摸清楚错误在哪，应该锁住谁

自己屡清楚

完美了这波

同样的道理
咱们可以拿index去索引gmv，在原来表中的所有数据
当index的行为0时，代表读取所有行
只用match去找gmv这个值在源数据表中表头的哪个列？
就能读取原来数据表中gmv整个列
你看看这个代码

match就能返回gmv在表头的第几个列
配上前面的0行
在整个原数据表中就能用index把gmv这个列引用出来了
而不是直接选择gmv这列

然后，我们要在这里面，把符合门店名称那些行，全部揪出来求和gmv
用sumifs函数
第一个参数就是求和的目标区域
我们用index搞出来了

然后第二个参数是搜索范围，用原数据表中的平台门店门称区域中找

找咱们B112这个格子值
匹配上这个条件下gmv的总和

好处就是咱们待会往右拉
就能把进店人数，下单人数，一一都给找出来了
非常非常便捷

该锁住的关键数据锁住，这样往右拖拽就能正常地把相应的数据抓出来算了
你看看
下面match中G111中的行111要锁住，这样下拉不会把gvm这个格子值搞丢了
还有sumifs中第三个参数B要锁住，这样往右拉不会把格子值搞丢
你看看之后就对了

咱们再往右拖拽看看

进店人数这，由拉，match里面找列，确实把H这个进店人数格子值拿去寻找了
这样读取的就是整个进店人数这个列

这样的话报表用了很短的时间写代码
而数据全部一下子搞完了
非常舒服

这个玩意一定要熟练，练习，发现错误就找对应错误即可

非常舒服吧
美滋滋

sumifs这些函数要熟悉，几个参数，每个参数应该选哪个区域，或者那个格子
好说
——这就是高级Excel操作技巧

咱们一步步走到现在，非常非常不容
但是这些东西就是非常非常重要的基础知识

要学会了后面我们继续讲大厂周报如何制作

---

总结

提示：重要经验：

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