Excel - вернуть несколько значений для каждого из списка значений

Question

Я пытался решить эту проблему в течение нескольких часов, но безрезультатно ...

Моя настройка:

• У меня есть превосходные листы рецептов косметического продукта.

• Каждый рецепт содержит десятки ингредиентов, каждый из которых имеет свое торговое название.

• У меня длинный список, переводящий каждую торговую марку на ее «научное» название.

Мне нужно перевести каждую торговую марку в ее научный аналог. Эту часть очень легко сделать с помощью vlookup.

Но некоторые торговые названия сами по себе являются рецептами , содержащими несколько ингредиентов в таблице научных названий. Так что это не отношения 1:1, иногда (не всегда) это 1:многие.

Например:

Косметика лист

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

Основной файл списка торговых наименований:

 IngredientA_trade_name     Science1
 IngredientB_trade_name     Science2
 IngredientB_trade_name     Science3
 IngredientB_trade_name     Science4    
 IngredientC_trade_name     Science5
 IngredientC_trade_name     Science6

 ....etc, for lots and lots of ingredients.

Теперь я нашел способы вернуть несколько значений, но ни одно из них не выполняет работу автоматически:

Я должен поместить имя ингредиента в ячейку, затем поместить формулу в ячейку рядом с ней и перетащить ее вниз на несколько мест, чтобы она автоматически заполняла все возможные совпадения. Некоторые другие решения помещают возвращаемые значения в одну ячейку (Science2, Science3, Science4), что немного лучше, но не работает для остальной части моего рабочего процесса ...

Есть ли способ просмотреть список торговых названий и при необходимости вставить строки, содержащие все научные названия?

Answer 1

Для Excel 2010 или 2013 я бы использовал для этого надстройку Power Query. В Excel 2016 Power Query встроен в ленту данных в разделе "Получить и преобразовать".

Power Query может начинаться из таблицы Excel. У него есть команда Merge, которая может объединять запросы вместе с возможностью сохранять только совпадения. Результат запроса может быть записан в таблицу Excel.

Для разработки ваших требований в Power Query потребуется несколько минут, без кода.

...

Я был вдохновлен некоторыми полезными отзывами, чтобы расширить этот ответ. На самом деле я создал рабочее решение, которое вы можете загрузить с моего OneDrive и попробовать:

http://1drv.ms/1AzPAZp

Это файл: Power Query demo - возвращает несколько значений для каждого из списка значений

Сборка заняла у меня менее 2 минут (не считая копирования во входных данных и написания листа Read Me), и не требовала никакого кода / функций.

Ключевой метод - это слияние и расширение, как описано здесь:

https://support.office.com/en-us/article/Merge-queries-Power-Query-fd157620-5470-4c0f-b132-7ca2616d17f9

Answer 2

Этот макрос, использующий VBA, сделает работу:

Public Sub ingredients()
    Dim wkb As Workbook
    Dim wks, wks1 As Worksheet
    Set wkb = ThisWorkbook
    Set wks = ActiveSheet
    wks.Application.ScreenUpdating = False
    mastername = "Master"
    totalsheets = wkb.Worksheets.Count
    For i = 1 To totalsheets
        Set wks1 = wkb.Worksheets(i)
        wks1name = wks1.Name
        If wks1name = mastername Then
            i = totalsheets
        Else
            Set wks1 = Nothing
        End If
    Next i
    reviewing = True
    activerow = 1
    While reviewing
        tradename = wks.Cells(activerow, 1)
        If tradename = "" Then
            reviewing = False
        End If
        reviewingmaster = True
        activerowmaster = 1
        found = 0
        While reviewingmaster
            sciname = wks1.Cells(activerowmaster, 1)
            If sciname = "" Then
                reviewingmaster = False
            End If
            If sciname = tradename Then
                found = found + 1
                If found > 1 Then
                    activerow = activerow + 1
                    wks.Rows(activerow).Insert
                End If
                wks.Cells(activerow, 2) = wks1.Cells(activerowmaster, 2)
                wks.Cells(activerow, 3) = wks1.Cells(activerowmaster, 3)
            End If
            activerowmaster = activerowmaster + 1
        Wend
        activerow = activerow + 1
    Wend
    wks.Application.ScreenUpdating = True
    theend = MsgBox("Finished on " & wks.Name, vbInformation)
End Sub

Вы должны открыть Macros /VBA, вставить модуль в ThisWorkbook и вставить код с правой стороны.

Имейте в виду это:

• Код работает на активном листе.
• Каждое новое научное название, кроме первого для торговой марки, добавляется в новом ряду.
• Код готов к работе до тех пор, пока в столбце А таблиц нет пустых ячеек.
• Поскольку предполагается, что имя рабочей таблицы основного списка - « Мастер», необходимо изменить строку « mastername="Master" на соответствующее имя.
• Установка 500 торговых наименований и 5000 научных названий заняла 23 секунды.