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

  小爬一般习惯使用Python来解决爬虫和某些办公自动化场景问题,不过最近却需要实现一个VBA需求:从一堆人员处理的Excel数据记录中,排除某些“用户名称”处理的数据。整个思考过程很有意思,很值得分享下。文章来源地址:https://www.yii666.com/article/764174.html

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

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

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

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

  小爬首先排除的方法是遍历每一行的“用户名称”,然后进行remove等操作,因为小爬的数据集超过50000行,这样执着的遍历方法显然跟上文提到的“高效”不沾边,自然不应该成为我们自动化的首选方案;文章地址https://www.yii666.com/article/764174.html

  小爬紧接着试了下VBA的录制宏功能:对数据进行筛选,然后手工勾选掉那些我们不想要的数据,再来看后台VBE自动生成的代码是否能稍加改造为我所用,该场景里,后台的录制的代码长这样:文章来源地址https://www.yii666.com/article/764174.html

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列。网址:yii666.com<

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

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

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

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

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

在这个“筛选中排除某些值”的场景,根据录制宏的代码,我们需要的应该是一个一维数组,内包含所有要筛选的结果。此时,我们可以利用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方法、字典键快速存入数组方法等。看上去每个单一功能都不复杂,但是任何一个功能掌握的不好,我们很可能就解决不了一个再常见不过的场景。工作中需要学会和总结的技能点还有很多,加油吧,骚年~

欢迎扫码关注我的公众号 获取更多爬虫、数据分析的知识!

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

版权声明:本文内容来源于网络,版权归原作者所有,此博客不拥有其著作权,亦不承担相应法律责任。文本页已经标记具体来源原文地址,请点击原文查看来源网址,站内文章以及资源内容站长不承诺其正确性,如侵犯了您的权益,请联系站长如有侵权请联系站长,将立刻删除

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信图片_20190322181744_03.jpg

微信扫一扫打赏

请作者喝杯咖啡吧~

支付宝扫一扫领取红包,优惠每天领

二维码1

zhifubaohongbao.png

二维码2

zhifubaohongbao2.png