Excel-VBA:14、数组

技术博客 (168) 2023-12-01 18:01:01

excel数据存放的位置:单元格和内存

如果数据存放再单元格中,系统计算需要两步:1,提取数据,加载到内存中;2、运算内存中的数据。而数据如直接在内存中,则可以直接进行运算,从而节省大量的时间,注意:是大量的时间。

数组:可以将一组数据存储在内存中,可以存放文本、数值、对象等。

VBA数组是以变量形式存放的一个空间。

数组分类:常量数组、静态数组和动态数组

常量数组:数组空间大小和元素均已确定

静态数组:数组空间大小已确定,但数组中的元素尚未确定

动态数组:数组的空间和元素都未确定

1、数组写入数据

1.1、一维数组写入数据

Sub test()
Dim arr(10), x!
For x = 0 To 10
 arr(x) = x
Next x
End Sub

注意

arr(10) 的取值范围是[0,10],一共11个数字,如果要指定从1到10,则指定具体的取值范围:arr(1 To 10)

一维数组即单行单列结构

1.2、二维数组写入数据

Sub test()
Dim arr(1 To 5, 1 To 3), x!, y!, i!
For x = 1 To 5
 For y = 1 To 3
 arr(x, y) = i
 i = i + 1
 Next y
Next x
End Sub

说明

二维数组即多行多列组成的区域

三维数组是工作表和区域共同组成的结构,如arr(工作表,行,列)

2、动态数组

动态数组使用需要两步

第一步:声明数组变量,不指定空间大小

[代码块]

第二步:ReDim 数组变量名(数组空间大小)

3、数组的批量写入

3.1、使用Array函数写入

Sub t()
Dim arr ' 不可以指定类型
arr = Array(1,2,3,"a")
End Sub

3.2、单元格区域写入

结果:arr是一个由B3:F5值组成的二维数组,如果由空单元格,值为空

Sub test()
Dim arr
arr = [b3:f5]
End Sub

结果:arr是一个由B3:F5值组成的二维数组,如果由空单元格,值为空

注意

如果Dim arr声明了一个数组,则此数组为二维数组,即使arr=[a1:a10],该数组也是一个10行1列的二维数组

4、将数组写入到单元格

4.1、二维数组写入

Sub test()
Dim arr, arr1(1 To 5, 1 To 1) ' 务必注意arr1(5,1)的坑,其代表6行2列
Dim x!
arr = Range("b2:c6")
For x = 1 To 5
 arr1(x, 1) = arr(x, 1) * arr(x, 2)
Next x
Range("d2").Resize(5) = arr1
End Sub

4.2、一维数组写入

Sub test()
Dim arr, arr1(1 To 5)
Dim x!
arr = Range("b2:c6")
For x = 1 To 5
 arr1(x) = arr(x, 1) * arr(x, 2)
Next x
Range("d2").Resize(5) = Application.WorksheetFunction.Transpose(arr1)
End Sub

说明

一维数组默认是行结构,如果需要列结构,需要使用Transpose函数

4.3、数组部分写入

Sub test()
Dim arr, arr1(1 To 50)
Dim x!
arr = Range("b2:c6")
For x = 1 To 5
 arr1(x) = arr(x, 1) * arr(x, 2)
Next x
Range("d2").Resize(5) = Application.WorksheetFunction.Transpose(arr1)
End Sub

说明

1、数组的有效数据不能少于存储单元格数量,如上例中,数组大小为50个,有效数据5个,所需要存储的单元格为5个,则可正常存入。

2、arr = range(),只会讲range的value属性赋值给数组arr,如果需要装载range对象本身,需使用set arr = range()语句。

5、数组的排序

数组使用编号排序的,可以通过Lbound方法和Ubound方法获取数组的上下限编号

Lbound(数组)可以获取数组的最小编号(下标)

Ubound(数组)可以获取数组的最大编号(上标)

Ubound(数组,1)获得二维数组的行上标

Ubound(数组,2)获得二维数组的列上标

Lbound反之

示例

Sub test()
Dim arr
Dim x!
arr = Range("a2:d5")
For x = 1 to Ubound(arr,1)
​
Next x
End Sub

6、动态数组的动态扩充

使用以下语句来扩充原先的数组,并且保留原来的数据

ReDim Preserve arr()

ReDim Preserve改变最末维的上界,且不能改变数组维数。如果改变下标,则出错。

一维数组动态扩充办法:直接扩充

多维数组动态扩充办法:逐个维度扩充,中间使用转置函数转换维度。

技巧

如果可以估算出动态数组的上界,则可以设定一个上界很大的数组,然后正常操作,最后截取有效数据部分。

7、强制声明数组下标

数组的下标除了在生命时规定下界(如dim arr(1 to 5))之外,可以使用强制生命来改变数组下界不以0开始

Option Base 数字

示例

Option Base 1 ' 强制声明数组下界从1开始

8、循环速度 

在循环数组时,使用for each循环结构比for next结构要快,但不足之处是for each不支持修改数组的元素(即修改语句无效)

示例

for each无法修改数组元素

Sub test()
Dim arr, element
arr = [a1:b12]
For Each element In arr
 element = element - 1
Next element
End Sub

结果:a1:b12单元格值不变。

注意

无论数组是什么数据类型,for each的循环变量必须是Variant类型。

9、清空数组

Erase 方法

清空数组

语法

Erase 数组名称

10、函数与数组

10.1、可以使用VBA函数计算数组,如VBA.Filter函数

10.2、可以使用工作表函数计算数组,如Application.Max(arr1)

11、数组与单元格格式

取得单元格的地址,然后放入到Range中,然后设置Range的单元格属性

其中注意Range中的字符长度<=255。

技巧

因Range有255个字符限制,所以在获得单元格地址时,可以只获得单元格的行号(同一个Ragne可以放置更多的结果,提高运行速度),然后使用Application.Intersect()方法获取行列的交集,从而定位到目标单元格。

12、排序算法

12.1、冒泡排序

每一个数字与其位置之后的所有数字进行比较,如大于后面的数字,位置不变,如小于后面的数字,双方互换。

示例

Sub 冒泡排序()
Dim arr,temp,x,y,k
arr = Range("a1:a10")
For x = 1 To Ubound(arr) - 1
 For y = x + 1 To Ubound(arr)
 If arr(x,1) > arr(y,1) Then
 temp = arr(x,1)
 arr(x,1) = arr(y,1)
 arr(y,1) = temp
 End If
 Next y
Next x
End Sub

12.2、选择排序

从后往前循环,每一个数字与位置之前的最大值进行比较互换。

选择排序比冒泡排序速度快

示例

Sub 选择排序()
Dim arr,temp,x,y,iMax,k,k1,k2
arr = Range("a1:a10)
For x = Ubound(arr) To 1+1 Step -1
 iMax = 1
 For y = 1 To x
 If arr(y,1) > arr(iMax,1) Then iMax = y
 Next y
 temp = arr(iMax,1)
 arr(iMax,1) = arr(x,1)
 arr(x,1) = temp
Next x
End Sub

13.3、插入排序

从数组的第2个元素开始往后循环,每一个数字与它之前的数字比较,如果位置之前的数字大于其,则将该数字与其互换,放置其后,否则,位置不变。

13.4、希尔排序

引用“

希尔排序对 未排序数据的顺序 不敏感,虽然平均而言速度比快速排序慢一倍左右

但是却安全很多,没有快速排序的堆栈溢出问题,没有退化为O(n^2)的问题

而且对比较有序的输入数据,重复度高的数据,速度远远超过快速排序。

所以还是一个很不错的排序算法,尤其是还可以跟快速排序结合,大大降低快速排序的递归次数。

希尔排序实现起来比也非常简单。就是多重循环比较不好看而已。

附件的希尔排序已经做成一个子程序,在任何程序直接调用即可。

希尔排序的速度关键是:选择合适的h序列,也就是每次循环跳过多少个数据来交换

再啰嗦一下h序列的问题,假设已经知道希尔排序使用的h序列是怎么回事

那么比较常用的,已经经过计算机界的大师验证,绝对慢不到哪里去的h序列,有以下几种

1 h1=1, 然后往后每个h都等于 3h+1

2 h1=1, 然后往后每个h都等于 2.25h+1,取整

3 这个比较复杂, h1当然也是1,h2往后则用公式计算出来,假如

[n]是单数,那么 h[n] =82^n-62^((n+1)/2)+1

[n]是双数,那么 h[n] =92^n-92^(n/2)+1

也就是说 h序列 = {1,,5,19,41,109,209,505,929,........}

这一个序列已经被数学证明为O(n^1.25)的增长阶,证明过程则估计我完全看不懂。。。。。。

4 我自己的发现,由于h序列一般来说,前后两个h是互质的会比较好,而我又发现3号序列并不完全是质数

于是就把所有的非质数换为最接近的质数,结果发现又快了3%左右,yeah! 这也是我的附件代码最终采用的h序列

论坛以前发过的希尔排序贴,貌似都是用 h[n] = 2^n - 1,或者干脆就是 h[n] = 2^n

前者貌似也是O(n^1.25),但是明显比前面四个序列慢一大截

后者则是算法发明人希尔最早提出的序列,但是很快就被证明是个非常坏的h序列,速度慢而且很可能会退化到O(n^2),所以不宜采用。

*************以下讨论堆排序

堆排序的速度,一般比希尔排序慢10%到20%

基本的堆排序,速度很慢,原因是每次交换最大的元素到数组末尾,这时候数组末尾的元素将交换到堆顶

再次把这个元素放进堆里面,就是把一个很小的元素压进堆,比较的次数将会很多,几乎必定是log(n)

所以采用Floyd的算法,先把这个很小的堆顶元素压到最底层,然后再上升到合适位置,这样就可以加快一倍

附件是四个堆排序算法:分别是最基本的堆排序,三元分叉的堆排序,最基本堆排序+先沉底再上升(最佳版本),三元的堆排序+先沉底再上升

可以看出,基本三元堆比基本两元堆快,而且先沉底再上升的做法,对三元堆,两元堆都有效

而令人惊奇的是:两元堆先沉底再上升,比三元堆先沉底再上升更快,可能是三元堆提升三个叶子的最大元素,其比较次数太多

这倒是件好事,因为两元堆的代码比较简单,而且速度更快,好极了。

堆排序最大特点:可以迅速取出最大值(或者最小值),如果是求100万元素的前1万个,那么堆排序肯定是最快的。

因为只需要对1万个元素进行堆处理,而其它排序方法需要对100万个元素排序。

嗯。。。。。。也许快速排序也可以缩小排序的范围,如超过1万的部分就不再递归排序,也许还有可能比堆排序快。

*************以下讨论快速排序

作为一般而言最高速的排序算法,快速排序既让人高兴,又让人不放心

高兴的是:速度真的快多了

不放心的是:碰到某些序列,排序时间会恶化到无法接受的O(n^2)

附件是六个版本的快速排序,基本上包括了对快速排序进行优化的各种技巧

作为对比,第一个最慢速最不可靠的版本是从论坛帖“七种排序算法”抄过来的

其余五个版本是我自己的作品

简而言之,快速排序的弊端是

1 递归次数太多,会导致堆栈空间溢出。

解决办法:对小于一定数量的元素进行直接插入排序,希尔排序,而不是留给下一层的递归快速排序

2 每次划分的枢纽值选取不当,就会大大影响速度

解决办法:用平均值,随机值等等方式来决定枢纽值,但是无法保证一定不会出现速度退化的现象

进一步解决方法:先用快速排序,然后如果超过一定时间,或者递归一定次数后还没完成,就完全转为希尔排序。

综合而论,我个人觉得还是希尔排序最可靠,堆排序由于每次都取出最大值的特点,有些时候是非它不可!

快速排序单纯用原始版本,是一个又慢又危险的算法,完全没有任何价值

改良以后速度极好,但不能完全放心,辅以希尔排序才是王道。

补充汉字的排序方式:

Excel本身的sort方法,有两种汉字排序方式

名称 值 描述

xlPinYin 1 按字符的汉语拼音顺序排序。这是默认值。

xlStroke 2 按每个字符的笔划数排序。

VBA里面,字符串比大小也有两种方式

option compare binary 对应按笔画数排序(默认值)

option compare text 对应按拼音排序,速度大概慢一倍吧

THE END

发表回复