cgregion 发表于 2015-6-27 11:38
非常感谢你对我的帮助,我照H2的公式处理了和H2最相近的L2,但L2是从P2:U2和B3:F3取数,公式改为=TRIM(S ...
H2 =TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(B3:F3,B2:F2)*B$1:F$1*10^(12-B$1:F$1*2)),0," "))
I2 =TRIM(SUBSTITUTE(SUMPRODUCT(COUNTIF(H3,"*"&--MID(H2,{1,3,5,7,9},1)&"*")*{1,2,3,4,5}*10^{8,6,4,2,0}),0," "))
(一) 查詢
H2公式是5個數值對5個數值查詢是否有相同的數值,COUNTIF(B3:F3,B2:F2)
I2公式是文字"3 4 5"查詢文字"1 3"是否有相同的數值,COUNTIF(H3,"*"&--MID(H2,{1,3,5,7,9},1)&"*")
(二)多數值以一個文字表示,{0,0,3,4,5}變為"3 4 5"
TRIM(SUBSTITUTE(SUMPRODUCT(...*{1,2,3,4,5}*10^{8,6,4,2,0}),0," "))
(1)SUMPRODUCT(...*{1,2,3,4,5}*10^{8,6,4,2,0}) 將 {0,0,3,4,5}變為 30405
(2)SUBSTITUTE(30405,0," ")將 30405變為 "3 4 5"
(3)TRIM(...) 使將前後有多於的" "刪除
|