6个文本函数的组合应用技巧解读,绝对的干货哦! |
您所在的位置:网站首页 › 函数len和lenb怎么用 › 6个文本函数的组合应用技巧解读,绝对的干货哦! |
Excel的主要功能是数据处理,但是在使用的过程中,经常会遇到一些文本数据,例如提取指定的字符串,查找指定值的位置等等,这时候,掌握一定数量的文本函数是必不可少的。 一、文本函数Left+Find:提取指定的值。 目的:提取地址中的省市。 (一)基础函数解读。 1、left函数 功能:从一个字符串的第一个字符开始,返回指定长度的字符。 语法结构:=Left(字符串,[字符长度]),如果省略“字符长度”,则默认为1。 目的:提取指定单元格中的前3个字符。 方法: 在目标单元格中输入公式:=LEFT(B3,3)。 2、Find函数 功能:返回一个字符串在另一个字符串中的起始位置(区分大小写)。 语法结构:=Find(查找字符,被查找字符串,[查找开始位置])。 目的:查找“市”的位置。 方法: 在目标单元格中输入公式:=FIND("市",B3)。 (二)Left+Find:提取地址中的“省市”。 方法: 1、在目标单元格中输入公式: 2、快捷键Ctrl+Shift+Enter填充。 解读: 公式中使用Find函数查找“市”和“省”的位置,然后利用Left函数提取查找到的位置的左侧字符即可,但由于B列地址中可能有市,也可能有省,所以在Find函数中使用了一个常量数组{"市","省"},可以同时查找“市”和“省”。 二、文本函数Left+求和函数Sum:统计参会人数。 目的:统计各地区参会人数。 方法: 1、在目标单元格中输入公式:=SUM((LEFT(B$3:B$12,2)=F3)*C$3:C$12) 2、快捷键Ctrl+Shift+Enter填充。 解读: 公式中首先用Left函数提取B列单元格值的前2位,和F3单元格的值进行比较,形成一个True和False的数组,然后将此数组和C$3:C$12区域中的值相乘,最后用Sum函数求和,得到相应地区的总人数。 三、文本函数Len、Lenb:计算字符或字节数。 目的:计算字符串中的数字个数。 (一)基础函数解读。 1、Len函数。 功能:计算文本中的字符个数。 语法结构:=Len(字符串)。 目的:计算指定字符串的长度。 方法: 在目标单元格中输入公式:=LEN(B3)。 2、Lenb函数。 功能:计算文本中的字符个数,中文按照每个字2个字符计算。 语法结构:=Lenb(字符串)。 目的:计算指定字符串的长度。 方法: 在目标单元格中输入公式:=LENB(B3)。 解读: 从功能定义上看,Len函数和Lenb函数的作用是相同的,但结算的结果却是有区别的,Len函数计算的是字符的长度,即字符的个数,而Lenb函数计算的是字节的个数,每个中文按照2个字节来计算。 3、Len+Lenb:计算指定值中除中文外的字符长度。 方法: 在目标单元格中输入公式:=LEN(B3)*2-LENB(B3)。 解读: 对于公式的理解,可以先从结果入手,例如第一个字符串“电视机DSJ0090”中除中文外的字符长度为7,而7=字符长度-中文长度,即7=LEN(B3)-(LENB(B3)-LEN(B3)),整理之后的公式为=LEN(B3)*2-LENB(B3)。 四、文本函数Mid+Find+Len:获取特定字符串。 目的:提取字符串中的“部门”和“姓名”。 (一)基础函数Mid解读。 功能:从文本字符串中指定的位置起返回指定长度的字符。 语法结构:=Mid(字符串,起始位置,字符长度)。 目的:返回指定字符串中从第3个字符开始,长度为2的字符串。 方法: 在目标单元格中输入公式:=MID(B3,3,2)。 (二)提取字符串中的“部门”和“姓名”。 方法: 在目标单元格中输入公式:=MID(B3,1,FIND(":",B3)-1)、=MID(B3,FIND(":",B3)+1,LEN(B3))。 解读: 公式=MID(B3,1,FIND(":",B3)-1)中,首先利用Find函数获取“:”的位置,然后-1进行修正,也就是“部门”的长度,然后用Mid函数进行提取;公式=MID(B3,FIND(":",B3)+1,LEN(B3))中,同样也是用Find函数获取“:”的位置,然后+1进行修正,也就是“姓名”的起始位置,然后用Len获取字符串的总长度,来填充Mid函数的第3个参数,因为要提取的字符串最长也就是字符串长度。 (二)巧用分列法提取“部门”和“姓名”。 方法: 1、选定目标单元格。 2、【数据】-【分列】,选择【分隔符号】并【下一步】,在【分隔符号】的【其他】中输入:并【下一步】。 3、单击【目标区域】右侧的箭头,选择数据的存放位置,并单击箭头返回,【完成】-【确定】。 五、文本函数Rept:制作简易的销售图表。 (一)基础函数Rept解读。 功能:生成重复的字符。 语法结构:=Rept(重复的文本,重复的次数)。 目的:重复“部门获奖人员”信息2次。 方法: 在目标单元格中输入公式:=REPT(B3,2)。 (二)巧用Rept制作简易的销售图表。 方法: 1、在目标单元格中输入公式:=REPT("r",C3/10)。 2、设置单元格的字体为:Wingdings。 六、函数Concatenate+IF+Sum:评定员工考核绩效。 (一)基础函数Concatenate解读 功能:将多个文本合并到一起。 语法结构:=Concatenate(字符串1,字符串2……字符串N)。 目的:将个人信息连接成一个字符串。 方法: 在目标单元格中输入公式:=CONCATENATE(B3,C3,D3,E3)。 (二)评定员工考核绩效。 方法: 在目标单元格中输入公式:=CONCATENATE(SUM(C3:E3)," | ",IF(SUM(C3:E3)>240,"优秀","一般"))。 解读: 公式中首先利用Sum函数计算个人的总分,然后用IF函数判断总分的情况是“优秀”还是“一般”,最后用Concatenate将总分,“ | ”和判断的结果连接起来。 结束语: 文本从实际出发,对常用的文本函数及求和、判断等函数的综合应用做了详实的解读,对于使用技巧,你Get到了吗?欢迎在留言区留言讨论哦! |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |