excel公式技巧:將所有數字提取到單個單元格

excel公式技巧:將所有數字提取到單個單元格

本文研究從字符串中提取所有數字并將這些數字作為單個數字放置在單個單元格中的技術。

本文使用與上一篇文中相同的字符串：

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)))

一對比，就會感嘆這樣巧妙的公式應用了，只能說佩服！

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。