0 前言

听b站戴戴戴师兄的课程，太干了，听完课感觉自己都长脑子了，果然人只要忙起来，就不会想东想西想七想八，我宣布，学习是女人最好的医美！

1 收获

1. 学会使用 Excel 的几个常用函数：sum, sumif, sumifs, if, vlookup, index, match
2. 附带学习：Excel 操作新建规则、几个快捷键

观看如下👇🏻视频，就知道阅读完本章你会获得的知识：

下面是展示的图片，大家对接下来大概完成的任务有个基本的认识比较好，这样可以加深大家的印象。看目录也可以知道要学习的内容。

我感觉 index 和 match 是比较难的，所以会重点多次详细讲解，在学习这里的时候，务必打起精神，务必喝一口咖啡，哈哈哈哈哈哈！

2 实操

2.1 数据资源

通过网盘分享的文件：戴师兄数据分析启蒙课_Excel练习.xlsx
链接: https://pan.baidu.com/s/1ToVPpKvirmFiauzK85CYMw?pwd=y4sd 提取码: y4sd

2.2 了解数据

网盘里面的数据下载好，用 Windows 自带的 Excel 打开就是如下表所示的内容。家人们拿到数据要仔细看一看都有啥，不要焦虑不要心急，慢就是快~

根据上面图片下半部分圈红的 sheet 部分，可以点击进行查看，顾名思义，分别表示拌客源数据1-8月、数据透视表-完成版、常用函数-完成版、常用函数-练习版、大厂周报-完成版、大厂周报-练习版。

了解表格有多少行多少列，将鼠标放到行或者列的位置，就会看到右下角有所显示，24列，561行。

打开表格，首先进入筛选模式，查看每一列的内容分别有什么，这里介绍两种进入筛选模式的方法。

方法一：快捷键方法
我觉得这是每一个用 Excel 的同学都需要掌握的快捷键，方便呀！

ctrl + shift + L

方法二：UI 界面点击的方法
选中开始卡片，点击右边类似筛选的图标即可。

例如这里，我们点击品牌ID的小三角，可以看到在这个列名下面，有 4636、6108 两种品牌ID，通过这样观察筛选，我们能够更快的了解数据。其他的值，都是用同样的方法查看，大家要自己一一查看，了解数据，是数据分析的第一步、基石。

这里有个比较特殊的地方，图片中圈出来的 拌客·干拌麻辣烫(武宁路店) 看似一样，实则有细微区别，有一个多了一个 点，可能是重开的店铺，以作区分，有的新店有促销活动，但是开店效果仍然不好，有的店家会考虑重新开店铺，可能这样数据会好一点，这都是细节细节。

上面这张图片，可以发现 无效订单 + 有效订单 = 门店下单量 。

宏观层面：
表格数据是561行 × 24列 的数据

---

微观层面：
· 日期
· 品牌ID
· 品牌名称
· 门店ID
· 门店名称
· 城市
· 平台
· 平台i（中文名称）
· 平台门店名称
· GMV
· 商家实收
· 门店曝光量
· 门店访问量
· 门店下单量
· 无效订单
· 有效订单
· 曝光人数
· 进店人数
· 下单人数
· cpc总费用
· cpc曝光量
· cpc访问量
· 商户补贴
· 平台补贴

是不是看完列，就有疑问，cpc是啥？

CPC(Cost Per Click) 每产生一次点击所花费的成本

2.3 源数据备份

这个数据源备份的实操，我单独放一节，因为真的很重要！！！

这里是因为已经备份过一个了，所以会出现如下的问题，按正常来说是不会有这样的问题的。

到这里，源数据备份就完成啦！注意：以后拿到数据第一步是将源数据备份！可以将备份的数据源进行隐藏，这样不会混淆之后的操作。

2.4 视图

快捷键来啦

win + 上下左右的右（左也可以）

在后续我们处理数据的时候需要查看多个数据，引用数据，新建一个视图会更加方便我们进行操作。

操作完毕就如上图所示。

冻结窗格：实现固定列

以下👇🏻演示的是保证首行首列固定不动：

新建透视表之后再操作叭

切片器就是筛选的意思，可以从上面图片看出来有两种筛选的方法，拖拽的方法，它的有效性之针对该透视表，而UI界面点击的切片器，适用于整个视图（等我再研究一下）

点击饿了么，发现数据发生变化了，就是说切片器可以设置，与哪些透视表进行连接，这样就可以通过一个切片器控制多个透视表了。

2.5 Excel 七个常用函数

如何学习新知识，当然是从实操中掌握，因此我们通过完成表格中的填空练习，来学习 Excel 的七个常用函数。

首先打开 常用函数-练习版，常用函数-完成版 表格里有答案，大家可以自行核对哈！

审题：表格要我们求什么？1-8月GMV，根据上一部分了解到，数据源的数据就是1-8月的，因此我们将所有的GMV相加即可。

GMV = Gross Merchandise Volume，商品交易总额，是电商、平台最常用的核心经营指标。
简单来说，GMV = 所有下单金额的总和（里面包含未付款、退款、取消的订单）

SUM: 求和

· SUM(number1,[number2])
· SUM求和，很好理解，把选中的单元格相加即可
· []中的内容表示可选项，可填写也可不填写，后文都是如此，不再赘述啦~

=SUM('拌客源数据1-8月'!J:J)

=SUM('拌客源数据1-8月'!J496:J562,'拌客源数据1-8月'!J2:J25)

SUMIF: 单条件求和

SUMIF(range, criteria, [sum_range])
SUMIF(条件判断所在的区域,条件,[用来求和的数值区域])

SUMIF 单条件查询，顾名思义就是一个条件进行查询，这里就是根据日期查询当天的 GMV 总和。

审题：筛选的范围是 A 日期列，筛选的条件是日期为 B15 单元格的数据 2020/07/01，求和的范围是 GMV，所以单元格书写的函数是：

=SUMIF('拌客源数据1-8月'!A:A,B15,'拌客源数据1-8月'!J:J)

插入一个小知识：
· 在 Excel 中， $ 符号表示固定的意思

· 举个例子：
直接引用 B15 单元格，输入 =B15，向右拖拽，会发现单元格内容变为 C15，向下拖拽，会发现单元格内容变为 B16，接下来试试使用 $ 会是什么结果。
= $B15
· 在 B 的前面加上 $ 符号，列不变
· 拖动句柄向右，列不变，单元格内容仍然是 $B15
· 拖动句柄向下，行变，单元格内容是 $B16
= B$15
· 在 15 的前面加上 $ 符号，行不变
· 拖动句柄向右，列变，单元格内容是 C$15
· 拖动句柄向下，行不变，单元格内容仍然是 B$15

SUMIFS: 多条件求和

SUMIFS(sum_range, range_1, criteria_1, [range_2, criteria_2])
SUMIFS(用来求和的数值区域,条件判断所在的区域1,条件1,[条件判断所在的区域2,条件2])

SUMIFS 多条件求和，就是有多个限制条件进行求和，很好记忆，在英文中，两个或者两个以上就表示复数，所以函数名称在 SUMIF 后面加了一个 -S 。

审题：求 日期为 2020/07/31，且平台为美团的 GMV 总和，限制条件有两个，日期和平台，所以需要使用 SUMIFS 函数。

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30,'拌客源数据1-8月'!H:H,"美团")

捋一下思路：这里与 SUMIF 有一个非常不同的点是，SUMIF 将求和的项放在最后， SUMIFS 将求和的项放在了第一个，求和的是 GMV，首先将 GMV 列放在第一个，接着是描述限制条件， 限制1：日期为 B30 单元格，限制2：平台为美团。

答案和常用函数-完成版不一样，为什么呢？因为日期不一样啦，完成版是 2020/07/01

修改日期就一样啦！任君选择日期哈。

· 日环比：环想到圆环，理解以下，类似周期一样，日环比，就是今天和昨天相比，(今天-昨天)/昨天，化简为：今天/昨天 -1
· 月环比：这个月与上个月相比，(本月-上个月)/上个月，化简为：本月/上个月 -1
· 日期：Excel 中的日期可以用整型数值表示，所以相应的可以+1，-1这样的操作。

从上述实操，你会发现在 Excel 中，1表示的日期是 1900/1/1日，可以试一试+1会发生什么。

=C30/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,B30-1,'拌客源数据1-8月'!H:H,"美团")-1

DATE：日期

· DATE(year, month, day)
· DATE(年，月，日)
· YEAR(serial_number) YEAR(日期)
· MONTH(serial_number) MONTH(日期)
· DAY(serial_number) DAY(日期)
· 永远不要用 Execl 日期格式存储日期，用字符

同比：同表示相同，同一时期相比，但是是和去年的统一时期相比，很好理解
日同比：2020/07/01 和 2019/07/01 相比
年同比： 2020 和 2019 相比
把日同比改成月环比，因为源数据中没有 2019 年的数据

=C30/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!H:H,"美团",'拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)))-1

这样我们就能够获取到年了，同理，月和日也能采用同样的方法获得

=MONTH(B30)

=DAY(B30)

=SUMIF('拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)),'拌客源数据1-8月'!J:J)

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,DATE(YEAR(B30),MONTH(B30)-1,DAY(B30)),'拌客源数据1-8月'!H:H,"美团")

继续拖动句柄~

换个思路，3月份的最后一天，是不是4月份的第一天-1

=DATE(YEAR(B39),MONTH(B39)+1,1)-1

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B39),MONTH(B39),1),'拌客源数据1-8月'!A:A,"<="&DATE(YEAR(B39),MONTH(B39)+1,1)-1,'拌客源数据1-8月'!H:H,"美团")

“>=” 、中文等内容要用英文双引号包含起来，且要加入&符号，表示连接上

=C39/SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&DATE(YEAR(B39),MONTH(B39)-1,1),'拌客源数据1-8月'!A:A,"<="&DATE(YEAR(B39),MONTH(B39),1)-1,'拌客源数据1-8月'!H:H,"美团")-1

那为啥结果是这样的呢？
因为 2020 年一月份的上个月是 2019 年 12 月份，源数据中没有 2019 年数据

SUBTOTAL

SUBTOTAL(function_num,ref1,[ref2],…)
SUBTOTAL(指定函数，选择区域1，[选择区域2],…)

SUM 和 SUBTOTAL 区别
SUM 就是对整体求和
SUBTOTAL 会根据源数据的筛选进行变化，更加灵活，可以实现求平均值、计数、最大值等效果

感兴趣的地方，大家可以多多尝试，还挺有趣的

IF: 逻辑判断

IF(logical_test, value_if_true,[value_if_false])
IF(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
[value_if_false]: 该参数选填，没有该参数时，返回值False

=IF(C64>100000,"达标","不达标")

VLOOKUP: 连接匹配数据，查找

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
VLOOKUP(要查找的数据、要查找的位置和要返回的数据、要返回的数据在区域中的列号、返回近似匹配或精确匹配-指示为1/TRUE或0/FALSE)

*: 代替不定数量的字符
?: (英文输入状态下)代替一个字符

家人们，重难点来了，掌握了，会发现 Excel 如此智能自动化，非常好使，所以喝口咖啡，上个厕所，醒醒神，开始闯关叭~

INDEX & MATCH：查找

INDEX & MATCH 是 Excel 中的最强查找组合，比 VLOOKUP 更加灵活、好用。

· INDEX(array,row_num,column_num)
· INDEX(区域,行号,列号)
· 作用：从一块区域里面，按照第几行、第几列把值取出来
· INDEX 行位置为0返回整列，列为0返回整行

· MATCH(lookup_value,lookup_array,[match_type])
· MATCH(查找项,查找区域,0)
· 作用：找某个值在区域里是第几行或第几列
· 只返回一个数字：位置序号
· match_type：0 表示精确匹配
· MATCH 不支持合并单元格
· 一句话：MATCH 找位置

index(数据区域,match(行查找项,index数据区域的相对区域,0),match(列查找项,indexB数据区域的相对区域,0))

看上面这个图片，理解一下 INDEX+MATCH 组合。

=INDEX('拌客源数据1-8月'!A:X,MATCH(B112,'拌客源数据1-8月'!I:I,0),MATCH(D111,'拌客源数据1-8月'!1:1,0))

这里分开解释以下两个 MATCH。

第一个是找到 行 的数值：

第二个是找到 列 的数值：

找到确定 行 确定 列 ，就能够确定一个值：

$ 固定操作

=INDEX('拌客源数据1-8月'!$A:$X,MATCH($B112,'拌客源数据1-8月'!$I:$I,0),MATCH(D$111,'拌客源数据1-8月'!$1:$1,0))

下面求 GMV 需要用到条件求和了
审题：对平台店名称为特定值的GMV求和，限制条件就只有一个，那么使用 SUMIF 足够了，先尝试简单的。

=SUMIF('拌客源数据1-8月'!I:I,$B112,'拌客源数据1-8月'!$J:$J)

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!$I:$I,B112)

接下来，我们试试将区域用 INDEX&MATCH 替换，非常有趣。

=SUMIF(INDEX('拌客源数据1-8月'!A:X,0,MATCH(B111,'拌客源数据1-8月'!1:1,0)),$B112,'拌客源数据1-8月'!$J:$J)

=SUMIFS(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)),'拌客源数据1-8月'!$I:$I,$B112)

奇怪了，我用这个SUMIFS的就可以横纵拖拽都可以！

解决了，将 SUMIF 的改成下面这个，也可以横纵拖拽，下面会分析原因的！

=SUMIF('拌客源数据1-8月'!$I:$I,$B112,INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(G$111,'拌客源数据1-8月'!$1:$1,0)))

然后，试着固定某些值，让我可以通过实现拖拽达到我们需要的效果。

=SUMIF(INDEX('拌客源数据1-8月'!$A:$X,0,MATCH(B$111,'拌客源数据1-8月'!$1:$1,0)),B112,'拌客源数据1-8月'!$J:$J)

我固定的这些值，纵向可以实现拖拽，但横向不可以，继续慢慢探索一下。这一次可以横向纵向拖拽，可能是使用了 SUMIFS ，我把GMV那一列用 SUMIFS 试一下。

问题解决：
· 是对要求和的值进行查找，而不是查找固定的条件
· 也就是说平台门店名称就等于那一列的值，没有必要使用 INDEX&MATCH 进行划分区域，它是固定的，不需要动态查找
· 而要求 GMV、进店人数、下单人数，这些值它是变化的，所以需要到 INDEX&MATCH
· 结论：固定的值不需要使用 INDEX&MATCH ，变化的使用 INDEX&MATCH 。

3 下期学习内容预告

大厂周报
学习完本篇博客，你将会获得如下周报报表，你以为只是简单的 Excel 表格，那简直是大错特错了，他是可以自动变化的，第一次见识到 Excel 的实力，即使只是一些皮毛，感觉世界真美好，还有这么多东西等着我去探索~

4 后记

如果博客对你有帮助，记得帮我点个赞赞~

家人们，这篇博客太肝了，主包硬是写了完整的两天，这两天里感觉疯狂长脑子了，然后思路捋顺了一些，表达观点更加清晰了。

今天去食堂吃饭晚了，爱吃的菜都没了，吃了一个别的窗口，说实话有点咸了~下次我就勉为其难的早点去叭!

给某书早上发微信，到中午才回我，真的很难过，然后我就故意的，仿佛是要让他知道我的生气似的，忍着没有回他，好像也没有什么立场生气，哈哈哈哈，没事，想做啥就做啥叭，我一直没回他消息，他也没想着找我，呵呵~

原来我的 Forest 邮箱是微信注册的qq邮箱啊，找到自己的帐号啦，还一定要是全球服，好开心~

作业：学习完所有课程后，点击 EXcel 菜单栏的所有按钮，看看都有哪些功能，记得是所有课程之后哟~