LookUP Value возвращает несколько объединенных значений

Question

У меня есть два столбца: один - «Категория» с простым кодом, а другой - «Свойства дерева / группы» с одним или несколькими из следующих категорий, которые, если их несколько, объединяются с помощью запятой, см. Пример

Мне нужен этот столбец L

заполнить значение, состоящее из кода категории (A, B, C или U) и числа / нескольких чисел (от 1 до 3), которые зависят от этого

Так, например, на первом изображении первая строка с данными будет переведена в мой столбец L ('CAT') как "B, 2,3,1"

Я пытался использовать = VLOOKUP, но могу получить только первый матч. Я думаю, что создание и Array могут быть решением, но я думаю, что мне нужно несколько советов по этому вопросу.

Answer 1

Я нашел обходной путь. не очень эффективно или умно, но это работает.

По сути, я создал массив со всеми возможными комбинациями трех элементов и добавил еще один столбец с правильным кодом, который должен был быть получен.

Используя формулу ниже, теперь вы можете подготовить Excel для каждого отдельного значения и найти его код в столбце на основе числа

= IF(деревья!AC2 <> "", ВПР (деревья!AC2, QUALITIES_ARRAY, 2, FALSE), "")

Надеюсь, поможет

Answer 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