返回首页
当前位置: 主页 > 精通Office > Excel教程 >

Excel数据分析函数 常见的Excel函数大全

时间:2016-12-25 20:42来源:知行网www.zhixing123.cn 编辑:麦田守望者

很多传统行业的数据分析师只要求掌握Excel即可,会SPSS/SAS是加分项。即使在挖掘满街走,Python不如狗的互联网数据分析界,Excel也是不可替代的。

Excel有很多强大的函数,这篇文章主要介绍各种函数的用途。实战会后续文章讲解。

函数可以被我们想象成一个盒子,专门负责将输入转换成输出,不同的函数对应不同的输出。

=Vlookup( lookup_value ,table_array,col_index_num,[range_lookup] )

上文的Vlookup就是一个经典函数。函数中包含参数,括号里的部分都是参数。我们可以把参数想象成盒子上的开关。vlookup就有四个开关,不同开关组合决定了函数的输入和输出。

=Vlookup( 参数1,参数2,参数3,参数4)

复杂的原理不需要了解。这篇文章是常用函数汇总。甚至你不需要特别记忆怎么使用函数,应用Excel函数最重要的能力是学会搜索。因为绝大部分函数网上已经有相应的解释,图文结合,非常详尽。

学会将遇到的问题转换成搜索语句,在我还是新人时并不会vlookup,我遇到的第一个问题就是关联多张表的数据,我在网上搜索:excel怎么匹配多张表的数据。于是就学会了。这里推荐使用百度,因为前三行的结果基本是百度经验,对新人学习很友好。(后续图片均引用自百度经验)

在理解函数的基础上,我会适当引入高层次的内容,SQL和Python(内建函数)。将其和Excel结合学习,如果大家吃透了Excel的函数,那么后续学习会轻松不少。

——————

清洗处理类

主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。数据越多,这个步骤花费的时间越长。

Trim

清除掉字符串两边的空格。

MySQL有同名函数,Python有近似函数strip。

Concatenate

=Concatenate(单元格1,单元格2……)

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

MySQL有近似函数concat。

Replace

=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)

替换掉单元格的字符串,清洗使用较多。

MySQL中有同名函数,Python中有同名函数。

Excel数据分析:常见的Excel函数大全

Substitute

和replace接近,区别是替换为全局替换,没有起始位置的概念

Left/Right/Mid

=Mid(指定字符串,开始位置,截取长度)

截取字符串中的字符。Left/Right(指定字符串,截取长度)。left为从左,right为从右,mid如上文示意。

MySQL中有同名函数。

Len/Lenb

返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。
MySQL中有同名函数,Python中有同名函数。

Find

=Find(要查找字符,指定字符串,第几个字符)

查找某字符串出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取
MySQL中有近似函数 find_in_set,Python中有同名函数。

Excel数据分析:常见的Excel函数大全

Search

和Find类似,区别是Search大小写不敏感,但支持*通配符

Text

将数值转化为指定的文本格式,可以和时间序列函数一起看

关联匹配类

在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。多说一句,良好的表习惯可以减少这类函数的使用。

Lookup

=Lookup(查找的值,值所在的位置,返回相应位置的值)

最被忽略的函数,功能性和Vlookup一样,但是引申有数组匹配和二分法。

Vlookup

=Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)

Excel第一大难关,因为涉及的逻辑对新手较复杂,通俗的理解是查找到某个值然后黏贴过来。

Excel数据分析:常见的Excel函数大全

Index

=Index(查找的区域,区域内第几行,区域内第几列)

和Match组合,媲美Vlookup,但是功能更强大。

Excel数据分析:常见的Excel函数大全

Match

=Match(查找指定的值,查找所在区域,查找方式的参数)

和Lookup类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。

Excel数据分析:常见的Excel函数大全

Row

返回单元格所在的行

Column

返回单元格所在的列

Offset

=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)

建立坐标系,以坐标系为原点,返回距离原点的值或者区域。正数代表向下或向左,负数则相反。

逻辑运算类

数据分析中不得不用到逻辑运算,逻辑运算返回的均是布尔类型,True和False。很多复杂的数据分析会牵扯到较多的逻辑运算

IF

经典的如果但是,在后期的Python中,也会经常用到,当然会有许多更优雅的写法。也有ifs用法,取代if(and)的写法。

And

全部参数为True,则返回True,经常用于多条件判断。

Or

只要参数有一个True,则返回Ture,经常用于多条件判断。

IS系列

常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。

计算统计类

常用的基础计算、分析、统计函数,以描述性统计为准。具体含义在后续的统计章节再展开。

Sum/Sumif/Sumifs

统计满足条件的单元格总和,SQL有中同名函数。

Sumproduct

统计总和相关,如果有两列数据销量和单价,现在要求卖出增加,用sumproduct是最方便的。

MySQL中有同名函数。

Count/Countif/Countifs

统计满足条件的字符串个数

Max

返回数组或引用区域的最大值

Min

返回数组或引用区域的最小值

顶一下
(1)
100%
踩一下
(0)
0%
标签(Tag):EXCEL excel2013 Excel数据分析 Excel函数大全
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片