使用VBA数组公式——Excel之VBA(11)

一、 认识VBA数组及常用操作

引例：计算所属区域的总金额

Sub test() Dim i, k Dim t t = Timer '获取时间值 For i = 2 To 200000 If Range("g" & i) = Range("n5") Then k = k + Range("j" & i) End If Next t = Timer - t '得到时间差 Range("p5") = k MsgBox Format(t, "0.00000") '显示时间差 End Sub t = 2.07

Sub test() Dim i, k Dim t Dim str As String t = Timer '获取时间值 str = Range("n5") ' 优化取数过程，加快执行效率 For i = 2 To 200000 If Range("g" & i) = str Then k = k + Range("j" & i) End If Next t = Timer - t '得到时间差 Range("p5") = k MsgBox Format(t, "0.00000") '显示时间差 End Sub t = 1.18

Sub test() Dim i, k Dim t Dim str As String Dim arr() t = Timer '获取时间值 str = Range("n5") '优化取数过程，加快执行效率 arr = Range("g1:j200000") '将数据源放进数组中存放，进一步加快效率 For i = 2 To 200000 If arr(i, 1) = str Then k = k + arr(i, 4) End If Next t = Timer - t '得到时间差 Range("p5") = k MsgBox Format(t, "0.00000") '显示时间差 End Sub t = 0.164

1. 什么是数组

2. 如何定义数组

一维数组	二维数组
Sub test() dim arr (0 to 3) '定义的数组起始值不一定要从0，如dim arr(1 to 4) arr(0) = "张三" arr(1) = "李四" arr(2) = "王五" arr(3) = "赵六" range("b1") = arr(2) '手动录入数组是一维的 range("a10:g10") = arr ' 一维数组是横的，直接赋值整个数组要横着赋，即一条记录 ' 当数组的值已经输出完，继续输出，后一个值为空，再后会报错N/A end Sub	Sub test() Dim arr() '不设置起始值 arr = Range("a1:a5") '直接录入数据区域 Range("c1") = arr(2, 1) '尽管录入的数据区域是1列的也是二维数组，arr（行，列） End Sub

 要点：

'定义的数组起始值不一定要从0，如dim arr(1 to 4)

'手动录入数组是一维的

' 一维数组是横的，直接赋值整个数组要横着赋，即一条记录

' 当数组的值已经输出完，继续输出，后一个值为空，再后会报错N/A

'尽管录入的数据区域是1列的也是二维数组，arr（行，列）

3. 动态数组与静态数组

要点：

静态数组：定义时设置了起始值

动态数组：定义时不设置起始值

引例：

Sub test()

Dim arr() '定义动态数组
Dim j, i As Integer

j = Range("a65536").End(xlUp).Row - 1 ' 取产品的行号
'如果上面不定义arr，而直接在这里
' Dim arr(1 to j)
'会报错，arr的定义必须是常量表达式

ReDim arr(1 To j) '重定义数组，因为上面定义了arr，所以这里是重定义


For i = 1 To j
    arr(i) = Range("b" & i + 1) * Range("c" & i + 1) '计算产品销售额
    
Next

Range("h3") = Application.WorksheetFunction.Max(arr) '调取产品最大销售额

'Application.WorksheetFunction.Match(Range("h3"), arr, 0) 匹配最大销售额的位置
Range("h2") = Range("a" & Application.WorksheetFunction.Match(Range("h3"), arr, 0) + 1)

 MsgBox UBound(arr) '弹出arr数组的上限
 MsgBox LBound(arr) '弹出arr数组的下

End Sub

 

要点：

' ReDim arr(1 To j) '重定义数组，因为上面定义了arr，所以这里是重定义
 MsgBox UBound(arr) '弹出arr数组的上限
 MsgBox LBound(arr) '弹出arr数组的下限

4. 计算数组的大小 Ubound和Lbound

Ubound：U代指UP，指的是上边界

Lbound：L代指Low，指的是下边界

5. 重定义数组大小 ReDim [Preserve] Arr(10)

二、数组应用实例解析

如何将区域赋值给数组
数组的维度
给数组赋值与取值

三、 本节案例演示

1）使用数组遍历计算多条件求和
2）查找销售额最高的商品
3）排列组合计算回款金额

说明：示例代码简单采用了暴力算法

Sub test()

Dim i, j, k, l As Integer

t = Timer

For i = 2 To 80
    For j = 2 To 80
        For k = 2 To 80
            For l = 2 To 80

                If Range("a" & i) + Range("a" & j) + Range("a" & k) + Range("a" & l) = 124704 Then '如果四个相加等于回款信息
                    Range("f3") = Range("a" & i) '记录回款信息
                    Range("g3") = Range("a" & j)
                    Range("h3") = Range("a" & k)
                    Range("i3") = Range("a" & l)

                    GoTo 100 '单单只是exit for是不行的，只会退出一层，这里用GoTo跳出所有循环

                End If
            Next
        Next
    Next
Next

100

t = Timer - t
MsgBox t


End Sub                                                 

t = 1.925

 优化算法：

Sub test()

Dim i, j, k, l As Integer

t = Timer

arr = Range("a1:a80") '把数据录入数组，避免重复取数，加快

For i = 2 To 80
    For j = 2 To 80
        For k = 2 To 80
            For l = 2 To 80
                
                ' If Range("a" & i) + Range("a" & j) + Range("a" & k) + Range("a" & l) = 124704 Then '
            
                If arr(i, 1) + arr(j, 1) + arr(k, 1) + arr(l, 1) = 124704 Then '如果四个相加等于回款信息
                    ' Range("f3") = Range("a" & i)
                    Range("f3") = arr(i, 1) '记录回款信息
                    Range("g3") = arr(j, 1)
                    Range("h3") = arr(k, 1)
                    Range("i3") = arr(l, 1)
                    
                    GoTo 100  '单单只是exit for是不行的，只会退出一层，这里用GoTo跳出所有循环
                    
                End If
            Next
        Next
    Next
Next

100

t = Timer - t
MsgBox Format(t, "0.0000")
 

End Sub

t=  0.0195

四、小结

需要理解并记住写法的概念

Dim Arr() As String
Range(“a1”)=Arr(2,1)
Arr=Range(“a1:b10”)

需要理解的概念

什么是数组 Dim Arr(10，2)
Ubound和Lbound ReDim [Preserve] Arr(10)
使用数组的优势
Timer GOTO 100

回顾以下几个概念
数组有维度 有大小
可以把值赋值给数组 也可以把区域复制给数组
数组可以用在函数里