自学数据分析——Excel函数基础操作01

本文以线上外卖订单数据为例，讲解Excel中的sum、sumif、subtotal、if、vlookup、index与match函数。本篇博客讲解sum、sumif、subtotal、if及if嵌套这几个函数，剩下的在下一篇哦~

部分数据展示


首先为了方便使用，新建一个数据窗口。

sum函数

sum函数是求和函数，主要用于单个或多个单元格、整列或整行的求和。求和实质上是选择需要的求和的单元格，如何正确理解需求和高效计算是关键。

用法主要有：
=SUM(单个或多个单元格)
=SUM(列的名称，如A:A)
=SUM(行的名称，如1:1)

• 计算1月到8月的总GMV

在单元格里输入=sum()后直接点击GMV那一列即可自动填充代码内容。

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

• 计算1月和8月的GMV

用逗号分隔开不连续的数据。

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

结果为：

sumif函数

sumif函数是根据条件筛选所需要求和的区域，一般分为单条件求和与多条件求和。

单条件求和sumif

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

例如求日期为2020/07/01的GMV总和，则代码为：

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

依次点击图片中的位置自动填充代码。

下面日期的GMV计算与第一个基本一致，快速方法为将鼠标移动到已经计算出的单元格的右下角，当变为＋号时下拉单元格直接快速填充。结果为：

多条件求和sumifs

=SUMIFS(sum_range,[criteria_range1],[criteria1],[criteria_range2],[criteria2]…)
=SUMIFS(用来求和的数值区域，条件1判断所在的区域1，条件1，条件2判断所在的区域2，条件2，…)

例1.求日期为2020/07/01的平台为美团的GMV总和，则代码为：

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

依次点击图片中的位置自动填充代码。

其他日期的美团GMV计算与第一个相似，大家可以自己练习一下，或者直接快速填充。

下一个例子是求日环比和日同比，下面还有要求月环比的。首先先了解一下什么叫做环比和同比。环比是本期统计数据与上期比较，例如2022年2月份与2022年1月份相比较。环比一般是用在月、日很少用在年上，主要是对比很短时间内涨幅程度，不过由于行业差异，比如旅游，会受到淡旺季影响。同比是与历史同时期比较，例如2022年1月份与2021年1月份相比。同比发展速度主要是为了消除季节变动的影响，用以说明本期发展水平与同期发展水平对比而达到的相对发展速度。在实际工作中，经常使用这个指标，如某年、某季、某月与同期对比计算的发展速度，就是同比发展速度。（作者：分析老贼，链接：https://www.zhihu.com/question/21909388/answer/3080956241）

同比 =（本期数-同期数）/同期数 = 本期数/同期数-1
环比 =（本期数-上期数）/上期数 = 本期数/上期数-1

例2.计算2020/07/01的美团GMV的日环比：

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

例3.计算2020/07/01的美团GMV的日同比：
日同比有两种概念，一种是周期为周，一种是周期为月。（如果周期为年的话就是月同比了）。基于以上概念，需要学习提取年、月、日的函数和日期组合的函数。注意需要将得到的数据变成数字形式。

=YEAR(日期)
=MONTH(日期)
=DAY(日期)
=DATE(year,month,day)

本题日同比我们选取以月为周期，首先计算上个月这一天的GMV：

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

然后直接计算日同比：

补充：如果要计算2020/7/31的前一个月那天，不能用DATE函数，要用EDATE(“2020/7/31”,-1)，-1表示日期的前一个月那天，得到的结果为2020/6/30才是正确结果。

例4.计算2020/01的美团GMV的月环比：
有了上面的基础，那么这题最主要的就是确定日期区间了，日期区间=每个月的最后一天 - 每个月的第一天。

每个月第一天 = EDATE(日期,0) 或者 date(year(日期),month(日期),1)
每个月最后一天 = EOMONTH(日期,0) 或者 date(year(日期),month(日期)+1,1)-1

然后计算所给日期的美团GMV：

=SUMIFS('拌客源数据1-8月'!J:J,'拌客源数据1-8月'!A:A,">="&EDATE(B39,0),'拌客源数据1-8月'!A:A,"<="&EOMONTH(B39,0),'拌客源数据1-8月'!H:H,"美团")

最后计算月环比，表示上个月的就是把0改为-1：

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

subtotal函数

subtotal函数比sum函数的应用更广泛，它不仅可以求和，还可以求平均值、最大值、最小值、标准差、方差等，最主要的区别是subtotal函数需要指定函数。

=SUBTOTAL(指定函数,选择区域1,[选择区域2],…)

if函数

=IF(逻辑比较条件,结果成立时返回的值,[结果不成立时返回的值])
结果不成立时如果没有设置返回值，则返回False。

if嵌套

if嵌套语句的逻辑很重要，原理就是利用简答的if函数做逻辑分析。

例1.判断AB是否为0。

本题的解题逻辑为：首先判断A是否为0，它需要携带两个返回值，IF(A=0,返回值1,返回值2)；因此第一个返回值是在A=0的情况下判断B是否为0，这样第一次嵌套就是IF(B=0,“AB都等于0”,“A等于0 B不等于0”)；第二个返回值是在A不等于0的情况下判断B是否为0，第二次嵌套就是IF(B=0,“A不等于0 B等于0”,“AB都不等于0”)。
所以代码为=IF(I80=0,IF(J80=0,"AB都等于0","A等于0 B不等于0"),IF(J80=0,"A不等于0 B等于0","AB都不等于0"))I80指A，J80指B。

例2.判断每个月成绩是否达标，GMV大于10万且cpc总费用小于5千的为达标。

按照例1的逻辑，这一题就很简单了。

PS：本文来源于b站戴师兄的数据分析自学课程，这篇博客是我自己的学习笔记，供大家一起参考学习哦~所用的资料在https://pan.baidu.com/s/1-jOwaXWArtv8h21B0-uWGg#list/path=%2F&parentPath=%2F这里，是戴师兄免费分享的学习资料，提取码是wsra。