Excel-如何在单个单元格中的分隔列表中VLOOKUP项目 [英] Excel - how to VLOOKUP items in a delimited list in single cell

问题描述

我有一个包含不同工作表的Excel文档.在一张纸上，我有一列:

I have an Excel document which consist of different sheets. In one sheet I have column:

| text column        |
|#test2,#test3,#test1|
|#test2, #test1      |
|#test1              |

在另一张纸上，我有一个值的映射:

In another sheet I have a mapping for the values:

|some column| value  |
|#test2     | Value2 |
|#test1     | Value1 |
|#test3     | Value3 |

所以我需要得到结果:

| text column           |
| Value2, Value3, Value1|
| Value2, Value1        |
| Value1                |

是否可以通过Excel Worksheet函数获得此功能?

Is this possible to get this by Excel Worksheet functions?

我试图爆炸数据，但是具体单元格中的值可以不确定.也尝试使用VLOOKUP，但是我不知道如何使用它，因为我需要在VLOOKUP之前拆分值.

I tried to explode the data but I can have undefined quantity of values in the concrete cell. Also tried to use VLOOKUP but I don't have an idea how to use this because I need to split the values before VLOOKUP.

你能帮我吗?谢谢.

推荐答案

因为您需要某种形式的迭代，即拆分一个值，对每个项目执行一个操作并重新加入这些项目-不太可能有一种方法这与Excel功能.如果您愿意使用VBA，则用户定义的功能非常简单:

Because you need some form of iteration, i.e. split a value, perform an operation on each item and rejoin the items - it is unlikely there is a way to do this with Excel functions. If you are happy to use VBA then the user-defined function is quite straight-forward:

Option Explicit

Public Function RemapValues(rngSource As Range, rngLookup As Range, strDelimiter As String) As String

    Dim arrIn() As String
    Dim lngCounter As Long
    Dim arrOut() As String

    ' split input string by supplied delimiter
    arrIn = Split(rngSource.Value, strDelimiter, -1)

    ' set capacity of array storing re-mapped values
    ReDim arrOut(LBound(arrIn) To UBound(arrIn))

    ' loop over input array
    For lngCounter = LBound(arrIn) To UBound(arrIn)
        ' do the vlookup operation
        arrOut(lngCounter) = WorksheetFunction.VLookup(arrIn(lngCounter), rngLookup, 2, 0)
    Next lngCounter

    ' return the re-mapped values
    RemapValues = Join(arrOut, strDelimiter)

End Function

例如

这篇关于Excel-如何在单个单元格中的分隔列表中VLOOKUP项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！