数据分析：常见的Excel 函数全部涵盖在这里了

数据分析：常见的Excel 函数全部涵盖在这里了

世界上的数据分析师分为两类，使用 Excel 的分析师，和其他分析师。

每一个数据新人的入门工具都离不开 Excel。因为 Excel 涵盖的功能足够多。

很多传统行业的数据分析师只要求掌握 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 中有同名函数。

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 中有同名函数。

Search

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

Text

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

关联匹配类

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

Lookup

=Lookup（查找的值，值所在的位置，返回相应位置的值）

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

Vlookup

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

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

Index

＝Index（查找的区域，区域内第几行，区域内第几列） 和 Match 组合，媲美 Vlookup，但是功能更强大。

Match

＝Match（查找指定的值，查找所在区域，查找方式的参数）

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

Row

返回单元格所在的行

Column

返回单元格所在的列

Offset

＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）

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

逻辑运算类

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

IF

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

if(and())的写法。

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

And

全部参数为 True，则返回 True，经常用于多条件判断。MySQL 中有同名函数，Python 中有同名函数。

Or

只要参数有一个 True，则返回 Ture，经常用于多条件判断。MySQL 中有同名函数，Python 中有同名函数。

IS 系列

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

计算统计类

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

Sum／Sumif／Sumifs

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

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

Sumproduct

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

MySQL 中有同名函数。Count／Countif／Countifs 统计满足条件的字符串个数

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

Max

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

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

Min

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

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

Rank

排序，返回指定值在引用区域的排名，重复值同一排名。SQL 中有近似函数 row_number() 。Rand／Randbetween

常用随机抽样，前者返回 0~1 之间的随机值，后者可以指定范围。

MySQL 中有同名函数。

Averagea

求平均值，也有 Averageaif，Averageaifs

MySQL 中有同名函数，python 有近似函数 mean。

Quartile

=Quartile（指定区域，分位参数）

计算四分位数，比如 1~100 的数字中，25 分位就是按从小到大排列，在 25%位置的数字，即 25。参数 0 代表最小值，参数 4 代表最大值，1~3 对应 25、50（中位数）、75 分位

Stdev

求标准差，统计型函数，后续数据分析再讲到

Substotal

=Substotal（引用区域，参数）

汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化，换言之，只要会了这个函数，上面的都可以抛弃掉了。

Int／Round

取整函数，int 向下取整，round 按小数位取数。

round(3.1415,2) =3.14 ; round(3.1415,1)=3.1

时间序列类

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

Year

返回日期中的年

MySQL 中有同名函数。

Month

返回日期中的月

MySQL 中有同名函数。

Weekday

=Weekday(指定时间，参数)

返回指定时间为一周中的第几天，参数为 1 代表从星期日开始算作第一天，参数为 2 代表从星期一开始算作第

一天（中西方差异）。我们中国用 2 为参数即可。

MySQL 中有同名函数。

Weeknum

=Weeknum(指定时间，参数)

返回一年中的第几个星期，后面的参数类同 weekday，意思是从周日算还是周一。MySQL 中有近似函数 week。

Day

返回日期中的日（第几号）

MySQL 中有同名函数。

Date

=Date（年，月，日）

时间转换函数，等于将 year()，month()，day()合并MySQL 中有近似函数 date_format。

Now

返回当前时间戳，动态函数MySQL 中有同名函数。Today

返回今天的日期，动态函数

MySQL 中有同名函数。

Datedif

=Datedif（开始日期，结束日期，参数）

日期计算函数，计算两日期的差。参数决定返回的是年还是月等。

MySQL 中有近似函数 DateDiff。