ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

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

[推荐] 在Excel中遍历大范围Range的最快方法

[复制链接]

TA的精华主题

TA的得分主题

发表于 2010-1-8 22:39 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:Range对象
(微软Excel官方博客上10月3日发表的文章)

今天的作者Dany Hoter(Excel组的产品计划员)将要讨论他最近发现的使用VBA操作大范围Range的各种方法之间的性能特性。

问题描述
你有一个很大的Range的Excel数据。就说是10万行,每行50列(当然是用Excel2007)。这样一共有5百万个单元格的数据。A到F列有你 需要分析的包含文字和字母的数据,根据每一行数据的组合,你需要使用G到H列的数字做一些计算并保存在I和J列。你也可以在I和J列放入20万个公式,但 你肯定知道有这么大数量的公式的表格会运行很慢并且占用很大的内存空间。

你决定尝试用VBA代码来解决这个难题。问题是怎样以最有效的方法来完成这样的任务?

你的选择
你怎样才能扫描Excel中的一个区域,从某些单元格中读取数据,然后更改其他某些单元格的内容?

1.使用Range对象

假设你想读取的区域是从A1开始的。代码就看起来应该这样:

Dim DataRange as Range       ' Could also be Dim DataRange as Object
Dim Irow as Long
Dim MaxRows as Long
Dim Icol as Integer
Dim MaxCols as Long
Dim MyVar as Double
Set DataRange=Range("A1").CurrentRegion
MaxRows= Range("A1").CurrentRegion.Rows.Count
MaxCols= Range("A1").CurrentRegion.Columns.Count
For Irow=1 to MaxRows
   For icol=1 to MaxCols
      MyVar=DataRange(Irow,Icol)
      If MyVar > 0 then
         MyVar=MyVar*Myvar        ' Change the value
         DataRange(Irow,Icol)=MyVar
      End If
   Next Icol
Next Irow

2.使用Selection,并使用Offset移动

许多VBA开发者是从记录宏来学习VBA技巧。
当时有相对引用时,生成的VBA代码如下:
ActiveCell.Offset(0, -1).Range("A1").Select
结果,很多开发者采用这种方法,在代码中使用ActiveCell或Selection区域来在单元格之间移动,读取或写入数据。代码应该就像这样的:
Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
Range("A1").Select
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
   For Icol = 1 To MaxCols
      MyVar = ActiveCell.Value
      If MyVar > 0 Then
         MyVar=MyVar*Myvar ' Change the value
         ActiveCell.Value = MyVar
      End If
      ActiveCell.Offset(0, 1).Select       ' Move one column to the right
   Next Icol
   ActiveCell.Offset(1, -MaxCols).Select       ' Move one rows down and back to first column
Next Irow

3.使用Variant类型变量
这个方法复制区域中所有单元格的值到内存中的一个变量,然后在变量中处理数值,如果需要的话,处理完后也可以将数字移回到该区域。
下面是这次的代码:
Dim DataRange As Variant
Dim Irow As Long
Dim MaxRows As Long
Dim Icol As Integer
Dim MaxCols As Long
Dim MyVar As Double
DataRange = Range("A1").CurrentRegion.Value      ' Not using set
MaxRows = Range("A1").CurrentRegion.Rows.Count
MaxCols = Range("A1").CurrentRegion.Columns.Count
For Irow = 1 To MaxRows
   For Icol = 1 To MaxCols
      MyVar = DataRange(Irow, Icol)
      If MyVar > 0 Then
         MyVar=MyVar*Myvar        ' Change the value
         DataRange(Irow, Icol) = MyVar
      End If
   Next Icol
Next Irow
Range("A1").CurrentRegion = DataRange     ' writes back the result to the range

比较前两个方法,这个方法的一个区别是速度非常之快。

性能总结
我使用相对较大的区域来比较这三个方法,下面是结果:
   方法    操作   单元格 / 秒
Variant       读       1,225,490
                  写        714,286
                  读/写     263,158
Range        读       250,000
                  写       1818
                  读/写     1,852
Offset        读        206
                  写        200
                  读/写     203
你可以看到使用Variant变量能够获得快很多的速度,尤其是改变单元格的时候。即使可以通过Excel公式来完成计算,在某些情况下这个方法是唯一可 以接受的,因为使用非常大量的公式会导致Excel非常缓慢。 明显需要避免的方法是使用Offset来移动ActiveCell。


========================================================
其实应该还有别的选择,如for each:
Dim MyVar as Double
For Each C In Range("A1").CurrentRegion
    MyVar=C.Value
    If MyVar > 0 then
         MyVar=MyVar*Myvar        ' Change the value
         C.Value=MyVar
      End If
Next C
Set C=Nothing
(其实这种方法本质还是Range)

[ 本帖最后由 hhh5460 于 2010-11-7 19:26 编辑 ]

评分

参与人数 1财富 +10 收起 理由
yanjie + 10 感谢支持

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-1-8 22:54 | 显示全部楼层
楼主辛苦 这个需要慢慢消化

TA的精华主题

TA的得分主题

发表于 2010-1-8 23:03 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-1-9 09:04 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-1-9 09:27 | 显示全部楼层
DataRange = Range("A1").CurrentRegion.Value  
DataRang 其实就是论坛中无数帖子中都有提及的、传说中的-----数组。

TA的精华主题

TA的得分主题

发表于 2010-7-23 12:40 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-7-24 00:20 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-7-24 01:18 | 显示全部楼层
vba程序优化要求少用变体类型变量,看来事情没有绝对的。学习。谢谢!

TA的精华主题

TA的得分主题

发表于 2010-7-24 07:14 | 显示全部楼层
03上不知能否这样用。。
另外楼主提供下博客的链接地址

TA的精华主题

TA的得分主题

发表于 2010-7-24 09:58 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

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

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

GMT+8, 2019-10-14 08:42 , Processed in 0.076763 second(s), 14 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 1999-2020 Wooffice Inc.

   

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

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

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