excel公式技巧:将所有数字提取到单个单元格
时间:2022-08-08 01:41:29
本文研究从字符串中提取所有数字并将这些数字作为单个数字放置在单个单元格中的技术。
本文使用与上一篇文中相同的字符串:
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)))
一对比,就会感叹这样巧妙的公式应用了,只能说佩服!
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Word中千分号和万分号怎么打?
SUMPRODUCT函数用法之一:单条件、多条件、模糊条件求和
![](https://img.aspxhome.com/file/2023/0/a142190_0s.png)
excel图表给单元格添加边框的快捷键
![](https://img.aspxhome.com/file/2023/8/a142618_0s.jpg)
Excel2007的公式常见错误汇总
Win10提示“未连接到nvidia gpu”怎么办?
![](https://img.aspxhome.com/file/2023/4/52344_0s.jpg)
Win10邮箱账号设置过期怎么办?Win10邮箱账号设置过期的解决方法
![](https://img.aspxhome.com/file/2023/2/52842_0s.jpg)
Word2007怎样在自选图形中添加文字
![](https://img.aspxhome.com/file/2023/9/20819_0s.jpg)
word 如何自定义制作页眉和页脚
![](https://img.aspxhome.com/file/2023/3/33033_0s.jpg)
Excel怎么设计销售漏斗图?Excel设计销售漏斗图教程
![](https://img.aspxhome.com/file/2023/3/39823_0s.png)
苹果手机APP权限可以随意授予吗?
![](https://img.aspxhome.com/file/2023/2/46132_0s.png)
word文档碰到搜狗输入法.无法切换中文解决教程
![](https://img.aspxhome.com/file/2023/7/18257_0s.jpg)
word创建链接到网页技巧
pdf转怎么换成word文档
![](https://img.aspxhome.com/file/2023/0/19980_0s.jpg)
word 如何设置自选图形样式
![](https://img.aspxhome.com/file/2023/8/32898_0s.jpg)
Excel表格制作斜线表头并添加文字
Onenote复制的文章字体怎么快速统一?
![](https://img.aspxhome.com/file/2023/8/15238_0s.jpg)
Word 2010组件中新增的"文档导航"功能
excel输入的数据直接显示成日期格式该怎么办?
![](https://img.aspxhome.com/file/2023/2/42042_0s.jpg)
Word如何删除现有Word文档页码
![](https://img.aspxhome.com/file/2023/0/19560_0s.jpg)