数据分析工具:数据分析必知必会的Excel函数

释放双眼,带上耳机,听听看~!

掌握这些常用Excel函数,轻松解决80%的数据分析问题!

数据分析工具:数据分析必知必会的Excel函数

01、写在前面

为什么要写【数据分析工具】这个系列文章?还是回到一个最根本的问题上:数据分析到底是干什么的?

数据分析虽然是以业务为主,工具为辅,业务为目的,工具为手段,但巧妇难为无米之炊,只有掌握了基础的工具才能够进行数据分析。但工具千千万,Excel、SQL、Python/R、SPSS、SAS、PowerBI/Tableau,到底哪些才是数据分析师日常工作中必备的工具?哪些是没有必要花费时间精力的花架子?数据分析工具这个系列,我们就一起来看一下哪些是数据分析师真正需要熟练掌握的工具!

Excel是目前最基础、也是应用最广的数据分析工具,百万行级别以下最好用的数据分析工具,没有之一,不接受反驳!熟练使用Excel,能够解决数据分析中80%的问题,而且非常灵活、快速和高效,所以Excel作为数据分析师必备的基本技能,重要性不言而喻。

很多想入行数据分析的小伙伴学习Excel没有经过系统的指引与训练,而往往是来自于各种东拼西凑的知识,感觉自己很懂Excel,所以简历上就非常自信地写上了“精通Excel”这样不知天高地厚的字眼,面试一问起来才发现自己的纯真和无知。是的,当你还觉得Excel很low很简单的时候,说明你还没有被它震撼和教育过,越是精通Excel的大神越会觉得它的强大和自己的无知。

那么想入行数据分析的同学来说,怎么快速高效地掌握Excel这个数据分析的利器呢?根据笔者多年来的工作经验,作为数据分析师,提升Excel水平可以按照这样的学习路径:

数据分析工具:数据分析必知必会的Excel函数

1)基本操作包括数据的简单处理汇总、图表制作等,属于Excel基础知识,一般大家都能正常使用。

2)熟练掌握常用的函数后,你就可以做简单的数据统计、分析和数据可视化等工作了。

3)为了进一步从不同维度对关心的指标进行上卷、下钻分析,还需要非常熟练地掌握数据透视表,这也是Excel最为强大、使用最为频繁的功能。

4)为了实现复杂的业务分析,解决不同数据源、海量数据的分析问题,我们就需要掌握PowerQuery和PowerPivot,PowerQuery负责整合多重来源数据,并进行数据转换,PowerPivot对整合后的规范化数据进行高效率的透视分析,几百万上千万行数据均不在话下。

5)当然,这个时候可能还不够,因为平时还有很多任务是需要每天手动处理的,所以PowerBI横空出世了,PowerBI融合了PowerQuery和PowerPivot的功能,你可以利用PowerBI制作好你想要监控的核心报表,以后只要更新数据源,报表就能自动化生成,再也不用每天重复地复制粘贴做表格了!

前面已经总结了一些数据分析工具,有兴趣的可以翻看之前的文章,包括:【数据分析工具】手把手教你制作Excel仪表盘。

02、Excel常用函数

Excel之所以强大,很大一方面是因为Excel中有大量的函数可以实现各种各样的功能,精力有限,作为数据分析师,我们不需要也没有必要学习所有的函数,只要重点学习数据分析中常用的一些函数即可,我已经按照分类列举如下,并通过实际案例,讲解具体的使用方法。熟练掌握这些函数,80%以上的数据分析问题都可以轻松解决。

数据分析工具:数据分析必知必会的Excel函数

03、关联匹配类函数

关联匹配-VLOOKUP

1)功能

在Excel中我们经常会有这样的场景,在数据A中记录了各个员工的绩效等级,但是并没有记录对应的年终奖,而在数据B中只记录了各个绩效等级对应的年终奖,我们想在数据A中增加年终奖这一列信息,这个时候VLOOKUP就排上用场了。

数据分析工具:数据分析必知必会的Excel函数

VLOOKUP是Excel第一大难关,也是Excel中使用频率较高,面试考查较为频繁的一个知识点,因为涉及的逻辑对新手较复杂,所以也是用来验证你简历上“熟练使用Excel”的重要依据。把它放在第一个来讲,是对Vlookup最起码的尊重。通俗的理解就是用某个值作为中间关联,找到另外一个值然后黏贴过来。如下图所示:

数据分析工具:数据分析必知必会的Excel函数

2)语法格式

=VLOOKUP(找谁?在哪找?返回的第几列?你是想精准地找还是粗略地找?),

这个函数有4个要素,对应如下:

①用谁找:一般是单个单元格的值。

②在哪找:一般是单元格区域。

③返回第几列:返回列数从查找区域算起。

④匹配类型:精确匹配用,0或FALSE,近似匹配用1或TRUE。

3)举例

如果上面还是不好理解,跟着下面这个视频一步步操作,你就能明白它的原理了。

但是有一种异常情况,就是我们有时候可能会找不到匹配的值,比如魏延同学今年表现太差,领导决定给他个F绩效,但是以前没有出现过这样的情况,也不知道F对应的年终奖是多少,所以就出现了#N/A的错误,如下图所示:

数据分析工具:数据分析必知必会的Excel函数

这个时候我们用IFERROR判断一下,如果报错了就给个处理的方案,这样#N/A的错误就能完美解决,公式如下:

数据分析工具:数据分析必知必会的Excel函数

关联匹配-INDEX+MATCH

1)功能

在Excel中MATCH函数可以返回指定内容所在的位置,而INDEX又可以根据指定位置查询到位置所对应的数据,两者结合使用,可以返回指定位置相关联的数据。而且INDEX+MATCH组合能够实现反向查找和双向查找,比VlOOKUP功能更强大更灵活。

2)语法格式

=INDEX(查找的区域,区域内第几行,区域内第几列)和Match组合

=MATCH(查找指定的值,查找所在区域,查找方式的参数)和VLOOKUP类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。

3)举例

1、反向查找

如下图所示,要求查找员工姓名为“张飞”的员工编号。

数据分析工具:数据分析必知必会的Excel函数

分析:

先利用MATCH函数根据产品名称在C列查找位置

=MATCH(B25,B17:B22,0)

再用INDEX函数根据查找到的位置从B列取值。完整的公式即为:

=INDEX(A17:A22,MATCH(B25,B17:B22,0))

2、双向查找

如下图所示,要求查找员工“张飞”在2018年的年终奖。

数据分析工具:数据分析必知必会的Excel函数

分析:

先用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的文本格式。

数据分析工具:数据分析必知必会的Excel函数

合并单元格-CONCAT

功能

将多个单元格的内容进行合并。

语法格式

=CONCAT(单元格1,单元格2……)合并单元格中的内容,还有另一种合并方式是& 。”我”&”很”&”帅” = 我很帅。当需要合并的内容过多时,CONCAT的效率快也优雅。

举例

例如对A、B、D3列的内容进行合并。

数据分析工具:数据分析必知必会的Excel函数

替换字符-SUBSTITUTE

功能

对字符串中指定的内容文本进行替换。

语法格式

=SUBSTITUTE(需要替换的文本,旧文本,新文本,第N个旧文本)。

举例

例如将F列中的”A00″替换成“A-”。

数据分析工具:数据分析必知必会的Excel函数

截取字符串-LEFT/RIGHT/MID

功能

从不同的位置对字符串进行截取。

语法格式

=MID(指定字符串,开始位置,截取长度)。从字符串的指定位置,截取指定长度的字符。LEFT/RIGHT(指定字符串,截取长度)。LEFT为从最左端开始截取指定长度,RIGHT为从最右端开始截取指定长度。

举例

例如分别截取F列中的左4、右3、中间2个字符。

数据分析工具:数据分析必知必会的Excel函数

定位文本位置-FIND

功能

对要查找的文本进行定位,以确定其位置。

语法格式

=Find(要查找的字符串,指定字符串,第几次出现)查找指定的字符串在要查找的字符串中出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取。

举例

例如查找A列中字符“A”出现的位置。

数据分析工具:数据分析必知必会的Excel函数

05、时间日期类函数

专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比下面列举的还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。

获取年月日-YEAR/MONTH/DAY

功能

返回日期中的年/月/日。

语法格式

=YEAR(日期)/MONTH(日期)/DAY(日期)。

举例

例如求B列中日期对应的年/月/日。

数据分析工具:数据分析必知必会的Excel函数

获取第几周-WEEKNUM

功能

返回对应日期是一年中的第几个星期。

语法格式

=WEEKNUM(指定时间)。

举例

例如求B列中日期对应是当年的第几周。

数据分析工具:数据分析必知必会的Excel函数

获取周几-WEEKDAY

功能

返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异),我们中国用2为参数即可。

语法格式

=WEEKDAY(指定时间,参数)。

举例

例如求B列中日期对应是周几。

数据分析工具:数据分析必知必会的Excel函数

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列中日期对应的年/月/日。

数据分析工具:数据分析必知必会的Excel函数

单条件汇总-(SUMIF/COUNTIF/…)

功能

对区域中符合指定条件的数值求和/计数/求平均值/…。

语法格式

=SUMIF(range, criteria),range为计算区域, criteria 以数字、表达式、单元格参考、文本或函数的形式来定义将选择哪些单元格。例如,criteria可以表示为 32、“>32”、B5、“3?”等。

举例

例如对符合条件的员工的年收入进行计算。

数据分析工具:数据分析必知必会的Excel函数

多条件汇总(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],…)。

举例

例如对符合条件的员工的年收入进行计算。

数据分析工具:数据分析必知必会的Excel函数

排名函数RANK

功能

对一组数据进行排序,返回指定值在引用区域的排名,重复值同一排名。

语法格式

=RANK(value,range)。其中value是需要确定位次的数据,range表示数据范围,返回的是排名。

举例

例如对员工的年收入进行排序。

数据分析工具:数据分析必知必会的Excel函数

百分比函数-(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分位数。

数据分析工具:数据分析必知必会的Excel函数

标准差STDEV与变异系数

功能

求一组数据的标准差,一般配合平均值使用,计算变异系数,其中变异系数=标准差/平均值,用于判断一组数据的分散程度,变异系数越大,数据越分散,反之,数据越集中。

语法格式

=STDEV(range)。

其中变异系数=标注差/平均值=STDEV(range)/AVERAGE(range)。

举例

例如计算各员工年收入的标准差和变异系数,看大家的年收入是否差异过大。

数据分析工具:数据分析必知必会的Excel函数

以上就是数据分析工具—Excel常用函数部分的内容,部分数据分析工具请翻看历史文章,更多数据分析工具的文章持续更新中,敬请期待,如果觉得不错,也欢迎分享、点赞和点在看哈~

给TA买糖
共{{data.count}}人
人已赞赏
数据分析

配置项改动后数据应该如何变化

2021-6-8 10:43:54

数据分析

数据指标是什么?必知必会的数据指标类型都在这了

2021-6-10 9:11:40

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索