☰

在字符串中查找多个编码值，然后返回每个找到的代码的查找值

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 是灰色主体带红色细节（通常是兼容的配色方案！）或绿色主体等复杂情况。

假设这些已经被解析（从第 2 行开始在 B:D 三列中），之后很容易，使用您的查找表（单独工作表上的表）命名codes：

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

在您的商品代码表中。为了在值之间留出空格，我假设代码数组右侧列中的所有条目都以空格结尾（很容易排列，例如将 =A2&" " 复制下来等）。

因此，困难的部分可能是从项目代码中解析出代码，为此我建议添加如下所示的列（如果有三个以上的代码，则添加更多列）：

SU531526 第一个例子

公式如下：

SU531526 第二例

ColumnB 用于定义从哪里开始查找代码（如果纯代码不是三个数字）。ColumnC:D 用于从哪里开始搜索下一个代码/下一个代码的长度。我同意这并不优雅，但相对通用。在向右侧工作之前，请确保左侧的解析正确。

解析成功后，我建议复制/粘贴特殊/值（以摆脱公式），然后替换空白的使用句号（以避免查找公式出错而不会使其变得更复杂）*。此外，假设您的商品代码列表位于 ColumnA 中，请在应用上述查找公式之前删除 ColumnsB:F（或调整引用以适应）-并根据需要添加更多查找。

*疏忽：确保在查找表的每一列的单元格中添加句号。

添加 c5、c6 和 c7 的列以允许 (a) 最大长度 7 和 (b)“最坏情况”场景（即所有单个字符）。

答案3

这是使用搜索功能的版本（不区分大小写的 find 版本）。设置如下。

在工作表 1 中，要查找的代码从工作表 1 的 A 列开始。最终结果将在 B 列中。C、D 等列将唯一代码水平排列在第 1 行中，即“BK”在 C1 中，“R”在 D2 中，等等。实现此目的的实际方法是简单地复制查找表中的代码列表，然后通过选择性粘贴将它们水平转置到单元格 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 行中包含代码的所有列。这个步骤很繁琐，但可以使用以下 VBA 函数简化，该函数允许连接范围内的所有单元格：

  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”的代码对），则该方法也适用于所有 1 个字符的代码。

相关内容