【原】海鮮大法找出相同數(shù)據(jù),，能看懂的全部都是高手,！

EXCEL應用之家 2025-01-11 發(fā)布于上海

展開全文

歡迎轉(zhuǎn)發(fā)和點一下“在看”,，文末留言互動!

置頂公眾號或設為星標及時接收更新不迷路

小伙伴們好，今天來和大家分享一道非常典型的題目,。通過這道題目，我們可以學習到兩個重要的技巧：一是FILTERXML函數(shù)的第二參數(shù)的寫法,；而是在低版本條件下如何合并數(shù)值型數(shù)據(jù),。

原題目是這樣子的：

由于是數(shù)值型數(shù)據(jù)，因此PHONETIC函數(shù)就不能使用了,。而今天介紹的這個技巧則可以成為解決這類問題的套路,。

FILTERXML函數(shù)

在單元格D2中輸入下列公式，三鍵回車即可,。

=MID(SUBSTITUTE(TEXT(SUM(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2)),REPT("!.00",7)),".0","."),2,100)

下面來詳細介紹一下這條公式,。

FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")

利用SUBSTITUTE函數(shù)將數(shù)據(jù)1中的“.”都替換為“</b><b>”,并在左右兩側(cè)添加“<a><b>”和“</b></a>”，滿足FILTERXML函數(shù)的格式要求,。

"a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]"

第二參數(shù)"a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]"是今天這道題目的第一個重點,。在這里：

SUBSTITUTE(B2,"."," or .=")

這部分，將數(shù)據(jù)2中的“.”替換為“ or .=”,，替換后的結果為：

"1 or .=4 or .=5 or .=6 or .=8 or .=9 or .=10 or .=11 or .=13",。

因此整個第二參數(shù)最終返回的結果是：

"a/b[.=1 or .=4 or .=5 or .=6 or .=8 or .=9 or .=10 or .=11 or .=13]"。

其含義是：數(shù)據(jù)1中的數(shù)據(jù)等于數(shù)據(jù)2中的任意一個,。

最終,，F(xiàn)ILTERXML函數(shù)返回的結果是{1;4;5;6;8;9;10}。但如何將它們按要求合并起來呢,？

接下來,，要將它們加權處理。

10^(14-ROW($1:$7)*2)

這部分是對應的權重,。已知有7個重復數(shù),，而且還有一個數(shù)字是兩位數(shù)，因此需要每個數(shù)字都以兩位數(shù)的形式,。所以這里寫為ROW(1:7)和14,。如果不確定有多少個重復的，那就可以把數(shù)字稍微寫大一些,，然后再套用一個IFERROR函數(shù)屏蔽錯誤值就好了,。

FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2)

這部分的結果是{1000000000000;40000000000;500000000;6000000;80000;900;10}。

SUM(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2))

SUM函數(shù)求和后的結果是1040506080910，現(xiàn)在朋友們理解為什么要寫成10^(14-ROW($1:$7)*2)了吧,！

TEXT(SUM(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2)),REPT("!.00",7))

接下來TEXT函數(shù)是這個公式的第二個重點,。在這里：REPT("!.00",7)部分將"!.00"重復7次，作為TEXT函數(shù)的第二個參數(shù),。

TEXT函數(shù)最終返回的結果是".01.04.05.06.08.09.10",。

SUBSTITUTE(TEXT(SUM(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2)),REPT("!.00",7)),".0",".")

接下來SUBSTITUTE函數(shù)將“.0”替換為“.”，結果就變成".1.4.5.6.8.9.10",。

MID(SUBSTITUTE(TEXT(SUM(FILTERXML("<a><b>"&SUBSTITUTE(A2,".","</b><b>")&"</b></a>","a/b[.="&SUBSTITUTE(B2,"."," or .=")&"]")*10^(14-ROW($1:$7)*2)),REPT("!.00",7)),".0","."),2,100)