Excel返回数组中的多个部分字符串匹配 [英] Excel Return multiple partial string matches in array(s)

问题描述

挑战:

• 要有一个公式(很可能是数组公式)，该公式将从一列/行中返回多个 partial 匹配项.

• To have a formula (most likely an array formula) that will return multiple partial matches from a column/row.

参数:

• 不能使用INDIRECT，因为它不能扩展，并且如果移动或插入数据会损坏
• 公式必须以最小的努力即可扩展(即:将角拖动到您需要扩展的方向以显示下一个部分匹配项)

• Cannot use INDIRECT, as this is not scalable and will break if data is moved or inserted
• Formula must be expandable with minimum effort (ie: drag the corner in the direction you need to expand it to display the next partial match)

注意:

• 不幸的是，INDEX/MATCH函数组合不适用于部分字符串匹配
• 我有一个基于其他人工作的解决方案，我将放在下面，但是我也很好奇是否有一个我看不到的更优雅的解决方案.非常感谢其他人的反馈

• Unfortunately the INDEX/MATCH function combination does not work for a partial string match
• I have a solution based on other people's work, which I will place below, but I am also curious if there is an even more elegant solution that I'm not seeing. I would very much appreciate other people's feedback

演示图片:

推荐答案

在单元格A2中(请参阅问题的所附图像)，我已放置了以下公式:

In cell A2 (see attached image from question), I've placed this formula:

=IF($E$2="","",IFERROR(INDEX(A$7:A$28,SMALL(IF(ISNUMBER(SEARCH($E$2,A$7:A$28)),ROW(A$7:A$28)-ROW(A$7)+1),ROWS(A$2:A2))),""))，然后按 CTRL + SHIFT + ENTER ，以公式形式输入数组公式.最后，将此公式向下拖动到A5，然后将A2:A5拖动到C列.

=IF($E$2="","",IFERROR(INDEX(A$7:A$28,SMALL(IF(ISNUMBER(SEARCH($E$2,A$7:A$28)),ROW(A$7:A$28)-ROW(A$7)+1),ROWS(A$2:A2))),"")) then pressing CTRL+SHIFT+ENTER to enter the formula as an array formula. Finally, dragging this formula down to A5, and then dragging A2:A5 over to the C column.

如果某人想要水平而不是垂直返回结果(如我所做的那样)，他们只需将公式的ROWS(A$2:A2)部分更改为COLUMNS(A$2:A2)并向侧面拖动适当的列数即可.

If a person wants to return their results horizontally instead of vertically (as I have done), they would simply change the ROWS(A$2:A2) portion of the formula to COLUMNS(A$2:A2) and drag sideways the appropriate number of columns.

此解决方案的原始想法来自 barry houdini ，作为对

The original idea for this solution is from barry houdini as an answer to this question.

此公式的工作方式为:

1. 计算搜索词框(在此示例中为E2)是否为空白，如果为空白，则结果单元格也保持空白.在此示例中，它不是空白，并且开始计算.
2. 接下来，它使用SEARCH函数搜索我们的搜索词(在本示例中为"ke").它返回一个错误消息数组和(如果有任何匹配项)它们在网格中的位置编号.
3. 接下来，使用ISNUMBER函数，根据SEARCH在上一步中返回的内容，将数组中的值转换为"FALSE"和"TRUE".在我们的示例中，此单元格将在数组的第三位置返回"TRUE".
4. 由于我们的IF函数具有"TRUE"响应，因此我们现在将评估数组中该特定点的返回值. ROW(A$7:A$28)-ROW(A$7)+1部分根据数组的起始位置将数组的所有绝对行值转换为相对值.
5. 在我们的SMALL函数中，我们现在有了一个数组，其中包含"FALSE"和匹配项的任何相对位置(在此示例中为"3").
6. 下一个评估步骤是公式末尾的第n个计数器".因为我们处于输出第一个结果(A2)的位置，所以它将返回数字"1".
7. 我们的SMALL函数求值，并返回第一个最小的结果("FALSE"被消除，返回的数字是步骤5中的3).
8. 我们的INDEX函数现在求值，并返回单元格A$7:A$28的第三个位置的单元格，即米老鼠".

1. Evaluates if the search term box (E2 for this example) is blank, if it is, it keeps the result cell blank as well. In this example, it is not blank, and it begins calculating.
2. Next it searches for our search term ("ke" for this example) using the SEARCH function. It returns an array of Error messages and (for any matches) numbers of their position in the grid.
3. Next, using the ISNUMBER function, it converts the values in the array to "FALSE" and "TRUE" based on what the SEARCH returned in the previous step. In our example, this cell will be returning a "TRUE" in the Third position of the array.
4. Since we have a "TRUE" response in our IF function, we will now evaluate what to return for that specific spot in the array. The ROW(A$7:A$28)-ROW(A$7)+1 section converts all the absolute row values of our array into relative values based on our array starting location.
5. Inside our SMALL function we now have an array containing "FALSE" and any relative positions of the matches (in this example "3").
6. The next evaluation step is our "nth counter" at the end of our formula. Because we are in the position that would output our first result (A2), it will return the number "1".
7. Our SMALL function evaluates, and returns the first smallest result ("FALSE" is eliminated, and the number returned is the 3 from step 5).
8. Our INDEX function now evaluates, and returns the cell in the third position of the array A$7:A$28 which is "Mickey Mouse".

这篇关于Excel返回数组中的多个部分字符串匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！