佩奇的数据分析学习之excel(二)

佩奇的数据分析学习之excel(二)

---

前言

文章内容来源于博主对B站UP主：”戴戴戴师兄“ 的学习笔记
戴戴戴师兄视频链接
练习数据获取来源：戴戴戴师兄
链接: https://pan.baidu.com/s/1uXVMupx4y1rw0amWjhyJgQ?pwd=1919

由于excel的功能多切杂乱，博客中仅学习一些数据分常用的功能
数据分析的目的：数据分析是指用适当的统计分析方法对收集来的大量数据进行分析，提取有用信息和形成结论而对数据加以详细研究和概括总结的过程。在实用中，数据分析可以帮助人们做出判断，以便采取适当行动。

所用的数据为外卖门店的一些信息
有以下字段：
日期；品牌ID ；品牌名称；门店ID；门店名称；城市；平台；平台i；平台门店名称；GMV；商家实收；门店曝光量；门店访问量；门店下单量；无效订单；有效订单；曝光人数；进店人数；下单人数；cpc（cpc指每产生一次点击所花费的成本）总费用；cpc曝光量；cpc访问量；商户补贴；平台补贴

---

一、excel是什么？

什么是excel，excel本质是一款电子表格软件，该软件是由一系列和行组成的，形成一个个网格，一个网格就是一个单元格，单元格可以存储文本，数字，公式等元素

二、excel的函数

excel最重要的就是函数和报表了
excel的函数大家在初中和高中相比都已经大概的学过了
这里有一篇excel常用的函数大全带有图文解析：函数大全

1.SUM

SUM函数是一个求和函数，以将单个值、单元格引用或是区域相加，或者将三者的组合相加。

语法：SUM(number1,[number2],…)

number1 （必需参数）要相加的第一个数字。 可以是具体数字，也可以是单元格引用或者单元格区域。

number2，这是要相加的第二个数字。
这里打开常用函数-练习版这个工作表
打开之后是这样，之后都在这里面进行函数的练习

我们在要求和的单元格内输入=SUM，系统会自动跳出一系列的选项，大家可以用上下键进行选择，用TAB键确定，系统会自动帮你填充你选择的函数

为了避免来回切换表麻烦。我们选择上方菜单中的试图再点击新建窗口，系统就会再分出一个窗口，两个窗口都是同一个工作簿，新建了窗口后我们继续操作。

系统只会给出前半部分的函数后半部分需要我们自己去取，要求1-8月的GMV我们就要去伴客源数据1-8月中取值，GMV列的所有数据就是我们要的1-8月的数据我们选中后按下enter键，系统会自动填入函数中

excel是可以跨表格求函数值的

相同的，如何取1月GMV和8月GMV的值呢
在我们只会SUM函数的基础上，我们需要框选1月份的GMV数据后再输入一个逗号再框选8月份的数据之后回车来得出结果

戴戴戴师兄整理的这份函数的练习，基本包含了所有最常用函数。大家一定要自己将全部空格都填上来练习一遍
博主也会在下面公布自己的答案大家互相学习

1-8月GMV函数如下（示例）：

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

1月和8月GMV函数如下（示例）：

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

如果看过第一篇文章，你会发现这些操作用数据透视表也能完成，可以自己动手去试试巩固知识

2.SUMIF

SUMIF函数是对选中范围内符合指定条件的值求和。

sumif函数语法是：=SUMIF(range，criteria，sum_range)

sumif函数的参数如下：

第一个参数：Range为条件区域，用于条件判断的单元格区域。

第二个参数：Criteria是求和条件，由数字、逻辑表达式等组成的判定条件。

第三个参数：Sum_range 为实际求和区域，需要求和的单元格、区域或引用。

使用了SUM后我们发现，想要取出不连段的数据都需要我们肉眼去识别，有问题就有需求
这里引出了一个新的函数SUMIF，对选中范围内符合指定条件的值求和。

以求2020/07/01日的GMV为例子，我们要给SUMIF填入三个参数，第一个参数是条件区域，这里为我们要作为条件选择区域的日期列，第二个参数是求和条件，这里为日期值是2020/07/01的那个单元格，第三个参数是我们要实际求和的区域，自己动手练习以下吧

sumif函数练习答案如下（答案不唯一）：

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

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

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

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

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

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

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

3.SUMIFS

SUMIFS函数，快速对多条件单元格求和。

SUMIFS函数语法是：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

sumifs函数的参数如下：

第一个参数：sum_range 是需要求和的实际单元格。

第二个参数：criteria_range1为计算关联条件的第一个区域。

第三个参数：criteria1为条件1，条件的形式为数字、表达式、单元格引用或者文本

第四个参数：criteria_range2为计算关联条件的第二个区域。

第五个参数：criteria2为条件2。

sumifs按理解来说就是拥有多个条件的求和函数，我们通过求某日的美团GMV，日环比，日同比来实际理解这个函数

拿第一个空为例，sumifs第一个函数是需要求和的实际单元格。这里应该选源数据的第J列，第一个函数是需要求和的实际单元格,这里应该选源数据的第J列。第二个参数为计算条件1的判断区域。选择的就是日期那列，第三个参数为条件1，选择内容为2020/07/31的单元格，以此类推后继为条件2的判断区域和条件2的值，所以应该选源数据的平台i列，条件二为“美团”（要在函数里用中文必须带双引号）

2020/07/31的函数应该为：

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

求出的值为5773.69
我们继续来计算日环比和日同比
那么什么是环比，同比呢
环比属统计术语，是指本期统计数据与上期比较,例如2014年7月份与2014年6月 份相比较,叫环比。
环比= (本期统计周期数据/.上期统计周期数据) x 100%。
同比：一般指的是某一东西今年同期月份与去年同期月份进行比较，计算出该东西的同比增长幅度。同比发展速度主要是企业、单位为了抛开季节变动等因素，来考量本期和去年同期的发展变化；

所以2020年7月的环比是和2020年6月相比的
2020年7月的同比是和2019年7月相比的
所以日环比=单日GMV/前日GMV-1

那么excel怎么计算出前一天的日期吗
excel的日期本质上是一个数字，0代表的是1900年的1月1日，前一天的日期只要当日的日期减1就能得到
所以2020/07/31的日环比=2020/07/31的GMV 除 2020/07/30的GMV

函数为

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

剩下的大家以此类推

下面我们再来看日同比的计算
2020/7/31的日同比有多种情况：和2020年6月31日相比，和和2020年7月31日前一周相比
前一周的日期好计算直接减7就可以
计算前一个月的数据
我们需要分别用year,month,day函数取出年月日，计算后再用date函数组合
想算B30格的前一月日期，函数为
=DATE(YEAR(B30),MONTH(B30)-1,DAY(B30))
以此计算上个月的GMV吧

由此我们可以计算出日同比

函数

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

接下来我们来求整个月的GMV值
让sumifs函数的条件变成大于等于第一天小于等于最后一天就行
每个月的第一天非常好算，把day函数改成1就是第一天
最后一天我们需要让下个月的第一天减去1天，不就是这个月的最后一天了吗
也就是 =DATE(YEAR(B39),MONTH(B39)+1,1)-1
大家自己尝试计算一下

让sumifs的条件变成大于等于第一天小于等于最后一天
以此我们可以求出美团这个月的GMV

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

当月的月环比

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

因为没有2019年的数据，所以2020年1月的月环比算不出来

4.subtotal

大BOSS函数
以一抵十的Subtotal函数，在计算隐藏数据时，有着无法替代的作用

语法：=SUBTOTAL(选择函数类型,ref1,[ref2],…)

选择函数类型：1到11（计算隐藏值），101到111（不计算隐藏值）


函数类型的数值都在上图中，在实际写函数的时候也会直接提示

我们分别用sum和subtotal来计算GMV

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

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

subtotal相比于sum来说要更加灵活好用

5.IF

IF函数是条件判断函数：如果指定条件的计算结果为 TRUE，IF函数将返回某个值；如果该条件的计算结果为 FALSE，则返回另一个值。

语法：IF(logical_test,value_if_true,value_if_false)

logical_test：测试条件

value_if_true：满足条件返回的结果

value_if_false：不满足条件返回的结果
我们的需求是判断GMV是否是大于某个值
以1月为例我们让C64大于100000，返回“达标”或者“不达标”。
=IF(C64>100000,“达标”,“不达标”)

6.VLOOKUP

VLOOKUP函数是一个运用非常广的纵向查找函数。

语法：VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

lookup_value：要查找的值

table_array：要查找的区域

col_index_num：返回数据在查找区域的第几列数

range_lookup：精确匹配/近似匹配

我们以此进行练习

通过门店ID来查找门店名称

拿第一个空为例，要查找的值在B96单元格，查找的区域在D到E列，返回的数据在D到E的第2列，所以选2，false或0代表精确查找，ture或1代表近似查找

=VLOOKUP(B96,'拌客源数据1-8月'!D:E,2,FALSE)

数据透视表聚合
我们还可以用VLOOKUP函数从数据透视表中取数值，这个操作比较常用到
先插入一个数据透视表

将行和值分别设为门店ID和GMV

这样就可以利用VLOOKUP读取数据透视表里的数值了

=VLOOKUP(O96,N$107:O$114,2,FALSE)  

为了让选择的区域在函数下拉的过程中不发生偏移，需要使用￥符号来进行固定

8.MATCH和INDEX

我们接下来要学会使用match和index函数基于源数据自由的取数并进行匹配，聚合运算
MATCH函数返回指定数值在指定数组区域中的位置。

语法：MATCH(lookup_value, lookup_array, [match_type])

lookup_value：查找的值

lookup_array：查找的区域

match_type：可选参数（1、0、-1）

使用match的目的就是求我们想要的数值是在第几行第几列
使用index函数输入所求数值的区域和所在行列来求出数值

比如我们要求门店ID这个字段在源数据中所在的列

要查找的值是D111，查找范围A1到X1，我们不希望查找范围发生改变所以要加$符号，0代表精确查找。

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

以此类推我们也能写出蛙小辣*美蛙火锅杯（宝山店）所在的列

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

用index函数可以在已知目标行和列的情况下取出数值，第一个参数为区域，第二个参数为行，第三个参数为列

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

接下来要求GMV的值

求GMV的值需要用到sumi函数
index行参数为0的话，函数会返回一列。列参数为0的话，函数会返回一行
可以利用这个来返回sumif的第一个参数，所要求和的GMV所在的列
第二个参数为条件所在的区域，也就是原数据的I列
第三个参数为条件，也就是B112单元格的数值

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