網友問到:在 Excel 中,如果有一個名字組成的清單,如何藉由輸入特定文字而列出含有該文字的清單?

參考下圖,在下拉式清單中選取不同的文字,即可列出含有該文字的姓名。

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

 

【公式設計與解析】

相關說明可以參考以往所寫的文章:

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

Excel-根據清單中列出含有指定字元的資料(OFFSET,SUBSUTITUTE)

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

Excel-列出含有某字的資料(陣列)

首先,選取儲存格A1:B200(名字清單的範圍),按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:編號、姓名。

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

接著,輸入公式:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(SUBSTITUTE(姓名,$D$2,"")<>
姓名,ROW(編號)-1,""),ROW(1:1)),0),"")}

儲存格G2:{=IFERROR(VLOOKUP(F2,資料,2,FALSE),"")}

以上二式都是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格F2:G2,貼至儲存格F2:G30。

其中公式:SUBSTITUTE(姓名,$D$2,"")<>姓名

乃在姓名陣列中,判斷如果將姓名以儲存格D2的內容置換為空字串(相當於刪掉該文字),如果結果和原來姓名不相同,則表示該姓名含有該文字。

如果選取不同的字元,清單會隨之改變列出的內容:

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

該公式不限查詢的字元數:

Excel-列出資料中含有指定文字的清單(SUBSTITUTE,OFFSET)

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel SUBSTITUTE OFFSET
    全站熱搜

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