Excel结合Vlookups [英] Excel combine Vlookups

问题描述

我有两个文件，一个是项目注册表，其中包含项目的关键信息，另一个是风险日志。

I have two files one is a Project Register that holds key information on a project and the other is a Risk log.

有一个1：m的关系注册表中的条目和风险日志。我需要做的是将项目风险整合到项目注册表文件中的一个单元格中。

There is a 1:m relationship between entries in the Register and the Risk log. What I need to do is combine all of a project risks into one cell inside the project register file.

两个文件中的匹配字段是项目ID字段

The matching field in both files is the Project ID field

有没有办法使用vlookup变体或多个嵌套vlookup？

Is there a way I can do this using a vlookup variant or multiple nested vlookups?

推荐答案

这里是我提到的用户定义的函数方法（从我已经做过的另一个VLOOKUP变体中改编）：

Here's the user-defined function approach I mentioned (adapted from a different VLOOKUP-variant I already had made):

' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
' instead of just returning the first match
Public Function VLOOKUP_MANY(lookup_value As String, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vArr As Variant
    Dim i As Long
    Dim found As Boolean: found = False

    ' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    ' Get values
    vArr = lookup_range.Value2

    ' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vArr, 2) Or column_number > UBound(vArr, 2) Then
        VLOOKUP_MANY = CVErr(xlErrRef)
        Exit Function
    End If

    ' Search for matches and build a concatenated list
    VLOOKUP_MANY = ""
    For i = 1 To UBound(vArr, 1)
        If UCase(vArr(i, 1)) = UCase(lookup_value) Then
            VLOOKUP_MANY = VLOOKUP_MANY & delimiter & vArr(i, column_number)
            found = True ' Mark at least 1 result
        End If
    Next

    If found Then
        VLOOKUP_MANY = Right(VLOOKUP_MANY, Len(VLOOKUP_MANY) - Len(delimiter)) ' Remove first delimiter
    Else
        VLOOKUP_MANY = CVErr(xlErrNA) ' If no matches found, return #N/A
    End If
End Function

这将搜索指定范围内的第一列指定的值（与VLOOKUP相同），但返回连接的指定列号中的值。如果没有找到匹配项，则返回＃N / A，如果列号指定了无效值，则返回#REF（例如，您选择列5，但只有4列表）。

This will search the first column in the specified range for the specified value (same as VLOOKUP), but returns the values in the specified column number concatenated. It will return #N/A when no matches are found, and #REF if an invalid value is specified for the column number (e.g. you choose column 5 but only had a 4-column table).

如果您不了解用户定义的函数，您可以将此VBA代码复制到工作簿中的模块的VBE中。按Alt + F11，转到插入>模块在屏幕顶部，然后将该代码粘贴到打开的空白文件中。当您保存时，您必须将工作簿保存为启用宏（.xlsm）以保持代码的正常工作 - Excel将在保存屏幕中提醒您。

In case you don't know about user-defined functions - you can just copy this VBA code into the VBE for a module in your workbook. Hit Alt+F11, go to Insert > Module at the top of the screen, then paste this code into the blank file that opens up. When you go to save, you'll have to save your workbook as Macro-Enabled (.xlsm) to keep the code working - Excel will remind you about this in the save screen.

要预先警告：由于必须查看整个查找范围，而不是能够在找到的第一个匹配项中停止，因此它将比VLOOKUP慢。

Be forewarned: it's going to be slower than VLOOKUP as a result of having to look through the entire lookup range instead of being able to stop at the first match it finds.

如果您开放使用数组公式，则可以通过这种方式加快这种非常大的数据集的功能。

If you're open to using an array formula instead, there are ways to speed up this sort of functionality for very large datasets...

使用数组公式的一些优点来存储查找值并加快后续调用的不同版本：

Different version that leverages some of the benefits of array formulas to store lookup values and speedup subsequent calls:

' Acts like VLOOKUP in a 1-to-many scenario by concatenating all values in matching rows
'  instead of just returning the first match
' Utilizes a dictionary to speedup multiple matches (great for array formulas)
Public Function VLOOKUP_MANY_ARRAY(lookup_values As Range, lookup_range As Range, column_number As Integer, Optional delimiter As Variant) As Variant
    Dim vHaystack As Variant, vNeedles As Variant
    Dim i As Long
    Dim found As Boolean: found = False
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")

    ' Set default delimiter
    If IsMissing(delimiter) Then delimiter = ", "

    ' Get values
    vHaystack = lookup_range
    vNeedles = lookup_values

    ' If column_number is outside of the specified range, return #REF
    If column_number < LBound(vHaystack, 2) Or column_number > UBound(vHaystack, 2) Then
        VLOOKUP_MANY_ARRAY = CVErr(xlErrRef)
        Exit Function
    End If

    ' Add values to a lookup dictionary
    For i = 1 To UBound(vHaystack, 1)
        If dict.Exists(UCase(vHaystack(i, 1))) Then
            dict.Item(UCase(vHaystack(i, 1))) = dict.Item(UCase(vHaystack(i, 1))) & delimiter & vHaystack(i, column_number)
        Else
            dict.Add UCase(vHaystack(i, 1)), vHaystack(i, column_number)
        End If
    Next

    Dim outArr As Variant
    If IsArray(vNeedles) Then ' Check number of lookup cells
        ' Build output array
        ReDim outArr(1 To UBound(vNeedles, 1), 1 To 1) As Variant

        For i = 1 To UBound(vNeedles, 1)
            If dict.Exists(UCase(vNeedles(i, 1))) Then
                outArr(i, 1) = dict.Item(UCase(vNeedles(i, 1)))
            Else
                outArr(i, 1) = CVErr(xlErrNA)
            End If
        Next
    Else
        ' Single output value
        If dict.Exists(UCase(vNeedles)) Then
            outArr = dict.Item(UCase(vNeedles))
        Else
            outArr = CVErr(xlErrNA)
        End If
    End If

    VLOOKUP_MANY_ARRAY = outArr
End Function

这将创建一个字典，这是一个非常适合查找值的特殊结构。构建它有一点额外的开销，但一旦有了结构，你可以很快地查找它。对于数组公式，这是非常好的，基本上当将相同的公式放入整个单元格集合中时，函数将执行一次，并为每个单元格返回值（而不是只执行一次，分别，一堆细胞）。输入它像一个数组公式与CTRL + SHIFT + ENTER，并使第一个参数引用所有您的查找值而不是一个。

This creates a Dictionary, which is a special structure that's really good for looking up values. There's a little extra overhead involved in building it, but once you have the structure, you can do lookups into it very quickly. This is especially nice with array formulas, which is basically when the exact same formula gets put into a whole collection of cells, then the function executes once and returns values for every cell (instead of just executing once, separately, for a bunch of cells). Enter it like an array formula with CTRL+SHIFT+ENTER, and make the first argument refer to all your lookup values instead of just one.

它不会被用作数组公式，但它在这种情况下会比第一个函数慢一些。但是，如果您在数组公式中使用它，您将看到巨大的加速。

It will work without being used as an array formula, but it will be somewhat slower than the first function in that situation. However, if you use it in an array formula, you'll see huge speedups.

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