☰

查找值返回多个连接的值

tag-icon • tag-icon microsoft-excel array worksheet-function
查找值返回多个连接的值

我有两列，一列是带有简单代码的“类别”，另一列名为“树/组质量”，其中包含一个或多个以下类别（如果有多个，则使用逗号连接），请参见示例

在此处输入图片描述

我需要这个 L 列

在此处输入图片描述

填写由类别代码（A、B、C 或 U）和依赖于此的一个数字/多个数字（1 到 3）组成的值

在此处输入图片描述

因此，例如在第一张图片中，第一行包含数据的内容将被转换为我的 L 列（“CAT”），即“B,2,3,1”

我尝试使用 =VLOOKUP，但只能得到第一个匹配项。我认为创建数组可能是解决方案，但我认为我需要一些建议。

答案1

我找到了一种解决方法，虽然不是很有效或很聪明，但确实有效。

基本上，我创建了一个包含这三个元素的所有可能组合的数组，并添加了另一列，其中包含应该检索的正确代码。 在此处输入图片描述

通过使用下面的公式，您现在可以使 Excel 准备好每个值，并根据匹配在列中找到其代码

=IF(树木！AC2<>“”,VLOOKUP(树木！AC2，QUALITIES_ARRAY，2，FALSE)，“”)

希望能帮助到你

答案2

我有一个可行的解决方案。按Alt+F11进入 Visual Basic 编辑器，创建一个新模块并将此代码粘贴到其中。您将能够CAT在任何单元格中使用该函数，如下所示：=CAT(category,qualities)。

' Function that expects a quality and returns the respective code
' Important: Tick check box beside "Microsoft Scripting Runtime" in Tools > References
Function lookup_code(ByRef quality)
    Dim dict As New Scripting.Dictionary
    dict.Add "Arboricultural", "1"
    dict.Add "Landscape", "2"
    dict.Add "Cultural_and_Conservation", "3"
    lookup_code = dict(quality)
End Function

' Function to output the concatenated CAT code
' Use in any Excel cell as `=CAT(category, qualities)`
' Expects a category (e.g. "A") and a string of one or more qualities, separated by a comma
Function CAT(category, qualities)
    Dim code As String
    If InStr(qualities, ",") > 0 Then ' Check for commas
        Dim QualityArray() As String
        QualityArray = Split(qualities, ",") ' Split string at commas
        For Each q In QualityArray
            code = code & "," & lookup_code(q) ' match code to quality
        Next
    Else
        code = "," & lookup_code(CStr(qualities))
    End If
    CAT = category & code ' return category and codes (e.g. "B,2,3,1")
End Function

相关内容