|
楼主 |
发表于 2013-4-10 17:05
|
显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
本帖最后由 lee1892 于 2013-4-11 09:55 编辑
莫名其妙,不能发附件了~~~
=================================
下面这段代码,首先利用字典将原始数据的真实值转化为一个整数索引值,然后使用几乎完全一样的冒泡大循环对比,在我的机器上,执行速度是 28秒左右!
使用Dim语句申明变量与否对初学者而言真的不重要吗?果真是见仁见智的话题吗?
- Dim arrData, i&, j&, k&, nItemNum&, nRowNum&, nDiff&, nCount&, sDiff$
- Dim arrDics() As Object, arrCounts&(), arrRows&(), arrResult()
- Dim t#
- t = Timer
- arrData = Cells(1, 1).CurrentRegion
- nRowNum = UBound(arrData, 1) - 1
- nItemNum = UBound(arrData, 2) - 1
- ReDim arrDics(1 To nItemNum)
- ReDim arrCounts(1 To nItemNum)
- ReDim arrRows(1 To nRowNum, 1 To nItemNum)
- ' 建立指标值字典数组
- For i = 1 To nItemNum
- Set arrDics(i) = CreateObject("Scripting.Dictionary")
- Next
- ' 将原始数据翻译成指标值索引,整数
- For i = 2 To nRowNum + 1
- For j = 2 To nItemNum + 1
- If Not arrDics(j - 1).exists(arrData(i, j)) Then
- arrCounts(j - 1) = arrCounts(j - 1) + 1
- arrDics(j - 1)(arrData(i, j)) = arrCounts(j - 1)
- End If
- arrRows(i - 1, j - 1) = arrDics(j - 1)(arrData(i, j))
- Next
- Next
- ' 清空缓存
- For i = 1 To nItemNum
- arrDics(i).RemoveAll
- Set arrDics(i) = Nothing
- Next
- Erase arrData
- Erase arrDics
- Erase arrCounts
- ReDim arrResult(0 To nRowNum, 0 To 1)
- arrResult(0, 0) = "监测点编号"
- arrResult(0, 1) = "相似监测点"
- ' 冒泡大循环对比
- For i = 1 To nRowNum - 1
- sDiff = ""
- For k = i + 1 To nRowNum
- nDiff = 0
- For j = 1 To nItemNum
- nDiff = nDiff + 1 + (arrRows(i, j) = arrRows(k, j))
- If nDiff > 4 Then Exit For
- Next
- If nDiff <= 4 Then
- sDiff = sDiff & " " & k & "(" & nItemNum - nDiff & ")"
- End If
- Next
- If Len(sDiff) > 0 Then
- nCount = nCount + 1
- arrResult(nCount, 0) = i
- arrResult(nCount, 1) = sDiff
- End If
- Next
- With Cells(1, 27)
- .CurrentRegion.ClearContents
- .Resize(nCount + 1, 2) = arrResult
- End With
复制代码
|
|