☰

在字串中尋找多個編碼值，然後為每個找到的代碼傳回一個查找值

tag-icon • tag-icon microsoft-excel microsoft-excel-2007 worksheet-function
在字串中尋找多個編碼值，然後為每個找到的代碼傳回一個查找值

我有 600 個商品代碼，每個商品代碼都在整個商品代碼中以屬性進行編碼。例如，我們可能有普通商品 600，然後是 600BK（黑色主體）和 600BKR（黑色主體，紅色細節）和 600BKR-YEL（黑色主體，紅色細節，黃色底座）。

所以我得到了項目代碼列表：

600
600BK
600BKR
600BKR-YEL

然後在一個單獨的工作表上，列出每個代碼及其代表的含義：

BK    Black Body
R     Red Detail
YEL   Yellow Base

我想編寫一個函數，該函數可以查找查找表上的項目標題中的每個程式碼，然後返回每個找到的程式碼的所有對應值，最好在一個單元格中。

600
600BK         Black Body
600BKR        Black Body Red Detail
600BKR-YEL    Black Body Red Detail Yellow Base

這可能嗎？

答案1

我會做類似下面的事情。假設您的項目代碼清單位於 Sheet1 的 A 列，例如第 2-20 行，並且您的查找清單位於 Sheet2 的 A 列和 B 列，第 2-10 行：

  =IFERROR(VLOOKUP(MID(A2,4,2),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,6,1),Sheet2!$A$2:$B$4,2,0),"")&" "
  &IFERROR(VLOOKUP(MID(A2,8,3),Sheet2!$A$2:$B$4,2,0),"")

在儲存格 B2 中，然後向下複製 Sheet1 中的所有行。

雖然它確實將查找放在一個公式中，但如果您有很多程式碼，這種方法會非常麻煩。我建議您在 Sheet1 中為每個程式碼位置插入輔助行（例如 mid(A2,4,2)），然後將它們連接成一個字串。

答案2

簡短的答案是“是”，一種方法是相當長。

由於您的問題中沒有排除許多可能性（例如編碼長度超過三個數字或字母數字的普通項目、每個普通項目超過三個代碼、代碼的重要性取決於普通項目等），我建議最安全的方式是從解析開始程式碼。這應該可以避免 GR 為帶有紅色細節的灰體（通常是兼容的配色方案！）或綠體之類的複雜情況。

假設這些已被解析（在從 Row2 開始的三列 B:D 中），此後很容易，用您的查找表（單獨的工作表上的那個）命名為codes：

=VLOOKUP(B2,codes,2,0)&VLOOKUP(C2,codes,2,0)&VLOOKUP(D2,codes,2,0)

在您的商品代碼表中。為了在值之間獲得空格，我假設代碼數組右側列中的所有條目都以空格結尾（很容易排列，例如複製下來的 =A2&" " 等）。

因此，困難的部分可能是從專案程式碼中解析出程式碼，為此我建議添加如下列（如果程式碼超過三個，則添加更多列）：

SU531526第一個例子

公式如下：

SU531526第二個例子

ColumnB 用於定義從哪裡開始尋找代碼（如果純代碼不是三個數字）。 C:D 欄位是從哪裡開始搜尋下一個代碼/下一個代碼的長度。我同意它不優雅，但相對通用。在向右進行解析之前，請確保左側的解析正確。

成功解析後，我建議複製/貼上特殊/值（以刪除公式）然後替換空白的帶有句號（以避免查找公式出錯而不使其變得更複雜）*。另外，假設您的項目代碼清單始終位於 ColumnA 中，請在應用上述查找公式之前刪除 ColumnsB:F（或調整引用以適應），並在必要時添加更多查找。

*監督：確保在查找表每列的儲存格中新增句號。

新增 c5、c6 和 c7 列，以允許 (a) 最大長度 7 和 (b) 「最壞情況」場景（即全部為單一字元）。

答案3

這是一個使用搜尋功能的版本（find 的不區分大小寫的版本）。設定如下。

在 Sheet 1 中，要尋找的程式碼從 Sheet 1 的 A 列開始。 R”等。實現此目的的實際方法是簡單地複製查找表中的代碼列表，然後在單元格C1、D1 等中水平粘貼特殊轉置它們。

然後先在儲存格 B2 中輸入以下公式：

  =IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")

將此公式從 C 列複製到您在第 2 行中新增的程式碼列數。

最後，在儲存格 C2 中，連接第 2 行的所有結果，即公式

 =D2&" "&E2&" "&F2

等等，對於第 1 行中所有帶有程式碼的欄位。

  Function Concat(useThis As Range, Optional delim As String) As String
     ' this function will concatenate a range of cells and return the result as a single string
     ' useful when you have a large range of cells that you need to concatenate
     ' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/

  Dim retVal As String, dlm As String, cell As Range
  retVal = ""
  If delim = Null Then
     dlm = ""
  Else
     dlm = delim
  End If
  For Each cell In useThis
      If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
          retVal = retVal & CStr(cell.Value) & dlm
      End If
  Next
  If dlm <> "" Then
     retVal = Left(retVal, Len(retVal) - Len(dlm))
  End If
  Concat = retVal
  End Function

您可以將此函數插入並複製到 Developer VBA 中的模組中。用法很簡單－例如 concat(C1:D1," ")。

請注意，此方法適用於所有 2 字元代碼，以及所有 1 字元代碼（如果它們不在 2+ 字元代碼中），即，如果不存在諸如“R”和“BR”之類的代碼對。

相關內容