2015年9月14日 星期一

Excel-列出含有特定文字的清單(FIND,ROW,SMALL,陣列公式)

有網友問到在 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 會自動產生「{}」。

沒有留言:

張貼留言

檢視其他文章

好康東東