☰

在 Excel 中将产品编号与相应的缩写产品编号进行匹配

tag-icon • tag-icon microsoft-excel
在 Excel 中将产品编号与相应的缩写产品编号进行匹配

我有一份缩写商品编号列表，其中包含相应的佣金率数据。当我下载销售数据时，它包含完整的商品编号。我想将完整的商品编号与正确的缩写（工作簿中的另一个选项卡）匹配。正确的缩写与相应商品编号的开头相匹配 - 缩写的长度在 3 到 5 个字符之间。

如何根据完整的产品编号检索适当的缩写？

答案1

这是一种解决产品编号和缩写代码长度变化以及不同长度的缩写代码共享一些首字母或数字的可能性的方法。

它使用三个辅助列VLOOKUPs，分别对每个项目的前 3、4 和 5 个字符的缩写代码进行精确匹配。对于任何缩写代码查找，都可能找到匹配项，或者（在数据错误的情况下）找不到任何匹配项。

第四列计算包含在三个结果中进行选择的公式VLOOKUPs。

选择规则很简单。如果在缩写代码列表中找到一个匹配项，则返回匹配的代码。如果找到两个或三个匹配项，则返回字符数最多的匹配项。如果未找到任何匹配项，则公式返回错误值 #N/A。

计算第四列的选择表达式是数组公式，必须用Control- Shift-Enter组合键输入（即，必须这样输入第一行的公式，然后才能复制到项目编号列表的底部）。

对于下面的示例公式，完整项目编号列表位于A2:A10Sheet 1 的单元格中。缩写代码列表位于A2:A10Sheet 2 的单元格中。辅助公式位于B-DSheet1 的列中，而选择数组公式位于E该工作表的列中。（通过将滚动条向右移动，示例数组公式应完全可见。）

公式

Cell B2:   =VLOOKUP(LEFT($A2,3),Sheet2!$A$2:$A$10,1,0)
Cell C2:   =VLOOKUP(LEFT($A2,4),Sheet2!$A$2:$A$10,1,0)
Cell D2:   =VLOOKUP(LEFT($A2,5),Sheet2!$A$2:$A$10,1,0)

Cell E2:   =IFERROR(INDEX($B2:$D2,1,MAX(NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3)))),#N/A)

我将简单解释一下第四个公式的工作原理。

  • NOT(ISERROR($B2:$D2))返回一个由 TRUE/FALSE 值组成的三元素数组，其中匹配的辅助列（在特定行中）为 TRUE，否则为 FALSE。例如，{TRUE, TRUE, FALSE}。

  • TRANSPOSE(ROW($1:$3))只需提供数组 {1, 2, 3}，每个数字表示一个辅助列。

  • 它们的乘积NOT(ISERROR($B2:$D2))*TRANSPOSE(ROW($1:$3)))——生成一个数组，其中匹配的列的值为 1、2 或 3，否则为零，如 {1, 2, 0}。

  • 该MAX函数返回该数组中的最大数字，该数字对应于返回字符数最多的匹配项的辅助列。在 {1, 2, 0} 数组中，MAX对于 C 列（辅助列中的第二列）中的匹配项，返回 2。

  • INDEX然后选择 C ​​列中的缩写代码。

  • 最后，IFERROR如果未找到匹配的缩写代码，该函数将返回 #N/A。


工作表1

表 1e


工作表2

工作表2

答案2

如果产品编号后面始终跟着两位数字/字符，如示例数据 (057GK12、057GK20 和 057GK31) 所示，则以下内容将为您提供产品编号：

=LEFT(A1,LEN(A1)-2)

如果您尝试查找其中包含缩写的单元格，您可以执行以下操作：

=MATCH(LEFT(A1,LEN(A1)-2),Sheet2!B:B,0)

如果您尝试从缩写中找到完整的值，您可以执行以下操作：

=MATCH(B1&"??",Sheet1!A:A,0)

这些仅提供列中的第一个结果。这还假设项目编号和缩写被格式化为文本而不是值。

相关内容