學不完．教不停．用不盡

有網友問到在 Excel 中有一個如下圖左的資料表，如果在儲存格E2中輸入某些字根，要自動列出含有這些字根的清單(如下圖左)。該如何處理？

【準備工作】

選取基本資料表的所有儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：序號、英文、中文。

【公式設計與解析】

儲存格G2：｛=IFERROR(SMALL(IF(ISNUMBER(FIND($E$2,英文)),
ROW(序號),FALSE),ROW(1:1))-1,"")｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。

FIND($E$2,英文)：在「英文」儲存格陣列中尋找是否包含儲存格E2的內容，如果有包含，則傳回一個數字(位置)，如果沒有包含，則傳回錯誤訊息 #VALUE!。

ISNUMBER(FIND($E$2,英文))：判斷 FIND($E$2,英文) 是否傳回數字，得到一個 TRUE／FALSE 的陣列

IF(ISNUMBER(FIND($E$2,英文)),ROW(序號),FALSE)：如果上式傳回數字，則由 ROW(序號) 傳回所在列的編號，否則傳回 FALSE。

SMALL(IF(ISNUMBER(FIND($E$2,英文)),ROW(序號),FALSE),ROW(1:1))-1：使用 SMALL 函數藉由 ROW(1:1)=1 找到對應的最小值。如果公式向下複製時，ROW(1:1)=1 → ROW(2:2)=2→ ROW(3:3)=3、...，可以取得由小到大的數值(該數字為序號)。如果 ROW 函數的數字已超過序號陣列的範圍，則會傳回錯誤訊息。

最後使用 IFERROR 函數將 SMALL 函數傳回的錯誤訊息以空白顯示。

儲存格H2：=IFERROR(OFFSET($B$1,G2,,,),"")

儲存格I2：=IFERROR(OFFSET($C$1,G2,,,),"")

以上二式藉由所找到的序號(儲存格G2)，在 OFFSET 函數中對應得到一個英文字和中文字。

這是個實用的工具，試著輸入各種要查詢的文字，在背英文單字時，可能會用到。

 

【後記】

如果你的 Excel 版本無法使用 IFERROR 函數，則可以使用 ISERR 函數來處理。

儲存格G2：｛=IF(ISERR(SMALL(IF(ISNUMBER(FIND($E$2,英文)),
ROW(序號),FALSE),ROW(1:1))-1),"",SMALL(IF(ISNUMBER(FIND($E$2,
英文)),ROW(序號),FALSE),ROW(1:1))-1)｝

上式=｛IF(ISERR(SMALL(公式),"",SMALL(公式))｝

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 會自動產生「｛｝」。