• 單列信息查詢（Vlookup）

源數(shù)據(jù)

Sub vf()
    Dim i&, d As Object, arr
    arr = [a1].CurrentRegion'將數(shù)據(jù)放進(jìn)數(shù)組arr
    Set d = CreateObject("scripting.dictionary")創(chuàng)建字典
    For i = 2 To UBound(arr)'將數(shù)組元素放進(jìn)字典
        d(arr(i, 1)) = arr(i, 2)'左邊皮膚key，右邊金幣item
    Next
    [f1] = d([e1].Value)'輸出數(shù)據(jù)，記住子弟讀取單元格是要寫上.value
End Sub

結(jié)果

• 整列查詢：根據(jù)數(shù)字編號(hào)，查詢整條數(shù)據(jù)。

源數(shù)據(jù)

Sub gf()
    Dim d As Object, i&, arr
    Set d = CreateObject("scripting.dictionary")'創(chuàng)建字典
    arr = [a1].CurrentRegion'數(shù)組賦值
    For i = 1 To UBound(arr)'遍歷數(shù)組
'用array函數(shù)將整列數(shù)據(jù)組成一個(gè)一維數(shù)組，作為item。
        d(arr(i, 1)) = Array(arr(i, 2), arr(i, 3), arr(i, 4), arr(i, 5), arr(i, 6))
    Next
    For i = 1 To 5'輸出數(shù)據(jù)
        Cells(i, "K").Resize(1, 5) = d(Cells(i, "j").Value)
    Next
End Sub

結(jié)果