Excel 数据处理5大常用函数

Excel函数主要可以分为几下几大类，接下来会每一类展开为大家讲解。

一. 文本清洗类：主要是文本、格式以及脏数据的清洗和转换

1. Trim：清除字符串空格

=TRIM(指定字符串)

说明：此方法只能删除字符串首尾的空格，而不能删除字符串中间的空格！因为英文单词之间的空格是必须的，Excel不会去除这种空格。

2. Concat，连接单元格内的内容，还有另一种合并方式是"&"

=CONCAT(单元格1，单元格2……)

说明：concat能够连接的参数最多只有30个，而&则没有限制。concat只有2016版才有，前身叫concatenate

3. Left / Right / Mid：截取左边/中间/右边字符串

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

4. 长度计算：Len/LenB

=LEN(字符串)/LENB(字符串)

注：返回字符串的长度，在Len中，中文计算为一个，在LenB中，中文计算为两个（B是Byte字节的意思）。

5. Replace/Substitute：替换单元格中内容

=REPLACE（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）

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

6. Find/Search：查找文本在单元格中的位置

=FIND（要查找字符，指定字符串，第几个字符），可与Left／Right／Mid结合能完成简单的文本提取

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

7. Text：格式转换，可以让单元格数值按照指定的格式显示

=TEXT(单元格)，如固定显示4位数，不足用0补齐

8. Upper/Lower：文本转大写/转小写函数

=UPPER(字符串)/LOWER(字符串)

9. Proper：首字母大写

=PROPER(字符串)

二. 关联匹配类：在进行多表关联或者行列比对时用到的函数，越复杂的表用得越多 1. Vlookup：查找首列满足条件的元素 =VLOOKUP(查找的值，哪里找，找哪个位置的值，精准匹配0/模糊匹配1)

2. Lookup：查找满足条件的元素

=LOOKUP（查找的值，值所在的位置，返回相应位置的值）功能性和Vlookup一样，但是引申有数组匹配和二分法，还有Hlookup，感兴趣的小伙伴可以私信或自行百度

3. Index：返回值或值的引用

＝INDEX（查找的区域，区域内第几行，区域内第几列）

和Match组合，媲美Vlookup，但是功能更强大（如下）

4. Match：返回指定内容在指定区域的位置

＝MATCH（查找指定的值，查找所在区域，匹配类型）和Lookup类似，但是可以按照指定方式查找，匹配类型有三个值，0表示精确查找，1表示小于，-1表示大于，返回值所在的位置

5. Row：返回单元格再第几行

=ROW(单元格)

6. Column：返回单元格再第几列

=COLUMN(单元格)

7. Offset单元格偏移

＝OFFSET（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）建立坐标系，以坐标系为原点，返回距离原点的值或者区域。

正数代表向下或向左，负数则相反。

8. Hyperlink：把单元格内容设为超链接

=HYPERLINK（单元格）

三. 逻辑运算类：逻辑运算返回的均是布尔类型，True和False 1. IF：如果但是

=IF(条件, true时返回值, false返回值)

2. And / Or：并/或

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

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

3. IS系列

常用判断检验，返回的都是布尔数值True和False。

常用ISERR，ISERROR，ISNA，ISTEXT，可以和IF嵌套使用。

四. 计算统计类：以描述性统计为准 1. Sum／Sumif／Sumifs 统计满足条件的单元格总和

=SUMIF（条件区域，求和条件，实际求和区域）

=SUMIFS（实际求和区域，第一个条件区域，第一个对应的求和条件，第二个条件区域，第二个对应的求和条件，第N个条件区域，第N个对应的求和条件）

2. Sumproduct：两列相乘求和

例如果有两列数据：销量和单价，现在要求销售总价，用sumproduct是最方便的

3. Max/Min：返回最大最小值

=MAX(指定区域)，

=MIN(指定区域)

4. Average：求平均值，也有Averageif，Averageifs

=AVERAGEIF（条件区，条件，平均值区域）

=AVERAGEIFS(求平均值范围, 条件范围1, 条件1, [条件范围2, 条件2], ...)

Averageif / Averageifs使用方法较多，后续会出详细教程～

5. Count／Countif／Countifs 统计满足条件的字符串个数

=COUNTIF（条件区域，条件）

=COUNTIFS(条件匹配查询区域1，条件1，条件匹配查询区域2，条件2，以此类推......）

Countif／Countifs使用方法较多，后续会出详细教程～

6. Rank：返回一列数字的数字排位，重复值同一排位

=RANK(要排序的数字, 数字列表, [排序方式])，排序方式为0或省略，降序，否则升序

7. Rand／Randbetween：返回随机值

=RAND() =RANDBETWEEN(值1，值2)

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

8. Quartile：计算四分位数

=Quartile（指定区域，分位参数）参数取值0-4，0代表最小值，4代表最大值，1~3对应25分位数、50（中位数）、75分位数。四分位数不明白的可以百度

9. Stdev：求标准差，统计型函数

=STDEV(序列)

10. Subtotal：强大的汇总型函数

=Subtotal（引用区域，参数）将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化，只要会了这个函数，上面的都可以抛弃掉了。

subtotal内置函数

11. Int：取整函数，int向下取整

INT(4.5)=4

INT(-4.5)=-5

12. Round：四舍五入，可以按按小数位取数

ROUND(3.1415,2) =3.14

ROUND(3.1415,1)=3.1

五. 时间序列类 专门用于处理时间格式以及转换，时间序列在金融、财务等数据分析中占有较大比重 1. Year：返回日期中的年

=YEAR(指定日期)

2. Month：返回日期中的月

=MONTH(指定日期)

3. Day：返回日期中的日（第几号）

=DAY(指定日期)

4. Weekday：返回指定时间为一周中的第几天

=WEEKDAY(指定日期，参数)参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天，看公司使用喜好。

5. Weeknum：返回一年中的第几个星期

=WEEKNUM(指定日期，参数)后面的参数类同weekday，意思是从周日算还是周一。

6. Date：时间转换函数，等于将year()，month()，day()合并

=Date（年，月，日）

7. Now：返回当前时间戳，动态函数

=NOW()

8. Today：返回今天的日期，动态函数

=TODAY()

9. Datedif：计算两日期的差

=Datedif（开始日期，结束日期，参数）参数决定返回的是年还是月等

总结～

这里为大家讲解了非常多函数，大家也不需要把这些都记住，了解了Excel有这个函数功能，具体如何使用函数如果忘记了可以回过来再看我们的内容，或学会使用搜索引擎，网上也会有详细的解释。

End.

作者：土豆爱数学 来源：知乎