EXCEL VBA终极提速，超级公式，算法优化

逍遥爱迪生

1，简化公式，200个字变成10个字
有很多公式写的很长，看不懂很难维护，如果改成VBA代码，简化成一个超级函数，方便维护，还可以给多个工程共用，实现模块化开发
2，计算提速。有些vba运算很慢，需要几分钟到几小时，用好的方法，运行速度可以减到1分钟以内甚至10秒以内。
这2天做了一个3万个左右的订单表和产品销售统计的算法。
原来的主人，运行后就卡死了，后面按10个产品去查找订单，需要1秒左右，那3万个产品就要3000秒，差不多一个小时才能完成。
后面采用了字典的方法代替每个单元格去循环对比，速度提高了100倍，只需要30秒左右。
再后来发现写入3万行2列数据也要10秒左右，采用变量绑定直接写入，只需要60毫秒。
经过终极优化，最终只需要200毫秒就完成了全部运算。
速度又提高了150倍，比最初的算法速度提高了1.5万倍。

如果还能采用多线程进行计算，估计还能提高不少。不过总体时间只有0.2秒了，没必要再折腾了。他的想法是30秒以内就很好了。

逍遥爱迪生

2.3万个数据转成字典Key，不引用Dictionary，用时：5281毫秒
引用的话只要39毫秒，快了135倍

写入6.3万个excel单元格用时=2304毫秒
直接用数组方式改变一批单元格只要57毫秒，快了39倍
================
V12-引用Dictionary
数据行数=28627,条件末行=22943
条件转字典用时：39毫秒
取产品+数量+金额 数据用时：16毫秒
提取所有数据用时=67毫秒
统计用时=85毫秒
写入单元格(超级法)用时=57毫秒

【借方V12】 >用时209毫秒,约0.003分钟
-----------
'v12不引用Dictionary
数据行数=28627,条件末行=22943
条件转字典用时：5281毫秒
取产品+数量+金额 数据用时：20毫秒
提取所有数据用时=5310毫秒
统计用时=6588毫秒
写入单元格(超级法)用时=59毫秒
借方V12 >用时11957毫秒
==============
2.86万个订单，查找的产品2.29万个
不用字典，纯循环查找用时17.3秒
字典生成39毫秒，查找用时85毫秒

数据行数=28627,条件末行=22943
条件转字典用时：0毫秒
取产品+数量+金额 数据用时：16毫秒
提取所有数据用时=29毫秒
统计用时=17252毫秒
写入单元格(超级法)用时=62毫秒

【借方V12】 >用时17343毫秒,约0.289分钟

yynrzwh

“再后来发现写入3万行2列数据也要10秒左右，采用变量绑定直接写入，只需要60毫秒。”
请问变量绑定写入是怎么个绑定？

逍遥爱迪生

arr=range("a1:b10")
arr(）修改之后，再range("a1:b10")=ARR
就是把EXCEL表格的数据转换成数组，再把数组修改后写回到多个单元格。
我QQ:527524938

hhxq001

逍遥爱迪生 发表于 2024-5-25 21:55
arr=range("a1:b10")
arr(）修改之后，再range("a1:b10")=ARR
就是把EXCEL表格的数据转换成数组，再把数 ...

在数组里操作修改，然后再写回单元格吗

nihao123456789

用VBA处理大量规范数据，可以使用ADO连接excel，当成数据库数据用SQL语句做。直接输出想要的结果。或者通过记录集二次处理。也可以转到字典二次处理。这样30万行都信手拈来

逍遥爱迪生

hhxq001 发表于 2024-6-3 07:21
在数组里操作修改，然后再写回单元格吗

是的，直接读写单元格操作也可以，用数据库也可以。

wang-way

自言自语系列？