在Excel中使⽤VBA来筛选数据
1.      问题由来
早晨还没有完全醒来，你就被电话吵醒，有⼀个中学同学向你请教⼀个Excel的问题。作为⼀个所谓的Excel专家，你经常会受到此类骚扰。问题⼤概是这样的，⼀个很⼤的Excel⽂件，其中有些⾏是重复的，也就是说，有2⾏是完全⼀样的，⽽有些⾏是不重复的，现在的问题是要出所有不重复或者重复的⾏，你没有听明⽩。你⼤概考虑了⼀下，⽤“VLOOKUP”查⼀下，然后重新排序，应该就可以了，你需要试⼀下，然后告诉他怎么⽤，于是你告诉他，20分钟后再打电话给你。
2.      问题解决的思路
你⾸先打开Excel，输⼊⼀些测试数据，⼤概是这个样⼦：
其中“张三”、“李四”有2个，其他只有⼀个，需要把他们分出来。⾸先在B列输⼊1，然后向下填充，在C列输
⼊“VLOOKUP(A1,$A$1:$B$7,2,FALSE)”，如果到，那么返回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层，真不知道微软的⼯程师怎么想的，你⼀边嘟囔⼀边按下了回车，结果是“#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写的。你灵机⼀动，为什么不⽤这个函数呢，在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函数，代码觉得很丑陋。你不知道为什么想起来⼆分查之类的东东，那么，查前应该先排序，你在Excel⾥把数据排了序。现在的问题是需要循环2次，复杂度为N*N，如果…...，你想如果排好了序，只需要检查当前数值和下⼀个是否⼀样，如果⼀样，那么把当前和下⼀个位置标⽰出来，循环变量加2，跳过下⼀个，如果不⼀样，循环变量加1继续⽐较就可以了，代码如下：
Sub SelectDouble4()
Dim i As Long, Max As Long
Max = 10000
i = 1
Do
excel vlookup函数怎么用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算法，思路是⼀样的，嗯，那么如果不是相同的内容不是2个，是多个，那么你可以使⽤⼀个循环来前溯，并且，对于不同的个数，可以标识为不同的数字。你忽然觉得⾃信满满，似乎要忘了已经失业半年的事实。
（2004-11-23 夜）
在表格或数值数组的⾸列查指定的数值，并由此返回表格或数组当前⾏中指定列处的数值。当⽐较值位于数据表⾸列时，可以使⽤函数VLOOKUP 代替函数 HLOOKUP。具体⽤法可以参考Excel帮助。
作为程序员的你，⼀直觉得IF函数之类是浪费时间和多此⼀举，7层的IF函数怎么看得懂？但函数代表简单，你不想因为告诉你同学要写程序解决问题⽽把他吓坏。
天知道微软⽤什么写的这些代码，也许是C，也许是C++，肯定不是Basic，也不是C#，写它时C#还没有出⽣呢。
或许是你没有写好。
虽然不是科班出⾝，你也学过数据结构和算法的。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。