数据分析必知必会的数据Excel函数

作者： 大数据分析与运营知识星球 2021-06-09 11:06:00大数据 数据分析 数据分析虽然是以业务为主，工具为辅，分析业务为目的必知必，工具为手段，函数但巧妇难为无米之炊，数据只有掌握了基础的分析工具才能够进行数据分析。

[[404616]]

01写在前面

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

数据分析虽然是以业务为主，工具为辅，函数业务为目的数据，工具为手段，分析但巧妇难为无米之炊，必知必只有掌握了基础的函数工具才能够进行数据分析。但工具千千万，数据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常用函数部分的内容，部分数据分析工具请翻看历史文章，更多数据分析工具的文章持续更新中，敬请期待，如果觉得不错，也欢迎分享、点赞和点在看哈~

 

责任编辑：武晓燕 来源： 大数据分析与运营知识星球 数据分析Excel

(责任编辑：娱乐)