 正好今天看到一位网友的问题，算是一个不错的素材，契合我们今天的主题 核心：文本中多数值提取 下面我就来一步一步讲讲，到底怎么实现，重要的思路和分析 1、找到每个数值的起始位置 要提取多个文本，首先就需要找到每个数值开始的位置，一般方式是FIND 但是FIND方式，只能找到首个位置，这里多个，不适合，换方式。 观察特征，如果我们一次提取两位，那么第一位不是数值，第二位是数值 那么第二位的位置就是数值开始的位置，但是有小数点的影响，所以先把小数点替换成任意数字，避免干扰。 > 排除小数点干扰 公式：=SUBSTITUTE($A$1,".",1) 替换换成任意数字都可以，主要是小数点是文本，排除这个干扰 在此基础上，我们就可以按照正常截取和偏移一位截取 > 偏移截取 ROW是竖向的数值，{0,1}这是一个横向的常量数值,二者相加就是两列30行 公式 =ROW($1:$30)+{0,1} 公式：=MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1) 配合MID截取效果如下 写入多个单元格方便观察，标色就是我们要的位置 文本长度，我们偷懒，估计30足够了，那么就是从第一位，一直提取到30位，每次截取长度1，同时做的事情还是+{0,1}，也是偏移以为的位置，2-31也截取1位 从上图，我们不难看出，第一位是文本，第二位是数值就是我们要的位置， 道理我们懂了，但是具体怎么做呢？ 判断是否是数值，我们可以使用ISNUMBER函数，来试试 没有问题!那么如果我们让同一行的数值，第一个乘以-1，第二个乘以1，然后相加，结果是1应该就是我们要的开始位置，其他都是FALSE的肯定是0，如果第一个是TRUE肯定是-1，也就是只有1才是我们要的 来到实操部分，具体怎么做，有没有哪个函数能实现对应相乘并相加 相当于矩阵了，EXCEL似乎最合适的就是MMULT了！ 从此处也可以看出，其实很多复杂的公式不仅仅要你懂公式，还是你有不错的数理逻辑。N的作用是把TRUE转为1，FALSE转为0，进行计算 公式：=MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1}) MMULT这个函数难度较大，学了很多年，依然不会这个函数的不在少数，整体难度可进全部函数前10，如果暂时无法理解，可以放放，看看示意图。 成功！下一步，只要判断是否是1，如果是1，就返回对应的位置ROW（1:30） > 成功找到起始位置 公式：=IF(MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1})=1,ROW($1:$30)+1) 2、依次提取位置并截取 接下来我们只要依次按照起始位置截取，然后配置LOOKUP知道数值即可 > 取出起始位置 使用SMALL可以从小到大依次提取出，我们算好的起始位置 公式：=SMALL(IF(MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1})=1,ROW($1:$30)+1),COLUMN(A1)) > 截取判断 1-30，依次截取（为了方便演示，我们临时转换一下方向），转成数值，我们要的结果已出来，就是每组的最后一个数值，那用什么函数可以实现提取最后一个数值呢？ 公式：=MID($A$1,SMALL(IF(MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1})=1,ROW($1:$30)+1),COLUMN(A1)),ROW($1:$30)) > 出来吧，结果…… 如果你有点函数储备，那么第一个应该想到LOOKUP这个函数有此特性 忽略错误值，第二参数都是负数，所以返回最后一个数值。 公式： =-LOOKUP(1,-MID($A$1,SMALL(IF(MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1})=1,ROW($1:$30)+1),COLUMN(A1)),ROW($1:$30)))
容错是必须的，超过的部分会报错，IFERROR，很轻松搞定！ 最终公式： =IFERROR(-LOOKUP(1,-MID($A$1,SMALL(IF(MMULT(N(ISNUMBER(-MID(SUBSTITUTE($A$1,".",1),ROW($1:$30)+{0,1},1))),{-1;1})=1,ROW($1:$30)+1),COLUMN(A1)),ROW($1:$30))),"")
|
|
|
