截取字符串的函数_常用的Excel函数

1.条件判断：IF函数应用

数据

目的：判断成绩所属的等级

方法：在目标单元格中输入公式：=IF(C2>=90,"优秀",IF(C2>=80,"良好",IF(C2>=60,"及格","不及格")))，双击小加号填充

语法结构： IF(logical_test,value_if_true,value_if_false)

• logical_test: 选项填写条件
• value_if_true: 选项填写条件为真返回的值
• value_if_false: 选项填写条件为假返回的值

解读：IF函数是条件判断函数，根据判断结果返回对应的值，如果判断条件为TRUE，则返回第一个参数，如果为FALSE，则返回第二个参数

结果

2.条件求和：SUMIF、SUMIFS函数应用

数据

目的：求男生的总成绩和男生中分数大于等于80分的总成绩

方法：在对应的目标单元格中输入公式：=SUMIF(D2:D8,"男",C2:C8)和=SUMIFS(C2:C8,C2:C8,">=80",D2:D8,"男")

解读：

（1）SUMIF函数用于单条件求和。求和条件只能有一个。易解语法结构为：SUMIF（条件范围，条件，求和范围）

（2）SUMIFS函数用于多条件求和。求和条件可以有多个。易解语法结构为：SUMIFS（求和范围，条件1范围，条件1，条件2范围，条件2,……条件N范围，条件N）

结果

3.条件计数：COUNTIF、COUNTIFS函数应用

数据

目的：计算男生的人数或男生中成绩>=80分的人数

方法：在对应的目标单元格中输入公式：=COUNTIF(D2:D8,"男")和=COUNTIFS(C2:C8,">=80",D2:D8,"男")

解读：

1、COUNTIF函数用于单条件计数，计数条件只能有一个。易解语法结构为：COUNTIF(条件范围，条件)

2、COUNTIFS函数用于多条件计数，计数条件可以有多个。易解语法结构为：COUNTIFS（条件范围1，条件1，条件范围2，条件2……条件范围N，条件N）

结果

4.数据查询：VLOOKUP函数应用

数据

目的：查询相关人员对应的成绩

方法：在目标单元格中输入公式：=VLOOKUP(H2,B1:E8,2,0)

解读：函数VLOOKUP的基本功能就是数据查询。易解语法结构为：VLOOKUP（查找的值，查找范围，找查找范围中的第几列，精准匹配（0）还是模糊匹配（1））

补充：由于VLOOKUP函数很重要，我单独写了VLOOKUP函数总结

结果

5.逆向查询：LOOKUP函数应用

数据

目的：根据学生姓名查询对应的学号

方法：在目标单元格中输入公式：=LOOKUP(1,0/(B2:B8=H2),A2:A8)

解读：

1、公式LOOKUP函数的语法结构为：LOOKUP(1,0/(条件),目标区域或数组)

2、其中，条件可以是多个逻辑判断相乘组成的多条件数组，LOOKUP(1,0/((条件1)*( 条件2)* ( 条件N)),目标区域或数组)

3、以0/(B2:B10=E5)构建一个0、#DIV/0!组成的数组，再用永远大于第2个参数中所有数值的1作为查找值，即可查找最后一个满足非空单元格条件的记录

补充：由于LOOKUP函数内容多，我单独写了万能查找函数Lookup的总结

结果

6.查询好搭档：INDEX+MATCH 函数应用

数据

目的：根据姓名查询对应的等次

方法：在目标单元格中输入公式：=INDEX(E2:E8,MATCH(H2,B2:B8,0))

解读：

1、INDEX函数：返回给定范围内行列交叉处的值

2、MATCH函数：给出指定值在指定范围内的所在位置

3、公式：=INDEX(E2:E8,MATCH(H2,B2:B8,0))，查询E2:E8中第MATCH(H2,B3:B8,0)行的值，并返回

结果

7.提取出生年月：TEXT+MID函数应用

数据

目的：从指定的身份证号码中提取出去年月

方法：选定目标单元格，输入公式：=TEXT(MID(C2,7,8),"00-00-00")，双击小加号填充

解读：

1、利用MID函数从C2单元格中提取从第7个开始，长度为8的字符串

2、利用TEXT函数将字符的格式转换为“00-00-00”的格式，如1965-08-21

结果

8.计算年龄：DATEDIF函数应用

数据

目的：根据给出的身份证号计算出对应的年龄

方法：选定目标单元格，输入公式：=DATEDIF(TEXT(MID(C2,7,8),"00-00-00"),TODAY(),"y")&"岁"，双击小加号填充

解读：

1、利用MID函数从C2单元格中提取从第7个开始，长度为8的字符串

2、利用TEXT函数将字符的格式转换为“00-00-00”的格式，如1965-08-21

3、利用DATEDIF函数计算出和当前日期（TODAY（））的相差年份（y）

结果

9.中国式排名：SUMPRODUCT+COUNTIF函数应用

数据

目的：对成绩进行排名

方法：选定目标单元格，输入公式：=SUMPRODUCT((C$2:C$8>C2)/COUNTIF(C$2:C$8,C$2:C$8))+1，双击小加号填充

解读：公式的前半部分(C$2:C$8>C2)返回的是一个数组，区域C$2:C$8中大于C2的单元格个数。后半部分COUNTIF(C$2:C$8,C$2:C$8)可以理解为：*1/COUNTIF(C$2:C$8,C$2:C$8),公式COUNTIF(C$2:C$8,C$2:C$8)返回的值为1，只是用于辅助计算。所以上述公式也可以简化为：=SUMPRODUCT((C$2:C$8>C2)*1)+1

结果

10.查找：FIND函数应用

数据

目的：得到第一个k所在字符串的位置

方法：选定目标单元格，输入公式：=FIND("k",A2,1)

语法：FIND(Find_text,Within_text,Start_num)

• Find_text 是要查找的字符串
• Within_text 是包含要查找关键字的单元格，就是说要在这个单元格内查找关键字
• Start_num 指定开始进行查找的字符数。比如Start_num为1，则从单元格内第一个字符开始查找关键字。如果忽略 start_num，则假设其为 1

易解语法：FIND(要查找的字符串,字符串所在单元格,从第几个字符串开始查找)

结果

11.截取：LEFT、RIGHT函数应用

数据

目的：截取字符串得到最低薪水

方法：选定目标单元格，输入公式：=LEFT(A2,FIND("k",A2,1)-1)

语法：LEFT / RIGHT(text,num_chars)

• text 代表用来截取的单元格内容
• num_chars代表从左开始截取的字符数

易解语法：FIND/RIGHT(字符串所在单元格,从左/右开始到什么位置进行截取)

解读：FIND函数经常和LEFT、RIGHT函数一起使用，来进行字符串截取

FIND("k",A2,1)-1 就可以得到k前一位的位置，因此LEFT截取到k前面的字符串

结果

12.高级截取：MID+LEN函数应用

数据

目的：截取字符串得到最高薪水

方法：选定目标单元格，输入公式：=MID(A2,FIND("-",A2,1)+1,LEN(A2)-FIND("-",A2,1)-1)

语法：

1、MID(text, start_num, num_chars)

• text 被截取的字符
• start_num 从左起第几位开始截取（用数字表达）
• num_chars 从左起向右截取的长度是多少（用数字表达）

2、LEN( text)

• text需计算字符长度的字符

易解语法：

1、MID(字符串所在单元格的位置，开始位置，截取长度)

2、LEN(字符串所在单元格的位置)

解读：一般要截取字符串中间的值，需要用到MID函数，LEN(A2)获得字符串长度，LEN(A2)-FIND("-",A2,1)获得"-"后的长度，就是9k、15k等的长度，LEN(A2)-FIND("-",A2,1)-1将要截取的长度-1就去掉了最后的"k"字符，获得最高薪水

结果

Excel中的函数非常多，需要不断学习

在后续的学习中，我会不断更新文章，增加常用的函数