使用VBA数组公式 |
您所在的位置:网站首页 › excel常用数组公式 › 使用VBA数组公式 |
一、 认识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 Subt = 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 Subt = 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和LboundUbound: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 Subt = 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 Subt= 0.0195
四、小结 需要理解并记住写法的概念 Dim Arr() As StringRange(“a1”)=Arr(2,1)Arr=Range(“a1:b10”)
需要理解的概念 什么是数组 Dim Arr(10,2) Ubound和Lbound ReDim [Preserve] Arr(10) 使用数组的优势Timer GOTO 100 回顾以下几个概念数组有维度 有大小可以把值赋值给数组 也可以把区域复制给数组数组可以用在函数里
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |