ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 效率神器,一键搞定繁琐工作
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
让更多数据处理,一键完成 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 2604|回复: 8

[原创] 申明变量与否及何种变量对代码执行速度的对比

[复制链接]

TA的精华主题

TA的得分主题

发表于 2013-4-10 17:01 | 显示全部楼层 |阅读模式
本帖最后由 lee1892 于 2013-4-11 09:45 编辑

本贴附件: 水样监测 by Lee1892.rar (351.15 KB, 下载次数: 38) ,速度对比可执行Sheet1对象内的SpeedCompare过程,表单内未作按钮。
原贴地址:[求助] 紧急求助高手,如何解决这个水样监测统计,字典法可能最好!
该贴原始数据中有9586个数据条目,每条记录有19个数据,需要找出15个或以上的数据完全一样的条目。显然,一个最为朴素的方法就是冒泡式大循环,保证任意两个条目都对比过一次,且仅对比一次。

原贴29楼northwolves给出了一个这样的代码,如下:

  1.     Dim arr, brr, t$, i&, j&, k&, l&, m&, n&, tt!
  2.     arr = [a1].CurrentRegion
  3.     ReDim brr(UBound(arr), 1)
  4.     tt = Timer
  5.     brr(0, 0) = "监测点编号"
  6.     brr(0, 1) = "相似监测点"
  7.     For k = 2 To UBound(arr) - 1
  8.         m = 0
  9.         t = ""
  10.         For i = k + 1 To UBound(arr)
  11.             n = 0
  12.             For j = 2 To 20
  13.                 If arr(i, j) <> arr(k, j) Then n = n + 1
  14.                 If n > 4 Then Exit For
  15.             Next
  16.             If n < 5 Then t = t & " " & arr(i, 1) & "(" & 19 - n & ")": m = m + 1
  17.         Next
  18.         If m Then
  19.             l = l + 1
  20.             brr(l, 0) = arr(k, 1)
  21.             brr(l, 1) = t
  22.         End If
  23.     Next
  24.     [aa1].CurrentRegion.ClearContents
  25.     [aa1].Resize(l + 1, 2) = brr
  26.     MsgBox Timer - tt & " seconds"

复制代码

在我的机器上,这样一段代码需要用时约69秒。

按照坛子里一些人的观点,这样一个简短的代码是不需要使用Dim语句申明任何变量的。那么将第一行语句注释掉,执行用时是多少呢?93秒!


仅仅是没有使用Dim语句申明变量,执行效率就下降了三分之一!

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-10 17:05 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 lee1892 于 2013-4-11 09:55 编辑

莫名其妙,不能发附件了~~~
=================================

下面这段代码,首先利用字典将原始数据的真实值转化为一个整数索引值,然后使用几乎完全一样的冒泡大循环对比,在我的机器上,执行速度是 28秒左右!

使用Dim语句申明变量与否对初学者而言真的不重要吗?果真是见仁见智的话题吗?

  1.     Dim arrData, i&, j&, k&, nItemNum&, nRowNum&, nDiff&, nCount&, sDiff$
  2.     Dim arrDics() As Object, arrCounts&(), arrRows&(), arrResult()
  3.     Dim t#
  4.     t = Timer
  5.     arrData = Cells(1, 1).CurrentRegion
  6.     nRowNum = UBound(arrData, 1) - 1
  7.     nItemNum = UBound(arrData, 2) - 1
  8.     ReDim arrDics(1 To nItemNum)
  9.     ReDim arrCounts(1 To nItemNum)
  10.     ReDim arrRows(1 To nRowNum, 1 To nItemNum)
  11.     ' 建立指标值字典数组
  12.     For i = 1 To nItemNum
  13.         Set arrDics(i) = CreateObject("Scripting.Dictionary")
  14.     Next
  15.     ' 将原始数据翻译成指标值索引,整数
  16.     For i = 2 To nRowNum + 1
  17.         For j = 2 To nItemNum + 1
  18.             If Not arrDics(j - 1).exists(arrData(i, j)) Then
  19.                 arrCounts(j - 1) = arrCounts(j - 1) + 1
  20.                 arrDics(j - 1)(arrData(i, j)) = arrCounts(j - 1)
  21.             End If
  22.             arrRows(i - 1, j - 1) = arrDics(j - 1)(arrData(i, j))
  23.         Next
  24.     Next
  25.     ' 清空缓存
  26.     For i = 1 To nItemNum
  27.         arrDics(i).RemoveAll
  28.         Set arrDics(i) = Nothing
  29.     Next
  30.     Erase arrData
  31.     Erase arrDics
  32.     Erase arrCounts
  33.     ReDim arrResult(0 To nRowNum, 0 To 1)
  34.     arrResult(0, 0) = "监测点编号"
  35.     arrResult(0, 1) = "相似监测点"
  36.     ' 冒泡大循环对比
  37.     For i = 1 To nRowNum - 1
  38.         sDiff = ""
  39.         For k = i + 1 To nRowNum
  40.             nDiff = 0
  41.             For j = 1 To nItemNum
  42.                 nDiff = nDiff + 1 + (arrRows(i, j) = arrRows(k, j))
  43.                 If nDiff > 4 Then Exit For
  44.             Next
  45.             If nDiff <= 4 Then
  46.                 sDiff = sDiff & " " & k & "(" & nItemNum - nDiff & ")"
  47.             End If
  48.         Next
  49.         If Len(sDiff) > 0 Then
  50.             nCount = nCount + 1
  51.             arrResult(nCount, 0) = i
  52.             arrResult(nCount, 1) = sDiff
  53.         End If
  54.     Next
  55.     With Cells(1, 27)
  56.         .CurrentRegion.ClearContents
  57.         .Resize(nCount + 1, 2) = arrResult
  58.     End With
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-11 09:58 | 显示全部楼层
原贴里有不少大家熟悉的ID,包括法师,嘿嘿~

冒泡式大循环是唯一的办法?没有更快的方式了吗?

移步俺的数据结构帖子吧~

TA的精华主题

TA的得分主题

发表于 2013-4-20 10:48 | 显示全部楼层
lee1892 发表于 2013-4-11 09:58
原贴里有不少大家熟悉的ID,包括法师,嘿嘿~

冒泡式大循环是唯一的办法?没有更快的方式了吗?

不声明变量(类型)的话,确实会对影响到执行效率,看了lee老师的数据结构帖,受益匪浅!本身不是学计算机的,但是对编程有些兴趣,深知数据结构对程序的效率和算法的好坏都起决定性作用,下了经典的数据结构教材,一直想好好学数据结构,看着看着就看不下去了,惭愧。

感谢lee老师的点悟,谢谢!{:soso_e183:}

TA的精华主题

TA的得分主题

发表于 2013-4-20 12:07 | 显示全部楼层
是否使用dim 速度差别在2-3倍。

而算法造成的速度差别通常在几十到几百倍。
狼版算法出来之前人家要算几小时的……呵呵。

备注,你的算法其实只要一个字典循环19次就可以了。比字典嵌套方法还可以提高一些速度。(大约0.3秒)






TA的精华主题

TA的得分主题

发表于 2013-4-20 12:17 | 显示全部楼层
想成为专业棋士就必须有声明,只想成为业务九段,那就乱棍齐舞吧。

TA的精华主题

TA的得分主题

发表于 2013-4-20 12:22 | 显示全部楼层
  1. Function CompareIt_by_kagawa()
  2.    
  3.     Dim i&, j&, k&, m&, n&, r&, s$, t, cnt&, tms#
  4.     tms = Timer
  5.    
  6.     arr = [a1].CurrentRegion
  7.     m = UBound(arr): n = UBound(arr, 2)
  8.    
  9.     ReDim brr&(2 To m, 2 To n)
  10.     Set d = CreateObject("Scripting.Dictionary")
  11.     For j = 2 To n
  12.         For i = 2 To m
  13.             t = d(CStr(arr(i, j)))
  14.             If t = "" Then k = k + 1: d(CStr(arr(i, j))) = k: t = k
  15.             brr(i, j) = t
  16.         Next
  17.         d.RemoveAll
  18.     Next
  19.     Set d = Nothing
  20. '    CompareIt_by_kagawa = Format(Timer - tms, "0.000s ") & "kagawa": Exit Function
  21.    
  22.     ReDim crr$(m, 1)
  23.     crr(0, 0) = "监测点编号": crr(0, 1) = "相似监测点"
  24.     For i = 2 To m
  25. '        s = ""
  26.         For k = i + 1 To m
  27.             cnt = 0
  28.             For j = 2 To n
  29.                 If brr(i, j) - brr(k, j) Then cnt = cnt + 1: If cnt > 4 Then Exit For
  30.             Next
  31.             If cnt < 5 Then s = s & " " & k - 1 & "(" & 19 - cnt & ")"
  32.         Next
  33.         If Len(s) Then r = r + 1: crr(r, 0) = i - 1: crr(r, 1) = s: s = ""
  34.     Next
  35.    
  36.     [aa1].CurrentRegion = ""
  37.     [aa1].Resize(r + 1, 2) = crr
  38.    
  39.     CompareIt_by_kagawa = Format(Timer - tms, "0.000s ") & "kagawa"
  40. End Function
复制代码

TA的精华主题

TA的得分主题

 楼主| 发表于 2013-4-20 12:27 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
香川群子 发表于 2013-4-20 12:07
是否使用dim 速度差别在2-3倍。

而算法造成的速度差别通常在几十到几百倍。

这种大循环对比方式毫无算法可言,呵呵~

TA的精华主题

TA的得分主题

发表于 2013-4-20 12:44 | 显示全部楼层
lee1892 发表于 2013-4-20 12:27
这种大循环对比方式毫无算法可言,呵呵~

呵呵,并非算法复杂度越高水平就越高。

越是水平高的人想出来的高效算法,实际上是比别人更简单。

…………
看了原始帖子,人家都准备要19取15组合后比较了……


您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-11-15 01:54 , Processed in 0.039867 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表