当前位置:首页 > 办公 > Excel教程 > 正文内容

excel将所有数字提取到单个单元格

酷网3周前 (10-07)Excel教程23

前三篇文章分别解了提取位于字符串开头和末尾的数字的公式技术、提取字符串中所有的数字并放在不同的单元格中的公式技术,本文研究从字符串中提取所有数字并将这些数字作为单个数字放置在单个单元格中的技术。

本文使用与上一篇文中相同的字符串:

81;8.75>@5279@4.=45>A?A;

我们希望公式能够返回:

818755279445

解决方案

相对简洁的数组公式:

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)/10,””))

原理解析

现在,我们应该很熟悉ROW/INDIRECT函数组合了:

ROW(INDIRECT(“1:” & LEN(A1)))

生成由1至单元格A1中的字符串长度数组成的数组,本例中A1里的字符串长度为24,因此得到:

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}

由1+LEN(A1)=25减去该数组,即:

25-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}

得到:

{24;23;22;21;20;19;18;17;16;15;14;13;12;11;10;9;8;7;6;5;4;3;2;1}

即公式中MID函数的参数start_num的值,这样:

MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1))),1)

转换为:

MID(“81;8.75>@5279@4.=45>A?A;”,{24;23;22;21;20;19;18;17;16;15;14;13;12;11;10;9;8;7;6;5;4;3;2;1},1)

得到:

{“;”;”A”;”?”;”A”;”>”;”5″;”4″;”=”;”.”;”4″;”@”;”9″;”7″;”2″;”5″;”@”;”>”;”5″;”7″;”.”;”8″;”;”;”1″;”8″}

再由10除这个数组,得到:

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.5;0.4;#VALUE!;#VALUE!;0.4;#VALUE!;0.9;0.7;0.2;0.5;#VALUE!;#VALUE!;0.5;0.7;#VALUE!;0.8;#VALUE!;0.1;0.8}

传递给IFERROR函数,得到:

{“”;””;””;””;””;0.5;0.4;””;””;0.4;””;0.9;0.7;0.2;0.5;””;””;0.5;0.7;””;0.8;””;0.1;0.8}

继续之前,我们先看看NPV函数。

NPV函数具有一个好特性,可以忽略传递给它的数据区域中的空格,仅按从左至右的顺序操作数据区域内的数值。

NPV函数的语法为:

NPV(rate,value1,value2,value3,,,)

等价于计算下列数的和:

=value1/(1+rate)^1+value2/(1+rate)^2+value3/(1+rate)^3+…

为了生成想要的结果,需将数组中的元素乘以连续的10的幂,然后将结果相加,可以看到,如果为参数rate选择合适的值,此公式将为会提供精确的结果。因此,选择-0.9,不仅因为1-0.9显然是0.1,而且从指数1开始采用0.1的连续幂时,得到:

0.1

0.01

0.001

0.0001

相应地得到:

10

100

1000

10000

因此,在示例中,生成的数组的第一个非空元素是0.5,将乘以10;第二个元素0.4乘以100,第三个元素0.4乘以1000,依此类推。

这样,公式:

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)/10,””))

转换成:

=NPV(-0.9,{“”;””;””;””;””;0.5;0.4;””;””;0.4;””;0.9;0.7;0.2;0.5;””;””;0.5;0.7;””;0.8;””;0.1;0.8})

得到:

818755279445

注意,应对单元格进行格式设置,否则可能结果是货币形式或者指数形式。也可以在公式中添加一个INT函数来确保输出的是整数:

=INT(NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1))),1)/10,””)))

其实,还有更复杂的公式可以实现,例如数组公式:

=SUM(MID(A1,LARGE(IF(ISNUMBER(0+MID(A1,Arry1,1)),Arry1),ROW(INDIRECT(“1:”&COUNT(0+MID(A1,Arry1,1))))),1)*10^(ROW(INDIRECT(“1:”&COUNT(0+MID(A1,Arry1,1))))-1))

公式中的Arry1是定义的名称:

=ROW(INDIRECT(“1:”&LEN($A1)))

一对比,就会感叹这样巧妙的公式应用了,只能说佩服!

扫描二维码推送至手机访问。

版权声明:本文章来源于互联网,由八酷网收集发布,如需转载请注明出处。

本文链接:https://www.i8ku.com/2021/39470.html

分享给朋友:

相关文章

Excel有密码怎么破解?Excel密码破解方法介绍

Excel有密码怎么破解?Excel密码破解方法介绍

  Excel密码破解方法有很多,最常见的便是使用密码字典去解读,解读成功即为破解,而本次使用的是宏命令,若是你还在一筹莫展,可以看看下文中的方法,因为涉及Excel重要文件,若是别人...

excel图表规范数据录入:不能隔行隔列填写

excel图表规范数据录入:不能隔行隔列填写

做仓库管理的一位朋友提出的问题: 他经常发表格到各个分仓库,让管理员填写商品数据,可:交上来的数据填写很不规范,时常会多出很多空白单元格,或者是少填写某项数据,给后续数据统计工作带来很多麻烦。问:能不能限定填写的时候不能...

如何使用excel数据透视表求和 使用excel求和的方法

如何使用excel数据透视表求和 使用excel求和的方法

  如何使用excel数据透视表求和?excel是一个办公软件,有文字,表格,演示等多种功能。excel中有一个数据透视表功能,想要使用其进行求和,具体怎么操作?下面就是使用excel...

怎么在Excel中圈出无效数据?Excel无效数据圈出教程

怎么在Excel中圈出无效数据?Excel无效数据圈出教程

  谈及Excel,相信网友们都不会觉得陌生,毕竟其是当下最流行的个人计算机数据处理软件。当然,在使用Excel过程中,不免会遇到需要将无效数据圈出的情况!那么,要怎么在Excel中圈...

excel图表大量员工信息,怎么快速查找哪些信息不完整?

excel图表大量员工信息,怎么快速查找哪些信息不完整?

一位做人事管理的朋友,核对本单位员工信息,要求信息不完整的要补充完整。他们单位上千名员工,核对起来很是费劲。 她问:有没有方便的办法,快速查到哪些员工信息不完整? 一、定位空值 CTRL+A全选数据,CTRL+G打开“定...

高手常用的35个函数解析,再不收藏就是你的错了

高手常用的35个函数解析,再不收藏就是你的错了

🔒Num01 SUM求和函数,可以对单个单元格或某个区域里的数值进行求和,使用语法:SUM(区域1,区域2,……)。   🔒Num02 AVERAGE求平均数函数,可以对单个单元格或某个...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。