Сложная функция Vlookup/VBA или макрос, необходимый для, если содержит текст, затем vlookup

У меня проблема, http://goo.gl/i82eA это пример данных, которые у меня есть с требуемым результатом . В настоящее время у меня есть определяемая пользователем функция, которая вручную использует множество операторов if для выполнения задания, но я хочу иметь возможность делать что-то вроде vlookup, если она находит определенный цвет в столбце и возвращает соответствующую ему цветовую карту.

Или используйте функцию фильтра, такую ​​как фильтрация всех ячеек, содержащих синий цвет, и укажите ячейку назначения с синим цветом, а затем запустите следующий фильтр со следующим значением в таблице цветов.


Color   ColorMap     Text             Required Output
blue    blue         Deep Blue Shoe   Blue (if Text contains blue return blue)
red     red          Deep red Shoe    red (if Text contains red return red) etc
tan     brown        Tan Shoe         brown
navy    blue         Navy Emp Shoe    blue
jade    green        Jade Shoe        green
plum    red          Plum Red Shoe    multicoloured (if Text contains more than 1 color return multicolored)

Таким образом, ввод должен быть похож на 2 столбца для поиска данных, 1 столбец для поиска, а 1 столбец - это столбец назначения, если это функция

function_name(lookup_text,lookup_table,destination)

заранее спасибо

ps: вот код, который я сейчас использую

Код:

Function Colormap(strVal As String) As String

If (InStr(strVal, "red") > 0) Then
    Colormap = "Red"
End If

If (InStr(strVal, "Beige") > 0) Then
    Colormap = "Beige"
End If

etc..

End Function

excel vlookup vba
person Alfred    schedule 13.12.2011    source источник

Ответы (1)


arrow_upward
3
arrow_downward

Это ищет значение Text и возвращает значение ColorMap; если найдено более одного совпадения, возвращается «разноцветный». Примечание. Это формула массива — введите ее с помощью Ctrl+Shift+Enter.

=IF(SUM(IF(ISNUMBER(SEARCH(A$2:A$7,C2)),1,0))>1,"multicolored",LOOKUP(2^15,SEARCH(A$2:A$7,C2),B$2:B$7))

Вот функция, которая делает то же самое. Требуется, чтобы справочная таблица была именованным диапазоном. Вызовите функцию следующим образом: =ColorMap(C2,"ColorTable"), где ColorTable — это именованный диапазон, относящийся к $A$2:$B$7.

Public Function ColorMap(LookupValue As Variant, LookupTableName As String) As String
    Dim vTable As Variant
    Dim lIdx As Long
    Dim sColor As String

    ' transfer lookup table to 2D range & loop through to find matches
    vTable = Names(LookupTableName).RefersToRange.Value
    For lIdx = LBound(vTable, 1) To UBound(vTable, 1)
        If UCase$(LookupValue) Like "*" & UCase$(vTable(lIdx, 1)) & "*" Then
            sColor = sColor & ", " & vTable(lIdx, 2)
        End If
    Next lIdx
    If Len(sColor) > 2 Then sColor = Mid$(sColor, 3)
    ' map multiple matches to "multicolored"
    If InStr(sColor, ",") > 0 Then sColor = "multicolored"
    ColorMap = sColor
End Function
person Rachel Hettinger    schedule 13.12.2011
comment
Ух ты! Спасибо!! это работает потрясающе, не могли бы вы разбить формулу для меня? есть ли способ превратить это в функцию? как я могу получить эквивалент vba для этого, если это возможно? ЕЩЕ РАЗ СПАСИБО!!!!!!!!!!!!!!!!! - person Alfred; 13.12.2011
comment
Обновленная версия обрабатывает многоцветное возвращаемое значение. Вам все еще нужна функция? Если это так, это, вероятно, более прямолинейно, но, конечно, требует VBA и включения макросов. - person Rachel Hettinger; 13.12.2011
comment
Привет, Рэйчел, хм, я пробовал формулу... но она не дает многоцветия для Plum Red Shoes - дает только красный цвет. Спасибо! и да, функция была бы проще, и включение макросов не проблема! - person Alfred; 13.12.2011
comment
Забыл отметить, что формула представляет собой формулу массива — введите ее с помощью Ctrl+Shift+Enter, а не просто Enter. - person Rachel Hettinger; 13.12.2011
comment
Рэйчел УР ГЕНИЙ!!! :) ‹3 спасибо большое!!!! Я должен нанять вас, нам нужны такие люди, как вы, чтобы помочь с автоматизацией !! - person Alfred; 13.12.2011
comment
Привет, Рэйчел, я пытался создать именованный диапазон, но получаю #ЗНАЧ! как выход? Я делаю что-то неправильно? - person Alfred; 13.12.2011
comment
Кроме того, если я перетащу формулу вниз для других ячеек, она даст мне либо 0, либо многоцветие. Я делаю что-то не так? - person Alfred; 13.12.2011
comment
Если вы используете первый вариант (чистая функция рабочего листа), убедитесь, что диапазон вашей таблицы зафиксирован с помощью $ перед индексами строк. Например: A$2:A$7 вместо A2:A7. - person Rachel Hettinger; 13.12.2011
comment
Возможно, эта ссылка поможет объяснить, как создавать именованные диапазоны. - person Rachel Hettinger; 14.12.2011
comment
У меня работает именованный диапазон, но когда я пробую его как функцию, он не работает, вот еще один сценарий. Если один из цветов в столбце B появится в столбце A, например, Бронзовый коричневый Бронзовые туфли Коричневый Коричневый коричневый Коричневые туфли многоцветный, то результат по какой-то причине будет разноцветным. вот ссылка на вывод goo.gl/i82eA посмотрите на красную строку. Спасибо за вашу помощь - person Alfred; 14.12.2011