使用说明:本文共介绍了在二级MS Office高级应用考试过程中考到的6类共51个函数,在学习过程建议打开Excel工作表【公式】-【函数库】,边操作边学习,更易于理解中每个函数参数意义。
一、 数学函数
1. Sum函数
求所有数值的总和
函数格式:=Sum(数值 1,数值 2,……)
2. Sumif函数
单个条件求和
函数格式:=Sumif(条件区域,条件,实际求和区域)
3. Sumifs函数
多个条件求和,一个条件区域对应一个指定条件,求和区域和条件区域要一致
函数格式:=Sumifs(实际求和区域,条件区域 1,条件 1,条件区域 2,条件 2,……)
4. Sumproduct函数
积和函数,对应区域的单元格相乘,然后再对这些乘积求和
函数格式:=Sumproduct(区域1*区域2*……)
5. Round函数
四舍五入函数,第二个参数表示保留多少位小数。如果取整数,第二个参数小数位数就是0
函数格式:=Round(数值,小数位数)
6. Roundup函数
向上舍入数字,第二个参数表示保留多少位小数。如果小数位数大于0,则向上舍入到指定的小数位。如果小数位数等于0,则向上舍入到最接近的整数。如果小数位数小于0,则在小数点左侧向上进行舍入。
函数格式:=Roundup(数值,小数位数)
7. Rounddown函数
向下舍入数字,第二个参数表示保留多少位小数。如果小数位数大于0,则向下舍入到指定的小数位。如果小数位数等于0,则向下舍入到最接近的整数。如果小数位数小于0,则在小数点左侧向下进行舍入。
函数格式:=Rounddown(数值,小数位数)
8. Int函数
向下取整数,与Rounddown第二个参数为0时用法一样
函数格式:=Int(数值)
9. Sqrt函数
开平方根
函数格式:= Sqrt(数值)
10. Mod函数
求余数
函数格式:= Mod(被除数,除数)
11. Ceiling函数
将数字向上舍入最接近指定基数的整倍数
函数格式:=Ceiling(数值,基数)
例 1:CEILING(3.5,1) 结果:4 说明:将 3.5 向上舍入到最接
近的 1 的倍数
例 2:CEILING(3.5,0.1) 结果:3.5 说明:将 3.5 向上舍入到最接
近的 0.1 的倍数
二、 统计函数
12. Count函数
统计单元格的个数,参数必须是数值
函数格式:=Count(数值 1,数值 2,…)
13. Countif函数
统计满足单个条件的单元格个数
函数格式:=Countif(指定区域,指定条件)
14. Countifs函数
统计满足多个条件的单元格个数
函数格式:=Countifs(指定区域 1,指定条件 1,指定区域 2,指定条件 2,…)
15. Average函数
求所有数值的平均值
函数格式:=Average(数值 1,数值 2,…)
16. Averageif函数
求满足单个条件的数值平均值
函数格式:=Averageif(条件区域,条件,实际求平均区域)
17. Averageifs函数
求满足多个条件的数值平均值,函数使用类似Sumifs
函数格式:=Averageifs(实际求平均区域,条件区域 1,条件 1,条件区域 2,条件 2,…)
18. Rank函数
排名函数,第三个参数是0或者省略时表示降序,是1表示升序,用法和Rank.eq函数一样
函数格式:=Rank(数值,要在其中排名的数据区域,降序/升序)
19. Large函数
返回数据区域第几个大的单元格值,可求任意名次。
函数格式:=Large(数据区域,名次)
20. Max函数
求一组数字中的最大值
函数格式:=Max(数据区域)
21. Min函数
求一组数字中的最小值
函数格式:=Min(数据区域)
三、 文本字符类函数
22. Concatenate函数
多个字符串合并为一个整体,与“&”连接符用法相同
函数格式:=Concatenate(字符串 1,字符串 2,…)
23. Left函数
从字符串最左边截取字符
函数格式:=Left(字符串,截取位数)
24. Right函数
从字符串最右边截取字符
函数格式:=Right(字符串,截取位数)
25. Mid函数
万能截取函数,可以从字符串的任意一个字符位置开始截取指定位数的内容
函数格式:=Mid(字符串,开始位置,截取位数)
26. Replace函数
替换字符串中指定内容,可用于屏蔽或隐藏字符,例如隐藏手机号后四位,目前只在选择题中考过
函数格式:=Replace(字符串,开始替换位置,替换个数,”替换后的字符”)
27. Find函数
寻找某一个字符串在另一个字符串中出现的起始位置(区分大小写)
函数格式:=Find(要查找的字符串,要在其中进行搜索的字符串)
28. Search函数
与Find函数用法一样,区别在于Find函数是精确查找,区分大小写;Search函数是模糊查找,不区分大小写
函数格式:=Search(要查找的字符串,要在其中进行搜索的字符串)
29. Len函数
测量字符串长度,其中标点、空格、英文字母、汉字都作为为一个字符计算
函数格式:=Len(字符串)
30. Lenb函数
测量字符串长度,其中汉字作为两个字符计算,标点、空格、英文字母作为一个字符计算
函数格式:=Lenb(字符串)
如果A1中内容是:易烊千玺ABCD,则LEN(A1)=8,LENB(A1)=12
31. Clean函数
删除文本中所有不可见字符(不可打印字符)
函数格式:=Clean(文本)
32. Trim函数
主要用于把单元格内容前后的空格去掉,但并不去除字符之间的空格
函数格式:=Trim(文本)
33. Text函数
把数值转换成文本,第2个参数是格式代码,如”@”
函数格式:=Text(数值,”格式代码”)
四、 日期类函数
34. Today函数
返回当前电脑的日期,没有参数
函数格式:=Today()
35. Now函数
不仅返回当前日期,还会返回当前时间,没有参数,当Now函数减去Today函数时返回此时此刻
函数格式:=Now()
36. Year函数、Month函数、Day函数
提取日期中的年份、月份、日
函数格式:=Year(日期)
=Month(日期)
=Day(日期)
37. Hour函数、Minute函数、Second函数
提取时间中的时、分、秒
函数格式:=Hour(时间)
=Minute(时间)
=Second(时间)
38. Weekday函数
判断日期是星期几,第二个参数是排序方式,一般使用2,表示星期一是每周的第一天,返回数字1就是星期一,返回数字7就是星期日,一一对应
函数格式:=Weekday(日期,2)
39. Date函数
构造日期,可以把年月日组合在一起,返回一个日期。其中年份介于数字1900-9999之间,月份介于数字1-12之间,日介于数字1-31之间
函数格式:=Date(年份,月份,日)
40. Days360函数
计算两个日期间隔的天数,每月按30天,一年按360天计算,一般题目会特别要求
函数格式:=Days360(开始日期,结束日期)
41. Datedif函数
计算日期之间的差值,是Excel隐藏函数,在帮助和插入公式里面没有,要自己手动输入。
第一个参数开始日期,第二个结束日期,第三个Unit有6种
"YD" 起始日期与结束日期的同年间隔天数。忽略日期中的年份。
"Y" 时间段中的整年数。
"M" 时间段中的整月数。
"D" 时间段中的天数。
"MD" 起始日期与结束日期的同月间隔天数,忽略日期中的月份和年份。
"YM" 起始日期与结束日期的间隔月数,忽略日期中年份。
函数格式:=Datedif(开始日期,结束日期,”间隔类型”)
五、 逻辑判断类函数
42. If函数
判断是否满足逻辑判断条件,如果满足返回指定值1,如果不满足返回指定值2。在对多种情况进行判断时使用 if函数
函数格式:=If(逻辑判断,指定值 1,指定值 2)
43. And函数
表达式同时成立时,结果为true,只要其中任意一个表达式不成立,结果为false
函数格式:=And(表达式1,表达式2,表达式3,……)
44. Or函数
只要其中一个或一个以上的表达式成立,结果为true,所有表达式均不成立时,结果为false
函数格式:=or(表达式1,表达式2,表达式3,……)
45. Iferror函数
如果公式计算结果为错误值,则返回指定的值,否则返回公式正常计算的结果
函数格式:=Iferror(表达式,指定值)
46. Row函数
返回指定单元格的行数
函数格式:=Row(单元格地址)
47. Column函数
返回指定单元格的列数,只返回数字
函数格式:=Column(单元格地址)
六、 查找引用类函数
48. Vlookup函数
垂直查找函数,给定一个查找的目标,从指定的查找区域中查找并返回想要查找到的值。查找目标所在列必须是查找范围的开始列(第一列)
第4个参数如果值为0或FALSE则表示精确查找,如果找不到精确匹配值,则返回错误值#N/A。如果值为1 或TRUE,或者省略时,则表示模糊查找。
函数格式:=Vlookup(查找目标,查找范围,返回值的列数,精确或模糊查找)
49. Lookup函数
从单行或单列或从数组中查找一个值。
函数格式:=Lookup(查询条件-要查找的值,条件区域-要查找的范围,要查找的内容区域-要获得的值)
50. Index函数
在给定的单元格区域中,返回指定行列交叉处单元格的值,常和match函数组合使用
函数格式:=Index(数据区域,行标,列标)
51. Match函数
返回指定值在指定数据区域中的行标或列标,通常作为Index函数的一个参数在一起考核。
第三个参数匹配方式有3种,为1或省略,表示MATCH函数会查找小于或等于指定值的最大值;为0表示MATCH 函数会查找等于指定值的第一个值;为-1表示MATCH函数会查找大于或等于指定值的最小值。
函数格式:=Match(指定值,指定数据区域,匹配方式)