老师给的大青枣烤蹄，现交作业。

excelflower · 发表于 2010-12-10 13:45

原帖由 liuguansky 于 2010-12-10 11:39 发表

关于238的问题：
RANGE最大支持字符串是255
而最后一次连接的最大文本是",A99999：A100000"加一个预留位为16
可以用239判断，238只是为了吉利嘛。。

多谢老师赐教！！！

灰袍法师 · 发表于 2010-12-10 16:11

原帖由 liuguansky 于 2010-12-10 11:43 发表

学习UNION用法了。这样又减少了单元格对象的调用。果然快了。不过分组判断更长了。

其实我现在发现，UNION跟利用字符串 Set RANGE 一样

只能线性降低耗时，所以最终速度没差多少

原因是

1 字符串SET RANGE存在255字符长度限制，所以只能把 100万个区域合并为 255/ 5到16 大概是20-30倍
也就是说用字符串大概可以减少上色次数到 1/25

2 UNION大概只能连接 30个120 长度的字符串，超过这个范围就急剧减速，也就是说大概连接 200个区域,减少上色次数 1/200

然后呢，以上两个方法都减少了上色的次数，但是增加了处理字符串，或者调用UNION函数的时间，而且较大的区域，上色时间也比小区域慢一点

所以实际加速效果根本达不到理论值，两种方法合用也不会好多少

因为每种方法都是有可处理区域的上限，互相制约，还外加大区域的上色速度降低。

现在处理 100万行，即60万个单元格，部分相邻的当作一个区域，实际上只有大约25万区域

简单逐个单元格上色也就30秒

字符串SET RANGE的速度将是 25万 /25 = 2万个区域的上色时间，大概是2秒，加上处理字符串的时间，大概是5秒

单纯用UNION合并单元格将是合并两次 60万 / 30 = 2万,然后2万/30=700

一共2万次30单元格的UNION,700次 900单元格的UNION，然后大区域上色700次左右, 大概是8秒

两个方法合用则可以达到3秒之内，不过确实没法再提高了，255长度限制＋UNION容量限制＋大区域上色速度限制，绕不过啊。

sunsoncheng · 发表于 2010-12-10 16:28

1\法师索性就总结一下这个帖并给出合理的通用程序吧

2\最好组织下人将其它的,有类似问题的函数找出来

现在回想下曾遇到过的UNION问题,幸好我用的量都不大,否则,就想S了

Zamyi · 发表于 2010-12-10 16:34

原帖由 灰袍法师 于 2010-12-10 16:11 发表

其实我现在发现，UNION跟利用字符串 Set RANGE 一样

只能线性降低耗时，所以最终速度没差多少

原因是

1 字符串SET RANGE存在255字符长度限制，所以只能把 100万个区域合并为 255/ 5到16 大概是20-30倍 ...

100W光分出地址就要1.6秒，无论怎样组合，上色在2.5秒已是极限。象下面，上色要1.2W次，如果能用with Range(Mid(s, 2)).Interior，可以减少1秒多，可惜动态区域没法办到。
Sub Test()
SJY 1000000
[a:a].ClearFormats
t = Timer
Application.ScreenUpdating = False
n = Cells(1048576, 1).End(3).Row
nl = Len(n)
r = Range("a1:a" & n)
i = 1
While i < n - 1
While r(i, 1) >= 60
   i = i + 1
Wend
j = i
While r(i, 1) < 60 And i < n
   i = i + 1
Wend
If i = j + 1 Then s = s & ",A" & i - 1 Else s = s & ",A" & j & ":A" & i - 1
If Len(s) > 252 - 2 * Len(i - 1) Then
   'Range(Mid(s, 2)).Interior.ColorIndex = 3
   s = ""
   k = k + 1
   End If
  Wend
'If s <> "" Then Range(Mid(s, 2)).Interior.ColorIndex = 3
'If r(n, 1) < 60 Then Cells(n, 1).Interior.ColorIndex = 3
Application.ScreenUpdating = True
[d1] = "K值：": [e1] = k
[d2] = "用时：": [e2] = Timer - t
End Sub
Sub SJY(n)
Dim a()
[a:a].Clear
ReDim a(1 To n, 1 To 1)
For i = 1 To n
  a(i, 1) = Int(Rnd() * 100) + 1
  Next
[a1].Resize(n) = a
End Sub

excelflower · 发表于 2010-12-10 16:51

又来货了

好好研究！多谢楼上三位老师！

灰袍法师 · 发表于 2010-12-10 18:55

原帖由 Zamyi 于 2010-12-10 16:34 发表

100W光分出地址就要1.6秒，无论怎样组合，上色在2.5秒已是极限。象下面，上色要1.2W次，如果能用with Range(Mid(s, 2)).Interior，可以减少1秒多，可惜动态区域没法办到。
Sub Test()
SJY 1000000
[a:a].ClearF ...

是的，你说的没错，现在综合起来，反倒是分离地址，处理字符串的耗时最多了，真是峰回路转啊。

字符串SET RANGE的妙法，我没看错的话，是office2008老师首先提出的吧？一直顾着抄袭，忘记致谢office2008，真是对不起了。

如果反过来，不是把<60上色，而是全部上色，然后把>=60去色，或许可以再快一丁点。

附件是一个作弊的做法

首先，把所有单元格上红色，这一步不算进总时间（不然要耗时0.35秒的）

然后，把>=60的去色，这样可以减少一丁点要处理的单元格

然而，其加速的地方其实是，把用于SET RANGE的字符串最大化(235长度)，减少字符串生成的耗时

然后，仔细调整UNION合并的大小，使其既不会大大减速，又可以尽量合并字符串RANGE，现在是每次合并9个最快

104万行是2.5到2.7秒

考虑到直接用数值填充104万行，也需要差不多2.5秒，所以这个速度很令人满意了。

最后，我测试了有/无条件格式的填充速度，结果是

条件格式几乎完全不影响填充单元格的速度，所以最佳做法肯定是条件格式，而不是VBA去处理

[ 本帖最后由灰袍法师于 2010-12-10 20:01 编辑 ]

excelflower · 发表于 2010-12-10 19:56

原帖由 灰袍法师 于 2010-12-10 18:55 发表

是的，你说的没错，现在综合所有聪明的方法，反倒是分离地址，处理字符串的耗时最多，哈哈哈。

如果反过来，不是把=60去色，或许可以再快一丁点。

附件是一个作弊的做法

首先，把所有单元格上红色，这一 ...

老师的办法真多！学习了！

这个帖最大的意义是大家通过一个实例，多种途径探求实现目标的方法。

并在此过程中，测试各种VBA函数、方法的效率，并发现其限制。

目前应然有问题尚未清楚，例如SpecialCells在10W、2007版本出错的原因，哪个官方资料有介绍？避免大家在应用SpecialCells时出现未知错误。

这楼，还得建

mingg · 发表于 2010-12-19 01:28

收藏学习！

praijna · 发表于 2010-12-21 16:36

只能学习的份儿，感谢分享！

jxnu · 发表于 2011-4-5 20:30

收藏了，学习学习。

		自动登录	找回密码
密码			免费注册

[讨论] 老师给的大青枣烤蹄，现交作业。