Q18 |
EXCEL的搜尋字串問題 |
||||
示範檔 |
無 |
範例檔 |
Q18.XLSX |
結果檔 |
Q18F.XLSX |
錦子老師您好:
我建立了一個資料庫在「工作表1」,當中A欄是數值、B欄是資料庫資料(B欄全列中無重複單字),如下圖所示。
現在我想要在搜尋的表單當中,在F2儲存格打一個單字,讓他搜尋資料庫的B欄後,只要吻合B欄字串的其中一個單字,就回傳同列的A欄數值,並在搜尋表單當中的G2儲存格顯示數值......
我嘗試使用網路很多部落格文章所教的LOOKUP函數、SEARCH函數,每次都顯示錯誤。為什麼明明看起來這麼簡單我都沒辦法弄出來?謝謝。
方法1:OFFSET與MATH函數
使用上述二個函數來處理,首先我們要改動資料,必須B欄的每一儲存格最後一個字後面加上英文的逗號「,」,否則無法判別會造成錯誤…
1.點取G2儲存格,輸入公式「=OFFSET(A2,MATCH("*"&F2&",*",B2:B8,0)-1,0,1,1)」後,按Enter鍵,結果如下圖所示。
MATCH
根據指定的比對方式,傳回一陣列中與搜尋值相符合之相對位置)
MATCH ( 搜尋值,比對範圍,符合條件 )
MATCH("*"&F2&",*",B2:B8,0):在B2:B8儲存格範圍搜尋F2儲存格的內容而且要符合條件。
OFFSET
計算根據所指定的儲存格位址、列距及欄距而算出相對應的參照位址
OFFSET ( 資料範圍,移動列數,移動欄數,涵蓋列數,涵蓋欄數 )
OFFSET(A2,MATCH("*"&F2&",*",B2:B8,0)-1,0,1,1):當MATCH("*"&F2&",*",B2:B8,0)找到資料時,傳回數值(N值),以A2儲存格為基準,設定要移動的欄/列數以及框選範圍包含幾欄幾列,本題為移動N列,0欄,只涵蓋1個儲存格。
方法2:INDIRECT、ADDRESS與MATH函數
1.點取G2儲存格,輸入公式「=INDIRECT(ADDRESS(MATCH("*,"&F2&",*",B1:B8,0),1))」後,由於是陣列公式故必須按Ctrl+Shift+Enter鍵,結果如下圖所示。
MATCH("*,"&F2&",*",B1:B8,0):在B1:B8儲存格範圍搜尋F2儲存格的內容而且要符合條件。
ADDRESS
根據指定的欄列數值,傳回代表儲存格位址的字串
ADDRESS(列號,欄號,傳回參照位址方式,參照位址表示方式,外部參照工作表名稱)
ADDRESS(MATCH("*,"&F2&",*",B1:B8,0),1):傳回A欄第幾列。
INDIRECT
根據單一儲存格所指定的參照位址,傳回被間接參照儲存格的內容
INDIRECT ( 單一儲存格位址,儲存格表示方式 )
INDIRECT(ADDRESS(MATCH("*,"&F2&",*",B1:B8,0),1)):顯示儲存格內容。
留言列表