☰

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

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel — возвращает несколько значений для каждого из списка значений

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

Моя установка:

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

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

  • У меня есть длинный список переводов каждого торгового названия на его'научное название.

Мне нужно перевести каждое торговое название в его научный аналог. Эту часть очень легко сделать с помощью 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), что немного лучше, но не работает для остальной части моего рабочего процесса...

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

решение1

Этот макрос, использующий 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, вставить модуль подЭта рабочая книгаи вставьте код справа.

Имейте в виду следующее:

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

решение2

Для Excel 2010 или 2013 я бы использовал надстройку Power Query. Начиная с Excel 2016, Power Query встроен в ленту «Данные» в разделе «Получить и преобразовать».

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

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

...

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

http://1drv.ms/1AzPAZp

Это файл:Демонстрация Power Query — возврат нескольких значений для каждого из списка значений

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

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

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

Связанный контент