excel的一些小技巧教程VBA For Excel精进Excel

数据处理VBA篇：数组

2017-04-25  本文已影响1051人  鸣人吃土豆

有些时候我们需要用Excel处理大量的数据，这时候如果还用range引用单元格内容就会显得有点慢，这时候如果采用数组的话呢速度会快一大截，当然速度要在数据量比较大情况下才看的出来。
现在让我们来学习一下数组怎么用吧。

1.数组的声明

通过起始和终止索引号定义数组的大小
public/dim 数组名([下界 to] 上界) as 数据类型
下界不填写，默认为0.如果希望默认从1开始，则在用户窗体或模块的声明部分且在声明数组之前输入以下语句：
option base 1

public/dim 数组名(第一维上界，第二维上界) as 数据类型   #未指定起始索引号，默认起始索引号为0
或
public/dim 数组名(第一维下界 to 第一维上界，第二维下界 to 第二维上界) as 数据类型

第一步：在用户窗体或模块或过程中用dim或public声明一个没有下标的数组（不能省略括号）即：dim/public arr() as  数据类型
注：VBA中不允许在public或dim语句中使用变量来指定数组的大小，但是在redim中却可以用变量来指定数组的大小
第二步：在过程中用redim重定义数组的大小
redim [preserve] 数组名（下标）[as 数据类型]
redim 不能在将一个数组定义为某种数据类型之后改为其他数据类型
Sub 生成幸运号码()
    Dim l()
    Dim n As Integer, x As Integer, y As Integer
    n = InputBox("输入要生成的幸运号码的个数", "幸运号码")
    ReDim l(n, 7)
    For x = 1 To n
        For y = 1 To 7
            Randomize
            l(x, y) = Int(10 * Rnd)
        Next
    Next
    For x = 1 To n
        For y = 1 To 7
            Debug.Print l(x, y);
        Next
     Debug.Print
    Next
End Sub

2.更加简单的数组创建

sub test()
     dim arr as variant   #使用array函数创建数组时，该数组应声明为一个variant类型的变量
     arr = array(1,2,3,4,5,6,7,8,9,10)
     msgbox "arr数组的第2个元素是：" & arr(1)  #使用array函数创建的数组索引号默认从0开始，除非使用option base 1.
end sub

将一个字符串按指定的分隔符拆分，将各部分结果保存到一个一维数组中
Sub test()
     Dim a As String, arr As Variant      '使用split函数创建数组，该数组必须声明为variant类型的变量
     a = "小米，华为，OPPO，苹果，三星"
     arr = Split(a, "，")       '按照"，"将字符串分割
     MsgBox "arr数组的第3个元素为：" & arr(2)     '使用split函数创建的数组索引号默认从0开始，无论是否使用option base 1
End Sub

sub test()
     dim arr as variant
     arr = range("a1:c3").value
     range("e1:g3").value = arr
end sub

3. 数组的维数：

总结：二维是由行和列表示的数组，如ARR(3,2)表示数组中第3排第2列的元素。而一维数组只是由一个元素决定，如ARR（4）表示数组中第4个元素

4. 动态数组的声明例子：

Sub darr()
  Dim arr()  '声明一个动态的arr数组(不知道它能盛多少数据)
  Dim k
  k = Application.WorksheetFunction.CountIf(Range("a2:a6"), ">10") '计算大于10的个数
  ReDim arr(1 To k)  '再次声明arr的大小,正好盛下k数量的值
  For x = 2 To 6
    If Cells(x, 1) > 10 Then
      m = m + 1
      arr(m) = Cells(x, 1)  '通过循环把大于10的数字装入数组
    End If
  Next x
  MsgBox arr(2)
End Sub

5. 数组的合并和字符串拆分（Join & Split）：

**A-REW-E-RWC-2-RWC 按分隔符-拆分成6个字符放在一个数组中 **

有一组数array(23,45,7,1,76)想用分隔符-连接成一个字符串

上面两种情况VBA提供了一对函数，即：

split（字符串,"分隔符"） 拆分字符串
join(数组,"分隔符")   用分隔连接数组的每个元成一个字符串   
Sub t1()
      Dim arr, myst As String
      myst = "A-REW-E-RWC-2-RWC"
      arr = Split(myst, "-")  '按-分隔成一组数装入数组中
      MsgBox arr(0) '显示数组的第一个数(分隔后的数组最小下标为0,不是1),显示结果为A
      MsgBox Join(arr, ",") '再用","把数组的每个值连接成一个字符串,结果为"A,REW,E,RWC,2,RWC"
End Sub

值得注意的是：split和join只能对一维数组进行操作，如果是单元格或二维数组怎么办？只有一条途径，想办法转换为一维数组：

Sub t2()
     Dim ARR
     ARR = Application.Transpose(Range("a1:a3")) ‘用转置的方法，把单元格一列数据转换成一维数组
     MsgBox Join(ARR, "-")
End Sub

7. Filter函数实现数组筛选:

数组的筛选就是根据一定的条件,从数组中筛选符合条件的值,组成一个新的数组，实现数组筛选的VBA函数是：

Filter函数

  用法：Filter(数组, 筛选的字符,  是否包含) 

Sub DD()
     arr = Array("ABC", "A", "D", "CA", "ER")
     arr1 = VBA.Filter(arr, "A", True) '筛选所有含A的数值组成一个新数组
     arr2 = VBA.Filter(arr, "A", False) '筛选所有不含A的数值组成一个新数组
     MsgBox Join(arr2, ",") '查看筛选的结果
End Sub

注意：遗憾的是函数只能进行模糊筛选，不能精确匹配。

8. VBA数组各种Excel函数应用

他山之石，可以攻玉，VBA中除可以利用的VBA函数外，还可以调用众多的Excel工作表函数对数组进行分解、查询和分析等，调用工作表函数可以省去循环判断的麻烦，进而提高运行效率。

1、Max和Min
工作表函数Max和Min是求最大值和最小值的函数，同样在VBA中也可以求数组的最大值和最小值。如：
Sub t()
arr = Array(1, 35, 4, 13)

MsgBox Application.Max(arr)  '最大值

MsgBox Application.Min(arr)   '最小值

End Sub

2、large和small
工作表函数large和small 是返回一组数的第N大和第N小，对VBA数组同样适用，如：
Sub t1()
     arr = Array(1, 35, 4, 13)
     MsgBox Application.Large(arr, 2) '第2大值
     MsgBox Application.Small(arr, 2)  '第2小值
End Sub

1、Sum
Sum函数可以在工作表中求，同样也可以对VBA数组求和，如：
Sub t2()
         arr = Array(1, 35, 4, 13)
         MsgBox Application.Sum(arr)  '对数组进行求和
End Sub

2、Count和Counta

                    Count和Counta可以统计数组中数字的个数和数字+文本的个数。

                   Sub t3()
                        arr = Array(1, 35, "a", 4, 13, "b")
                        MsgBox Application.Count(arr)  '返回数字的个数4
                        MsgBox Application.CountA(arr)  ‘返回数组文本和数字的总个数
                   End Sub

1、Mach查询数组

Match函数可以查询一个指定值在一组数中的位置，它也可以用于VBA数组的查询。如：

                 Sub t4()
                        arr = Array(1, 35, 4, 13)
                        MsgBox Application.Match(4, arr, 0)  '查询数值4在数组Arr中的位置
                End Sub

2、Index拆分数组
数组的拆分在VBA中是一个难题，如果是按行拆分数组，除了用循环外也只能借用API函数完成了。幸好我们可以借用工作表函数index达到按列拆分数组，即多列构成的数组，你可以任意拆分出一列构成新的数组。方法是：Application.Index(数组, , 列数) ，例：

Sub t2()
          arr2 = Range("A1:B4") ‘把单元格区域A1：B4的值装入数组arr2
          arr3 = Application.Index(arr2, , 2)  '把数组第2列拆分出来装入新数组arr3中，新数组为二维数组
           MsgBox arr3(2, 1)  '取出新数组第2行的值
 End Sub

四、数组维数的转换
Transpose转置数组在工作表中可以把行列转换。在VBA中同样也可以做到转换的效果。

1、一维转二维。

Sub t9()
           arr = Array(1, 35, "a", 4, 13, "b")
           arr1 = Application.Transpose(arr)
           MsgBox arr1(2, 1) ‘转换后的数组是1列多行的二维数组
End Sub

2、二维数组转一维。

                  Sub t2()
                       arr2 = Range("A1:B4")
                       arr3 = Application.transpose(Application.Index(arr2, , 2)) '取得arr2第2列数据并转置成1维数组
                      MsgBox arr3(2,)
                  End Sub

五、数组索引号
用ubound函数求出数组的最大索引号
用lbound函数求出数组的最小索引号

sub test()
     dim srr(1 to 10,1 to 100) as interger
     dim a as interger,b as interger
     a = ubound(arr,1)  '行的最大索引号
     b = ubound(arr,2)   '列的最大索引号
end sub

求数组包含的元素个数

ubound(数组)-lbound(数组)+1

注：在转置时只有1列N行的数组才能直接转置成一维数组
思考题：我要把a1:c1中的内容用“-”连接起来，下面代码中为什么用了两次transpose

Sub t10()
     arr = Range("A1:C1")  '二维数组
     MsgBox Join(Application.Transpose(Application.Transpose(arr)), "-")
End Sub
上一篇下一篇

猜你喜欢

热点阅读