VLookup多列 [英] VLookup multiple columns

问题描述

我正在使用VLookup函数，它查找列中存在的多个值。这个工作非常好，但是只需要很多时间，因为我在Excel表中有10万行。

有没有办法加快这段代码？

代码基本上在列中查找一个特定的值并获得偏移。简单的VLookup和这之间的区别是，如果有多个行具有相同的查找值，那么它将获取所有元素。

 函数VLookupAll（ByVal lookup_value As String，_ 
 ByVal lookup_column As Range，_ 
 ByVal return_value_column As Long，_ 
可选的分隔符As String =，）As String 
 
 Dim i As Long 
 Dim result As String 
 
 For i = 1 To lookup_column.Rows.Count 
如果Len（lookup_column（i，1）.Text）< ;> 0然后
如果lookup_column（i，1）.Text = lookup_value然后
 result = result& （lookup_column（i）.Offset（0，return_value_column）.Text& seperator）
 End If 
 End If 
 Next 
 
如果Len（result） > 0然后
 result = Left（result，Len（result） -  Len（seperator））
 End If 
 
 VLookupAll = result 
 
 End Function 
  

解决方案

这比简单循环快20-30倍测试了20k个值的列，与正在搜索的值进行3次匹配）。

 函数MultiLookup（rng As Range，val As String，col As Long）
 
 Dim i As Long，v，s 
 Dim r As Long 
 
r = rng.Cells.Count 
v = Application.Match（val，rng，0）
s =
 Do While Not IsError（v）
s = s& IIf（s，，，）& rng.Cells（v）.Offset（0，col  -  1）.Value 
r = r  -  v 
设置rng = rng.Offset（v，0）.Resize（r，1）
v = Application.Match（val，rng，0）
循环
 MultiLookup = s 
 
结束函数
  

I am using VLookup function which looks up multiple values which are present in the column. This works very well but just takes a lot of time as I have 100,000 rows in the Excel sheet.

Is there any way to quicken this code?

The code basically looks up a particular value in a column and gets the offset. The difference between simple VLookup and this is that in case there are multiple rows with the same lookup value then it gets all the elements.

   Function VLookupAll(ByVal lookup_value As String, _
                ByVal lookup_column As Range, _
                ByVal return_value_column As Long, _
                Optional seperator As String = ", ") As String

 Dim i As Long
 Dim result As String

For i = 1 To lookup_column.Rows.Count
If Len(lookup_column(i, 1).Text) <> 0 Then
    If lookup_column(i, 1).Text = lookup_value Then
        result = result & (lookup_column(i).Offset(0, return_value_column).Text & seperator)
    End If
End If
Next

If Len(result) <> 0 Then
result = Left(result, Len(result) - Len(seperator))
End If

VLookupAll = result

End Function

解决方案

This is about 20-30x faster than a simple loop (tested over a column of 20k values, with 3 matches to the value being searched).

Function MultiLookup(rng As Range, val As String, col As Long)

    Dim i As Long, v, s
    Dim r As Long

    r = rng.Cells.Count
    v = Application.Match(val, rng, 0)
    s = ""
    Do While Not IsError(v)
        s = s & IIf(s <> "", ",", "") & rng.Cells(v).Offset(0, col - 1).Value
        r = r - v
        Set rng = rng.Offset(v, 0).Resize(r, 1)
        v = Application.Match(val, rng, 0)
    Loop
    MultiLookup = s

End Function

这篇关于VLookup多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！