學習Excel技術,關注微信公眾號:
excelperfect
在《Excel公式技巧58:在句子中查找出現了多少個指定的單詞》中,我們使用FIND函數可以查找並獲得多個單詞在句子中出現的起始位置。本文將以此為基礎,查找句子中是否存在指定列表中的單詞,獲取並列出所有找到的單詞。如下圖1所示。
圖1
在單元格D3中輸入數組公式:
=IFERROR(INDEX(NameList,SMALL(IF(ISNUMBER(FIND(NameList,$C3,1)),ROW(NameList)-2),COLUMNS($D$2:D2))),"-")
向右向下拖拉至合適的單元格為止。
公式中的「NameList」是定義的名稱,代表單元格區域B3:B12。
正如上篇文章中講解的,公式中的:
FIND(NameList,$C3,1)
在單元格C3中分別查找名稱NameList中的單詞,返回其出現的起始位置,如果沒有找到,則返回錯誤值#VALUE!,即生成數組:
{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;10;#VALUE!}
代入ISNUMBER函數,生成一個由TRUE/FALSE值組成的數組:
{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE}
公式中的:
ROW(NameList)
生成由名稱所在單元格的行號組成的數組:
{3;4;5;6;7;8;9;10;11;12}
減去2後得到:
{1;2;3;4;5;6;7;8;9;10}
而COLUMNS($D$2:D2)的結果為1,當公式向右拖拉時,會根據公式所在單元格與列D相隔的距離得到相應的數字。
這樣,單元格D3中的公式可解析為:
=IFERROR(INDEX(NameList,SMALL(IF({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE},{1;2;3;4;5;6;7;8;9;10}),1)),"-")
IF函數運行後,轉換為:
=IFERROR(INDEX(NameList,SMALL({1;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;9;FALSE},1)),"-")
SMALL函數運行後,轉換為:
=IFERROR(INDEX(NameList,1),"-")
INDEX函數獲取NameList中的第1個值,即:
Alka
技巧:利用ROW函數獲取值列表所在的行號,與查找到的結果相對應,從而獲取相應的值。
註:本文學習整理自Chandoo.org論壇。
歡迎在下面留言,完善本文內容,讓更多的人學到更完美的知識。
歡迎到知識星球:完美Excel社群,進行技術交流和提問,獲取更多電子資料。
完美Excel社群2020.10.16動態
#VBA# Excel編程周末速成班第3課:Excel對象模型
主要內容:理解屬性和方法;對象引用的重要性;使用集合;對象層次模型;Workbook對象常用屬性和方法;Worksheet對象常用屬性和方法
預告:Excel編程周末速成班第4課:VBA的語法和數據