ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

EH搜索     
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 挑战你的Excel知识,一起测验下 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
楼主: excelflower

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

[复制链接]

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-12-10 13:45 | 显示全部楼层
本帖已被收录到知识树中,索引项:Range对象
原帖由 liuguansky 于 2010-12-10 11:39 发表

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

多谢老师赐教!!!

TA的精华主题

TA的得分主题

发表于 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容量限制+大区域上色速度限制,绕不过啊。

TA的精华主题

TA的得分主题

发表于 2010-12-10 16:28 | 显示全部楼层
1\法师索性就总结一下这个帖并给出合理的通用程序吧

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

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

TA的精华主题

TA的得分主题

发表于 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

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-12-10 16:51 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 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 编辑 ]

VBA - Union函数 + Set字符串Range大量修改单元格格式.rar

19.62 KB, 下载次数: 44

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-12-10 19:56 | 显示全部楼层
原帖由 灰袍法师 于 2010-12-10 18:55 发表


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

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

附件是一个作弊的做法

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


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

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

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

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

这楼,还得建

TA的精华主题

TA的得分主题

发表于 2010-12-19 01:28 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-12-21 16:36 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2011-4-5 20:30 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关注官方微信,高效办公专列,每天发车

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

GMT+8, 2019-9-23 14:53 , Processed in 0.076949 second(s), 12 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

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

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

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