|
在面對大量數(shù)據(jù)時，我們可以使用Excel的篩選功能，濾出我們需要的信息。在本文中，我們先從Excel中的“篩選”命令談起。
如下圖所示的工作表，將活動單元格置于任一數(shù)據(jù)單元格中，單擊功能區(qū)中的“排序和篩選”中的“篩選”命令，可以看到表頭單元格中出現(xiàn)了篩選下拉箭頭。
上述操作錄制的VBA代碼如下： Sub Macro1() ' ' Macro1 Macro '
' Selection.AutoFilter End Sub
接著操作。單擊內(nèi)容為“語文”的下拉箭頭（即表頭第3列），選擇“數(shù)字篩選——大于(G)…”，在“自定義自動篩選方式”對話框的“顯示行”中，第一個組合框左側(cè)選擇“大于或等于”，右側(cè)輸入“80”，第二個組合框左側(cè)選擇“小于”，右側(cè)輸入“90”，即篩選語文分?jǐn)?shù)大于或等于80且小于90的數(shù)據(jù)，結(jié)果如下圖所示。 錄制的代碼如下： Sub Macro2() ' ' Macro2 Macro '
' Selection.AutoFilter ActiveSheet.Range('$A$1:$F$19').AutoFilter Field:=3,Criteria1:='>=80', _ Operator:=xlAnd,Criteria2:='<90' End Sub
觀察上面錄制的代碼，可以看出，Excel VBA使用AutoFilter方法來實現(xiàn)“篩選”功能，并提供了一系列可選的參數(shù)來進(jìn)一步執(zhí)行篩選操作。
AutoFilter方法的語法及說明 下面是Range對象的AutoFilter方法的語法： Range對象.AutoFilter(Field,Criterial1,Operator,Criteria2,VisibleDropDown) 說明： 1. 參數(shù)Field，指定想要基于篩選的字段的整數(shù)偏移量。從列表的左側(cè)算起，最左側(cè)的字段是字段一。 2. 參數(shù)Criterial1，指定判斷條件（為字符串）。使用“=”查找空字段，或者使用“<>”查找非空字段。如果忽略該參數(shù)，那么判斷是全部。如果參數(shù)Operator是xlTop10Items，那么參數(shù)Criterial1指定項目的數(shù)量。 3. 參數(shù)Operator，指定篩選的類型，為XlAutoFilterOperator常量之一：
示例1：移除自動篩選提供的下拉箭頭 在Excel中使用自動篩選時，會在每列頂部都添加一個下拉箭頭以獲取相應(yīng)的篩選項。有時，我們只需要使用其中某些字段的下拉箭頭，不需要其它字段帶有下拉箭頭，以免誤操作這些字段。例如，上面的示例中，我們只需要代表科目的語文、數(shù)學(xué)、英語、歷史字段有下拉箭頭，而移除列A、列B中的下拉箭頭。代碼如下： Sub testAutoFilter1() Range('A1').AutoFilter Field:=1,VisibleDropDown:=False Range('A1').AutoFilter Field:=2,VisibleDropDown:=False End Sub 即，將相應(yīng)列字段的參數(shù)VisibleDropDown設(shè)置為False。 執(zhí)行后的效果如下圖：
示例2：一次執(zhí)行多個列字段的篩選 仍以本文開頭的工作表為例，要求得到語文成績大于等于80的男生的數(shù)據(jù)記錄。代碼如下： Sub testAutoFilter2() Range('A1').AutoFilter Field:=2,Criteria1:='=男' Range('A1').AutoFilter Field:=3,Criteria1:='>=80' End Sub
示例3：復(fù)制篩選結(jié)果 將示例2中得到的結(jié)果復(fù)制到以單元格H21開頭的區(qū)域中。代碼如下： Sub testAutoFilter3() Dim lngLastRow As Long
'找到工作表中最后一行 lngLastRow = Range('A' &Rows.Count).End(xlUp).Row
'按條件執(zhí)行自動篩選 Range('A1').AutoFilter Field:=2,Criteria1:='=男' Range('A1').AutoFilter Field:=3,Criteria1:='>=80'
'將篩選后的結(jié)果復(fù)制到指定位置 Range('A1:F' &lngLastRow).Copy Range('H21') End Sub 可以看出，Copy方法僅復(fù)制可見單元格中的內(nèi)容。
示例4：刪除篩選出的數(shù)據(jù) 如下圖所示的工作表，我們要刪除列A中單元格內(nèi)容為“0”的數(shù)據(jù)行。 此時，我們可以使用AutoFilter方法篩選出這些行，然后進(jìn)行刪除。代碼如下： Sub testAutoFilter4() Dim rng As Range
'設(shè)置篩選區(qū)域 Set rng = Range('A1:B10')
'如果開啟了篩選模式則關(guān)閉該模式 If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False End If
'篩選列A中內(nèi)容為0的單元 rng.AutoFilter Field:=1,Criteria1:='0'
'刪除篩選出來的行 With rng .Offset(1).Resize(.Rows.Count -1).SpecialCells(xlCellTypeVisible).Delete Shift:=xlShiftUp '關(guān)閉篩選模式 .Worksheet.AutoFilterMode = False End With End Sub 您可能注意到代碼中出現(xiàn)了一些我們前面的文章中沒有提到的新屬性和方法，下面來作些解釋。
代碼運行后的結(jié)果如下圖。 也可以參照下面的視頻來加深理解。
示例5：根據(jù)當(dāng)前單元格內(nèi)容篩選數(shù)據(jù) 如下圖所示的工作表，我們要篩選出和當(dāng)前單元格內(nèi)容相同的單元格所在的數(shù)據(jù)行。 例如，當(dāng)前單元格為單元格B7，當(dāng)運行程序后，會篩選出與單元格B7中的內(nèi)容（即“一班”）相同的單元格所在的數(shù)據(jù)行，所需效果如下圖： 代碼如下： Sub testAutoFilter5() Dim lngColNum As Long
'計算當(dāng)前單元格在區(qū)域中的列號 lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1)
'篩選 Selection.AutoFilter Field:=lngColNum,Criteria1:=ActiveCell
End Sub 注意到本代碼中使用了一個技巧，即代碼： lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1) 當(dāng)單元格區(qū)域不是以列A為第1列時，可以準(zhǔn)確地計算出當(dāng)前單元格在所處區(qū)域中的列號，從而將其運用到接下來的AutoFilter方法的參數(shù)Field中。
示例6：根據(jù)當(dāng)前單元格內(nèi)容實時篩選數(shù)據(jù)并將數(shù)據(jù)粘貼到指定位置 本示例將示例3和示例5結(jié)合，實時篩選與當(dāng)前單元格內(nèi)容相同的數(shù)據(jù)并將數(shù)據(jù)復(fù)制到指定位置。 仍以示例5的工作表為例。當(dāng)活動單元格處于A2:C9中時，能夠?qū)崟r對數(shù)據(jù)進(jìn)行篩選，并將篩選出來的數(shù)據(jù)復(fù)制到以單元格A13開頭的單元格區(qū)域中。 要實時篩選數(shù)據(jù)，必須結(jié)合工作表事件代碼。即，我們的代碼放置在了工作表模塊的Worksheet_SelectionChange事件（將在Worksheet對象中介紹其詳細(xì)用法）中： Private SubWorksheet_SelectionChange(ByVal Target As Range) Dim lngColNum As Long Dim lngLastRow As Long Dim rng As Range
'如果開啟了篩選模式則關(guān)閉該模式 If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False End If
'設(shè)置當(dāng)前單元格與單元格區(qū)域A2:C9相重合的單元格 Set rng = Intersect(Target,Range('A2:C9')) '找到工作表中數(shù)據(jù)所在的最后行 lngLastRow = Range('A' &Rows.Count).End(xlUp).Row
'如果工作表中第9行外還有數(shù)據(jù)則清除 If lngLastRow > 9 Then Range('A13:C' &lngLastRow).Value = '' End If
If Not rng Is Nothing Then '計算當(dāng)前單元格在區(qū)域中的列號 lngColNum = ActiveCell.Column -(ActiveCell.CurrentRegion.Column - 1)
'篩選 Selection.AutoFilter Field:=lngColNum,Criteria1:=ActiveCell '關(guān)閉事件響應(yīng) Application.EnableEvents = False Range('A2:C9').CopyRange('A13') End If
'關(guān)閉篩選模式 ActiveSheet.AutoFilterMode = False '開啟事件響應(yīng) Application.EnableEvents = True End Sub
注意，上述代碼必須放置在數(shù)據(jù)所在工作表模塊中。此時，當(dāng)活動單元格處于該工作表A2:C3區(qū)域中時，會自動篩選與活動單元格內(nèi)容相同的單元格數(shù)據(jù)，并復(fù)制粘貼到以單元格A3開始的區(qū)域中。 下面是一段簡短的演示視頻： 在代碼中，我們使用了語句： Application.EnableEvents = False 來關(guān)閉事件響應(yīng)。因為我們的代碼是靠事件實時響應(yīng)來達(dá)到動態(tài)選擇復(fù)制的效果，如果在復(fù)制前不關(guān)閉事件響應(yīng)，那么復(fù)制操作將會引發(fā)SelectionChange事件，會達(dá)不到我們想要的結(jié)果，因此，先關(guān)閉事件響應(yīng)，復(fù)制完后再開啟，以實現(xiàn)我們再次選擇單元格時數(shù)據(jù)的變化。我們會在Application對象中詳細(xì)講解關(guān)于EnableEvents屬性的內(nèi)容。
-------------------------------------- 如果您對本文介紹的內(nèi)容還有什么好的示例，歡迎發(fā)送郵件給我：xhdsxfjy@163.com 也可以在本文下方留言，提出您的看法或建議。 本文屬原創(chuàng)文章，轉(zhuǎn)載請聯(lián)系我或者注明出處。
|
|
|
