3.3,转置 取工作表区域的转置到数组:arr=Application.Transpose([a1:c5]) ‘此时arr是转置成3行5列的数组,arr(1 to 3,1 to 5) [e1:i3]=arr ‘此时3行5列。 数组间也可以转置:arr1=Application.Transpose(arr) 取数组arr的第n列赋值到某列区域:[e1:e5]=Application.Index(arr, 0, n) 也可写成 [e1:e5]=Application.Index(arr, , n) 赋值产生一个新数组:arr1=Application.Index(arr,0 , n) 取数组arr的第n行赋值到某行区域:[a6:c6]=Application.Index(arr,n ,0 ) 也可写成 [a6:c6]=Application.Index(arr,n ) 省略0,也省略了“,“ 赋值产生一个新数组:arr1=Application.Index(arr, n ) 3.4,数组的比较(字典法) 题目:将A列中的数据与C列相比较,输出C列中没有的数据到D列: Sub cc() ‘by:ccwan Dim arr, brr, i&, x&, d As Object arr = Range("a1:a" & [a65536].End(xlUp).Row) brr = Range("c1:c" & [c65536].End(xlUp).Row) Set d = CreateObject("scripting.dictionary") For i = 1 To UBound(arr) d(arr(i, 1)) = "" Next For x = 1 To UBound(brr) If d.exists(brr(x, 1)) Then d.Remove brr(x, 1) End If Next [d1].Resize(d.Count, 1) = Application.Transpose(d.keys) End Sub 3.5,数组的排序 字符串数组不能用Large(Arr,i) 或者 Small(Arr,i) 来排序; 但数值数组可以; 一个很好的字典+数组排序的实例: Sub yy1() ‘by:oobird Dim i%, c As Range, x, d As Object Set d = CreateObject("Scripting.Dictionary") For Each c In Sheet2.UsedRange If c.Value <> "" Then If Not d.exists(c.Value) Then d.Add c.Value, 1 Else d(c.Value) = d(c.Value) + 1 End If End If Next k = d.keys: t = d.items 'k是各个不重复值,t是各个不重复值的个数 ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(2, i) = Application.Large(k, i) ‘从大到小排序 x(1, i) = d(x(2, i)) Next i With Sheet1 .[b2].Resize(2, i - 1) = x ReDim x(1 To 2, 1 To d.Count) For i = 1 To d.Count x(1, i) = Application.Max(t) ‘从大到小排序 w = Application.Match(x(1, i), t, 0) – 1 ‘查找此值在不重复值系列中的排位,因为w是从0开始的,所以-1 x(2, i) = k(w) ‘求得对应的不重复值 t(w) = "" ‘使前面的最大值为空,继续循环 Next i .[b5].Resize(2, i - 1) = x ‘两行一起赋值给B5开始的单元格 End With End Sub 字符串数组的排序,可以使用辅助列,把数组各元素依次赋给单元格,然后对这些单元格运用Excel自有的数据排序功能进行排序,再把单元格排过序的值重新赋给数组。 3.6,数组赋给单元格区域 r=Ubound(Arr) r为一维数组的上限; Range("a2").Resize(1, r) = Arr '填充到工作表的一行之中(Arr为一维数组) 或者写成 Range("a2").Resize(1, Ubound(Arr)) = Arr
二维数组Arr(100,5) Range(“a1”).Resize(100,5)=Arr [a1:e100]=Arr 或者写成 Range("a1").Resize(Ubound(Arr,1), Ubound(Arr,2)) = Arr
|