☰

从列表中检查单元格以哪个关键字开头并返回匹配的关键字

tag-icon • tag-icon microsoft-excel worksheet-function
从列表中检查单元格以哪个关键字开头并返回匹配的关键字

我正在尝试获取产品列表并从产品名称的开头提取其制造商。每个产品名称都以其制造商开头。由于某些商品的名称正文中还有其他制造商，因此这很复杂；我需要查看商品以什么开头。我正在处理 50,000 多个商品和 3,000 多个制造商。到目前为止，我拥有的公式是：

=LOOKUP(1,1/(FIND($C$2:$C$5,B2)),$C$2:$C$5)

有时这种方法有效，有时则无效。例如，在下面的工作表中，第 2 行和第 3 行是正确的，但第 4 行不正确。单元格 A4“Mike's Fun Toys”（单元格中B4）的结果应为“Mike's”，但结果显示为“Fun”。

按字母倒序排列制造商的工作表

（以下是您可以复制粘贴的文本形式的数据：

+---+---------+-----------------+---------------+
|   |    A    |        B        |       C       |
+---+---------+-----------------+---------------+
| 1 | Formula | Items           | Manufacturers |
+---+---------+-----------------+---------------+
| 2 | Brown   | Brown Cat Toys  | Mike's        |
| 3 | Cat     | Cat Fun Toys    | Fun           |
| 4 | Fun     | Mike's Fun Toys | Cat           |
| 5 |         |                 | Brown         |
+---+---------+-----------------+---------------+

）

但是当我改变列（制造商）的顺序时 C：

按（正常）字母顺序列出制造商的工作表

第 4 行变为正确（“Mike's”），但第 2 行变为错误。

A 列自上而下包含整个公式。预期结果：

A2 - Brown
A3 - Cat
A4 - Mike's

如何才能使公式起作用，而不管列的顺序如何 C？

答案1

对于那些不理解 RyanMark 公式的读者来说，

  • 查找给定产品名称中每个制造商名称的位置。结果为
    • 1制造商名称的值，以产品名称开头（因为它出现在1st 字符），
    • 对于产品名称中出现的其他制造商名称，数字更高（因为它们出现得更靠后，位置数字更高），并且
    • #VALUE!产品名称中未出现的制造商名称的错误代码。
  • 将上述每个数字取反（用 1 除以它），得到
    • 1对于以产品名称开头的制造商名称（也就是我们想要查找的制造商名称），
    • 对于产品名称中出现的其他制造商名称，其正数较低（因为 1 除以大于 1 的数字得到的比率小于 1），并且
    • #VALUE!产品名称中未出现的制造商名称的错误代码。
  • 使用在上文中LOOKUP查找。1

例如，对于第一幅图中的单元格A4（对应于单元格中的“Mike's Fun Toys” B4），我们按顺序获得，

  • 1，因为“Mike's”（C2）以“Mike's Fun Toys”开头，
  • 8，因为“Fun”（C3）出现在“Mike's Fun Toys”的第 8 个字符处，并且
  • #VALUE!并且#VALUE!，因为“Cat”（C4）和“Brown”（C5）没有出现在“Mike's Fun Toys”中。

反转结果为1、  0.125( 1/8) #VALUE!和。然后它在数组中#VALUE!查找。这“应该”有效，因为是第一个结果，而“Mike's”是 Column 中的名字 。11C

您可以在以下帮助页面中看到此问题LOOKUP：

为了抬头为了使函数正常工作，查找的数据必须按升序排列。

并且显然1后面0.125没有按升序排列。

正如LOOKUP建议的那样，我们可以使用 来解决这个问题MATCH。您想要的公式使用与您的公式相同的基本方法（除了没有反转，这是不必要的），是

=INDEX($C$2:$C$5, MATCH(1, FIND($C$2:$C$5,$B2), 0))

的第三个参数MATCH称为“match_type”。我在这里将其设置为0，这意味着MATCH将在数组中查找第一个恰好为的元素1，并且不会假设数组已排序。

这是一个数组公式，因此输入时必须按++ Ctrl。ShiftEnter

相关内容