在Excel中使用VBA来筛选数据

1. 问题由来

早晨还没有完全醒来,你就被电话吵醒,有一个中学同学向你请教一个 Excel 的问题。作为一个所谓的 Excel 专家,你经常会受到此类骚扰。问题大概是这样的,一个很大的 Excel 文件,其中有些行是重复的,也就是说,有 2 行是完全一样的,而有些行是不重复的,现在的问题是要找出所有不重复或者重复的行,你没有听明白。你大概考虑了一下,用“ VLOOKUP ”查找一下,然后重新排序,应该就可以了,你需要试一下,然后告诉他怎么用,于是你告诉他, 20 分钟后再打电话给你。

2. 问题解决的思路

你首先打开 Excel ,输入一些测试数据,大概是这个样子:

其中“张三”、“李四”有 2 个,其他只有一个,需要把他们分出来。首先在 B 列输入 1 ,然后向下填充,在 C 列输入“ VLOOKUP(A1,$A$1:$B$7,2,FALSE) ” [1] ,如果找到,那么返回 1 ,如果找不到,空着就可以了。结果 C 列全部变成了 1 ,因为查找自己肯定可以找到,那么查找的 Range 必须要去除本行。

你接着找了几个其他函数,“ MATCH ”,“ INDEX ”试了试,都无法办到;那么用 IF 函数呢,你开始试着写 IF 函数。先输入第 4 行吧,参数和引用区域回头再处理,或许 Excel 聪明到可以填充出你需要的引用区域。

你输入了如下的 IF 函数:

IF(OR(VLOOKUP(A4,A1:B3,2,FALSE),VLOOKUP(A4,A5:B7,2,FALSE)),1,0)

真够复杂的, Excel 应该开一个小窗口,然后作为代码输入这样的判断逻辑, IF 函数可以嵌套 7 层,真不知道微软的工程师怎么想的 [2] ,你一边嘟囔一边按下了回车,结果是“ #N/A ”,就是“值不可用”,你知道函数 VLOOKUP 如果找不到需要的值,则返回错误值 #N/A ,表达式里有了这个东东,所以不管什么计算,结果都是它了。

从工具菜单选择“错误检查”,“显示计算步骤”,证实了你的猜测,第二个 VLOOKUP 函数返回的错误值 #N/A 传递到了最后。

这时,你同学的电话来了,你告诉他需要写一段小程序,你决定还是使用直接又简单的 VBA 来解决问题。

3. VBA 程序

打开 VBA 编辑器,插入一个模块,你不假思索的敲入了以下代码:

Sub SelectDouble()

Dim i As Long, j As Long

For i = 1 To 7 Step 1

For j = 1 To 7 Step 1

' 不比较相同的行

If i <> j Then

If Range("A" & i).Value = Range("A" & j).Value Then

Range("E" & i).Value = 1

End If

End If

Next j

Next i

End Sub

点击运行,很好,是重复的都标志了 1 ,没有重复的空着,然后排序就可以了。你很满意你还输入了一行注释。你拨通了你同学的电话,告诉他可以了,然后他打电话给你,你把程序念给他,告诉他该改什么地方。天知道他上学时学的什么语言,反正不是 Basic ,你得解释 Dim 是什么含义。经过一番折腾,他终于在电话另一端把代码输入了计算机。作为电信员工的他可以每天 24 小时用电话聊天,只是可怜你的手机话费单,你叹了口气,该去洗脸刷牙了。

4. 效率

洗完脸,刷完牙,你泡好了一杯咖啡,又回到了计算机旁边,电话又来了。你以为是告诉你已经完成了的“喜讯”,听到的却是说死机了,愣了 0.1 秒钟,你想想应该是程序还在执行或者是死循环。你问了他大概的数据量,知道大概有 9000 多条记录,还好,你想。

你检查了一下代码,没有什么死循环,也许是你同学输入时有什么错误,你把循环改到 1 到 10000 ,然后拿起杯子,咽了一口咖啡,往后靠了靠,等着计算结果。几分钟过去了,还是没有结束,你觉得有些奇怪,你敲了“ Ctrl + Break ”,暂停了程序,将鼠标放在 i 变量上,显示 i 还是 24 , TNND ,你知道是 Range 函数太慢,算了,你打电话告诉你同学,大概需要几个小时才可以计算完成。你又喝了一口咖啡,自言自语道,比起手工筛选,毕竟很快了。

但不就不到 1 万条纪录吗, Excel 的 VLOOKUP 等内置函数一眨眼也就计算好了啊。

4.1. 通过数组

数组要比 Range 函数快一些,你把程序改了一下,定义了 2 个数组,首先把数据全部读入第一个数组,然后对数组进行操作,对于重复的,把第二个数组的相应部分写为 1 ,计算完成后,根据第二个数组,把结果写回去。程序代码如下:

Sub SelectDouble2()

Dim i As Long, j As Long

Dim max As Long

Dim a() As String, b() As Long

max = 10000

ReDim a(max) As String

ReDim b(max) As Long

For i = 1 To max Step 1

a(i) = Range("A" & i).Value

Next i

For i = 1 To max Step 1

For j = 1 To max Step 1

' 不比较相同的行

If i <> j Then

If a(i) = a(j) Then

b(i) = 1

End If

End If

Next j

Next i

For i = 1 To max Step 1

Range("F" & i).Value = b(i)

Next

End Sub

你执行了一下,对于 10000 条纪录,大概需要不到 5 分钟。你觉得很满意,效率提高了几个数量级,你还没有忘记设置了一个 max 变量,这样,代码使用时改动就会少很多。

4.2. 使用内置函数

你又想起了 VLOOKUP 这个函数,真是阴魂不散。是啊,为什么 VLOOKUP 执行这么快,当然是因为它是编译好的,不是用 VBA 写的 [3] 。你灵机一动,为什么不用这个函数呢,在 VBA 中,可以使用 Application. 函数名,调用 Excel 的内置函数。这样,改过的代码如下:

Sub SelectDouble3()

Dim i As Long, j As Long, a, b

For i = 2 To 9999 Step 1

a = Application.VLookup(Range("A" & i), Range("A1:B" & (i - 1)), 2, False)

b = Application.VLookup(Range("A" & i), Range("A" & (i + 1) & ":B1000"), 2, False)

If IsError(a) And IsError(b) Then

Range("G" & i).Value = 0

End If

Next i

End Sub

代码很短,但有一点复杂和讨厌,循环是从 2 到 9999 ,因为为了防止 VLOOKUP 函数的 Range 范围失效,所以这两行需要手动处理。 IsError 函数来检测返回值,如果两个返回值都是错误,则此行为单一的没有重复的行,标志为 0 即可。程序执行速度和上面的差不多,至少你没有感觉出来差别。

4.3. 继续 Hack

到这里,你还是觉得不满意,使用数组,数据量太大会内存吃紧,使用 VLOOKUP 函数,代码觉得很丑陋 [4] 。你不知道为什么想起来二分查找之类的东东,那么,查找前应该先排序,你在 Excel 里把数据排了序。现在的问题是需要循环 2 次,复杂度为 N*N ,如果 …... ,你想如果排好了序,只需要检查当前数值和下一个是否一样,如果一样,那么把当前和下一个位置标示出来,循环变量加 2 ,跳过下一个,如果不一样,循环变量加 1 继续比较就可以了,代码如下:

Sub SelectDouble4()

Dim i As Long, Max As Long

Max = 10000

i = 1

Do

If Range("A" & i).Value = Range("A" & (i + 1)).Value Then

Range("I" & i).Value = 1

Range("I" & (i + 1)).Value = 1

i = i + 2

Else

i = i + 1

End If

Loop While i < Max

End Sub

这个程序复杂度只有 N ,执行速度当然是你今天写的所有程序里最快的,而且内存占用也最小。你觉得很满意,露出了贼贼的笑容。

5. 总结

你打开了日志,开始记下了今天问题的解决过程。

你想,嗯,如果只是想怎样把 Range 函数变快来解决问题,速度不会有本质的提高。速度提高,第一,排序才是关键,快速的查找和搜索都是要基于排好序的内容,比如二分查找,那么,为什么数据库要建索引,索引的有无对于查找速度影响很大,道理都是一样的了;第二,查找时没有回溯,对于查找过的内容直接跳过,这个和字符串的匹配算法,好像是 KMP 算法 [5] ,思路是一样的,嗯,那么如果不是相同的内容不是 2 个,是多个,那么你可以使用一个循环来前溯,并且,对于不同的个数,可以标识为不同的数字。你忽然觉得自信满满,似乎要忘了已经失业半年的事实。


[1] 在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时,可以使用函数 VLOOKUP 代替函数 HLOOKUP 。具体用法可以参考 Excel 帮助。

[2] 作为程序员的你,一直觉得 IF 函数之类是浪费时间和多此一举, 7 层的 IF 函数怎么看得懂?但函数代表简单,你不想因为告诉你同学要写程序解决问题而把他吓坏。

[3] 天知道微软用什么写的这些代码,也许是 C ,也许是 C++ ,肯定不是 Basic ,也不是 C# ,写它时 C# 还没有出生呢。

[4] 或许是你没有写好。

[5] 虽然不是科班出身,你也学过数据结构和算法的。

Published At
Categories with Web编程
Tagged with
comments powered by Disqus