Excel公式技巧59:批量查找並返回找到的內容

2021-02-15 完美Excel

學習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的語法和數據

相關焦點

  • Excel公式使用技巧大全
    最近蘭色陸續收到很多關於excel公式操作的提問,於是乎索性把和excel公式相關的操作來一次大整理,好象你在網上還查不到這麼全的
  • Excel多條件計數技巧,返回多個符合條件的值,批量操作超輕鬆
    今天跟大家分享一下Excel多條件計數技巧,返回多個符合條件的值,批量操作超輕鬆。如果覺得幫幫真的幫到了您,分享分享朋友圈呀,親們^^<——非常重要!!!//這裡我們想要對符合條件的員工進行提取,這也是前兩天用到的公式=LOOKUP(1,0/((B2:B14="生產部")*(D2:D14<>"")),A2:A14
  • 所有的Excel 公式用法.....幫你整理齊了!(共13種)
    excel公式操作的提問,於是乎索性把和excel公式相關的操作來一次大整理,好象你在網上還查不到這麼全的,嘿嘿!建議收藏分享。批量輸入公式批量修改公式快速查找公式顯示公式部分計算結果保護公式隱藏公式顯示所有公式把公式轉換為數值顯示另一個單元格的公式把表達式轉換為計算結果快速查找公式錯誤原因輸入和取消數組公式插入行後自動複製上一行公式1批量輸入公式選取要輸入的區域,在編輯欄中輸入公式
  • Excel中的數據匹配和查找
    G2是要查找的內容,B3:D9是查找區域(注意第一列一定是First Name),3是Salary在查找區域的列號,False表示精確匹配。 第一步,找到一個可以區分大小寫的函數EXACT。,並保存在excel內存中。
  • Excel怎樣查找重複內容或不同的內容?
    工作中,我們需要對excel工作表中的重複值進行核對,利用excel條件格式功能,可輕鬆快速解決這類問題。
  • Excel公式技巧55:查找並獲取最大值/最小值所在的工作表
    學習Excel技術,關注微信公眾號:excelperfect 在《Excel公式技巧54:在多個工作表中查找最大值最小值
  • 16個excel實戰小技巧
    3 怎樣讓首行 分頁列印後都有頁面布局 - 列印標題 - 頂端標題行,選取行區域4 怎麼批量清除EXCEL左上角的綠色.全選含綠色三角的區域 - 打開綠三角 - 點忽略錯誤或轉換為數值(列表中的選項一個不行再試另一個,生成的原因不同,點的選項也不同)5 excel當天日期函數=today() 返回當天的日期=now() 返回現在的時間和日期6 EXCEL表格裡可以選擇顏色篩選嗎?excel2010裡是可以按顏色篩選的,如下圖所示。
  • Excel公式技巧16: 使用VLOOKUP函數在多個工作表中查找相匹配的值(1)
    學習Excel技術,關注微信公眾號:excelperfect 在某個工作表單元格區域中查找值時,我們通常都會使用
  • Excel公式技巧01: 使用INDEX函數返回整行或整列
    ,可以返回一個值,然而,如果將其行參數或者列參數指定為0(或者忽略),那麼會返回對指定列或行的引用。注意,這裡返回的不是單個值,而是一組值。 如下圖1所示,公式:=INDEX(A1:D4,0,2)或者:=INDEX(A1:D4,,2) 返回數據區域A1:D4中的第2列,即單元格區域B2:B4。
  • Excel格式刷,很多人都用錯了!如何利用格式刷實現合併單元格時保留每一個格的內容和批量設置單元格填充色案例教程
    複製格式對於Excel格式刷來說,基本上就是牛刀小試,真正逆天的使用方法還多著呢,今天小編excel小課堂(ID:excel-xiaoketang 長按複製)就帶大家看看如何利用格式刷實現合併單元格時保留每一個格的內容和批量設置單元格填充色合併單元格時保留每一個單元格的內容合併單元格時,默認情況下只保留第一個單元格的值,但自從有了格式刷
  • Excel VBA解讀(41):藏得再好也能找到——使用Find方法實現查找
    原因為:雖然Find方法默認為按行查找,但由於之前我運行了代碼testFind2,而在這裡的代碼中沒有指定參數SearchOrder,所以Excel仍按之前的代碼設定的參數運行,即修改為按列查找,所以先找到單元格A2。Find方法的參數LookAt的默認值為xlPart,即只要單元格中的部分內容與所查找的內容相匹配就認為已經找到了單元格。
  • excel查找函數-vlookup
    大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數,但是對於剛入接觸或學習excel的新手不知道怎麼操作,今天分享關於查詢問題通過下面例子進行演示,如下:關鍵詞:vlookup、IFERROR、反方向查找=VLOOKUP(查找目標,查找範圍,返回值的列數,精確OR模糊查找) =IFERROR(值,錯誤值)=VLOOKUP(E3,$A$3:$C
  • 說說Excel公式中的小星號
    說它身份獨特,是因為星號(*)除了表示運算符乘號,還具有通配符的身份,用來表示任意多個字符。與之對應的還有半角的問號「?」,問號也是通配符的一種,表示的是單個字符。既然身份特殊,就要有特殊的處理規則。如下圖所示,需要將單元格中的星號(*)批量替換為「待評估」。
  • 【Office小技巧】Excel文本查找函數FIND/SEARCH使用小技巧
    在Excel中經常會碰到查找文本/內容等等需求,很多小夥伴可能都還在手動進行判斷查找,今天芒種君來分享Excel中的文本查找函數FIND和SEARCH使用小技巧,最後附上通配符小技巧,讓查詢替換更加高效輕鬆。
  • 對Excel批量操作,提升你的工作效率提升8倍以上(一))
    excel變亂碼的數字如何恢復為數字? 辦公室最常用Excel函數公式大全,學會了工作得心應手Excel表格如何計算特定數值範圍個數我們在日常進行進行Excel表格計算工作中,遇到的最多就是涉及到的內容較多的查找、計算時,如果一個個的去查看,工作效率會非常的慢和容易出現差錯,這時進行Excel
  • Word查找替換技巧——批量刪除文檔中空格、空行
    1、批量刪除空格(1)點擊【開始】-【編輯】-【替換】按鈕或按【Ctrl+H】組合鍵,打開「查找與替換」對話框,將滑鼠光標定位於「查找內容」文本框中,然後點擊「更多」按鈕。(2)展開「替換」欄,點擊「特殊格式」按鈕,在彈出菜單中選擇「空白區域」選項。
  • Excel vlookup篩選兩列的重複項與查找兩個表格相同數據
    篩選兩列重複數據時,不僅僅是返回一項重複數據,是把所有重複的都標示出來;查找兩表格相同數據時,兩個表格既可以位於同一Excel文檔,又可分別位於兩個Excel文檔,並且也可以標示出所有重複的數據;當查找兩個位於不同Excel文檔中的表格相同數據時,查找範圍需要寫文檔名稱和工作簿名稱,這樣Excel才能找到查找區域。
  • Excel公式技巧54: 在多個工作表中查找最大值/最小值
    學習Excel技術,關注微信公眾號:excelperfect 要在Excel工作表中獲取最大值或最小值
  • 3個​反向查找公式【Excel分享】
    大家好,今天和大家分享「反向查找公式」,部分朋友在後臺留言,如何實現反向查找,其實反向查找公式有許多,下面我分享幾種,根據工號查找姓名
  • 如何用Excel批量查找數據?
    =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])=VLOOKUP(你想要查找的關鍵詞,查找區域,返回值在查找區域中對應列的序號,近似或精確匹配)幫助文檔可能是由英文直接翻譯過來的,理解起來感覺很「抽象」,上面的公式是按我的理解重新整理的