|
在前几次Excel学习内容中，讲到了关于对单元格内容进行提取的几种场景，由此前天一位朋友又提到了另外一种特殊情况，如下图：
大家看到在A列中存放着不同单位的内容，如果想把其中的数字提取出来，该如何进行呢？首先应该说明的是，这些数字没有任何规则，不是同样的长度，不是相同的单位，所以今天我们就得动动脑筋了，接下来我来一步一步的进行分析； 首先在前几次的函数学习中我们讲到了mid()函数，因为数字都在中间，如果想进行提取，必然要用到mid()函数，那接下来我们该如何来确定每个单元格第一个数字字符所在的位置呢，就拿A2单元格来说，我们如何在确定60中的6从左向右数的位置3呢？ 因为不确定每个单元格中都包含哪些数字，所以为了保险起见，我们用find()来对每一个数字进行查找，=(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1),该函数的目的是在A2&1234567890中从左边第一位向右分别找1,2,3,4,5,6,7,8,9,0；这样查找完之后，对应的从1到0，每个数字的位置为{6,7,8,9,10,3,12,13,14,4}，其中找到的每个数字的位置中，重点是其中的3和4，因为这两个数字分别对应着A2单元格中6和0的位置； 着重强调一下，为什么要在A2右侧要链接一个字符串1234567890，这是保证我们在用find进行查找时，确保每个数字都能有一个结果，否则find只能找到6和0的位置，而其他的数字找不到，找不到公式就会报错，这里用了一个小技巧，大家想一想我在上一篇文章里是如何处理这种场景的吧？ 接下来如何来确定6的位置3呢？我们只要用到min()函数就可以了，min(A,B,C)就是从A,B,C中把最小值找出来，而在A2&1234567890中第一个找到的数字，肯定是最小的=MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1))，所以在find()函数外嵌套一层min()函数就可以找到6的位置为3； 对于MID()函数而言，现在我们确定了第一个数字的位置，接下来我们要找到对应的数字的长度，才能大功告成，接下来我们继续烧脑，在此我们用了ROW($1:$20)作为辅助，这个函数的意思就是1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20；我们为了简化，用到了这样一个函数；我们对前三步所分析的结果进一步处理=MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1)),ROW($1:$20))；该函数的意思就相当于=MID(A2,3,ROW($1:$20))；对A2单元格从第3位开始提取20次，分别取1位，取2位......取20位，得到20个结果分别为'6';'60';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤';'60斤'；因为A2单元格到斤字就结束了，这样只有前两位是数字，后面就是都是包含文本的相同的“60斤”； 在上一步中我们为什么用到了row($1:$20),而不是1:19或1:21呢，其实在这里你用哪个都是可以的，主要的问题你要想想你的A列中最长的数字可能有多少位，你在这里所设的值20，不能小于A列中最大数字长度；如果你A列最长的数字是8位，那你完全可以设置为ROW($1:$9)； 我们为了将找到的这些文本转化为数值类型的内容，又要使用另外一个技巧“--”（减负），用两个减号对查找出来的结果进行处理，就可以将其转化为数值类型，为什么要转化为数值类型呢？其实不难理解，我们要做数学计算，当然得用数值类型才行； 所以最原始的公式就变成了=--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1)),ROW($1:$20))；也就是=--MID(A2,3,ROW($1:$20))，这样将找到的20个结果转化为{6;60;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}，只有真正的数值6和60正常转换了，而后面包含有文本的都显示为了#VALUE!，不过大家不用担心，我们要的就是这个结果； 最后，我们要将这组数中的最大值60，提取出来，我们用了lookup(),最后完整的公式既为=LOOKUP(2^50,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1)),ROW($1:$20)))；就相当于=lookup(2^50,{6;60;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}),在这组数中找到不大于2^50（2的50次方）的最大数，也就是其中的60；这里要求=lookup(A,{b,c,d,e,f})的第一个参数A一定要大于或等于后面大括号内的任何一个值，这样才能找出其中不大于A的最大值，所以我选用了2的50次方，这已经是一个极大的数了； 至此我们基本已经将数字都找出来了，但是最后还有一个问题就是如果A列中如果出现都是文本，而没有数字怎么办，那是不是find函数就什么也找不到了呢？如果是这样，公式就会报错，如下图： 考虑到这种可能性，我们在原始公式的最外层，加一层函数iferror来进行排错，=IFERROR(LOOKUP(2^50,--MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890,1)),ROW($1:$20))),'没有数字')，如果公式没有报错，就正常使用，如果公式出现报错，就会返回后面的文本“没有数字”，iferror是我们在使用函数过程中考虑排错用的最多的函数，
注：1.今天讲的内容有点难，希望大家能够一步一步进行分析理解，我们想学习任何东西，最重要的掌握分析问题的思路，这样我们才能触类旁通，解决类似的所有相关问题，而不是解决的仅仅是某一个问题本身；
2.今天的问题有些烧脑，但我想如果我们能把最难得部分都能解决掉了，那以后工作中那些简简单单的小函数就不就可以不放在眼里了吗;
3.大家也可以结合我在上次的文章中使用的方法进行处理，看看能否利用上篇文章的办法解决今天的问题呢？
|
