☰

Excel - 为值列表中的每个值返回多个值

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
Excel - 为值列表中的每个值返回多个值

我已经尝试解决这个问题好几个小时了，但没有结果......

我的设置：

  • 我有以下 Excel 表化妆品配方。

  • 每个食谱包含几十种原料，每种原料都有其商品名称。

  • 我有一个长长的清单，将每个商品名称翻译成其'科学名称。

我需要将每个商品名翻译成相应的科学名称。这部分使用 vlookup 很容易完成。

但一些商品名称是食谱本身就含有表中的多种成分科学名称。因此，它不是 1：1 的关系，有时（并非总是）是 1：多的关系。

例如：

化妆品A片

 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

您必须打开宏/VBA，在下方插入一个模块本工作簿并将代码粘贴到右侧。

请记住以下几点：

  • 代码在活动工作表上运行。
  • 除第一个商品名外，每个新的科学名称都添加在新的一行中。
  • 只要工作表的 A 列上没有空白单元格，代码就可以运行。
  • 假设主列表工作表的名称是掌握您必须将该行更改mastername="Master"为适当的名称。
  • 设置500个商品名和5000个科学名称需要23秒。

答案2

对于 Excel 2010 或 2013，我会使用 Power Query 插件来实现此目的。从 Excel 2016 开始，Power Query 内置于“获取和转换”部分下的数据功能区中。

Power Query 可以从 Excel 表开始。它有一个合并命令，可以将查询合并在一起，并可以选择仅保留匹配项。查询结果可以写入 Excel 表。

您的需求只需在 Power Query 中花几分钟进行设计，无需任何代码。

...

一些有用的反馈启发了我来扩展这个答案。事实上，我构建了一个可行的解决方案，您可以从我的 OneDrive 下载并尝试：

http://1drv.ms/1AzPAZp

这是文件：Power Query 演示 - 为值列表中的每个值返回多个值

我花了不到 2 分钟的时间来构建（不包括复制输入数据和编写自述表），并且不需要任何代码/功能。

关键技术是合并和扩展，如下所述：

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

相关内容