湖南龙网

  1. 首页 > 生活百科 > >

excel图表将所有数字分别提取到不同的单元格

小知识生活百科

前两篇文章分别解了提取位于字符串开头和末尾的数字的公式技术，本文研究从字符串中提取所有数字的技术：
1. 字符串由数字、字母和特殊字符组成
2. 数字在字符串的任意地方
3. 字符串中的小数也一样提取
3. 想要的结果是将所有数字返回独立的单元格
例如，在单元格A1中的字符串：
81;8.75>@5279@4.=45>A?A;
返回：
单元格B1：81
单元格C1：8.75
单元格D1：5279
单元格E1：4
单元格F1：45
解决方案
首先，确保活动单元格处于工作表行1中，然后定义下面两个名称 。
名称：Arry1
引用位置：=ROW(INDIRECT(“1:”&LEN(“α”&$A1&”α0”)-1))
名称：Arry2
引用位置：=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
在单元格B1中输入数组公式：
=IFERROR(0+MID(“α”& $A1 &”α0″,1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A)),SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})),””)
向右拖放直至出现空单元格为止 。
原理解析
1. 先看看这两个定义的名称 。对于Arry1：
=ROW(INDIRECT(“1:”&LEN(“α”&$A1&”α0”)-1))
生成由整数构成的数组 。注意，在单元格A1的字符串前面添加了一个非数字字符“α”，在末尾添加了一个非数字字符和一个数字“α0” 。为什么这样处理？具体原因在后文详述 。
上述公式转换为：
=ROW(INDIRECT(“1:”&27-1))
结果为：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26}
2. 对于Arry2：
=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
（1）公式通过引用ASCⅡ字符代码来测试单元格A1里的数字 。
（2）要识别数字子字符串，必须找到字符串里两个不同的位置：一个对应着数字的起始位置，另一个对应着数字的结束位置 。
（3）公式中的第一部分将给MID函数提供参数start_num，在生成的结果之间的减法提供相应的参数num_chars 。
（4）对于0-9范围的整数的ASCⅡ编码从48到57，小数点是46 。因此，如果首先从字符串中排除与ASCⅡ编码47相对应的任何字符（“/”），那么可以确定字符串中ASCⅡ编码在46-57范围内的任何字符要么是数字要么是小数点 。
（5）使用字符的ASCⅡ编码减51.5，判断其结果的绝对值，如果小于或等于6，则可以判断该字符是数字或小数点 。（这里运用的技巧等价于通常要使用的两个单独的条件判断，即一个来比较ASCⅡ编码大于45，另一个来比较ASCⅡ编码小于58 。）
（6）注意，为了发现数字的开始位置和结束位置，这里查找字符串里的两对字符：一对中的第一个字符是非数字字符而第二个是数字字符（提供数字字符串的开始），另一对中的第一个字符是数字字符而第二个是非数字字符（提供数字字符串的结尾） 。
（7）当然，如果字符串中的第一个或最后一个字符与上述标准相符，那么需要确保有一些字符在它们的前面或后面，这就是我们在A1的开头和结尾连接合适的字符串的原因 。于是，就有了你所看见的”α”&和&”α0″ 。
这样，Arry2公式转换为：
MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α81;8.75>@5279@4.=45>A?A;α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})
转换为：
MMULT(0+(ABS(51.5-CODE(MID(“α81;8.75>@5279@4.=45>A?A;α0”,Arry1+{0,1},1)))>6)*{2,1},{1;1})

推荐阅读


上一篇：搜狗浏览器和360浏览器哪个好？搜狗浏览器与360浏览器对比

下一篇：excel从字符串中提取数字——数字位于字符串末尾