热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

在Excel中使用VBA来筛选数据

订购信息:ExcelVBA应用开发从基础到实践已可从第二书店订购http:www.cnblogs.commaweifengarchive200608224837

订购信息:
Excel VBA应用开发从基础到实践 已可从第二书店订购
http://www.cnblogs.com/maweifeng/archive/2006/08/22/483790.html


《Excel与VBA程序设计》最新消息,预计9月上市 

    

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

        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

               

End Sub

 

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

5.       总结

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

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

 

(2004-11-23 夜)



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

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

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

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

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


推荐阅读
  • Hadoop入门与核心组件详解
    本文详细介绍了Hadoop的基础知识及其核心组件,包括HDFS、MapReduce和YARN。通过本文,读者可以全面了解Hadoop的生态系统及应用场景。 ... [详细]
  • 本文总结了2018年的关键成就,包括职业变动、购车、考取驾照等重要事件,并分享了读书、工作、家庭和朋友方面的感悟。同时,展望2019年,制定了健康、软实力提升和技术学习的具体目标。 ... [详细]
  • 本文详细介绍了MicroATX(也称Mini ATX)和MATX主板规格,探讨了它们的结构特点、应用场景及对电脑系统成本和性能的影响。同时,文章还涵盖了相关操作系统的实用技巧,如蓝牙设备图标删除、磁盘管理等。 ... [详细]
  • 解决JAX-WS动态客户端工厂弃用问题并迁移到XFire
    在处理Java项目中的JAR包冲突时,我们遇到了JaxWsDynamicClientFactory被弃用的问题,并成功将其迁移到org.codehaus.xfire.client。本文详细介绍了这一过程及解决方案。 ... [详细]
  • Vue 2 中解决页面刷新和按钮跳转导致导航栏样式失效的问题
    本文介绍了如何通过配置路由的 meta 字段,确保 Vue 2 项目中的导航栏在页面刷新或内部按钮跳转时,始终保持正确的 active 样式。具体实现方法包括设置路由的 meta 属性,并在 HTML 模板中动态绑定类名。 ... [详细]
  • QUIC协议:快速UDP互联网连接
    QUIC(Quick UDP Internet Connections)是谷歌开发的一种旨在提高网络性能和安全性的传输层协议。它基于UDP,并结合了TLS级别的安全性,提供了更高效、更可靠的互联网通信方式。 ... [详细]
  • PHP 编程疑难解析与知识点汇总
    本文详细解答了 PHP 编程中的常见问题,并提供了丰富的代码示例和解决方案,帮助开发者更好地理解和应用 PHP 知识。 ... [详细]
  • 深入理解OAuth认证机制
    本文介绍了OAuth认证协议的核心概念及其工作原理。OAuth是一种开放标准,旨在为第三方应用提供安全的用户资源访问授权,同时确保用户的账户信息(如用户名和密码)不会暴露给第三方。 ... [详细]
  • 2023 ARM嵌入式系统全国技术巡讲旨在分享ARM公司在半导体知识产权(IP)领域的最新进展。作为全球领先的IP提供商,ARM在嵌入式处理器市场占据主导地位,其产品广泛应用于90%以上的嵌入式设备中。此次巡讲将邀请来自ARM、飞思卡尔以及华清远见教育集团的行业专家,共同探讨当前嵌入式系统的前沿技术和应用。 ... [详细]
  • 国内BI工具迎战国际巨头Tableau,稳步崛起
    尽管商业智能(BI)工具在中国的普及程度尚不及国际市场,但近年来,随着本土企业的持续创新和市场推广,国内主流BI工具正逐渐崭露头角。面对国际品牌如Tableau的强大竞争,国内BI工具通过不断优化产品和技术,赢得了越来越多用户的认可。 ... [详细]
  • 本文介绍了如何使用jQuery根据元素的类型(如复选框)和标签名(如段落)来获取DOM对象。这有助于更高效地操作网页中的特定元素。 ... [详细]
  • XNA 3.0 游戏编程:从 XML 文件加载数据
    本文介绍如何在 XNA 3.0 游戏项目中从 XML 文件加载数据。我们将探讨如何将 XML 数据序列化为二进制文件,并通过内容管道加载到游戏中。此外,还会涉及自定义类型读取器和写入器的实现。 ... [详细]
  • 使用Python在SAE上开发新浪微博应用的初步探索
    最近重新审视了新浪云平台(SAE)提供的服务,发现其已支持Python开发。本文将详细介绍如何利用Django框架构建一个简单的新浪微博应用,并分享开发过程中的关键步骤。 ... [详细]
  • 深入探讨CPU虚拟化与KVM内存管理
    本文详细介绍了现代服务器架构中的CPU虚拟化技术,包括SMP、NUMA和MPP三种多处理器结构,并深入探讨了KVM的内存虚拟化机制。通过对比不同架构的特点和应用场景,帮助读者理解如何选择最适合的架构以优化性能。 ... [详细]
  • 本题通过将每个矩形视为一个节点,根据其相对位置构建拓扑图,并利用深度优先搜索(DFS)或状态压缩动态规划(DP)求解最小涂色次数。本文详细解析了该问题的建模思路与算法实现。 ... [详细]
author-avatar
倒退淂磁带_628
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有