變了,它才是2021年Excel查找函數的No.1!

2021-02-13 Excel實用技巧

如果要評選功能最強大的查找函數，肯定有很多人會把Vlookup、lookup或xlookup放在前三把交椅。其實，有一個函數雖然名氣不足，實力上卻可以無情碾壓它們，它就是---Filter函數。

filter是Excel365新增的篩選函數，除了單條件、反向查找、多條件查找外，還可以實現其他查找函數很難或無法實現的查找功能：

一對多查找

合併查找

查找任意位置

從後向前任意位置查

批量查找


實例1：一對多查找（篩選）

=FILTER(A2:C31,B2:B31=G1)

實例2：一對多查找（結果合併）

=TEXTJOIN(",",,FILTER(C$2:C25,B$2:B25=F2))

實例3：查找第N次消費金額

=INDEX(FILTER(C$2:C25,B2:B25=F2),N)

如查找第2次

=INDEX(FILTER(C$2:C25,B2:B25=F2),2)

實例4：查找最後一次消費金額

=INDEX(FILTER(C$2:C25,B$2:B25=F20),COUNTIF(B:B,F20))

實例5：查找倒數第N次消費金額

=INDEX(FILTER(C$2:C25,B$2:B25=F20),COUNTIF(B:B,F20)-(N-1))

如例數第2次

=INDEX(FILTER(C$2:C25,B$2:B25=F20),COUNTIF(B:B,F20)-1)


實例6：返回所有客戶第一次消費記錄

=FILTER(A2:C25,MATCH(B2:B25,B2:B25,0)=ROW(B1:B24))

實例7：批量查找姓名

=FILTER(D2:D19,E$2:E$19=A2)

Filter函數的第2個參數，可以聯合其他函數設置更複雜的條件，以應對更加繁雜的需求，這個優勢也讓filter函數在Vlookup這些老前輩面前顯得無比強大。

相關焦點

  • 比 Sum 利害十倍,它才是Excel中的萬能函數
    有一個函數，它隸屬Sum家族，卻比Sum好用十倍。原本它只是一個很簡單的一個分組乘積後再求和的函數，後被高手挖掘出了很多擴展用法。
  • 你會用Countif函數嘛?據說它才是Excel函數中的NO.1
    COUNTIF函數，是對指定區域內滿足特定條件的單元格進行計數統計。語法表達式是：COUNTIF(區域，判斷條件)下面我們就來看幾個常見的使用場景，來了解一下該函數在Excel當中的運用。如下圖所示，我們要統計，市場部和運營部兩個部門的人數，那麼我們就可以在空白單元格內錄入函數公式：=COUNTIF($C$3:$C$14,H3)該函數表示，統計C3到C14單元格當中，單元格內內容為H3的個數，並對他們進行求和。計算出第一個結果之後，我們再往下進行填充，那麼即可得到運營部的人數。
  • Excel中字符串包含查找的實現1(FIND函數一對一)
    在講「一對多」查找之前，我們有必要先了解一下簡單的字符串查找：「一對一」查找。     例：如下圖所示。A列是包括省名的地址，要求在B列判斷是否為「河南」省的地方。    分析：     1 在excel裡判斷字符串是否包含另一個字符的是FIND函數，另外也可以用COUNTIF函數的模糊查找實現，這裡我們介紹FIND函數的應用。
  • excel查找函數-vlookup
    大家都會想到 VLOOKUP、LOOKUP等熟悉的查詢函數，但是對於剛入接觸或學習excel的新手不知道怎麼操作，今天分享關於查詢問題通過下面例子進行演示，如下：關鍵詞：vlookup、IFERROR、反方向查找=VLOOKUP（查找目標，查找範圍，返回值的列數，精確OR模糊查找) =IFERROR(值,錯誤值)=VLOOKUP(E3,$A$3:$C
  • VLOOKUP函數滾一邊去,我才是Excel真正的查找之王!
    6;9})，返回9也就是說，Lookup函數查找到最後一個滿足條件的值，在數字不確定的情況下，查找的值越大也能保證查找到的值得準確性。VLOOKUP函數很好用，如果要逆序查找，也就是從右到左，就相對比較麻煩。傳說中可以借用IF({1,0},,)組合來實現，不過要花費九牛二虎之力，吃力不討好。這時他的兄弟LOOKUP函數就派上用場，藉助這個函數卻能輕而易舉就辦到。LOOKUP函數不區分正常順序跟逆序，用在這裡再合適不過。
  • Excel vlookup篩選兩列的重複項與查找兩個表格相同數據
    Vlookup函數可用於多種情況查找，篩選重複數據就是其中之一，它既可篩選兩列重複的數據又可查找兩個表格相同的數據。
  • 【Excel函數貼】五個技巧性函數小套路
    今天給大家分享5個常用的函數小套路，都比較簡單，過一遍，多少能用上。1，字母大小寫一個做外貿的朋友問，Excel有沒有函數可以把英文從小寫變大寫？他可能碰到蠻多洋人的人名或者貨名需要大小寫轉換的。小寫轉大寫：=UPPER("excel")大寫轉小寫：=LOWER("EXCEL")只要首字母大寫，比如excel轉成Excel：=PROPER("excel")  2，
  • 一個隱藏10多年的Excel功能,今天才發現它竟然是一個神器
    但有一個功能可以絕大部分用戶根據不知道它的存在，它就是一個被隱藏了十幾年的功能：記錄單記得蘭色剛學excel時，這個功能已不在Excel工具欄裡，只能通過添加的方式使用它。今天我們依然可以找到它：文件 - 選項 - 自定義功能區 - 所有命令 - 記錄單。你要以把它添加到自定義選項卡裡，然後就可以使用了。
  • 比 Vlookup 好用10倍,它才是Excel函數中的NO.1
    Countif函數。步驟1 在兩列數據旁添加公式，用Countif函數進行重複轉化。【例】如下圖所示左表為客戶消費明細，要求在F:H列的藍色區域根據F2的客戶名稱查找所有消費記錄。步驟1 在左表前插入一列並設置公式，用countif函數統計客戶的消費次數並用&連接成 客戶名稱+序號的形式。
  • Excel公式技巧16: 使用VLOOKUP函數在多個工作表中查找相匹配的值(1)
    學習Excel技術，關注微信公眾號：excelperfect 在某個工作表單元格區域中查找值時，我們通常都會使用
  • Excel中的數據匹配和查找
    VLOOKUP的基本使用先看一個示例，在B2:D9區域有一張員工薪資表，G2單元格可以輸入員工的First Name，希望可以在G3單元格查找出相應員工的具體薪資。這時候可以使用VLOOKUP函數來進行查找匹配。
  • Excel查找函數:Hlookup函數的學習!
    Excel情報局生產搬運分享Excel基礎技能用1%
  • Excel查找函數
    1、查找字符FIND函數概念：返回一個字符串在另一個字符串中出現的起始位置(區分大小寫, 不支持通配符)語法：=FIND(子字符串,母字符串,起始位置)2、查找字符FINDB函數概念：在一文字串中搜索另一文字串的起始位置,與雙字節字符集(DBCS)一起使用(區分大小寫, 不支持通配符)語法：=FINDB(子字符串,母字符串,起始位置)基礎應用如下圖，在字符串【Excel公式與技巧可以學習EXCEL公式】中查找【公式
  • EXCEL查找函數【LOOKUP】和【HLOOKUP】
    簡單點說，LOOKUP的功能是根據特定的值進行模糊查找；     VLOOKUP的功能是根據特定的值在指定的列進行精確匹配，並返回對應列的值，同時兼具LOOKUP功能；    excel給出的LOOK UP函數語為：LOOKUP(lookup_value,lookup_vector,result_vector)
  • 一大群Excel多條件查找函數來了!
    對於Vlookup函數來說，最大的短板就是不支持多條件查找。
  • Excel中字符串包含查找的實現(FIND函數一對一)
    分析： 1 在excel裡判斷字符串是否包含另一個字符的是FIND函數，另外也可以用COUNTIF函數的模糊查找實現，這裡我們介紹FIND函數的應用。 FIND函數的基本語法為： FIND(查找的字符串，在哪個字符串裡查找,從第幾位開始查找） 注意：FIND函數返回的是子字符串在被查找字符串中的位置，如在"ABCD"裡查找B，FIND("B","ABCD")得到的結果是B的位置2，如果查找不到,比如FIND("E","ABCD"）則返回錯誤值"#VALUE".
  • 只會Vlookup函數Out了!Excel所有查找公式全在這兒!
    查找3月辦公費的金額=VLOOKUP(H2,A:F,MATCH(I2,1:1,0),0)查找上海產品B的銷量其實原理和跨多表查找一樣，也是藉助lookup等函數實現。/COUNTIF(INDIRECT("["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1!
  • 會計人,你會用Countif函數嘛?據說它才是Excel函數中的NO.1
    步驟1 在兩列數據旁添加公式，用Countif函數進行重複轉化。如下圖所示禁止在G列重複錄入數據：數據 - 有效性（2016版為數據驗證） - 序列 - 輸入公式=COUNTIF(G:G,G1)=1=COUNTIF(H1,"*A*")=1如果輸入不含A的字符就會警示並無法輸入
  • [Excel] 如何優雅的查找,史上最容易學會的index+match教程!
    但vlookup終究不夠優雅、不夠簡潔高效，今天要學的index+match組合，絕對是excel裝逼界的一把好手！index函數和match函數的組合用法，就可以做到。 那我們就從第一個格子開始，到數據源表中查找「Frank」同學的「部門」。
  • VLOOK函數--查找函數
    HLOOK函數和VLOOKUP幾乎一樣咯，一個是列查找一個是行查找。