iT邦幫忙

0

EXCEL的搜尋字串問題

我建立了一個資料庫sheet,當中A欄是數值、B欄是資料庫資料(B欄全列中無重複單字),現在我想要在搜尋的表單當中,在A1打一個單字,讓他搜尋資料庫sheet的B欄後,只要吻合B欄字串的其中一個單字,就回傳同列的A欄數值,並在搜尋表單sheet當中的A2顯示數值......

例如:
這是資料庫
資料庫狀態↑

我要用以搜尋的表單↓
我想要的效果

我嘗試使用網路很多部落格、文章所教學的LOOKUP還有SEARCH,每次都錯誤,求如何使用我想研究一下……為什麼明明看起來這麼簡單我都沒辦法弄出來QQ

謝謝各位OTZ

圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中
2
蟹老闆
iT邦大師 1 級 ‧ 2017-07-06 02:25:14
最佳解答
=OFFSET(工作表1!A1,MATCH("*"&A1&"*",工作表1!B:B,0)-1,0,1,1)

在A1輸入單字在B1輸入以上函數, "工作表1" 請依實際修改
如資料庫每個字串字尾皆有,號,則應在"*"&A1&"*" 加上逗號("*"&A1&",*"),如沒有一致的固定格式則有可能發生錯誤,如dream與dreamer

看更多先前的回應...收起先前的回應...

老闆真是為國辛苦了,兩點多還沒睡
/images/emoticon/emoticon28.gif

froce iT邦大師 1 級 ‧ 2017-07-06 08:24:01 檢舉

少一個派大星。XD

我本來想用
VLOOKUP "*"&A1&"*" 或者 "*"&A1&",*" 
混過去就算了
可是看到有
Joan 和 Joanna 這種情形
若用逗號來區分又不是所有單字尾巴都有逗號
想想還是蓋牌
這把我不去了
shikagari iT邦新手 5 級 ‧ 2017-07-06 11:27:13 檢舉

謝謝兩位回答,多學到了""&的用法,感激不盡!(超好用!!)

因為我這樣的表格想說公式一次學起來就不用再多問了(可以用來查名字/分組、我想要的姓氏/起源地,還有這次我想舉例的查部首/筆劃),不過像是海綿寶寶大說的,Joan和Joanna利用VLOOKUP會出錯(逗號倒是結尾都可以加沒關係,因為資料庫和搜尋表格我想放在不同頁)......

那麼這樣的公式(OFFSET和VLOOKUP)放在搜尋不重複的中文字上會出錯嗎?
例如說我已經歸納文字筆劃、部首的狀態,想要搜尋文字、輸出數值(筆劃數、部首);像這樣:
舉例

(資料庫中,為了方便VLOOKUP的尋找,我把A列、C列都設成我要的數值,B列則為資料庫)

我是了蟹老闆大的公式,結果變成這樣:
失敗例子1

覺得不太甘心又用了一欄使用VLOOKUP,「=VLOOKUP(""&A5&"",【查詢】筆劃字庫!B1:C36,2,0)」、或者放在同一張sheet裡面,「=VLOOKUP(""&A44&"",B1:C36,2,0)」,但是顯示出來都是「#N/A」。

再次懇求求健檢,感謝!

蟹老闆 iT邦大師 1 級 ‧ 2017-07-06 17:07:30 檢舉

海綿寶寶人的年紀到了一定,就會睡不覺/images/emoticon/emoticon10.gif

shikagari
假設資料庫 Sheet 是 "筆劃字庫" 應該改成這樣

=OFFSET(筆劃字庫!A1,MATCH("*"&A1&"*",筆劃字庫!B1:B36,0)-1,0,1,1)

那麼這樣的公式(OFFSET和VLOOKUP)放在搜尋不重複的中文字上會出錯嗎? <--若有固定格式,不會出錯
例外狀況:若要查詢的字串包含分隔符號(,)就可能有錯

shikagari iT邦新手 5 級 ‧ 2017-07-09 09:05:25 檢舉

謝謝你的回答~~!
在使用OFFSET以前我有稍微摸出了比較笨的方法,就是在資料庫當中先開一欄使用IFERROR(IF(FIND))的方式,先判定是否有某字,有的話就標一個符號(例如O或X),然後在使用VLOOKUP進行判定,搜尋出有該符號的那一列的數值欄位,就可以進行我要的判斷。

實際使用後發現OFFSET很方便,不過要熟悉還得靠自己多練習,謝謝你的回答!

2
q00153
iT邦新手 3 級 ‧ 2017-07-06 11:56:45

!!注意!! 這是陣列公式,輸入完要按 Ctrl + Shift + Enter 才行

{=INDIRECT(ADDRESS(MATCH("*,"&D2&",*",","&SUBSTITUTE(B1:B6," ","")&",",0),1))}

http://ithelp.ithome.com.tw/upload/images/20170706/20092441P5jpBQ0IZ4.png

shikagari iT邦新手 5 級 ‧ 2017-07-09 09:08:10 檢舉

謝謝你的回答,第一次知道了INDIRECT、ADDRESS的用法,雖然還沒有很仔細進行研究,只是依照線索跑去OFFICE函數那邊看怎麼使用、但還沒有實際操作過(因為後來自己摸出了很笨的方法),也學到了陣列公式的輸入法...

很抱歉最佳解答只能選一個,但還是非常感謝你的熱心QQ有學習道東西真的太感謝了!

0
albert0723
iT邦見習生 ‧ 2021-04-27 21:41:00

大家在此的解答都很好, 不過, 我和shikagari有一樣的困擾, 就是:
不論式offset+match, 還是indirect+address都只能找到一筆, 而我們需要的是符合條件的多筆結果, 大家能在幫忙想想辦法嗎? 謝謝各位!

我要發表回答

立即登入回答