ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

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

[推荐] 在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 编辑 ]

评分

2

查看全部评分

TA的精华主题

TA的得分主题

发表于 2010-1-8 22:54 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
楼主辛苦 这个需要慢慢消化

TA的精华主题

TA的得分主题

发表于 2010-1-8 23:03 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
进来学习下

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 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
我晕啊,你第三个是数组啊。

TA的精华主题

TA的得分主题

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

TA的精华主题

TA的得分主题

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

TA的精华主题

TA的得分主题

发表于 2010-7-24 07:14 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
03上不知能否这样用。。
另外楼主提供下博客的链接地址

TA的精华主题

TA的得分主题

发表于 2010-7-24 09:58 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
谢谢,学习下
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

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

GMT+8, 2024-3-29 02:09 , Processed in 0.052224 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

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

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

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