由于本人技术力不足,开发之路对我来说过于艰难,但是通过读研期间,我发现自己对于数据的敏感度较高,也喜欢用数据来说话,加之有一定的学习基础和技术在,特此整理一下个人跟戴师兄学习的数据分析过程和个人理解思考。目前进度:
- Excel大厂周报制作(基本操作)
- tableau仪表盘制作(0基础)
- SQL(有基础)
- Python(有基础)
- 个人加课spss(读研时用的数分工具,后续复习一下)
所使用数据集均来自于这里
学习目标1:
Excel大厂周报制作
PART01:
1.先看一下最终呈现的效果图:
图中的所有数据都可以跟随右上角的筛选器【全部、美团、饿了么】改变。
2.Excel基本计算
既然是学习数据分析,那么首先肯定要熟悉自己所获得的数据集,观察自己得到的数据集有哪些属性,是又哪些部分组成的,理解每一个属性和他的的作用,基本知晓哪些数据是数值型数据,哪些数据是类型数据,观察有哪些属性可以通过某种计算得到一个有意义的新的值。戴师兄所提供的数据集是已经经过处理好的数据(可以下载数据集看一下,如果想学习数据分析,一定是要非常清楚数据集的内容的,不然后面的分析细思下来可能会想不明白,数据分析的一切以数据为基准),没有脏数据,如果在真实的业务环境下,数据获取是一方面,另外还需要对数据进行清洗,剔除掉会对分析结果有误导的脏数据;也有可能数据有所缺漏,此时又需要判断是否以及如何去填充缺失数据。如果有的数据集过大,那可能还需要对数据分析工具做出选择,毕竟Excel只能处理有限的数据,行最大容纳10148576,列最大容纳到XFD,理论上如此,但是实际操作过程中,Excel不一定能够没有任何错误并且能够加载出来这么大的文件。(我使用的Excel版本为2019家庭版。)
Excel的基本计算大概为以下几种:
- sum求和
- sumif单条件求和
- sumifs多条件求和
- subtotal
- if函数
- vlookup函数
- index和match函数
- 数据透视表
PART02:
1.SUM函数
sum函数表达为SUM(num1,num2,···),不仅可以计算单个属性的和,也可以计算多个属性相加后的和,比如图二中,求1-8月GMV总和,输入“=sum(…)”即可;
如图三所示,求一月和八月的GMV,输入“=sum(… , …)”,用逗号分隔开num1和num2。
一个简单粗暴直接计算所有月份的和,一个可以有选择性的计算你想得到的某两个月或者n个月的和。
2.SUMIF函数
sumif单条件求和,公式如图四所示。举个例子,要求2020/7/1的当天的GMV之和,首先确定条件,条件是日期的范围,其次,选中你要求和的那一天日期即具体的条件,最后确定求和的范围也就是GMV。以上为个人理解,我觉得这样可以帮助我更好的理解并记住这个函数。那么,日期在原数据集中位于A列,直接选中即可,2020/07/01位于B15单元格,选中,GMV求和则选中原数据集中所在的GMV列也就是J列,一通操作后即可得到这一天的GMV总和。
3.SUMIFS函数
sumifs函数将求和项放在了第一个参数位置,这是与sumif的区别,一般也可以直接使用sumifs函数代替sumif函数,直接采用sumifs函数依然可以达到sumif函数的效果,并且如果后续需要对该求和项进行修改的时候更快捷。
如图五所示,要求2020.7.1 美团的GMV,显然不止一个条件,不仅需要日期还需要平台(平台分为美团与饿了么),第一个参数sum_range选中原表中的GMV列,第一个条件范围选中日期范围,条件选中2020.7.1这个单元格即B30,第二个条件范围选择平台范围,手动输入我们所求的条件,注意需要加上英文双引号,如果在实际业务场景中还有其他条件可以继续增加。
如图五所见,表格内不仅要求GMV,还要求日环比、日同比、月环比,这就涉及到了一些简单的运算,不再只是单单使用一个函数了。
日环比运算公式为:当天的GMV/前一天的GMV -1
日同比运算公式为:当天的GMV/前一个月这一天的GMV -1
月环比运算公式为:当月的GMV/前一个的GMV -1
在进行运算之前,首先我们需要知道当天的GMV我们已经得到了,那么需要求的就是前一天的GMV和前一个月这一天的GMV,跟随戴师兄的课程,先了解一下DATE函数。
如图六所示,DATE(year(),month(),day()),在这里插一句,日期在Excel里是可以直接计算的,比如今天是2024.4.8,那么输入在Excel里,表达为日期:
可以看得出来,2024/4/7是通过计算得到的,2024/4/8-1即可,计算前一天或者后一天或者几天甚至一个月一年都可以利用date函数得到。不过这也涉及到了一个问题,当你想求一个月的最后一天,或者涉及到2月份这个比较特殊的月份的时候,直接计算可能会出现一些bug,据戴师兄所说,这也是各个大厂在面试时经常会问到的一个算法。
DATE(year(),month(),day())这个函数可以直接将day替换掉,如果写作DATE(year(),month(),1),那么这个日期就是某年某月的1号,这个没有问题,但是如果要求最后一天,肯定不可以直接将day替换为30或者31,这样计算就会出错,大家可以自己试一试,会有一些奇妙的“智能”或者说“智障”的bug。其实一个月的最后一天,换个思路想也就是后一个月的第一天的前一天,也就是month()+1,day替换为1后,再DATE整体 -1 即可,也就是=DATE(YEAR(xx),MONTH(xx)+1,1)-1。
那么当月的GMV可以直接从数据集里获取,前一个月的GMV要如何得到呢?前一个月也就是DATE(year(),month(),1)到DATE(YEAR(xx),MONTH(xx)+1,1)-1,通过简单的运算,如要计算2020/07的月环比,可以:
=当月的GMV/sumifs(GMV列,平台列,“美团”,日期列,“>=”& DATE(year(),month()-1,1),日期列, “<=”& DATE(YEAR(),MONTH(),1)-1) -1
至此,已经可以利用sumifs和date函数计算出日环比、日同比、月环比了。
待续。。。