麻辣家族討論版版 - Powered by Discuz! Board

標題: [發問] excel 模糊比對的問題 [打印本頁]

作者: oshi 時間: 2015-2-4 23:07 標題: excel 模糊比對的問題

本帖最後由 oshi 於 2015-2-4 23:09 編輯

目前有一個檔案

目的:
C欄位的值，模糊比對B欄位。
如果找到類似的就在D欄位填入A欄位的值，如附圖

問題:
本來想要使用match("*"&C2&"*"....去收尋

但是會發生C欄位裡面的字串會比B欄位多
所以在判別的時候就會變成 " *DVDD12_LP2_3* "

希望各位大大能夠幫小弟解決這個問題>"<
感謝

[attach]20199[/attach]

作者: rouber590324 時間: 2015-2-5 09:46

DEAR SIR
使用如下試試
=IF(SUBSTITUTE(C2,B2,"")=C2,"",A2)

作者: oshi 時間: 2015-2-5 15:38

回復 2# rouber590324

rouber大您好~
剛剛試了一下你給得提示發現

他只會把第一個更新之後就沒反應了XDD
[attach]20203[/attach]
昨天沒有說明清楚，想到的時候已經不能編輯了

檔案裡面有很多(B/C欄)重複的名字，但B欄的名稱一定有相對應的A欄

B/C欄位長度不一樣Q_Q

而位置也不像是昨天提供的那麼整齊

可能會是

A1  bbbb PPPP
B2  CCC bbbb
C3 bbbb  YYYY
               QQQ

大概是這樣，不過還是很感謝R大幫我QQ

作者: rouber590324 時間: 2015-2-5 15:49

DEAR SIR
我只是寫 D2範例你要用游標COPY公式至 D3-D65536啦
D3以後無公式當然無反應.

=IF(SUBSTITUTE(C2,B2,"")=C2,"",A2)

作者: oshi 時間: 2015-2-5 19:08

回復 4# rouber590324

Dear R大

我有複製耶並且下拉喔~

結果就是顯示剛剛那樣...0.0

作者: oshi 時間: 2015-2-5 19:16

DEAR SIR
我只是寫 D2範例你要用游標COPY公式至 D3-D65536啦
D3以後無公式當然無反應.

=IF(SUBST ...
rouber590324 發表於 2015-2-5 15:49

Dear R大

另外有一個問題是~用SUBSTITUTE 不就只是把B欄的字串替換到C欄??

這樣跟我想要做的模糊比對好像不太一樣QQ

作者: Hsieh 時間: 2015-2-5 19:44

回復 6# oshi

以圖檔資料看來B欄與C欄比對，可能不只一個儲存格符合
你要傳回的是那些資料?
請將EXCEL檔案壓縮後上傳，方便測試使用

作者: oshi 時間: 2015-2-5 21:58

本帖最後由 oshi 於 2015-2-5 21:59 編輯

回復 7# Hsieh

[attach]20214[/attach]

因為涉及的保密的問題
所以我另外做了一份excel，不過大概的概念是一致的

如附檔~

Step1：比對B欄位跟D欄位的值，若有比對到相同值，則讓A欄位的值填入E欄位
Step2 : 如果都沒有找到E欄位填#not found，若有找到但是有多而無法填入值的儲存格填#TBD
Step3 : 模糊比對是否有類似name ，想要也讓E欄位可以填入值
目前就是卡在第三步

本來是有考慮過用vlookup的模糊收尋的功能，
但是就會遇到vlookup 遇到重複值只會填第一個值
這樣就不是我的目的了Q_Q

再麻煩Hsieh版主大大了

作者: Hsieh 時間: 2015-2-6 18:20

回復 8# oshi
無符合資料填入"#not found"
符合有1個以上則填入"#TBD"
僅1個符合則填入A欄對應值
是這樣嗎?
試試看
E2公式
=IF(SUMPRODUCT(ISNUMBER(FIND($D2,$B$2:$B$151))*1)=0,"#not found",IF(SUMPRODUCT(ISNUMBER(FIND($D2,$B$2:$B$151))*1)>1,"#TBD",LOOKUP(2,1/ISNUMBER(FIND($B$2:$B$151,$D2)),$A$2:A$151)))
向下複製
[attach]20221[/attach]

作者: 准提部林 時間: 2015-2-6 21:12

模糊比對：
=CHOOSE(SUM(N(COUNTIF(B:B,"*"&D2&"*")>{0,1}))+1,"#not found",INDEX(A:A,MATCH("*"&D2&"*",B:B,)),"#TBD")

作者: oshi 時間: 2015-2-7 12:39

本帖最後由 oshi 於 2015-2-7 12:41 編輯

回復 oshi
無符合資料填入"#not found"
符合有1個以上則填入"#TBD"
僅1個符合則填入A欄對應值
是這樣 ...
Hsieh 發表於 2015-2-6 18:20

Hsieh板大~
我那天可能說明的不是很清楚QQ
大概是這樣~
原本的作法是
1.如果有1個符合填入A欄相對值
2.如果有多數符合，先依序填入A欄相對值,多餘的填入"#TBD"
3.如果沒有符合，填入"#not found"

目前想要再多加一個功能是
對於顯示#not found 的儲存格想要讓D欄模糊比對B欄的值
如果用vlookup的模糊比對只會找到第一項

剛剛使用您的式子
之後變成
1.如果有1個符合填入A欄相對值 =>被填0
2.如果有多數符合，先依序填入A欄相對值,多餘的填入"#TBD" =>全部填"#TBD"
3.如果沒有符合，填入"#not found" =>毫無反應Q_Q 還是填#not found

我用直接下拉的方式跟用ctrl+shift+enter的結果都是這樣Q_Q

不過可以請問一下Hsieh板大用這些公式的思緒是什麼嗎?我覺得好像接近我想要的方式了

感謝

作者: oshi 時間: 2015-2-7 14:35

模糊比對：
=CHOOSE(SUM(N(COUNTIF(B:B,"*"&D2&"*")>{0,1}))+1,"#not found",INDEX(A:A,MATCH("*"&D2&"*", ...
准提部林發表於 2015-2-6 21:12

准提部林大我Test您的公式結果會有些原本填正確的值卻被填入#TBD
我猜測是因為您用"*"&D2&"* 導致有些類似的儲存格被判定錯誤

[attach]20227[/attach]

作者: oshi 時間: 2015-2-7 14:39

模糊比對：
=CHOOSE(SUM(N(COUNTIF(B:B,"*"&D2&"*")>{0,1}))+1,"#not found",INDEX(A:A,MATCH("*"&D2&"*", ...
准提部林發表於 2015-2-6 21:12

准提部林大您用萬用字元去模糊收尋的方式我有試過
可是因為資料裡面有些時候是D2的字元較長

比方說是
我本來的意思是要讓AAA_1可以模糊對到 AAA

這樣d2收尋的時候就會變成 *AAA_1*

AAA就被判定不是類似的字串Q_Q

歡迎光臨麻辣家族討論版版 (http://forum.twbts.com/)