麻烦老师帮我做个条件查找返回值的宏

hfwufanhf2006

zhxj1983 发表于 2019-6-28 16:15
老师，按我们这样的条件来处理的话，是不是随着记录多，或是表格里面一些空的信息多，用起来会越来越卡的 ...

速度当然是与数据量相关联的，但也不至于卡的那么厉害，比工作表函数要好得多，这个你可以放心。
工作表函数卡是因为其运行模式造成的，比如vlookup，每查找一个记录都需要把被查找数据全部浏览一遍，类似下面的双层循环：
for i=1 to 1000
    s=cells(1,1)   ’读取查询条件关键字
    for k=1 to 1000  这个部分相当于vlookup功能，它会全部浏览一次所有的被查询数据
         if 满足条件 then
            读取单元格，并返回
         endif
    next k
next i

本来vba代码的结构也是这样的，并不会有特别的优化。造成速度差异的主要原因是vlookup永远都是直接读取单元格，不会使用数组。但vba代码一般会大量使用数组，数组的数据是完全存放在内存中的，而单元格的内容大部分会在磁盘上，只有很少的数据会预缓存。只有当操作到当前单元格的时候才会从磁盘缓存到内存，当然这个过程是execl自动处理的，不需要人工干预。
内存的速度与磁盘的速度相差至少几十倍，目前台式机大多数还是机械磁盘，比手机的固态磁盘还要慢很多。所有的工作表函数都是这种工作模式，这就是工作表函数普遍较慢的原因，数据量小不会有多大差别，几千记录就很明显了。

如果vba代码全部都是直接读取单元格，那它与工作表函数的运行模式的差别就不大了。我早起刚学vba时也受这个问题的困扰，我的经验是：
3000-5000行记录，直接读取单元格也可以接受，虽然要慢一点；
5000行以上必须要用内存操作数据；
excel内置的系统功能中，数据透视是效率最高的汇总工具，它处理数据的上限大约为10万行；
vba代码要看编码质量，高质量的代码能处理10万行以内的数据；
超过10万行最好使用sql，这是数据库的通用查询语言，excel内置全支持，效率最高，理论上能处理任何数据，当然我也只用它处理过10万行数据；我也处理过几十万行的数据，但那是用的真正数据库mssql，虽然sql语法都一样，但excel的存储效率比真正的数据库应该要差很多；
作为对比：vlookup大约1000行的交叉查询都会让人无法忍受，会出现假死的状况。我前面说3000-5000行记录也可以代码直接读取单元格，代码毕竟还是有很多局部是使用内存的，工作表函数则完全是不用；

zhxj1983

hfwufanhf2006 发表于 2019-6-28 16:40
速度当然是与数据量相关联的，但也不至于卡的那么厉害，比工作表函数要好得多，这个你可以放心。
工作表 ...

因为当这个表格的数据有5000条以上的时候就会慢了，如果我们减少前面再查找，只处理后面新录的数据，这样会不会再快一些，就是前面是空我也不管它，反正就是从客户，数量这两列有数据，但是后面这些全部为空，我们就从这一列开始找这样。这样速度会不会提高更多的