不规则匹配的问题,如图一、图二
看这道题要求,根据书目序号,从书目工作表里面找到书名并连接起来,找到书单价并把他们求和。
这里的书目序号并非在不同的单元格里面,而是集合在一个单元格里面,这里就有两个问题需要我们解决：
1. 如何提取出书目序号组合里面每一个书目序号？
2. 如何将匹配出来的书目连接起来？
问题一解决
这里可以发现书目序号都是由"+"连接起来,我们可以根据"+"将各个书目序号分隔开来,然后用MID函数来进行提取。
但是由于书目序号也不规则,有个位、十位、百位之分,MID函数第三个参数是要求提取固定长度的文本的,那么我们退而求其次：
如果用MID函数提取的文本里,只包含一个书目序列号,前后都是空格；这样的话我们就可以使用TRIM函数来剔除前后的空格拿到书目序号的文本了。
所以我们首先要"+"全部替换成空格,需要替换成多少个空格才好呢？
这里是替换成足够多的空格,因为这样可以保证各个书目序号隔得足够的开,取数的时候不会互相干扰。这里一般是99个空格。
所以有函数
=
REPT函数是一个重复函数,指的是指定字符重复多少次。
这样书目序号之间隔得就足够开了
哪个地方开始取值呢？取多少好呢？
由于书目序号组合间都隔了99个空格,这里取的字符长度可以是99。
从哪里开始取值,第一个肯定是要从第一个字符开始取值,不然第一个字符取不到。然后隔99个字符取99个长度的字符,保证取得书目序号之间不会重复
这里有四个书目序号那么就是有4次取值,可以使用函数
=COLUMN($A:$D)-1)*99+1
最后取到书目序号的公式就是
=--TRIM(MID(SUBSTITUTE(C4,"+",REPT(" ",99)),(COLUMN($A:$D)-1)*99+1,99))
"--"是把文本数字转换成数值,因为书目工作表是数值当索引。
那么这样就取到了书目序号的一个数组。
之后就是用书目序号来进行书名的匹配,这里用到一个函数OFFSET,因为一般单元格数组公式只能处理其中的第一个引用,但是这个函数可以和T或者N函数配合形成一个内置数组,就是数组里面的每个值都可以作为参数来进行使用。
可以这样来理解
A1=1,A2=2,A3=3,=SUM(OFFSET($A$1,{0,1,2},0))
OFFSET函数是一个位移函数,就是指定单元格或者区域移动多少行,移动多少列。第二个参数是行,正数就是往下移,负数就是往上移动；第三个参数是列,正数就是往右移动,负数就是往左移动,这里可以通过平面直角坐标系来联想记忆。
T函数是让文本返回文本,其他返回空值；N函数是将不是数值形式的值转换为数值形式,日期会转换成序列值,TRUE转换成1,其他值转换成0。
这个里面{0,1,2}是一个数组,但是在参与求和时,只会返回第一个引用,也就是0,那么这个OFFSET函数就会返回A1,所以最后求和结果是1。
但是如果我修改一下,加一个N函数上去,那么和OFFSET配合就会形成一个内置数组。
=SUM(N(OFFSET($A$1,{0,1,2},0)))
那么OFFSET就会返回A1、A2、A3,SUM求和结果就会是6。
那么接下来就是找到书名
从书目!$C$1 开始找起即可
公式为
=T(OFFSET(书目!$C$1,--TRIM(MID(SUBSTITUTE(C4,"+",REPT(" ",99)),(COLUMN($A:$D)-1)*99+1,99)),0))
那么问题一解决,这里就会返回一个由书名组成的数组。
问题二解决
将一个数组内的文本连接就需要引用一个TEXTJOIN函数
例如A1=1,A2=2,A3=3,=TEXTJOIN(",",1,A1:A3)
结果就是1,2,3。第二个参数表示是否忽略空单元格,1就是忽略,0就是不忽略。
所以连接书名的函数为
《"&TEXTJOIN("》,《书目!$C$1,--TRIM(MID(SUBSTITUTE(C4,"+",REPT(" ",99)),(COLUMN($A:$D)-1)*99+1,99)),0)))&"》"
如图三
求和函数同理
书目!$D$1,--TRIM(MID(SUBSTITUTE(C4,"+",REPT(" ",99)),(COLUMN($A:$D)-1)*99+1,99)),0)))
如图四
如果遇到批量操作,看最多书目序号组合,有多少就将,里面的A:D范围修改一下,套上IFERROR函数即可解决
例如如果书目序号最多有10个,那么就可以写出如下函数
《"&TEXTJOIN("》,《书目!$C$1,IFERROR(--TRIM(MID(SUBSTITUTE(C3,"+",REPT(" ",99)),(COLUMN($A:$J)-1)*99+1,99)),1000),0)))&"》"
需要练习题则回复009