Excel - 為每個值清單傳回多個值

Excel - 為每個值清單傳回多個值

我已經嘗試解決這個問題好幾個小時了,但沒有結果...

我的設定:

  • 我有 excel 表格化妝品產品配方

  • 每個食譜都包含數十種成分,每種成分都有其特定的成分商品名稱

  • 我有一個很長的清單,將每個商品名稱翻譯成它的名稱'科學名稱

我需要將每個商品名稱翻譯成其對應的科學名稱。這部分很容易透過 vlookup 完成。

但一些商品名稱食譜其本身含有表中的多種成分學名。所以這不是一對一的關係,有時(並不總是)是一對多的關係。

例如:

化妝品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

相關內容