☰

Excel: devuelve varios valores para cada uno de una lista de valores

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel: devuelve varios valores para cada uno de una lista de valores

Llevo unas cuantas horas intentando solucionar esto, pero sin resultado...

Mi configuración:

  • tengo hojas de excel derecetas de productos cosméticos.

  • Cada receta contiene decenas de ingredientes, cada uno dado por sunombre comercial.

  • Tengo una larga lista traduciendo cada nombre comercial a su'nombre científico.

Necesito traducir cada nombre comercial a su contraparte científica. Esta parte es muy fácil de hacer con una búsqueda virtual.

Pero algunosnombres comercialessonrecetasen sí mismos, que contienen múltiples ingredientes en la tabla denombres científicos. Así que no es una relación 1:1, a veces (no siempre) es 1:muchos.

Por ejemplo:

Cosmético una hoja

 IngredientA_trade_name

 IngredientB_trade_name

 IngredientC_trade_name

Cambie al archivo maestro de la lista de nombres 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.

Ahora, he encontrado formas de devolver múltiples valores, pero ninguno de ellos hace el trabajo automáticamente:

debo poner elIngredientenombre en una celda, luego coloque la fórmula en la celda al lado y arrástrela hacia abajo algunos lugares para que se complete automáticamente para todas las coincidencias posibles. Algunas otras soluciones colocan los valores devueltos en una sola celda (Ciencia2, Ciencia3, Ciencia4), lo cual es un poco mejor pero no funciona para el resto de mi flujo de trabajo...

¿Hay alguna manera de revisar la lista de nombres comerciales e insertar filas según sea necesario que contengan todos los nombres científicos?

Respuesta1

Esta macro que usa VBA hará el trabajo:

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

Tienes que abrir Macros /VBA, insertar un módulo debajoEste libro de trabajoy pega el código en el lado derecho.

Tenga en cuenta estos:

  • El código se ejecuta en la hoja de trabajo activa.
  • Cada nuevo nombre científico, además del primero de un nombre comercial, se agrega en una nueva fila.
  • El código está preparado para funcionar siempre que no haya celdas en blanco en la columna A de las hojas de trabajo.
  • Como se supone que el nombre de la hoja de trabajo de la lista maestra esMaestrotienes que cambiar la línea mastername="Master"al nombre apropiado.
  • Configurar 500 nombres comerciales y 5000 nombres científicos tomó 23 segundos.

Respuesta2

Para Excel 2010 o 2013, usaría el complemento Power Query para esto. Desde Excel 2016, Power Query está integrado en la cinta Datos en la sección "Obtener y transformar".

Power Query puede comenzar desde una tabla de Excel. Tiene un comando Fusionar que puede unir consultas, con una opción para conservar solo las coincidencias. El resultado de una consulta se puede escribir en una tabla de Excel.

Sus requisitos tardarían unos minutos en diseñarse en Power Query, sin necesidad de código.

...

Me inspiraron algunos comentarios útiles para ampliar esta respuesta. De hecho, creé una solución funcional que puedes descargar desde mi OneDrive y probar:

http://1drv.ms/1AzPAZp

Es el archivo:Demostración de Power Query: devuelve varios valores para cada uno de una lista de valores

Me tomó menos de 2 minutos construirlo (sin contar copiar los datos de entrada y escribir la hoja Léame) y no requirió ningún código/función.

La técnica clave es Fusionar y Expandir, como se describe aquí:

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

información relacionada