excel將所有數(shù)字提取到單個(gè)單元格

excel將所有數(shù)字提取到單個(gè)單元格

前三篇文章分別解了提取位于字符串開(kāi)頭和末尾的數(shù)字的公式技術(shù)、提取字符串中所有的數(shù)字并放在不同的單元格中的公式技術(shù)，本文研究從字符串中提取所有數(shù)字并將這些數(shù)字作為單個(gè)數(shù)字放置在單個(gè)單元格中的技術(shù)。

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

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

我們希望公式能夠返回：

818755279445

解決方案

相對(duì)簡(jiǎn)潔的數(shù)組公式：

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

原理解析

現(xiàn)在，我們應(yīng)該很熟悉ROW/INDIRECT函數(shù)組合了：

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

生成由1至單元格A1中的字符串長(zhǎng)度數(shù)組成的數(shù)組，本例中A1里的字符串長(zhǎng)度為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減去該數(shù)組，即：

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函數(shù)的參數(shù)start_num的值，這樣：

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

轉(zhuǎn)換為：

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除這個(gè)數(shù)組，得到：

{#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函數(shù)，得到：

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

繼續(xù)之前，我們先看看NPV函數(shù)。

NPV函數(shù)具有一個(gè)好特性，可以忽略傳遞給它的數(shù)據(jù)區(qū)域中的空格，僅按從左至右的順序操作數(shù)據(jù)區(qū)域內(nèi)的數(shù)值。

NPV函數(shù)的語(yǔ)法為：

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

等價(jià)于計(jì)算下列數(shù)的和：

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

為了生成想要的結(jié)果，需將數(shù)組中的元素乘以連續(xù)的10的冪，然后將結(jié)果相加，可以看到，如果為參數(shù)rate選擇合適的值，此公式將為會(huì)提供精確的結(jié)果。因此，選擇-0.9，不僅因?yàn)?-0.9顯然是0.1，而且從指數(shù)1開(kāi)始采用0.1的連續(xù)冪時(shí)，得到：

0.1

0.01

0.001

0.0001

…

相應(yīng)地得到：

10

100

1000

10000

…

因此，在示例中，生成的數(shù)組的第一個(gè)非空元素是0.5，將乘以10；第二個(gè)元素0.4乘以100，第三個(gè)元素0.4乘以1000，依此類(lèi)推。

這樣，公式：

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

轉(zhuǎn)換成：

=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

注意，應(yīng)對(duì)單元格進(jìn)行格式設(shè)置，否則可能結(jié)果是貨幣形式或者指數(shù)形式。也可以在公式中添加一個(gè)INT函數(shù)來(lái)確保輸出的是整數(shù)：

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

其實(shí)，還有更復(fù)雜的公式可以實(shí)現(xiàn)，例如數(shù)組公式：

=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是定義的名稱(chēng)：

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

一對(duì)比，就會(huì)感嘆這樣巧妙的公式應(yīng)用了，只能說(shuō)佩服！

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。