錦子老師

下面這張表格是一位讀者所提的問題，由於資料筆數有數十萬筆諆以下圖簡略資料詢問，如果說工作表左邊是產品型號及零件的對照表，而右邊則是欲查詢型號，要用什麼方法可以對應到所屬的零件。

這個問題談到最後還是只有一個答案，使用Excel函數。

1.點取A4儲存格輸入公式「=LEN(SUBSTITUTE(B4,"*",""))」後，按ENTER鍵完成輸入。

【公式說明】

LEN(SUBSTITUTE(B4,"*",""))是說先用SUBTITUTE將B4儲存格中的星號去掉，再用LEN函數去計算其資料長度。

這個是為了保證函數在搜尋的時候找到那個"最符合"的那筆資料，假設有個CSS12**以及CSS1234那麼用CS1234搜尋的時候，有可能會配對到CSS12**，這時候就是我們不希望的，所以要去除星號後的資料表這樣才能比對到最符合的那筆資料。

2.將A4儲存格公式複製到A5:A12。

3.框取A4:A12儲存格範圍，再取「資料 > 編輯 > 排序與篩選」倒三角鈕，選擇「從最小到最大排序」選項，如下圖所示，當然就是排序拉，由小到大。

4.點取E4儲存格輸入公式「=LARGE(IF(ISERROR(SEARCH($B$4:$B$11,F4)),0,1)*ROW ($B$4:$B$11),1)」後，按Ctrl+Shift+Enter完成輸入，如下圖所示。

【注意】

這是陣列公式，輸入完後要有看到Excel幫你在公式兩側加入{ }這樣的大括號才是陣列公式。

【公式說明】

因為這是陣列公式，用一個例子來說明一下，假設要找 ASS200那麼SEARCH($B$4:$B$11,F5)函數會找出{0,1,0,0,0,0,0,0}，回傳的資訊會是一個陣列，而不是單純的數值。

然後再去乘以 ROW()函數，因為ROW內容也是那個範圍的列號，所以實際上列是{4,5,6,7,8,9,10,11}，將這兩個互乘就變成陣列 {0,5,0,0,0,0,0,0}，因為1是代表有比對到，那ROW是範圍的列號陣列，所以會剔除沒有比對到的列號0。

最後再用LARGE函數或MAX函數取出最大的那個數，就是比對到的列號。

簡單說：

SEARCH() => {#ERR!,1,#ERR!,#ERR!,#ERR!,#ERR!,#ERR!,#ERR!}

IF(ISERROR()) => {0,1,0,0,0,0,0,0}

ROW() => {4,5,6,7,8,9,10,11}

互乘後 => {0,5,0,0,0,0,0,0}

LARGE(或MAX) =>5 => 列號

5.將E4儲存格公式複製到E5:E12儲存格。

6.點取G4儲存格輸入公式「=INDIRECT(ADDRESS($E4,3))」後，按ENTER鍵完成輸入。

7.將G4儲存格公式複製到G5:G12儲存格。

8.點取H4儲存格輸入公式「=INDIRECT(ADDRESS($E4,4))」後，按ENTER鍵完成輸入。

9.將H4儲存格公式複製到H5:H12儲存格，如下圖所示。

【公式說明】

ADDRESS($E4,3)表示要抓取E4儲存格內容的列號與第3欄所代表的儲存格，即為C4儲存格。

INDIRECT(ADDRESS($E4,4))表示要抓取C4儲存格的內容。