Excel VBA解讀(53):高級篩選——AdvancedFilter方法

2022-01-10 完美Excel

微信公眾號:excelperfect

 

本文接著講解Excel的篩選功能。

在Excel的「數據」選項卡的「排序和篩選」中,單擊「高級」命令按鈕,會彈出「高級篩選」對話框,進行相應的條件設置後,會篩選出符合要求的數據。


這是一個強大的功能,尤其是在VBA中。

 

我們先使用Excel的錄製宏工具錄製一段進行高級篩選操作的代碼,初步窺探其基本功能。

為避免大量工作表數據對理解的影響,仍以簡單的數據工作表為例。只需了解基本原理,就可以在含有大量數據的複雜工作表中靈活應用。

 

如下圖所示的工作表。我們需要將列A中不重複的姓名提取出來,放置在列G中。

在Excel的高級篩選中,有兩種方法。

第一種:在「高級篩選」對話框的「方式」中選擇「將篩選結果複製到其他位置」,「列表區域」選擇單元格區域A1:A9,「複製到」選擇單元格G1,選中「選擇不重複的記錄」,單擊「確定」。

第二種:先在單元格G1中輸入標題「學生姓名」,然後在「高級篩選」對話框的「方式」中選擇「將篩選結果複製到其他位置」,「列表區域」選擇單元格區域A1:D9,「複製到」選擇單元格G1,選中「選擇不重複的記錄」,單擊「確定」。

下圖展示了這兩種方法的過程:


可以看出,兩種方法的結果相同。但是,如果預先設置了標題,則可以選擇整個數據區域。因為Excel在每次篩選操作後,都會記住前一次的區域選擇,所以預先設置標題後,就用不著每次都要選擇不同的列表區域了。

 

上述操作錄製的代碼如下:

Sub Macro1()

'

' Macro1 Macro

'

 

'

    Range("A1:A9").AdvancedFilterAction:=xlFilterCopy, CopyToRange:=Range("G1" _

        ), Unique:=True

End Sub

Sub Macro2()

'

' Macro2 Macro

'

 

'

    Range("A1:D9").AdvancedFilterAction:=xlFilterCopy, CopyToRange:=Range("G1" _

        ), Unique:=True

End Sub

 

從代碼中可以看出,參數Action設置為xlFilterCopy,表明將數據複製到由參數CopyToRange指定的區域,參數Unique設置為True,指定篩選不重複的數據。

 

下面,我們使用條件區域,篩選學生姓名為「張三」的數據記錄。如圖所示,條件區域為單元格區域F1:F2。在「高級篩選」對話框中選擇「將篩選結果複製到其他位置」,設置「列表區域」為A1:D9,「條件區域為「F1:F2」,複製到「H1」。單擊「確定」按鈕後的結果如圖。

 

上述操作錄製的代碼如下:
Sub Macro3()

'

' Macro3 Macro

'

 

'

    Range("A1:D9").AdvancedFilterAction:=xlFilterCopy, CriteriaRange:=Range( _

        "F1:F2"),CopyToRange:=Range("H1"), Unique:=False

End Sub

 

與本文開頭錄製的代碼相對,這次錄製的代碼中多了一個參數CriteriaRange,用來指定條件區域。

條件區域至少包含兩行,第一行包含一個或多個列標題,是想要在數據區域中篩選的內容,第二行包含的是想要獲取的數據。

 

AdvancedFilter方法的語法

AdvancedFilter方法用於基於條件單元格區域從數據表中篩選或者複製數據。語法如下:

Range對象.AdvancedFilter(Action,CriteriaRange,CopyToRange,Unique)

 

說明

上述參數中,除參數Action必需外,其他都可選。

參數Action指定一個XlFilterAction常量,表明是直接將結果篩選在數據表所在位置,還是在將篩選結果複製到指定位置。xlFilterInPlace指定在數據表所在位置放置篩選結果,xlFilterCopy指定將篩選結果複製到指定位置。

參數CriteriaRange指定條件區域。如果忽略該參數,那麼表明沒有條件。

參數CopyToRange指定在參數Action設置為xlFilterCopy時結果複製到的目標單元格區域位置。如果參數Action設置為xlFilterInPlace,則忽略該參數。

參數Unique用來指定是否僅複製唯一值(即不重複的值)。設置為True用來篩選不重複的數據記錄,設置為False用來篩選滿足條件的所有數據記

錄。默認值為False。

下圖直觀地表明了各參數的意義。

 

示例1:獲取不重複值

下面的使用純代碼完成上文中篩選不重複值的兩種方法。

代碼1:獲取不重複的學生姓名,不預先在G1中輸入標題。

Sub testAdvancedFilter1()

    Dim rngData As Range

    Dim rngResult As Range

    Dim lngLastRow As Long

   

    '查找數據區域中最後一行

    lngLastRow = Range("A" &Rows.Count).End(xlUp).Row

   

    '設置被篩選的數據區域

    Set rngData = Range("A1:A" &lngLastRow)

   

    '設置複製數據的目標區域

    Set rngResult = Range("G1")

       

    '篩選不重複的學生姓名

    rngData.AdvancedFilterAction:=xlFilterCopy, CopyToRange:=rngResult, Unique:=True

End Sub

 

代碼2:獲取不重複的學生姓名,預先在G1中輸入標題。

Sub testAdvancedFilter2()

    Dim rngData As Range

    Dim rngResult As Range

    Dim lngLastRow As Long

    Dim lngLastCol As Long

   

    '查找數據區域中最後一行

    lngLastRow = Range("A" &Rows.Count).End(xlUp).Row

    '查找數據區域中最後一列

    lngLastCol = Cells(1,Columns.Count).End(xlToLeft).Column

   

    '設置被篩選的數據區域

    Set rngData =Range("A1").Resize(lngLastRow, lngLastCol)

   

    '設置複製數據的目標區域

    Set rngResult = Range("G1")

   

    '設置標題

    Range("A1").CopyDestination:=Range("G1")

   

    '篩選不重複的學生姓名

    rngData.AdvancedFilterAction:=xlFilterCopy, CopyToRange:=rngResult, Unique:=True

End Sub

 

高級篩選應該是獲取不重複值最便捷的方法。

 

示例2:獲取兩列或多列組合後的不重複值

仍以上述工作表為例。現在,要篩選單元格區域A1:D9中的「學生姓名」和「科目」的不重複的組合。為使代碼簡便起見,我們先在要複製到的目標區域中輸入這兩個標題,即在單元格G1中輸入「學生姓名」,在單元格H1中輸入「科目」。

在VBE中輸入代碼:

Sub testAdvancedFilter3()

    Dim rngData As Range

    Dim rngResult As Range

     

    '設置被篩選的數據區域

    Set rngData = Range("A1:D9")

   

    '設置複製數據的目標區域

    Set rngResult = Range("G1:H1")

   

    '篩選不重複的學生姓名和科目的組合

    rngData.AdvancedFilterAction:=xlFilterCopy, CopyToRange:=rngResult, Unique:=True

End Sub

執行代碼後的效果如下圖:

 

細心的讀者可能會發現,上面示例中的3段代碼中AdvancedFilter方法語句相同:

    rngData.AdvancedFilter Action:=xlFilterCopy,CopyToRange:=rngResult, Unique:=True

如果我們在代碼中設置好了相應的參數後,就可以反覆使用同樣的代碼!

 

示例3:根據條件區域篩選

本示例改寫上文中錄製的使用條件區域篩選的代碼,根據條件區域F1:F2篩選數據到單元格H1開始的區域。

Sub testAdvancedFilter3()

    Dim rngData As Range

    Dim rngCriteria As Range

    Dim rngResult As Range

     

    '設置被篩選的數據區域

    Set rngData = Range("A1:D9")

   

    '設置條件區域

    Set rngCriteria = Range("F1:F2")

   

    '設置複製數據的目標區域

    Set rngResult = Range("H1")

   

    '篩選滿足條件區域的不重複數據

    rngData.AdvancedFilterAction:=xlFilterCopy, _

                           CriteriaRange:=rngCriteria, _

                          CopyToRange:=rngResult, _

                           Unique:=True

End Sub

 

示例4:設置邏輯條件篩選

繼續以上文中的工作表為例,使用邏輯運算連接多個條件來進行篩選。


我們可以看到,上圖中,當條件數據在同一列時,表明邏輯或的關係,即篩選該標題中包含所列中所有內容的數據;當條件數據在不同列時,表明邏輯與的關係,即篩選同時滿足列標題所在列內容組合的數據;當條件數據在不同列不同行時,篩選滿足列標題中所有內容的數據。代碼如下:

Sub testAdvancedFilter4()

    Dim rngData As Range

    Dim rngCriteria As Range

    Dim rngResult As Range

     

    '設置被篩選的數據區域

    Set rngData = Range("A1:D9")

   

    '設置條件區域

    '篩選科目為語文或英語的數據

    Set rngCriteria = Range("F1:F3")

    '篩選學生姓名為張三且科目為數學的數據

    'Set rngCriteria = Range("F1:G2")

    '篩選學生姓名為李四或科目為英語的數據

    'Set rngCriteria = Range("F1:G3")

   

    '設置複製數據的目標區域

    Set rngResult = Range("I1")

   

    '篩選滿足條件區域的不重複數據

    rngData.AdvancedFilterAction:=xlFilterCopy, _

                          CriteriaRange:=rngCriteria, _

                          CopyToRange:=rngResult, _

                           Unique:=True

End Sub

 

注釋掉相應的代碼後,可以執行不同條件組合的篩選。

 

也可以使用公式作為篩選條件。例如,在單元格F2中輸入公式:

=AND(科目="語文",成績>85)

用來查找數據區域A1:D9中,科目為「語文」並且成績大於85的數據記錄。條件區域為F1:F2,執行條件篩選後的結果如圖中單元格區域I1:L2所示。


說明

    Set rngCriteria = Range("F1:F2")

 

示例5:找出兩列中不相同的內容

如下圖所示的工作表,列A和列B中有相同的數據,也有不同的數據,要找出兩列中不同的數據並將這些單元格設置紅色背景色。

代碼如下:
Sub testAdvancedFilter5()

    Dim lngLastRowA As Long, lngLastRowD AsLong

    Dim lngLastRowB As Long, lngLastRowE AsLong

    Dim rngA As Range, rngB As Range

    Dim rngD As Range, rngE As Range

    Dim rng As Range, rngTo As Range

   

    '找到列A中的最後一行

    lngLastRowA = Range("A" &Rows.Count).End(xlUp).Row

    '設置列A中的數據區域

    Set rngA = Range("A1:A" &lngLastRowA)

   

    '找到列B中的最後一行

    lngLastRowB = Range("B" &Rows.Count).End(xlUp).Row

    '設置列B中的數據區域

    Set rngB = Range("B1:B" &lngLastRowB)

   

    '篩選列A找到不重複值並複製到列D

    Range("A1:A" & lngLastRowA).AdvancedFilterAction:=xlFilterCopy, _

                               CopyToRange:=Range("D1"), Unique:=True

    '找到列D中的最後一行

    lngLastRowD = Range("D" &Rows.Count).End(xlUp).Row

    '設置含有列A中的不重複值的區域

    Set rngD = Range("D1:D" &lngLastRowD)

   

    '篩選列B找到不重複值並複製到列E

    Range("B1:B" &lngLastRowB).AdvancedFilter Action:=xlFilterCopy, _

                              CopyToRange:=Range("E1"), Unique:=True

    '找到列E中的最後一行

    lngLastRowE = Range("E" &Rows.Count).End(xlUp).Row

    '設置含有列B中的不重複值的區域

    Set rngE = Range("E1:E" &lngLastRowE)

   

    '找到列A中有但列B中沒有的數據並設置紅色背景色

    For Each rng In rngA

        Set rngTo = rngE.Find(What:=rng)

        If rngTo Is Nothing Then

            rng.Interior.Color = RGB(225, 0, 0)

        End If

    Next rng

   

    '找到列B中有但列A中沒有的數據並設置紅色背景色

    For Each rng In rngB

        Set rngTo = rngD.Find(What:=rng)

        If rngTo Is Nothing Then

            rng.Interior.Color = RGB(225, 0, 0)

        End If

    Next rng

   

    '清除臨時存放不重複值的區域

    rngD.Clear

    rngE.Clear

End Sub

說明

 

 

---

 

如果您對本文介紹的內容還有什麼好的示例,歡迎發送郵件給我:xhdsxfjy@163.com

也可以在本文下方留言,提出您的看法或建議。

本文屬原創文章,轉載請聯繫我或者註明出處。

 

關注《完美Excel》微信公眾帳號:

方法1—點擊右上角的按鈕,選擇「查看公眾號」,點擊關注

方法2—在添加朋友中搜索excelperfect

方法3—掃一掃下面的二維碼

相關焦點

  • Excel技術 | 名稱12:名稱在VBA代碼中的應用示例—篩選數據
    5:查找並顯示部分匹配的數據》一文中,充分展示了如何使用名稱來高效篩選數據。代碼中設置了一個臨時的條件區域,用於高級篩選,使用完後刪除。使用Transpose函數將水平數組轉換為垂直數組,即在同一列的兩行中放置數據。
  • Excel篩選與高級篩選的15個應用技巧解讀!
    在Excel中,「篩選」功能的應用率是比較高的,「高級篩選」的應用率相對要低很多,今天,小編帶大家全面了解和掌握「篩選」、「高級篩選」的應用技巧。一、普通篩選。1、單條件篩選。解讀:其他數據的篩選方法類似。2、多條件篩選。目的:「男」同志在「上海」地區的銷量。
  • excel VBA是什麼?VBA編程入門教程
    本篇將介紹excel vba是什麼?vba編程入門教程,有興趣的朋友可以了解一下!一、excel vba是什麼?VBA的英文全稱是Visual Basic for Applications,是一門標準的宏語言。VBA語言不能單獨運行,只能被office軟體(如:Word、Excel等)所調用。
  • Excel高級篩選的使用方法(高手進階必會)
    Excel自動篩選在工作中被經常使用,但掌握高級篩選的同學卻很少,甚至都不知道高級篩選高級到哪兒了。
  • excel高級篩選的使用方法(入門+進階+高級
    Excel自動篩選在工作中被經常使用,但掌握高級篩選的同學卻很少,甚至都不知道高級篩選高級到哪兒了。
  • Excel高級篩選怎麼用同時滿足多個條件進行篩選
    在工作中,我們會使用excel的篩選功能,篩選查找出你想要的信息。只不過篩選功能僅能滿足一個條件的查找,如果同時滿足兩個條件,這時候單純的使用excel篩選功能就會顯得無能為力了。那麼,問題來了,如果我們要挑選出同時滿足兩個條件的數據應該如何實現呢?
  • excel表格中的簡單篩選和高級篩選怎麼用? - 國哥筆記
    本篇將介紹excel表格中的簡單篩選和高級篩選怎麼用,有興趣的朋友可以了解一下!excel是我們工作中經常用到的表格製作工具,它不僅僅只是用來製作表格,它還可以對表格中的數據進行處理(比如:運算、排序、篩選等等)。今天小編要介紹的就是excel篩選功能,excel篩選分為簡單篩選和高級篩選。
  • excel中高級篩選的巧妙使用
    在日常工作和生活中,面對excel工作表中大量的數據,我們為了從中精煉提取出我們所需要的重要數據,篩選和高級篩選是我們必須要學會運用的強大功能。篩選的操作方法:首先我們選中要進行篩選的數據下的任意一個單元格,然後我們找到並點擊【開始】選項卡,接著在功能欄右邊倒數第二個位置找到並點擊【排序和篩選】,在彈出的下拉列選項中,我們找到並點擊【篩選】,這樣我們會發現工作表中表頭的每個單元格都會出現一個下拉列選項,我們通過點擊下拉列就能快速進行數據的篩選工作了。
  • R語言行篩選的方法--filter
    上篇是數據框中列的篩選(R語言列篩選的方法--select),本次講解行的篩選,主要是介紹filter函數。1.re2 = blup2 %>% filter(Type == "vm(Progeny, ainv)",effect <0)head(re2)
  • Excel如何使用高級篩選對數據進行篩選?
    excel表格中一般我們都是使用高級篩選命令如何進行操作的,下面來看看吧。打開一個excel 表格。1.我們想對數據中的成交金額進行篩選。2.在空白單元格中輸入成交金額,下方輸入大於五百。4.找到高級篩選中的按鈕,點擊一下。5.彈出高級篩選對話框,選擇將篩選結果複製到其他位置,以免影響原數據。(未完待續...)高級篩選命令是excel中篩選中的主要組成部分,我們可以通過高級篩選來篩選一些我們自定義的數據,一起來看看吧。
  • Excel高級篩選匯總:多條件篩選、數據「或」「和」條件篩選?
    Excel如何對數據多個條件進行高級篩選?如果在excel表格中對於數據篩選有多個條件,如何進行篩選,一起來學習吧。excel表格中的數據篩選有「和」條件和「或」條件,或條件的話,如何進行篩選的,一起來學習吧。1.首先我們要篩選地區的是北京或者是上海的地區數據。
  • Excel高級篩選匯總:多條件篩選、數據「或」「和」條件篩選?
    Excel如何對數據多個條件進行高級篩選?如果在excel表格中對於數據篩選有多個條件,如何進行篩選,一起來學習吧。1.首先我們要篩選成交金額大於300小於500的數據。3.然後點擊數據中的高級選項。4.在打開的高級篩選中框選所需要的數據範圍,點擊確定,5.這個時候就能得到成交金額大於三百小於五百的所有數據了。Excel如何對同一列數據「或」條件進行篩選?
  • 高級篩選,Excel高手的進階之路!
    高級篩選之所以稱之為高級,是因為它能實現很多一般篩選無法完成的功能。如圖所示是高級篩選能夠實現的功能,夠厲害的吧!打開「數據」選項卡,在「排序和篩選」分區可以看到有「高級」命令,它就是高級篩選的入口。操作方法和「且」條件是一樣的,後文就不再進行GIF演示。
  • 必須要掌握的Excel「高級篩選」應用技巧解讀
    解讀:1、如果第一步選取的數據源有誤或需要調整,可以在打開【高級篩選】對話框後,單擊【列表區域】右側的箭頭重新選取數據源。解讀:篩選的結果為「王東」關於「手機」的銷售記錄或「小西」關於「音響」的銷售記錄,如果兩個條件都不滿足,則篩選結果為空。五、Excel【高級篩選】:時間段篩選。
  • VBA——利用篩選功能做一對多
    由於我們想要的結果是,E2單元格的值,發生改變,對應的返回區域也發生改變。到這裡,整個過程就做完了,我們來看看最終效果。如果不想要看到,刷新的過程,可以在宏5的開頭和結尾加上這兩句。
  • 零基礎也能掌握的6個Excel高級篩選應用技巧解讀
    高級篩選,主要分為2種方式:1、在原有區域顯示篩選結果。2、將篩選結果複製到其他位置。操作方法為:【數據】-選擇【排序和篩選】工具組中的【高級】,請參閱下圖。解讀:使用此方法刪除的重複記錄是指行內容完全相同的記錄,並不是單元格內容相同的記錄,即數據比較的基礎是行,而不是單元格。三、單欄位單條件篩選。
  • Excel VBA解讀(54):排序——Sort方法
    看看下面的Excel界面截圖,「排序」和「篩選」往往在一起,這大概是很多數據需要先排序後篩選吧!將排序與自動篩選結合,可達到我們的目的。將排序與高級篩選相結合,可以達到我們的目的。代碼中的:rng.Columns(3)表示單元格區域rng中的第3列,即「性別」欄位列。
  • Excel VBA 實例(22) - 一鍵篩選其他工作表或工作簿的數據
    今天說的這個vba實例還是和學校有關。
  • Pandas filter 篩選標籤
    Pandas 的 filter 方法根據指定的索引標籤對數據框行或列查詢子集。
  • 重磅 | Excel VBA解讀:Range對象篇
    在《ExcelVBA解讀》系列第2季中,我們詳細講解了Range對象的大多數方法和屬性,現整理於此,以饗讀者。自動篩選—AutoFilter方法詳細講解了在VBA中實現自動篩選功能的AutoFilter方法,包括該方法的語法及詳細的參數說明,並提供了6個有用的示例:①移除自動篩選提供的下拉箭頭、②一次執行多個列欄位的篩選、③複製篩選結果、