Excel是数据分析师最常用的一种工具,可以搞定绝大多数数据分析工作,所以,对于数据分析初学者来说,最好先从Excel函数开始学习,通过Excel函数感受Excel的强大与神奇。
关于Excel函数,可以分为5大类。
- 数据清洗类
- 关联匹配类
- 逻辑运算类
- 计算统计类
- 时间序列类
本文将会讲解第1类:数据清洗类,在开始讲解函数之前,需要先了解以下一些关于Excel的基础知识。
1、我们可以在 Excel 中进行加、减、乘、除、幂等运算,无需使用任何函数。只需使用基本运算符:+(加)、-(减)、*(乘)、/(除)、^(幂)。
2、Excel是一个二维表格,由行、列组成,行采用数字编号,列采用字母编号。共有一百多万行和 16,000 多列
3、公式可包含常数、运算符及单元格引用。
![af8876df4b0c58f8daff51bc68952167.png](https://i-blog.csdnimg.cn/blog_migrate/dd200b111fff93d495d760c98991dab4.jpeg)
Excel基本运算
注意:所有公式均以等号开头。
在了解了以上基础知识后,就可以正式开始Excel函数的学习了。
1、TRIM
功能:去除单元格两端的空格。
用法:=TRIM(text)
例如,单元格B2前后有空格(红色框线所示),通过trim函数作用后,空格被去除了。
![f80dd33692189153d6fc8528c35219c2.png](https://i-blog.csdnimg.cn/blog_migrate/23f5a53d31fd09059793d6219dd13031.jpeg)
trim用法示例
说明:TRIM公式并不是去除所有空格,只是去除单元格前后的空格。
2、&符号
功能:字符串拼接
在Excel中,有一个特殊符号很有用,&(读作and连接符),可以连接字符串及单元格中的内容。
例如,将单元格A1和B1进行连接,直接使用公式:=A1&B1,即可得到结果。
![5452a3dd45669cc22cb38502eb9ea6fc.png](https://i-blog.csdnimg.cn/blog_migrate/ed9af7047cce3e7dd9a7d7b2aea0cf12.jpeg)
&连接符用法示例
3、CONCAT
功能:连接单元格的内容。
用法:= CONCAT(text1, [text2],…)
例如,通过CONCAT将单元格A1和B1进行连接,如下图所示。
![bac2e3d0f1f75b5489a2bb27f4e02af9.png](https://i-blog.csdnimg.cn/blog_migrate/3d9d7470169d22577b42eb30134719fb.jpeg)
concat用法示例
说明:老版本公式为CONCATENATE。
4、MID
功能:提取字符串中间的字符串
用法:= MID(text, start_num, num_chars)
参数说明:
- 第1个参数text:提取的目标字符串
- 第2个参数start_num:从第一个字符开始提取
- 第3个参数num_chars:提取几个字符
例如,从身份证号中提取出生年月(图中标红的部分),可以通过MID函数实现,提取目标为A2单元格,从第7个字符开始提取,提取个数为8。
![e7e2b62a69458d1d53fafebc0acd6421.png](https://i-blog.csdnimg.cn/blog_migrate/f7d01f75b1dd7bb69e2a76314f5525a6.jpeg)
mid函数用法示例
5、LEFT
功能:提取字符串左边的字符串
用法:= LEFT(text, [num_chars])
参数说明:
- 第1个参数text:要提取的目标字符串
- 第2个参数 [num_chars](可缺省):提取几个字符。
说明:用中括号括起来的参数表示该参数可以不写,即缺省。
例如,通过LEFT函数从前面提取出来的年月日中提取年份,从左边开始,提取4个字符,如下图所示。
![46a9e6d37a1cada33d61d6f976ce3138.png](https://i-blog.csdnimg.cn/blog_migrate/e98abdea33d4e98c257353253a5296f5.jpeg)
left函数用法示例
6、RIGHT
功能:提取字符串右边的字符串
用法:=RIGHT(text,[num_chars])
参数说明:
- 第1个参数text:要提取的目标字符串
- 第2个参数[num_chars]:提取几个字符。
RIGHT用法与LEFT类似,只是从字符串右边开B始提取。例如,从年月日中提取月日,如下图所示。
![d34f45344938c39a77ccaaf5260c1a91.png](https://i-blog.csdnimg.cn/blog_migrate/778cf84f57b555e59968a7c9cbd330fc.jpeg)
right函数用法示例
7、REPLACE
功能:替换字符串中的连续几个字符或者某个字符
用法:= REPLACE(old_text, start_num, num_chars, new_text)
参数说明:
- 第1个参数old_text:被替换的原始字符串
- 第2个参数start_num:从第几个字符开始替换
- 第3个参数num_chars:要替换几个字符
- 第4个参数new_text:替换后的新字符串
例如,用REPLACE函数将手机号的后4位均替换为星号(*),替换的原始字符串为单元格A2,从第8个字符开始替换,替换4个字符,替换为4个星号,如下图所示。
![55bb3e63d69fb1045bc9901350dc59c1.png](https://i-blog.csdnimg.cn/blog_migrate/43e70d9491b98822a774ce6b4c08141f.jpeg)
replace函数用法示例
8、SUBSTITUTE
功能:替换字符串中的连续几个字符或者某个字符
用法:=SUBSTITUTE(text, old_text, new_text, [instance_num])
参数说明:
- 第1个参数text:被替换的原始字符串
- 第2个参数old_text:原始字符串中被替换的部分
- 第3个参数new_text:替换后的新字符串
- 第4个参数[instance_num]:从第几个字符开始替换,可缺省。
SUBSTITUTE可以实现与REPLACE相同的效果,只是用法有所差异。
例如,用SUBSTITUTE将手机号的后4位均替换为星号,要替换的原始字符串为A2单元格中的手机号,原始字符串中被替换的部分为后4位(通过RIGHT函数获取,前面已经介绍了RIGHT函数),替换后字符串为4个星号,如下图所示。
![c9f85346e3cc485847ff6c8688f3b5b5.png](https://i-blog.csdnimg.cn/blog_migrate/bbfa82ad4e3192cfbba302b9d60ea0ac.jpeg)
substitute函数用法示例
9、求字符串长度:LEN
功能:返回文本字符串中的字符个数。
用法:=LEN(text)
说明:还有另外一个函数LENB,也可以求字符串的长度。
10、求字符串长度:LENB
功能:返回文本字符串中的字符个数。
用法:=LENB(text)
LEN和LENB的区别在于,对于len来说,一个中文字符的长度计算为1,对于lenb来说,一个中文字符的长度计算为2,对于英文字符或者数字则没有差异。
例如,通过LEN和LENB分别获取字符串的长度,对于英文、数字没有差异,对于中文有差异,如下图所示。
![fbf6369fbc495e3d82ff6e00143be94a.png](https://i-blog.csdnimg.cn/blog_migrate/76695afeb8ed27cbea7e9788787842be.jpeg)
len和lenb用法示例
关注我,学习更多数据分析知识!
Excel数据分析全套视频教程已上线,涵盖6大模块:Excel函数、Excel常用技巧、数据透视表、Excel图表、基础统计分析和Excel VBA,目前限时优惠中!