Excel 数据处理5大常用函数

木木
木木
木木
24
文章
0
评论
2020-04-1802:05:00 评论 229 3095字
摘要

Excel是数据分析师工作过程中必不可少的工具,其蕴含了强大的功能,可以很好地进行数据处理、基础统计分析、可视化展现,是新人入门数据分析最好最基础的工具。本文主要介绍数据处理(数据清洗)过程中常用的函数,功能语法及使用方法,非常全面,学习后对学习SQL及python函数也会有较大帮助。

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

Excel 数据处理5大常用函数

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

1. Trim:清除字符串空格

=TRIM(指定字符串)

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

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

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

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

Excel 数据处理5大常用函数

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

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

Excel 数据处理5大常用函数

4. 长度计算:Len/LenB

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

注:返回字符串的长度,在Len中,中文计算为一个,在LenB中,中文计算为两个(B是Byte字节的意思)。

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

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

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

Excel 数据处理5大常用函数

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

=FIND(要查找字符,指定字符串,第几个字符),可与Left/Right/Mid结合能完成简单的文本提取

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

Excel 数据处理5大常用函数

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

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

Excel 数据处理5大常用函数

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

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

9. Proper:首字母大写

=PROPER(字符串)

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

Excel 数据处理5大常用函数

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

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

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

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

Excel 数据处理5大常用函数

和Match组合,媲美Vlookup,但是功能更强大(如下)

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

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

Excel 数据处理5大常用函数

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

=ROW(单元格)

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

=COLUMN(单元格)

7. Offset单元格偏移

=OFFSET(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)建立坐标系,以坐标系为原点,返回距离原点的值或者区域。

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

Excel 数据处理5大常用函数

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个对应的求和条件)

Excel 数据处理5大常用函数

2. Sumproduct:两列相乘求和

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

Excel 数据处理5大常用函数

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或省略,降序,否则升序

Excel 数据处理5大常用函数

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(引用区域,参数)将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,只要会了这个函数,上面的都可以抛弃掉了。

Excel 数据处理5大常用函数

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有这个函数功能,具体如何使用函数如果忘记了可以回过来再看我们的内容,或学会使用搜索引擎,网上也会有详细的解释。

Excel 数据处理5大常用函数

End.

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

Excel 数据处理5大常用函数

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: