使用公式将单元格区域中的数据颠倒过来。例如，下图所示工作表中的单元格区域Data（即A1:A7），使用公式将原来处于区域Data中第一个单元格A1中的数据放置到最后一个单元格，本例中为单元格C7，将区......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程
excel将所有数字提取到单个单元格
Excel教程
2023-05-29 22:26:20
前三篇文章分别解了提取位于字符串开头和末尾的数字的公式技术、提取字符串中所有的数字并放在不同的单元格中的公式技术，本文研究从字符串中提取所有数字并将这些数字作为单个数字放置在单个单元格中的技术。
本文使用与上一篇文中相同的字符串：
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)))
一对比，就会感叹这样巧妙的公式应用了，只能说佩服！
标签： Excel函数 excel函数公式 excel表格制作 Excel教程 excel 提取 单元 数字 excel单元格 excel单元格怎么自动填满 word单
相关文章
- 详细阅读
-
excel函数公式大全详细阅读
一、条件判断：IF函数。 目的：判断成绩所属的等次。 方法： 1、选定目标单元格。 2、在目标单元格中输入公式：=IF(C3>=90.'优秀',IF(C3>=80.'良好',IF(C3>=60......
2023-05-29 0 excel,Excel函数,Excel教程,EXECL
-
如何隐藏Excel中的行详细阅读
隐藏你不需要的行和列可以使Excel表格更容易阅读，特别是当表格很大的时候。隐藏的行不会影响表格，但会影响公式。按照本指南中的方法，你可以在任何版本的Excel中轻松隐藏和取消隐藏行。 步骤 方法 1......
2023-05-29 0 excel公式,excel技巧,excel教程,Excel表格,Excel教程
-
SUMPRODUCT函数详解(2)详细阅读
工作表有一类最基本的函数是基于某条件统计或汇总结果，例如COUNTIF函数或SUMIF函数，都是相当有用和灵活的函数，但仅局限于单条件。而本文将着重探讨基于多条件获得结果的函数——SUMPRODUCT......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程
-
excel图表怎么使用公式排序详细阅读
Excel提供了排序功能，可以方便地对选中的列表进行排序。本文给出一个基于公式的排序解决方案，将指定区域内的数据按字母顺序排序。 如下所示，在单元格区域A2:A11中是一组未排序的数据，在单元格区域......
2023-05-29 0 Excel函数,excel函数公式,excel表格制作,Excel教程