
我已經嘗試解決這個問題好幾個小時了,但沒有結果...
我的設定:
我有 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 下載並嘗試:
這是文件:Power Query 演示 - 為值清單中的每個值傳回多個值
我花了不到 2 分鐘的時間來建立(不包括複製輸入資料和編寫自述表),並且不需要任何程式碼/函數。
關鍵技術是合併和擴展,如下所述: