数据分析篇–04文本函数
数据集
链接:https://pan.baidu.com/s/1T3Rwbyynwe3oaZeJMgx47Q?pwd=zq1v
提取码:zq1v
1.LEFT,RIGHT,MID,FIND,LEN系列
1.用法
1.LEFT---输出单元格左边的N个字符
2.RIGHT---输出单元格右边的N个字符
3.MID---输出单元格从第X位开始的N个字符
4.FIND---输出查找内容在单元格的第几位
5.LEN---输出单元格的字符数
6.LENB---输出单元格的字节数
1.=LEFT(单元格,输出几位)
2.=RIGHT(单元格,输出几位)
3.=MID(单元格,从第几位开始,输出几位)
4.=FIND(查找内容,单元格,[从第几位开始])
5.=LEN(单元格)
6.=LENB(单元格)
例1:输出左边的4个字符
=LEFT(A2,4)
输出右边的6个字符
=RIGHT(A2,4)
输出从第4位开始的5个字符
=MID(A2,4,5)
输出“花”在单元格的第几位
=FIND("花",A2)
注:英文双引号
输出A2单元格的字符数
=LEN(A2)
输出A2单元格的字节数
=LENB(A2)
案例1:财务应用
=LEFT(RIGHT(" ¥"&$A2*100,11-COLUMN(A2)),1)
案例2:地址提取
省:=LEFT(A2,FIND("省",A2))
市:=MID(A2,FIND("省",A2)+1,FIND("市",A2)-FIND("省",A2))
案例3:提取省/自治区、市、区/县/镇
省/自治区:=LEFT(A3,MIN(FIND({"省","市","区"},A3&"省市区")))
市:=LEFT(MID(A3,LEN(B3)+1,99),MIN(FIND({"市","区"},MID(A3,LEN(B3)+1,99)&"市区")))
区/县/镇:=LEFT(MID(A3,LEN(B3&C3)+1,99),MIN(FIND({"县","区","市","镇"},MID(A3,LEN(B3&C3)+1,99)&"县区市镇")))
2.巩固练习
1.提取省份、市
省:=LEFT(A2,FIND("省",A2))
市:=MID(A2,FIND("省",A2)+1,FIND("市",A2)-FIND("省",A2))
2.根据金额输出各位上的数字
=LEFT(RIGHT(" ¥"&$A2*100,9-COLUMN(A2)),1)
2.REPLACE,SUBSTITUTE系列
1.用法
1.REPLACE---在指定位置替换内容
2.SUBSTITUTE---以指定文本替换内容
1.=REPLACE(单元格,从哪换,换几个,换成什么)
2.=SUBSTITUTE(单元格,要换谁,换成谁,[换第几个])
例1:手机号加密(Crtl+E—根据模板,自动填充)
=REPLACE(A4,4,4,"****")
例2:银行卡号去空格
=SUBSTITUTE(A3," ","")
例3:获奖人数的统计
=LEN(B3)-LEN(SUBSTITUTE(B3,"、",""))+1
注:二等奖名单中间换成英文,
例4:某段文字的词频统计
=LEN(A2)-LEN(SUBSTITUTE(A2,B2,""))
发现第二次替换成味道
词频统计数据更多了,明显出现了问题,
这是因为第二次替换成味道
时,两个字相当于是4个字符,所以在计算时需要除以相对应的汉字数,也就是除以2.
=(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2)