翟老师函数课堂开始啦,搬好凳子听我慢慢道来。
我们来看看以下的这样一个数据:
有同学问:“怎样用公式将A列的数据分别提取成B、C列呢?”
在这里告诉大家一个正确的写公式的过程:
1、碰到问题,一定要先分析问题,查找规律;
2、之后将问题拆解,使用我们平实的清晰的逻辑描述清楚;
3、使用函数将思路翻译成Excel语言;
4、调整细节,使公式具有更强的通用性和容错性。
在我的《函数100例》课程里,仅仅是第二课时就要讲以下这么多文本函数,那么到底要用哪个函数能完成这道题目的要求呢?千万不要晕,跟着我的思路慢慢走向我的坑中。
我们静下心来仔细看看原始数据,帮助这位同学来做下分析。
<方案一>:
通过仔细观察,我们发现,这些数据全都是中文与字母数字的区分,而字母数字的部分恰好是7位长度,于是根据文本函数,在C2单元格很容易得到:
=RIGHT(A2,7)
(这样,我们就学会并记下了右侧提取函数Right)
那么B列的产品名称呢?就是把型号删除的部分,那就把字母数字替换掉就行啦,借用C2已经得到的结果,我们在B2写下这样的公式:
=SUBSTITUTE(A2,C2,'')
(替换指定字符的函数,Substitute也学会啦)
<方案二>:
我们观察的更加细致一些呢,发现每一个数字都是以0开头,而中文是在数字0前面两位之前的部分,那么根据这个特色,我们就想到了使用FIND函数来查找0的位置。
=FIND(0,A2)
(现在知道哪个函数是用来查找特定字符的吧,那就是Find)
综合使用,在B2写公式:
=LEFT(A2,FIND(0,A2)-2)
(Left函数,也在我们的实战中理解了,它的作用是从左侧提取)
在C2写公式:
=MID(A2,FIND(0,A2)-1,99)
(有了Left和Right做基础,Mid也可以基本理解,就是从中间的某个位置开始提取)
这样的题目,我们使用两种方法搞定,只需要耐心一些,仔细分析数据特点就好。
注意,我在上面的过程中,并没有一上来就问:“这个问题用Left函数怎么做,用If函数怎么写?”而是从头到位是以思路作为引领,思路到了,然后去翻译成相应的函数。
但是,还没完……
如果数据中的型号,并不是固定的7位长度,也没有固定的字符,而是像下面这样的数据的话,那该怎么处理呢?
经过前面的学习,大家可能已经形成了自己的分析和思维方式,我们来看看这些数据要怎么处理。
首先补充一个基础知识:字符与字节的区别
函数LEN始终将每个字符(不管是单字节还是双字节)按1计数;
函数LENB会将每个双字节字符按2计数,否则,函数LENB会将每个字符按1计数。
这个是Excel的帮助信息中所写的内容,看上去晦涩难懂,我们日常的使用就可以简单记忆:
每一个英文字母、数字、以及英文状态下的标点符号,都是1个字节宽度;
每一个中文字符、以及中文标点符号,都是2个字节宽度。
有了这样的一个基础概念,我们来看看处理方案:
<方案一>:
首先通过长度的差异来取值:
=LEN(A2)
返回的结果是9,因为A2单元格有9个字符。
(Len函数我们也学会了吧,缩写与Length,表示计算字符串的长度)
=LENB(A2)
返回的结果是12,因为A2单元格的3个汉字每个占2个字节宽度,再加上6个英文、数字每个占1个字节宽度,总计12字节的宽度。
(B缩写于Byte,字节,所以Lenb就是按照字节来计算字符串的长度)
仔细观察:
LENB(A2)-LEN(A2)的这个差值,恰好等于3,就是A2单元格中汉字的个数。
LEN(A2)-(LENB(A2)-LEN(A2)),通过LEN减去汉字的长度,恰好就等于剩下的字母、数字的长度6。
做好以上铺垫,于是在B2写公式:
=LEFT(A2,LENB(A2)-LEN(A2))
在C2写公式:
=RIGHT(A2,LEN(A2)*2-LENB(A2)))
就完美的分别提取产品和型号到B、C列当中。
<方案二>:
通过观察,我们可不可以以英文字母作为起点呢?虽然字母不一样,有A、B、T,但我们希望找到一个简单的办法,找到这些第一次出现的位置。
好的,我们来引入一种通配符的概念,那就是英文半角状态下的问号(?),在可以使用通配符的函数当中它可以指代任意的一个字符。
什么样的查找字符函数可以使用通配符呢?
SEARCH呼之欲出。
进一步,如果这里单单只用SEARCH显然是无法解决问题的,那么就得召唤他的兄弟SEARCHB出场。在文本函数中,有n多带有B的函数,他们的计算都是按照“字节”,而不是“字符”来统计的。
于是有:
=SEARCHB('?',A2)
这个结果返回数字7,通过SEARCHB查找第一个“单字节”的位置,因为每一个汉字都代表2个字节宽度,所以SEARCHB在只查找那些单身字符时,这些成双配对的字符全部都忽视不见,这样就找到了A2单元格中的字母B。前面3个汉字,总计6个字节宽度,而B恰好在第7个字节的位置,所以结果是7。
有了这个分割点,于是进一步我们就得到了B2的公式:
=LEFTB(A2,SEARCHB('?',A2)-1)
以及C2的公式:
=MIDB(A2,SEARCHB('?',A2),99)
LEFTB、MIDB都是按照字节来计算的。
思路决定了你的公式是什么样子。写公式,一定是先有思路,再翻译成公式。
但是,还没完。。。
如果数据更加复杂难搞呢?快快报名参加Excel函数实战100例课程吧。通过100个实战案例,学习70个函数的实战使用。用知识装备你的武器库,丰富你的储备。
你会发现学习Excel走过最长的路是什么路?那就是翟老师的套路。
10月22日-11月6日,连续三周周末上午9点-12点开课。
作者:翟振福
联系客服