☰

Excel - retorna vários valores para cada uma de uma lista de valores

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel - retorna vários valores para cada uma de uma lista de valores

Estou tentando resolver isso há algumas horas, mas sem resultado...

Minha configuração:

  • Eu tenho planilhas do Excel dereceitas de produtos cosméticos.

  • Cada receita contém dezenas de ingredientes, cada um dado por seunome comercial.

  • Eu tenho uma longa lista traduzindo cada nome comercial para seu'nome científico.

Preciso traduzir cada nome comercial para sua contraparte científica. Esta parte é muito fácil de fazer com um vlookup.

Mas algunsnomes comerciaissãoreceitasem si, contendo vários ingredientes na tabela denomes científicos. Portanto, não é um relacionamento 1:1, às vezes (nem sempre) é 1:muitos.

Por exemplo:

Folha cosmética A

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

Arquivo mestre da lista de nomes comerciais para científicos:

 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.

Agora, encontrei maneiras de retornar vários valores, mas nenhuma delas funciona automaticamente:

devo colocar oIngredientenome em uma célula, coloque a fórmula na célula próxima a ela e arraste-a alguns lugares para baixo para que seja preenchida automaticamente para todas as correspondências possíveis. Algumas outras soluções colocam os valores retornados em uma única célula (Science2,Science3,Science4), o que é um pouco melhor, mas não funciona no resto do meu fluxo de trabalho...

Existe uma maneira de percorrer a lista de nomes comerciais e inserir linhas conforme necessário contendo todos os nomes científicos?

Responder1

Esta macro usando VBA fará o trabalho:

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

Você tem que abrir Macros /VBA, inserir um módulo emEsta pasta de trabalhoe cole o código no lado direito.

Tenha em mente o seguinte:

  • O código é executado na planilha ativa.
  • Cada novo nome científico, exceto o primeiro de um nome comercial, é adicionado em uma nova linha.
  • O código está preparado para funcionar desde que não haja células em branco na coluna A das planilhas.
  • Como supõe que o nome da planilha da lista mestra sejaMestrevocê deve alterar a linha mastername="Master"para o nome apropriado.
  • Definir 500 nomes comerciais e 5.000 nomes científicos levou 23 segundos.

Responder2

Para Excel 2010 ou 2013, eu usaria o suplemento Power Query para isso. No Excel 2016, o Power Query é integrado à faixa de dados na seção "Obter e transformar".

O Power Query pode começar a partir de uma tabela do Excel. Possui um comando Mesclar que pode unir Consultas, com opção de manter apenas as correspondências. Um resultado de consulta pode ser gravado em uma tabela do Excel.

Seus requisitos levariam alguns minutos para serem projetados no Power Query, sem a necessidade de código.

...

Fui inspirado por alguns comentários úteis para expandir esta resposta. Na verdade, criei uma solução funcional que você pode baixar do meu OneDrive e experimentar:

http://1drv.ms/1AzPAZp

É o arquivo:Demonstração do Power Query - retorne vários valores para cada uma de uma lista de valores

Levei menos de 2 minutos para construir (sem contar a cópia dos dados de entrada e a escrita da planilha Leia-me) e não exigi nenhum código/função.

A técnica principal é Mesclar e Expandir, conforme descrito aqui:

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

informação relacionada