数据分析虽然是以业务为主,工具为辅,业务为目的,工具为手段,但巧妇难为无米之炊,只有掌握了基础的工具才能够进行数据分析。但工具千千万,Excel、SQL、Python/R、SPSS、SAS、PowerBI/Tableau,到底哪些才是数据分析师日常工作中必备的工具?哪些是没有必要花费时间精力的花架子?数据分析工具这个系列,我们就一起来看一下哪些是数据分析师真正需要熟练掌握的工具!
Excel是目前最基础、也是应用最广的数据分析工具,百万行级别以下最好用的数据分析工具,没有之一,不接受反驳!熟练使用Excel,能够解决数据分析中80%的问题,而且非常灵活、快速和高效,所以Excel作为数据分析师必备的基本技能,重要性不言而喻。
很多想入行数据分析的小伙伴学习Excel没有经过系统的指引与训练,而往往是来自于各种东拼西凑的知识,感觉自己很懂Excel,所以简历上就非常自信地写上了“精通Excel”这样不知天高地厚的字眼,面试一问起来才发现自己的纯真和无知。是的,当你还觉得Excel很low很简单的时候,说明你还没有被它震撼和教育过,越是精通Excel的大神越会觉得它的强大和自己的无知。
那么想入行数据分析的同学来说,怎么快速高效地掌握Excel这个数据分析的利器呢?根据笔者多年来的工作经验,作为数据分析师,提升Excel水平可以按照这样的学习路径:
1)基本操作包括数据的简单处理汇总、图表制作等,属于Excel基础知识,一般大家都能正常使用。
2)熟练掌握常用的函数后,你就可以做简单的数据统计、分析和数据可视化等工作了。
3)为了进一步从不同维度对关心的指标进行上卷、下钻分析,还需要非常熟练地掌握数据透视表,这也是Excel最为强大、使用最为频繁的功能。
4)为了实现复杂的业务分析,解决不同数据源、海量数据的分析问题,我们就需要掌握PowerQuery和PowerPivot,PowerQuery负责整合多重来源数据,并进行数据转换,PowerPivot对整合后的规范化数据进行高效率的透视分析,几百万上千万行数据均不在话下。
5)当然,这个时候可能还不够,因为平时还有很多任务是需要每天手动处理的,所以PowerBI横空出世了,PowerBI融合了PowerQuery和PowerPivot的功能,你可以利用PowerBI制作好你想要监控的核心报表,以后只要更新数据源,报表就能自动化生成,再也不用每天重复地复制粘贴做表格了!
02Excel常用函数
Excel之所以强大,很大一方面是因为Excel中有大量的函数可以实现各种各样的功能,精力有限,作为数据分析师,我们不需要也没有必要学习所有的函数,只要重点学习数据分析中常用的一些函数即可,我已经按照分类列举如下,并通过实际案例,讲解具体的使用方法。熟练掌握这些函数,80%以上的数据分析问题都可以轻松解决。
03关联匹配类函数
关联匹配-VLOOKUP
1)功能
在Excel中我们经常会有这样的场景,在数据A中记录了各个员工的绩效等级,但是并没有记录对应的年终奖,而在数据B中只记录了各个绩效等级对应的年终奖,我们想在数据A中增加年终奖这一列信息,这个时候VLOOKUP就排上用场了。
VLOOKUP是Excel第一大难关,也是Excel中使用频率较高,面试考查较为频繁的一个知识点,因为涉及的逻辑对新手较复杂,所以也是用来验证你简历上“熟练使用Excel”的重要依据。把它放在第一个来讲,是对Vlookup最起码的尊重。通俗的理解就是用某个值作为中间关联,找到另外一个值然后黏贴过来。如下图所示:
2)语法格式
=VLOOKUP(找谁?在哪找?返回的第几列?你是想精准地找还是粗略地找?),
这个函数有4个要素,对应如下:
①用谁找:一般是单个单元格的值。
②在哪找:一般是单元格区域。
③返回第几列:返回列数从查找区域算起。
④匹配类型:精确匹配用,0或FALSE,近似匹配用1或TRUE。
3)举例
如果上面还是不好理解,跟着下面这个视频一步步操作,你就能明白它的原理了。
但是有一种异常情况,就是我们有时候可能会找不到匹配的值,比如魏延同学今年表现太差,领导决定给他个F绩效,但是以前没有出现过这样的情况,也不知道F对应的年终奖是多少,所以就出现了#N/A的错误,如下图所示:
这个时候我们用IFERROR判断一下,如果报错了就给个处理的方案,这样#N/A的错误就能完美解决,公式如下:
关联匹配-INDEX+MATCH
1)功能
在Excel中MATCH函数可以返回指定内容所在的位置,而INDEX又可以根据指定位置查询到位置所对应的数据,两者结合使用,可以返回指定位置相关联的数据。而且INDEX+MATCH组合能够实现反向查找和双向查找,比VlOOKUP功能更强大更灵活。
2)语法格式
=INDEX(查找的区域,区域内第几行,区域内第几列)和Match组合
=MATCH(查找指定的值,查找所在区域,查找方式的参数)和VLOOKUP类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。
3)举例
1、反向查找
如下图所示,要求查找员工姓名为“张飞”的员工编号。
分析:
先利用MATCH函数根据产品名称在C列查找位置
=MATCH(B25,B17:B22,0)
再用INDEX函数根据查找到的位置从B列取值。完整的公式即为:
=INDEX(A17:A22,MATCH(B25,B17:B22,0))
2、双向查找
如下图所示,要求查找员工“张飞”在2018年的年终奖。
分析:
先用MATCH函数查找员工“张飞”在A列的位置
= MATCH(A43,$A$33:$A$39,0)
再用MATCH函数查找"2018年"在第一行中的位置
=MATCH(B43,$B$32:$F$32,0)
最后用INDEX根据行数和列数提取数值
=INDEX(B33:F39,MATCH(A43,$A$33:$A$39,0),MATCH(B43,$B$32:$F$32,0))
04清洗处理类函数
很多数据并不是直接拿来就能用的,在进行数据分析之前,我们需要对数据进行清洗和处理,主要包括数据格式转换、数据组合、截取等。虽然这个步骤耗费较多的时间精力,但如果能熟练使用一些函数,也能让我们事半功倍。
转化为文本-TEXT
功能
将数值/文本/日期转化为自己想要的文本格式。
语法格式
=TEXT(value,format_text)Value为原始数据。Format_text为希望转换成的文本格式。
举例
例如把C列的2020/1/1的日期格式转换成2020-01-01的文本格式。
合并单元格-CONCAT
功能
将多个单元格的内容进行合并。
语法格式
=CONCAT(单元格1,单元格2……)合并单元格中的内容,还有另一种合并方式是& 。"我"&"很"&"帅" = 我很帅。当需要合并的内容过多时,CONCAT的效率快也优雅。
举例
例如对A、B、D3列的内容进行合并。
替换字符-SUBSTITUTE
功能
对字符串中指定的内容文本进行替换。
语法格式
=SUBSTITUTE(需要替换的文本,旧文本,新文本,第N个旧文本)。
举例
例如将F列中的"A00"替换成“A-”。
截取字符串-LEFT/RIGHT/MID
功能
从不同的位置对字符串进行截取。
语法格式
=MID(指定字符串,开始位置,截取长度)。从字符串的指定位置,截取指定长度的字符。LEFT/RIGHT(指定字符串,截取长度)。LEFT为从最左端开始截取指定长度,RIGHT为从最右端开始截取指定长度。
举例
例如分别截取F列中的左4、右3、中间2个字符。
定位文本位置-FIND
功能
对要查找的文本进行定位,以确定其位置。
语法格式
=Find(要查找的字符串,指定字符串,第几次出现)查找指定的字符串在要查找的字符串中出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取。
举例
例如查找A列中字符“A”出现的位置。
05时间日期类函数
专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比下面列举的还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。
获取年月日-YEAR/MONTH/DAY
功能
返回日期中的年/月/日。
语法格式
=YEAR(日期)/MONTH(日期)/DAY(日期)。
举例
例如求B列中日期对应的年/月/日。
获取第几周-WEEKNUM
功能
返回对应日期是一年中的第几个星期。
语法格式
=WEEKNUM(指定时间)。
举例
例如求B列中日期对应是当年的第几周。
获取周几-WEEKDAY
功能
返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异),我们中国用2为参数即可。
语法格式
=WEEKDAY(指定时间,参数)。
举例
例如求B列中日期对应是周几。
06统计计算类函数
常用的基础计算、分析、统计函数,以描述性统计为准。主要包括:基础聚合函数SUM/COUNT/AVERAGE/MIN/MAX等、以及单条件汇总SUMIF/COUNTIF等,多条件汇总SUMIFS/COUNTIFS等、排名函数RANK、百分位函数PERCENTILE/QUARTILE、标准差STDEV等,具体用法下面展开。
基本聚合函数-SUM/COUNT/...
功能
对所选单元格进行SUM(求和)/COUNT(计数)/AVERAGE(平均值)/MIN(最小值)/MAX(最大值)。
语法格式
=SUM/COUNT/AVERAGE/MIN/MAX(range)。
举例
例如求B列中日期对应的年/月/日。
单条件汇总-(SUMIF/COUNTIF/...)
功能
对区域中符合指定条件的数值求和/计数/求平均值/...。
语法格式
=SUMIF(range, criteria),range为计算区域, criteria 以数字、表达式、单元格参考、文本或函数的形式来定义将选择哪些单元格。例如,criteria可以表示为 32、“>32”、B5、“3?”等。
举例
例如对符合条件的员工的年收入进行计算。
多条件汇总(SUMIFS/COUNTIFS/...)
功能
返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异),我们中国用2为参数即可。
语法格式
除COUNTIFS外,所有xIFS函数都使用相同的语法。以下是SUMIFS和COUNTIFS的语法示例:
SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)
COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],...)。
举例
例如对符合条件的员工的年收入进行计算。
排名函数RANK
功能
对一组数据进行排序,返回指定值在引用区域的排名,重复值同一排名。
语法格式
=RANK(value,range)。其中value是需要确定位次的数据,range表示数据范围,返回的是排名。
举例
例如对员工的年收入进行排序。
百分比函数-(QUARTILE/PRECENTILE)
功能
计算一组数据的四分位值/百分位值。
语法格式
=QUARTILE(range,n)计算四分位数,n=0~4,其中0代表最小值,4代表最大值,1~3分别对应1/4、1/2(中位数)、3/4分位数。
=PERCENTILE(range,k) 是返回数组的k百分点值的函数,功能与QUARTILE类似,区别是QUARTILE只能计算四分位值,而PERCENTILE可以计算任意百分位的值,功能比QUARTILE更灵活更强大。
举例
例如计算员工年收入的1/4、1/2(中位数)、3/4分位数。
标准差STDEV与变异系数
功能
求一组数据的标准差,一般配合平均值使用,计算变异系数,其中变异系数=标准差/平均值,用于判断一组数据的分散程度,变异系数越大,数据越分散,反之,数据越集中。
语法格式
=STDEV(range)。
其中变异系数=标注差/平均值=STDEV(range)/AVERAGE(range)。
举例
例如计算各员工年收入的标准差和变异系数,看大家的年收入是否差异过大。
以上就是数据分析工具—Excel常用函数部分的内容,部分数据分析工具请翻看历史文章,更多数据分析工具的文章持续更新中,敬请期待,如果觉得不错,也欢迎分享、点赞和点在看哈~