VBA如何实现筛选条件之“排除某些值”

2024-01-12 09:03:10

　　小爬一般习惯使用Python来解决爬虫和某些办公自动化场景问题，不过最近却需要实现一个VBA需求：从一堆人员处理的Excel数据记录中，排除某些“用户名称”处理的数据。整个思考过程很有意思，很值得分享下。

这个需求看上去很简单，实际当我们的待排除“用户名称”数超过2时，Excel原生的筛选“自定义筛选-不等于”功能是难以支持的：

　　那么这类问题该怎么高效解决呢？

　　小爬首先排除的方法是遍历每一行的“用户名称”，然后进行remove等操作，因为小爬的数据集超过50000行，这样执着的遍历方法显然跟上文提到的“高效”不沾边，自然不应该成为我们自动化的首选方案；

　　小爬紧接着试了下VBA的录制宏功能：读数据进行筛选，然后手工勾选掉那些我们不想要的数据，再来看后台VBE自动生成的代码是否能稍加改造为我所用，该场景里，后台的录制的代码长这样：

Sub 宏1()

    Range("I1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$J$141").AutoFilter Field:=9, Criteria1:=Array("User1", "User11", "User12", "User13", "User14", "User15", "User16", "User17", "User18", "User19", "User2", "User20", "User3"), Operator:=xlFilterValues
End Sub

　　可以看出，我们要排除的人员是User4到User10，但是录制的宏代码中，Array里提到的人员恰恰是需要保留的人员数据（即筛选时勾选的人），这里的“Operator:=xlFilterValues”指的是筛选后要保留哪些值，这里的Field:=9 指的是我们要筛选的字段是表格的第9列。

那么问题就转化成了如下形式：

　　如何得到某列所有的人员名单（去重重复项和空值），然后从这些名单中剔除掉我们的“排除人员名单”，从而得到我们最终的待保留人员名单，并存入一个array数组？

　　下图是我在ExcelHome论坛中看到的一个典型的方法：

　　该方法可以快速将某一列值存入列表，然后借助字典的键不重复这一特性来快速去重，最终将字典的键写入新的列。该方法非常典型，不过当我们将某一列值快速存入数组（arr=[A1:A1000])时，默认得到的是二维数组（数组的二级下标默认为1)，同理，只有将某一行数据快速写入数组,得到的才是一维数组；

在这个“筛选中排除某些值”的场景，根据录制宏的代码，我们需要的应该是一个一维数组，内包含所有要筛选的结果。此时，我们可以利用excel的转置功能快速将列变成行，达到快速将某列值存入一维数组的目的，有了思路，代码就水到渠成了，下面是示例代码：

Sub test()
'基于N列的排除人员名单，对“用户名称”列进行筛选，晒除这些人
Dim max_row As Integer
max_row = Sheets("Sheet1").Cells(Rows.Count, 9).End(xlUp).Row '得到表格第九列的最大行号
Set d = CreateObject("Scripting.Dictionary")
arr = Application.Transpose(Range("I2:I" & max_row).Value)
For i = LBound(arr) To UBound(arr) '将人员名单遍历后，借助字典，筛除重复值和空值
    If arr(i) <> "" Then d(arr(i)) = ""
Next
For i = 2 To 8 '将字典的键，去除人员名单，得到其他键，存入新的数组
    If d.Exists(Range("N" & i).Value) = True Then
        d.Remove (Range("N" & i).Value)
    End If
    
Next
newArr = d.keys '排除人员名单后的新数组
'For i = LBound(newArr) To UBound(newArr)
'Debug.Print (newArr(i))
'Next
Range("A1").Select
Selection.AutoFilter
Range("$A$1:$J$" & max_row).AutoFilter Field:=9, Criteria1:=newArr, Operator:=xlFilterValues '基于新的数组进行筛选（达到排除某些人员的效果）

End Sub

　　通过上面的思路也可以看出来，简单的一个“筛选——不包含某些值”的VBA场景，我们需要用到录制宏功能，一维数组、二维数组功能、数组的转置方法、字典的remove方法、字典键快速存入数组方法等。看上去每个单一功能都不复杂，但是任何一个功能掌握的不好，我们很可能就解决不了一个再常见不过的场景。工作中需要学会和总结的技能点还有很多，加油吧，骚年~

码农公寓

相关文章