match 多个查找对象_使用INDEX和MATCH查找文本

最新推荐文章于 2023-06-14 23:41:39 发布

culiao2169

最新推荐文章于 2023-06-14 23:41:39 发布

阅读量2.6k

点赞数

文章标签：字符串 python java excel 人工智能

原文链接：https://contexturesblog.com/archives/2012/10/09/find-text-with-index-and-match/

版权

match 多个查找对象

Is there a harder working team in Excel, than the reliable duo of INDEX and MATCH? These functions work beautifully together, with MATCH identifying the location of an item, and INDEX pulling the results out from the murky depths of data. See how to find text with INDEX and MATCH.

与可靠的INDEX和MATCH组合相比，Excel中有一支更努力的团队吗？这些功能可以完美地协同工作，MATCH可以识别项目的位置，而INDEX可以从模糊的数据深度中提取结果。了解如何使用INDEX和MATCH查找文本。

查找字符串中的文本 (Find Text in a String)

Last week, Jodie asked if I could help with a problem, and INDEX and MATCH came to the rescue again. Jodie sent me a picture of her worksheet, with text strings in column A and codes in column D. Each text string contained one of the codes, and Jodie wanted that code to appear in column B.

上周，乔迪(Jodie)问我是否可以解决问题，INDEX和MATCH再次进行了救援。 Jodie给我发送了一张她的工作表的图片，其中A列中的文本字符串和D列中的代码。每个文本字符串都包含其中一个代码，Jodie希望该代码出现在B列中。

Would you use INDEX and MATCH to find the code, or another method? Keep reading to see my solution, and please share your ideas, if you have other ways to solve this.

您将使用INDEX和MATCH查找代码还是其他方法？继续阅读以查看我的解决方案，如果您还有其他解决方法，请分享您的想法。

用COUNTIF计算发生次数 (Count the Occurrences With COUNTIF)

When you want to find text that's buried somewhere in a string, the * wildcard character is useful. We can use the wildcard with COUNTIF, to see if the string is found somewhere in the text.

当您要查找埋在字符串中某个地方的文本时，*通配符非常有用。我们可以将通配符与COUNTIF一起使用，以查看字符串是否在文本中找到。

I entered this test formula in cell B1. This formula needs to be array-entered, so press Ctrl + Shift + Enter.

我在单元格B1中输入了此测试公式。该公式需要输入数组，因此按Ctrl + Shift + Enter。

=COUNTIF(A1,"*" & $D$1:$D$3 & "*")

= COUNTIF(A1，“ *”＆$ D $ 1：$ D $ 3＆“ *”)

There are wildcard characters before and after the cell references to D1:D3, so the text will be found anywhere within the text string.

单元格引用D1：D3之前和之后都有通配符，因此可以在文本字符串中的任何位置找到该文本。

To see the results of the array formula, click in the formula bar, and press the F9 key. The array shows 0;1;0 so it found a match for CAT, which is in the second cell in the range.

要查看数组公式的结果，请在公式栏中单击，然后按F9键。数组显示0; 1; 0，因此找到了CAT的匹配项，该匹配项位于范围的第二个单元格中。

通过MATCH获得职位 (Get the Position With MATCH)

Press the Esc key, to exit the formula without saving the calculated results. Next, you can add the MATCH function, to get the position of the "1" in the results. Remember to press Ctrl + Shift + Enter.

按Esc键，不保存计算结果即可退出公式。接下来，您可以添加MATCH函数，以获取结果中“ 1”的位置。请记住按Ctrl + Shift + Enter。

=MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0)

= MATCH(1，COUNTIF(A1，“ *”＆$ D $ 1：$ D $ 3＆“ *”)，0)

The result is 2, so the code "CAT" was found in cell A1.

结果为2，因此在单元格A1中找到了代码“ CAT”。

使用INDEX获取代码 (Get the Code With INDEX)

Next, the INDEX function can return the code that is at the position that the MATCH function identified. Press Ctrl + Shift + Enter to see the result.

接下来，INDEX函数可以返回MATCH函数标识的位置处的代码。按Ctrl + Shift + Enter查看结果。

=INDEX($D$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0))

= INDEX($ D $ 1：$ D $ 3，MATCH(1，COUNTIF(A1，“ *”＆$ D $ 1：$ D $ 3＆“ *”)，0)))

The result is CAT, so the formula is working correctly.

结果是CAT，因此公式正常工作。

使用IFERROR防止错误结果 (Prevent Error Results With IFERROR)

There should be a valid code in each text string, but sometimes the data doesn't cooperate. Just in case there are text strings without a code, or more than one instance of the code, you can use IFERROR to show an empty string, instead of an error. (Excel 2007 and later versions)

每个文本字符串中都应该有一个有效的代码，但有时数据无法配合。万一有没有代码的文本字符串，或代码的多个实例，则可以使用IFERROR显示一个空字符串，而不是一个错误。 (Excel 2007及更高版本)

=IFERROR(INDEX($D$1:$D$3,MATCH(1,COUNTIF(A1,"*"&$D$1:$D$3&"*"),0)),"")

= IFERROR(INDEX($ D $ 1：$ D $ 3，MATCH(1，COUNTIF(A1，“ *”＆$ D $ 1：$ D $ 3＆“ *”)，0))，“”)

Enter with Ctrl + Shift + Enter, and then copy the formula down to row 10.

使用Ctrl + Shift + Enter输入，然后将公式向下复制到第10行。

In cell B6, the formula returns an empty string, and the cell looks blank, because none of the valid codes are in the text that's in cell A6.

在单元格B6中，该公式返回一个空字符串，并且该单元格看起来为空白，因为在单元格A6中的文本中都没有有效的代码。

使用命名范围 (Use a Named Range)

Instead of referring to range $D$1:$D$3, you could name that range, and use the name in the INDEX/MATCH formula. That would make it easier to maintain, if the size of the codes list will change.

您可以命名该范围，而不必使用$ D $ 1：$ D $ 3范围，而可以在INDEX / MATCH公式中使用该名称。如果代码列表的大小将更改，这将使维护更加容易。