LEFTRIGHTsubstitute函数讲解.docx
- 文档编号:16013425
- 上传时间:2023-07-09
- 格式:DOCX
- 页数:12
- 大小:437.02KB
LEFTRIGHTsubstitute函数讲解.docx
《LEFTRIGHTsubstitute函数讲解.docx》由会员分享,可在线阅读,更多相关《LEFTRIGHTsubstitute函数讲解.docx(12页珍藏版)》请在冰点文库上搜索。
LEFTRIGHTsubstitute函数讲解
今天我来说下 文本 包括概念 使用过程中的拆合
讲之前,我们来做个小试验
A1="a”
B1="b"
然后在C1中输入 =a1=b1(意思是让 EXCEL判断 A1等于B1吗)?
出乎了我们的意料,EXCEL认为 A=a 是成立的
这个意味着:
在平常不为人知的应用中,EXCEL是‘模糊’匹配的
也就是 不管是大写还是小写,都认为是相等的
在工作中,如果遇到这样的数据,你就会得出错误值
精确比较:
比如,在单纯的比较上
我们可以使用 EXACT函数
大至说了,我们在处理文本数据时,要考虑是否“精确处理”
什么是文本:
不可计算的字符是文本,就算是数字,也可以是文本型
比如 通过 & 文本连接符,可以让两个文本合成一个
对于两个文本型数字相加,可以得到数值型数字结果记住了,是直接相加
如果用SUM呢?
SUM的参数是忽略文本的,包括文本型数字,好多朋友会经常问,为什么我的表格好多数字加不上去呢?
很多原因是SUM函数中的引用的区域中,是文本型数字
文本对SUM来说,就是0
如何不改变数据源的情况下,让SUM求出文本数字的和?
这个式子很奇怪首先,前面多了大括号,中间的区域还*1
为了便于理解,我换个函数
如果用sum,就要数组公式了也就是在输入完
=sum(1*a1:
b1) 后要按 CTRL+SHIFT+回车键 结束三键结合,会在公式的两端产生 大括号
要用1*?
意思是告诉EXCEL,我的公式进行的是数组运算,仅仅是让文本型数字恢复为数值型
*1
SUM里面的参数,如果是文本,都视为0,就得不到结果
文本型数字经过运算后,可以得到可计算的数值型数字结果如果不经过运算,比什么是数组运算
A1:
B1*1
相当于
A1*1
B1*1
得到两个结果
由一个公式产生多个结果的,我们称之为数组运算
像1+1=2 一个式子得到一个结果,普通公式
A1+1 也是
但 A1:
A10+2相当于 让A1至A10十个单元格的值都+2产生十个新值
求和:
单条件用 SUMIF,多条件用SUMPRODUCT
现在我来说下 我们平时常见的 拆文本的函数
三剑客 LEFT MID RIGHT
ejypx-冬天的蛹(20078) 14:
26:
49
LEFT是左的意思
从指定文本的左边第一个字符开始,向右截出多少个字符。
参数的第一个是指定的文本,第二个是截取长度。
截取长度可以忽略(忽略的话,是截取一个)
所以,如果只截取一个的话,就不用写 =LEFT(A1,1)了,呵呵
A1=123456是数值型
通过=LEFT(A1,3),我要从123456的左边开始截取3个字符长度的文本,结果为 123 大家要注意,结果值为文本型在以后的后续处理中,一定要考虑这个因素
如果123456 我要截取 LEFT(A1,20)会出现123456 也就是截取长度超过字符数的时候,不会出错
下面我来说 RIGHT
和LEFT一样,RIGHT也是截取,只是RIGHT是从右边往左截的
这个注意点和LEFT一样
接下来,说 MID
MID 意思是 中间即从中间开始截。
事实上,MID可以完成LEFT和RIGHT的功能
MID有三个参数即MID(文本,指定的文本截取开始位置,截取文本长度)
上面的公式是 从123456的第2个字符位置开始截,往右截取三个字符长度。
结果是234
如何通过MID来摸拟LEFT?
LEFT(A1,3) 用MID怎么整?
其实就是 MID(A1,1,3)从左边第一个位置开始截3个字符长度
A1:
A3为长度不等的数字而我需要截取 十位上面的数字
结果是
我们在考虑一个问题时,要仔细观察数据中的共同点比如,我们需要的结果都是十位,也就是倒数第2位
因为数据长度都不同,因此,我们可以通过RIGHT截取,让截取出来的结果值长度都一样
通过这一步,可以让数据的长度一样,而且结果的位置在各数据也一样
解题的思路过程 是把原本不统一的数据,统一起来作为在统一的数据上,进行再处理
为什么我们输入公式经过拖动后,会得出正确结果?
那是因为公式是建立在 一定规律的基础之上,而这个规律是数据源共有的没有规律的数据源,公式也就谈不上了而我们编写公式的难点就是如何在一堆看似无序的数据中,找出规律来,通过该规律,构思其中的逻辑关系,再用函数拼接
在 刚才的步骤上再进行一次判断。
判断提取出来的>5的数字显示正确,否则就是错误.注意:
截取出来的是文本。
TEXT的第一参数如果是文本型数字,是不需要转数值型的.如果是IF的话,就必须转了因为IF文本数字不能直接与数值型数字比较
TEXT的第二参数就是格式代码,类同于自定义格式
首先,公式的核心部分是 left(right(a2,2))也是思路的第一步了
通过截取出来的数字,再进行比较,得出最终的答案
条件格式符的编写规则,一定要记住,有时,一个TEXT可以省却好多字符
LEN函数:
LEN是判断文本总共有多少个字符的。
lenb 就是 判断字节长度的函数
对len来说,不管是汉字还是字母数字,都视为一个字符
但lenb不一样,后面的 b 是 byte 字节的意思
一个汉字是两个字节,一个半角数字或字母是一个字节
所以 122我是谁 判断字节时3个数字+3个汉字*2=9个字节
lenb的魅力
如果通过 A列的数据,通过 函数来分开。
能用公式拆,说明数据源有规律仔细观察数据源,我们发现,尽管汉字的个数不同,但都在右侧,左侧都是半角的字母或数字通过这个规律,我们可以有这样的一个思路
第一步,判断字节数:
=lenb(a2)
第二步,判断字符数:
=len(a2)
第三步,字节数-字符数 会得到什么结果?
=lenb(a2)-len(a2)
首先,字节数是>=字符数的,字节数减去字符数 是 双字节字符的数量,本例中,就是汉字的数量。
汉字的个数都出来了,而且都在右侧
第四步,取出汉字 =right(a2,lenb(a2)-len(a2))
这样,我们通过上面的分析,一步拆出汉字,大家再进一步思路,前面的如何提取?
汉字个数都知道了,那不是汉字的数量怎么求?
是不是 字符数-汉字数?
1判断字节数 =lenb(a2)
2判断字符数 =len(a2)
3判断汉字数 =lenb(a2)-len(a2)
4判断非汉字数 =len(a2)-(lenb(a2)-len(a2))
公式简化下 =2*len(a2)-lenb(a2)
前面套用个 left即可=left(a2,2*len(a2)-lenb(a2))
通过分步拆解分析,我们一步步的进行函数拼装,最终完成了我们的要求。
我们知道了 len+b后变成了判断字节长了同样 left也可以有leftb midb rightb
substitute函数英文是替代的意思。
这个函数的应用面很广
此函数有四个参数,如果用中文来描述应该是这样
substitute(原文本,原文本的部分字符串,所替换的新字符串,替换位置)
替换位置 这个参数可以省略,省略的情况下,是所有的旧文本都替换成新文本
1、把 上海 替换成 南京
如果用技巧,查找替换即可,如果用函数substitute就有用了
把A1中所有的上海改成南京。
从这个例子上看,我们应该了解了
2、如果把文本中的第二个上海替换成南京呢?
那就指定个替换位置。
如果文本中有两个上海的话,那第二个上海就替换了。
这个是 查找替换办不到的
3、如果把第一个上海清空呢?
在替换的文本可以不写,你也可以写=SUBSTITUTE(A1,"上海","",1)
好多函数如果不写参数的话,会默认为0。
而这个函数是 “”即空值:
原因估计是文本函数吧
比如 132312 十位数为1 结果全替换掉 值为 3232
4664 这是下一个的值
132312 右起第二个数字是1 那么 整个文本都把所有的1替换掉
第二个是 456654 右起第2个是 5 把5替换掉 就是 4664
判断右起第二个数字,把文本中所有等于右起第二个数字替换掉
这个右起第二个是1 就合替换掉,变成3232
如果我们确定是1的情况下=substitute(a1,1,)
把A1中所有的1替换成无,那这个1怎么得来?
之前的LEFT+RIGHT得来的
通过left+right形式,可以取得 右起第二个字符
再进行替换即可
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- LEFTRIGHTsubstitute 函数 讲解
![提示](https://static.bingdoc.com/images/bang_tan.gif)