VBA index(AryOrg, AryXrow, AryYcol,AryFilter)的理解

20221201 更新

最佳实践为,  rst = application.index(org, 2D_Y, array) 返回2维数组

研究方法

Sub Button1_Click()
arr = Range("A1:E5")

X = 2: Y = 5
ary = Array(3, 4, 5)
D = Range("G6:H7")
D_X = Range("G6:H6")
D_Y = Range("G6:G7")

nY = Application.Index(arr, 0, Y)
nary = Application.Index(arr, 0, ary)
nD_X = Application.Index(arr, 0, D_X)
nD_Y = Application.Index(arr, 0, D_Y)
nD = Application.Index(arr, 0, D)

Xn = Application.Index(arr, X, 0)
XY = Application.Index(arr, X, Y)
Xary = Application.Index(arr, X, ary)
XD_X = Application.Index(arr, X, D_X)
XD_Y = Application.Index(arr, X, D_Y)
XD = Application.Index(arr, X, D)

aryn = Application.Index(arr, ary, 0)
aryY = Application.Index(arr, ary, Y)
aryary = Application.Index(arr, ary, ary)
aryD_X = Application.Index(arr, ary, D_X)
aryD_Y = Application.Index(arr, ary, D_Y)
aryD = Application.Index(arr, ary, D)

D_Xn = Application.Index(arr, D_X, 0)
D_XY = Application.Index(arr, D_X, Y)
D_Xary = Application.Index(arr, D_X, ary)
D_XD_X = Application.Index(arr, D_X, D_X)
D_XD_Y = Application.Index(arr, D_X, D_Y)
D_XD = Application.Index(arr, D_X, D)

Dn = Application.Index(arr, D, 0)
DY = Application.Index(arr, D, Y)
Dary = Application.Index(arr, D, ary)
DD_X = Application.Index(arr, D, D_X)
DD_Y = Application.Index(arr, D, D_Y)
DD = Application.Index(arr, D, D)
D_Yn = Application.Index(arr, D_Y, 0)
D_YY = Application.Index(arr, D_Y, Y)
D_Yary = Application.Index(arr, D_Y, ary)
D_YD_X = Application.Index(arr, D_Y, D_X)
D_YD_Y = Application.Index(arr, D_Y, D_Y)
D_YD = Application.Index(arr, D_Y, D)
End Sub

 结果

 

以下为历史版本: 

Row数组应为 二维1列; Column数组应为 二维1行, 才可得到所需要的数组区域, 返回为二维数组.

 以下 单个格子代表 数字 或 一维数组的元素,

横向连接在一起的单元格代表 二维一行数组;

竖向连接在一起的单元格代表 二维单列数组;

第4个数组,AryFilter相当于对返回的数组再次筛选,如果返回m*n的二维数组,则最后一个数组也应该是m*n的二维数组,需要的坐标处就填1,不需要就填0.

如下图r2xy, (1,1)和(2,2)处为1,则返回的数组就只保留了(1,1)的21和(2,2)的43.