Excel之函数
1. 逻辑函数
例如:=SWITCH(TRUE,C50>=60,“合格”,C50<60,“不合格”)
2. 文本函数
2.1 LEFT、LEFTB
- LEFT(text, num)
- LEFT(text,字节数)
- LEFTB(text,字符数)
例如:上海自来水来自海上
=LEFT(E21,2)或者=LEFTB(E21,4)都可以提取上海
2.2 RIGHT(text, num)
例如:abcdrABCDE
=RIGHT(C21,2)提取DC
2.3 MID(text,start,num)
例如:=MID(C21,2,1)提取b
2.4 LEN()
-
LEN()
-
LENB()
例如:上海自来水来自海上
=LEN(C27)显示9个字符数
=LEN(C27)显示18个字节数
2.5 FIND()和SEARCH()
- FIND():支持大小写,不支持通配符
- SEARCH():不支持大小写,支持通配符
例如:ABVDavBsASA
=FIND(“a”,C31)显示a的位置5
=SEARCH(“*a”,C31)显示a(A)的位置1
2.6 REPLACE和SUBSTITUTE
-
REPLACE(text,开始位置,个数,替换)
-
SUBSTITUTE(text,MID(text,开始,个数),REPT(替换,重复个数))
例如:ABVDavBsASA
=REPLACE(C31,5,2,“##”) -----------> ABVD##BsASA
=SUBSTITUTE(C21,MID(C21,5,1),REPT(“*”,2)) -----------> ABVD**BsASA
2.7 REPT重复多遍
例如:
=REPT(‘ASd’,3) ------> ASdASdASd
2.8 TEXTJOIN和CONCAT
TEXTJOIN
-
TRUE:忽略空单元格;FALSE不忽略空单元格
例如:=TEXTJOIN(“.”,TRUE,“1999”,“”,“02”) --------> 1999.03
CONCAT
=CONCAT(“a”,“.”,“b”," ",“b”) ---------> a.b b
2.9 TEXT:将数字转化为日期
例如:=TEXT(“33”,“YYYY/MM/DD”) ------> 1900/02/02
2.10 VALUE:文本数字转化为数字
靠右是数字,靠左文本
2.11 TRIM 消除空格
例如:=TRIM(abc d bg) 变成 abc d bg
3. 日期时间函数
3.1 DATE
- 时间的初始值1是1900年1月1日
例如:=DATE(12,4,84059) -------> 2001/12/16
3.2 TIME
例如:=TIME(12,68,128) ----------> 1:10 PM
3.3 NOW() 和 TODAY()
1)NOW():显示当前年月日以及时间
2)TODAY():只显示当前年月日
3.4 YEAR()、MONTH()、DAY()
- 提取时间中的月份
例如:2001/12/16
=YEAR(“2001/12/16”) ---------> 2001
=MONTH(“2001/12/16”) ------> 12
=DAY(“2001/12/16”) ------------> 16
3.5 HOUR()、MINUTE()、SECOND()
- HOUR() ----------> 根据时间返回小时
- MINUTE() -------> 根据时间返回分钟
- SECOND() -------> 根据时间返回秒
3.6 EDATE
- 返回与指定日期相隔N个月的日期的序列数.记得转换为日期型
例如:2012/3/8
=EDATE(D72,35) ---------> 42043 ----------> 2015/2/8
3.7 EOMONTH
返回与指定日期相隔N个月的月份的最后一天日期的序列数,记得转化为日期型
例如:=EOMONTH(“2012/3/8”,35) ---------> 42063 --------> 2015/2/28
3.8 DATEDIF
计算两个日期之间相隔的天数、月数、年数
4.数学、统计函数
4.1 ABS()
- 返回数字绝对值
4.2 INT
- 向小取整
4.3 MOD
- 返回两数相除的余数
4.4 RAND 和 RANDBETWEEN
- RAND() --------> 生成[0,1)之间的随机数
- RANDBETWEEN ----------> 生成[a,b]之间的随机整数
4.5 ROUND
- 将数字四舍五入到指定位数
4.6 SQRT
- 返回算术平方根
4.7 SUM()、SUMIF()、SUMIFS()
- SUM ----------> 将单个值、单元格或者区域的值相加求和
- SUMIF --------> 对范围中符合指定条件的值求和(只有一个条件)
- SUMIFS --------> 计算满足多条件的全部参数的和(有多个条件)
4.8 AVERAGE、AVERAGEA、AVERAGEIF、AVERAGEIFS
- AVERAGE --------------> 返回其参数的平均值
- AVERAGEA -------------> 返回其参数的平均值,包括数字、文本和逻辑值
- AVERAGEIF -------------> 返回区域中满足给定条件的所有单元格的平均值(只有一个条件)
- AVERAGEIFS ------------>返回满足多个条件的所有单元格的平均值(多个条件)
4.9 COUNT、COUNTA、COUNTIF、COUNTIFS
- COUNT ------------------> 计算参数列表中数字的个数(只能是数字)
- COUNTA -----------------> 计算计算参数列表中值的个数
- COUNTIF -----------------> 计算区域内符合给定条件的单元格的数量(只有一个条件)
- COUNTIFS ---------------> 计算区域内符合多个条件的单元格的数量(多个条件)
4.10 MAX/MIN
- 返回参数列表中的最大(最小)值
4.11 RANK.AVG、RANK.EQ
- RANK.EQ ---------> 排序,返回排序序号,若值相同,序号相同,并跳过一个序号
- RANK.AVG --------> 排序,返回排序序号,若值相同,序号为平均序号
4.12 FREQUENCY
- 用数组返回频率分布
例题:
5. 查找和引用函数
5.1 INDIRECT
- 返回由文本字符串指定的引用,并对引用进行计算,显示引用结果。
5.2 TRANSPOSE(行列转置)
例如:=TRANSPOSE(A10:E19)
5.3 COLUMN、ROW
- COLUMN() ------> 列号
- ROW() ------------> 行号
5.4 MATCH
- 返回要查找信息所在的行或列
- MATCH ( 要查询的值,要查询的区域,查找类型)
- -1 表示match函数会查找大于或等于“要查询值”的最小值。
- 0 表示match函数会查找等于“要查询值”的第一个值;
- 1 表示match函数会查找小于或等于“要查询值”的最大值;
5.5 INDEX
-
返回指定位置对应的信息或值
-
**INDEX ( 信息所在的区间,查询信息所处的行,查询信息所处的列) **
5.6 VLOOKUP(按行查找)
- VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)
注意:
1)第一参数是要查的值
2)第二参数是需要查询的单元格区域,这个区域中的首列必须要包含查询值,否则公式将返回错误值。如果查询区域中包含多个符合条件的查询值,VLOOKUP函数只能返回第一个查找到的结果。(查询范围,期中要查的值必须在第一列)
3)第三参数用于指定返回查询区域中第几列的值,该参数如果超出待查询区域的总列数。(结果所在列)
4)第四参数决定函数的查找方式,如果为0或FASLE,用精确匹配方式,而且支持无序查找;如果为TRUE或被省略,则使用近似匹配方式,同时要求查询区域的首列按升序排序。如果找不到精确匹配值,则返回小于查询值的最大值。(近似查找要求所选区域第一列必须是升序排列。)
- 正常情况下,是从左往右查询,如果遇到从右往左,需要用IF来交换
例题
5.7 HLOOKUP(按列查找)
- HLOOKUP(查找条件,查找区域,返回查找区域第几行,匹配方式)
**注意:**圈定查找条件时,必须将查找条件放在第一行
例如: