close

Q18

EXCEL的搜尋字串問題

示範檔

範例檔

Q18.XLSX

結果檔

Q18F.XLSX

錦子老師您好:

我建立了一個資料庫在「工作表1」,當中A欄是數值、B欄是資料庫資料(B欄全列中無重複單字),如下圖所示。

現在我想要在搜尋的表單當中,在F2儲存格打一個單字,讓他搜尋資料庫的B欄後,只要吻合B欄字串的其中一個單字,就回傳同列的A欄數值,並在搜尋表單當中的G2儲存格顯示數值......

我嘗試使用網路很多部落格文章所教的LOOKUP函數、SEARCH函數,每次都顯示錯誤。為什麼明明看起來這麼簡單我都沒辦法弄出來?謝謝。

方法1OFFSETMATH函數

使用上述二個函數來處理,首先我們要改動資料,必須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個儲存格。

方法2INDIRECTADDRESSMATH函數

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)):顯示儲存格內容。


arrow
arrow
    創作者介紹

    錦子老師 發表在 痞客邦 留言(0) 人氣()