贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

【準備工作】

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

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

【公式設計與解析】

儲存格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 會自動產生「{}」。

創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(9) 人氣()


留言列表 (9)

發表留言
  • eastyip
  • 你好,謝謝你的分享。
    想請問如果在E2 輸入英文或中文也可搜尋的話,除了用IF 的巢狀結構來解決外,還有其他方法嗎?
    感謝。
  • CT
  • 請問如果想在一陳列中如下:在b列中要找到有"助理"的字,傳回在儲存格,沒有助理則不用出任何字眼. 應該如何處理?謝謝
    1 B C
    2 資料 結果
    3 主管
    4 文員
    5 工人
    6 助理主管 助理
    7 主管
    8 文員
    9 工人
    10 文員
    11 助理文員 助理
    12 主管
    13 文員
    14 主管
    15 助理工人 助理
  • 請參考:http://isvincent.pixnet.net/blog/post/46809895

    vincent 於 2016/11/15 20:26 回覆

  • 你好
  • 你好 請問如果是班表,要在G欄輸入班別就可以看H欄有哪些姓名,應該如何處理?
    謝謝
    A欄姓名 B欄班別 G H
    xxx o o xxx zzz
    yyy c
    zzz o
  • 感恩
  • 您好,謝謝教導,受益頗多。
    想請教...
    在您舉的例子中,如果"含有"的字根有數個。
    例如:要含有"oo"或"th",都要列出的話,應該怎麼做?
  • 請參考這篇:http://isvincent.pixnet.net/blog/post/47169061

    vincent 於 2017/04/09 23:40 回覆

  • shiau944
  • 你好!!!
    請問,
    ROW(序號)
    可以用EXCEL 本身的儲存格位址嗎?
    公式怎寫呢??
    不想再設一個序號欄
  • ROW(A1)=1、ROW(B1)=1、....,ROW函數是取其列號。不用輔助欄位,就改用以上的做法。

    vincent 於 2017/09/17 19:48 回覆

  • Harriet
  • 你好:
    我以-- Sep 14 2015
    Excel-列出含有特定文字的清單(FIND,ROW,SMALL,陣列公式)
    做所有步驟的模擬,但結果只顯示找到的第一筆,為何無法顯示符合的全部筆數?
    請指正,謝謝!
  • 邱少允
  • 請問原始資料是兩欄以上(例如第一批欄A序號、欄B英文、欄C中文,第二批欄D序號、欄E英文、欄F中文),如何能傳回的值是包含這兩批範圍內的值,勞煩了。
  • jason0856317
  • 您好
    我有照您的範本做練習打了一次
    但是發生了一些問題
    想請教 當我在E2 收詢含有 a的字母
    卻無法出現book bag
    且H、I欄位 其他空格 沒有收詢結果的情況下會出現
    英文、中文
    想請問一下 我是不是有打錯
    感謝

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼